Parse excel data and create multidimensional array

Question

I have an excel file which could would have contents as shown below. It has dynamic rows and columns. I can read from the excel fine. I want to create a custom jarray and return to my UI from this.

Example1
id      lotno       stateno
1       22   
2       1111     
5       99   
7       3           222
55      0           123
21                  2
44                  55

Example2
id      lotno       stateno  bldgno
1       22   
2       1111     
5       99   
7       3           222
55      0           123
21                  2
44                  55
1       23          03        9
55      33          12        2 

If you see example 1, it has 3 sets: set1: id, lotno; set2: id, lotno, stateno; set3: id, stateno. Example 2 has 4: set1: id, lotno; set2: id, lotno, stateno; set3:id, stateno; set3: id, lotno, stateno, bldgno These sets can all change based on the columns of the excel. Above I have given 2 examples with 3 and 4 columns.

Now I want to parse the sets separately and return an jarray back to my UI. How can I parse the excel data. So with simple excel I am using the following code to parse (Using epplus)

 using (ExcelPackage package = new ExcelPackage(file.OpenReadStream()))
 {
  int rowCount = worksheet.Dimension.End.Row;
  int colCount = worksheet.Dimension.End.Column;

  for (int row = 1; row <= rowCount; row++)
  {
      for (int col = 1; col <= colCount; col++)
      {
        var rowValue = worksheet.Cells[row, col].Value;                 
      } 
  } 
}

If someone can share how can I make individual multidimentional arrays from my original example, then I an make my Jarray myself. So what I am expecting the result as 3 sets for example 1:

 id     lotno       
 1      22   
 2      1111     
 5      99

 id     lotno       stateno
 7      3           222
 55     0           123

 id     stateno
 21     2
 44     55

Any inputs are appreciated.

Answer 1

If you don't have that many columns and know how many I would start with looping the rows twice. Once for catching where each set starts and ends. Then I would loop the rows again and collect the data.
When that works - all test are green, then I would refine to make it nicer.

Since you know the number of columns and they are fewer than 32 give them a number like 1, 2, 4, 8, 16... Let's call it Alpha. Binary that means up to 32 bits in a row. If you, for each row add the Alphas together for every column with a value in it you get a single int telling you which column is populated in every row. Store that value for every row.

Now time for loop 2. Loop the rows again. As long as the Alpha is the same you are in the same set. When the Alpha changes, create a new set and populate it.

It will be some hits and misses while you play but the problem is not too hard.
Then when you decide to have lots and lots of columns... you choose another method for getting an Alpha. And when you get lots and lots of rows... you choose yet a method. But I suggest you start approximately as I describted.