Use Apache POI to process Microsoft Office documents

Original sync to: http://www.waylau.com/apache-poi-handle-microsoft-documents/

POI Overview

The mission of the Apache POI project is to create and maintain Java APIs to manipulate files in various formats, including the Office Open XML Standard (OOXML) and Microsoft OLE 2 Compound document Format (OLE2). In summary, you can read and write MS Excel files using Java. In addition, you can use Java to read and write Ms Word and Ms PowerPoint files. Apache POI is your Java Excel solution (for Excel 97-2008). Contains a complete API for porting other OOXML and OLE2 formats.

The OLE2 file includes Microsoft Office files, such as XLS, DOC, PPT, and MFC's serialization API-based file format. The project provides APIs such as OLE2 Filesystem (poifs) and OLE2 Document Properties (HPSF).

Office OpenXML Format is the new XML-based standard in Microsoft Office 2007 and 2008. Includes XLSX, DOCX, and PPTX. The project provides a low-level API to support open Packaging Conventions (open Packaging Conventions) using OPENXML4J.

For each existing MS Office module component, an attempt was made to provide a common high-level Java API to the OLE2 and OOXML document formats. Excel (SS=HSSF+XSSF)

Word (HWPF+XWPF), PowerPoint (HSLF+XSLF), Outlook (HSMF), Visio (HDGF), TNEF (HMEF), and Publisher (HPBF).

The project provides this functionality as much as possible in collaboration with other projects. For example: Cocoon provides serialization of HSSF, works with open office.org in XLS format, and Tika/lucene provides a format interpreter.

Component

Components	Application Type	Maven Artifactid	Notes
Poifs	OLE2 Filesystem	Poi	Required to work with ole2/poifs based files
Hpsf	OLE2 Property Sets	Poi
Hssf	Excel XLS	Poi	For HSSF only, if common SS are needed see below
Hslf	PowerPoint PPT	Poi-scratchpad
Hwpf	Word DOC	Poi-scratchpad
Hdgf	Visio VSD	Poi-scratchpad
Hpbf	Publisher PUB	Poi-scratchpad
Hsmf	Outlook MSG	Poi-scratchpad
Openxml4j	OOXML	Poi-ooxml Plus either Poi-ooxml-schemas or Ooxml-schemas and Ooxml-security	See below for differences
Xssf	Excel XLSX	Poi-ooxml
Xslf	PowerPoint PPTX	Poi-ooxml
Xwpf	Word DOCX	Poi-ooxml
Common SS	Excel XLS and XLSX	Poi-ooxml	Workbookfactory and friends all require poi-ooxml, not just core poi

Use

The following shows a case of using HSSF to create a excle

Create a POI Project

mvn archetype:create -DgroupId=com.waylau.poi -DartifactId=poi-demos

Introducing relevant POI dependencies

This example is version 3.11

<dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.11</version></dependency>

First HelloWorld Program

Add a Helloworld.java

Hssfworkbook () to create a workbook

Workbook wb = new HSSFWorkbook();  // 或 new XSSFWorkbook();

Wb.createshee is used to create Sheet

Sheet sheet1 = wb.createSheet("第1个 sheet");Sheet sheet2 = wb.createSheet("第2个 sheet");

There are certain restrictions on the naming of Sheet, for example, can not be used * , ？ etc., for the sake of convenient and safe naming, it is possible to use

org.apache.poi.ss.util.WorkbookUtil#createSafeSheetNameMethod

String safeName = WorkbookUtil.createSafeSheetName("[WayLau‘s Blog*?]"); // 返回 " WayLau‘s Blog   "Sheet sheet3 = wb.createSheet(safeName);

Creates a data row. Row is starting from 0.

Row row = sheet1.createRow((short)0);

Create Cell drop values in

Cell cell0 = row.createCell(0);cell0.setCellValue(1);

A simpler approach is to implement it in a single line of code

row.createCell(1).setCellValue(1.2);row.createCell(2).setCellValue(true);

You can instantiate different instances of a specific class with creationhelper processing

CreationHelper createHelper = wb.getCreationHelper();row.createCell(3).setCellValue(createHelper.createRichTextString("This is a string"));

Use CellStyle to set the Cell's style

CellStyle cellStyle = wb.createCellStyle();cellStyle.setDataFormat(    createHelper.createDataFormat().getFormat("m/d/yy h:mm"));Cell cell4 = row.createCell(4);cell4.setCellValue(new Date());cell4.setCellStyle(cellStyle);

Finally generate Excle file

FileOutputStream fileOut = new FileOutputStream("helloword.xls");wb.write(fileOut);fileOut.close();

Effect

Project Source

See Helloworld.java examples in the Https://github.com/waylau/poi-demos project

Reference:

• Http://poi.apache.org/index.html
• Http://poi.apache.org/overview.html#components

Use Apache POI to process Microsoft Office documents