| « Objektorientiert programmieren in PHP und Java: 4 Tutorial-Links | Pentaho/Kettle - schrittweise » |
Apache POI / HSSF und Formeln in Excel
Apache POI / HSSF eignet sich wunderbar, um mit Java Excel-Dateien zu erzeugen. Werte, Formatierungen und auch Formeln sind möglich - letzteres ist aber nicht ganz einfach.
Auf der HSSF Website findet sich der Satz “…the string that you pass in to the setCellFormula call should be what you expect to type into excel.". Ist aber nicht so. Intern arbeitet Excel mit einer ganz anderen Formelnotation als auf der (deutschen) Benutzeroberfläche. Eine Formel, die man in Excel so eingeben würde:
=WENN(A8>HEUTE();A5;SUMME(B1:B3))
sieht für Excel eigentlich so aus:
IF(ATTR(semiVolatile)>TODAY(A8),A5,SUM(B1:B3))
Und auf diese Weise muss man auch HSSF anweisen, Formeln zu erzeugen.
Um das zu vereinfachen, habe ich ein kleines Hilfsprogramm gebaut…
Fortsetzung:
Um die interne Schreibweise einer Formel herauszufinden, erzeugt man die Formel am besten direkt in Excel. Dann ist man auch sicher, dass sie funktioniert.
Anschließend muss man sich nur merken, in welches Feld man die Formel eingetragen hat und startet den POI Formel Finder.
Die Anwendung ist sehr einfach: Man wählt eine vorhanden Excel-Datei aus, danach eines der enthaltenen Arbeitsblätter und gibt noch das Feld an, in dem die Formel steht. Mit “Formel ermitteln” erscheint die übersetzte Formel im unteren Textfeld.
In diesem Fall steht im Feld A8 der Exceldatei die Formel “=HEUTE()". Daraus wird dann “TODAY(ATTR(semiVolatile))".
Und hier der Java-Programmcode:
Code:
/* | |
* Hauptfenster.java | |
* | |
* Created on 30. Mai 2008, 08:29 | |
*/ | |
package de.jrthies.poiformelfinder; | |
| |
import java.io.File; | |
import java.io.FileInputStream; | |
import javax.swing.JFileChooser; | |
import javax.swing.UIManager; | |
import javax.swing.filechooser.FileFilter; | |
import org.apache.poi.hssf.usermodel.HSSFCell; | |
import org.apache.poi.hssf.usermodel.HSSFRow; | |
import org.apache.poi.hssf.usermodel.HSSFSheet; | |
import org.apache.poi.hssf.usermodel.HSSFWorkbook; | |
import org.apache.poi.hssf.util.CellReference; | |
| |
/** | |
* Klasse zur Erzeugung des Programmfensters | |
* @author Jean-Rene Thies | |
*/ | |
public class Hauptfenster extends javax.swing.JFrame { | |
| |
FileInputStream fis = null; | |
HSSFWorkbook wb = null; | |
| |
/** | |
* Konstruktor des Hauptfensters | |
*/ | |
public Hauptfenster() { | |
initComponents(); | |
} | |
| |
/** | |
* Arbeitsblätter in der gewählten Exceldatei ermitteln und in Klappmenü übertragen | |
* @param path Dateipfad zur Exceldatei | |
*/ | |
private void getWorksheets(String path) { | |
try { | |
fis = new FileInputStream(path); | |
wb = new HSSFWorkbook(fis); | |
int sheetcount = wb.getNumberOfSheets(); | |
System.out.println(sheetcount); | |
for (int i = 0; i < sheetcount; i++) { | |
String sheetname = wb.getSheetName(i); | |
comboArbeitsblatt.addItem(sheetname); | |
System.out.println(sheetname); | |
} | |
} catch (Exception e) { | |
e.printStackTrace(); | |
} | |
| |
} | |
| |
/** This method is called from within the constructor to | |
* initialize the form. | |
* WARNING: Do NOT modify this code. The content of this method is | |
* always regenerated by the Form Editor. | |
*/ | |
@SuppressWarnings("unchecked") | |
// <editor-fold defaultstate="collapsed" desc="Generated Code"> | |
private void initComponents() { | |
| |
jLabel1 = new javax.swing.JLabel(); | |
txtFile = new javax.swing.JTextField(); | |
btnFile = new javax.swing.JButton(); | |
jLabel2 = new javax.swing.JLabel(); | |
comboArbeitsblatt = new javax.swing.JComboBox(); | |
jLabel3 = new javax.swing.JLabel(); | |
txtFeld = new javax.swing.JTextField(); | |
jLabel4 = new javax.swing.JLabel(); | |
jScrollPane1 = new javax.swing.JScrollPane(); | |
txtFormel = new javax.swing.JTextArea(); | |
btnQuit = new javax.swing.JButton(); | |
btnFormel = new javax.swing.JButton(); | |
| |
setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE); | |
setTitle("POI Formel Finder"); | |
| |
jLabel1.setText("Pfad zur Excel-Datei:"); | |
| |
btnFile.setText("Durchsuchen..."); | |
btnFile.addActionListener(new java.awt.event.ActionListener() { | |
public void actionPerformed(java.awt.event.ActionEvent evt) { | |
btnFileActionPerformed(evt); | |
} | |
}); | |
| |
jLabel2.setText("Arbeitsblatt:"); | |
| |
jLabel3.setText("Tabellenfeld (z.B. A1)"); | |
| |
txtFeld.setText("A1"); | |
| |
jLabel4.setText("Formel:"); | |
| |
txtFormel.setColumns(20); | |
txtFormel.setFont(new java.awt.Font("Arial", 0, 11)); // NOI18N | |
txtFormel.setRows(5); | |
jScrollPane1.setViewportView(txtFormel); | |
| |
btnQuit.setText("Beenden"); | |
btnQuit.addActionListener(new java.awt.event.ActionListener() { | |
public void actionPerformed(java.awt.event.ActionEvent evt) { | |
btnQuitActionPerformed(evt); | |
} | |
}); | |
| |
btnFormel.setText("Formel ermitteln"); | |
btnFormel.addActionListener(new java.awt.event.ActionListener() { | |
public void actionPerformed(java.awt.event.ActionEvent evt) { | |
btnFormelActionPerformed(evt); | |
} | |
}); | |
| |
org.jdesktop.layout.GroupLayout layout = new org.jdesktop.layout.GroupLayout(getContentPane()); | |
getContentPane().setLayout(layout); | |
layout.setHorizontalGroup( | |
layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING) | |
.add(layout.createSequentialGroup() | |
.addContainerGap() | |
.add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING) | |
.add(layout.createSequentialGroup() | |
.add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.TRAILING) | |
.add(jLabel4) | |
.add(jLabel3) | |
.add(jLabel2) | |
.add(jLabel1)) | |
.addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED) | |
.add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING) | |
.add(comboArbeitsblatt, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 137, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE) | |
.add(txtFeld, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 48, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE) | |
.add(org.jdesktop.layout.GroupLayout.TRAILING, layout.createSequentialGroup() | |
.add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.TRAILING) | |
.add(org.jdesktop.layout.GroupLayout.LEADING, jScrollPane1, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 251, Short.MAX_VALUE) | |
.add(txtFile, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 251, Short.MAX_VALUE) | |
.add(org.jdesktop.layout.GroupLayout.LEADING, btnFormel)) | |
.addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED) | |
.add(btnFile)))) | |
.add(org.jdesktop.layout.GroupLayout.TRAILING, btnQuit)) | |
.addContainerGap()) | |
); | |
layout.setVerticalGroup( | |
layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING) | |
.add(layout.createSequentialGroup() | |
.addContainerGap() | |
.add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE) | |
.add(jLabel1) | |
.add(btnFile) | |
.add(txtFile, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)) | |
.addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED) | |
.add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE) | |
.add(jLabel2) | |
.add(comboArbeitsblatt, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)) | |
.addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED) | |
.add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE) | |
.add(jLabel3) | |
.add(txtFeld, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)) | |
.addPreferredGap(org.jdesktop.layout.LayoutStyle.UNRELATED) | |
.add(btnFormel) | |
.add(11, 11, 11) | |
.add(layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING) | |
.add(jLabel4) | |
.add(jScrollPane1, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)) | |
.addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED, 25, Short.MAX_VALUE) | |
.add(btnQuit) | |
.addContainerGap()) | |
); | |
| |
java.awt.Dimension screenSize = java.awt.Toolkit.getDefaultToolkit().getScreenSize(); | |
setBounds((screenSize.width-500)/2, (screenSize.height-300)/2, 500, 300); | |
}// </editor-fold> | |
| |
private void btnFileActionPerformed(java.awt.event.ActionEvent evt) { | |
JFileChooser fc = new JFileChooser(); | |
fc.setFileFilter(new FileFilter() { | |
| |
@Override | |
public boolean accept(File f) { | |
return f.isDirectory() || | |
f.getName().toLowerCase().endsWith(".xls"); | |
} | |
| |
@Override | |
public String getDescription() { | |
return "Excel-Dateien (*.xls)"; | |
} | |
}); | |
| |
int state = fc.showOpenDialog(null); | |
if (state == JFileChooser.APPROVE_OPTION) { | |
File file = fc.getSelectedFile(); | |
String path = file.getPath(); | |
txtFile.setText(path); | |
getWorksheets(path); | |
} | |
} | |
| |
private void btnQuitActionPerformed(java.awt.event.ActionEvent evt) { | |
System.exit(0); | |
} | |
| |
private void btnFormelActionPerformed(java.awt.event.ActionEvent evt) { | |
int index = wb.getSheetIndex(comboArbeitsblatt.getSelectedItem().toString()); | |
HSSFSheet sheet = wb.getSheetAt(index); | |
CellReference cellReference = new CellReference(txtFeld.getText()); | |
HSSFRow row = sheet.getRow(cellReference.getRow()); | |
HSSFCell cell = row.getCell(cellReference.getCol()); | |
txtFormel.setText(cell.getCellFormula()); | |
} | |
| |
/** | |
* @param args command line arguments | |
*/ | |
public static void main(String args[]) { | |
java.awt.EventQueue.invokeLater(new Runnable() { | |
| |
@Override | |
@SuppressWarnings("static-access") | |
public void run() { | |
try { | |
UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName()); | |
Hauptfenster hauptfenster = new Hauptfenster(); | |
hauptfenster.setDefaultLookAndFeelDecorated(true); | |
hauptfenster.setVisible(true); | |
} catch (Exception e) { | |
e.printStackTrace(); | |
} | |
} | |
}); | |
} | |
| |
// Variables declaration - do not modify | |
private javax.swing.JButton btnFile; | |
private javax.swing.JButton btnFormel; | |
private javax.swing.JButton btnQuit; | |
private javax.swing.JComboBox comboArbeitsblatt; | |
private javax.swing.JLabel jLabel1; | |
private javax.swing.JLabel jLabel2; | |
private javax.swing.JLabel jLabel3; | |
private javax.swing.JLabel jLabel4; | |
private javax.swing.JScrollPane jScrollPane1; | |
private javax.swing.JTextField txtFeld; | |
private javax.swing.JTextField txtFile; | |
private javax.swing.JTextArea txtFormel; | |
// End of variables declaration | |
} |
Nun gibt es leider ein paar Formeln, mit denen das nicht funktioniert. “NETTOARBEITSTAGE” zum Beispiel. Diese Funktion wird in Excel als Add-In namens “Analysefunktionen” hinzugefügt, und die oben genutzte Methode getCellFormula() liefert daraus nur “NO IDEA - NAME()".
Was tun?
Erste Möglichkeit:
OpenOffice statt Excel verwenden. 
Zweite Möglichkeit:
Die in POI nicht mögliche Funktion nachprogrammieren. Im Fall NETTOARBEITSTAGE z.B. so:
Code:
import java.util.Calendar; | |
import java.util.GregorianCalendar; | |
| |
/** | |
* Datumsberechnungen | |
* @author Jean-Rene Thies | |
*/ | |
public class Datumsberechnung { | |
| |
/** | |
* Berechnung der Werktage zwischen zwei Datumswerten, wobei der Starttermin nicht mitzählt. | |
* Bsp.: Von Mo bis Mi=2 Werktage. Von Fr bis Di=2 Werktage. | |
* @param startdate Starttermin | |
* @param enddate Endtermin | |
* @return Anzahl der Werktage | |
*/ | |
public static int anzahlWerktage(Calendar startdate, Calendar enddate) { | |
int werktage = 0; | |
long diffMillis = enddate.getTimeInMillis() - startdate.getTimeInMillis(); | |
double diffDays = Math.ceil(diffMillis / (24d * 60d * 60d * 1000d)); | |
long diff = Math.round(diffDays); | |
for (int i = 1; i <= diff; i++) { | |
Calendar check = new GregorianCalendar(); | |
check.set(startdate.get(Calendar.YEAR), startdate.get(Calendar.MONTH), startdate.get(Calendar.DAY_OF_MONTH) + i); | |
if (istWerktag(check)) { | |
werktage++; | |
} | |
} | |
return werktage; | |
} | |
| |
/** | |
* Prüfung, ob ein Datumswert ein Werktag ist | |
* @param day Datum | |
* @return Ist ein Werktag | |
*/ | |
public static boolean istWerktag(Calendar day) { | |
boolean check = true; | |
if (day.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY || day.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY) { | |
check = false; | |
} | |
return check; | |
} | |
} |
Dritte Möglichkeit:
Auf POI verzichten, die Exceldatei in HTML-Form erzeugen und einfach mit der Dateiendung .xls speichern. Das ist nicht schwer und mit jeder Programmiersprache möglich, die Textdateien erzeugen kann.
Hier kurz der Ablauf:
- Tabellenvorlage mit Formeln in Excel erzeugen, als HTML speichern.
- Die Datei mit einem Texteditor öffnen und genau anschauen.
- Änderbare Bereiche erkennen und diese per Programm erzeugen.
- Mit Programm komplette Exceldatei als HTML erzeugen und mit Endung .xls speichern. Diese Datei kann von Excel genau wie eine originäre Exceldatei geöffnet werden.
Ein Beispiel:
Code:
<html xmlns:o="urn:schemas-microsoft-com:office:office" | |
xmlns:x="urn:schemas-microsoft-com:office:excel" | |
xmlns="http://www.w3.org/TR/REC-html40"> | |
| |
<head> | |
<meta http-equiv=Content-Type content="text/html; charset=windows-1252"> | |
<meta name=ProgId content=Excel.Sheet> | |
<meta name=Generator content="Microsoft Excel 9"> | |
<link rel=File-List href="./Mappe1-Dateien/filelist.xml"> | |
<link rel=Edit-Time-Data href="./Mappe1-Dateien/editdata.mso"> | |
<link rel=OLE-Object-Data href="./Mappe1-Dateien/oledata.mso"> | |
<!--[if gte mso 9]><xml> | |
<o:DocumentProperties> | |
<o:Author>Thies</o:Author> | |
<o:LastAuthor>Thies</o:LastAuthor> | |
<o:Created>2008-05-30T14:03:21Z</o:Created> | |
<o:LastSaved>2008-05-30T14:04:01Z</o:LastSaved> | |
<o:Company></o:Company> | |
<o:Version>9.8968</o:Version> | |
</o:DocumentProperties> | |
<o:OfficeDocumentSettings> | |
<o:DownloadComponents/> | |
<o:LocationOfComponents HRef="file:\\DSV_DATA03\RESERVE\O2K\O2K_1\msowc.cab"/> | |
</o:OfficeDocumentSettings> | |
</xml><![endif]--> | |
<style> | |
<!--table | |
{mso-displayed-decimal-separator:"\,"; | |
mso-displayed-thousand-separator:"\.";} | |
@page | |
{margin:.98in .79in .98in .79in; | |
mso-header-margin:.49in; | |
mso-footer-margin:.49in;} | |
tr | |
{mso-height-source:auto;} | |
col | |
{mso-width-source:auto;} | |
br | |
{mso-data-placement:same-cell;} | |
.style0 | |
{mso-number-format:General; | |
text-align:general; | |
vertical-align:bottom; | |
white-space:nowrap; | |
mso-rotate:0; | |
mso-background-source:auto; | |
mso-pattern:auto; | |
color:windowtext; | |
font-size:10.0pt; | |
font-weight:400; | |
font-style:normal; | |
text-decoration:none; | |
font-family:Arial; | |
mso-generic-font-family:auto; | |
mso-font-charset:0; | |
border:none; | |
mso-protection:locked visible; | |
mso-style-name:Standard; | |
mso-style-id:0;} | |
td | |
{mso-style-parent:style0; | |
padding-top:1px; | |
padding-right:1px; | |
padding-left:1px; | |
mso-ignore:padding; | |
color:windowtext; | |
font-size:10.0pt; | |
font-weight:400; | |
font-style:normal; | |
text-decoration:none; | |
font-family:Arial; | |
mso-generic-font-family:auto; | |
mso-font-charset:0; | |
mso-number-format:General; | |
text-align:general; | |
vertical-align:bottom; | |
border:none; | |
mso-background-source:auto; | |
mso-pattern:auto; | |
mso-protection:locked visible; | |
white-space:nowrap; | |
mso-rotate:0;} | |
.xl24 | |
{mso-style-parent:style0; | |
mso-number-format:"Short Date";} | |
--> | |
</style> | |
<!--[if gte mso 9]><xml> | |
<x:ExcelWorkbook> | |
<x:ExcelWorksheets> | |
<x:ExcelWorksheet> | |
<x:Name>Tabelle1</x:Name> | |
<x:WorksheetOptions> | |
<x:DefaultColWidth>10</x:DefaultColWidth> | |
<x:Selected/> | |
<x:Panes> | |
<x:Pane> | |
<x:Number>3</x:Number> | |
<x:ActiveRow>2</x:ActiveRow> | |
</x:Pane> | |
</x:Panes> | |
<x:ProtectContents>False</x:ProtectContents> | |
<x:ProtectObjects>False</x:ProtectObjects> | |
<x:ProtectScenarios>False</x:ProtectScenarios> | |
</x:WorksheetOptions> | |
</x:ExcelWorksheet> | |
<x:ExcelWorksheet> | |
<x:Name>Tabelle2</x:Name> | |
<x:WorksheetOptions> | |
<x:DefaultColWidth>10</x:DefaultColWidth> | |
<x:ProtectContents>False</x:ProtectContents> | |
<x:ProtectObjects>False</x:ProtectObjects> | |
<x:ProtectScenarios>False</x:ProtectScenarios> | |
</x:WorksheetOptions> | |
</x:ExcelWorksheet> | |
<x:ExcelWorksheet> | |
<x:Name>Tabelle3</x:Name> | |
<x:WorksheetOptions> | |
<x:DefaultColWidth>10</x:DefaultColWidth> | |
<x:ProtectContents>False</x:ProtectContents> | |
<x:ProtectObjects>False</x:ProtectObjects> | |
<x:ProtectScenarios>False</x:ProtectScenarios> | |
</x:WorksheetOptions> | |
</x:ExcelWorksheet> | |
</x:ExcelWorksheets> | |
<x:WindowHeight>12270</x:WindowHeight> | |
<x:WindowWidth>18795</x:WindowWidth> | |
<x:WindowTopX>120</x:WindowTopX> | |
<x:WindowTopY>30</x:WindowTopY> | |
<x:ProtectStructure>False</x:ProtectStructure> | |
<x:ProtectWindows>False</x:ProtectWindows> | |
</x:ExcelWorkbook> | |
<x:SupBook> | |
<x:Dll/> | |
<x:ExternName> | |
<x:Name>NETTOARBEITSTAGE</x:Name> | |
<x:Macro>Function</x:Macro> | |
<x:FunctionGroup>2</x:FunctionGroup> | |
<x:Formula>=#REF!</x:Formula> | |
</x:ExternName> | |
</x:SupBook> | |
</xml><![endif]--> | |
</head> | |
| |
<body link=blue vlink=purple> | |
| |
<table x:str border=0 cellpadding=0 cellspacing=0 width=80> | |
<col width=80 style='width:60pt'> | |
<tr height=17 style='height:12.75pt'> | |
<td height=17 class=xl24 align=right width=80 style='height:12.75pt; width:60pt' x:num>03.01.2008</td> | |
</tr> | |
<tr height=17 style='height:12.75pt'> | |
<td height=17 class=xl24 align=right style='height:12.75pt' x:num>14.02.2008</td> | |
</tr> | |
<tr height=17 style='height:12.75pt'> | |
<td height=17 align=right style='height:12.75pt' x:num x:fmla="=NETTOARBEITSTAGE(A1,A2)"></td> | |
</tr> | |
<![if supportMisalignedColumns]> | |
<tr height=0 style='display:none'> | |
<td width=80 style='width:60pt'></td> | |
</tr> | |
<![endif]> | |
</table> | |
| |
</body> | |
| |
</html> |
Die eigentliche Tabelle beginnt in Zeile 147. Ich habe dort bereits einige Dinge entfernt. So sind im Original alle Datumswerte nochmal mit den internen numerischen Werten versehen. In Zeile 156 erkennt man die Formel NETTOARBEITSTAGE, die dann auch erwartungsgemäß funktioniert. Das Attribut x:num lässt Excel erkennen, dass das Feld numerisch (also berechenbar) ist und nicht nur Text enthält.
Noch kein Feedback
Kommentar-Feed für diesen EintragKommentare sind für diesen Eintrag deaktiviert.
