Чтение и запись файла 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());
}
}
