Importing data from excel

[zielonywojownik]

Hello,
When importing some of excel files I'm receiving the following error:

"Row number or column number cannot be zero"

My code:

public DataSet ImportData(ExcelImportOptions options)
{
if (options != null && !String.IsNullOrEmpty(options.FilePath))
{
DataSet set = new DataSet();
Workbook book = new Workbook();
book.Open(options.FilePath);
foreach (Worksheet worksheet in book.Worksheets)
{
if (worksheet.Cells.Count > 0)
{
DataTable table = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.Rows.Count, worksheet.Cells.Columns.Count, true);
table.TableName = worksheet.Name;
set.Tables.Add(table);
}
}
return set;
}
else return null;
}

It seems like it cannot recognize columns at all. The problematic example excel file is attached.

Any idea what could be the problem?

Regards

Tomasz

[Amjad_Sahi]

Hi Tomasz,

There is an error in your code. Please use Cells.MaxDataRow and Cells.MaxDataColumn instead of Cells.Rows.Count and Cells.Columns.Count attributes. Well, Cells.Rows/Columns.Count property would give you rows/columns count excluding the rows/columns with standard (default) settings / formatting. For your information, the Rows.Count and Columns.Count are mainly useful if you want to retrieve the number of rows / columns which are initialized / used in the worksheet. Please use Cells.MaxColumn and Cells.MaxRow if you need to get the farthest cell with data or formatting and style applied and use Cells.MaxDataColumn and Cells.MaxDataRow attributes to get the farthest column and row indexes (zero based) containing data only.

You may change you code to, e.g

DataTable table =
worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxDataRow+1,
worksheet.Cells.MaxDataColumn+1, true);
table.TableName = worksheet.Name;
set.Tables.Add(table);


Thank you.

[zielonywojownik]

That did the trick - thank you.