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());
                        }
                }
        }

Answer 1

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....