Skip certain Rows and Columns while Parsing XLS

Question

I'm using the following code to parse the XLS file using ExcelDataReader. I would like to exclude the first three rows, first two columns followed by any columns that are after 9.

//create the reader
var reader = ExcelReaderFactory.CreateReader(stream);
var result = reader.AsDataSet();

//remove the first 3 rows
DataRowCollection dt = result.Tables[0].Rows;
dt.RemoveAt(0);
dt.RemoveAt(1);
dt.RemoveAt(2);
//exclude the column 1 and2 and any columns after 9
for (int columnNumber = 2; columnNumber < 8; columnNumber++)
{
    foreach (DataRow dr in dt)
    {
        Debug.Log(dr[columnNumber].ToString());
        msg += dr[columnNumber].ToString();
    }
}

Unfortunately, it does not skip the rows and columns as expected. How do I skip specific columns and rows using excelDataReader?

score 0 · Accepted Answer · answered Feb 11 '20 at 11:14

You are doing the following

dt.RemoveAt(0);
dt.RemoveAt(1);
dt.RemoveAt(2);

When the first line executes, the rows are reindexed with the 1 becoming 0, 2 becoming 1 and so on.

When the second line executes you have now removed the line that was position 2 originally. The rows are again reindexed.

When the third line executes you are then again removing an incorrect row.

As a result, when this process completes, it will have removed the lines that were originally positioned at 0, 2, and 4.

Change the code to remove the correct lines, or skip three lines with linq or a for loop.

Sample using for loop (not tested).

//create the reader
var reader = ExcelReaderFactory.CreateReader(stream);
var result = reader.AsDataSet();

DataRowCollection dt = result.Tables[0].Rows;
//ignore the first 3 rows
for(int dataRowCount = 3; dataRowCount < dt.Count; dataRowCount++)
{
    //exclude the column 1 and 2 and any columns after 9
    for (int columnNumber = 2; columnNumber < 8; columnNumber++)
    {
        Debug.Log(dr[dataRowCount][columnNumber].ToString());
        msg += dr[dataRowCount][columnNumber].ToString();
    }
}

Skip certain Rows and Columns while Parsing XLS

1 Answers1