How to read Excel cells and write to database

Question

I am writing an C# application that reads from an an excel sheet. It reads the whole sheet, however I am interested in just reading particular cells in a row and move to the next row and read particular cells in that row again. Cells not read or omitted are the same for all rows in excel. Below is my sample code for reading excel:

private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:/test.xlsx");
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;
            for(int i=1; i <= rowCount; i++)
                {
                    for(int j=1; j<=colCount; j++)
                        {
                             MessageBox.Show(xlRange.Cells[i,j].Value2.ToString());
                        }
                }
        }

just adjust the i and j values in your for loop to only go over the cells you want? — PoeHaH, Aug 13 '12 at 14:30
right now you're reading from row 1 to the rowcount and cell 1 to the columncount. This means you're reading every row and every cell. If the cells you'd like to read are all next to eachother, you can change the second for loop to something like : for(int j=6;j<=8;j++), which will read only cell 6 to 8. If the cells you'd like to read are not next to eachother, it'll be trickier with this code — PoeHaH, Aug 15 '12 at 06:01
@PoeHaH The cells are not next to each. Thats the trickier part. I would like to read cell 1 to cell 3 then skip read cell 6 to cell 8. I think I have to use SQL and modify my SELECT statement and cells to be read. My SQL has to be dynamic i.e changing for every row. — Sithelo, Aug 16 '12 at 07:46

score 0 · Answer 1 · answered Aug 13 '12 at 14:42

You could use OleDB instead of Excel.Interop. Your question requires only reading cell values so it is easier to treat your excel file as a datatable itself....

string fileName = @"C:\test.xlsx"; 
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;" + 
                          "Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=0'", fileName); 
using(OleDbConnection cn = new OleDbConnection(connectionString))
{
   cn.Open();
   OleDbCommand cmd = new OleDbCommand("SELECT Column1, Column2, Column3 From [Sheet1$]", cn);
   OleDbDataReader rd = cmd.ExecuteReader();
   while(rd.Read())
   {
        Console.WriteLine("Loop");
        Console.WriteLine(rd.GetString(0));
        Console.WriteLine(rd.GetString(1));
        Console.WriteLine(rd.GetString(2));
        Console.WriteLine();
    }
}

A couple of notes:

I'm using ACE.OleDB because your file has XLSX extensions
I'm assuming your file has Column headers in the first line called Column1. etc....

I have tried your code. At the moment i am getting error The 'Microsoft.JET.OLEDB.12.0' provider is not registered on the local machine. — Sithelo, Aug 14 '12 at 13:10
This [question and the relative answers](http://stackoverflow.com/questions/238625/microsoft-ace-oledb-12-0-provider-is-not-registered), will give you good infos to analyze this problem relative to your working computer. Hope it helps — Steve, Aug 14 '12 at 13:20

How to read Excel cells and write to database

1 Answers1