Retrieving data from Excel cells with C#

Question

please look at my code below, I tried to get the excel worksheet cells values with C#. However when I checked the values in the immediate window, they are not match with the real values at all. Some are null. Why? Thanks for advice.

string fname;
    int nRows;
    int nCols;
    private void button1_Click(object sender, EventArgs e)
    {
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWookSheet1;
        Excel.Range xlRange;

        openFileDialog1.Filter = "(*.xls)|*.xls|(*.xlsx)|*.xlsx";
        openFileDialog1.Title = "Select an excel file";
        if (openFileDialog1.ShowDialog() == DialogResult.OK)
            fname = openFileDialog1.FileName;
        System.IO.FileInfo fi = new System.IO.FileInfo(fname);
        string ext = fi.Extension.ToString();
        xlApp = new Excel.ApplicationClass();
        string parameter;
        if (ext == ".xls")
            parameter = "5";
        else
            parameter = "51";
        xlWorkBook = xlApp.Workbooks.Open(fname, 0, true, parameter, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        xlWookSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        object misValue = System.Reflection.Missing.Value;


        xlRange = xlWookSheet1.UsedRange;
        if (xlRange != null)
        {
            nRows = xlRange.Rows.Count;
            nCols = xlRange.Columns.Count;
            xlRange = (Microsoft.Office.Interop.Excel.Range)xlWookSheet1.Cells[nRows, nCols];
        }

        Dictionary<int, List<int>> dict = new Dictionary<int, List<int>>();
        List<string> list1 = new List<string>();
        for (int i = 1; i < nCols; i++)
        {
            string temp = (string)(xlRange.Cells[1, i] as Excel.Range).Value2; 
            // wrong values from temp
            list1.Add(temp);
        }

Not sure if this will help, but I always use "Range.Value". Try using "Value" instead of "Value2" — Mike Webb
– Mike Webb, Commented Feb 21, 2011 at 20:22

Harsh Baid · Accepted Answer · 2011-02-22 04:26:38Z

using System.Data.OleDb;
using System.Data;


public DataTable GetDataTableFromExcel(string FilePath, string strTableName)
        {
            string XLSConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            //string XLSConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
            OleDbConnection XLSCon = new OleDbConnection(XLSConnectionString);
            OleDbDataAdapter XLSDataAdp = new OleDbDataAdapter();
            XLSDataAdp.SelectCommand = new OleDbCommand();
            XLSDataAdp.SelectCommand.Connection = XLSCon;
            try
            {
                DataTable dtXLSData = new DataTable();
                XLSCon.Open();
                XLSDataAdp.SelectCommand.CommandText = "SELECT * FROM [" + strTableName + "$]";
                XLSDataAdp.Fill(dtXLSData);

                return dtXLSData;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                XLSCon.Close();
            }
        }

Try this... pass the file path and the worksheet name then parse the datatable as you like in simple way or LINQ way

VinayC · Accepted Answer · 2011-02-22 04:10:52Z

0

I believe that culprit would be below line:

xlRange = (Microsoft.Office.Interop.Excel.Range)xlWookSheet1.Cells[nRows, nCols];

This will collapse your range to just one cell - you can quickly test that by checking xlRange.Rows.Count. I will suggest that you remove this line and code should work. Another trick that you may find useful is Range.Value property will return array of values if range has multiple cells.

answered Feb 22, 2011 at 4:10

VinayC

49.6k7 gold badges64 silver badges74 bronze badges

Collectives™ on Stack Overflow

Retrieving data from Excel cells with C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related