Apache Poi and excel generation basics

Generate and edit excel xlsx file with Apache Poi

Albert Lacambra BasilAlbert Lacambra Basil

Load a workbook

A woorkbook represents an excel file
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("/path/to/xlsx/file"));

Load and remove a sheet

Load a sheet named "table 1"
XSSFSheet sheet = workbook.getSheet("table 1");
removes sheet on position 1. The first sheet is position 0.
workbook.removeSheetAt(1);

Load Rows and Cells

You can work directly with row and cell indexes which are Integers and begin per the index 0.

Load rows and cells using an index
XSSFRow row = sheet.getRow(rowIndex);
XSSFCell cell = row.getCell(cellIndex);

Another option that helps to work together with an excel sheet as a template are CellReferences. The can reference cell in a more human way, using excel coordinates.

Load rows and cells using a CellReference
//Represent indexes (0, 0)
CellReference reference = new CellReference("A1");

XSSFRow row = sheet.getRow(reference.getRow());
XSSFCell cell = row.getCell(reference.getCol());

Create a color map for you application.

Definae a color map with the colors that you are gone a use in your application.

public class MyIndexedColorMap implements IndexedColorMap {

  public static final byte RED = 0;
  public static final byte GREEN_ = 1;
  public static final byte YELLOW = 2;
  public static final byte NORMAL = 3;


  private static final byte[][] colorIndex;

  static {
    colorIndex = new byte[4][3];
    colorIndex[RED] = new byte[]{(byte) 255, 0, 0};
    colorIndex[GREEN_] = new byte[]{0, (byte) 255, 0};
    colorIndex[YELLOW] = new byte[]{(byte) 255, (byte) 255, 0};
    colorIndex[NORMAL] = new byte[]{0, 0, 0};
  }

  @Override
  public byte[] getRGB(int index) {
    if (colorIndex == null || index < -127 || index >= colorIndex.length) return null;
    return colorIndex[index];
  }
}

It is also possible to use the DefaultInsdexedColorMap.class which can be used with a variety of predefined colors.

On HSSFColor.HSSFColorPredefined.class
   public enum HSSFColorPredefined {
        BLACK                (0x08,   -1, 0x000000),
        BROWN                (0x3C,   -1, 0x993300),
        OLIVE_GREEN          (0x3B,   -1, 0x333300),
        DARK_GREEN           (0x3A,   -1, 0x003300),
        DARK_TEAL            (0x38,   -1, 0x003366),
        DARK_BLUE            (0x12, 0x20, 0x000080),
        INDIGO               (0x3E,   -1, 0x333399),
        GREY_80_PERCENT      (0x3F,   -1, 0x333333),
        ORANGE               (0x35,   -1, 0xFF6600),
        DARK_YELLOW          (0x13,   -1, 0x808000),
        GREEN                (0x11,   -1, 0x008000),
        TEAL                 (0x15, 0x26, 0x008080),
        BLUE                 (0x0C, 0x27, 0x0000FF),
        BLUE_GREY            (0x36,   -1, 0x666699),
        GREY_50_PERCENT      (0x17,   -1, 0x808080),
        RED                  (0x0A,   -1, 0xFF0000),
        LIGHT_ORANGE         (0x34,   -1, 0xFF9900),
        LIME                 (0x32,   -1, 0x99CC00),
        SEA_GREEN            (0x39,   -1, 0x339966),
        AQUA                 (0x31,   -1, 0x33CCCC),
        LIGHT_BLUE           (0x30,   -1, 0x3366FF),
        VIOLET               (0x14, 0x24, 0x800080),
        GREY_40_PERCENT      (0x37,   -1, 0x969696),
        PINK                 (0x0E, 0x21, 0xFF00FF),
        GOLD                 (0x33,   -1, 0xFFCC00),
        YELLOW               (0x0D, 0x22, 0xFFFF00),
        BRIGHT_GREEN         (0x0B,   -1, 0x00FF00),
        TURQUOISE            (0x0F, 0x23, 0x00FFFF),
        DARK_RED             (0x10, 0x25, 0x800000),
        SKY_BLUE             (0x28,   -1, 0x00CCFF),
        PLUM                 (0x3D, 0x19, 0x993366),
        GREY_25_PERCENT      (0x16,   -1, 0xC0C0C0),
        ROSE                 (0x2D,   -1, 0xFF99CC),
        LIGHT_YELLOW         (0x2B,   -1, 0xFFFF99),
        LIGHT_GREEN          (0x2A,   -1, 0xCCFFCC),
        LIGHT_TURQUOISE      (0x29, 0x1B, 0xCCFFFF),
        PALE_BLUE            (0x2C,   -1, 0x99CCFF),
        LAVENDER             (0x2E,   -1, 0xCC99FF),
        WHITE                (0x09,   -1, 0xFFFFFF),
        CORNFLOWER_BLUE      (0x18,   -1, 0x9999FF),
        LEMON_CHIFFON        (0x1A,   -1, 0xFFFFCC),
        MAROON               (0x19,   -1, 0x7F0000),
        ORCHID               (0x1C,   -1, 0x660066),
        CORAL                (0x1D,   -1, 0xFF8080),
        ROYAL_BLUE           (0x1E,   -1, 0x0066CC),
        LIGHT_CORNFLOWER_BLUE(0x1F,   -1, 0xCCCCFF),
        TAN                  (0x2F,   -1, 0xFFCC99),


    }
load a color
//Load a color from MyIndexedColorMap.class
IndexedColorMap COLOR_MAP = new MyIndexedColorMap();
xssfColor.setRGB(COLOR_MAP.getRGB(color));

//Use the HSSFColorPredefined.class
xssfColor.setRGB(DefaultIndexedColorMap.getDefaultRGB(HSSFColor.HSSFColorPredefined.BLACK.getIndex()));

Set background color to a cell fromma cloned CellStyle

XSSFCellStyle style = (XSSFCellStyle) cell.getCellStyle().clone();

style.cloneStyleFrom(cell.getCellStyle());
XSSFColor xssfColor = new XSSFColor(COLOR_MAP);
xssfColor.setRGB(COLOR_MAP.getRGB(colorIndex));
style.setFillForegroundColor(xssfColor);
cell.setCellStyle(style);

Add a cell comment

CreationHelper factory = wrkbook.getCreationHelper();
ClientAnchor anchor = factory.createClientAnchor();
Drawing drawing = cell.getSheet().createDrawingPatriarch();

anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 1);

anchor.setRow1(cell.getRowIndex());
anchor.setRow2(cell.getRowIndex() + 1);

Comment comment = drawing.createCellComment(anchor);
XSSFRichTextString xssfRichTextString = new XSSFRichTextString("some text in first row\n");
xssfRichTextString.append("text in second row");
comment.setString(xssfRichTextString);

Apply color to the text in a cell

XSSFFont xssfFont = new XSSFFont();
XSSFColor xssfColor = new XSSFColor(COLOR_MAP);
byte color = MyIndexedColorMap.RED;
xssfColor.setRGB(COLOR_MAP.getRGB(color));
xssfFont.setColor(xssfColor);
richTextString.append("a RED text", xssfFont);

XSSFFont xssfFont = new XSSFFont();
XSSFColor xssfColor = new XSSFColor(COLOR_MAP);
byte color = MyIndexedColorMap.GREEN;
xssfColor.setRGB(COLOR_MAP.getRGB(color));
xssfFont.setColor(xssfColor);
richTextString.append("a GREEN text", xssfFont);

Copy a row from one sheet to another

XSSFSheet sheet1 = workbook.getSheet("table 1");
XSSFSheet sheet2 = workbook.getSheet("table 2");

XSSFRow sourceRow = sheet2.getRow(0);

int targetRowIndex = 0;
sheet1.copyRows(
    Collections.singletonList(sourceRow),
    targetRowIndex,
    new CellCopyPolicy.Builder().cellStyle(true).cellValue(true).cellFormula(true).build()
);

With this method you can copy rows from one sheet to another. That allows you to create some templates and use it directly to another sheet. You can remove at the end the templates sheet, leaving only the reals content.

However, you need to be aware, that if the source row index and the target row index are the same, Poi will throw and exception.

It will treat it as if you try to copy over the source row. That happens because Poi is not aware the the rows belongs to a different sheet.

  •   Tags: