MexiCode Ideas en codigo

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;
        }
    }
}
Tagged as: , No Comments