Adding a comment to a cell in an Excel file with Java using jxl library

Question

I am trying to add a comment to a cell in Excel. I am using jxl library to do that:

   cell = sheet.getWritableCell(1, 2); // cols, rows
   WritableCellFeatures wcf = cell.getWritableCellFeatures();
   wcf.setComment("comment2");

The last line returns: Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException. Despite many attempts I can't fix it. Help will be appreciated. Thank you.

--EDIT--
This is the addNumber method after modifications:

private static void addNumber(WritableSheet sheet, int column, int row,
        double d) throws WriteException, RowsExceededException {

    Number number;
    number = new Number(column, row, d, timesStandard);

    //sheet.addCell(number); // need to add the cell first

    if (user wants to add a comment) {
        WritableCellFeatures wcf = new WritableCellFeatures();
        wcf.setComment("the comment");
        number.setCellFeatures(wcf);
    }

    //sheet.addCell(number); // but may need to add the cell with comments as well
}

`cell.getWritableCellFeatures()` return null because the cell has no feature. Try using `WritableCellFeatures wcf = new WritableCellFeatures(); wcf.setComment("comment"); cell.setCellFeatures(wcf);` — alain.janinm, Apr 14 '12 at 19:49
In your if statement can you add `Cell cell = sheet.getWritableCell(column, row);` and then replace `number.setCellFeatures(wcf);` with `cell.setCellFeatures(wcf);` — alain.janinm, Apr 14 '12 at 21:11
I added `WritableCell cell = sheet.getWritableCell(column, row);` and replaced `number` with `cell` but the comment is not there — Hurdler, Apr 14 '12 at 21:20
Then you replace `sheet.addCell(number);` with `sheet.addCell(cell);`? — alain.janinm, Apr 14 '12 at 21:26

Torious · Accepted Answer · 2012-04-14T21:20:16.590

Have you previously added a cell at that location? The problem is that you can't set cell features on an EmptyCell and it will always return null as its cell features.

If you add a cell first, it works (try/catch omitted for clarity), as shown by the code below. Note that it also sets a WritableCellFeatures on the new Label cell first, since initially, cell features are always null.

            WritableWorkbook book = Workbook.createWorkbook(new File("output.xls"));
            WritableSheet sheet = book.createSheet("Some Sheet", 0);

            Label label = new Label(1, 2, "Some label"); 
            sheet.addCell(label); // add cell!

            WritableCellFeatures wcf = new WritableCellFeatures();
            wcf.setComment("Hello!");

            // set cell features!
            label.setCellFeatures(wcf);

            book.write();
            book.close();

Using this with the method in the OP:

I modified the method to return the created (and added!) Number instance. If you don't want that, you could instead retrieve the same cell using WritableWorkbook.getWritableCell() using the same row/col.

public static void main(String[] args) throws IOException, RowsExceededException, WriteException {

    File file = new File("output.xls");
    WritableWorkbook workbook = Workbook.createWorkbook(file);
    WritableSheet sheet = workbook.createSheet("First Sheet", 0);

    Number number = addNumber(sheet, 3, 2, 565d);

    WritableCellFeatures wcf = number.getWritableCellFeatures();
    if (wcf == null) wcf = new WritableCellFeatures();
    wcf.setComment("the comment");
    number.setCellFeatures(wcf);

    workbook.write(); 
    workbook.close();

}

private static Number addNumber(WritableSheet sheet, int column, int row,
        double d) throws WriteException, RowsExceededException {

    Number number = new Number(column, row, d, timesStandard);
    sheet.addCell(number); // need to add the cell first
    return number;
}

Thanks for your answer, just checked it and the comment was added. However, the cell is created using this method: `private static void addNumber(WritableSheet sheet, int column, int row, double d) throws WriteException, RowsExceededException {` what apparently overwrites the comment. Also, the comment is optional, whereas this method is used to add every number. — Hurdler, Apr 14 '12 at 20:19
`addNumber` probably sets new cell features. You'll have to get the writable cell after it was added through `addNumber` and perform your operations. — Torious, Apr 14 '12 at 20:28
This piece of code creates a cell with "Some label" text in it and a comment saying "Hello". I need to use the `addNumber` method to create a cell with a number in it and then, optionally, a comment. When I run the method after creating your "dummy" cell and try to replace the content with my number - the comment disappears. — Hurdler, Apr 14 '12 at 20:36
After your call to `addNumber()`, can you retrieve the `WritableCell` using the same coordinates and retrieve its cell features using `getWritableCellFeatures()`, or does that result in `null`? If it's non-`null`, just call `setComment()` on the obtained `WritableCellFeatures`, otherwise set a new `WritableCellFeatures` as outlined above. — Torious, Apr 14 '12 at 20:39
I only changed the `if` condition from `(wcf == null)` to `boolean`: `(user wants to add a comment)` so I don't need `getWritableCellFeatures();` as I always create new wcf in `if`. Thanks for your answer ;) — Hurdler, Apr 15 '12 at 21:05

score 2 · Answer 2 · answered Apr 14 '12 at 21:32

From comments and :

I made this :

private static void addNumber(WritableSheet sheet, int column, int row,
    double d) throws WriteException, RowsExceededException {

   Number number;
   number = new Number(column, row, d, timesStandard);

   sheet.addCell(number);

   if (user wants to add a comment) {
     WritableCell cell = sheet.getWritableCell(column, row)
     Label l = (Label) cell;
     WritableCellFeatures cellFeatures = new WritableCellFeatures();
     cellFeatures.setComment("the cell comment");
     l.setCellFeatures(cellFeatures);
     sheet.addCell(l);
  }

}

Adding a comment to a cell in an Excel file with Java using jxl library

2 Answers2

Linked