C# read in Excel file

Question

I want to get the data of an excel-file(from A2-A28--> first column) into my C# program. My code looks like this:

    using System.IO;    
    using Excel = Microsoft.Office.Interop.Excel; 

    private void btnEinlesen1_Click(object sender, EventArgs e)
    {
        ofd.Filter = "Excel (*.xls;*.xlsx)|*.xls;*.xlsx;|" + "All files (*.*)|*.*";
        ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        ofd.Title = "Exceldatei öffnen";
        ofd.FileName = "";
        DialogResult dr = ofd.ShowDialog();
        if (dr == DialogResult.OK)
        {
            Excel.Application xlApp;      //Excel starten
            Excel.Workbook xlWorkBook;   // Dokument anlegen
            Excel.Worksheet xlWorkSheet; // Blatt anlagen
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);  //Zugriff auf erstes Sheet

            string aux = "";
            for (int i = 2; i <= 28; i++)
            {
                aux += xlWorkSheet.get_Range("A" + i.ToString(), "A" + i.ToString()).Value.toString() + "/";
            }
            aux.ToArray(); //wandelt string aux in ein array um
            MessageBox.Show(aux.ToString());

            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

        }
    }
    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Unable to release the Object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    } 
}

}

But every time 1 read in the excel file (where A2-A28 is full with numbers) i get an error:

enter image description here

what's the problem?

Andre Hofmeister · Accepted Answer · 2014-10-10 08:08:39Z

1

http://aspxdeveloper.blogspot.com.au/2013/03/read-excel-file-in-c.html

protected void BtnReadExcel_Click(object sender, EventArgs e)
{
    try
    {
        OleDbConnection con = new OleDbConnection("
            Provider=Microsoft.Jet.OLEDB.4.0;
            Data Source=" + Server.MapPath("TestExcel.xlsx") + ";
            Extended Properties='Excel 8.0;HDR=Yes;'" 
        );

        OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);
        DataSet ds = new DataSet();
        da.Fill(ds);
    }
    catch (Exception ex)
    {
        throw;
    }
}

edited Oct 10, 2014 at 8:08

Andre Hofmeister

3,45511 gold badges55 silver badges80 bronze badges

answered Oct 10, 2014 at 7:37

Kuldeep Pal

111 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

Andre Hofmeister Over a year ago

Please describe your answer.

Sourav 'Abhi' Mitra · Accepted Answer · 2014-03-22 09:51:52Z

0

There is no need to loop with the get_Range method.

Remove

string aux = "";
for (int i = 2; i <= 28; i++)
{
    aux += xlWorkSheet.get_Range("A" + i.ToString(), "A" + i.ToString()).Value.toString() + "/";
}
aux.ToArray(); //wandelt string aux in ein array um

And just add this:

Excel.Range xlRange = xlWorkSheet.get_Range("A2:A28", System.Reflection.Missing.Value);
System.Array dataArray = (System.Array)(xlRange.Cells.Value2);

answered Mar 22, 2014 at 9:51

Sourav 'Abhi' Mitra

2,52019 silver badges16 bronze badges

Collectives™ on Stack Overflow

C# read in Excel file

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related