Excel Export Related issues

[Renard]

Hi

When using an export, if both numerical columns have values, I can perform formula calculations on the exported Excel file.

However, when I changed the value of one of the numerical columns to empty and exported it to Excel again, the formula calculation could not be used.

 

This causes the Excel I exported to not be able to perform formula calculations, which is a problem. Hope to get a solution.

thanks

[Vikram Sundararajan]

Hi Renard,

Greetings from Syncfusion support,

The issue you're encountering, where formulas in Excel cannot be calculated when one of the numerical columns is empty, occurs because empty cells in the exported Excel file may be treated as non-numeric values (such as text). This causes Excel to throw a VALUE! error, preventing the formula from executing.

To resolve this, you can handle empty cells during the export process using the excelQueryCellInfo event in Syncfusion EJ2 Grid. By ensuring that empty cells are treated as 0, the formula will be able to calculate correctly. Here's how you can implement this:

   excelQueryCellInfo(args){

        if (

            args.column.field === 'ProductID' ||

            args.column.field === 'UnitsInStock' ||

            args.column.field === 'UnitPrice'

          ) {

            // If the cell is empty, treat it as 0

            if (args.value === null || args.value === undefined || args.value === '') {

              args.value = 0;

            }

          }

    }

This solution ensures that when exporting the Excel file, empty cells in your specified columns are replaced with 0, allowing the formula to perform calculations without any issues.

Sample: https://stackblitz.com/edit/angular-jqzayq-d4bpgj?file=src%2Fapp.component.html,src%2Fapp.component.ts

Please get back us if you need further assistance.

Regards,

Vikram S

[Renard]

Hi   Vikram     

Because the grid is currently showing as empty, the Excel file I need to export is also showing as empty. Because some column values have different meanings represented by null and 0. Is there any way to handle this situation.

thanks

[Sivaranjani Rajasekaran]

Hi Renard,

Thank you for your inquiry. 

By default, if the grid data is empty and exported to Excel, the empty record is treated as text. When you try to add text and a number, a format collision occurs, resulting in the #VALUE! error when a formula is used to perform any mathematical operation.

To avoid this issue, you should define empty records as 0 before exporting the grid to Excel using the excelQueryCellInfo event. Alternatively, when applying the formula, you can convert null values to 0 so that the operation works properly.

Option 1: Change null values to '0' during export

Please refer to the code below for more information.

Code example : 

 excelQueryCellInfo(args){

        if (

            args.column.field === 'ProductID' ||

            args.column.field === 'UnitsInStock' ||

            args.column.field === 'UnitPrice'

          ) {

            // If the cell is empty, treat it as 0

            if (args.value === null || args.value === undefined || args.value === '') {

              args.value = 0;

            }

          }

    }

Option 2: Modify the formula to cast null values to '0'

Use a formula to treat null or blank cells as 0:

For Example : 

[ =IF(ISNUMBER(A1), A1, 0) + IF(ISNUMBER(B1), B1, 0) ]

Output : 

This should resolve the issue. Please get back to us if you have any questions.

Regards,

Sivaranjani R.