Apache POI - Excel-Datei einlesen: Unterschied zwischen den Versionen

Aus Byte-Welt Wiki
Zur Navigation springenZur Suche springen
K
K
 
(4 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 1: Zeile 1:
 
[[Kategorie:Java-Codeschnipsel]]
 
[[Kategorie:Java-Codeschnipsel]]
−
Mit dem Java-[[Framework]] Apache POI kann man recht komfortabel Excel-Dateien auslesen und erzeugen (schreiben).
+
Mit dem Java-[[Framework]] Apache POI kann man recht komfortabel Microsoft Excel-Dateien auslesen und erzeugen (schreiben).
  
−
Hier ein Beispiel zum Auslesen einer beliebigen Excel-Datei. Das heißt, wir verarbeiten das binäre Format (.xls) sowie das auf XML basierende Format .xslx .
+
Hier ein vollständiges Beispiel zum Auslesen des Tabelleninhaltes einer beliebigen Excel-Datei. Das heißt, wir können mit folgendem Code das binäre Format (.xls) sowie das auf [[XML]] basierende Format .xslx einlesen.
 +
 
 +
Diese Klasse wurde mit der Apache License 2.0 veröffentlicht:
 +
 
 +
*[https://www.apache.org/licenses/LICENSE-2.0 Apache License 2.0 (Original)]
 +
*[https://www.moo.com/de/about/fonts/apache-licence Apache License 2.0 (Deutsche Übersetzung)]
  
 
==Excel-Datei einlesen==
 
==Excel-Datei einlesen==
−
Wir schreiben uns zunächst eine [[Klasse]], die zwei private [[Methoden]] implementiert, die die beiden Dateitypen separat verarbeiten:
+
 
 
<syntaxhighlight lang="java" line="true">
 
<syntaxhighlight lang="java" line="true">
 +
/*
 +
* ExcelFileReader.java
 +
*
 +
* Copyright (c) 2019 Gernot Segieth - wiki.byte-welt.net
 +
*
 +
* Permission is hereby granted, free of charge, to any person
 +
* obtaining a copy of this software and associated documentation
 +
* files (the "Software"), to deal in the Software without
 +
* restriction, including without limitation the rights to use,
 +
* copy, modify, merge, publish, distribute, sublicense, and/or sell
 +
* copies of the Software, and to permit persons to whom the
 +
* Software is furnished to do so, subject to the following
 +
* conditions:
 +
*
 +
* The above copyright notice and this permission notice shall be
 +
* included in all copies or substantial portions of the Software.
 +
*
 +
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 +
* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 +
* OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 +
* NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 +
* HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 +
* WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 +
* FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 +
* OTHER DEALINGS IN THE SOFTWARE.
 +
*/
 +
 +
import java.io.File;
 +
import java.io.FileNotFoundException;
 +
import java.io.FileOutputStream;
 +
import java.io.IOException;
 +
import java.util.ArrayList;
 +
import java.util.Date;
 +
import java.util.Iterator;
 +
import java.util.List;
 +
import java.util.Map;
 +
import java.util.Set;
 +
import java.util.logging.Level;
 +
import java.util.logging.Logger;
 
import org.apache.poi.EncryptedDocumentException;
 
import org.apache.poi.EncryptedDocumentException;
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
Zeile 23: Zeile 67:
  
 
/**
 
/**
−
  * ExcelFileReader liest die Excel-Datei ein und erzeugt ein tabellarisches
+
  * ExcelFileProxy liest die Excel-Datei ein und erzeugt ein tabellarisches
 
  * Abbild der Arbeitsmappe in Form von verschachtelten Listen.
 
  * Abbild der Arbeitsmappe in Form von verschachtelten Listen.
 
  *
 
  *
Zeile 29: Zeile 73:
 
  */
 
  */
 
public class ExcelFileReader {
 
public class ExcelFileReader {
−
     private Workbook workbook
+
 
 +
    private File excelFile;
 +
     private Workbook workbook;
 +
    private FormulaEvaluator evaluator;
 +
    private boolean isEmptyRow;
  
 
     /**
 
     /**
−
     * Liest die ihm übergebene Excel-Datei ein und erzeugt ein tabellarisches Abbild
+
     * Erzeugt einen neuen Excel-Datei-Stellvertreter. ExcelFileProxy liest die
 +
    * ihm übergebene Excel-Datei ein und erzeugt ein tabellarisches Abbild
 
     * davon.
 
     * davon.
 
     *
 
     *
Zeile 43: Zeile 92:
 
     */
 
     */
 
     public ExcelFileReader(File excelFile) throws ClassNotFoundException, IOException, InvalidFormatException {
 
     public ExcelFileReader(File excelFile) throws ClassNotFoundException, IOException, InvalidFormatException {
 +
        this.excelFile = excelFile;
 
         createWorkbook(excelFile);
 
         createWorkbook(excelFile);
 
     }
 
     }
  
−
     private void readHSSFWorkbook(File file) throws IOException {
+
     /**
−
         POIFSFileSystem fs = new POIFSFileSystem(file);
+
    * Gibt den Inhalt des übergebenen Tabellenblattes in einer geschachtelten
−
         workbook = new HSSFWorkbook(fs.getRoot(), true);
+
    * Liste zurück.
−
         fs.close();
+
    *
 +
    * @param sheetIndex der Index des Tabellenblattes
 +
    * @return eine Liste aus Listen (Zeilen mit Spalten)
 +
    */
 +
    public List<List<?>> getSheetContent(int sheetIndex) {
 +
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
 +
        return getSheetContent(sheetIndex, 0, 0, 0, 0);
 +
    }
 +
 
 +
    /**
 +
    * Gibt den Inhalt des übergebenen Tabellenblattes in einer geschachtelten
 +
    * Liste zurück.
 +
    *
 +
    * @param sheetIndex der Index des Tabellenblattes
 +
    * @param firstRow in dieser Zeile mit dem Auslesen beginnen
 +
    * @param firstCol in dieser Spalte mit dem Auslesen beginnen
 +
    * @param lastRow letzte auszulesene Zeile
 +
    * @param lastCol letzte auszulesene Spalte
 +
    * @return eine Liste aus Listen (Zeilen mit Spalten)
 +
    */
 +
    public List<List<?>> getSheetContent(int sheetIndex, int firstRow, int firstCol, int lastRow, int lastCol) {
 +
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
 +
        Sheet sheet = workbook.getSheetAt(sheetIndex);
 +
        List<List<?>> sheetData = new ArrayList<>();
 +
 
 +
        // Range festlegen (bei Übergabe 0 - ermitteln, sonst Range verarbeiten
 +
        int lastRowNum;
 +
        if (lastRow < 1) {
 +
            lastRowNum = sheet.getLastRowNum();
 +
        } else {
 +
            lastRowNum = lastRow;
 +
        }
 +
 
 +
        for (int actuallyRow = firstRow; actuallyRow <= lastRowNum; actuallyRow++) {
 +
 
 +
            Row row = sheet.getRow(actuallyRow); //leere Zeile kann NPE auslösen!
 +
 
 +
            if (row != null) {  //leere Zeile abfangen
 +
                List<Object> rowData = new ArrayList<>();
 +
                isEmptyRow = true; //Zeile erstmal als leer ansehen
 +
 
 +
                int lastColNum;
 +
                if (lastCol < 1) {
 +
                    lastColNum = row.getLastCellNum();
 +
                } else {
 +
                    lastColNum = lastCol;
 +
                }
 +
 
 +
                for (int actuallyCol = firstCol; actuallyCol < lastColNum; actuallyCol++) {
 +
                    Cell cell = row.getCell(actuallyCol);
 +
                    rowData.add(getData(cell));
 +
                }
 +
                if (!isEmptyRow) { //leere Zeilen nicht aufnehmen
 +
                    sheetData.add(rowData);
 +
                }
 +
            }
 +
        }
 +
        return sheetData;
 +
    }
 +
 
 +
    /**
 +
    * Schreibt Daten einer tabellenartig aufgebauten Liste in das Arbeitsblatt.
 +
    *
 +
    * @param sheetData die Daten aus der Liste, die in das Tabellenblatt
 +
    * geschrieben werden sollen
 +
    * @param sheetName Name des Tabellenblattes
 +
    */
 +
    public void setSheetContent(List<List<?>> sheetData, String sheetName) {
 +
         String safeName = WorkbookUtil.createSafeSheetName(sheetName);
 +
        CreationHelper createHelper = workbook.getCreationHelper();
 +
         Sheet sheet = workbook.createSheet(safeName);
 +
 
 +
        for (int i = 0, j = sheetData.size(); i < j; i++) {
 +
            Row row = sheet.createRow(i); //Zeile erzeugen
 +
            List<?> rowList = sheetData.get(i); //Zeile holen
 +
            for (int x = 0, y = rowList.size(); x < y; x++) {
 +
                Cell cell = row.createCell(x); //Zelle erzeugen
 +
 
 +
                Object cellValue = rowList.get(x);
 +
                if (cellValue instanceof Number) {
 +
                    cell.setCellValue((Double) cellValue);
 +
                } else if (cellValue instanceof Date) {
 +
                    cell.setCellValue((Date) cellValue);
 +
                } else if (cellValue instanceof Boolean) {
 +
                    cell.setCellValue((Boolean) cellValue);
 +
                } else if (cellValue instanceof String) {
 +
                    cell.setCellValue(createHelper.createRichTextString(cellValue.toString()));
 +
                }
 +
            }
 +
        }
 +
 
 +
    }
 +
 
 +
    /**
 +
    * Gibt die Namen aller Tabellenblätter zurück.
 +
    *
 +
    * @return die Namen der Tabellenblätter
 +
    */
 +
    public String[] getSheetNames() {
 +
        String[] sheetNames = new String[getNumberOfSheets()];
 +
        for (int i = 0; i < sheetNames.length; i++) {
 +
            sheetNames[i] = getSheetName(i);
 +
        }
 +
        return sheetNames;
 +
    }
 +
 
 +
    /**
 +
    * Gibt die Anzahl der Tabellenblätter in der Excel-Datei zurück.
 +
    *
 +
    * @return die Anzahl
 +
    */
 +
    public int getNumberOfSheets() {
 +
        return workbook.getNumberOfSheets();
 +
    }
 +
 
 +
    /**
 +
    * Gibt den Namen des Tabellenblattes am übergebenen Index zurück.
 +
    *
 +
    * @param sheetIndex der Index
 +
    * @return das Tabelleenblatt
 +
    */
 +
    public String getSheetName(int sheetIndex) {
 +
        return workbook.getSheetName(sheetIndex);
 +
    }
 +
 
 +
    /**
 +
    * Setzt den Namen des Tabellenblattes am übergebenen Index.
 +
    *
 +
    * @param index der Index des Tabellenblattes in der Arbeitsmappe
 +
    * @param sheetName der Name des Tabellenblattes
 +
    */
 +
    public void setSheetName(int index, String sheetName) {
 +
        workbook.setSheetName(index, sheetName);
 +
    }
 +
 
 +
    /**
 +
    * Gibt den Index des aktiven Tabellenblattes zurück.
 +
    *
 +
    * @return der Index des aktiven Tabellenblattes
 +
    */
 +
    public int getActiveSheetIndex() {
 +
        return workbook.getActiveSheetIndex();
 +
    }
 +
 
 +
    /**
 +
    * Setzt das aktive Tabellenblatt.
 +
    *
 +
    * @param index der Index des aktiven Tabellenblattes in der Arbeitsmappe
 +
    */
 +
    public void setActiveSheet(int index) {
 +
        workbook.setActiveSheet(index);
 +
    }
 +
 
 +
    /**
 +
    * Gibt das Datum der letzten Änderung der Excel-Datei zurück.
 +
    *
 +
    * @return
 +
    */
 +
    public long lastModified() {
 +
        return excelFile.lastModified();
 +
    }
 +
 
 +
    /**
 +
    * Gibt den genauen (einzigen) Pfad zur geladenen Excel-Datei zurück.
 +
    *
 +
    * @return Der Pfad zur geladenen Excel-Datei. //@throws java.io.IOException
 +
    */
 +
    public String getPath() /*throws IOException*/ {
 +
         return excelFile.getAbsolutePath();
 +
    }
 +
 
 +
    /**
 +
    * Setzt den Pfad zur Excel-Datei und erzeugt ein Abbild des Workbooks (alle
 +
    * Tabellenblätter).
 +
    *
 +
    * @param excelFile
 +
    * @throws java.lang.ClassNotFoundException
 +
    * @throws java.io.IOException
 +
    * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException
 +
    */
 +
    public void setPath(File excelFile) throws ClassNotFoundException, IOException, InvalidFormatException {
 +
        this.excelFile = excelFile;
 +
        update();
 
     }
 
     }
  
−
     private void readXSSFWorkbook(File file) throws InvalidFormatException, IOException {
+
     /**
−
         OPCPackage pkg = OPCPackage.open(file);
+
    * Liest das Workbook der Excel-Datei neu ein.
−
        workbook = new XSSFWorkbook(pkg);
+
    *
−
        pkg.close();
+
    * @throws java.lang.ClassNotFoundException
 +
    * @throws java.io.IOException
 +
    * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException
 +
    */
 +
    public synchronized void update() throws ClassNotFoundException, IOException, InvalidFormatException {
 +
         createWorkbook(excelFile);
 
     }
 
     }
  
−
}
+
    public void createWorkbook(Map<String, List<List<?>>> sheetData) throws IOException {
−
</syntaxhighlight>
+
        Set<String> keys = sheetData.keySet();
 +
        Iterator<String> it = keys.iterator();
 +
        while (it.hasNext()) {
 +
            String key = it.next();
 +
            this.setSheetContent(sheetData.get(key), key);
 +
        }
 +
        createExcelFile(excelFile);
 +
    }
  
−
Anschließend eine fügen wir die bereits im Konstruktor aufgerufene [[Methode]], die die Dateitypen unterscheidet, unsere eben erstellten Methoden aufruft und damit das <code>Workbook</code> aus der übergebenen Datei ({{JAPI|File}} erzeugt, ein.
+
    //erzeugt ein Workbook aus der übergebenen Excel-Datei
−
<syntaxhighlight lang="java" line="true">
 
 
     private void createWorkbook(File file) throws ClassNotFoundException, IOException, InvalidFormatException {
 
     private void createWorkbook(File file) throws ClassNotFoundException, IOException, InvalidFormatException {
 
         String filename = file.getName().toLowerCase();
 
         String filename = file.getName().toLowerCase();
Zeile 68: Zeile 311:
 
             if (filename.endsWith(".xls")) {
 
             if (filename.endsWith(".xls")) {
 
                 readHSSFWorkbook(file);
 
                 readHSSFWorkbook(file);
 +
                //workbook = WorkbookFactory.create(file);
 
             } else if (filename.endsWith(".xlsx")) {
 
             } else if (filename.endsWith(".xlsx")) {
 
                 readXSSFWorkbook(file);
 
                 readXSSFWorkbook(file);
Zeile 74: Zeile 318:
 
             }
 
             }
 
         } catch (IOException ioe) {
 
         } catch (IOException ioe) {
−
             System.err.println(getClass().getName() + ": " + ioe);
+
             Logger.getLogger(ExcelFileProxy.class.getName()).log(Level.SEVERE, null, ioe);
 
             throw new IOException("Ausnahmefehler: Die Datei " + file.getName() + " konnte nicht eingelesen werden!");
 
             throw new IOException("Ausnahmefehler: Die Datei " + file.getName() + " konnte nicht eingelesen werden!");
 
         } catch (EncryptedDocumentException ede) {
 
         } catch (EncryptedDocumentException ede) {
−
             System.err.println(getClass().getName() + ": " + ede);
+
             Logger.getLogger(ExcelFileProxy.class.getName()).log(Level.SEVERE, null, ede);
 
             throw new EncryptedDocumentException("Ausnahmefehler: Die Datei " + file.getName() + " ist mit einem Passwort geschützt und kann daher nicht geöffnet werden!");
 
             throw new EncryptedDocumentException("Ausnahmefehler: Die Datei " + file.getName() + " ist mit einem Passwort geschützt und kann daher nicht geöffnet werden!");
 
         } catch (NoClassDefFoundError err) {
 
         } catch (NoClassDefFoundError err) {
−
             System.err.println(getClass().getName() + ": " + err.toString());
+
             System.err.println(err.toString());
 
             throw new ClassNotFoundException("Schwerer Ausnahmefehler: Klassendefinition nicht gefunden!\nPrüfen Sie die Programmdateien und externen Bibliotheken auf Vollständigkeit!");
 
             throw new ClassNotFoundException("Schwerer Ausnahmefehler: Klassendefinition nicht gefunden!\nPrüfen Sie die Programmdateien und externen Bibliotheken auf Vollständigkeit!");
 
         }
 
         }
 +
 
     }
 
     }
−
</syntaxhighlight>
 
  
−
Damit haben wir den Code zum Öffnen und Einlesen von Excel-Dateien schon geschrieben.
+
    private void readHSSFWorkbook(File file) throws IOException {
 +
        POIFSFileSystem fs = new POIFSFileSystem(file);
 +
        workbook = new HSSFWorkbook(fs.getRoot(), true);
 +
        fs.close();
 +
    }
  
−
==Workbook auslesen==
+
    private void readXSSFWorkbook(File file) throws InvalidFormatException, IOException {
−
Eine Excel-Datei wird als <code>Workbook</code> bezeichnet und besteht aus mindestens einem Arbeitsblatt. Wir sprechen hier vom [[Interface]] <code>Sheet</code>. Ein Arbeitsblatt enthält wiederum Zellen, die in POI als Spalten in Zeilen organisiert sind und mit dem Interface <code>Cell</code> repräsentiert werden. Eine Zelle kann verschiedene Zelltypen haben, die in der Enumeration <code>CellType</code> aufgelistet sind.
+
        OPCPackage pkg = OPCPackage.open(file);
 +
        workbook = new XSSFWorkbook(pkg);
 +
        pkg.close();
 +
    }
  
−
Ein Arbeitsblatt besteht also aus Zeilen, über die in einer Schleife iteriert werden kann.
+
    //Schreibt eine Arbeitsmappe in eine Exceldatei
 +
    private void createExcelFile(File file) throws FileNotFoundException, IOException {
 +
        try (FileOutputStream fileOut = new FileOutputStream(file)) {
 +
            workbook.write(fileOut);
 +
        } catch (FileNotFoundException fnfe) {
 +
            throw new IOException("Ausnahmefehler: Die Datei " + file.getName() + " konnte nicht erzeugt werden!");
 +
        } catch (IOException ioe) {
 +
            throw new IOException("Ausnahmefehler: Die Datei " + file.getName() + " konnte nicht erzeugt werden!");
 +
        }
 +
    }
  
−
===Workbook vollständig auslesen===
+
     //Liest die Daten aus einer Zelle und gibt sie zurück.
−
In diesem Abschnitt schreiben wir uns den Code zum vollständigen Auslesen '''eines''' Arbeitsblattes einer Excel-Datei.
+
     private Object getData(Cell cell) {
−
<syntaxhighlight lang="java" line="true">
+
         if (cell != null) {
−
     /**
 
−
    * Gibt den Inhalt des übergebenen Tabellenblattes in einer Liste
 
−
    * die Listen mit Objekten speichert, zurück.
 
−
    *
 
−
    * @param sheetIndex der Index des Tabellenblattes
 
−
    * @return eine Liste aus Listen (Zeilen mit Spalten)
 
−
    */
 
−
     public List<List<?>> getSheetContent(int sheetIndex) {
 
−
         evaluator = workbook.getCreationHelper().createFormulaEvaluator();
 
−
        Sheet sheet = workbook.getSheetAt(sheetIndex);
 
−
        List<List<?>> sheetData = new ArrayList<>();
 
  
−
        for (Row row : sheet) {
+
            switch (cell.getCellType()) {
−
 
+
                case STRING:
−
            if (row != null) {
+
                    String string = cell.getRichStringCellValue().getString();
−
                List<Object> rowData = new ArrayList<>();
+
                    if (string.isEmpty()) {
−
                boolean isEmptyRow = true; //Zeile erstmal als leer ansehen
+
                        return null;
 +
                    } else {
 +
                        isEmptyRow = false;
 +
                        return string.trim();
 +
                    }
 +
                case NUMERIC:
 +
                    if (DateUtil.isCellDateFormatted(cell)) {
 +
                        Date date = cell.getDateCellValue();
 +
                        if (date.toString().isEmpty()) {
 +
                            return null;
 +
                        } else {
 +
                            isEmptyRow = false;
 +
                            return date;
 +
                        }
 +
                    } else {
 +
                        Number number = cell.getNumericCellValue();
 +
                        if (number.toString().isEmpty()) {
 +
                            return null;
 +
                        } else {
 +
                            isEmptyRow = false;
 +
                            return number;
 +
                        }
 +
                    }
  
−
                 for (Cell cell : row) {
+
                 case BOOLEAN:
−
 
+
                    Boolean bool = (cell.getBooleanCellValue());
−
                    if (cell != null) {
+
                    if (bool.toString().isEmpty()) {
−
 
+
                        return null;
−
                        switch (cell.getCellType()) {
 
−
                            case STRING:
 
−
                                String string = cell.getRichStringCellValue().getString();
 
−
                                if (string.isEmpty()) {
 
−
                                    rowData.add(null);
 
−
                                } else {
 
−
                                    rowData.add(string.trim());
 
−
                                    isEmptyRow = false;
 
−
                                }
 
−
                                break;
 
−
                            case NUMERIC:
 
−
                                if (DateUtil.isCellDateFormatted(cell)) {
 
−
                                    Date date = cell.getDateCellValue();
 
−
                                    if (date.toString().isEmpty()) {
 
−
                                        rowData.add(null);
 
−
                                    } else {
 
−
                                        rowData.add(date);
 
−
                                        isEmptyRow = false;
 
−
                                    }
 
−
                                } else {
 
−
                                    Number number = cell.getNumericCellValue();
 
−
                                    if (number.toString().isEmpty()) {
 
−
                                        rowData.add(null);
 
−
                                    } else {
 
−
                                        rowData.add(number);
 
−
                                        isEmptyRow = false;
 
−
                                    }
 
−
                                }
 
−
                                break;
 
−
                            case BOOLEAN:
 
−
                                Boolean bool = (cell.getBooleanCellValue());
 
−
                                if (bool.toString().isEmpty()) {
 
−
                                    rowData.add(null);
 
−
                                } else {
 
−
                                    rowData.add(bool);
 
−
                                    isEmptyRow = false;
 
−
                                }
 
−
                                break;
 
−
                            case _NONE:
 
−
                            case FORMULA:
 
−
                            case BLANK:
 
−
                            case ERROR:
 
−
                            default:
 
−
                                rowData.add(null);
 
−
                                break;
 
−
                        }
 
 
                     } else {
 
                     } else {
−
                         rowData.add(null);
+
                         isEmptyRow = false;
 +
                        return bool;
 
                     }
 
                     }
−
                 }
+
 
−
                 if (!isEmptyRow) { //leere Zeilen nicht aufnehmen
+
                 case _NONE:
−
                     sheetData.add(rowData);
+
                 case FORMULA:
−
                }
+
                case BLANK:
 +
                case ERROR:
 +
                default:
 +
                     return null;
 
             }
 
             }
 +
        } else {
 +
            return null;
 
         }
 
         }
−
        return sheetData;
 
 
     }
 
     }
 +
}
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
===Workbook teilweise auslesen===
+
==Beispiel-Code für die Benutzung dieser Klasse==
 +
 
 +
==Erläuterungen zum Code==
  
 
{{In Arbeit}}
 
{{In Arbeit}}
 +
 +
{{Fragen stellen}}

Aktuelle Version vom 27. Januar 2020, 17:05 Uhr

Mit dem Java-Framework Apache POI kann man recht komfortabel Microsoft Excel-Dateien auslesen und erzeugen (schreiben).

Hier ein vollständiges Beispiel zum Auslesen des Tabelleninhaltes einer beliebigen Excel-Datei. Das heißt, wir können mit folgendem Code das binäre Format (.xls) sowie das auf XML basierende Format .xslx einlesen.

Diese Klasse wurde mit der Apache License 2.0 veröffentlicht:

Excel-Datei einlesen

  1 /*
  2  * ExcelFileReader.java
  3  *
  4  * Copyright (c) 2019 Gernot Segieth - wiki.byte-welt.net
  5  * 
  6  * Permission is hereby granted, free of charge, to any person
  7  * obtaining a copy of this software and associated documentation
  8  * files (the "Software"), to deal in the Software without
  9  * restriction, including without limitation the rights to use,
 10  * copy, modify, merge, publish, distribute, sublicense, and/or sell
 11  * copies of the Software, and to permit persons to whom the
 12  * Software is furnished to do so, subject to the following
 13  * conditions:
 14  * 
 15  * The above copyright notice and this permission notice shall be
 16  * included in all copies or substantial portions of the Software.
 17  * 
 18  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 19  * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 20  * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 21  * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 22  * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 23  * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 24  * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 25  * OTHER DEALINGS IN THE SOFTWARE.
 26  */
 27 
 28 import java.io.File;
 29 import java.io.FileNotFoundException;
 30 import java.io.FileOutputStream;
 31 import java.io.IOException;
 32 import java.util.ArrayList;
 33 import java.util.Date;
 34 import java.util.Iterator;
 35 import java.util.List;
 36 import java.util.Map;
 37 import java.util.Set;
 38 import java.util.logging.Level;
 39 import java.util.logging.Logger;
 40 import org.apache.poi.EncryptedDocumentException;
 41 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 42 import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
 43 import org.apache.poi.openxml4j.opc.OPCPackage;
 44 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 45 import org.apache.poi.ss.usermodel.Cell;
 46 import org.apache.poi.ss.usermodel.CreationHelper;
 47 import org.apache.poi.ss.usermodel.DateUtil;
 48 import org.apache.poi.ss.usermodel.FormulaEvaluator;
 49 import org.apache.poi.ss.usermodel.Row;
 50 import org.apache.poi.ss.usermodel.Sheet;
 51 import org.apache.poi.ss.usermodel.Workbook;
 52 import org.apache.poi.ss.util.WorkbookUtil;
 53 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 54 
 55 /**
 56  * ExcelFileProxy liest die Excel-Datei ein und erzeugt ein tabellarisches
 57  * Abbild der Arbeitsmappe in Form von verschachtelten Listen.
 58  *
 59  * @author Gernot Segieth
 60  */
 61 public class ExcelFileReader {
 62 
 63     private File excelFile;
 64     private Workbook workbook;
 65     private FormulaEvaluator evaluator;
 66     private boolean isEmptyRow;
 67 
 68     /**
 69      * Erzeugt einen neuen Excel-Datei-Stellvertreter. ExcelFileProxy liest die
 70      * ihm übergebene Excel-Datei ein und erzeugt ein tabellarisches Abbild
 71      * davon.
 72      *
 73      * @param excelFile Die einzulesene Excel-Datei
 74      * @throws java.lang.ClassNotFoundException wenn eine Klasse nicht gefunden
 75      * wurde
 76      * @throws java.io.IOException wenn die Datei nicht eingelesen werde konnte
 77      * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException wenn
 78      * es Probleme mit dem Format der Datei oder der Verschlüsselung gibt
 79      */
 80     public ExcelFileReader(File excelFile) throws ClassNotFoundException, IOException, InvalidFormatException {
 81         this.excelFile = excelFile;
 82         createWorkbook(excelFile);
 83     }
 84 
 85     /**
 86      * Gibt den Inhalt des übergebenen Tabellenblattes in einer geschachtelten
 87      * Liste zurück.
 88      *
 89      * @param sheetIndex der Index des Tabellenblattes
 90      * @return eine Liste aus Listen (Zeilen mit Spalten)
 91      */
 92     public List<List<?>> getSheetContent(int sheetIndex) {
 93         evaluator = workbook.getCreationHelper().createFormulaEvaluator();
 94         return getSheetContent(sheetIndex, 0, 0, 0, 0);
 95     }
 96 
 97     /**
 98      * Gibt den Inhalt des übergebenen Tabellenblattes in einer geschachtelten
 99      * Liste zurück.
100      *
101      * @param sheetIndex der Index des Tabellenblattes
102      * @param firstRow in dieser Zeile mit dem Auslesen beginnen
103      * @param firstCol in dieser Spalte mit dem Auslesen beginnen
104      * @param lastRow letzte auszulesene Zeile
105      * @param lastCol letzte auszulesene Spalte
106      * @return eine Liste aus Listen (Zeilen mit Spalten)
107      */
108     public List<List<?>> getSheetContent(int sheetIndex, int firstRow, int firstCol, int lastRow, int lastCol) {
109         evaluator = workbook.getCreationHelper().createFormulaEvaluator();
110         Sheet sheet = workbook.getSheetAt(sheetIndex);
111         List<List<?>> sheetData = new ArrayList<>();
112 
113         // Range festlegen (bei Übergabe 0 - ermitteln, sonst Range verarbeiten
114         int lastRowNum;
115         if (lastRow < 1) {
116             lastRowNum = sheet.getLastRowNum();
117         } else {
118             lastRowNum = lastRow;
119         }
120 
121         for (int actuallyRow = firstRow; actuallyRow <= lastRowNum; actuallyRow++) {
122 
123             Row row = sheet.getRow(actuallyRow); //leere Zeile kann NPE auslösen!
124 
125             if (row != null) {  //leere Zeile abfangen
126                 List<Object> rowData = new ArrayList<>();
127                 isEmptyRow = true; //Zeile erstmal als leer ansehen
128 
129                 int lastColNum;
130                 if (lastCol < 1) {
131                     lastColNum = row.getLastCellNum();
132                 } else {
133                     lastColNum = lastCol;
134                 }
135 
136                 for (int actuallyCol = firstCol; actuallyCol < lastColNum; actuallyCol++) {
137                     Cell cell = row.getCell(actuallyCol);
138                     rowData.add(getData(cell));
139                 }
140                 if (!isEmptyRow) { //leere Zeilen nicht aufnehmen
141                     sheetData.add(rowData);
142                 }
143             }
144         }
145         return sheetData;
146     }
147 
148     /**
149      * Schreibt Daten einer tabellenartig aufgebauten Liste in das Arbeitsblatt.
150      *
151      * @param sheetData die Daten aus der Liste, die in das Tabellenblatt
152      * geschrieben werden sollen
153      * @param sheetName Name des Tabellenblattes
154      */
155     public void setSheetContent(List<List<?>> sheetData, String sheetName) {
156         String safeName = WorkbookUtil.createSafeSheetName(sheetName);
157         CreationHelper createHelper = workbook.getCreationHelper();
158         Sheet sheet = workbook.createSheet(safeName);
159 
160         for (int i = 0, j = sheetData.size(); i < j; i++) {
161             Row row = sheet.createRow(i); //Zeile erzeugen
162             List<?> rowList = sheetData.get(i); //Zeile holen
163             for (int x = 0, y = rowList.size(); x < y; x++) {
164                 Cell cell = row.createCell(x); //Zelle erzeugen
165 
166                 Object cellValue = rowList.get(x);
167                 if (cellValue instanceof Number) {
168                     cell.setCellValue((Double) cellValue);
169                 } else if (cellValue instanceof Date) {
170                     cell.setCellValue((Date) cellValue);
171                 } else if (cellValue instanceof Boolean) {
172                     cell.setCellValue((Boolean) cellValue);
173                 } else if (cellValue instanceof String) {
174                     cell.setCellValue(createHelper.createRichTextString(cellValue.toString()));
175                 }
176             }
177         }
178 
179     }
180 
181     /**
182      * Gibt die Namen aller Tabellenblätter zurück.
183      *
184      * @return die Namen der Tabellenblätter
185      */
186     public String[] getSheetNames() {
187         String[] sheetNames = new String[getNumberOfSheets()];
188         for (int i = 0; i < sheetNames.length; i++) {
189             sheetNames[i] = getSheetName(i);
190         }
191         return sheetNames;
192     }
193 
194     /**
195      * Gibt die Anzahl der Tabellenblätter in der Excel-Datei zurück.
196      *
197      * @return die Anzahl
198      */
199     public int getNumberOfSheets() {
200         return workbook.getNumberOfSheets();
201     }
202 
203     /**
204      * Gibt den Namen des Tabellenblattes am übergebenen Index zurück.
205      *
206      * @param sheetIndex der Index
207      * @return das Tabelleenblatt
208      */
209     public String getSheetName(int sheetIndex) {
210         return workbook.getSheetName(sheetIndex);
211     }
212 
213     /**
214      * Setzt den Namen des Tabellenblattes am übergebenen Index.
215      *
216      * @param index der Index des Tabellenblattes in der Arbeitsmappe
217      * @param sheetName der Name des Tabellenblattes
218      */
219     public void setSheetName(int index, String sheetName) {
220         workbook.setSheetName(index, sheetName);
221     }
222 
223     /**
224      * Gibt den Index des aktiven Tabellenblattes zurück.
225      *
226      * @return der Index des aktiven Tabellenblattes
227      */
228     public int getActiveSheetIndex() {
229         return workbook.getActiveSheetIndex();
230     }
231 
232     /**
233      * Setzt das aktive Tabellenblatt.
234      *
235      * @param index der Index des aktiven Tabellenblattes in der Arbeitsmappe
236      */
237     public void setActiveSheet(int index) {
238         workbook.setActiveSheet(index);
239     }
240 
241     /**
242      * Gibt das Datum der letzten Änderung der Excel-Datei zurück.
243      *
244      * @return
245      */
246     public long lastModified() {
247         return excelFile.lastModified();
248     }
249 
250     /**
251      * Gibt den genauen (einzigen) Pfad zur geladenen Excel-Datei zurück.
252      *
253      * @return Der Pfad zur geladenen Excel-Datei. //@throws java.io.IOException
254      */
255     public String getPath() /*throws IOException*/ {
256         return excelFile.getAbsolutePath();
257     }
258 
259     /**
260      * Setzt den Pfad zur Excel-Datei und erzeugt ein Abbild des Workbooks (alle
261      * Tabellenblätter).
262      *
263      * @param excelFile
264      * @throws java.lang.ClassNotFoundException
265      * @throws java.io.IOException
266      * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException
267      */
268     public void setPath(File excelFile) throws ClassNotFoundException, IOException, InvalidFormatException {
269         this.excelFile = excelFile;
270         update();
271     }
272 
273     /**
274      * Liest das Workbook der Excel-Datei neu ein.
275      *
276      * @throws java.lang.ClassNotFoundException
277      * @throws java.io.IOException
278      * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException
279      */
280     public synchronized void update() throws ClassNotFoundException, IOException, InvalidFormatException {
281         createWorkbook(excelFile);
282     }
283 
284     public void createWorkbook(Map<String, List<List<?>>> sheetData) throws IOException {
285         Set<String> keys = sheetData.keySet();
286         Iterator<String> it = keys.iterator();
287         while (it.hasNext()) {
288             String key = it.next();
289             this.setSheetContent(sheetData.get(key), key);
290         }
291         createExcelFile(excelFile);
292     }
293 
294     //erzeugt ein Workbook aus der übergebenen Excel-Datei
295     private void createWorkbook(File file) throws ClassNotFoundException, IOException, InvalidFormatException {
296         String filename = file.getName().toLowerCase();
297         try {
298             if (filename.endsWith(".xls")) {
299                 readHSSFWorkbook(file);
300                 //workbook = WorkbookFactory.create(file);
301             } else if (filename.endsWith(".xlsx")) {
302                 readXSSFWorkbook(file);
303             } else {
304                 throw new IllegalArgumentException("Ausnahmefehler: Die übergebene Datei " + file.getName() + " ist keine valide Microsoft Excel-Datei (*.xls bzw. *.xlsx)!");
305             }
306         } catch (IOException ioe) {
307             Logger.getLogger(ExcelFileProxy.class.getName()).log(Level.SEVERE, null, ioe);
308             throw new IOException("Ausnahmefehler: Die Datei " + file.getName() + " konnte nicht eingelesen werden!");
309         } catch (EncryptedDocumentException ede) {
310             Logger.getLogger(ExcelFileProxy.class.getName()).log(Level.SEVERE, null, ede);
311             throw new EncryptedDocumentException("Ausnahmefehler: Die Datei " + file.getName() + " ist mit einem Passwort geschützt und kann daher nicht geöffnet werden!");
312         } catch (NoClassDefFoundError err) {
313             System.err.println(err.toString());
314             throw new ClassNotFoundException("Schwerer Ausnahmefehler: Klassendefinition nicht gefunden!\nPrüfen Sie die Programmdateien und externen Bibliotheken auf Vollständigkeit!");
315         }
316 
317     }
318 
319     private void readHSSFWorkbook(File file) throws IOException {
320         POIFSFileSystem fs = new POIFSFileSystem(file);
321         workbook = new HSSFWorkbook(fs.getRoot(), true);
322         fs.close();
323     }
324 
325     private void readXSSFWorkbook(File file) throws InvalidFormatException, IOException {
326         OPCPackage pkg = OPCPackage.open(file);
327         workbook = new XSSFWorkbook(pkg);
328         pkg.close();
329     }
330 
331     //Schreibt eine Arbeitsmappe in eine Exceldatei
332     private void createExcelFile(File file) throws FileNotFoundException, IOException {
333         try (FileOutputStream fileOut = new FileOutputStream(file)) {
334             workbook.write(fileOut);
335         } catch (FileNotFoundException fnfe) {
336             throw new IOException("Ausnahmefehler: Die Datei " + file.getName() + " konnte nicht erzeugt werden!");
337         } catch (IOException ioe) {
338             throw new IOException("Ausnahmefehler: Die Datei " + file.getName() + " konnte nicht erzeugt werden!");
339         }
340     }
341 
342     //Liest die Daten aus einer Zelle und gibt sie zurück.
343     private Object getData(Cell cell) {
344         if (cell != null) {
345 
346             switch (cell.getCellType()) {
347                 case STRING:
348                     String string = cell.getRichStringCellValue().getString();
349                     if (string.isEmpty()) {
350                         return null;
351                     } else {
352                         isEmptyRow = false;
353                         return string.trim();
354                     }
355                 case NUMERIC:
356                     if (DateUtil.isCellDateFormatted(cell)) {
357                         Date date = cell.getDateCellValue();
358                         if (date.toString().isEmpty()) {
359                             return null;
360                         } else {
361                             isEmptyRow = false;
362                             return date;
363                         }
364                     } else {
365                         Number number = cell.getNumericCellValue();
366                         if (number.toString().isEmpty()) {
367                             return null;
368                         } else {
369                             isEmptyRow = false;
370                             return number;
371                         }
372                     }
373 
374                 case BOOLEAN:
375                     Boolean bool = (cell.getBooleanCellValue());
376                     if (bool.toString().isEmpty()) {
377                         return null;
378                     } else {
379                         isEmptyRow = false;
380                         return bool;
381                     }
382 
383                 case _NONE:
384                 case FORMULA:
385                 case BLANK:
386                 case ERROR:
387                 default:
388                     return null;
389             }
390         } else {
391             return null;
392         }
393     }
394 }

Beispiel-Code für die Benutzung dieser Klasse

Erläuterungen zum Code

Baustelle.png Dieser Beitrag wird derzeit noch bearbeitet. Der Text ist deshalb unvollständig und kann Fehler oder ungeprüfte Aussagen enthalten.


Fragen

Das Thema wurde nicht ausreichend behandelt? Du hast Fragen dazu und brauchst weitere Informationen? Lass Dir von uns helfen!

Wir helfen dir gerne!


Dir hat dieser Artikel gefallen? Oder Du hast Fehler entdeckt und möchtest zur Berichtigung beitragen? Prima! Schreibe einen Kommentar!

Du musst angemeldet sein, um einen Kommentar abzugeben.

Abgerufen von „https://wiki.byte-welt.net/mediawiki/index.php?title=Apache_POI_-_Excel-Datei_einlesen&oldid=9126“