Apache POI Formatting Question

Can you show us the code that you used, specifically the POI code?

Ben nadel has some good post  about CF and poi:

http://www.bennadel.com/blog/531-First-Attempt-At-Excel-Document-Formatting-Hooks-Using-ColdFusion-And-POI.htm

The question (as was shown above) is:

How can I preserve the styles in the template?

Thanks!

Here are two snippets of the POI code mixed with Cold Fusion:

===========================================================================
<cfscript>
      fileIn = createObject("java","java.io.FileInputStream").init("C:\template.xls");
      fs = createObject("java","org.apache.poi.poifs.filesystem.POIFSFileSystem").init(fileIn);
      wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fs);
      format = wb.createDataFormat();
      sheet = wb.getSheetAt(0);
      currencyStyle = wb.createCellStyle();
      currencyStyle.setDataFormat(format.getFormat("$##,####0.00;[Red]$##,####0.00"));
</cfscript>
===========================================================================

...

===========================================================================
<cfquery datasource="RMSDev" name="getData">
...
</cfquery>

<cfset colHeaderNames = ArrayToList(getData.getColumnList()) />

<cfset oCnt = 0>
<cfloop query="getData">
      <cfset row = sheet.createRow(#evaluate(oCnt)#)/>
      <cfset iCnt = 0>
      <cfloop index="x" list="#colHeaderNames#">
            <cfset cell = row.createCell(#evaluate(iCnt)#)/>

            <cfif left(x,2) eq "N_">                  
                  <cfif #evaluate("getData." & x)# neq "">
                        <cfset cell.setCellValue(#javacast("float",evaluate("getData." & x))#)>
                        <cfif left(x,4) eq "N_C_">
                              <cfset cell.setCellStyle(currencyStyle)>
                        </cfif>
                  <cfelse>
                        <cfset cell.setCellValue("")>
                  </cfif>
            <cfelse>
                  <cfset cell.setCellValue(#evaluate("getData." & x)#)>
            </cfif>
            <cfset iCnt = iCnt + 1>
      </cfloop>
      <cfset oCnt = oCnt + 1>
</cfloop>

<!--- Write the output to a file --->
<cfset fileOut = createObject("java","java.io.FileOutputStream").init("D:\report.xls")/>
<cfset wb.write(fileOut)/>
<cfset  fileOut.close()/>
===========================================================================

I have tried using getCellStyle() but I cannot seem to get that to work.

You can also use the queryName.columnList variable instead of the undocumented method: queryName.getColumnList() .  

Here's an array based version:

<cfset colHeaderArray = listToArray(getData.columnList) />
<cfloop query="getData">
      <cfset row = sheet.createRow(javacast("int", currentRow-1))/>
      <cfloop from="1" to="#ArrayLen(colHeaderArray)#" index="colNum">
                    <cfset colName  = colHeaderArray[colNum]>
                    <cfset colValue = getData[colHeaderArray[colNum]][currentRow]>
            <cfset cell       = row.createCell(javacast("int", colNum-1))/>

            <cfif left(colName, 2) eq "N_">                  
                  <cfif colValue neq "">
                        <cfset cell.setCellValue(javacast("float", colValue))>
                        <cfif left(colName,4) eq "N_C_">
                              <cfset cell.setCellStyle(currencyStyle)>
                        </cfif>
                  <cfelse>
                        <cfset cell.setCellValue("")>
                  </cfif>
            <cfelse>
                  <cfset cell.setCellValue(colValue)>
            </cfif>
      </cfloop>
</cfloop>