LINQ To Excel: How to load 1023x10 array of numbers in Excel into jagged array int[][] ??

Question

Hello,

This question is related to using LINQ to Excel component.

I have a variable in code defined as jagged array of int:

private int[][] array1; // Jagged array

How can I load the data from excel into such array?

var excel = new ExcelQueryFactory(pathToExcelFile);
// The range in Excel is B3:K1026
// Is this the right thing to do?
ExcelQueryable<int[][]> result = excel.WorksheetRange<int[][]>("B3", "K1026");

How do I populate the array1?

Thanks.

Answer 1

Hi Sigourney_Weaver;

Does the work sheet have column headers for each column? 

Do you have a sample excel file that you can upload?

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 2

Hi Fernando,

Yes, columns have simple headers from 1 to 10.

Here is the file: SampleFile

I changed the range to keep it simple A2:J1025

Answer 3

Hi Sigourney_Weaver;

The LinqToExcel returns a collection of ExcelQueryable<LinqToExcel.Row>. So in order to fill an array of array you will need to enumerate the results and then enumerate each row to get the cell values and place into the array. The code snippet below should give you an idea how it can be done.

// Instanciate the Excel object
var excel = new ExcelQueryFactory( "C:/Working Directory/Downloads/FAECalculationArrays.xls" );
// Create the query to get the data
ExcelQueryable<LinqToExcel.Row> results = excel.WorksheetRange( "A2", "J1025", "PLANYEARARRAY" );
// Create an array of array NOT jaggard
int [][] resultsAsArrey = new int[ results.Count( ) ][ ];
// Used to index array of array
int rowIndex = 0;

foreach( LinqToExcel.Row row in results )
{
	// Used to index array of array
    int cellIndex = 0;
    // Create a new array for the element of the array
    resultsAsArrey[rowIndex] = new int[10];
	// Fill the array
    foreach( LinqToExcel.Cell cell in row )
    {
        resultsAsArrey[ rowIndex ][ cellIndex ] = cell.Cast<int>( );
        // Increment to the next cell in the row
        cellIndex++;
    }
    // Increment to the next row in the rows
    rowIndex++;
}

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 4

That's great !!

Works like a charm.

Thank you Fernando.

Answer 5

  

Not a problem, glad I was able to help.

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".