POI Shape not displaying in excel spreadsheet

I am required to add a shape on the excel spreadsheet.

I use the Post Processor process to display POI Shape but nothing displays in .xlsx page.

Where am I going wrong with with my Java method:

private void createPortfolioFlightPlan(XSSFWorkbook wb, String sheetName, String cellRef) {
// Set the Active sheet
Sheet activeSheet = wb.getSheet(sheetName);
// Set the Cell reference
CellReference startCellRef = new CellReference(cellRef);
// Set the Start Row and Start Column variables
int startRow = startCellRef.getRow();
int startColumn = startCellRef.getCol();
XSSFDrawing drawing = (XSSFDrawing) activeSheet.createDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(104775, 9525, 190500, 28575, startColumn, startRow, startColumn, startRow);
XSSFSimpleShape diamond = drawing.createSimpleShape(anchor);
diamond.setShapeType(ShapeTypes.DIAMOND);
diamond.setLineWidth(5);
diamond.setFillColor(255, 255, 0);
}

Assistance highly appreciated.

  • Hi,

    What about creating the Anchor using the workbook's CreationHelper?

    XSSFCreationHelper helper = wb.getCreationHelper();
    XSSFClientAnchor anchor = helper.createClientAnchor();
  • Thanks Etienne.

    Still no result. There's not a lot of these examples on Google search. The one example I found is very similar to my method "createPortfolioFlightPlan" in the attached text file. I included the full java source file, maybe I am just missing something or using methods incorrect.

  • Verified Answer

    Hi,

    Based on https://stackoverflow.com/questions/38763554/create-a-small-circle-in-excel-poi, I wrote this simpler code that works for me:

     

    Workbook wb = WorkbookFactory.create(new File("c:\\temp\\EmptySheet.xlsx")); Sheet sheet = wb.getSheetAt(0); Row row = sheet.createRow(0); Cell cell = row.createCell(0); row.setHeight((short)(20*20)); sheet.setColumnWidth(0, 20*256); CreationHelper helper = wb.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); //set anchor to A1 only anchor.setCol1(0); anchor.setRow1(0); anchor.setCol2(0); anchor.setRow2(0); //set wanted shape size int shapeWidthPx = 20; int shapeHeightPx = 20; //set the position of left edge as Dx1 in unit EMU anchor.setDx1(Math.round(5 * Units.EMU_PER_PIXEL)); //set the position of right edge as Dx2 in unit EMU anchor.setDx2(Math.round(25 * Units.EMU_PER_PIXEL)); //set upper padding int upperPaddingPx = 4; //set upper padding as Dy1 in unit EMU anchor.setDy1(upperPaddingPx * Units.EMU_PER_PIXEL); //set upper padding shape height as Dy2 in unit EMU anchor.setDy2((upperPaddingPx shapeHeightPx) * Units.EMU_PER_PIXEL); XSSFSimpleShape shape = ((XSSFDrawing)drawing).createSimpleShape((XSSFClientAnchor)anchor); shape.setShapeType(ShapeTypes.DIAMOND); shape.setFillColor(255, 0, 0); try (OutputStream fileOut = new FileOutputStream("c:\\temp\\SheetWithShape.xlsx")) { wb.write(fileOut); }

     

    Cheers,

    Etienne.

  • Just to check, did you ensure that the cell coordinates at which you insert your drawing are existing cells in the spreadsheet?
  • Thanks Etienne,

    My issue was I did not set the row and cell where the shape needs to be added to. Here is the method that is working:

    private void createPortfolioFlightPlan(XSSFWorkbook wb, String sheetName, String cellRef) { // Set the Active sheet Sheet activeSheet = wb.getSheet(sheetName); // Set the Cell reference CellReference startCellRef = new CellReference(cellRef); // Set the Start Row and Start Column variables int startRow = startCellRef.getRow(); int startColumn = startCellRef.getCol(); // Set Cell as per reference Row row = activeSheet.getRow(startRow); @SuppressWarnings("unused") Cell cell = row.getCell(startColumn); row.setHeight((short) (20 * 20)); activeSheet.setColumnWidth(startColumn, 20 * 256); XSSFCreationHelper helper = wb.getCreationHelper(); XSSFClientAnchor anchor = helper.createClientAnchor(); // set anchor to Cell Reference anchor.setCol1(startColumn); anchor.setRow1(startRow); anchor.setCol2(startColumn); anchor.setRow2(startRow); // set wanted shape size @SuppressWarnings("unused") int shapeWidthPx = 20; int shapeHeightPx = 20; // set the position of left edge as Dx1 in unit EMU anchor.setDx1(Math.round(5 * Units.EMU_PER_PIXEL)); // set the position of right edge as Dx2 in unit EMU anchor.setDx2(Math.round(25 * Units.EMU_PER_PIXEL)); // set upper padding int upperPaddingPx = 4; // set upper padding as Dy1 in unit EMU anchor.setDy1(upperPaddingPx * Units.EMU_PER_PIXEL); // set upper padding shape height as Dy2 in unit EMU anchor.setDy2((upperPaddingPx shapeHeightPx) * Units.EMU_PER_PIXEL); XSSFDrawing drawing = (XSSFDrawing) activeSheet.createDrawingPatriarch(); XSSFSimpleShape diamond = drawing.createSimpleShape(anchor); diamond.setShapeType(ShapeTypes.DIAMOND); diamond.setLineWidth(5); diamond.setFillColor(255, 255, 0); }

    Annotation 2020-03-25 072426.jpg

    As always, thanks again for your help, Etienne