Adding New Cell Type in POI Utility

  • February 8, 2017
  • Posted by: Bharat Patel
  • Category: Uncategorized
No Comments

Recently, we have given ability in our one of the project to export data in excel sheet. For exporting data into excel we used POI Utility from Ben Nadel. In this package there are so many excel type conversion such as date, numeric, formula.. In exporting data into excel we have given formula to calculate percentage. For example (A3*D3)/100. All things work fine. Client needs to enter only value for calculating percentage amount.(eg. 2.5, 0.75).
But they suggest we will be entering number to calculate percentage like 2.5%, 0.75%. Now time to require implement new excel type and its PERCENTAGE(%). But there is no specification in POI UTILITY package for PERCENTAGE type. Percentage format is 0.00%.

It’s very simple to add new type in POI UTILITY. Just open cell.cfm page from POI UTILITY package. Add new cfparam for percentage type.

[code:cf]<cfparam

   name="ATTRIBUTES.PercentageFormat"
   type="string"
   default="0.00%"

   />[/code]

Add Switch case for percentage. Approx. line no. 415.

[code:cf]<cfcase value="Percentage">

<!—
We are going to assume that for percentage, we are going to
use the number formatting.
—>
<cfset VARIABLES.CellStyle.SetDataFormat(
VARIABLES.DocumentTag.DataFormat.GetBuiltinFormat(
JavaCast( "string", ATTRIBUTES.PercentageFormat )
)
) />

</cfcase>[/code]

In .cfm page just add type as Percentage.

[code:cf]<poi:cell type="Percentage" value="" />[/code]