Чтение и запись файла Excel в Java с использованием Apache POI

Что такое Apache POI?
Обзор Apache POI
Обзор Apache POI Excel
Библиотека Apache POI
Создать и записать файл Excel
Читать файл xsl и xslx
Обновить готовый файл Excel
Формулы и оценка
Применение стиля (Style)

1. Что такое Apache POI?

Apache POI это библиотека Java с открытым исходным кодо, предоставленный Apache, это сильная библиотека помогающая вам работать с документами Microsoft, как Word, Excel, Power point, Visio,...

POI это аббревиатура "Poor Obfuscation Implementation". Форматы файлов Microsoft скрыты. Инженеры Apache должны были постараться чтобы понять, и они увидели что Microsoft создал сложные форматы, когда это не необходимо. И название библиотеки имеет происхождение от юмора.

Poor Obfuscation Implementation: Плохая реализация обфускации. (Примерный перевод).

В данной статье мы покажем вам как использовать Apache POI для работы с Excel.

2. Обзор Apache POI

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.

3. Обзор Apache POI Excel

The image below illustrate the structure of an excel document.

Apache POI предоставляет вам интерфейсы Workbook, Sheet, Row, Cell,... и применение соответствуюших классов (implementation) это HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell,...

4. Библиотека Apache POI

Если ваш 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 по ссылке:

http://poi.apache.org/download.html

Скачать и извлечь, для работы с 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>

5. Создать и записать файл Excel

В предыдущих версиях 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;
    }

}

Запуск примера:

6. Читать файл xsl и xslx

Пример ниже читает простой файл 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("");
        }
    }

}

Запуск примера:

7. Обновить готовый файл Excel

В данном примере, я читаю файл 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();

   }

}

Результат после обновления:

8. Формулы и оценка

Если у вас имеется знание про 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();
// ...

9. Применение стиля (Style)

Пример:

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());

    }

}