/* Autor: Dipl.-Inf., Dipl.-Ing. (FH) Michael Wilhelm Firma: HS Harz, FB AI Version: 1.1 Ersterstellung: 09.11.2010 Letzte Änderung: 07.04.2013 Erstellt einen nativen Export nach Excel, ohne csv */ import java.awt.*; import javax.swing.*; import java.awt.event.*; import java.io.*; public class Export2Excel_01 extends JFrame { JButton BnString = new JButton("String"); JButton BnInt = new JButton("int"); JButton BnDouble = new JButton("double"); JButton BnAll = new JButton("alles"); JButton BnEsc = new JButton("Abbruch"); public Export2Excel_01() { setDefaultCloseOperation( JFrame.EXIT_ON_CLOSE ); setGUI(); } private void setGUI() { setSize(400, 230); setTitle("JFrame als Dialog"); BnString.setFont( new Font("Arial", Font.BOLD,18) ); BnInt.setFont( new Font("Arial", Font.BOLD,18) ); BnDouble.setFont( new Font("Arial", Font.BOLD,18) ); BnAll.setFont( new Font("Arial", Font.BOLD,18) ); BnEsc.setFont( new Font("Arial", Font.BOLD,18) ); this.getContentPane().setLayout( new BorderLayout() ); JPanel pnButton = new JPanel(); //pnButton.setBackground(Color.red); pnButton.setLayout( new FlowLayout() ); pnButton.add(BnString); pnButton.add(BnInt); pnButton.add(BnDouble); pnButton.add(BnAll); pnButton.add(BnEsc); this.getContentPane().add( pnButton, BorderLayout.SOUTH ); BnString.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { BnString_Click(); } }); BnInt.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { BnInt_Click(); } }); BnDouble.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { BnDouble_Click(); } }); BnAll.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { BnAll_Click(); } }); BnEsc.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(ActionEvent e) { BnEsc_Click(); } }); } // setGUI private void BnString_Click() { 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 BnInt_Click() { 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 BnDouble_Click() { Export2BIFF excel = new Export2BIFF("test3.xls"); excel.openFile(); excel.Write(1,1,1234.45678); // col row Value excel.closeFile(); StartExcel("test3.xls"); } private void BnAll_Click() { 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 sFilename) { try { // funktioniert nur mit vollstaendigen Pfad von Excel Runtime.getRuntime().exec("C:\\Program Files (x86)\\Microsoft Office\\Office14\\EXCEL.EXE "+sFilename); } catch (IOException e1) { System.out.println("Error in Start Excel"); } } private void BnEsc_Click() { setVisible(false); System.exit(0); } public static void main(String[] args) { Export2Excel_01 frame = new Export2Excel_01(); frame.setVisible(true); } } // Export2Excel_01 /* Autor: Dipl.-Inf., Dipl.-Ing. (FH) Michael Wilhelm Firma: HS Harz, FB AI Version: 1.1 Ersterstellung: 09.11.2010 Letzte Änderung: 09.11.2010 EWrstellt einen nativen Export nach Excel, ohne csv Export nach Excel mittels des Binary Interchange File Format (BIFF) Hauptproblem: Windows speichert little Endian Java speichert big Endian */ // import java.io.*; class Export2BIFF{ // interne Variablen fuer das Speichern private FileOutputStream _fout; private DataOutputStream _dout; private String _sFilename; // Felder Beginn Ende String Int byte [] CXlsBof = { 9, 8, 8, 0, 0, 0, 0x10, 0, 1, 0, 0, 0 }; byte [] CXlsEof = {0x0A, 0, 0, 0}; byte [] CXlsString = new byte[12]; byte [] CXlsDouble = new byte[10]; byte [] CXlsInt = new byte[10]; public Export2BIFF(String sFilename) { _sFilename = sFilename; ClearArrays(); } // Export2BIFF public void openFile() { try { _fout = new FileOutputStream(_sFilename); _dout = new DataOutputStream(_fout); for (int i=0; i> 8) & 255); // 4 5 [2] s = (short) (Col); CXlsInt[6] = (byte) (s & 255); // 6 7 [3] CXlsInt[7] = (byte) ((s >> 8) & 255); // 6 7 [3] writeCXlsInt(); // Trennung der Bytes, ā Resim v = (iValue << 2) | 2; i0 = (byte) (v & 255); i1 = (byte) ((v >> 8) & 255); i2 = (byte) ((v >> 16) & 255); i3 = (byte) ((v >> 24) & 255); _dout.writeByte( i0 ); _dout.writeByte( i1 ); _dout.writeByte( i2 ); _dout.writeByte( i3 ); } catch (IOException e) { System.err.println("IOException: " + e); } } // Write Int // schreiben der Vorspann eines double private void writeCXlsDouble() { try { for (int i=0; i> 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(); // Konvertierung in long, auftrennen der einzelnen Bytes der ACHT Double-Bytes !!!! // Tja, sowas gibt es in jaaaava long lD = Double.doubleToLongBits(dValue); int ofs; for (i=0, ofs=0; i> ofs) & 255); // GDI 1 Resim } for (i=0; i> 8) & 255); // 2 3 s = (short) (Row); CXlsString[4] = (byte) (s & 255); // 4 5 [2] CXlsString[5] = (byte) ((s >> 8) & 255); // 4 5 [2] s = (short) (Col); CXlsString[6] = (byte) (s & 255); // 6 7 [3] CXlsString[7] = (byte) ((s >> 8) & 255); // 6 7 [3] s = (short) (len); CXlsString[10] = (byte) (s & 255); // 10 11 [5] CXlsString[11] = (byte) ((s >> 8) & 255); // 10 11 [5] writeCXlsString(); for (int i=0; i