namespace ExceltoWindow { public partial class Form1 : Form { private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"; private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { } private void button1_Click(object sender, EventArgs e) { openFileDialog1.ShowDialog(); } private void openFileDialog1_FileOk(object sender, CancelEventArgs e) { string filePath = openFileDialog1.FileName; string extension = Path.GetExtension(filePath); string header = rbHeaderYes.Checked ? "YES" : "NO"; string conStr, sheetName; conStr = string.Empty; switch (extension) { case ".xls": //Excel 97-03 conStr = string.Format(Excel03ConString, filePath, header); break; case ".xlsx": //Excel 07 conStr = string.Format(Excel07ConString, filePath, header); break; } //Get the name of the First Sheet. using (OleDbConnection con = new OleDbConnection(conStr)) { using (OleDbCommand cmd = new OleDbCommand()) { cmd.Connection = con; con.Open(); DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); con.Close(); } } //Read Data from the First Sheet. using (OleDbConnection con = new OleDbConnection(conStr)) { using (OleDbCommand cmd = new OleDbCommand()) { using (OleDbDataAdapter oda = new OleDbDataAdapter()) { DataTable dt = new DataTable(); cmd.CommandText = "SELECT * From [" + sheetName + "]"; cmd.Connection = con; con.Open(); oda.SelectCommand = cmd; oda.Fill(dt); con.Close(); //Populate DataGridView. dataGridView1.DataSource = dt; } } } } } }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)