Excel Export

Excel Export

Beschreibung Download
Export nach Excel mittels des Binary Interchange File Format (BIFF)

Hauptproblem:
Windows speichert in little Endian
Java speichert in big Endian 		UIBspExport2Excel01.java
UIBspTabView01.png

Export2BIFF

private void bnStringClick() {
Export2BIFF excel = new Export2BIFF("test1.xls");
excel.openFile();
excel.Write(1,1,"abc"); // col row Value
excel.Write(1,2,"01.01.2010"); // col row Value hier String, NICHT Datum !
excel.Write(1,3,"01/01/2010"); // col row Value hier String, NICHT Datum !
excel.closeFile();
StartExcel("test1.xls");
}

private void bnIntegerClick() {
Export2BIFF excel = new Export2BIFF("test2.xls");
excel.openFile();
// excel.Write(1,1,"abc"); // col row Value
excel.Write(1,2,1234); // col row Value
excel.Write(1,3,2004); // col row Value
// excel.Write(1,3,"=SUMME(B3:B4)"); // col row Value
excel.closeFile();
StartExcel("test2.xls");
}

private void bnDoubleClick() {
Export2BIFF excel = new Export2BIFF("test3.xls");
excel.openFile();
excel.Write(1,1,1234.45678); // col row Value
excel.closeFile();
StartExcel("test3.xls");
}

private void bnAllesClick() {
int row;
Export2BIFF excel = new Export2BIFF("test4.xls");
excel.openFile();
java.util.Random random = new java.util.Random();
excel.Write(1,0, "Name" );
excel.Write(2,0, "Integer" );
excel.Write(3,0, "Gehälter" );
for (row=1; row<50; row++) {
excel.Write(1,row, "abc" + Integer.toString(row) ); // col row Value
excel.Write(2,row, random.nextInt() );
excel.Write(3,row, random.nextDouble()*10000 );
} // for
excel.closeFile();
StartExcel("test4.xls");
}

private void StartExcel(String filename) {
try {
// funktioniert nur mit vollstaendigen Pfad
String excel = "\"C:\\Program Files (x86)\\Microsoft Office\\Office14\\EXCEL.EXE\"";
Runtime.getRuntime().exec(excel+" D:\\Daten\\Dp60\\Java-Edit\\"+filename);
}
catch (IOException e1) {
System.out.println("Error in Start Excel");
}
}
Export nach Excel mittels des Binary Interchange File Format (BIFF)
Diese Lösung verwendet beim Double-Datentyp zwei Pipes
Die erste Lösung ist natürlich besser, zeigt aber die Verwendung von Pipes
Hauptproblem:
Windows speichert in little Endian
Java speichert in big Endian 		UIBspExport2Excel02.java
UIBspTabView02.png

Export2BIFF

private void bnStringClick() {
Export2BIFF excel = new Export2BIFF("test1.xls");
excel.openFile();
excel.Write(1,1,"abc"); // col row Value
excel.Write(1,2,"01.01.2010"); // col row Value hier String, NICHT Datum !
excel.Write(1,3,"01/01/2010"); // col row Value hier String, NICHT Datum !
excel.closeFile();
StartExcel("test1.xls");
}

private void bnIntegerClick() {
Export2BIFF excel = new Export2BIFF("test2.xls");
excel.openFile();
// excel.Write(1,1,"abc"); // col row Value
excel.Write(1,2,1234); // col row Value
excel.Write(1,3,2004); // col row Value
// excel.Write(1,3,"=SUMME(B3:B4)"); // col row Value
excel.closeFile();
StartExcel("test2.xls");
}

private void bnDoubleClick() {
Export2BIFF excel = new Export2BIFF("test3.xls");
excel.openFile();
excel.Write(1,1,1234.45678); // col row Value
excel.closeFile();
StartExcel("test3.xls");
}

private void bnAllesClick() {
int row;
Export2BIFF excel = new Export2BIFF("test4.xls");
excel.openFile();
java.util.Random random = new java.util.Random();
excel.Write(1,0, "Name" );
excel.Write(2,0, "Integer" );
excel.Write(3,0, "Gehälter" );
for (row=1; row<50; row++) {
excel.Write(1,row, "abc" + Integer.toString(row) ); // col row Value
excel.Write(2,row, random.nextInt() );
excel.Write(3,row, random.nextDouble()*10000 );
} // for
excel.closeFile();
StartExcel("test4.xls");
}

private void StartExcel(String filename) {
try {
// funktioniert nur mit vollstaendigen Pfad
String excel = "\"C:\\Program Files (x86)\\Microsoft Office\\Office14\\EXCEL.EXE\"";
Runtime.getRuntime().exec(excel+" D:\\Daten\\Dp60\\Java-Edit\\"+filename);
}
catch (IOException e1) {
System.out.println("Error in Start Excel");
}
}

NEU:
// ein double-Wert wird genau verkehrt herum geschrieben
// also extra schreiben, einlesen und neuschreiben
public void Write(int Col, int Row, double dValue) {
short s;
int v;
int i;
byte [] bits = new byte[8];
PipedInputStream pipein; //
PipedOutputStream pipeout;
DataOutputStream dout;

try {
ClearArrays();
s = (short) (Row);
CXlsDouble[4] = (byte) (s & 255); // 4 5 [2]
CXlsDouble[5] = (byte) ((s >> 8) & 255); // 4 5 [2]

s = (short) (Col);
CXlsDouble[6] = (byte) (s & 255); // 6 7 [3]
CXlsDouble[7] = (byte) ((s >> 8) & 255); // 6 7 [3]
writeCXlsDouble();

pipein = new PipedInputStream();
pipeout = new PipedOutputStream();
dout = new DataOutputStream(pipeout);
// Erzeugen der Stream-Objekte zum Schreiben und Lesen

pipeout.connect(pipein); // Kopplung der Streams

dout.writeDouble(dValue);
pipein.read(bits, 0, bits.length); // in dem Array wird gelesen, Startindex=0 Anzahl = 8
pipein.close();
dout.close();
pipeout.close();

for (i=bits.length-1; i>=0; i--) {
dout.writeByte( bits[i] );
}

// _dout.writeDouble( Value ); // leider hier verkehrte Reihenfolge
}
catch (IOException e) {
System.err.println("IOException: " + e);
}
} // Write Double
Dialog mit Alert
Farben-Snippet