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

Aus Byte-Welt Wiki
Zur Navigation springenZur Suche springen
K
Zeile 2: Zeile 2:
 
Mit dem Java-[[Framework]] Apache POI kann man recht komfortabel Microsoft 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 mit diesem 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.
 
  *
 
  *
 
  * @author Gernot Segieth
 
  * @author Gernot Segieth
 
  */
 
  */
public class ExcelFileReader {
+
public class ExcelFileProxy {
     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 42: Zeile 91:
 
     * es Probleme mit dem Format der Datei oder der Verschlüsselung gibt
 
     * es Probleme mit dem Format der Datei oder der Verschlüsselung gibt
 
     */
 
     */
     public ExcelFileReader(File excelFile) throws ClassNotFoundException, IOException, InvalidFormatException {
+
     public ExcelFileProxy(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 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:
 +
                    String string = cell.getRichStringCellValue().getString();
 +
                    if (string.isEmpty()) {
 +
                        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;
 +
                        }
 +
                    }
  
            if (row != null) {
+
                 case BOOLEAN:
                List<Object> rowData = new ArrayList<>();
+
                    Boolean bool = (cell.getBooleanCellValue());
                boolean isEmptyRow = true; //Zeile erstmal als leer ansehen
+
                    if (bool.toString().isEmpty()) {
 
+
                        return null;
                 for (Cell cell : row) {
 
 
 
                    if (cell != 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}}

Version vom 25. Juni 2019, 14:49 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 mit diesem 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 ExcelFileProxy {
 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 ExcelFileProxy(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.