Export Excel worksheet with calculated values and format

Aspose.Cells Product Family
1

Hi,

I am trying to export formatted values ​​from a worksheet to a DataTable as they appear in Excel. I also want any existing formulas to be calculated before exporting. Here is my code:

public DataTable ExportDataTable(string file)
{
    var excel = new Aspose.Cells.Workbook(file);

    if (excel.Worksheets.Count == 0)
        throw new JoinWithExcelException($"Worksheet not found.");

    var ws = excel.Worksheets[0];
    
    var exportOptions = new ExportTableOptions();
    exportOptions.ExportColumnName = true;
    exportOptions.FormatStrategy = CellValueFormatStrategy.DisplayString;
    
    var totalRows = ws.Cells.MaxDataRow + 1;
    var totalColumns = ws.Cells.MaxDataColumn + 1;
    var dt = ws.Cells.ExportDataTable(0, 0, totalRows, totalColumns, exportOptions);

    return dt;
}

Join with Excel.zip (8.4 KB)

What I want for the first row is

Velislav Nikolov - Veli, Thursday, January 2, 2020, $100.00, 5.367% , -1,000.0, $ 15,000

instead of

Velislav Nikolov - Veli, 1/2/2020 12:00:00 AM, 100, 0.05367, -1000, 15000

I’ve tried with different FormatStrategies.

2

@profiler
Please refer to the following example code to export the string value of the cells to the DataTable. Please refer to the attachment. result.png (45.6 KB)

var excel = new Aspose.Cells.Workbook(filePath + "Join with Excel.xlsx");

excel.CalculateFormula();
var ws = excel.Worksheets[0];

var exportOptions = new ExportTableOptions();
exportOptions.ExportColumnName = true;
//add this line to export the string value of the cells
exportOptions.ExportAsString = true;
exportOptions.FormatStrategy = CellValueFormatStrategy.DisplayString;

var totalRows = ws.Cells.MaxDataRow + 1;
var totalColumns = ws.Cells.MaxDataColumn + 1;
var dt = ws.Cells.ExportDataTable(0, 0, totalRows, totalColumns, exportOptions);
Console.WriteLine(dt);

Hope helps a bit.

3

Thanks for the quick reply! Works for me :slight_smile:

4

@profiler
You are welcome！ If you have any issue , please feel free to contact us.