locked
Can ExportDataTable export values not formulas? RRS feed

  • Question

  • 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? 
    Thursday, April 30, 2015 2:56 AM

Answers

  • Hi UIyadam,

    According to the desciption, you want to export the worksheet to DataTable and keep the values instead of formula.

    As far as I know, we can loop the Worksheet.UserdRange to create a datatable and the the value instead of forumla via Range.Value.

    If you want to use third-party tools to achieve the goal, I suggest that you get support from Official support from the comment.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, May 7, 2015 11:34 AM

All replies

  • In which conditon and which languages u use? Recommend you use  this excel component.It can support C# and VB.NET.
    Thursday, April 30, 2015 3:04 AM
  • I use VB ,thanks.

    I tried free edition and use the following code but not work. Please help.

    Dim book As New Workbook()
    book.LoadFromFile("NewReportTemplate.xlsx")
    book.CalculateAllValue()
    Dim configSheet As Worksheet = book.Worksheets("Config")
    Dim configTable As DataTable = configSheet.ExportDataTable()
    Thursday, April 30, 2015 3:07 AM
  • you can change the code as follows:

    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)

    Thursday, April 30, 2015 3:08 AM
  • Hi UIyadam,

    According to the desciption, you want to export the worksheet to DataTable and keep the values instead of formula.

    As far as I know, we can loop the Worksheet.UserdRange to create a datatable and the the value instead of forumla via Range.Value.

    If you want to use third-party tools to achieve the goal, I suggest that you get support from Official support from the comment.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, May 7, 2015 11:34 AM