Mit DataStage Excel Dateien lesen – Teil 1

Mit DataStage vor Version 9.1, war es nicht möglich, Excel Dateien direkt als Input zu verwenden. Leider verwenden Business Leute recht gerne Excel zur Datenerfassung (Excel ist ein tooles Programm, aber zur Erfassung von Daten, die dann automatisiert verarbeitet werden sollen, naja), weshalb in Daten Migrationsprojekten, oft der Punkt kommt, an dem man eben diese verarbeiten muss.

Eine Abhilfe kann hier die Verwendung eine Java Stage bringen.

Denn mit Java und der Apache POI API, ist es recht einfach Microsoft Office Excel Dateien mit Java auszulesen.

Dieser Code Snippet befasst sich daher mit einer einfachen Implementierung eines Wrappers, um POI von einer Java Stage aufrufen zu können.
Die Anforderungen der frei definierbaren Rahmenbedingungen:

  • Dateiname
  • Arbeitsblatt
  • Spalten
  • Startzeile

Die ersten drei Punkte werden mit dem Excel Wrapper abgehandelt. Die Startzeile wird später von der Java Stage berücksichtigt.

package de.kissoft.datastage.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import de.kissoft.datastage.exceptions.RejectException;

/**
 * Ein Apache POI Wrapper, zum einfachen Auslesen von Excel Dateien.
 * <br/><br/>
 * Verwendung:<br/>
 * ExcelReader reader = new ExcelReader("Daten.xls", "Sheet1", "A,B");
 * <br/>List<String> zeile1 = reader.getRow(1);
 * <br/><br/>
 * Apache POI beginnt die Zählung der Zeilen mit "0", dies wird in diesem
 * Programm "überschrieben", damit die Zahlen in Excel identisch den 
 * Übergabewerten sind.
 * 
 * @author Michael Schmut
 */
public class ExcelReader {
  private int[] columns;
  private Sheet sheet;
  private SpreadsheetVersion version;

  
  /**
   * Aufruf des Excel Wrapper mit einem String als Dateiverweis.
   * 
   * @param file Dateiname des zu öffnenden Excel
   * @param sheetName Auszulesendes Arbeitsblatt in Excel
   * @param columnList Komma separierte Liste von Spalten, die gelesen 
   *                    werden sollen
   * @throws Exception Existiert die Datei nicht, ist der Spaltenbereich
   *  außerhalb des zulässigen Bereichs, existiert das Arbeitsblatt nicht
   */
  public ExcelReader(String file, String sheetName, String columnList) 
      throws Exception {
    this(new File(file), sheetName, columnList);
  }
  
  /**
   * Aufruf des Excel Wrapper mit einem String als Dateiverweis.
   * 
   * @param file zu öffnende Excel
   * @param sheetName Auszulesendes Arbeitsblatt in Excel
   * @param columnList Komma separierte Liste von Spalten, die gelesen 
   *                    werden sollen
   * @throws Exception Existiert die Datei nicht, ist der Spaltenbereich
   *  außerhalb des zulässigen Bereichs, existiert das Arbeitsblatt nicht
   */
  public ExcelReader(File excelFile, String sheetName, String columnList) 
      throws Exception {
    Workbook workbook = openWorkbook(excelFile);

    sheet = workbook.getSheet(sheetName);
    if (sheet == null) {
      throw new Exception("Das angegebene Arbeitsblatt existiert " +
          "nicht in dieser Excel Datei.");
    }

    setColumnLetters(columnList);
  }

  /**
   * Oeffnet das Excel Dokument - das Workbook
   * 
   * @param excelFile Zu oeffnende Excel Datei
   * @return Wenn die Datei korrekt geoeffnet wurde, wird das Workbook
   * 	zurueckgegeben
   * @throws Exception Existiert die Datei nicht, oder ist es kein Excel
   * 	Dokument
   */
  private Workbook openWorkbook(File excelFile) throws Exception {
    if (!excelFile.exists()) {
      throw new Exception("Die angegebene Datei '"+excelFile.getName()+
          "' existiert nicht.");
    }  if (excelFile.isDirectory()) {
      throw new Exception("Bei der angegebenen Datei '"+excelFile.getName()+
          "' handelt es sich um ein Verzeichnis.");
    }
    
    Workbook workbook = null;
    try {
      InputStream inStream = new FileInputStream(excelFile);
      
      // Hier die einfache Annahme, wenn die Endung XLSX ist, 
      // dann handelt es sich um ein Office 2010 XML Format
      if (excelFile.getName().toUpperCase().endsWith(".XLSX")) {
        workbook = new XSSFWorkbook(inStream);
        setExcelVersion(SpreadsheetVersion.EXCEL2007);
      } else {
        workbook = new HSSFWorkbook(inStream);
        setExcelVersion(SpreadsheetVersion.EXCEL97);
      }
    } catch (Exception e) {
      throw new Exception("Beim Öffnen des Excel Dokument ist ein Fehler " +
          "augetreten.", e);
    }
    return workbook;
  }
  
  /**
   * Setzen der verwendeten Excel Version
   * 
   * @param version Die verwendete Excel version (2007 oder 97)
   */
  private void setExcelVersion(SpreadsheetVersion version) {
  	this.version = version;
  }
  
  /**
   * Gibt die Excel Version zurueck
   * 
   * @return Die Excel Version
   */
  private SpreadsheetVersion getExcelVersion() {
  	return version;
  }
  
  /**
   * Zerlege die Liste an Spalten und wandle die Buchstaben in 
   * Zahlen um.
   * 
   * @param columnList Komma separierte Liste an Spalten
   * @throws Exception Ueberschreitet die Spaltenangabe den 
   * 	zulaessigen Bereich
   */
  private void setColumnLetters(String columnList) throws Exception {
    String[] columnLetters = columnList.toUpperCase().split(",");
    
    columns = new int[columnLetters.length];
    
    for (int i = 0; i < columnLetters.length; i++) {
      if (!CellReference.isColumnWithnRange(columnLetters[i], getExcelVersion())) {
        throw new Exception("Die Angegebene Spalte '"+columnLetters[i]+
            "' ist unzulässig - der letzte zulässige Wert lautet '"+
            getExcelVersion().getLastColumnName()+"'.");
      }
      columns[i] = CellReference.convertColStringToIndex(columnLetters[i]);
    }
  }

  /**
   * Ermittelt die Anzahl Zeilen, welche das Excel Dokument enthält.<br/>
   * Ein Arbeitsblatt enthält immer eine Zeile, auch wenn diese leer ist.
   * 
   * 
   * @return Anzahl Zeilen im Arbeitsblatt
   */
  public int getRowCount() {
    int rowCount = sheet.getLastRowNum();
    if (rowCount >= 0)
      rowCount++;
    return rowCount;
  }
  
  /**
   * Gibt die Werte der angeforderten Zeile zurück. Sollte die Zeile Fehler
   * enthalten, wird eine entsprechende Exception geworfen. Hier kann man
   * beliebige weitere Bedingungen für einen Reject einbauen.
   * 
   * 
   * @param rowNum Die auszulesende Zeile (identisch der Zeilenunummer in Excel
   * @return Die Werte, der auszulesenden Spalten
   * @throws RejectException Enthält die Zeile Fehler, wird sie verworfen
   */
  public List<String> getRow(int rowNum) throws RejectException {
    rowNum--;
    List<String> result = new ArrayList<String>();
    
    Row row = sheet.getRow(rowNum);
    
    for (int column : columns) {
      Cell cell = row.getCell(column);
      
      if (cell == null) {
        result.add("");
        continue;
      }
      
      int cellType = cell.getCellType();
      
      if (cellType == Cell.CELL_TYPE_ERROR) {
        throw new RejectException(CellReference.convertNumToColString(column), 
            ""+rowNum, 
            FormulaError.forInt(cell.getErrorCellValue()).getString(), 
            "Die Zeile '"+(rowNum+1)+"' enthält enthält einen Fehler und wird" +
                " daher nicht ausgelesen.");
      }
      
      cell.setCellType(Cell.CELL_TYPE_STRING);
      result.add(cell.getStringCellValue());
      
    }
    
    return result;
  }
}

Beim Aufruf werden die drei Punkte direkt dem Konstruktor übergeben. Dieser prüft, ob die Datei existiert, dass es das gesuchte Arbeitsblatt gibt und dass die Spalten im zulässigen Bereich liegen. Für die Java Stage braucht man die Methode getRowCount(), um zu ermitteln wieviele Zeilen es gibt, damit die Stage weiss, wann alle Daten ausgelesen wurden. Mit der Methode getRow() werden letztendlich die Daten ausgelesen und in String umgewandelt (ist aus Daten Sicht vielleicht nicht perfekt, da man die Typen verlieren, aber es ist schon schlimm genug, dass man sich überhaupt mit Excel in dem Rahmen beschäftigen muss ;-)). Sollte man noch mehr Prüfungen benötigen, können diese in dieser Methode ergänzt werden, z.B. falls es im Projekt die Vorgabe gibt, dass keine numerischen Werte erfasst werden sollen, sondern alles als Text zu erfassen ist, kann man dies hier prüfen und alle Zeilen zurückweisen, die der Anforderung nicht entsprechen.

Der Vollständigkeit wegen, hier noch die RejectException

package de.kissoft.datastage.exceptions;

/**
 * Exception für abgelehnte Zeilen.
 * 
 * @author Michael Schmut
 */
public class RejectException extends Exception {
  private static final long serialVersionUID = -6407737496257508581L;

  /**
   * Lege eine abzulehnende Zeile an
   * @param column Der Spaltenbuchstabe der abgelehnten Zelle
   * @param row Die Zeile, welche einen Fehler enthält
   * @param value Der Wert, auf Grund dessen die Zeile abgelehnt wurde
   * @param message Eine freitext Begründung
   */
  public RejectException(String column, String row, String value, 
      String message) {
    super(message);
  }
}

Abschließend noch die Gradle Projektdatei, mit welcher man das ganze bauen kann.

apply plugin: 'java'
apply plugin: 'eclipse'

repositories {
	mavenCentral()
}

configurations {
    poi
    poiooxml
}

dependencies {
    testCompile group: 'junit', name: 'junit', version: '4.8.+'
    poi 'org.apache.poi:poi:3.9'
    poiooxml 'org.apache.poi:poi-ooxml:3.9'
    compile configurations.poi
    compile configurations.poiooxml
}

eclipse {
  project {
    name = 'ExcelStage'
  }

  classpath {
    plusConfigurations += configurations.poi
    plusConfigurations += configurations.poiooxml

    defaultOutputDir = compileJava.destinationDir
  }
}

Legt man im Verzeichnis src/test/java entsprechende JUnit Tests an, werden diese mit dem Aufruf gradle test direkt mit einem Report als Ergebnis ausgeführt.

Der hier angegebene Quellcode kann frei kopiert werden, aber auf eigenes Verantwortung.

Im nächsten Schritt wird der Java Teil der Java Stage erstellt.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.