JExcelApi currency format on formula cell - file error

Question

Hi guys,

I'm writing a spreadsheet with JExcelApi.

I have two cells that I want to apply currency formatting to. From reading the API I discovered jxl.write.NumberFormat, which seems to do what I want. In my application I've written:

NumberFormat currency = new NumberFormat(NumberFormat.CURRENCY_DOLLAR);
WritableCellFormat currencyFormat = new WritableCellFormat(currency);

cells.add(new Formula(col, row, myFormula, currencyFormat));

col and row are the column and row to write to. myFormula is the formula to be written. There's one AVERAGE() and one SUM(), each of which is written to a different cell. cells is an ArrayList which is writtten to the spreadsheet like this:

for (WritableCell cell : cells) {
    ws.addCell(cell);
}

(There's a try/catch block around that, but it's irrelavent at the moment.)

EDIT: ws is a WritableSheet obtained with WritableWorkbook.getSheet(String name). Everything else added to ws is written without a problem :)

The file writes successfully, but when I open it I get the message "File error. Some number formats may have been lost." The cells I formatted above have not been formatted in the Excel file.

I'm using Microsoft Excel 2003 SP3 and JExcelApi 2.6.10.

Can anyone help me, please? Thanks in advance :)

Answer 1

It seems your currency format is not writen out. You can try to create the formula cell first,then format it to currency format. Hope this can help you.

Answer 2

you may find help here: http://www.docjar.com/html/api/jxl/demo/Write.java.html - use

WritableCellFormat cfi3 = new WritableCellFormat
                (NumberFormats.ACCOUNTING_FLOAT);

Answer 3

NumberFormat currencyFormat = new NumberFormat(NumberFormat.CURRENCY_DOLLAR + " ###,###.00", NumberFormat.COMPLEX_FORMAT); 
WritableCellFormat lCurrencyFormat = new WritableCellFormat(currencyFormat);
Number currency = new Number(j, cnt, Double.parseDouble(lStr), lCurrencyFormat);
lSheet.addCell(currency);

This will definately solve your purpose!