/* 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
*/



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<CXlsBof.length; i++) {
      _dout.writeByte(CXlsBof[i]);
    }
   }
   catch (IOException ee) {
         System.err.println("IOException: " + ee);
   }
  }  // Export2Modul


  public void closeFile() {
   try {
    for (int i=0; i<CXlsEof.length; i++) {
      _dout.writeByte(CXlsEof[i]);
    }
    _dout.close();
    _fout.close();
   }
   catch (IOException ee) {
         System.err.println("IOException: " + ee);
   }
  }  // close()


  public void ClearArrays() {
      // eigentlich sind das Word, unsigned short, 16 Bit, die Reihenfolge passt aber nicht
      // deshalb byteweise
     CXlsString[0] =  4;
     CXlsString[1] =  2;
     CXlsString[2] =  0;
     CXlsString[3] =  0;
     CXlsString[4] =  0;
     CXlsString[5] =  0;
     CXlsString[6] =  0;
     CXlsString[7] =  0;
     CXlsString[8] =  0;
     CXlsString[9] =  0;
     CXlsString[10]=  0;
     CXlsString[11]=  0;

      // eigentlich sind das Word, unsigned short, 16 Bit, die Reihgenfolge passt aber nicht
      // deshalb byteweise
     CXlsDouble[0] = 3;   
     CXlsDouble[1] = 2;
     CXlsDouble[2] = 14;
     CXlsDouble[3] = 0;
     CXlsDouble[4] = 0;
     CXlsDouble[5] = 0;
     CXlsDouble[6] = 0;
     CXlsDouble[7] = 0;
     CXlsDouble[8] = 0;
     CXlsDouble[9] = 0;

      // eigentlich sind das Word, unsigned short, 16 Bit, die Reihgenfolge passt aber nicht
      // deshalb byteweise
     CXlsInt[0] = 0x7E;
     CXlsInt[1] = 2;
     CXlsInt[2] = 10;
     CXlsInt[3] = 0;
     CXlsInt[4] = 0;
     CXlsInt[5] = 0;
     CXlsInt[6] = 0;
     CXlsInt[7] = 0;
     CXlsInt[8] = 0;
     CXlsInt[9] = 0;
  }  // ClearArrays


  

   // schreiben der Vorspann eines Int
  private void writeCXlsInt() {
   try {
    for (int i=0; i<CXlsInt.length; i++) {
      _dout.writeByte(CXlsInt[i]);
    }
   }
   catch (IOException e) {
         System.err.println("IOException: " + e);
   }
  }


  public void Write(int Col, int Row, int iValue) {
     short s;
     int v;
     byte i3,i2,i1,i0;
     try {
        ClearArrays();
        s = (short) (Row);
        CXlsInt[4] = (byte) (s & 255);  // 4 5   [2]
        CXlsInt[5] = (byte) ((s >> 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<CXlsDouble.length; i++) {
      _dout.writeByte(CXlsDouble[i]);
    }
   }
   catch (IOException ee) {
         System.err.println("IOException: " + ee);
   }
  }


  // ein double-Wert wird genau verkehrt herum in Java geschrieben
  // also aufsplitten der einzelnen Bytes
  public void Write(int Col, int Row, double dValue) {
     short s;
     int v;
     int i;
     int [] bits = new int[8];  // int statt byte, da byte Vorzeichenbehaftig

     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();

         // 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<bits.length; i++, ofs+=8) {
         bits[i] = (byte) ((lD >> ofs) & 255);   // GDI 1 Resim
       }


        for (i=0; i<bits.length; i++) {
          _dout.writeByte( bits[i] );
          /*
          if (bits[i]<0)
           System.out.println("i: "+i+"   "+(int) (bits[i]+256));
          else
          System.out.println("i: "+i+"   "+bits[i]);
           */
        } 
        // _dout.writeDouble( Value );  // leider hier verkehrte Reihenfolge
     }
     catch (IOException e) {
         System.err.println("IOException: " + e);
     }
  }  // Write Double


   // schreiben der Vorspann eines String
  private void writeCXlsString() {
   try {
    for (int i=0; i<CXlsString.length; i++) {
      _dout.writeByte(CXlsString[i]);
    }
   }
   catch (IOException ee) {
         System.err.println("IOException: " + ee);
   }
  }


  public void Write(int Col, int Row, String sValue) {
   short len;
   short s;
   try {
     ClearArrays();
     len = (short) sValue.length() ;
    
     s = (short) (len+8);
     CXlsString[2] = (byte) (s & 255);  // 2 3   [1]   erst low dann high byte
     CXlsString[3] = (byte) ((s >> 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<len; i++) {
      _dout.writeByte( (byte) sValue.charAt(i) );  // cahr ist unicopde, zwei Byte !
     }
   }
   catch (IOException ee) {
         System.err.println("IOException: " + ee);
   }
  }  // Write string



  // alle diese Konstanten sind nicht benutzt
   private final byte  BIFF12_DEFINEDNAME            = 0x27;
   private final short BIFF12_FILEVERSION            = 0x0180;
   private final short BIFF12_WORKBOOK               = 0x0183;
   private final short BIFF12_WORKBOOK_END           = 0x0184;
   private final short BIFF12_BOOKVIEWS              = 0x0187;
   private final short BIFF12_BOOKVIEWS_END          = 0x0188;
   private final short BIFF12_SHEETS                 = 0x018F;
   private final short BIFF12_SHEETS_END             = 0x0190;
   private final short BIFF12_WORKBOOKPR             = 0x0199;
   private final short BIFF12_SHEET                  = 0x019C;
   private final short BIFF12_CALCPR                 = 0x019D;
   private final short BIFF12_WORKBOOKVIEW           = 0x019E;
   private final short BIFF12_EXTERNALREFERENCES     = 0x02E1;
   private final short BIFF12_EXTERNALREFERENCES_END = 0x02E2;
   private final short BIFF12_EXTERNALREFERENCE      = 0x02E3;
   private final short BIFF12_WEBPUBLISHING          = 0x04A9;
   
   // Worksheet records
   private final byte BIFF12_ROW             = 0x00;
   private final byte BIFF12_BLANK           = 0x01;
   private final byte BIFF12_NUM             = 0x02;
   private final byte BIFF12_BOOLERR         = 0x03;
   private final byte BIFF12_BOOL            = 0x04;
   private final byte BIFF12_FLOAT           = 0x05;
   private final byte BIFF12_STRING          = 0x07;
   private final byte BIFF12_FORMULA_STRING  = 0x08;
   private final byte BIFF12_FORMULA_FLOAT   = 0x09;
   private final byte BIFF12_FORMULA_BOOL    = 0x0A;
   private final byte BIFF12_FORMULA_BOOLERR = 0x0B;
   private final byte BIFF12_COL             = 0x3C;
   private final short BIFF12_WORKSHEET       = 0x0181;
   private final short BIFF12_WORKSHEET_END   = 0x0182;
   private final short BIFF12_SHEETVIEWS      = 0x0185;
   private final short BIFF12_SHEETVIEWS_END  = 0x0186;
   private final short BIFF12_SHEETVIEW       = 0x0189;
   private final short BIFF12_SHEETVIEW_END   = 0x018A;
   private final short BIFF12_SHEETDATA       = 0x0191;
   private final short BIFF12_SHEETDATA_END   = 0x0192;
   private final short BIFF12_SHEETPR         = 0x0193;
   private final short BIFF12_DIMENSION       = 0x0194;
   private final short BIFF12_SELECTION       = 0x0198;
   private final short BIFF12_COLS            = 0x0386;
   private final short BIFF12_COLS_END        = 0x0387;
   private final short BIFF12_CONDITIONALFORMATTING = 0x03CD;
   private final short BIFF12_CONDITIONALFORMATTING_END = 0x03CE;
   private final short BIFF12_CFRULE          = 0x03CF;
   private final short BIFF12_CFRULE_END      = 0x03D0;
   private final short BIFF12_ICONSET         = 0x03D1;
   private final short BIFF12_ICONSET_END     = 0x03D2;
   private final short BIFF12_DATABAR         = 0x03D3;
   private final short BIFF12_DATABAR_END     = 0x03D4;
   private final short BIFF12_COLORSCALE      = 0x03D5;
   private final short BIFF12_COLORSCALE_END  = 0x03D6;
   private final short BIFF12_CFVO            = 0x03D7;
   private final short BIFF12_PAGEMARGINS     = 0x03DC;
   private final short BIFF12_PRINTOPTIONS    = 0x03DD;
   private final short BIFF12_PAGESETUP       = 0x03DE;
   private final short BIFF12_HEADERFOOTER    = 0x03DF;
   private final short BIFF12_SHEETFORMATPR   = 0x03E5;
   private final short BIFF12_HYPERLINK       = 0x03EE;
   private final short BIFF12_DRAWING         = 0x04A6;
   private final short BIFF12_LEGACYDRAWING   = 0x04A7;
   private final short BIFF12_COLOR           = 0x04B4;
   private final short BIFF12_OLEOBJECTS      = 0x04FE;
   private final short BIFF12_OLEOBJECT       = 0x04FF;
   private final short BIFF12_OLEOBJECTS_END  = 0x0580;
   private final short BIFF12_TABLEPARTS      = 0x0594;
   private final short BIFF12_TABLEPART       = 0x0595;
   private final short BIFF12_TABLEPARTS_END  = 0x0596;
   
   //SharedStrings records
   private final byte BIFF12_SI              = 0x13;
   private final short BIFF12_SST             = 0x019F;
   private final short BIFF12_SST_END         = 0x01A0;
   
   //Styles records
   private final byte BIFF12_FONT            = 0x2B;
   private final byte BIFF12_FILL            = 0x2D;
   private final byte BIFF12_BORDER          = 0x2E;
   private final byte BIFF12_XF              = 0x2F;
   private final byte BIFF12_CELLSTYLE       = 0x30;
   private final short BIFF12_STYLESHEET      = 0x0296;
   private final short BIFF12_STYLESHEET_END  = 0x0297;
   private final short BIFF12_COLORS          = 0x03D9;
   private final short BIFF12_COLORS_END      = 0x03DA;
   private final short BIFF12_DXFS            = 0x03F9;
   private final short BIFF12_DXFS_END        = 0x03FA;
   private final short BIFF12_TABLESTYLES     = 0x03FC;
   private final short BIFF12_TABLESTYLES_END = 0x03FD;
   private final short BIFF12_FILLS           = 0x04DB;
   private final short BIFF12_FILLS_END       = 0x04DC;
   private final short BIFF12_FONTS           = 0x04E3;
   private final short BIFF12_FONTS_END       = 0x04E4;
   private final short BIFF12_BORDERS         = 0x04E5;
   private final short BIFF12_BORDERS_END     = 0x04E6;
   private final short BIFF12_CELLXFS         = 0x04E9;
   private final short BIFF12_CELLXFS_END     = 0x04EA;
   private final short BIFF12_CELLSTYLES      = 0x04EB;
   private final short BIFF12_CELLSTYLES_END  = 0x04EC;
   private final short BIFF12_CELLSTYLEXFS    = 0x04F2;
   private final short BIFF12_CELLSTYLEXFS_END = 0x04F3;
   
   //Comment records
   private final short BIFF12_COMMENTS        = 0x04F4;
   private final short BIFF12_COMMENTS_END    = 0x04F5;
   private final short BIFF12_AUTHORS         = 0x04F6;
   private final short BIFF12_AUTHORS_END     = 0x04F7;
   private final short BIFF12_AUTHOR          = 0x04F8;
   private final short BIFF12_COMMENTLIST     = 0x04F9;
   private final short BIFF12_COMMENTLIST_END = 0x04FA;
   private final short BIFF12_COMMENT         = 0x04FB;
   private final short BIFF12_COMMENT_END     = 0x04FC;
   private final short BIFF12_TEXT            = 0x04FD;
   
   //Table records
   private final short BIFF12_AUTOFILTER      = 0x01A1;
   private final short BIFF12_AUTOFILTER_END  = 0x01A2;
   private final short BIFF12_FILTERCOLUMN    = 0x01A3;
   private final short BIFF12_FILTERCOLUMN_END= 0x01A4;
   private final short BIFF12_FILTERS         = 0x01A5;
   private final short BIFF12_FILTERS_END     = 0x01A6;
   private final short BIFF12_FILTER          = 0x01A7;
   private final short BIFF12_TABLE           = 0x02D7;
   private final short BIFF12_TABLE_END       = 0x02D8;
   private final short BIFF12_TABLECOLUMNS    = 0x02D9;
   private final short BIFF12_TABLECOLUMNS_END= 0x02DA;
   private final short BIFF12_TABLECOLUMN     = 0x02DB;
   private final short BIFF12_TABLECOLUMN_END = 0x02DC;
   private final short BIFF12_TABLESTYLEINFO  = 0x0481;
   private final short BIFF12_SORTSTATE       = 0x0492;
   private final short BIFF12_SORTCONDITION   = 0x0494;
   private final short BIFF12_SORTSTATE_END   = 0x0495;
   
   //QueryTable records
   private final short BIFF12_QUERYTABLE            = 0x03BF;
   private final short BIFF12_QUERYTABLE_END        = 0x03C0;
   private final short BIFF12_QUERYTABLEREFRESH     = 0x03C1;
   private final short BIFF12_QUERYTABLEREFRESH_END = 0x03C2;
   private final short BIFF12_QUERYTABLEFIELDS      = 0x03C7;
   private final short BIFF12_QUERYTABLEFIELDS_END  = 0x03C8;
   private final short BIFF12_QUERYTABLEFIELD       = 0x03C9;
   private final short BIFF12_QUERYTABLEFIELD_END   = 0x03CA;
   
   //Connection records
   private final short BIFF12_CONNECTIONS           = 0x03AD;
   private final short BIFF12_CONNECTIONS_END       = 0x03AE;
   private final short BIFF12_CONNECTION            = 0x01C9;
   private final short BIFF12_CONNECTION_END        = 0x01CA;
   private final short BIFF12_DBPR                  = 0x01CB;
   private final short BIFF12_DBPR_END              = 0x01CC;

}