write.java

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

    s.getSettings().setFooter(footer);

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

    WritableCellFormat allThickRed = new WritableCellFormat();
    allThickRed.setBorder(Border.ALL, BorderLineStyle.THICK, Colour.RED);
    lr = new Label(1, 5, "All thick red", allThickRed);
    s.addCell(lr);

    WritableCellFormat topBottomBlue = new WritableCellFormat();
    topBottomBlue.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLUE);
    topBottomBlue.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLUE);
    lr = new Label(4, 5, "Top and bottom blue", topBottomBlue);
    s.addCell(lr);
  }

  /**
   * 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.setSize(25 * 256);
    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.setSize(25 * 256);
    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, 23, "RAND()");
    ws.addCell(f);
    l = new Label(3, 23, "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, 24, buf.toString());
    ws.addCell(f);
    l = new Label(3, 24, buf.toString());
    ws.addCell(l);

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

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

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

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

    l = new Label(1, 29, "a1");
    ws.addCell(l);
    f = new Formula(2, 29, "SUM(INDIRECT(ADDRESS(2,29)):A4)");
    ws.addCell(f);
    l = new Label(3, 29, "SUM(INDIRECT(ADDRESS(2,29):A4)");
    ws.addCell(l);

    f = new Formula(2, 30, "COUNTIF(A1:A4, \">=12\")");
    ws.addCell(f);
    l = new Label(3, 30, "COUNTIF(A1:A4, \">=12\")");
    ws.addCell(l);

    f = new Formula(2, 31, "MAX($A$1:$A$4)");
    ws.addCell(f);
    l = new Label(3, 31, "MAX($A$1:$A$4)");
    ws.addCell(l);

    f = new Formula(2, 32, "OR(A1,TRUE)");
    ws.addCell(f);
    l = new Label(3, 32, "OR(A1,TRUE)");
    ws.addCell(l);

    f = new Formula(2, 33, "ROWS(A1:C14)");
    ws.addCell(f);
    l = new Label(3, 33, "ROWS(A1:C14)");
    ws.addCell(l);

    f = new Formula(2, 34, "COUNTBLANK(A1:C14)");
    ws.addCell(f);
    l = new Label(3, 34, "COUNTBLANK(A1:C14)");
    ws.addCell(l);

    f = new Formula(2, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)");
    ws.addCell(f);
    l = new Label(3, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)");
    ws.addCell(l);

    f = new Formula(2, 36, 
       "HYPERLINK(\"http://www.amazon.co.uk/exec/obidos/ASIN/0571058086qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664\",  \"Long hyperlink\")");
    ws.addCell(f);

    f = new Formula(2, 37, "1234567+2699");
    ws.addCell(f);
    l = new Label(3, 37, "1234567+2699");
    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);
    */
  }
}