import javafx.application.Application;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.scene.layout.*;
import javafx.stage.Stage;
import javafx.scene.text.TextAlignment;
import javafx.scene.text.Font;
import javafx.geometry.Pos;
import javafx.geometry.Insets;
import javafx.scene.layout.Border;
import javafx.scene.layout.BorderStroke;
import javafx.scene.layout.BorderStrokeStyle;
import javafx.scene.layout.BorderWidths;
import javafx.scene.paint.Color;


import javafx.application.Platform;

import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.event.Event;

import javafx.geometry.HPos;
import java.io.*;

public class UIBspExport2Excel02 extends Application  {


    private  Button bnString = new Button("String") ;
    private  Button bnInteger = new Button("Integer") ;
    private  Button bnDouble = new Button("Double") ;
    private  Button bnAlles = new Button("Alles") ;

    
     @Override 
     public void start(Stage stage) {
             // https://docs.oracle.com/javase/8/javafx/api/javafx/scene/layout/VBox.html
             // https://docs.oracle.com/javase/8/javafx/api/toc.htm
            VBox root = new VBox(22);       
            root.setAlignment(Pos.CENTER);
            root.setFillWidth(true);

            HBox hbox = new HBox();
            root.getChildren().add(hbox);
            Label label = new Label("Excel Export") ;
            label.setFont(new Font("Courier New",28));
            hbox.getChildren().add(label);
            hbox.setMargin(label, new Insets(5, 0, 0, 10) );  // TRBL
            label.setTextAlignment(TextAlignment.LEFT);
            label.setAlignment(Pos.BASELINE_LEFT);
            label.setTextFill(Color.BLUE);

            FlowPane boxpane = new FlowPane(20,20);   
            boxpane.setAlignment(Pos.CENTER);
            boxpane.setAlignment(Pos.CENTER);
            boxpane.setMaxWidth(Double.POSITIVE_INFINITY);
            root.getChildren().add(boxpane);
            root.setMargin(boxpane, new Insets(5, 0, 0, 10) );  // TRBL


            insertButton(boxpane, bnString);
            bnString.setOnAction(e->bnStringClick());

            insertButton(boxpane, bnInteger);
            bnInteger.setOnAction(e->bnIntegerClick());

            insertButton(boxpane, bnDouble);
            bnDouble.setOnAction(e->bnDoubleClick());


            insertButton(boxpane, bnAlles);
            bnAlles.setOnAction(e->bnAllesClick());



            Scene scene= new Scene(root, 660, 150);
            stage.setTitle("UIBspExport2Excel02");        
            stage.setScene(scene);        
            stage.show();  
    }


    private void   insertButton(FlowPane pane, Button bn) {
            bn.setFont(new Font("Courier New",22));            
            bn.setMaxWidth(Double.POSITIVE_INFINITY);
            pane.getChildren().add(bn);
            pane.setMargin(bn, new Insets(0, 10, 0, 10) );  // TRBL
   }


     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");
       }
    }


      public static void main(String[] argv) {       
              launch(argv);   
      } 
}


class Export2BIFF {

   // interne Varibalen fuer das Speichern
  private FileOutputStream fout;
  private DataOutputStream dout;
  private String filename;

    // 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 filename) {
    this.filename = filename;
    ClearArrays();
  }  // Export2BIFF


  public void openFile() {
   try {
    fout = new FileOutputStream(filename);
    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 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


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

}