POI .xlsx - Einlesen leerer Zellen, Datumszellen

[Andreas Jaeger]

Ich versuche gerade, Excel-Dateien zu lesen und experimentiere mit POI.
Mein Code lautete ungefähr:

XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet = wb.getSheetAt(0);
for (int i = 0; i < sheet.getLastRowNum(); i++) {
XSSFRow row = sheet.getRow(i);
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j, Row.RETURN_BLANK_AS_NULL);
int type = cell.getCellType();
System.out.print("[" + type + "]");
if (type == 1) {
System.out.print(cell.getStringCellValue());
} else if (type == 0) {
System.out.print(cell.getNumericCellValue());
}
System.out.print(" *** ");
}
System.out.println();
}

1.
Wenn ich auf Zellen mit leerem Inhalt cell.getNumericCellValue()
aufrufe, kommt:
java.lang.IllegalStateException: Cannot get a numeric value from a text cell
Wenn ich auf den gleichen Zellen cell.getStringCellValue() aufrufe, kommt:
java.lang.IllegalStateException: Cannot get a text value from a numeric cell

Das empfinde ich als Beleidigung. Hat schon wer einen Umgang damit
gefunden, womöglich gar erfolgreich eine leere Zelle identifiziert, ohne
deren Wert auslesen zu müssen?

2.
Es gibt auch Datumsformatierungen in einigen meiner Zellen. Wie bekomme
ich heraus, wie der numerische Wert in einer Zelle zu interpretieren
ist? Es gibt keine Variante Cell.CELL_TYPE_DATE. Kommt man irgendwie an
die Formatierung der Zellen, wie sie über den Format->Cell-Dialog in
Excel selbst zu besichtigen ist?

3.
Gibt's womöglich eine andere Bibliothek, die ich benutzen könnte?

Grüße, Andreas

[Thorben Ruthke]

Post by Andreas Jaeger
Ich versuche gerade, Excel-Dateien zu lesen und experimentiere mit POI.
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet = wb.getSheetAt(0);
for (int i = 0; i < sheet.getLastRowNum(); i++) {
  XSSFRow row = sheet.getRow(i);
  for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
    Cell cell = row.getCell(j, Row.RETURN_BLANK_AS_NULL);
    int type = cell.getCellType();
    System.out.print("[" + type + "]");
    if (type == 1) {
      System.out.print(cell.getStringCellValue());
    } else if (type == 0) {
      System.out.print(cell.getNumericCellValue());
    }
    System.out.print(" *** ");
  }
  System.out.println();
}
1.
Wenn ich auf Zellen mit leerem Inhalt cell.getNumericCellValue()
java.lang.IllegalStateException: Cannot get a numeric value from a text cell
java.lang.IllegalStateException: Cannot get a text value from a numeric cell
Das empfinde ich als Beleidigung. Hat schon wer einen Umgang damit
gefunden, womöglich gar erfolgreich eine leere Zelle identifiziert, ohne
deren Wert auslesen zu müssen?
2.
Es gibt auch Datumsformatierungen in einigen meiner Zellen. Wie bekomme
ich heraus, wie der numerische Wert in einer Zelle zu interpretieren
ist? Es gibt keine Variante Cell.CELL_TYPE_DATE. Kommt man irgendwie an
die Formatierung der Zellen, wie sie über den Format->Cell-Dialog in
Excel selbst zu besichtigen ist?
3.
Gibt's womöglich eine andere Bibliothek, die ich benutzen könnte?
Grüße, Andreas
--
Andreas Jaeger
MMF GmbH, Herdecke, Germany

Moin Andreas,

1. in der API steht zu getNumericCellValue() :

Get the value of the cell as a number. For strings we throw an
exception. For blank cells we return a 0. See HSSFDataFormatter for
turning this number into a string similar to that which Excel would
render this number as.

zu getStringCellValue()

get the value of the cell as a string - for numeric cells we throw an
exception. For blank cells we return an empty string. For formulaCells
that are not string Formulas, we throw an exception

so da beide Fälle Exception schmeißen kann laut api nur noch ein Fall
passen:

Cells can be numeric, formula-based or string-based (text)

Formular Basierend:
zu 1 und 2. http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDataFormatter.html

3. Gibt es bestimmt, ich bin aber in dem Thema nicht so tief drin.
Wohlmöglich über OpenOffice API!? Ich denke aber das poi das
eigentlich packen sollte.
Man muss sich ab und zu aber nunmal in die API einlesen :)

Gruß
Thorben

[Andreas Jaeger]

Post by Thorben Ruthke

Post by Andreas Jaeger
java.lang.IllegalStateException: Cannot get a numeric value from a text cell
java.lang.IllegalStateException: Cannot get a text value from a numeric cell

zu getStringCellValue()

(Zitate aus dem API)
Ich bin mir nicht sicher, ob Du mein Problem verstanden hast, ich
verstehe jedenfalls die Antwort nicht. Offenbar erzählen die
POI-Entwickler die Unwahrheit. Ich habe leere Zellen, und da kommt
definitiv kein leerer String zurück und auch keine 0. Formeln sind in
dem Excel-Sheet gar nicht enthalten. Jedenfalls ist es ja wohl
widersprüchlich, wenn beim Zugriff als vermutete numerische Zelle
behauptet wird, sie sei eine "text cell", und dann beim Zugriff als
vermutete Textzelle, sie sei eine "numeric cell".

Post by Thorben Ruthke
zu 1 und 2. http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDataFormatter.html

Dieser Verweis nützt mir so noch nicht viel. Ich bräuchte ein kurzes
Beispiel, wie man von einer Zelle (oder deren row/col-Indices) auf die
derzeit gesetzte Formatierug kommt.

Andreas

[Thorben Ruthke]

Post by Andreas Jaeger

Post by Thorben Ruthke

Post by Andreas Jaeger
java.lang.IllegalStateException: Cannot get a numeric value from a text cell
java.lang.IllegalStateException: Cannot get a text value from a numeric cell

zu getStringCellValue()

(Zitate aus dem API)
Ich bin mir nicht sicher, ob Du mein Problem verstanden hast, ich
verstehe jedenfalls die Antwort nicht. Offenbar erzählen die
POI-Entwickler die Unwahrheit. Ich habe leere Zellen, und da kommt
definitiv kein leerer String zurück und auch keine 0. Formeln sind in
dem Excel-Sheet gar nicht enthalten. Jedenfalls ist es ja wohl
widersprüchlich, wenn beim Zugriff als vermutete numerische Zelle
behauptet wird, sie sei eine "text cell", und dann beim Zugriff als
vermutete Textzelle, sie sei eine "numeric cell".

Post by Thorben Ruthke
zu 1 und 2.http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDataF...

Dieser Verweis nützt mir so noch nicht viel. Ich bräuchte ein kurzes
Beispiel, wie man von einer Zelle (oder deren row/col-Indices) auf die
derzeit gesetzte Formatierug kommt.
Andreas
--
Andreas Jaeger
MMF GmbH, Herdecke, Germany

Also konkret:

InputStream inp = new FileInputStream("input.xls");

Workbook wb = WorkbookFactory.create(inp);

Sheet sheet = wb.getSheetAt(0);

// Ist wohl klar bis hier

// Row holen... wenn null (weil vorher noch nicht gesetzt!) neu
anlegen
Row row = sheet.getRow(2);
if (row == null) {
row = sheet.createRow(2);
}

// Das gleiche mit cell
Cell cell = row.getCell(3);

if (cell == null) {
cell = row.createCell(3);
} else {

// Die einzelnen Typen abfragen, z.B.:
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
System.out.println(cell.getStringCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
System.out.println(cell.getNumericCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
System.out.println("Nix Drin");
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
System.out.println("Zelle Kaputt");
} // usw...
}

// Nun nochmal einen String in eine Zelle schreiben
// Dazu den CELL TYPE Setzen
cell.setCellType(Cell.CELL_TYPE_STRING);

// String definieren
cell.setCellValue("test123");

FileOutputStream fileOut = new FileOutputStream("output.xls");

// Und ab dafür ;)
wb.write(fileOut);
fileOut.close();

Code gibts ausserdem genug im Netz und es ist schon hilfreich die API
zu lesen und nicht nur zu überfliegen (Es ist natürlich immer einfach
nach Code zu fragen aber begreifen tut man das dann doch nur wenn man
sich der API stellt :P).
Der API Link den ich mit geschickt hab, ist für die Formatierung
zuständig. Damit können u.a. Daten (Datum) geparsed werden...

Viel Spaß dabei
Gruss
Thorben

[Florian Schaetz]

And thus, Andreas Jaeger wrote...

Post by Andreas Jaeger
Wenn ich auf Zellen mit leerem Inhalt cell.getNumericCellValue()

Also ich habs gerade mal ausprobiert, bei mir kommen die leeren Zellen
als null zurück, wie das RETURN_BLANK_AS_NULL auch so dezent andeutet.
Wenn ich statt dessen Row.RETURN_NULL_AND_BLANK mache, bekomme ich für
leere Zellen den type CELL_TYPE_BLANK.

Gruß,

Flo

[Andreas Jaeger]

Post by Florian Schaetz
Also ich habs gerade mal ausprobiert, bei mir kommen die leeren Zellen
als null zurück, wie das RETURN_BLANK_AS_NULL auch so dezent andeutet.
Wenn ich statt dessen Row.RETURN_NULL_AND_BLANK mache, bekomme ich für
leere Zellen den type CELL_TYPE_BLANK.

Offenbar ist POI nicht imstande, korrekt .xls(x)-Files zu lesen, die von
SPSS ausgeschrieben werden. Jetzt müßte man sicher prüfen, ob das an
SPSS oder an POI liegt. Jedenfalls kann ich das von Dir beschriebene
Verhalten nachvollziehen, wenn ich die von SPSS exportierten Dateien
nochmal nach Excel lade und dann neu speichere. Freilich nützt mir das
in der Praxis nichts, da ich den Anwendern nicht erzählen kann, sie
mögen bitte solch ein salto mortale durchführen.

Wenn ich mal die These aufstelle, dass das Problem nur auftritt, wenn
die Zellen leer sind, könnte ich von Exception auf leere Zelle
schließen. Bliebe dann aber immmer noch das Problem, dass ich auch aus
den Antwortversuchen von Thorben nicht entnehmen kann, wie man
herauskriegt, ob in einer Zelle eine Zahl oder ein Datum oder sonstwas
steckt.

Grüße, Andreas

[Florian Schaetz]

And thus, Andreas Jaeger wrote...

Post by Andreas Jaeger
Bliebe dann aber immmer noch das Problem, dass ich auch aus
den Antwortversuchen von Thorben nicht entnehmen kann, wie man
herauskriegt, ob in einer Zelle eine Zahl oder ein Datum oder sonstwas
steckt.

Das kriegst du über...

short format = cell.getCellStyle().getDataFormat();
String name = HSSFDataFormat.getBuiltinFormat(format);

Und ich hab insgesamt nur ca. 5 Minuten in POI investiert bisher :-)

Gruß,

Flo

[Thorben Ruthke]

Post by Andreas Jaeger

Post by Florian Schaetz
Also ich habs gerade mal ausprobiert, bei mir kommen die leeren Zellen
als null zurück, wie das RETURN_BLANK_AS_NULL auch so dezent andeutet.
Wenn ich statt dessen Row.RETURN_NULL_AND_BLANK mache, bekomme ich für
leere Zellen den type CELL_TYPE_BLANK.

Offenbar ist POI nicht imstande, korrekt .xls(x)-Files zu lesen, die von
SPSS ausgeschrieben werden. Jetzt müßte man sicher prüfen, ob das an
SPSS oder an POI liegt. Jedenfalls kann ich das von Dir beschriebene
Verhalten nachvollziehen, wenn ich die von SPSS exportierten Dateien
nochmal nach Excel lade und dann neu speichere. Freilich nützt mir das
in der Praxis nichts, da ich den Anwendern nicht erzählen kann, sie
mögen bitte solch ein salto mortale durchführen.
Wenn ich mal die These aufstelle, dass das Problem nur auftritt, wenn
die Zellen leer sind, könnte ich von Exception auf leere Zelle
schließen. Bliebe dann aber immmer noch das Problem, dass ich auch aus
den Antwortversuchen von Thorben nicht entnehmen kann, wie man
herauskriegt, ob in einer Zelle eine Zahl oder ein Datum oder sonstwas
steckt.
Grüße, Andreas
--
Andreas Jaeger
MMF GmbH, Herdecke, Germany

Moin Andreas,

also als erstes es funktioniert einwandfrei.
Wie ich schon oben gesagt hab ist es wichtig sich mit der API vertraut
zu machen.

So ich hab hier mal den Fall nachgestellt und und eine Möglichkeit für
das Datum erstellt:

XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("test.xlsx"));

XSSFSheet sheet = wb.getSheetAt(wb.getSheetIndex("Tabelle1"));

...
for (Iterator iterator = row.iterator(); iterator.hasNext();) {

XSSFCell cell = (XSSFCell) iterator.next();

int type = cell.getCellType();

System.out.print("[" + type + "]");

if (type == Cell.CELL_TYPE_STRING ) {
// Wenn kein Datums format vorhanden dann kommts hier als
String
System.out.print("String Wert: " + cell.getStringCellValue());

} else if (type == Cell.CELL_TYPE_NUMERIC) {


//
**********************************************************************************
// Das Datum kommt als numerischer Wert! Ich prüfe mit
folgender Abfrage ob die Zelle einen Style hat der ein DataFormat
String zurück wirft.
// Wie gesagt ist eine Möglichkeit, nicht unbedingt die beste
aber ein Beispiel.
// Wenn zutrifft dann ist es ein Datums-Format // Das
Datumsformat ist ja in der Zelle als Attribut festgelegt. Ist es kein
Datums Format
// würde es sich sicherlich als String wiederfinden.
if
(cell.getCellStyle().getDataFormatString().equalsIgnoreCase("m/d/yy"))
{

// Dann gib mir das Datum aus
System.out.println("Datum ist: " + cell.getDateCellValue());

} else {

// Andern falls scheint es z.B. eine Zahl zu sein
System.out.println("Numerischer Wert: " +
cell.getNumericCellValue());

}


} else {

...


Ich hab unter einfach nur das Datum 11.09.2011 eingetragen, und diese
Zelle als Datumszelle mit dem Format "m/d/yy" deklariert.

Ausgabe ist:
[0]Datum ist: Sun Sep 11 00:00:00 CEST 2011

Das hat nix mit Magic zu tun sondern nur mit verstehen :)

Viel Spaß
Gruß
Thorben