Apache POI XSSFSimpleShape rectangle and triangle

Question

Want to create triangle and rectangle like below image. Some sample code I'm trying to implement. But did not meet my requirement.

XSSFWorkbook wb = new XSSFWorkbook();
OutputStream os = new FileOutputStream("C:\\sample.xlsx");
XSSFSheet sheet = wb.createSheet("Sheet");
XSSFRow row = sheet.createRow(4); // Creating a row
XSSFCell cell = row.createCell(1); // Creating a cell
XSSFDrawing drawing = sheet.createDrawingPatriarch();


XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 1, 10, 5, 11);
anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE);
anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE);

XSSFSimpleShape rectangle = drawing.createSimpleShape(anchor);

//rounded rectangle
rectangle.setShapeType(26);
rectangle.setFillColor(180,180,180);

XSSFClientAnchor anchor1 = new XSSFClientAnchor(10, 10, 100, 100, 1, 10, 2, 11);
XSSFSimpleShape triangle = drawing.createSimpleShape(anchor1);
//triangle
triangle.setShapeType(3);   
triangle.setFillColor(255,0,0);

wb.write(os);
wb.close();
os.close();

Wanted result:

Answer 1

Your code works and creates a gray rounded rectangle and a red triangle.

What you needs to know now, is how to place the shapes on the cells of the sheet using the XSSFClientAnchor. So what exactly means dx1, dy1, dx2, dy2, col1, row1, col2, row2 in the constructor?

dx1 means how many English Metric Units (EMUs) away from col1 does the shape start

dy1 means how many English Metric Units (EMUs) away from row1 does the shape start

dx2 means how many English Metric Units (EMUs) away from col2 does the shape end

dy2 means how many English Metric Units (EMUs) away from row2 does the shape end

col1 means what column (0-based) is the start column of the shape

row1 means what row (0-based) is the start row of the shape

col2 means what column (0-based) is the end column of the shape

row2 means what row (0-based) is the end row of the shape

For working with the EMUs org.apache.poi.util.Units was made.

And you need to know how to flip a shape. This is not supported in high level XSSFSimpleShape up to now. But using the low level ooxml-schemas classes, it is possible:

shape.getCTShape().getSpPr().getXfrm().setFlipV(true);

The shape is a XSSFSimpleShape here.

If you knows that, you can do:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.util.Units;

class CreateXSSFSimpleShapes {

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

  try (XSSFWorkbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   XSSFSheet sheet = workbook.createSheet("Sheet");

   XSSFDrawing drawing = sheet.createDrawingPatriarch();

   XSSFClientAnchor anchor = new XSSFClientAnchor(
    0, //dx1 = 0
    0, //dy1 = 0
    0, //dx2 = 0
    0, //dy2 = 0
    1, //col1 = 1 (col B)
    10,//row1 = 10 (row 11) 
    5, //col2 = 5 (col F)
    11 //row2 = 11 (row 12)
   );
   anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
   XSSFSimpleShape shape = drawing.createSimpleShape(anchor);
   //rounded rectangle
   shape.setShapeType(ShapeTypes.ROUND_RECT);
   shape.setFillColor(180,180,180);

   anchor = new XSSFClientAnchor(
    0, //dx1 = 0
    0, //dy1 = 0
    Units.pixelToEMU(20), //dx2 = 20px away from left border of col2
    0, //dy2 = 0
    1, //col1 = 1 (col B)
    10,//row1 = 10 (row 11) 
    1, //col2 = 1 (col B)
    11 //row2 = 11 (row 12)
   );
   shape = drawing.createSimpleShape(anchor);
   //blue triangle
   shape.setShapeType(ShapeTypes.TRIANGLE);   
   shape.setFillColor(0,0,255);

   anchor = new XSSFClientAnchor(
    Units.pixelToEMU(20), //dx1 = 20px away from left border of col1
    0, //dy1 = 0
    Units.pixelToEMU(40), //dx2 = 40px away from left border of col2
    0, //dy2 = 0
    1, //col1 = 1 (col B)
    10,//row1 = 10 (row 11) 
    1, //col2 = 1 (col B)
    11 //row2 = 11 (row 12)
   );
   shape = drawing.createSimpleShape(anchor);
   //red triangle
   shape.setShapeType(ShapeTypes.TRIANGLE);   
   shape.setFillColor(255,0,0);
   //flip shape vertical
   shape.getCTShape().getSpPr().getXfrm().setFlipV(true);

   workbook.write(fileout);
  }

 }
}