[Excel] How To Format Number In Excel

Reporting Design Cookbook Report Design
1

This recipe demonstrates how to format numbers in the Microsoft Excel-format output of a report template.

When we pass in a field of double, float or integer type into Excel, Excel ignores our formatting and treat it as a number without the formatting we specified eg #,###.00 etc.

To get around this, you need to append “’” in front of the field so that Excel will treat it as String and show the number with the formatting.

Steps taken:

  1. Create a report template (report.rml) using the datasource (data.ds).
  2. Add the following data fields (Literal) in the blank area of “Section Header”:number_double
    number_float
    number_integer
  3. Add the following data fields (Script) in the blank area of “Detail”:"’" + Format.formatNumber(number_double,"#,##0.00")
    “’” + Format.formatNumber(number_float,"#,##0.00")
    “’” + Format.formatNumber(number_integer,"#,##0.00")

To download the necessary files for this recipe, refer to the attached ZIP package.
HowToFormatNumberInExcel.zip (3.4 KB)