write.java

一个非常有用的操作MCRSOFT EXCEL文件的工具。可以用JAVA方便的新建

    Date date7 = c.getTime();
    c.set(1900, 1, 1, 0, 0, 0);
    Date date8 = c.getTime();
    c.set(1900, 0, 31, 0, 0, 0);
    Date date9 = c.getTime();
    c.set(1900, 2, 1, 0, 0, 0);
    Date date10 = c.getTime();
    c.set(1900, 1, 27, 0, 0, 0);
    Date date11 = c.getTime();
    c.set(1900, 1, 28, 0, 0, 0);
    Date date12 = c.getTime();
    c.set(1980, 5, 31, 12, 0, 0);
    Date date13 = c.getTime();

    // Build in date formats
    SimpleDateFormat sdf = new SimpleDateFormat("dd MMM yyyy HH:mm:ss.SSS");
    Label l = new Label(0,0,"All dates are " + sdf.format(date),
                        wrappedText);
    s.addCell(l);

    l = new Label(0,1,"Built in formats", 
                  wrappedText);
    s.addCell(l);

    l = new Label(2, 1, "Custom formats");
    s.addCell(l);

    WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT1);
    DateTime dt = new DateTime(0,2,date, cf1);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT2);
    dt = new DateTime(0,3,date, cf1);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT3);
    dt = new DateTime(0,4,date, cf1);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT4);
    dt = new DateTime(0,5,date, cf1);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT5);
    dt = new DateTime(0,6,date, cf1);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT6);
    dt = new DateTime(0,7,date, cf1);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT7);
    dt = new DateTime(0,8,date, cf1);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT8);
    dt = new DateTime(0,9,date, cf1);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(0,10,date, cf1);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT10);
    dt = new DateTime(0,11,date, cf1);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT11);
    dt = new DateTime(0,12,date, cf1);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT12);
    dt = new DateTime(0,13,date, cf1);
    s.addCell(dt);

    // Custom formats
    DateFormat df = new DateFormat("dd MM yyyy");
    cf1 = new WritableCellFormat(df);
    l = new Label(2, 2, "dd MM yyyy");
    s.addCell(l);

    dt = new DateTime(3, 2, date, cf1);
    s.addCell(dt);

    df = new DateFormat("dd MMM yyyy");
    cf1 = new WritableCellFormat(df);
    l = new Label(2, 3, "dd MMM yyyy");
    s.addCell(l);

    dt = new DateTime(3, 3, date, cf1);
    s.addCell(dt);
 
    df = new DateFormat("hh:mm");
    cf1 = new WritableCellFormat(df);
    l = new Label(2, 4, "hh:mm");
    s.addCell(l);

    dt = new DateTime(3, 4, date, cf1);
    s.addCell(dt);

    df = new DateFormat("hh:mm:ss");
    cf1 = new WritableCellFormat(df);
    l = new Label(2, 5, "hh:mm:ss");
    s.addCell(l);

    dt = new DateTime(3, 5, date, cf1);
    s.addCell(dt);

    df = new DateFormat("H:mm:ss a");
    cf1 = new WritableCellFormat(df);
    l = new Label(2, 5, "H:mm:ss a");
    s.addCell(l);

    dt = new DateTime(3, 5, date, cf1);
    s.addCell(dt);
    dt = new DateTime(4, 5, date13, cf1);
    s.addCell(dt);

    df = new DateFormat("mm:ss.SSS");
    cf1 = new WritableCellFormat(df);
    l = new Label(2, 6, "mm:ss.SSS");
    s.addCell(l);

    dt = new DateTime(3, 6, date, cf1);
    s.addCell(dt);

    df = new DateFormat("hh:mm:ss a");
    cf1 = new WritableCellFormat(df);
    l = new Label(2, 7, "hh:mm:ss a");
    s.addCell(l);

    dt = new DateTime(4, 7, date13, cf1);
    s.addCell(dt);


    // Check out the zero date ie. 1 Jan 1900
    l = new Label(0,16,"Zero date " + sdf.format(date2),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(0,17,date2, cf1);
    s.addCell(dt);

    // Check out the zero date + 1 ie. 2 Jan 1900
    l = new Label(3,16,"Zero date + 1 " + sdf.format(date5),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(3,17,date5, cf1);
    s.addCell(dt);

    // Check out the 1 Jan 1901
    l = new Label(3,19, sdf.format(date6),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(3,20,date6, cf1);
    s.addCell(dt);

    // Check out the 31 May 1900
    l = new Label(3,22, sdf.format(date7),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(3,23, date7, cf1);
    s.addCell(dt);

    // Check out 1 Feb 1900
    l = new Label(3,25, sdf.format(date8),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(3,26, date8, cf1);
    s.addCell(dt);

    // Check out 31 Jan 1900
    l = new Label(3,28, sdf.format(date9),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(3,29, date9, cf1);
    s.addCell(dt);

    // Check out 31 Jan 1900
    l = new Label(3,28, sdf.format(date9),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(3,29, date9, cf1);
    s.addCell(dt);

    // Check out 1 Mar 1900
    l = new Label(3,31, sdf.format(date10),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(3,32, date10, cf1);
    s.addCell(dt);

    // Check out 27 Feb 1900
    l = new Label(3,34, sdf.format(date11),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(3,35, date11, cf1);
    s.addCell(dt);

    // Check out 28 Feb 1900
    l = new Label(3,37, sdf.format(date12),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(3,38, date12, cf1);
    s.addCell(dt);

    // Check out the zero date ie. 1 Jan 1970
    l = new Label(0,19,"Zero UTC date " + sdf.format(date3),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(0,20,date3, cf1);
    s.addCell(dt);

    // Check out the WWI armistice day ie. 11 am, Nov 11, 1918
    l = new Label(0,22,"Armistice date " + sdf.format(date4),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT9);
    dt = new DateTime(0,23,date4, cf1);
    s.addCell(dt);
  }

  /**
   * Adds cells to the specified sheet which test the various label formatting
   * styles, such as different fonts, different sizes and bold, underline etc.
   * 
   * @param s1 
   */
  private void writeLabelFormatSheet(WritableSheet s1) throws WriteException
  {
    s1.setColumnView(0, 60);

    Label lr = new Label(0,0, "Arial Fonts");
    s1.addCell(lr);

    lr = new Label(1,0, "10pt");
    s1.addCell(lr);

    lr = new Label(2, 0, "Normal");
    s1.addCell(lr);

    lr = new Label(3, 0, "12pt");
    s1.addCell(lr);

    WritableFont arial12pt = new WritableFont(WritableFont.ARIAL, 12);
    WritableCellFormat arial12format = new WritableCellFormat(arial12pt);
    arial12format.setWrap(true);
    lr = new Label(4, 0, "Normal", arial12format);
    s1.addCell(lr);

    WritableFont arial10ptBold = new WritableFont
      (WritableFont.ARIAL, 10, WritableFont.BOLD);
    WritableCellFormat arial10BoldFormat = new WritableCellFormat
      (arial10ptBold);
    lr = new Label(2, 2, "BOLD", arial10BoldFormat);
    s1.addCell(lr);

    WritableFont arial12ptBold = new WritableFont
      (WritableFont.ARIAL, 12, WritableFont.BOLD);
    WritableCellFormat arial12BoldFormat = new WritableCellFormat
      (arial12ptBold);
    lr = new Label(4, 2, "BOLD", arial12BoldFormat);
    s1.addCell(lr);

    WritableFont arial10ptItalic = new WritableFont
      (WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true);
    WritableCellFormat arial10ItalicFormat = new WritableCellFormat
      (arial10ptItalic);
    lr = new Label(2, 4, "Italic", arial10ItalicFormat);
    s1.addCell(lr);

    WritableFont arial12ptItalic = new WritableFont
      (WritableFont.ARIAL, 12, WritableFont.NO_BOLD, true);
    WritableCellFormat arial12ptItalicFormat = new WritableCellFormat
      (arial12ptItalic);
    lr = new Label(4, 4, "Italic", arial12ptItalicFormat);
    s1.addCell(lr);

    WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
    WritableCellFormat times10format = new WritableCellFormat(times10pt);
    lr = new Label(0, 7, "Times Fonts", times10format);
    s1.addCell(lr);

    lr = new Label(1, 7, "10pt", times10format);
    s1.addCell(lr);

    lr = new Label(2, 7, "Normal", times10format);
    s1.addCell(lr);

    lr = new Label(3, 7, "12pt", times10format);
    s1.addCell(lr);

    WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
    WritableCellFormat times12format = new WritableCellFormat(times12pt);
    lr = new Label(4, 7, "Normal", times12format);
    s1.addCell(lr);

    WritableFont times10ptBold = new WritableFont
      (WritableFont.TIMES, 10, WritableFont.BOLD);
    WritableCellFormat times10BoldFormat = new WritableCellFormat
      (times10ptBold);
    lr = new Label(2, 9, "BOLD", times10BoldFormat);
    s1.addCell(lr);

    WritableFont times12ptBold = new WritableFont
      (WritableFont.TIMES, 12, WritableFont.BOLD);
    WritableCellFormat times12BoldFormat = new WritableCellFormat
      (times12ptBold);
    lr = new Label(4, 9, "BOLD", times12BoldFormat);
    s1.addCell(lr);

    // The underline styles
    s1.setColumnView(6, 22);
    s1.setColumnView(7, 22);
    s1.setColumnView(8, 22);
    s1.setColumnView(9, 22);

    lr = new Label(0, 11, "Underlining");
    s1.addCell(lr);

    WritableFont arial10ptUnderline = new WritableFont
      (WritableFont.ARIAL, 
       WritableFont.DEFAULT_POINT_SIZE,
       WritableFont.NO_BOLD,
       false,
       UnderlineStyle.SINGLE);
    WritableCellFormat arialUnderline = new WritableCellFormat
      (arial10ptUnderline);
    lr = new Label(6,11, "Underline", arialUnderline);
    s1.addCell(lr);

    WritableFont arial10ptDoubleUnderline = new WritableFont
      (WritableFont.ARIAL, 
       WritableFont.DEFAULT_POINT_SIZE,
       WritableFont.NO_BOLD,
       false,
       UnderlineStyle.DOUBLE);
    WritableCellFormat arialDoubleUnderline = new WritableCellFormat
      (arial10ptDoubleUnderline);
    lr = new Label(7,11, "Double Underline", arialDoubleUnderline);
    s1.addCell(lr);

    WritableFont arial10ptSingleAcc = new WritableFont
      (WritableFont.ARIAL, 
       WritableFont.DEFAULT_POINT_SIZE,
       WritableFont.NO_BOLD,
       false,
       UnderlineStyle.SINGLE_ACCOUNTING);
    WritableCellFormat arialSingleAcc = new WritableCellFormat