Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..
114 views

POI Shape not displaying in excel spreadsheet

Jump to solution

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.

Labels (3)
0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: POI Shape not displaying in excel spreadsheet

Jump to solution

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.

View solution in original post

0 Likes
5 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: POI Shape not displaying in excel spreadsheet

Jump to solution
Hi,

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

XSSFCreationHelper helper = wb.getCreationHelper();
XSSFClientAnchor anchor = helper.createClientAnchor();
0 Likes
Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..

Re: POI Shape not displaying in excel spreadsheet

Jump to solution

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.

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: POI Shape not displaying in excel spreadsheet

Jump to solution

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.

View solution in original post

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: POI Shape not displaying in excel spreadsheet

Jump to solution
Just to check, did you ensure that the cell coordinates at which you insert your drawing are existing cells in the spreadsheet?
0 Likes
Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..

Re: POI Shape not displaying in excel spreadsheet

Jump to solution

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

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.