CODEWITHSUNDEEP
javaexcelapache-poi
Grim
Grim

Reputation: 2040

Set position of comment-tooltip in xls-file using Apache POI

I try to add a comment to a excel-field.

If I open the excel-file using Excel97 the Tooltip has bad boundings.

public static void main(String[] args) throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellValue("Test1");

    HSSFCreationHelper ch = sheet.getWorkbook().getCreationHelper();
    HSSFClientAnchor anchor = ch.createClientAnchor();
    HSSFComment comment = sheet.createDrawingPatriarch().createCellComment(anchor);
    comment.setRow(0);
    comment.setColumn(1);
    comment.setString(ch.createRichTextString("Test2"));
    comment.setAuthor("RM");
    cell.setCellComment(comment);
    sheet.autoSizeColumn(0);
    workbook.close();
    workbook.write(new FileOutputStream("d:/test.pdf"));
}

enter image description here

How to set the size of the tooltip programatically?

Upvotes: 0

Views: 1905

Answers (1)

Axel Richter
Axel Richter

Reputation: 61975

You should add comment along the lines of the example in Busy Developers' Guide to HSSF and XSSF Features.

Using the ClientAnchor position settings (col1, dx1, row1, dy1, col2, dx2, row2, dy2) you can set the position of the comment box.

Example:

import java.io.FileOutputStream;
import java.io.OutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

class CreateExcelWithComments {

 public static void main(String[] args) throws Exception {

    String type = "HSSF";
    //String type = "XSSF";

    Workbook wb = ("HSSF".equals(type))?new HSSFWorkbook():new XSSFWorkbook();

    CreationHelper factory = wb.getCreationHelper();

    Sheet sheet = wb.createSheet();

    Row row   = sheet.createRow(0);
    Cell cell = row.createCell(0); // cell A1
    cell.setCellValue("A1");

    Drawing drawing = sheet.createDrawingPatriarch();

    // When the comment box is visible, have it show in a 1x3 space
    ClientAnchor anchor = factory.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex()+1);                           // starts at column A + 1 = B
    anchor.setDx1(("HSSF".equals(type))?10*15:10*Units.EMU_PER_PIXEL); // plus 10 px     
    anchor.setCol2(cell.getColumnIndex()+2);                           // ends at column A + 2 = C
    anchor.setDx2(("HSSF".equals(type))?10*15:10*Units.EMU_PER_PIXEL); // plus 10 px    

    anchor.setRow1(row.getRowNum());                                   // starts at row 1
    anchor.setDy1(("HSSF".equals(type))?10*15:10*Units.EMU_PER_PIXEL); // plus 10 px
    anchor.setRow2(row.getRowNum()+3);                                 // ends at row 4
    anchor.setDy2(("HSSF".equals(type))?10*15:10*Units.EMU_PER_PIXEL); // plus 10 px


    // Create the comment and set the text+author
    Comment comment = drawing.createCellComment(anchor);
    RichTextString str = factory.createRichTextString("Hello, World!");
    comment.setString(str);
    comment.setAuthor("Apache POI");

    // Assign the comment to the cell
    cell.setCellComment(comment);

    String fname = ("HSSF".equals(type))?"./comment-xssf.xls":"./comment-xssf.xlsx";
    try (OutputStream out = new FileOutputStream(fname)) {
        wb.write(out);
    }

    wb.close();

 }
}

Result:

enter image description here

Upvotes: 2

Related Questions