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; paragraph.setIndentationFirstLine(indentationFirstLine); Integer indentationLeft; paragraph.setIndentationLeft(indentationLeft); Integer indentationRight; paragraph.setIndentationRight();
Spacing 1 2 3 4 5 6 7 8 9 10 11 12 13 XWPFParagraph paragraph = document.createParagraph();Integer spacingBefore; paragraph.setSpacingBefore(spacingBefore); Integer spacingBeforeLines; paragraph.setSpacingBeforeLines(spacingBeforeLines); Integer spacingAfter; paragraph.setSpacingAfter(spacingAfter); Integer spacingAfterLines; paragraph.setSpacingAfterLines(spacingAfterLines); Integer spacingBetween; 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; run.setFontSize(fontSize); String fontFamily = "Calibri" ;run.setFontFamily(fontFamily); String color = "000000" ; run.setColor(color); run.setBold(false ); run.setItalic(false ); UnderlinePatterns underline; run.setUnderline(underline); run.setStrikeThrough(false ); Integer characterSpacing; 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 ; 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 );
Row Row height
1 2 XSSFRow row = sheet.createRow(rowNum);row.setHeight((short ) (row.getHeight() * 20 ));
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 );cell.setCellValue("Hello World" ); XSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true ); cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); byte [] rgb = {(byte ) 155 , (byte ) 194 , (byte ) 230 };cellStyle.setFillForegroundColor(new XSSFColor (rgb, new DefaultIndexedColorMap ())); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); 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 = 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 HSSFPalette palette = workbook.getCustomPalette();short colorIndex = 8 ;palette.setColorAtIndex(colorIndex, (byte ) 189 , (byte ) 215 , (byte ) 238 ); cellStyle.setFillForegroundColor(palette.getColor(colorIndex).getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Hyperlink 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 CellUtil.setAlignment(cell, HorizontalAlignment.LEFT); CellUtil.setVerticalAlignment(cell, VerticalAlignment.TOP); CellUtil.setCellStyleProperty(cell, CellUtil.WRAP_TEXT, true ); 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(); CreationHelper helper = workbook.getCreationHelper();Drawing drawing = sheet.createDrawingPatriarch();ClientAnchor anchor = helper.createClientAnchor();anchor.setCol1(1 ); anchor.setRow1(2 ); Picture picture = drawing.createPicture(anchor, pictureIdx);double scale = 0.2 ;picture.resize(scale);
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)