Чтение и запись файла Excel в Java с использованием Apache POI
View more Tutorials:
Apache POI это библиотека Java с открытым исходным кодо, предоставленный Apache, это сильная библиотека помогающая вам работать с документами Microsoft, как Word, Excel, Power point, Visio,...
POI это аббревиатура "Poor Obfuscation Implementation". Форматы файлов Microsoft скрыты. Инженеры Apache должны были постараться чтобы понять, и они увидели что Microsoft создал сложные форматы, когда это не необходимо. И название библиотеки имеет происхождение от юмора.
Poor Obfuscation Implementation: Плохая реализация обфускации. (Примерный перевод).
POI это аббревиатура "Poor Obfuscation Implementation". Форматы файлов Microsoft скрыты. Инженеры Apache должны были постараться чтобы понять, и они увидели что Microsoft создал сложные форматы, когда это не необходимо. И название библиотеки имеет происхождение от юмора.
Poor Obfuscation Implementation: Плохая реализация обфускации. (Примерный перевод).
В данной статье мы покажем вам как использовать Apache POI для работы с Excel.
Apache POI поддерживает вас при работе с форматами Microsoft, его классы часто имеют приставку HSSF, XSSF, HPSF, ... Смотря на приставки класса, вы можете узнать какой формат поддерживает этот класс.
Например чтобы работать с форматом Excel (XLS) вам нужны классы:
- HSSFWorkbook
- HSSFSheet
- HSSFCellStyle
- HSSFDataFormat
- HSSFFont
- ...
Prefix | Description | |
1 | HSSF (Horrible SpreadSheet Format) | reads and writes Microsoft Excel (XLS) format files. |
2 | XSSF (XML SpreadSheet Format) | reads and writes Office Open XML (XLSX) format files. |
3 | HPSF (Horrible Property Set Format) | reads “Document Summary” information from Microsoft Office files. |
4 | HWPF (Horrible Word Processor Format) | aims to read and write Microsoft Word 97 (DOC) format files. |
5 | HSLF (Horrible Slide Layout Format) | a pure Java implementation for Microsoft PowerPoint files. |
6 | HDGF (Horrible DiaGram Format) | an initial pure Java implementation for Microsoft Visio binary files. |
7 | HPBF (Horrible PuBlisher Format) | a pure Java implementation for Microsoft Publisher files. |
8 | HSMF (Horrible Stupid Mail Format) | a pure Java implementation for Microsoft Outlook MSG files |
9 | DDF (Dreadful Drawing Format) | a package for decoding the Microsoft Office Drawing format. |
The image below illustrate the structure of an excel document.

Apache POI предоставляет вам интерфейсы Workbook, Sheet, Row, Cell,... и применение соответствуюших классов (implementation) это HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell,...
Если ваш project использует Maven, вам нужно только объявить библиотеку простым способом в pom.xml:
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency>
Если вы не используете Maven, то можете скачать библиотеку Apache POI по ссылке:

Скачать и извлечь, для работы с Excel вам нужно минимум 3 файла jar:
- poi-**.jar
- lib/commons-codec-**.jar
- lib/commons-collections4-**.jar

В данной статье, я создам простой Project Maven с названием ApachePOIExcel
- Group ID: org.o7planning
- Artifact ID: ApachePOIExcel

pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.o7planning</groupId> <artifactId>ApachePOIExcel</artifactId> <version>0.0.1-SNAPSHOT</version> <dependencies> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> </dependencies> </project>
В предыдущих версиях Microsoft Office (97-2003) файлы excel имели формат XLS и новый версии обычно используют формат XSLX. Для работы с файлами XSL вам нужно использовать классы с приставкой HSSF. Для файлов формата XSLX нужно использовать классы с приставкой XSSF.
Пример ниже является простым примером использования POI чтобы создать файл excel. Вы можете сочетать с использованием стиля (Style) в ячейках (Cell) чтобы создать красивый документ Excel. POI Style объясняется более детально в конце статьи.
CreateExcelDemo.java
package org.o7planning.apachepoiexcel.demo; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.o7planning.apachepoiexcel.model.Employee; import org.o7planning.apachepoiexcel.model.EmployeeDAO; public class CreateExcelDemo { private static HSSFCellStyle createStyleForTitle(HSSFWorkbook workbook) { HSSFFont font = workbook.createFont(); font.setBold(true); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); return style; } public static void main(String[] args) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Employees sheet"); List<Employee> list = EmployeeDAO.listEmployees(); int rownum = 0; Cell cell; Row row; // HSSFCellStyle style = createStyleForTitle(workbook); row = sheet.createRow(rownum); // EmpNo cell = row.createCell(0, CellType.STRING); cell.setCellValue("EmpNo"); cell.setCellStyle(style); // EmpName cell = row.createCell(1, CellType.STRING); cell.setCellValue("EmpNo"); cell.setCellStyle(style); // Salary cell = row.createCell(2, CellType.STRING); cell.setCellValue("Salary"); cell.setCellStyle(style); // Grade cell = row.createCell(3, CellType.STRING); cell.setCellValue("Grade"); cell.setCellStyle(style); // Bonus cell = row.createCell(4, CellType.STRING); cell.setCellValue("Bonus"); cell.setCellStyle(style); // Data for (Employee emp : list) { rownum++; row = sheet.createRow(rownum); // EmpNo (A) cell = row.createCell(0, CellType.STRING); cell.setCellValue(emp.getEmpNo()); // EmpName (B) cell = row.createCell(1, CellType.STRING); cell.setCellValue(emp.getEmpName()); // Salary (C) cell = row.createCell(2, CellType.NUMERIC); cell.setCellValue(emp.getSalary()); // Grade (D) cell = row.createCell(3, CellType.NUMERIC); cell.setCellValue(emp.getGrade()); // Bonus (E) String formula = "0.1*C" + (rownum + 1) + "*D" + (rownum + 1); cell = row.createCell(4, CellType.FORMULA); cell.setCellFormula(formula); } File file = new File("C:/demo/employee.xls"); file.getParentFile().mkdirs(); FileOutputStream outFile = new FileOutputStream(file); workbook.write(outFile); System.out.println("Created file: " + file.getAbsolutePath()); } }
Employee.java
package org.o7planning.apachepoiexcel.model; public class Employee { private String empNo; private String empName; private Double salary; private int grade; private Double bonus; public Employee(String empNo, String empName,// Double salary, int grade, Double bonus) { this.empNo = empNo; this.empName = empName; this.salary = salary; this.grade = grade; this.bonus = bonus; } public String getEmpNo() { return empNo; } public void setEmpNo(String empNo) { this.empNo = empNo; } public String getEmpName() { return empName; } public void setEmpName(String empName) { this.empName = empName; } public Double getSalary() { return salary; } public void setSalary(Double salary) { this.salary = salary; } public int getGrade() { return grade; } public void setGrade(int grade) { this.grade = grade; } public Double getBonus() { return bonus; } public void setBonus(Double bonus) { this.bonus = bonus; } }
EmployeeDAO.java
package org.o7planning.apachepoiexcel.model; import java.util.ArrayList; import java.util.List; public class EmployeeDAO { public static List<Employee> listEmployees() { List<Employee> list = new ArrayList<Employee>(); Employee e1 = new Employee("E01", "Tom", 200.0, 1, null); Employee e2 = new Employee("E02", "Jerry", 100.2, 2, null); Employee e3 = new Employee("E03", "Donald", 150.0, 2, null); list.add(e1); list.add(e2); list.add(e3); return list; } }
Запуск примера:

Пример ниже читает простой файл excel и записывает информацию на экране Console. Файл excel, использующийся для чтения, это файл excel созданный в примере выше.

Заметка: В данной статье я использую Apache POI 3.15, API имеет много изменений по сравнению со старой версией. Многие методы будут удалены из будущей версии (Apache POI 4.x). POI стремится использовать Enum чтобы заменить констанции в его API.
ReadExcelDemo.java
package org.o7planning.apachepoiexcel.demo; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.Iterator; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; public class ReadExcelDemo { public static void main(String[] args) throws IOException { // Read XSL file FileInputStream inputStream = new FileInputStream(new File("C:/demo/employee.xls")); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(inputStream); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); // Change to getCellType() if using POI 4.x CellType cellType = cell.getCellTypeEnum(); switch (cellType) { case _NONE: System.out.print(""); System.out.print("\t"); break; case BOOLEAN: System.out.print(cell.getBooleanCellValue()); System.out.print("\t"); break; case BLANK: System.out.print(""); System.out.print("\t"); break; case FORMULA: // Formula System.out.print(cell.getCellFormula()); System.out.print("\t"); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); // Print out value evaluated by formula System.out.print(evaluator.evaluate(cell).getNumberValue()); break; case NUMERIC: System.out.print(cell.getNumericCellValue()); System.out.print("\t"); break; case STRING: System.out.print(cell.getStringCellValue()); System.out.print("\t"); break; case ERROR: System.out.print("!"); System.out.print("\t"); break; } } System.out.println(""); } } }
Запуск примера:

В данном примере, я читаю файл excel employee.xls и обновляю новые значения для столбца Salary, увеличиваю в 2 раза.

UpdateExcelDemo.java
package org.o7planning.apachepoiexcel.demo; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class UpdateExcelDemo { public static void main(String[] args) throws IOException { File file = new File("C:/demo/employee.xls"); // Read XSL file FileInputStream inputStream = new FileInputStream(file); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(inputStream); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); HSSFCell cell = sheet.getRow(1).getCell(2); cell.setCellValue(cell.getNumericCellValue() * 2); cell = sheet.getRow(2).getCell(2); cell.setCellValue(cell.getNumericCellValue() * 2); cell = sheet.getRow(3).getCell(2); cell.setCellValue(cell.getNumericCellValue() * 2); inputStream.close(); // Write File FileOutputStream out = new FileOutputStream(file); workbook.write(out); out.close(); } }
Результат после обновления:

Если у вас имеется знание про Excel, то вам будет легко сформулировать формулу. С Apache POI вы можете создать Cell вида CellType.FORMULA, его значение будет расчитано на основании формулы.
SUM
Например: Посчитать сумму ячеек одного столбца "C" начиная со 2-ой строки до 4-ой:
// Create Cell type of FORMULA cell = row.createCell(rowIndex, CellType.FORMULA); // Set formula cell.setCellFormula("SUM(C2:C4)");

Пример формулы:
cell = row.createCell(rowIndex, CellType.FORMULA); cell.setCellFormula("0.1*C2*D3");
Для ячейки вида FORMULA, вы можете распечатать ее формулу и использовать FormulaEvaluator, чтобы посчитать значение ячейки данная формулой.
// Formula String formula = cell.getCellFormula(); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); // CellValue CellValue cellValue = evaluator.evaluate(cell); double value = cellValue.getNumberValue(); String value = cellValue.getStringValue(); boolean value = cellValue.getBooleanValue(); // ...
Пример:

StyleDemo.java
package org.o7planning.apachepoiexcel.demo; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.IndexedColors; public class StyleDemo { private static HSSFCellStyle getSampleStyle(HSSFWorkbook workbook) { // Font HSSFFont font = workbook.createFont(); font.setBold(true); font.setItalic(true); // Font Height font.setFontHeightInPoints((short) 18); // Font Color font.setColor(IndexedColors.RED.index); // Style HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); return style; } public static void main(String[] args) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Style Demo"); HSSFRow row = sheet.createRow(0); // HSSFCell cell = row.createCell(0); cell.setCellValue("String with Style"); HSSFCellStyle style = getSampleStyle(workbook); cell.setCellStyle(style); File file = new File("C:/demo/style.xls"); file.getParentFile().mkdirs(); FileOutputStream outFile = new FileOutputStream(file); workbook.write(outFile); System.out.println("Created file: " + file.getAbsolutePath()); } }