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

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?

• 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).

• 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

1. Erstellen einer Excel-Datei

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

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");

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);

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

sumCell.setCellFormula("SUM(" + columnLetter + firstRow + ":" + columnLetter + lastRow + ")");

String formula = "IF("+ columnLetter + estimationRow + "=0,0,("
        + columnLetter + cumulatedRow + "/" + columnLetter + estimationRow
        + ")*100)";
percentCell.setCellFormula(formula);

6. Hyperlinks hinzufügen

HSSFCell cell = row4.createCell((short) 0);
CreationHelper createHelper = workbook.getCreationHelper();
Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
link.setAddress("https://berg-software.com/");
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@bergsoftprod.wpengine.com?" + "subject = Hyperlink");
cell.setHyperlink(link);
cell.setCellStyle(hlinkstyle);

7. Sicherheit

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);

8. Fenster fixieren

sheet3.createFreezePane( 2, 2 );

9. Reduzierende Gruppe für Spalten oder Zeilen

sheet.groupColumn(2, 5);
sheet.groupRow(7, 9);

10. Zellvalidierung

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);

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

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");

12. Zellen verbinden

sheet.addMergedRegion(new CellRangeAddress(firstRowNumber, lastRowNumber, mergedCellFirstCol, mergedCellLastCol));

13. Grafiken

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.

—

—

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