how to read from excel row wise and store it in some tempoary variables in c#

Question

I am working on reading excel file in c#.

---

        AAAA            bbbbb              cccc
        1                2                  3
        4                5                  6
        --------------------------------------



        --------------------------------------

       data           data                  data

I want to read every row and store it in temporary varibales.

Can any one share the dlls needed and views on it.

Thank you.

Answer 1

 string connstr ="Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\aaa.xls;Extended Properties=Excel 8.0"/>

        OleDbConnection conn = new OleDbConnection(connstr);

        string strSQL = 
"SELECT * FROM [Sheet$]"; 


        OleDbCommand cmd = new OleDbCommand(strSQL, conn);

        DataSet ds = new DataSet();

        OleDbDataAdapter da = new OleDbDataAdapter(cmd);

        da.Fill(ds); 

Answer 2

You need to use OpenXml. Please read this question: open xml excel read cell value

Then you just need to loop for all cells and store where you want

Answer 3

you can follow these steps:

1. Add reference: Microsoft.Office.Interop.Excel
2. use that in your code behind: using Microsoft.Office.Interop.Excel;

3. use following code for getting excel data into DataTable:

   string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + datafilename + ";" + "Extended Properties=Excel 12.0;";
           OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
   
       //fetching excel data into DataTable
       System.Data.DataTable _dtDataFile = new System.Data.DataTable();
       myCommand.Fill(_dtDataFile);