Importing data directly from Excel
Posted on December 21, 2009
using System; using System.Collections.Generic; using System.Data; using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; namespace ExcelImport { public partial class Form1 : Form { public Form1() { InitializeComponent(); System.IO.FileInfo info = new System.IO.FileInfo("C:\\cosa.xls"); this.openFileDialog1.FileName = "*.xls"; DataTable dt = new DataTable(); dt.Columns.Add("ID"); dt.Columns.Add("Name"); dt.Columns.Add("Date"); dt.Columns.Add("Last Name"); dt.Columns.Add("Address"); if (this.openFileDialog1.ShowDialog() == DialogResult.OK) { /* For some reason, all excel lists and "arrays" are not zero based. * This is important to keep in mind while looping thru everything */ Excel.Application ExcelObj = new Excel.Application(); //I really don't know what the other 13 or so parameters do, but i swear this works Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(openFileDialog1.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Sheets sheets = theWorkbook.Worksheets; Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1); /* This is neat. Only retrieve the used range in the worksheet. * Instead you could load the entire worksheet and get ranges for each row, * but this approach is *much* faster*/ Excel.Range range = worksheet.UsedRange; object[,] value = (object[,])range.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault); int rows = value.GetLength(0); for (int i = 1; i < = value.GetLength(0); i++) { dt.Rows.Add(); for (int j = 1; j <= value.GetLength(1); j++) { dt.Rows[dt.Rows.Count - 1][j - 1] = value[i, j]; } } //This line is very important. Otherwise you'll end up with N excel processes ExcelObj.Quit(); } dataGridView1.DataSource = dt; } } }