Java – Formatting Excel cell as Dollar with Apache POI where the machine currency is not dollar

Couldn't you just flag the field as being a currency field, and let Excel figure out the right format to use? Or does Excel require you to specify the format to produce the relevant 'type'?

I would have suggested using java.text.NumberFormat.getCurrencyInstance() but there appears to be no way to get the actual format used by the instance from the object itself (unless tostring() returns it).

Off the top of my head I can't think of anything else sorry.

To get something like this...

...use this code (full running example):

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XlsComments {
    public static void main(String[] args) {
        try {
            new XlsComments().go();
        } catch (IOException ex) {
            Logger.getLogger(XlsComments.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void go() throws IOException {
        Workbook workbook = new XSSFWorkbook(); //create workbook
        Sheet sheet = workbook.createSheet(); //create sheet
        Cell cell = getOrCreateCell(sheet, 5, 2); //create cell
        cell.setCellValue("Cell with comment"); //write text to cell
        //add comment to cell C6 (row 5, column 2):
        addComment(workbook, sheet, 5, 2, "the author", "content of comment");
        //write to disc and close workbook:
        workbook.write(new FileOutputStream(new File("c:/temp/comments.xlsx")));
        workbook.close();
    }

    public Cell getOrCreateCell(Sheet sheet, int rowIdx, int colIdx) {
        Row row = sheet.getRow(rowIdx);
        if (row == null) {
            row = sheet.createRow(rowIdx);
        }

        Cell cell = row.getCell(colIdx);
        if (cell == null) {
            cell = row.createCell(colIdx);
        }

        return cell;
    }

    public void addComment(Workbook workbook, Sheet sheet, int rowIdx, int colIdx, String author, String commentText) {
        CreationHelper factory = workbook.getCreationHelper();
        //get an existing cell or create it otherwise:
        Cell cell = getOrCreateCell(sheet, rowIdx, colIdx);

        ClientAnchor anchor = factory.createClientAnchor();
        //i found it useful to show the comment box at the bottom right corner
        anchor.setCol1(cell.getColumnIndex() + 1); //the box of the comment starts at this given column...
        anchor.setCol2(cell.getColumnIndex() + 3); //...and ends at that given column
        anchor.setRow1(rowIdx + 1); //one row below the cell...
        anchor.setRow2(rowIdx + 5); //...and 4 rows high

        Drawing drawing = sheet.createDrawingPatriarch();
        Comment comment = drawing.createCellComment(anchor);
        //set the comment text and author
        comment.setString(factory.createRichTextString(commentText));
        comment.setAuthor(author);

        cell.setCellComment(comment);
    }
}

Please note the created file in c:/temp/comments.xlsx. The author is displayed in the lower left corner of the excel application when the cell is selected.