Currency Format Not Applied to Table Column When Populated via Payload in Aspose Cells

Aspose.Cells Product Family
1

While working with Aspose Cells for a table, we encountered an issue where applying a currency format to a table column doesn’t reflect correctly when values are populated via the payload. The cells display raw numbers without the currency symbol. However, if we manually click and edit any of these cells, the formatting updates and the currency symbol appears.

Interestingly, when the column values are populated using a dynamic formula, the currency formatting works as expected.

Due to this behavior, the currency symbol is missing, and any aggregation (like sum) on that column is also not working correctly.

Attached are the sample working code, template, payload JSON, and the generated output for reference.
AsposeCellFormattingIssue.zip (48.6 KB)

2

@geetha123

Cause

The issue you’re experiencing with the currency format not being applied to table columns populated via payload is likely due to how Aspose.Cells processes cell formatting when values are set programmatically. When values are added directly, the formatting may not automatically refresh, which is why you see raw numbers without the currency symbol. However, manual editing triggers a refresh, applying the formatting correctly.

Solution

To ensure that the currency format is applied correctly when populating the table via payload, you can explicitly set the format for the cells after populating them. This can be done by accessing the specific range of cells and applying the currency format programmatically.

Code

Here’s a sample code snippet to apply the currency format after populating the cells:

// Assuming 'worksheet' is your target worksheet and 'columnIndex' is the index of your currency column
int columnIndex = 1; // Example column index
int rowCount = worksheet.Cells.GetMaxDataRow() + 1; // Get the number of rows populated

// Apply currency format to the entire column
string currencyFormat = "$#,##0.00"; // Define your currency format
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
    worksheet.Cells[rowIndex, columnIndex].SetStyle(new Style() { Number = currencyFormat });
}

Next Steps

  1. Implement the code snippet above in your application after populating the table.
  2. Test to ensure that the currency format is applied correctly without needing manual edits.
  3. If the issue persists, check for any specific settings or configurations in your payload that might affect formatting.

If you have any further questions or need additional assistance, please feel free to reach out!

3

As per the requirement, we cannot explicitly apply formatting to specific cells or columns in the code, since the structure is generated dynamically based on the payload. Explicit formatting would require knowing the exact cell locations in advance, which is not feasible.

Additionally, it’s not limited to just currency formatting, various formatting types (e.g., date, percentage, etc.) might be applied to different cells. Manually detecting and applying each format in code is not practical. Instead, the expectation is that the formatting defined in the template should automatically be picked up and applied when the data is populated.

4

@geetha123,

Thanks for the sample app, template Excel file and output Excel file.

Apparently, I found “numbers stored as text” issue with those columns cells. So, you may try to update the markers in the template file and then give it a try to your scenario again:
i.e.,
&=Orders.Quantity
to:
&=Orders.Quantity(numeric)

and

&=Orders.UnitPrice
to:
&=Orders.UnitPrice(numeric)

In short, use “numeric” parameter, the data should be converted to numeric values so the formatting could be applied properly.