betacode

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

View more Tutorials:

  1. Что такое Apache POI?
  2. Обзор Apache POI
  3. Обзор Apache POI Excel
  4. Библиотека Apache POI
  5. Создать и записать файл Excel
  6. Читать файл xsl и xslx
  7. Обновить готовый файл Excel
  8. Формулы и оценка 
  9. Применение стиля (Style)
Следуйте за нами на нашей фан-странице, чтобы получать уведомления каждый раз, когда появляются новые статьи. Facebook

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

    }

}

View more Tutorials:

Maybe you are interested

Это онлайн курс вне вебсайта o7planning, который мы представляем, он включает бесплатные курсы или курсы со скидкой.