Problem with ExportDataTable

[Martin Nitschke]

I have the following line of code:
DataTable excelTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);

There are 241 cells used and the first row is the column names. My DataTable has 241 rows. The last row is blank. The ExportDataTable method correctly names my columns, but it incorrectly adds a blank row to the datatable.

I have Boolean values in the table as TRUE, FALSE and I need to make a new datatable with the correct columntype. So when looping through the datatable returned by ExportDataTable, I have an empty string, which doesn''t convert to Boolean.

I would suggest two things:
1. Eliminate the extra row when specifying column names
2. Have another overload whether or not to create a Boolean column type for boolean values in the excel data.

[Administrator]

Hi Martin, Sorry for the delay in getting you. The addition of an extra row cannot be due to specifying Column Names in ExcelExportDataTableOptions.ColumnNames. This may happen because of using ''sheet.UsedRange'' in the code DataTable excelTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); Because a blank cell containing some format can be regarded as UsedRange in XlsIO. Here is the code which will not add any extra rows in the datatable. sheet.ExportDataTable(int firstRow of the cell,int firstColumn of the cell,int maximum no.of rows to export,int maximum no.of columns to export,ExcelExportDataTableOptions.ColumnNames) I have filed a feature request(FeatureID:908) for exporting boolean datas to the datatable. please see the attachment and let me know if you have any questions. DataTableUser.zip Thanks, Bharath

[Martin Nitschke]

I have attached my sample excel. I am trying to say when I specify ColumnNames, then it should know that I want the UsedRange, but not the top row. This example gives me a used range of A1:E241, which is correct. That says 241 rows of Data, however, since I specified ColumnNames, that should tell the ExportTable method that the real data UsedRange is B1:E241, which is 240 rows.

Test27.zip

[Martin Nitschke]

Here is my code, I still get 241 rows in my DataTable

ExcelEngine excelEngine = new ExcelEngine();
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(openFileDialog.FileName);
IWorksheet sheet = workbook.Worksheets[0];
int maxRows = sheet.Rows.Length - 1;
int maxCols = sheet.Columns.Length;
DataTable excelTable = sheet.ExportDataTable(1, 1, maxRows, maxCols, ExcelExportDataTableOptions.ColumnNames);

[Administrator]

Hi Martin,

Sorry for the delay in getting you.

I am able to see the problem.

Please try our latest version 4.1.0.63 which has a fix for this problem.

ftp://syncpatch.syncfusion.com/EssentialStudio/4.1.0.63/syncfusionessentialstudiosetup.exe

Please take a look at the attachment and let me know if you need any assistance.

BestRegards,

Bharath