Examples of jxl.write.Formula

jxl.write.Formula
A cell, created by user applications, which contains a numerical value


    protected Formula formula() throws JRException
    {
      try
      {
        return new Formula(x, y, formula, getLoadedCellStyle(baseStyle));
      }
      catch(Exception e)//FIXMENOW what exceptions could we get here?
      {
        if(log.isWarnEnabled())
        {

View Full Code Here

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


    f = new Formula(2, 38, "IF(ISERROR(G25/G29),0,-1)");
    ws.addCell(f);
    l = new Label(3, 38, "IF(ISERROR(G25/G29),0,-1)");
    ws.addCell(l);


    f = new Formula(2, 39, "SEARCH(\"C\",D40)");
    ws.addCell(f);
    l = new Label(3, 39, "SEARCH(\"C\",D40)");
    ws.addCell(l);


    f = new Formula(2, 40, "#REF!");
    ws.addCell(f);
    l = new Label(3, 40, "#REF!");
    ws.addCell(l);


    nc = new Number (1,41, 79);
    ws.addCell(nc);
    f = new Formula(2, 41, "--B42");
    ws.addCell(f);
    l = new Label(3, 41, "--B42");
    ws.addCell(l);


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


    f = new Formula(2, 43, "A4-A3-A2");
    ws.addCell(f);
    l = new Label(3,43, "A4-A3-A2");
    ws.addCell(l);


    f = new Formula(2, 44, "F29+F34+F41+F48+F55+F62+F69+F76+F83+F90+F97+F104+F111+F118+F125+F132+F139+F146+F153+F160+F167+F174+F181+F188+F195+F202+F209+F216+F223+F230+F237+F244+F251+F258+F265+F272+F279+F286+F293+F300+F305+F308");
    ws.addCell(f);
    l = new Label(3, 44, "F29+F34+F41+F48+F55+F62+F69+F76+F83+F90+F97+F104+F111+F118+F125+F132+F139+F146+F153+F160+F167+F174+F181+F188+F195+F202+F209+F216+F223+F230+F237+F244+F251+F258+F265+F272+F279+F286+F293+F300+F305+F308");
    ws.addCell(l);


    nc = new Number(1,45, 17);
    ws.addCell(nc);
    f = new Formula(2, 45, "formulavalue+5");
    ws.addCell(f);
    l = new Label(3, 45, "formulavalue+5");
    ws.addCell(l);
    
    // Errors

View Full Code Here


    n = (Number) sheet.getWritableCell(0, 73);
    n.setValue(4);


    // Add in a cross sheet formula
    Formula f = new Formula(1, 80, "ROUND(COS(original!B10),2)");
    sheet.addCell(f);


    // Add in a formula from the named cells
    f = new Formula(1, 83, "value1+value2");
    sheet.addCell(f);


    // Add in a function formula using named cells
    f = new Formula(1, 84, "AVERAGE(value1,value1*4,value2)");
    sheet.addCell(f);


    // Copy sheet 1 to sheet 3
    //     w.copySheet(0, "copy", 2);


    // Use the cell deep copy feature
    Label label = new Label(0, 88, "Some copied cells", cf);
    sheet.addCell(label);


    label = new Label(0,89, "Number from B9");
    sheet.addCell(label);


    WritableCell wc = sheet.getWritableCell(1, 9).copyTo(1,89);
    sheet.addCell(wc);


    label = new Label(0, 90, "Label from B4 (modified format)");
    sheet.addCell(label);


    wc = sheet.getWritableCell(1, 3).copyTo(1,90);
    sheet.addCell(wc);


    label = new Label(0, 91, "Date from B17");
    sheet.addCell(label);


    wc = sheet.getWritableCell(1, 16).copyTo(1,91);
    sheet.addCell(wc);


    label = new Label(0, 92, "Boolean from E16");
    sheet.addCell(label);


    wc = sheet.getWritableCell(4, 15).copyTo(1,92);
    sheet.addCell(wc);


    label = new Label(0, 93, "URL from B40");
    sheet.addCell(label);


    wc = sheet.getWritableCell(1, 39).copyTo(1,93);
    sheet.addCell(wc);


    // Add some numbers for the formula copy
    for (int i = 0 ; i < 6; i++)
    {
      Number number = new Number(1,94+i, i + 1 + i/8.0);
      sheet.addCell(number);
    }


    label = new Label(0,100, "Formula from B27");
    sheet.addCell(label);


    wc = sheet.getWritableCell(1, 26).copyTo(1,100);
    sheet.addCell(wc);


    label = new Label(0,101, "A brand new formula");
    sheet.addCell(label);
    
    Formula formula = new Formula(1, 101, "SUM(B94:B96)");
    sheet.addCell(formula);


    label = new Label(0,102, "A copy of it");
    sheet.addCell(label);

View Full Code Here

  private WritableCell createCell( CellInstance _c, WritableWorkbook _xwb, int _col, int _row )
      throws SpreadsheetException
  {
    if (_c instanceof CellWithExpression) {
      final ExpressionNode expr = ((CellWithExpression) _c).getExpression();
      return new Formula( _col, _row, this.formatter.format( expr ) );
    }
    else {
      final Object val = _c.getValue();
      if (val instanceof String) {
        return new jxl.write.Label( _col, _row, ((String) val) );

View Full Code Here

              return new DateTime(column, row, (Date) data, cellFormat);
          } catch (ClassCastException e) {
              return new Label(column, row, "#not a date#", cellFormat);
          }
      case formula:
         return new Formula(column, row, data.toString(), cellFormat);
      case bool:
         return new jxl.write.Boolean(column, row, Boolean.parseBoolean(data.toString()), cellFormat);
      default:
         return new Label(column, row, data.toString(), cellFormat);
      }

View Full Code Here

          } catch (ClassCastException e) {
              String message = Interpolator.instance().interpolate(ResourceBundle.instance().getString("org.jboss.seam.excel.not_a_date"), data.toString());
              return new Label(column, row, message, cellFormat);
          }
      case formula:
         return new Formula(column, row, data.toString(), cellFormat);
      case bool:
         return new jxl.write.Boolean(column, row, Boolean.parseBoolean(data.toString()), cellFormat);
      default:
         return new Label(column, row, data.toString(), cellFormat);
      }

View Full Code Here

TOP

Related Classes of jxl.write.Formula

jxl.demo.ReadWrite

jxl.demo.Write

net.sf.jasperreports.engine.export.JExcelApiExporter$FormulaTextValueHandler

org.formulacompiler.spreadsheet.internal.excel.xls.saver.ExcelXLSSaver

org.jboss.seam.excel.jxl.JXLHelper

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.