Reading from Excel (Range into multidimensional Array) C#

Question

How would I read from an Excel sheet and load the marked selection (Area) into an multidimensional array? A column in Excel could itself be a multi dimensional array since it would contain more than just one value.

The idea (not sure how good or bad this is) is right now is to do a for loop through all the Excel.Area (selected fields) and add the content of that field to the multi dimensional array. Since the multi dimensional array is of type object[,] and therefore non-generic there is no convenient add() method to it. All of it needs to be done manually.

Any idea if this approach is OK or if it could be done more efficiently?

Answer 1

You can read the value of Range as array:

using (MSExcel.Application app = MSExcel.Application.CreateApplication()) 
{
    MSExcel.Workbook book1 = app.Workbooks.Open( this.txtOpen_FilePath.Text);
    MSExcel.Worksheet sheet = (MSExcel.Worksheet)book1.Worksheets[1];
    MSExcel.Range range = sheet.GetRange("A1", "F13");

    object value = range.Value; //the value is boxed two-dimensional array
}

This code snippet is from .NET wrapper for MS Office. But same princip is in VSTO or VBA in MS Excel.

Answer 2

Here is the C# code to do this with SpreadsheetGear:

    // Load the workbook.
    SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(@"MyWorkbook.xlsx");
    // Get a range of cells as an array of object[,].
    object[,] values = (object[,])workbook.Worksheets["MySheet"].Cells["A1:J10"].Value;

SpreadsheetGear also provides fast APIs for accessing cells one at a time so that you can avoid copying the values to an array without sacrificing performance.

Disclaimer: I own SpreadsheetGear LLC