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?

spaghettifunk · Accepted Answer · 2014-04-07 13:42:18Z

20

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.

edited Apr 7, 2014 at 13:42

spaghettifunk

2,0244 gold badges26 silver badges47 bronze badges

answered May 26, 2009 at 12:31

TcKs

26.8k11 gold badges72 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Erickson · Accepted Answer · 2009-05-26 14:18:49Z

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

answered May 26, 2009 at 14:18

Joe Erickson

7,2672 gold badges33 silver badges31 bronze badges

4 Comments

gyurisc Over a year ago

Why is it better to use SpreadsheetGear versus the Excel Interop?

Joe Erickson Over a year ago

That depends on the kind of application you are building and what you are doing with it. Rather than listing them here, take a look at comments from our customers on the right hand side of this page: spreadsheetgear.com/products/spreadsheetgear.net.aspx

Terry Over a year ago

"Also provides fast APIs"...is that the SpreadsheetGear.Advanced.Cells API or just the fact that we are casting the value here to an object[,]? I assume you can cast directly to double[,] too?

user585968 Over a year ago

@Terry I agree with you, but to be fair to Joe, TcKs accepted answer above also is using object

Collectives™ on Stack Overflow

Reading from Excel (Range into multidimensional Array) C#

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related