I seem to be getting some strange behavior trying to get data out of a Worksheet. I’ve included some sample code and the file I’ve been using so you can see if you can recreate what I see.
When I open the Workbook I see sheet.Cells.Rows.Count as 6 (which is what I see in the data file) and sheet.Cells.Columns.Count as 4 (there are 6 columns in the data file). The Cells.MaxColumn value is 5, which would match the data file (since the columns are zero indexed). If I then foreach through Cells.Columns it seems to skip columns 3 and 4. However if I loop through the Cells numerically and adjust the column count from before, all the data seems to be there. When I ExportDataTableAsString, it seems to pick up the missing columns from before.
I would assume that Cells.MaxColumn and Cells.Columns.Count would be equivalent (except for the indexing). How can they give different values? How are these actually calculated?
Why are some of the columns in the data file not showing up in Cells.Columns but are in the Cells array? Am I doing something wrong with how I am looping through the columns?
Thanks for you help with this.
Hi,
Well, Cells.Columns.Count gives you the total of those columns which are initialized. For your info, the Cells.Columns.Count would not include the columns with default width (8.43), since the 3rd and the 4th columns have default widths so these will be excluded for Cells.Columns.Count. For exporting data to datatabe using ExportDataTableAsString(), your code will be processed as:
DataTable dataTable = sheet.Cells.ExportDataTableAsString(0, 0, 6, 4); so it will pick first four columns (including the missing 3rd and 4th columns but excluding the 5th ad 6th).
Now come to the point. You should always use Cells.MaxDataRow and Cells.MaxDataColumns while reading or writing data into the cells, because these method would give your farthest row/column indexes that has data in any of its cells irrespective initialization or row/column standard widths limitations that are with Rows/Columns.Count attribute(s).
So,kindly change your code to, e.g
Console.WriteLine("Data from the worksheet-->for loop");
for (int i = 0; i <= sheet.Cells.MaxDataRow; i++)
{
for (int j = 0; j <= sheet.Cells.MaxDataColumn; j++)
{
Console.WriteLine(String.Format("Sheet-->Value: {0}", sheet.Cells[i, j].Value));
}
}
Also, change your code of exporting a datatable:
DataTable dataTable = sheet.Cells.ExportDataTableAsString(0, 0, sheet.Cells.MaxDataRow+1, sheet.Cells.MaxDataColumn+1);
Hope, you understand now.
Thank you.
Thank you. The changes that you suggested work fine.
I would like to understand what you mean by initialized. Is that an Excel term / state or is it related to Aspose? What does it take to “initialize” a cell or column?
From what I understand we should never use Cells.Columns if we don’t want to miss data in the worksheet. Would we fact the same problem of missing valid data if I were to do a foreach over every Cell in a Worksheet?
Thanks for your help with this. I just want to make sure that any algorithms we design for parsing excel spreadsheets don’t end up missing columns because they aren’t initialized.
Hi,
Thank you for considering Aspose.
Well, initialize means if the column properties (like width etc.) are not changed explicitly, the column object will not be created for that particular column and Cells.Column will not include that column into the collection, no matter how much data is present in the cells of that column. Same goes for template file, when we load a template file using Aspose.Cells, if the column is present in the file with default properties, it will not be included in the Cells.Columns collection.
For the issue regarding use of Cell.Columns, I think as Amjad said you should use the Cells.MaxDataRow & Cells.MaxDataColumn to get the complete data as per your requirement.
Please do let us know if you need any further assistance, we will be happy to help you out.
Thank You & Best Regards,
I think I understand how your functions work now. Thank you.