write.java

jexcelapi_2_4, JXL的API, JXL是JAVA读取EXCEL的开源项目

      wh = new WritableHyperlink(0, 34, 8, 34, 
                                 "Link to another cell",
                                 s1,
                                 0, 180, 1, 181);
      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 mergec cell", 
                  arial12format);
    s1.addCell(l);
    s1.mergeCells(3,55,4,55);
  }

  /**
   * 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
    s.setHeader(null, "Page Header", null);
    s.setFooter(null, null, "page &P");

    // Add a page break and insert a couple of rows
    s.addRowPageBreak(18);
    s.insertRow(17);
    s.insertRow(17);
    s.removeRow(17);

    // Add a page break off the screen
    s.addRowPageBreak(30);

    // Add a hidden column
    lr = new Label(10, 1, "Hidden column");
    s.addCell(lr);


    lr = new Label(3, 8, "Hidden row");
    s.addCell(lr);
    s.setRowView(8, true);
  }

  /**
   * Write out loads of labels, in order to test the shared string table
   */
  private void writeLabelsSheet(WritableSheet ws) throws WriteException
  {
    ws.getSettings().setProtected(true);
    ws.getSettings().setPassword("jxl");
    ws.getSettings().setVerticalFreeze(5);

    WritableFont wf = new WritableFont(WritableFont.ARIAL, 12);
    wf.setItalic(true);

    WritableCellFormat wcf = new WritableCellFormat(wf);

    CellView cv = new CellView();
    cv.setDimension(25);
    cv.setFormat(wcf);
    ws.setColumnView(0, cv);
    ws.setColumnView(1, 15);

    for (int i =  0; i < 61; i++)
    {
      Label l1 = new Label(0, i, "Common Label");
      Label l2 = new Label(1, i, "Distinct label number " + i);
      ws.addCell(l1);
      ws.addCell(l2);
    }

    // Frig this test record - it appears exactly on the boundary of an SST
    // continue record

    Label l3 = new Label(0, 61, "Common Label", wcf);
    Label l4 = new Label(1, 61, "1-1234567890", wcf);
    Label l5 = new Label(2, 61, "2-1234567890", wcf);
    ws.addCell(l3);
    ws.addCell(l4);
    ws.addCell(l5);

    for (int i =  62; i < 200; i++)
    {
      Label l1 = new Label(0, i, "Common Label");
      Label l2 = new Label(1, i, "Distinct label number " + i);
      ws.addCell(l1);
      ws.addCell(l2);
    }

    // Add in a last label which doesn't take the common format
    wf = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
    wf.setColour(Colour.RED);
    wcf = new WritableCellFormat(wf);
    wcf.setWrap(true);
    Label l = new Label(0, 205, "Different format", wcf);
    ws.addCell(l);
  }

  /**
   * Test out the formula parser
   */
  private void writeFormulaSheet(WritableSheet ws) throws WriteException
  {
    // Add some cells to manipulate
    Number nc = new Number(0,0,15);
    ws.addCell(nc);
    
    nc = new Number(0,1,16);
    ws.addCell(nc);

    nc = new Number(0,2,10);
    ws.addCell(nc);
    
    nc = new Number(0,3, 12);
    ws.addCell(nc);

    ws.setColumnView(2, 20);
    WritableCellFormat wcf = new WritableCellFormat();
    wcf.setAlignment(Alignment.RIGHT);
    wcf.setWrap(true);
    CellView cv = new CellView();
    cv.setDimension(25);
    cv.setFormat(wcf);
    ws.setColumnView(3, cv);

    // Add in the formulas
    Formula f = null;
    Label l = null;

    f = new Formula(2,0, "A1+A2");
    ws.addCell(f);
    l = new Label(3, 0, "a1+a2");
    ws.addCell(l);

    f = new Formula(2,1, "A2 * 3");
    ws.addCell(f);
    l = new Label(3,1, "A2 * 3");
    ws.addCell(l);

    f = new Formula(2,2, "A2+A1/2.5");
    ws.addCell(f);
    l = new Label(3,2, "A2+A1/2.5");
    ws.addCell(l);

    f = new Formula(2,3, "3+(a1+a2)/2.5");
    ws.addCell(f);
    l = new Label(3,3, "3+(a1+a2)/2.5");
    ws.addCell(l);

    f = new Formula(2,4, "(a1+a2)/2.5");
    ws.addCell(f);
    l = new Label(3,4, "(a1+a2)/2.5");
    ws.addCell(l);

    f = new Formula(2,5, "15+((a1+a2)/2.5)*17");
    ws.addCell(f);
    l = new Label(3,5, "15+((a1+a2)/2.5)*17");
    ws.addCell(l);

    f = new Formula(2, 6, "SUM(a1:a4)");
    ws.addCell(f);
    l = new Label(3, 6, "SUM(a1:a4)");
    ws.addCell(l);

    f = new Formula(2, 7, "SUM(a1:a4)/4");
    ws.addCell(f);
    l = new Label(3, 7, "SUM(a1:a4)/4");
    ws.addCell(l);

    f = new Formula(2, 8, "AVERAGE(A1:A4)");
    ws.addCell(f);
    l = new Label(3, 8, "AVERAGE(a1:a4)");
    ws.addCell(l);

    f = new Formula(2, 9, "MIN(5,4,1,2,3)");
    ws.addCell(f);
    l = new Label(3, 9, "MIN(5,4,1,2,3)");
    ws.addCell(l);

    f = new Formula(2, 10, "ROUND(3.14159265, 3)");
    ws.addCell(f);
    l = new Label(3, 10, "ROUND(3.14159265, 3)");
    ws.addCell(l);

    f = new Formula(2, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
    ws.addCell(f);
    l = new Label(3, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))");
    ws.addCell(l);

    f = new Formula(2,12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
    ws.addCell(f);
    l = new Label(3,12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")");
    ws.addCell(l);

    f = new Formula(2,13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
    ws.addCell(f);
    l = new Label(3,13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")");
    ws.addCell(l);

    f = new Formula(2,14, "IF(A3<=10, \"<= 10\")");
    ws.addCell(f);
    l = new Label(3,14, "IF(A3<=10, \"<= 10\")");
    ws.addCell(l);

    f = new Formula(2, 15, "SUM(1,2,3,4,5)");
    ws.addCell(f);
    l = new Label(3, 15, "SUM(1,2,3,4,5)");
    ws.addCell(l);

    f = new Formula(2, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
    ws.addCell(f);
    l = new Label(3, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")");
    ws.addCell(l);

    f = new Formula(2, 17, "3*4+5");
    ws.addCell(f);
    l = new Label(3, 17, "3*4+5");
    ws.addCell(l);

    f = new Formula(2, 18, "\"Plain text formula\"");
    ws.addCell(f);
    l = new Label(3, 18, "Plain text formula");
    ws.addCell(l);

    f = new Formula(2, 19, "SUM(a1,a2,-a3,a4)");
    ws.addCell(f);
    l = new Label(3, 19, "SUM(a1,a2,-a3,a4)");
    ws.addCell(l);

    f = new Formula(2, 20, "2*-(a1+a2)");
    ws.addCell(f);
    l = new Label(3, 20, "2*-(a1+a2)");
    ws.addCell(l);

    f = new Formula(2, 21, "Number Formats!B1/2");
    ws.addCell(f);
    l = new Label(3, 21, "Number Formats!B1/2");
    ws.addCell(l);

    f = new Formula(2, 22, "IF(F22=0, 0, F21/F22)");
    ws.addCell(f);
    l = new Label(3, 22, "IF(F22=0, 0, F21/F22)");
    ws.addCell(l);

    f = new Formula(2, 22, "RAND()");
    ws.addCell(f);
    l = new Label(3, 22, "RAND()");
    ws.addCell(l);

    StringBuffer buf = new StringBuffer();
    buf.append("'");
    buf.append(workbook.getSheet(0).getName());
    buf.append("'!");
    buf.append(CellReferenceHelper.getCellReference(9, 18));
    buf.append("*25");
    f = new Formula(2, 23, buf.toString());
    ws.addCell(f);
    l = new Label(3, 23, buf.toString());
    ws.addCell(l);

    wcf = new WritableCellFormat(DateFormats.DEFAULT);
    f = new Formula(2, 24, "NOW()", wcf);
    ws.addCell(f);
    l = new Label(3, 24, "NOW()");
    ws.addCell(l);

    f = new Formula(2, 25, "$A$2+A3");
    ws.addCell(f);
    l = new Label(3, 25, "$A$2+A3");
    ws.addCell(l);

    f = new Formula(2, 26, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
    ws.addCell(f);
    l = new Label(3, 26, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))");
    ws.addCell(l);

    f = new Formula(2, 27, "SUM(A1,A2,A3,A4)");
    ws.addCell(f);
    l = new Label(3, 27, "SUM(A1,A2,A3,A4)");
    ws.addCell(l);

    /*
    f = new Formula(2, 28, "SUM(ADDRESS(1,1):A4)");
    ws.addCell(f);
    l = new Label(2, 28, "SUM(ADDRESS(1,1):A4)");
    ws.addCell(l);
    */    


    // Errors
    /*
    f = new Formula(2, 25, "PLOP(15)"); // unknown function
    ws.addCell(f);

    f = new Formula(2, 26, "SUM(15,3"); // unmatched parentheses
    ws.addCell(f);

    f = new Formula(2, 27, "SUM15,3)"); // missing opening parentheses
    ws.addCell(f);

    f = new Formula(2, 28, "ROUND(3.14159)"); // missing args
    ws.addCell(f);

    f = new Formula(2, 29, "NONSHEET!A1"); // sheet not found
    ws.addCell(f);
    */
  }

  /**
   * Write out the images
   */
  private void writeImageSheet(WritableSheet ws) throws WriteException
  {
    Label l = new Label(0, 0, "Weald & Downland Open Air Museum, Sussex");
    ws.addCell(l);

    WritableImage wi = new WritableImage
      (0, 3, 5, 7, new File("resources/wealdanddownland.png"));
    ws.addImage(wi);

    l = new Label(0, 12, "Merchant Adventurers Hall, York");
    ws.addCell(l);

    wi = new WritableImage(5, 12, 4, 10, 
                           new File("resources/merchantadventurers.png"));
    ws.addImage(wi);

    // An unsupported file time
    /*
      wi = new WritableImage(0, 60, 5, 5, new File("resources/somefile.gif"));
      ws.addImage(wi);
    */
  }
}