Apache POI printing of line shapes problem

Question

I need your help, I have a code ( See below ) that add line shapes with arrows in a cell in excel using Apache POI in Java. See my code. but after exporting, then opening the excel this message from dialog throws, "Drawing from /xl/drawings/drawing3.xml part (Drawing shape)". Please see screenshot.

// create workbook
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet( "Node" );
CreationHelper helper = wb.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();

//anchor from
//parent
anchor.setCol1( gParentCell.getColumnIndex() + 1 );
anchor.setRow1( gParentCell.getRowIndex() );

anchor.setDy1( Units.toEMU( 7.2 ) );

//anchor to
//child
anchor.setCol2( this.col );
anchor.setRow2( this.row );

anchor.setDy2( Units.toEMU( 7.2 ) );

XSSFSimpleShape shape = ((XSSFDrawing)drawing).createSimpleShape((XSSFClientAnchor)anchor);
shape.setShapeType( ShapeTypes.LINE );
shape.setLineWidth(1);
shape.setLineStyle(0);

// draw the arrow
CTShapeProperties shapeProperties = shape.getCTShape().getSpPr();

CTLineEndProperties lineEndProperties = org.openxmlformats.schemas.drawingml.x2006.main.CTLineEndProperties.Factory.newInstance();
lineEndProperties.setType(STLineEndType.TRIANGLE);

// print arrow
CTLineProperties lineProperties = shapeProperties.getLn();
lineProperties.setTailEnd(lineEndProperties);

Excel is trying to recover

the error

Output

Answer 1

In an anchor the index of col1 always needs to be lower than the one of col2 and the index of row1 needs to be lower than the one of row2. Drawn lines in anchors are always top left to bottom right. If needed you can flip them vertically.

So if the need is drawing a line from B14 to G4 for example, you create an anchor B4:G14, draw the line and then flip the shape vertically.

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

Complete example:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.ShapeTypes;
import org.apache.poi.util.Units;
import org.openxmlformats.schemas.drawingml.x2006.main.*;

import java.io.FileOutputStream;

class CreateExcelLineShapesArrows {

 static XSSFSimpleShape drawLine(XSSFCell fromCell, XSSFCell toCell) {
  XSSFWorkbook wb = fromCell.getSheet().getWorkbook();
  XSSFCreationHelper helper = wb.getCreationHelper();
  XSSFSheet sheet = fromCell.getSheet();
  XSSFDrawing drawing = sheet.createDrawingPatriarch();
  XSSFClientAnchor anchor = helper.createClientAnchor();

  int fromCol = Math.min(fromCell.getColumnIndex(), toCell.getColumnIndex());
  int toCol = Math.max(fromCell.getColumnIndex(), toCell.getColumnIndex());
  int fromRow = Math.min(fromCell.getRow().getRowNum(), toCell.getRow().getRowNum());
  int toRow = Math.max(fromCell.getRow().getRowNum(), toCell.getRow().getRowNum());

  anchor.setCol1(fromCol);
  anchor.setRow1(fromRow); // anchor starts top left of this cell
  anchor.setDy1(Units.toEMU(sheet.getRow(fromRow).getHeightInPoints()/2)); //plus 1/2 row height
  anchor.setCol2(toCol);
  anchor.setRow2(toRow);// anchor ends top left of this cell
  anchor.setDy2(Units.toEMU(sheet.getRow(toRow).getHeightInPoints()/2)); //plus 1/2 row height

  XSSFSimpleShape shape = drawing.createSimpleShape(anchor);
  shape.setShapeType(ShapeTypes.LINE);
  shape.setLineWidth(1);
  shape.setLineStyleColor(0, 0, 0);

  if (fromCell.getRow().getRowNum() > toCell.getRow().getRowNum()) { // if fromCell's row is below toCells's row, then flip vertically
   shape.getCTShape().getSpPr().getXfrm().setFlipV(true);
  }

  return shape;
 }

 static void setTailEndTriangle(XSSFSimpleShape shape) {
  CTShapeProperties shapeProperties = shape.getCTShape().getSpPr();
  CTLineProperties lineProperties = shapeProperties.getLn();
  CTLineEndProperties lineEndProperties = CTLineEndProperties.Factory.newInstance();
  lineEndProperties.setType(STLineEndType.TRIANGLE);
  //lineEndProperties.setLen(STLineEndLength.LG);
  //lineEndProperties.setW(STLineEndWidth.LG);
  lineProperties.setTailEnd(lineEndProperties);
 }

 static void setHeadEndTriangle(XSSFSimpleShape shape) {
  CTShapeProperties shapeProperties = shape.getCTShape().getSpPr();
  CTLineProperties lineProperties = shapeProperties.getLn();
  CTLineEndProperties lineEndProperties = CTLineEndProperties.Factory.newInstance();
  lineEndProperties.setType(STLineEndType.TRIANGLE);
  //lineEndProperties.setLen(STLineEndLength.LG);
  //lineEndProperties.setW(STLineEndWidth.LG);
  lineProperties.setHeadEnd(lineEndProperties);
 }

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

  XSSFWorkbook wb = new XSSFWorkbook();

  XSSFSheet sheet = wb.createSheet("Sheet1");

  XSSFCell cellG4 = sheet.createRow(3).createCell(6); cellG4.setCellValue("Test A");
  XSSFCell cellA14 = sheet.createRow(13).createCell(0); cellA14.setCellValue("Test");
  XSSFCell cellB14 = sheet.getRow(13).createCell(1);
  XSSFCell cellG23 = sheet.createRow(22).createCell(6); cellG23.setCellValue("Test B");

  XSSFSimpleShape shape = drawLine(cellB14, cellG23);
  setTailEndTriangle(shape);
  shape = drawLine(cellB14, cellG4);
  setTailEndTriangle(shape);

  FileOutputStream out = new FileOutputStream("CreateExcelLineShapesArrows.xlsx");
  wb.write(out);
  out.close();
  wb.close();

 }
}

Result:

Answer 2

I want to share also what I learned just now,

This is correct shape.getCTShape().getSpPr().getXfrm().setFlipV(true);

But you can also use ShapeTypes.LINE_INV;