write.java

JAVA读取excel表格里的数据,与jdbc功能相当,是一个包

    s.addCell(n);
    
    // Lots of numbers
    for (int row = 0; row < 100; row++)
    {
      for (int col = 8; col < 108; col++)
      {
        n = new Number(col, row, col+row);
        s.addCell(n);
      }
    }

    // Lots of numbers
    for (int row = 101; row < 3000; row++)
    {
      for (int col = 0; col < 25; col++)
      {
        n = new Number(col, row, col+row);
        s.addCell(n);
      }
    }
  }

  /**
   * Adds cells to the specified sheet which test the various date formats
   * 
   * @param s 
   */
  private void writeDateFormatSheet(WritableSheet s) throws WriteException
  {
    WritableCellFormat wrappedText = new WritableCellFormat
      (WritableWorkbook.ARIAL_10_PT);
    wrappedText.setWrap(true);

    s.setColumnView(0, 20);
    s.setColumnView(2, 20);
    s.setColumnView(3, 20);
    s.setColumnView(4, 20);

    s.getSettings().setFitWidth(2);
    s.getSettings().setFitHeight(2);

    Calendar c = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
    c.set(1975, 4, 31, 15, 21, 45);
    c.set(Calendar.MILLISECOND, 660);
    Date date = c.getTime();
    c.set(1900, 0, 1, 0, 0, 0);
    c.set(Calendar.MILLISECOND, 0);

    Date date2 = c.getTime();
    c.set(1970, 0, 1, 0, 0, 0);
    Date date3 = c.getTime();
    c.set(1918, 10, 11, 11, 0, 0);
    Date date4 = c.getTime();
    c.set(1900, 0, 2, 0, 0, 0);
    Date date5 = c.getTime();
    c.set(1901, 0, 1, 0, 0, 0);
    Date date6 = c.getTime();
    c.set(1900, 4, 31, 0, 0, 0);
    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();
    c.set(1066, 9, 14, 0, 0, 0);
    Date date14 = c.getTime();

    // Built in date formats
    SimpleDateFormat sdf = new SimpleDateFormat("dd MMM yyyy HH:mm:ss.SSS");
    sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
    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, DateTime.GMT);
    s.addCell(dt);

    cf1 = new WritableCellFormat(DateFormats.FORMAT2);
    dt = new DateTime(0,3,date, cf1,DateTime.GMT);
    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, DateTime.GMT);
    s.addCell(dt);

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

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

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

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

    cf1 = new WritableCellFormat(DateFormats.FORMAT12);
    dt = new DateTime(0,13,date, cf1, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    s.addCell(dt);
    dt = new DateTime(4, 5, date13, cf1, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    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, DateTime.GMT);
    s.addCell(dt);

    // Check out the Battle of Hastings date Oct 14th, 1066
    l = new Label(0,25, "Battle of Hastings " + sdf.format(date14),
                  wrappedText);
    s.addCell(l);

    cf1 = new WritableCellFormat(DateFormats.FORMAT2);
    dt = new DateTime(0, 26, date14, cf1, DateTime.GMT);
    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);