We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

ExportDataTable bringing in Excel formulas, not values

Thread ID:

Created:

Updated:

Platform:

Replies:

97254 Nov 11,2010 05:51 PM UTC Oct 14,2013 12:22 PM UTC Windows Forms 6
loading
Tags: XlsIO
Shawn Benson
Asked On November 11, 2010 05:51 PM UTC

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 [Syncfusion]
Replied On November 12, 2010 01:39 PM UTC

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
Replied On November 12, 2010 02:33 PM UTC

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
Replied On November 13, 2010 09:12 PM UTC

Works perfect!

Thanks,

Shawn.


Zakeer Ahmed S [Syncfusion]
Replied On November 15, 2010 04:23 AM UTC

Hi Shawn,

Thanks for your update.

Regards,
Zakeer Ahamed.S



Varun Doiphode
Replied On October 11, 2013 04:42 PM UTC

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 [Syncfusion]
Replied On October 14, 2013 12:22 PM UTC

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


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;