Read data from Excel files

Question

I'm having some trouble reading from an Excel spreadsheet in C#. I have this code which I read every cell from A to X.

System.Array myvalues; string[] strArray;
Microsoft.Office.Interop.Excel.Range range = 
    worksheet.get_Range("A" + i.ToString(), "W" + i.ToString());

while(range.Count!=0)
{
    i++;
    //Console.WriteLine(i);
    range = worksheet.get_Range("A" + i.ToString(), "W" + i.ToString());
    myvalues = (System.Array)range.Cells.Value;
    strArray = ConvertToStringArray(myvalues);
    name = clearstr(strArray[1]);

    for ( int j = 1 ; j <= Int32.Parse(number_add_file)*4 ; j++) 
    {
        name = "";
        lang_add = "";
        price = "";
        description = "";
        Console.WriteLine("I got in!");

        Microsoft.Office.Interop.Excel.Range range_add = 
            worksheet.get_Range("X" + i.ToString(),Type.Missing);

        System.Array values = (System.Array)range_add.Cells.Value;
        string[] str = ConvertToStringArray(values);
        name = str[0];
        lang_add = str[1];
        price = str[2];
        description = str[3];
        Console.WriteLine(name + "   " 
          + lang_add + "   " + price + "   " + description);

        addfile();
    }

My question is: How could i read next 4 * "number" rows in excel based on "number" value ?

For example:

         A B C D E F G H I J
         a a a a a 1 a a a a 

F's cell value is 1 so I would like to read ( G H I J) If F's cell value is 2 the I would like to read ( G H I J K L M N)

         A B C D E F G H I J K L M N
         a a a a a 2 a a a a a a a a

F's cell value 3 :

        A B C D E F G H I J K L M N O P Q R
        a a a a a 3 a a a a a a a a a a a a

Solution

This is for .NET 4.0:

using Excel = Microsoft.Office.Interop.Excel;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open("somefile.xls");
Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // assume it is the first sheet
Excel.Range xlRange = xlWorksheet.UsedRange; // get the entire used range
int value = 0;
if(Int32.TryParse(xlRange.Cells[1,6].Value2.ToString(), out value)) // get the F cell from the first row
{
   int numberOfColumnsToRead = value * 4;
   for(int col=7; col < (numberOfColumnsToRead + 7); col++)
   {
      Console.WriteLine(xlRange.Cells[1,col].Value2.ToString()); // do whatever with value
   }
}

This will open the workbook and get the first worksheet in the workbook. We then get the entire used range and put that in the range variable. From there, we try to parse the int in column "F" (which is the 6th column, it is 1 based not zero based) in the first row. If that parsing is successful, we then multiply that number by 4 to see how many columns you need (in your post you said rows, but your examples were columns). We use a for loop to start at the G column (column 7) and go to the number of columns to read + 7 (to account for the columns that we skipped). You would be free to do what you want with the values but for this example I just wrote them to the console.

Comments

[Vlasin]

Your code throws me this error : Cannot perform runtime binding on a null reference !

[Jetti]

@Vlasin - I apologize, I hadn't tested it before I wrote it. I have fixed the code and I just tested it myself and it should work for you now.

[Praveen Puglia]

code is good. explaining why it's good is awesome! can you do that please?