Lire et écrire un fichier Excel en Java à l'aide d'Apache POI

View more Tutorials:

1- Qu'est-ce que POI d'Apache?  

Apache POI est un Java open source bibliothèque fournie par Apache, que c'est une bibliothèque puissante qui vous aide à travailler avec des documents   Microsoft comme Word, Excel, PowerPoint, Visio...

POI singnifie  "Poor Obfuscation Implementation". Les formats de fichiers de Microsoft sont cachés. Les ingénieurs d'Apache ont essayé de l'apprendre , et ils voient que Microsoft a créé le format complexe inutile. Et le nom est dérivé de l'humour bibliothèque.

Poor Obfuscation Implementation: Réalise l'insensé. (Traduit approximative en tant que telle).
 Dans ce document je vous guide à utiliser  Apache POI pour travailler avec  Excel.

2- Vue d'ensemble de Apache POI

Apache POI  vous aide à travailler avec le format de  Microsoft , c'est la classe habituellement ont le préfixe riche,  XSSF,  HPSF,... Regardez le préfixe d'une classe, vous pouvez savoir que le type de classe de Format.
Par exemple, pour travailler avec le format  Excel ( XLS) vous avez besoin de la classe :
 
  • 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- Vue d'ensemble d'Apache POI Excel

L'image ci-dessous illustre la structure d'un document  Excel.
 
Apache POI vous fournit les interfaces  Workbook, Sheet, Row, Cell,...  et les classes représentent (implementation) sont respectivement  HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell,...

4- Bibliothèque d'Apache POI

Si votre projet utilise  Maven , vous devez seulement déclarer la bibliothèque tout simplement dans le  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>
Si vous n'utilisez pas de  Maven, vous pouvez télécharger la bibliothèque Apache POI à:
Télécharger et décompresser, pour travailler avec Excel , il faut au moins trois fichiers jar  :
  • poi-**.jar
  • lib/commons-codec-**.jar
  • lib/commons-collections4-**.jar
Dans ce document, j'ai créé un projet  Maven simple avec le nom  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- Créer et écrire des fichiers Excel

Microsoft Office  et les versions précédentes (97-2003) format  Excel fichiers de  XLS et la nouvelle version  XSLX utilisent souvent le format. Pour manipuler le fichier  XSL vous devez utiliser le préfixe classe  HSSF là. En ce qui concerne le format de fichier à utiliser le  XSLX de classe avec le préfixe  XSSF.
 
Ci-dessous est un exemple simple en utilisant le  POI pour créer un fichier  Excel. Vous pouvez combiner avec l'utilisation de styles (style) sur la cellule ( Cell) pour créer un meilleur document  Excel. Style de  POI est mentionné plus en détail à la fin du document.
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;
    }

}
Exécutez l'exemple:

6- Lire fichier xsl et xslx

L'exemple ci-dessous lit simple fichier  Excel et écrire des informations à l'écran de la console. fichier  Excel utilisé pour lire le fichier  Excel qui a été créé dans l'exemple ci-dessus.
Remarque : dans ce document, j'utilise Apache POI 3.15 , API a beaucoup changé par rapport à l'ancienne version. Il existe de nombreuses méthodes seront supprimée dans une futur version (Apache  POI 4. x).  POI vient d'utiliser à  Enum pour remplacer l'une des constantes dans son 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("");
        }
    }

}
Exécution de l'exemple  :

7- Mettre à jour le fichier Excel disponible

Dans cet exemple, je lis file excel employee.xls et mettre à jour les valeurs de la colonne pour celle de Salary a augmenté deux fois.
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();

   }

}
Résultats après la mise à jour:

8- Formules et évaluation  

Si vous avez connaissance d' Excel, vous ne serez pas difficile d'établir une formule. Avec Apache POI , vous pouvez créer un  CellType.FORMULA cellulaire de type, sa valeur est calculée sur la base d'une formule.

SUM

Exemple : Compter les cellules sur la même colonne "C" de la 2e à la ligne à 4e:
// Create Cell type of FORMULA
cell = row.createCell(rowIndex, CellType.FORMULA);

// Set formula
cell.setCellFormula("SUM(C2:C4)");
Recette de cellules individuelles :
 
cell = row.createCell(rowIndex, CellType.FORMULA);
cell.setCellFormula("0.1*C2*D3");
Avec un type de cellule de FORMULA, vous pouvez imprimer sa recette et utiliser  FormulaEvaluator pour calculer les valeurs des cellules données par celui-ci.
// 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- Appliquer le style (style) 

Exemple:
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: