Handling Microsoft Documents in Java with Apache POI

Posted on Edited on In ,

Apache POI is a Java API for Microsoft Documents processing. It provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint and Excel. The code examples in this post are based on Apache POI v5.0.0.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.0.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.0.0</version>
</dependency>

poi is for Excel 97-2003 workbook.

poi-ooxml is for Excel 2007+ workbook.

Word

Write Data into Word

A basic use example

public static void main(String[] args) throws IOException {
    XWPFDocument document = new XWPFDocument();
    XWPFParagraph paragraph = document.createParagraph();
    XWPFRun run = paragraph.createRun();
    run.setText("Hello World");
    run.addBreak();
    run.setText("Apache POI");
    run.setBold(true);
    run.setFontSize(20);
    String filePath = "C:\\Users\\Taogen\\Desktop\\hello.docx";
    document.write(new FileOutputStream(filePath));
}

Alignment

XWPFParagraph paragraph = doc.createParagraph();

paragraph.setAlignment(ParagraphAlignment.CENTER);

Indentation

XWPFParagraph paragraph = document.createParagraph();

Integer indentationFirstLine; // 1/20th point
paragraph.setIndentationFirstLine(indentationFirstLine);
Integer indentationLeft; // 1/20th point
paragraph.setIndentationLeft(indentationLeft);
Integer indentationRight; // 1/20th point
paragraph.setIndentationRight();

Spacing

XWPFParagraph paragraph = document.createParagraph();

Integer spacingBefore; // 1/20th point
paragraph.setSpacingBefore(spacingBefore);
Integer spacingBeforeLines; // 1/100th line
paragraph.setSpacingBeforeLines(spacingBeforeLines);
Integer spacingAfter; // 1/20th point
paragraph.setSpacingAfter(spacingAfter);
Integer spacingAfterLines; // 1/100th line
paragraph.setSpacingAfterLines(spacingAfterLines);
Integer spacingBetween; // 1 line or 1 point, It depends on what LineSpacingRule used
paragraph.setSpacingBetween(spacingBetween, LineSpacingRule.AUTO);
paragraph.setSpacingBetween(spacingBetween, LineSpacingRule.EXACT);

Font

XWPFParagraph paragraph = document.createParagraph();

XWPFRun run = paragraph.createRun();
Integer fontSize; // 1 point
run.setFontSize(fontSize);
String fontFamily = "Calibri";
run.setFontFamily(fontFamily);
String color = "000000"; // RGB string
run.setColor(color);
run.setBold(false);
run.setItalic(false);
UnderlinePatterns underline;
run.setUnderline(underline);
run.setStrikeThrough(false);
Integer characterSpacing; // 1/20th points
run.setCharacterSpacing(characterSpacing)

Text

XWPFParagraph paragraph = document.createParagraph();

XWPFRun run = paragraph.createRun();
String text = "Hello World!";
run.setText(text);

Add newline

run.addBreak();

Insert Images

XWPFParagraph imageParagraph = document.createParagraph();

imageParagraph.setAlignment(ParagraphAlignment.CENTER);
XWPFRun imageRun = imageParagraph.createRun();
Integer textPosition = 100; // 1/2nd points
imageRun.setTextPosition(textPosition);
byte[] imageBytes = IOUtils.toByteArray(bufferedInputStream);
BufferedImage bufferedImage = ImageIO.read(new ByteArrayInputStream(imageBytes));
int imageWidth = bufferedImage.getWidth();
int imageHeight = bufferedImage.getHeight();
double scalePercent = 0.2;
int scaledWidth = (int) (imageWidth * scalePercent);
int scaledHeight = (int) (imageHeight * scalePercent);
String fileName = imageFilePath.substring(imageFilePath.lastIndexOf(File.separator) + 1);
imageRun.addPicture(new ByteArrayInputStream(imageBytes),
                    getImageFormat(fileName),
                    fileName,
                    Units.toEMU(scaledWidth),
                    Units.toEMU(scaledHeight));
document.write(bufferedOutputStream);

Insert Tables

TODO

insert Charts

TODO

Insert HTML

Excel

Write Data into Excel

A basic use example

public static void main(String[] args) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();
    XSSFRow row = sheet.createRow(0);
    XSSFCell cell = row.createCell(0);
    cell.setCellValue("Hello World");
    workbook.write(new FileOutputStream("D:\\test.xlsx"));
}

Column

Column width

sheet.setColumnWidth(0, 15 * 256); // unit: 1/256 character

Row

Row height

XSSFRow row = sheet.createRow(rowNum);
row.setHeight((short) (row.getHeight() * 20)); // unit: 1/20 point

Cell

XSSFCell

XSSFCell cell = row.createCell(0);
// value
cell.setCellValue("Hello World");

XSSFCellStyle cellStyle = workbook.createCellStyle();
// align
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// wrap text
cellStyle.setWrapText(true);
// backgroud color
cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// custom backgroud color. note: use XSSFCellStyle object.
byte[] rgb = {(byte) 155, (byte) 194, (byte) 230};
cellStyle.setFillForegroundColor(new XSSFColor(rgb, new DefaultIndexedColorMap()));
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// border
cellStyle.setBorderTop(BorderStyle.MEDIUM);
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderLeft(BorderStyle.MEDIUM);
cellStyle.setBorderRight(BorderStyle.MEDIUM);
cellStyle.setTopBorderColor(IndexedColors.BLUE.getIndex());
cellStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());

// font
Font font = workbook.createFont();
font.setFontName("仿宋");
font.setFontHeightInPoints((short) 14);
font.setColor(IndexedColors.RED.getIndex());
font.setBold(true);
font.setItalic(true);
font.setUnderline(FontUnderline.SINGLE);
font.setStrikeout(true);
cellStyle.setFont(font);

cell.setCellStyle(cellStyle);

HSSFCell

// custom backgroud color
HSSFPalette palette = workbook.getCustomPalette();
// the palette index, between 0x8 to 0x40 inclusive
short colorIndex = 8;
palette.setColorAtIndex(colorIndex, (byte) 189, (byte) 215, (byte) 238);
cellStyle.setFillForegroundColor(palette.getColor(colorIndex).getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
XSSFCell cell = sheet.createRow(rowNum).createCell(columnNum);
cell.setCellValue("Open " + url);
XSSFWorkbook workbook = sheet.getWorkbook();
CreationHelper createHelper = workbook.getCreationHelper();
XSSFHyperlink hyperlink = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.URL);
hyperlink.setAddress(url);
cell.setHyperlink(hyperlink);
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setUnderline(FontUnderline.SINGLE);
font.setColor(IndexedColors.BLUE.getIndex());
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);

Rich Text String

XSSFCell cell = sheet.createRow(rowNum).createCell(columnNum);
XSSFFont font = new XSSFFont();
font.setColor(IndexedColors.RED.getIndex());
XSSFFont font2 = new XSSFFont();
font2.setColor(IndexedColors.GREEN.getIndex());
RichTextString richTextString = sheet.getWorkbook().getCreationHelper()
    .createRichTextString("hello world");
richTextString.applyFont(0, 5, font);
richTextString.applyFont(6, 11, font2);
cell.setCellValue(richTextString);

Merge Cell

sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));
sheet.addMergedRegion(CellRangeAddress.valueOf("A1:B3"));

Note: If set cell styles for merged cells, you should set the leftmost or topmost cell.

Cell Utility

get cell

Cell cell = CellUtil.getCell(CellUtil.getRow(rowNum, sheet), columnNum);

set cell style

// align
CellUtil.setAlignment(cell, HorizontalAlignment.LEFT);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.TOP);
// wrap text
CellUtil.setCellStyleProperty(cell, CellUtil.WRAP_TEXT, true);
// font
CellUtil.setFont(cell, font);

Insert Images

InputStream inputStream = new FileInputStream("C:\\Users\\Taogen\\Desktop\\demo.png");
byte[] bytes = IOUtils.toByteArray(inputStream);
int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
inputStream.close();
//Returns an object that handles instantiating concrete classes
CreationHelper helper = workbook.getCreationHelper();
//Creates the top-level drawing patriarch.
Drawing drawing = sheet.createDrawingPatriarch();
//Create an anchor that is attached to the worksheet
ClientAnchor anchor = helper.createClientAnchor();
//set top-left corner for the image
anchor.setCol1(1);
anchor.setRow1(2);
//Creates a picture
Picture picture = drawing.createPicture(anchor, pictureIdx);
//Reset the image size
double scale = 0.2;
picture.resize(scale); // or picture.resize() original size

Read Data from Excel

Workbook workbook = new XSSFWorkbook(new File(filePath));
DataFormatter formatter = new DataFormatter();
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> iterator = sheet.iterator();
Row row;
while (iterator.hasNext()) {
    row = iterator.next();
    for (Cell cell : row) {
        System.out.print(formatter.formatCellValue(cell) + "\t");
    }
    System.out.println();
}

Appendixes

Javadocs

Excel (XSSF)

Word (XWPF)