Thursday 20 December 2012

Excel Sheet Column Names



using Excel = Microsoft.Office.Interop.Excel;   //namespace
Excel.Application sExcelApp;
Excel.Workbook sWorkbook;
 


Solutions 1: 
sExcelApp = new Microsoft.Office.Interop.Excel.Application();
sWorkbook = sExcelApp.Workbooks.Open(sFilePath, 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);  //sFilePath  Excel File Path
            Excel.Worksheet workSheet = (Excel.Worksheet)sWorkbook.Worksheets.get_Item(XlSheetNo);  //Sheet No (sheet1=1 / sheet2=2)
            Excel.Range range = workSheet.UsedRange;
            for (int i = 1; i <= range.Columns.Count; i++)
            {
                this.Invoke(new MethodInvoker(delegate()
                {
                    listBox1.Items.Add(((Excel.Range)range.Cells[1, i]).Value2);
                }));
            }

Solutions 2:

using System.Data.Oldeb; //namespace

OleDbConnectionStringBuilder connectionStringBuilder = new OleDbConnectionStringBuilder();
            connectionStringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            connectionStringBuilder.DataSource = sFilePath;
            connectionStringBuilder.Add("Mode", "Read");

            const string extendedProperties = "Excel 12.0;IMEX=1;HDR=YES";
            connectionStringBuilder.Add("Extended Properties", extendedProperties);

           
            using (OleDbConnection connection = new OleDbConnection(connectionStringBuilder.ToString()))           
            {
                connection.Open();
                OleDbDataAdapter Oapter = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", connection);
                oDataSet = new DataSet();
                Oapter.Fill(oDataSet);
                DataTable dt = (oDataSet.Tables[0]);
                foreach (DataTable table in oDataSet.Tables)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        this.Invoke(new MethodInvoker(delegate()
                        {
                            listBox1.Items.Add(column.ColumnName.ToString());
                        }));
                    }
                }
                connection.Close();
            }
 

No comments:

Post a Comment