Excel Cell Format - XSSF Workbook

Question

This is my code so far, it gets data from a query then exports it into an Excel document:

<cfscript>
  oArray = CreateObject("java", "java.util.Arrays");
  workbook = CreateObject("java", "org.apache.poi.xssf.usermodel.XSSFWorkbook");
  workbook.init();

  myFont = workbook.createFont();
  myFont.setBoldweight(myFont.BOLDWEIGHT_BOLD);

  boldStyle = workbook.createCellStyle();
  boldStyle.setFont(myFont);
</cfscript>

<!--- Set up the headings for the Excel worksheet --->
  <cfscript>
    thisSheet = workbook.createSheet(JavaCast("string", 'invoices due'));
    rows = {};
    // we need to refer to these three rows later on
    rows[1] = thisSheet.createRow(0);
    rows[2] = thisSheet.createRow(1);
    rows[3] = thisSheet.createRow(2);
    rows[4] = thisSheet.createRow(3);

    //Report parameters explanation
    thisCell = rows[2].createCell(0, 1);
    thisCell.setCellValue(reportSum);

    // user column headings
    thisCell = rows[4].createCell(0, 1);
    thisCell.setCellValue('Value');
    thisCell.setCellStyle(boldStyle);
    thisCell = rows[4].createCell(1, 1);
    thisCell.setCellValue('Team');
    thisCell.setCellStyle(boldStyle);
    thisCell = rows[4].createCell(2, 1);
    thisCell.setCellValue('Manager');
    thisCell.setCellStyle(boldStyle);
  </cfscript>

<cfset row = 5>
<cfloop query="invoicesDue">

<cfscript>
   thisRow = thisSheet.createRow(JavaCast("int", row));
   thisCell = thisRow.createCell(0, 1);
   thisCell.setCellValue(HTMLEditFormat(invoicesDue.value));
   thisCell = thisRow.createCell(1, 1);
   thisCell.setCellValue(HTMLEditFormat(invoicesDue.ct_team));
   thisCell = thisRow.createCell(2, 1);
   thisCell.setCellValue(HTMLEditFormat(invoicesDue.manager));
   thisCell = thisRow.createCell(3, 1);  
 </cfscript>
 </cfloop>

<cfscript>
 // todo: change to datadir from getAppRoot
 outputFileName = "invoicesDue(withfundingsource)" & "_" & RandRange(00000,99999) & ".xlsx";
 fos = CreateObject("java", "java.io.FileOutputStream");
 fos.init(outputFilename);
 workbook.write(fos);
 fos.close();
</cfscript>

What I am trying to do is to format the column that is titled 'Value' to the data format 'Accounting' in Excel. I have done research but I am quite stuck.

Any ideas?

Have you looked in to SXXFDataFormat, https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFDataFormat.html This example uses HSSF but should be identical or very similar to SXXFDataFormat, both use the DataFormat interface http://npoi.codeplex.com/discussions/391336 — genericHCU, Jan 11 '13 at 13:38
@Travis - I do not know about the .net port, but in the java version 'Accounting' is one of the built in formats ie `44`. (If you dump them, most of the excel standards are there) — Leigh, Jan 11 '13 at 19:14
I saw in the vba there were several, didn't see it in the java IDs. Never used poi, honestly, just what I could find in some quick research. — genericHCU, Jan 11 '13 at 20:28
Ah, okay. FWIW, in the java version [BuilinFormats](http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html) class holds all the standard ones, "accounting" is `0x2c` (Real intuitive .. I know ;) — Leigh, Jan 11 '13 at 21:42

Leigh · Accepted Answer · 2013-01-11T17:10:41.240

3

What I would do is create a file in Excel. Apply the "Accounting" format to the one of the cells. Then check the cell's style/dataFormat in POI:

    dataFormatIndex = theAccountingCell.getCellStyle().getDataFormat();

In my tests, POI reported the "Accounting" format is BuiltInFormat 44. You can apply it like this:

    accountingStyle = workbook.createCellStyle();
    accountingStyle.setDataFormat( javacast("int", 44) ) ;

    someRow  = someSheet.createRow(0);
    someCell = someRow.createCell(0);
    someCell.setCellStyle( accountingStyle );  
    someCell.setCellValue( javacast("double", 123.75) );

To view all of the BuiltInFormat values:

 formats = createObject("java", "org.apache.poi.ss.usermodel.BuiltinFormats");
 writeDump(formats.getAll());

edited Jan 11 '13 at 17:10

answered Jan 11 '13 at 17:02

Leigh

28,765
10
55
103

1

I can't seem to view the formats. How do I find out the equivalent for the Excel 'Number' format? – Alias Jan 15 '13 at 14:19
Did you run the last two lines of code above? It dumps all of the available formats. `Number` is a general category with a lot of "sub" formats like 6,7,8, ... – Leigh Jan 15 '13 at 16:58
In Java you can use: `for(String s : BuiltinFormats.getAll()) {System.out.println(s);}` – Ron Oct 08 '13 at 07:38

Excel Cell Format - XSSF Workbook

1 Answers1