write.java

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

      (arial10ptSingleAcc);
    lr = new Label(8,11, "Single Accounting Underline", arialSingleAcc);
    s1.addCell(lr);

    WritableFont arial10ptDoubleAcc = new WritableFont
      (WritableFont.ARIAL, 
       WritableFont.DEFAULT_POINT_SIZE,
       WritableFont.NO_BOLD,
       false,
       UnderlineStyle.DOUBLE_ACCOUNTING);
    WritableCellFormat arialDoubleAcc = new WritableCellFormat
      (arial10ptDoubleAcc);
    lr = new Label(9,11, "Double Accounting Underline", arialDoubleAcc);
    s1.addCell(lr);

    WritableFont times14ptBoldUnderline = new WritableFont
      (WritableFont.TIMES,
       14,
       WritableFont.BOLD,
       false,
       UnderlineStyle.SINGLE);
    WritableCellFormat timesBoldUnderline = new WritableCellFormat
      (times14ptBoldUnderline);
    lr = new Label(6,12, "Times 14 Bold Underline", timesBoldUnderline);
    s1.addCell(lr);

    WritableFont arial18ptBoldItalicUnderline = new WritableFont
      (WritableFont.ARIAL,
       18,
       WritableFont.BOLD,
       true,
       UnderlineStyle.SINGLE);
    WritableCellFormat arialBoldItalicUnderline = new WritableCellFormat
      (arial18ptBoldItalicUnderline);
    lr = new Label(6,13, "Arial 18 Bold Italic Underline", 
                   arialBoldItalicUnderline);
    s1.addCell(lr);

    lr = new Label(0, 15, "Script styles");
    s1.addCell(lr);

    WritableFont superscript = new WritableFont
      (WritableFont.ARIAL,
       WritableFont.DEFAULT_POINT_SIZE,
       WritableFont.NO_BOLD,
       false,
       UnderlineStyle.NO_UNDERLINE,
       Colour.BLACK,
       ScriptStyle.SUPERSCRIPT);
    WritableCellFormat superscriptFormat = new WritableCellFormat
      (superscript);
    lr = new Label(1,15, "superscript", superscriptFormat);
    s1.addCell(lr);

    WritableFont subscript = new WritableFont
      (WritableFont.ARIAL,
       WritableFont.DEFAULT_POINT_SIZE,
       WritableFont.NO_BOLD,
       false,
       UnderlineStyle.NO_UNDERLINE,
       Colour.BLACK,
       ScriptStyle.SUBSCRIPT);
    WritableCellFormat subscriptFormat = new WritableCellFormat
      (subscript);
    lr = new Label(2,15, "subscript", subscriptFormat);
    s1.addCell(lr);

    lr = new Label(0, 17, "Colours");
    s1.addCell(lr);

    WritableFont red = new WritableFont(WritableFont.ARIAL, 
                                        WritableFont.DEFAULT_POINT_SIZE,
                                        WritableFont.NO_BOLD,
                                        false,
                                        UnderlineStyle.NO_UNDERLINE,
                                        Colour.RED);
    WritableCellFormat redFormat = new WritableCellFormat(red);
    lr = new Label(2, 17, "Red", redFormat);
    s1.addCell(lr);

    WritableFont blue = new WritableFont(WritableFont.ARIAL, 
                                         WritableFont.DEFAULT_POINT_SIZE,
                                         WritableFont.NO_BOLD,
                                         false,
                                         UnderlineStyle.NO_UNDERLINE,
                                         Colour.BLUE);
    WritableCellFormat blueFormat = new WritableCellFormat(blue);
    lr = new Label(2, 18, "Blue", blueFormat);
    s1.addCell(lr);

    WritableFont lime = new WritableFont(WritableFont.ARIAL);
    lime.setColour(Colour.LIME);
    WritableCellFormat limeFormat = new WritableCellFormat(lime);
    limeFormat.setWrap(true);
    lr = new Label(4, 18, "Modified palette - was lime, now red", limeFormat);
    s1.addCell(lr);
    
    WritableCellFormat greyBackground = new WritableCellFormat();
    greyBackground.setWrap(true);
    greyBackground.setBackground(Colour.GRAY_50);
    lr = new Label(2, 19, "Grey background", greyBackground);
    s1.addCell(lr);

    WritableFont yellow = new WritableFont(WritableFont.ARIAL, 
                                           WritableFont.DEFAULT_POINT_SIZE,
                                           WritableFont.NO_BOLD,
                                           false,
                                           UnderlineStyle.NO_UNDERLINE,
                                           Colour.YELLOW);
    WritableCellFormat yellowOnBlue = new WritableCellFormat(yellow);
    yellowOnBlue.setWrap(true);
    yellowOnBlue.setBackground(Colour.BLUE);
    lr = new Label(2, 20, "Blue background, yellow foreground", yellowOnBlue);
    s1.addCell(lr);

    lr = new Label(0, 22, "Null label");
    s1.addCell(lr);

    lr = new Label(2, 22, null);
    s1.addCell(lr);

    lr = new Label(0, 24, 
                   "A very long label, more than 255 characters\012" +
                   "Rejoice O shores\012" +
                   "Sing O bells\012" + 
                   "But I with mournful tread\012" +
                   "Walk the deck my captain lies\012" +
                   "Fallen cold and dead\012"+
                   "Summer surprised, coming over the Starnbergersee\012" +
                   "With a shower of rain. We stopped in the Colonnade\012" +
                   "A very long label, more than 255 characters\012" +
                   "Rejoice O shores\012" +
                   "Sing O bells\012" + 
                   "But I with mournful tread\012" +
                   "Walk the deck my captain lies\012" +
                   "Fallen cold and dead\012"+
                   "Summer surprised, coming over the Starnbergersee\012" +
                   "With a shower of rain. We stopped in the Colonnade\012" +                   "A very long label, more than 255 characters\012" +
                   "Rejoice O shores\012" +
                   "Sing O bells\012" + 
                   "But I with mournful tread\012" +
                   "Walk the deck my captain lies\012" +
                   "Fallen cold and dead\012"+
                   "Summer surprised, coming over the Starnbergersee\012" +
                   "With a shower of rain. We stopped in the Colonnade\012" +                   "A very long label, more than 255 characters\012" +
                   "Rejoice O shores\012" +
                   "Sing O bells\012" + 
                   "But I with mournful tread\012" +
                   "Walk the deck my captain lies\012" +
                   "Fallen cold and dead\012"+
                   "Summer surprised, coming over the Starnbergersee\012" +
                   "With a shower of rain. We stopped in the Colonnade\012" +
                   "And sat and drank coffee an talked for an hour\012",
                   arial12format);
    s1.addCell(lr);

    WritableCellFormat vertical = new WritableCellFormat();
    vertical.setOrientation(Orientation.VERTICAL);
    lr = new Label(0, 26, "Vertical orientation", vertical);
    s1.addCell(lr);
    

    WritableCellFormat plus_90 = new WritableCellFormat();
    plus_90.setOrientation(Orientation.PLUS_90);
    lr = new Label(1, 26, "Plus 90", plus_90);
    s1.addCell(lr);


    WritableCellFormat minus_90 = new WritableCellFormat();
    minus_90.setOrientation(Orientation.MINUS_90);
    lr = new Label(2, 26, "Minus 90", minus_90);
    s1.addCell(lr);

    lr = new Label(0, 28, "Modified row height");
    s1.addCell(lr);
    s1.setRowView(28, 24);

    lr = new Label(0, 29, "Collapsed row");
    s1.addCell(lr);
    s1.setRowView(29, true);

    // Write hyperlinks
    try
    {
      Label l = new Label(0, 30, "Hyperlink to home page");
      s1.addCell(l);
      
      URL url = new URL("http://www.andykhan.com/jexcelapi");
      WritableHyperlink wh = new WritableHyperlink(0, 30, 8, 31, url);
      s1.addHyperlink(wh);

      // The below hyperlink clashes with above
      WritableHyperlink wh2 = new WritableHyperlink(7, 30, 9, 31, url);
      s1.addHyperlink(wh2);

      l = new Label(4, 2, "File hyperlink to documentation");
      s1.addCell(l);

      File file = new File("../jexcelapi/docs/index.html");
      wh = new WritableHyperlink(0, 32, 8, 32, file);
      s1.addHyperlink(wh);

      // Add a hyperlink to another cell on this sheet
      wh = new WritableHyperlink(0, 34, 8, 34, 
                                 "Link to another cell",
                                 s1,
                                 0, 180, 1, 181);
      s1.addHyperlink(wh);

      file = new File("\\\\localhost\\file.txt");
      wh = new WritableHyperlink(0, 36, 8, 36, file);
      s1.addHyperlink(wh);

      // Add a very long hyperlink
      url = new URL("http://www.amazon.co.uk/exec/obidos/ASIN/0571058086"+
                   "/qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664");
      wh = new WritableHyperlink(0, 38, 0, 38, url);
      s1.addHyperlink(wh);
    }
    catch (MalformedURLException e)
    {
      System.err.println(e.toString());
    }

    // Write out some merged cells
    Label l = new Label(5, 35, "Merged cells", timesBoldUnderline);
    s1.mergeCells(5, 35, 8, 37);
    s1.addCell(l);

    l = new Label(5, 38, "More merged cells");
    s1.addCell(l);
    Range r = s1.mergeCells(5, 38, 8, 41);
    s1.insertRow(40);
    s1.removeRow(39);
    s1.unmergeCells(r);

    // Merge cells and centre across them
    WritableCellFormat wcf = new WritableCellFormat();
    wcf.setAlignment(Alignment.CENTRE);
    l = new Label(5, 42, "Centred across merged cells", wcf);
    s1.addCell(l);
    s1.mergeCells(5, 42, 10, 42);

    wcf = new WritableCellFormat();
    wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
    wcf.setBackground(Colour.GRAY_25);
    l = new Label(3, 44, "Merged with border", wcf);
    s1.addCell(l);
    s1.mergeCells(3, 44, 4, 46);

    // Clash some ranges - the second range will not be added
    // Also merge some cells with two data items in the - the second data
    // item will not be merged
    /*
    l = new Label(5, 16, "merged cells");
    s1.addCell(l);

    Label l5 = new Label(7, 17, "this label won't appear");
    s1.addCell(l5);
    s1.mergeCells(5, 16, 8, 18);    

    s1.mergeCells(5, 19, 6, 24);
    s1.mergeCells(6, 18, 10, 19);
    */
    
    WritableFont courier10ptFont = new WritableFont(WritableFont.COURIER, 10);
    WritableCellFormat courier10pt = new WritableCellFormat(courier10ptFont);
    l = new Label(0, 49, "Courier fonts", courier10pt);
    s1.addCell(l);

    WritableFont tahoma12ptFont = new WritableFont(WritableFont.TAHOMA, 12);
    WritableCellFormat tahoma12pt = new WritableCellFormat(tahoma12ptFont);
    l = new Label(0, 50, "Tahoma fonts", tahoma12pt);
    s1.addCell(l);

    WritableFont.FontName wingdingsFont = 
      WritableFont.createFont("Wingdings 2");
    WritableFont wingdings210ptFont = new WritableFont(wingdingsFont, 10);
    WritableCellFormat wingdings210pt = new WritableCellFormat
      (wingdings210ptFont);
    l = new Label(0,51, "Bespoke Windgdings 2", wingdings210pt);
    s1.addCell(l);

    WritableCellFormat shrinkToFit = new WritableCellFormat(times12pt);
    shrinkToFit.setShrinkToFit(true);
    l = new Label(3,53, "Shrunk to fit", shrinkToFit);
    s1.addCell(l);

    l = new Label(3,55, "Some long wrapped text in a merged cell", 
                  arial12format);
    s1.addCell(l);
    s1.mergeCells(3,55,4,55);

    l = new Label(0, 57, "A cell with a comment");
    WritableCellFeatures cellFeatures = new WritableCellFeatures();
    cellFeatures.setComment("the cell comment");
    l.setCellFeatures(cellFeatures);
    s1.addCell(l);

    WritableCellFormat indented = new WritableCellFormat(times12pt);
    indented.setIndentation(4);
    l = new Label(0, 59, "Some indented text", indented);
    s1.addCell(l);
    
  }

  /**
   * Adds cells to the specified sheet which test the various border
   * styles
   * 
   * @param s
   */
  private void writeBordersSheet(WritableSheet s) throws WriteException
  {
    s.getSettings().setProtected(true);

    s.setColumnView(1, 15);
    s.setColumnView(2, 15);
    s.setColumnView(4, 15);
    WritableCellFormat thickLeft = new WritableCellFormat();
    thickLeft.setBorder(Border.LEFT, BorderLineStyle.THICK); 
    Label lr = new Label(1,0, "Thick left", thickLeft);
    s.addCell(lr);

    WritableCellFormat dashedRight = new WritableCellFormat();
    dashedRight.setBorder(Border.RIGHT, BorderLineStyle.DASHED);
    lr = new Label(2, 0, "Dashed right", dashedRight);
    s.addCell(lr);

    WritableCellFormat doubleTop = new WritableCellFormat();
    doubleTop.setBorder(Border.TOP, BorderLineStyle.DOUBLE);
    lr = new Label(1, 2, "Double top", doubleTop);
    s.addCell(lr);

    WritableCellFormat hairBottom = new WritableCellFormat();
    hairBottom.setBorder(Border.BOTTOM, BorderLineStyle.HAIR);
    lr = new Label(2, 2, "Hair bottom", hairBottom);
    s.addCell(lr);

    WritableCellFormat allThin = new WritableCellFormat();
    allThin.setBorder(Border.ALL, BorderLineStyle.THIN);
    lr = new Label(4, 2, "All thin", allThin);
    s.addCell(lr);

    WritableCellFormat twoBorders = new WritableCellFormat();
    twoBorders.setBorder(Border.TOP, BorderLineStyle.THICK);
    twoBorders.setBorder(Border.LEFT, BorderLineStyle.THICK);
    lr = new Label(6,2, "Two borders", twoBorders);
    s.addCell(lr);

    // Create a cell in the middle of nowhere (out of the grow region)
    lr = new Label(20, 20, "Dislocated cell - after a page break");
    s.addCell(lr);

    // Set the orientation and the margins
    s.getSettings().setPaperSize(PaperSize.A3);
    s.getSettings().setOrientation(PageOrientation.LANDSCAPE);
    s.getSettings().setHeaderMargin(2);
    s.getSettings().setFooterMargin(2);

    s.getSettings().setTopMargin(3);
    s.getSettings().setBottomMargin(3);

    // Add a header and footera
    HeaderFooter header = new HeaderFooter();
    header.getCentre().append("Page Header");
    s.getSettings().setHeader(header);

    HeaderFooter footer = new HeaderFooter();
    footer.getRight().append("page ");
    footer.getRight().appendPageNumber();