Wie Sie Java für das automatisierte Berichtswesen über Excel verwenden

von Aug 12, 2020Einblicke, How to

Wissen Sie, welche Technologie die medizinische Forschung, das Financial Engineering, die Eisenbahn, die Banken, unterschiedliche Dienstleistungen (und im Grunde genommen jeder Bereich, den Sie sich vorstellen können) gemeinsam haben? Wenn Sie an „Microsoft Excel“ denken, dann liegen Sie richtig: Das „grüne X“ ist der gemeinsame Nenner für alle, die ein Berichtswesen brauchen, unabhängig von der Branche.

Ja, wir wissen, Excel kann langweilig sein. Es steckt kaum etwas Spektakuläres in Datenzeilen und -spalten, Zellformeln, Linien- und Balkendiagrammen, Tortendiagrammen oder was auch immer, Excel noch alles kann – es ist alles langweilig. Wir sind aber der festen Überzeugung, dass Sie wenig Software in Ihrem Werkzeugkasten finden, die so leistungsfähig und so einfach zu bedienen ist wie Excel.

Besonders bei der Arbeit mit vielen Daten macht Excel das Ordnen, Filtern, Berechnen, Visualisieren und Berichten recht einfach. Sogar das Erweitern auf hohe Datenmengen^2 und das Hinzufügen von Automatisierungskomponenten sind relativ einfach: Indem Sie Excel einfach mit Java verbinden, erledigt das Programm die harte Arbeit des Datendurchsuchens und das Bereitstellen von sauberen Blättern von alleine.

Nun dann, los geht’s: Wenn Sie Unternehmer/in, C-Suit Executive, Produktmanager/in, Finanz-„Ingenieur/in“ oder in irgendeiner anderen Position sind, die mit Reporting (und/oder Datenbanken) zu tun hat, sollten Sie mit Ihrem Software-Entwicklungsteam darüber sprechen, wie die Leistungsfähigkeit von Java bei der Automatisierung der Leistungsfähigkeit von Excel genutzt werden kann.

(Außerdem: Beachten Sie bitte, dass Sie – obwohl wir uns in der Folge nur noch auf Excel beziehen werden) – mit derselben Technologie auch Word automatisieren können).

Wie funktioniert es?

Der gesamte Weg von „Java zu Excel“ besteht aus insgesamt vier Schritten:
  • Bringen Sie Ihre Daten (/Datenbank) in Ordnung;
  • Verwenden Sie Apache POI („die Java-API für Microsoft-Dokumente“), um Ihre Daten mit Java zu integrieren;
  • Lassen Sie Java die Dinge, die Sie benötigen, auswählen, berechnen und formatieren (siehe Funktionen und Code-Beispiele weiter unten);
  • Bericht an Excel (möglicherweise: „in Excel herunterladen“, wenn Sie alles in einem Webbrowser machen).
Bevor wir zu den technischen Einzelheiten kommen, möchten wir daran erinnern, dass Apache POI „ein Projekt der Apache Software Foundation und zuvor ein Unterprojekt des Jakarta-Projekts ist, [that] reine Java-Bibliotheken zum Lesen und Schreiben von Dateien in Microsoft Office-Formaten wie Word, PowerPoint und Excel bereitstellt“. (lt. Wikipedia) Zurück zu Apache POI: Es kann zwar nicht Ihre Wäsche waschen, aber abgesehen davon gibt es eine Menge Dinge, die es für Sie erledigen kann:
  • Schriftarten bis auf Zellebene
  • Farben (Hintergrund und Text) bis auf Zellebene
  • Schattierung bis auf Zellenebene
  • Zellmuster bis auf Zellebene
  • Zell-Initialisierung
  • Fenster fixieren
  • Kennwörter
  • Abbildungen/Fotos sowohl statisch als auch dynamisch
  • Überschriften
  • Seitenumbrüche
  • Blattumbrüche
  • Einfügen von Text und vieles mehr
  • Funktionen/Formeln
  • Zellen verbinden
  • Zeilenhöhe
  • Ausrichtung von Zelltext
  • Textdrehung
  • Arbeitsblatt per E-Mail senden
  • Die Arbeitsblätter können archiviert werden, so dass historische Arbeitsblätter abgerufen werden können.

Details

Unsere Erfahrung mit dem Apache POI – Java – Excel-Ökosystem sind positiv, da wir regelmäßig komplexe Excel-Dateien erstellen. Lassen Sie uns einige (nicht alle) spezifische Funktionen anschauen, um Ihnen zu zeigen, wie einfach es ist:

1. Erstellen einer Excel-Datei

Der Apache POI funktioniert mit allen Excel-Elementen: der Datei selbst, den Arbeitsblättern, Zeilen und Spalten usw;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("New Sheet");
final File excelFile = new File("myExcelFile.xls");
FileOutputStream fileOut = new FileOutputStream(excelFile, false);
workbook.write(fileOut);
workbook.close();

2. Größe ändern und bestücken

Es ist auch möglich, die Spalten- und Zeilengrößen entsprechend den Daten zu ändern. Es ist einfach, Zeilen, Spalten und Zellen zu erstellen und sie mit Daten zu füllen:
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("Text value");

3. Stil-Bearbeitung

Mit Apache POI können Sie die Bearbeitung auf Zellenebene durchführen:

  • die Hintergrundfarbe der Zellen einstellen,
  • Art und Farbe der Rahmenlinien,
  • Schriftart, Schriftgröße oder Farbe,
  • Designs definieren und nach Bedarf verwenden (z.B. das gleiche Design für mehrere Zellen verwenden, ohne das Design neu zu definieren).
final HSSFFont arialRed8Font = workbook.createFont();
arialRed8Font.setFontHeightInPoints((short) 8);
arialRed8Font.setFontName( "Arial");
arialRed8Font.setColor(IndexedColors.RED.getIndex());
arialRed8Font.setBold(true);
 
final HSSFCellStyle cellWithThinBordersStyle = workbook.createCellStyle();
cellWithThinBordersStyle.setBorderBottom(BorderStyle.MEDIUM);
cellWithThinBordersStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellWithThinBordersStyle.setBorderRight(BorderStyle.MEDIUM);
cellWithThinBordersStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellWithThinBordersStyle.setBorderLeft(BorderStyle.MEDIUM);
cellWithThinBordersStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellWithThinBordersStyle.setBorderTop(BorderStyle.MEDIUM);
cellWithThinBordersStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
 
cellWithThinBordersStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
cellWithThinBordersStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellWithThinBordersStyle.setFont(arialRed8Font);
cellWithStyle.setCellStyle( cellWithThinBordersStyle);
cellWithStyle.setCellValue( "Defined cellstyle");
Berg Software - automated reporting - Java to Excel - Style editing

Natürlich können Sie, wie in unserem Beispiel, mehrere Schriftarten in einer einzigen Zelle haben:

  • der erste Teil ist [Farbe: rot], [Schriftart: Arial Black], [Schriftgrad: 10 Punkt];
  • der Rest des Textes ist [Farbe: schwarz], [Schriftart: Arial], [Schriftgrad: 8 Punkt], [bold];
HSSFFont firstFont = workbook.createFont();
firstFont.setFontHeightInPoints((short) 10);
firstFont.setFontName("Arial Black");
firstFont.setColor(IndexedColors.RED.getIndex());
firstFont.setBold(false);
 
HSSFFont secondFont = workbook.createFont();
secondFont.setFontHeightInPoints((short) 8);
secondFont.setFontName("Arial");
secondFont.setColor(IndexedColors.BLACK.getIndex());
secondFont.setBold(true);
 
final HSSFCell cellWith2fonts = firstRow.createCell(0);
final String font1Text = "Validation failed! ";
final String font2Text = " Please fill all required data!";
final String allText = font1Text + font2Text;
 
final HSSFRichTextString cellValue = new HSSFRichTextString(allText);
cellValue.applyFont(0, font1Text.length(), firstFont);
cellValue.applyFont(font1Text.length(), (allText).length(), secondFont);
 
cellWith2fonts.setCellValue(cellValue);
Berg Software - automated reporting - Java to Excel - Include comments

4. Kommentare einfügen

HSSFPatriarch hpt = sheet.createDrawingPatriarch();
HSSFComment comment = hpt.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 12, 7));
comment.setString(new HSSFRichTextString("The comment added to the cell." + System.getProperty("line.separator") + " New information..."));
newCell.setCellComment(comment);
newCell.setCellValue("This cell has a note!");

5. Formeln einfügen

Sie können programmatische, komplexe Formel-Zellen mit Werten aus anderen Zellen erstellen. Zum Beispiel,
sumCell.setCellFormula("SUM(" + columnLetter + firstRow + ":" + columnLetter + lastRow + ")");
…ergibt:
Berg Software - automated reporting - Java to Excel - Include formulas
Beispielformel:
String formula = "IF("+ columnLetter + estimationRow + "=0,0,("
        + columnLetter + cumulatedRow + "/" + columnLetter + estimationRow
        + ")*100)";
percentCell.setCellFormula(formula);
…und das Ergebnis:
Berg Software - automated reporting - Java to Excel - Include formulas part 2

6. Hyperlinks hinzufügen

Diese können auf eine Web-URL, eine Datei, eine E-Mail-Anwendung oder auf eine beliebige Zelle innerhalb der Datei (auf einem beliebigen Arbeitsblatt derselben Excel-Datei) verweisen.
HSSFCell cell = row4.createCell((short) 0);
CreationHelper createHelper = workbook.getCreationHelper();
Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
link.setAddress("https://www.berg-software.com/en/");
cell.setHyperlink(link);
cell.setCellValue("URL Link - to our company site");
 
cell = sheet.createRow(5).createCell((short) 0);
cell.setCellValue("File Link");
link = createHelper.createHyperlink(HyperlinkType.FILE);
link.setAddress("myFile.doc");
cell.setHyperlink(link);
 
cell = sheet.createRow(6).createCell((short) 0);
cell.setCellValue("Email Link");
link = createHelper.createHyperlink(HyperlinkType.EMAIL);
link.setAddress("mailto:contact@berg-software.com?" + "subject = Hyperlink");
cell.setHyperlink(link);
cell.setCellStyle(hlinkstyle);
Berg Software - automated reporting - Java to Excel - Add hyperlinks

7. Sicherheit

Sie können ein Kennwort festlegen, um ein ganzes Blatt vor Änderungen zu schützen. Auf diese Weise können Sie sicherstellen, dass Vorgänge wie das Löschen, Ausblenden und Bearbeiten von Zellinhalten nur von autorisiertem Personal durchgeführt werden können. Bei Bedarf können jedoch einige Zellen ganz entsperrt werden.
protected_sheet.protectSheet( "mypassword");
 
 
CellStyle unlockedCellStyle = workbook.createCellStyle();
unlockedCellStyle.setLocked(false);
 
row.createCell( 2).setCellValue("Cell value protected !");
 
final HSSFCell oneCell = row.createCell(4);
oneCell.setCellValue("This cell value can be changed !");
oneCell.setCellStyle(unlockedCellStyle);
Berg Software - automated reporting - Java to Excel - Security

8. Fenster fixieren

Sie können die ersten Zeilen oder die ersten Spalten fixieren (d.h. sowohl vertikales als auch horizontales Fixieren ist möglich). Zum Beispiel:
sheet3.createFreezePane( 2, 2 );

9. Reduzierende Gruppe für Spalten oder Zeilen

Das ist auch sehr einfach zu machen:
sheet.groupColumn(2, 5);
sheet.groupRow(7, 9);

10. Zellvalidierung

Es ist möglich, Datenvalidierungszellen zu setzen. Im Beispiel unten verwenden wir eine beschreibbare Zelle, um zu prüfen, ob der Benutzer einen der Werte 10, 20 oder 30 eingestellt hat, und wir lassen nicht zu, dass ein anderer Wert eingestellt wird. Der Benutzer wird über eine Eingabeaufforderung (die ebenfalls entweder definiert oder voreingestellt sein kann) informiert.
Die Standardmeldung:
Berg Software - automated reporting - Java to Excel - Cell validation default
Es kann mit einer einzigen Zeile Code angepasst werden:
sheet.createRow(25).createCell((short) 3).setCellValue("10");
CellRangeAddressList addressList = new CellRangeAddressList(
        25, 25, 3, 3);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
        new String[]{"10", "20", "30"});
 
DataValidation dataValidation = new HSSFDataValidation
        (addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.createErrorBox("Validation Error", "The accepted values are: 10, 20 and 30!");
 
sheet.addValidationData(dataValidation);
Berg Software - automated reporting - Java to Excel - Cell validation customised

11. Kopf- und Fußzeilen zu erstellen ist nicht schwierig

Sie können Überschriften links, zentriert oder rechts (oder alle) mit benutzerdefinierten Schriftarten festlegen:
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
 HSSFHeader.fontSize((short) 16) + "Right Header - special font");
 
sheet.getFooter().setCenter( "Center Footer");
sheet.getFooter().setLeft( "Left Footer");
sheet.getFooter().setRight("Right Footer");
Das Ergebnis:
Berg Software - automated reporting - Java to Excel - Header and footer

12. Zellen verbinden

Sie benötigen nur einen einzigen Befehl, um den Bereich der zu verbindenden Zellen anzugeben:
sheet.addMergedRegion(new CellRangeAddress(firstRowNumber, lastRowNumber, mergedCellFirstCol, mergedCellLastCol));

13. Grafiken

Apache POI kann bei der Erstellung verschiedener Diagramme (z.B. Linien-, Balken-, Tortendiagramme usw.) einschließlich ihrer 3D-Varianten helfen:

Einschränkungen

Wahrscheinlich haben Sie inzwischen herausgefunden, dass Sie beliebige Excel-Funktionalitäten über Java/Apache POI ausführen können. Wenn jedoch zwei spezielle Einstellungen für den gleichen Zellensatz gelten, kann es Einschränkungen geben.

Zum Beispiel:

  • Sie können ein Bild hinzufügen oder Zellen ausblenden/zuklappen. Wenn Sie jedoch beides gleichzeitig tun (ein Bild in eine Zeile einfügen, die später zugeklappt wird), wird das Bild nicht vollständig angezeigt. Als Abhilfe können Sie den Bildanker mit der Anzahl der sichtbaren Zeilen/Spalten setzen – so bleibt das Bild vollständig sichtbar, unabhängig davon, ob Zellen erweitert oder zugeklappt werden.
  • Wenn einklappbare Spalten verwendet werden, kann es schwierig sein, den Kommentaranker anzupassen.
  • Bei Verwendung von HSSF gibt es eine Begrenzung auf 256 Spalten pro Blatt.

Bei der Arbeit mit großen Datensätzen, die einen beliebigen Automatisierungsgrad erfordern, empfehlen wir dringend die Verwendung von Java zum Erstellen, Bearbeiten, Berechnen und Herunterladen der zugehörigen Excel-Dateien. Für die meisten Funktionen benötigen Sie nur wenige Zeilen Code. Danach erweist sich eine Excel-Datei, an der Sie weiter herumbasteln können, als unbezahlbar.

Benötigen Sie weitere Ideen oder Anleitungen für den Weg „von Java zu Excel“? Dann lassen Sie es uns wissen!

29 Jahre im Geschäft | 2700 Software-Projekte | 760 Kunden | 24 Länder

Wir verwandeln Ideen in Software. Wie lautet Ihre Idee?

Kontakt aufnehmen

11 + 8 =