The behavior of Cells.ExportDataTable() methods changed

Aspose.Cells Product Family
1

In Aspose.Cells for .NET 24.7 Release Notes there is a Changes the behavior of Cells.ExportDataTable() methods. The change of behavior is causing issues for us. Is there a way for us to continue exporting a numeric cell as a raw literal of its value without formatting? We would like to continue having percentages exported as decimals instead of a percentage (“.1” instead of “10%”) Setting options CellValueFormatStategy.None gives the desired result for percentages, but changes how other types (like dates) are exported.

2

@achittick

In Aspose.Cells for .NET 24.7, the behavior of the Cells.ExportDataTable() method was indeed changed, which can affect how numeric values, including percentages and dates, are exported. If you want to continue exporting numeric cells as raw literals without formatting, while also ensuring that percentages are exported as decimals, you can implement a custom solution.

Here’s a general approach you can take:

  1. Use CellValueFormatStrategy: You can set the CellValueFormatStrategy to None to get the raw values, but as you mentioned, this affects other types like dates. Instead, you can create a custom export method that handles the formatting for you.

  2. Custom Export Logic: You can iterate through the cells and manually handle the export logic based on the cell type. For numeric values, you can check if the cell is a percentage and convert it accordingly. For dates, you can format them as needed.

Here’s a sample code snippet to illustrate this approach:

using Aspose.Cells;
using System;
using System.Data;

public DataTable ExportCustomDataTable(Worksheet worksheet)
{
    DataTable dataTable = new DataTable();
    int rowCount = worksheet.Cells.MaxDataRow + 1;
    int colCount = worksheet.Cells.MaxDataColumn + 1;

    for (int col = 0; col < colCount; col++)
    {
        dataTable.Columns.Add(worksheet.Cells[0, col].StringValue);
    }

    for (int row = 0; row < rowCount; row++)
    {
        DataRow dataRow = dataTable.NewRow();
        for (int col = 0; col < colCount; col++)
        {
            Cell cell = worksheet.Cells[row, col];
            if (cell.Type == CellValueType.IsNumeric)
            {
                // Check if the cell is a percentage
                if (cell.GetStyle().Number == 6) // 6 is the number format for percentage
                {
                    dataRow[col] = cell.DoubleValue; // Export as decimal
                }
                else
                {
                    dataRow[col] = cell.DoubleValue; // Export as raw numeric value
                }
            }
            else if (cell.Type == CellValueType.IsDateTime)
            {
                dataRow[col] = cell.DateTimeValue; // Export date as DateTime
            }
            else
            {
                dataRow[col] = cell.StringValue; // Export as string for other types
            }
        }
        dataTable.Rows.Add(dataRow);
    }

    return dataTable;
}

Explanation:

  • This code creates a DataTable and populates it by iterating through the cells of the specified worksheet.
  • It checks the type of each cell and handles numeric values, percentages, and dates accordingly.
  • For percentages, it exports the raw decimal value instead of the formatted percentage string.
  • For dates, it exports them as DateTime objects.

Conclusion:

By implementing a custom export method, you can maintain control over how different cell types are exported, ensuring that numeric values and percentages are handled according to your requirements without affecting other data types. Adjust the logic as needed based on your specific use case.

3

@achittick,

I tested your scenario/case using the following sample code with latest version/fix, i.e., Aspose.Cells for .NET v25.9 (Releases | NuGet) along with the template Excel file (attached), it works fine and I get your expected results. It exports “.1” instead of “10%”.
e.g.,
Sample code:

Workbook workbook = new Aspose.Cells.Workbook("e:\\test2\\Bk_test1.xlsx");

Worksheet worksheet = workbook.Worksheets[0];

var exportOptions = new ExportTableOptions();
exportOptions.ExportColumnName = false;
exportOptions.ExportAsString = true;
exportOptions.FormatStrategy = CellValueFormatStrategy.None;

var totalRows = worksheet.Cells.MaxDataRow + 1;
var totalColumns = worksheet.Cells.MaxDataColumn + 1;
System.Data.DataTable dt = worksheet.Cells.ExportDataTable(0, 0, totalRows, totalColumns, exportOptions);
foreach(System.Data.DataRow dataRow in dt.Rows)
{
   foreach(var item in dataRow.ItemArray)
   {
       Console.WriteLine(item);
   }
}

Bk_test1.zip (6.0 KB)
output:

1
0.10
2
0.20
3
0.30
4
0.40
5
0.50

Kindly try the latest version/fix (Aspose.Cells for .NET v25.9). If the issue persists, we would appreciate it if you could share your template Excel file (please ensure the file is zipped before attaching) along with the sample code you are using. We will review the matter promptly.

Please note that we do not evaluate issues based on older versions, nor do we provide fixes for them. All updates and fixes are implemented in the latest API set only.

4

Thanks,
I agree that setting exportOptions.FormatStrategy = CellValueFormatStrategy.None provides the expected results for percentages, but it also introduces issues with dates (11/30/2014 becomes 41973).
Is there a way for the CellValueFormatStrategy.None to only be applied to certain types or another way to avoid this?

5

@achittick

You may try custom ExportTableOptions to process the cell value while exporting. Code example like:

        private class CustomExport : ExportTableOptions
        {
            private readonly Cells cells;
            internal CustomExport(Cells cells)
            {
                this.cells = cells;
            }

            public override bool PreprocessExportedValue(int cellRow, int cellColumn, CellValue value)
            {
                if (value.Type == CellValueType.IsNull)
                {
                    return false;
                }
                Cell cell = cells[cellRow, cellColumn];
                if (value.Type == CellValueType.IsNumeric)
                {
                    value.Type = CellValueType.IsString;
                    value.Value = cell.DoubleValue.ToString(CultureInfo.InvariantCulture);
                }
                else
                {
                    value.Type = CellValueType.IsString;
                    value.Value = cell.StringValue;
                }
                return true;
            }
        }

...
            var exportOptions = new CustomExport(cells);
            System.Data.DataTable dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1,
                cells.MaxDataColumn + 1, exportOptions);
...

When ExportAsString is enabled, the custom process will be ignored currently. We will improve the engine to support custom process for this situation. Then the implementation of custom export options will become easier.

We have opened the following new ticket in our internal issue tracking system for this enhancement. The fix will be delivered according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-59052

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

1 Like
6

@achittick,

This is to inform you that your issue (Ticket ID: “CELLSNET-59052”) has been resolved. The fix/enhancement will be included in the upcoming release (Aspose.Cells v25.10) that we plan to release in the first half of October 2025. You will be notified in this thread once the new version is published.

1 Like