ExportDataTable bringing in Excel formulas, not values

[Shawn Benson]

When using the ExportDataTable method, if I specify something like the following

_data = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);

I get the column names correctly from the spreadsheet, however, the formulas are written out to the table, not the values.

If I specify

_data = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ComputedFormulaValues);

The formula values come in correctly, but now the column names are part of the dataset and the table column names are generic.

Can I specify more than one option? Is there a work around?

code snippet

DataTable _data = new DataTable();

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2007;

IWorkbook workbook = application.Workbooks.Open(_fileName.FullName, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
_data = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ComputedFormulaValues);

workbook.Close();
excelEngine.Dispose();

[Zakeer Ahmed S]

Hi Shawn,

Thanks for using Syncfusion products.

We can able to understand your requirement from the previous update. In order to

get both the column names and calculated formula value in the exported excel

sheet you need to select both the options(ColumnNames , ComputedFormulaValues)

available in the “ExcelExportDataTableOptions”. I have given below the modified

sample ,please try the sample and let us know if this helps you.

data = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ComputedFormulaValues | ExcelExportDataTableOptions.ColumnNames);


DataSample-318158889.zip

Let me know if you have any queries
Regards,
Zakeer Ahamed. S




            

[Shawn Benson]

Thank you for the response. I understand I need to use both options, but I do not know the syntax and the sample only uses ExcelExportDataTableOptions.ComputedFormulaValues. How do I specify columns and computed values?

[Shawn Benson]

Works perfect!

Thanks,

Shawn.

[Zakeer Ahmed S]

Hi Shawn,

Thanks for your update.

Regards,
Zakeer Ahamed.S

[Varun Doiphode]

Zakeer,

I have a column in excel which has been currency formatted. So the number 

10000   appear like ==>  $10,000.00

How can we read it as is. 

[Sridhar]

Hi Varun Doiphode,

Currently, we do not have support for exporting number formats in ExportDataTable method. Also, we could see the reported query is similar to the query updated in the incident 114304. So, we request you to follow up with the incident 114304 for further more updates.

Thanks,
Sridhar.S