Apache POI XSSF can't create an upward arrow

Question

I am working on an Excel generated file with Apache POI. The file must be xlsx and not xls. I need to draw some arrows, but I can't draw an upward arrow. I use XSSFClientAnchor to create my arrow and specify row/col 1 and row/col 2.

XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)

It only works when col 1 > col 2 and row 1 > row 2. So I can't draw an upward arrow. If I try to change values to get an upward arrow, my file generated is not readable by Excel. Excel repairs it but then the arrows are hidden.

Here is my code:

public static void test() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet("linechart");
    XSSFDrawing pat = sheet.createDrawingPatriarch();

    XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 10, 10, 5, 5);

    XSSFSimpleShape shape = pat.createSimpleShape(anchor);
    shape.setShapeType(ShapeTypes.LINE);
    shape.setLineWidth(4);
    shape.setLineStyle(0);
    shape.setLineStyleColor(0, 0, 0);

    FileOutputStream fileOut;
    try {
        fileOut = new FileOutputStream(
                "C:\\monfichier" + new Date().toString().replace(':', '_') + ".xlsx");
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

If I try to replace:

XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 10, 10, 5, 5);

with:

XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 5, 5, 10, 10);

it's ok...

Could you test this and say me what do you think about this. It really difficult to find informations about POI, and I didn't found this problem...

Axel Richter Axel Richter · Accepted Answer · 2017-04-07T13:43:49

The anchor determines the size of the shape. For line shapes the line is per default from upper left edge of the first anchor cell to the upper left edge plus dx and dy of the last anchor cell. The first anchor cell is the cell upper left of the anchor while the last anchor cell is the cell bottom right of the anchor. So per default a line shape will be from upper left to bottom right.

If we want it to be from bottom left to upper right, then we must flip the shape.

If we draw a line from bottom left to upper right in Excel and then have a look at the stored /xl/drawings/drawing1.xml, we will see that this manually drawed line shape is flipped also.

Example with both lines:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;

class CreateExcelLineShapes {

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

  Workbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet("Sheet1");

  CreationHelper helper = workbook.getCreationHelper();
  Drawing drawing = sheet.createDrawingPatriarch();

  ClientAnchor anchor = helper.createClientAnchor();

  //Anchor B2:C4
  //This determines the size of the line shape to be from 
  //upper left edge of B2 to upper left edge of C4 plus dx and dy.
  //Since dx and dy are both 0, this is the bottom right edge of B3.
  anchor.setCol1(1);
  anchor.setRow1(1); 
  anchor.setCol2(2);
  anchor.setRow2(3);

  //From here on XSSF only.
  XSSFDrawing xssfdrawing = (XSSFDrawing)drawing;
  XSSFClientAnchor xssfanchor = (XSSFClientAnchor)anchor;

  //Draw a line from upper left edge of B2 to upper left edge of C4 = bottom right edge of B3.
  //This is the default.
  XSSFSimpleShape xssfshape = xssfdrawing.createSimpleShape(xssfanchor);
  xssfshape.setShapeType(ShapeTypes.LINE);
  xssfshape.setLineWidth(4);
  xssfshape.setLineStyle(0);
  xssfshape.setLineStyleColor(0, 0, 0);

  //This sets the arrow line end type:
  xssfshape.getCTShape().getSpPr().getLn().addNewTailEnd().setType(
    org.openxmlformats.schemas.drawingml.x2006.main.STLineEndType.TRIANGLE);

  //Again draw a line from upper left edge of B2 to upper left edge of C4 = bottom right edge of B3.
  xssfshape = xssfdrawing.createSimpleShape(xssfanchor);
  xssfshape.setShapeType(ShapeTypes.LINE);
  xssfshape.setLineWidth(4);
  xssfshape.setLineStyle(0);
  xssfshape.setLineStyleColor(0, 0, 0);

  //Now flip this vertically.
  //So it now will to be from bottom left edge of B3 to upper left edge of B2.
  xssfshape.getCTShape().getSpPr().getXfrm().setFlipV(true);

  xssfshape.getCTShape().getSpPr().getLn().addNewTailEnd().setType(
    org.openxmlformats.schemas.drawingml.x2006.main.STLineEndType.TRIANGLE);

  workbook.write(new FileOutputStream("CreateExcelLineShapes.xlsx"));
  workbook.close();

 }
}

Result:

Apache POI XSSF can't create an upward arrow

1 Answers