Apache POI - Excel-Datei einlesen

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:

• Apache License 2.0 (Original)
• Apache License 2.0 (Deutsche Übersetzung)

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 }

Erläuterungen zum Code

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