Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Wed Mar 25, 2015 7:38 pm

I am trying to export the excel sheet to a datatable so I can easily loop through each row and populate a custom class in VB. The problem is that the ExportDataTable is exporting the string values of the formulas of the cells and not the calculated value as I would expect. Is this a known issue/limitation of the ExportDataTable function or do I need to do something different? I am currently using the free version as we are evaluating this product to determine if we should purchase it going forward.

Sample of my call to ExportDataTable:

Dim book As New Workbook()
book.LoadFromFile("NewReportTemplate.xlsx")
book.CalculateAllValue()
Dim configSheet As Worksheet = book.Worksheets("Config")
Dim configTable As DataTable = configSheet.ExportDataTable()

almarciulionis
 
Posts: 5
Joined: Wed Mar 25, 2015 2:34 pm

Thu Mar 26, 2015 3:37 am

Hello,

Thanks for your inquiry.
You can refer to following codes:
Code: Select all
Dim workbook As New Workbook()
workbook.LoadFromFile("F:\sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim dt As DataTable = sheet.ExportDataTable(sheet.AllocatedRange, False, True)

If there are any questions, welcome to get it back to us.

Best Regards,
Betsy
E-iceblue support team
User avatar

Betsy
 
Posts: 802
Joined: Mon Jan 19, 2015 6:14 am

Fri Mar 27, 2015 8:28 am

Hello,

Has your issue been resolved? Could you please give us some feedback at your convenience?

Thanks,
Betsy
E-iceblue support team
User avatar

Betsy
 
Posts: 802
Joined: Mon Jan 19, 2015 6:14 am

Wed Apr 01, 2015 12:41 pm

Thank you for the reply and code sample. At this time I have found that the best way to solve my problem for my situation was to get at the cell specifically and calculate the formula and use the display text of the calculated value for the cells/rows that I need

almarciulionis
 
Posts: 5
Joined: Wed Mar 25, 2015 2:34 pm

Thu Apr 02, 2015 1:31 am

Hello,

I am glad to hear your issue has been resolved.
If there are any questions, welcome to get it back to us.

Best Regards,
Betsy
E-iceblue support team
User avatar

Betsy
 
Posts: 802
Joined: Mon Jan 19, 2015 6:14 am

Sat Jul 04, 2015 11:07 am

Betsy wrote:Hello,

Thanks for your inquiry.
You can refer to following codes:
Code: Select all
Dim workbook As New Workbook()
workbook.LoadFromFile("F:\sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim dt As DataTable = sheet.ExportDataTable(sheet.AllocatedRange, False, True)

If there are any questions, welcome to get it back to us.

Best Regards,
Betsy
E-iceblue support team


Its Working For me also thank you so much

Brinda_A
 
Posts: 1
Joined: Sat Jul 04, 2015 10:44 am

Mon Jul 06, 2015 6:01 am

Dear Brinda_A,

Thanks for your feedback. If there is any question, welcome to get it back to us.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy
 
Posts: 802
Joined: Mon Jan 19, 2015 6:14 am

Return to Spire.XLS