How can I not Export "#DIV/0!" using ExportDataTable function

Aspose.Cells Product Family
1

I am using ExportDataTable function to read data from Excel file and there are some formulas in cells that could not be calculated and display error values like “#DIV/0!”. The export function reads these error cells like strings.

Is there a way I could skip such values while exporting or could get null values instead of such error strings in my resulting datatable?

2

@kanbee,

I am afraid, there is no automatic way to skip those error values. You may iterate those formula cells and replace the error values with null/empty or zero in code before exporting DataTable by yourselves.

3

@kanbee

For your convenience, we may consider to provide some kind of interface(apis) for ExportTableOptions to give you the ability of determining how to process values for every cell. If you do think such kind of solution is helpful, please let us know so we will investigate it further.

4

Yes, it would be great if you can provide such an option. Thank you.

5

@kanbee
Thank you for your feedback. We have added a new requirement that supports excluding specific data when exporting data to DataTable.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-54023

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.

6

Can you give me some kind of idea of how long it might take to add this feature and for it to be released?

thanks

7

@kanbee,

We have not yet analyzed your requested feature in detail. We will do so soon and update you or provide an ETA for it.

8

@kanbee,
We hope to support this requirement in the release version of Aspose.Cells 23.9. Once we have any new information, we will share it with you.

9

@kanbee

We added new apis for ExportTableOptions for your requirement:

        public virtual bool PreprocessExportedValue(int cellRow, int cellColumn, CellValue value):

You may extend ExportTableOptions and overwrite this method to perform the replacement for those types/values you need to replace with other types/values.

For your specific requirement about #DIV/0! error, the code example:

        ...
        DataTable dt = cells.ExportDataTable(startRow, startColum, rowCount, columnCount, new ExportTableOptions());
        ...
        class ExcludeErrorValue : ExportTableOptions
        {
            public override bool PreprocessExportedValue(int cellRow, int cellColumn, CellValue value)
            {
                if (value.Type == CellValueType.IsError && "#DIV/0!".Equals(value.Value))
                {
                    value.Type = CellValueType.IsNull;
                    return true;
                }
                return false;
            }
        }

The enhanced functions and new apis will be included into our next official version 23.9 which may be published in this week.

10

The issues you have found earlier (filed as CELLSNET-54023) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi