By

Contents
  1. 1. Word
    1. 1.1. Write Data into Word
      1. 1.1.1. Alignment
      2. 1.1.2. Indentation
      3. 1.1.3. Spacing
      4. 1.1.4. Font
      5. 1.1.5. Text
      6. 1.1.6. Insert Images
      7. 1.1.7. Insert Tables
      8. 1.1.8. insert Charts
      9. 1.1.9. Insert HTML
  2. 2. Excel
    1. 2.1. Write Data into Excel
      1. 2.1.1. Column
      2. 2.1.2. Row
      3. 2.1.3. Cell
      4. 2.1.4. Hyperlink
      5. 2.1.5. Rich Text String
      6. 2.1.6. Merge Cell
      7. 2.1.7. Cell Utility
      8. 2.1.8. Insert Images
    2. 2.2. Read Data from Excel
  3. 3. Appendixes

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.

1
2
3
4
5
6
7
8
9
10
<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

1
2
3
4
5
6
7
8
9
10
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.setBold(true);
    run.setFontSize(20);
    String filePath = "C:\\Users\\Taogen\\Desktop\\hello.docx";
    document.write(new FileOutputStream(filePath));
}

Alignment

1
2
3
XWPFParagraph paragraph = doc.createParagraph();

paragraph.setAlignment(ParagraphAlignment.CENTER);

Indentation

1
2
3
4
5
6
7
8
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

1
2
3
4
5
6
7
8
9
10
11
12
13
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
5
XWPFParagraph paragraph = document.createParagraph();

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

Insert Images

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

1
2
3
4
5
6
7
8
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

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

Row

Row height

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

Cell

XSSFCell

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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

1
2
3
4
5
6
7
// 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);
1
2
3
4
5
6
7
8
9
10
11
12
13
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

1
2
3
4
5
6
7
8
9
10
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

1
sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));
1
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

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

set cell style

1
2
3
4
5
6
7
// 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

1
2
3
4
5
6
7
8
9
10
11
12
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)

Contents
  1. 1. Word
    1. 1.1. Write Data into Word
      1. 1.1.1. Alignment
      2. 1.1.2. Indentation
      3. 1.1.3. Spacing
      4. 1.1.4. Font
      5. 1.1.5. Text
      6. 1.1.6. Insert Images
      7. 1.1.7. Insert Tables
      8. 1.1.8. insert Charts
      9. 1.1.9. Insert HTML
  2. 2. Excel
    1. 2.1. Write Data into Excel
      1. 2.1.1. Column
      2. 2.1.2. Row
      3. 2.1.3. Cell
      4. 2.1.4. Hyperlink
      5. 2.1.5. Rich Text String
      6. 2.1.6. Merge Cell
      7. 2.1.7. Cell Utility
      8. 2.1.8. Insert Images
    2. 2.2. Read Data from Excel
  3. 3. Appendixes