ExcelTable's ToDataTable method failed to retain the error value string

[minren118]

EPPlus usage

Commercial use (I have a commercial license)

Environment

Windows

Epplus version

7.4

Spreadsheet application

Excel

Description

I want to convert ExcelTable or Range to a memory DataTable with its original contents and error text as the error value, but that doesn't work now, the error value just becomes blank.

I tried to convert it to an error string manually in the column configuration, but it didn't work.

I think the priority and column configuration is more reasonable, so that the DataTable can get richer information, the current error value in addition to the error and ignore null, may lose some information with the real world use.

                var worksheet = package.Workbook.Worksheets["Sheet1"];  // 获取第一个工作表
               
                var excelTable = worksheet.Tables["表1"];

                var cell = worksheet.Cells["C5"];

                var iserror = cell.Value is ExcelErrorValue;
                ToDataTableOptions toDataTableOptions = ToDataTableOptions.Create();
                for (int i = 0; i < excelTable.Columns.Count; i++)
                {
                    toDataTableOptions.Mappings.Add(i, excelTable.Columns[i].Name, typeof(string), true, s => s is ExcelErrorValue ? s.ToString() : s);
                }

                var dt = excelTable.ToDataTable(toDataTableOptions);

[minren118]

I use a loop to turn the error value into text, and then execute the following code to complete my expected effect, but the performance is poor, if you can judge once, it is best to set up in the column configuration.

Error values are not very sensitive to Excel, and it is acceptable for some cells in a column or row of data to have error values, especially during formula operations. The direct conversion to empty, and the real cell empty both confuse, but cause ambiguity.

                foreach (var item in worksheet.Cells[excelTable.Range.Address].Where(s => s.Value is ExcelErrorValue))
                {
                    item.Value = item.Value.ToString();
                }

                var cell = worksheet.Cells["C5"];

                var iserror = cell.Value is ExcelErrorValue;
                ToDataTableOptions toDataTableOptions = ToDataTableOptions.Create();
                for (int i = 0; i < excelTable.Columns.Count; i++)
                {
                    toDataTableOptions.Mappings.Add(i, excelTable.Columns[i].Name, typeof(object), true);
                }

                var dt = excelTable.ToDataTable(toDataTableOptions);

[OssianEPPlus]

This is by design as the typical use-case for ToDataTable is to use it for database operations and typically the DatatableColumn has a specific datatype e.g. Int rather than Object. These error values are rarely relevant outside of excel.

We've discussed it internally and will be adding the possibility to add your own mapping for this as a minor feature instead of adding it to the ExcelErrorParsingStrategy enum.

Next version of Epplus you should be able to do something like:

var sheet = package.Workbook.Worksheets["Sheet1"];
var table = sheet.Tables["Table1"];

ToDataTableOptions dtOptions = ToDataTableOptions.Create();
for (int i = 0; i < table.Columns.Count; i++)
{
    dtOptions.Mappings.Add(i, table.Columns[i].Name, typeof(object), false, cellVal => {
        if (cellVal is ExcelErrorValue eev)
        {
            return cellVal.ToString();
            // return eev.Type;
        }
        return cellVal;
    });
}

var dt = table.ToDataTable(dtOptions);
var rows = dt.Rows;

var naError1 = rows[3][2];
var naError2 = rows[3][3];

This should also allow for mapping/transforming the error values to whatever column value might be appropriate.