Data Manipulation(Java)

 
top
Find & Replace cell content

 find cell text and replace with new content.

//find text "what" in the worksheet and locate in the first cell if value matched
RangeArea ra = workBook.find(1, 1, "what");
//last parameter specify if it need meet entire cell content,or it will match if the text is contained.
//RangeArea ra = workBook.find(1, 1, "what", true);
int row = ra.getRow1();
int col = ra.getCol1();

//find all cells with value "what" and replace the content to "this"
workBook.replace("what", "this");
//only entire value matched be replaced
workBook.replace("what", "this", true);

 
top
Data Filter

add data filters to worksheet

WorkBook workBook = new WorkBook();
//set data
workBook.setText(0,1,"Jan");
workBook.setText(0,2,"Feb");
workBook.setText(0,3,"Mar");
workBook.setText(0,4,"Apr");
workBook.setText(0,5,"Jun");
workBook.setText(1,0,"Comfrey");
workBook.setText(2,0,"Bananas");
workBook.setText(3,0,"Papaya");
workBook.setText(4,0,"Mango");
workBook.setText(5,0,"Lilikoi");
for(int col = 1; col <= 5; col++)
for(int row = 1; row <= 5; row++)
workBook.setFormula(row, col, "RAND()");
workBook.setText(6, 0, "Total");
workBook.setFormula(6, 1, "SUM(B2:B6)");
workBook.setSelection("B7:F7");
//auto fill the range with the first cell's formula or data
workBook.editCopyRight();

//select range A1:F7
workBook.setSelection(0,0,6,5);
//Creating an AutoFilter
workBook.autoFilter();

//Counting the auto filtered value in the cell "E11"
workBook.setFormula(10, 4, "SUBTOTAL(2,B1:B7)");

workBook.write("autofiller.xls");

 
top
Formulas

support all Excel formulas and can get value with the real time calculation engine.

WorkBook m_view = new WorkBook();
int rowIndex = 0;

//set column width,units equal to 1/256th of the character 0's width in the default font
m_view.setColWidth(0,10*256);
m_view.setColWidth(1,25*256);
m_view.setColWidth(2,25*256);
m_view.setColWidth(4,14*256);
m_view.setColWidth(5,14*256);

m_view.setText(rowIndex, 1 , "result");
m_view.setText(rowIndex, 2, "formula");
String nextFunction = "ABS(-1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ACCRINT(DATE(08,9,12),DATE(08,9,30),DATE(08,12,15),0.2,1000,1,1)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ACCRINTM(DATE(97,3,1),DATE(98,2,1),0.2,1000,1)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ACOS(.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ACOSH(1.2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ADDRESS(5,5,1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "AMORDEGRC(1000,DATE(95,5,5),DATE(95,7,1),100,0,0.15)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "AMORLINC(1000,DATE(95,5,5),DATE(95,7,1),100,0,0.15)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "AND(TRUE(), FALSE())";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "AREAS(B2:C3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ASIN(1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ASINH(5.3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ATAN(3.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ATAN2(3, 6)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ATANH(.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "AVEDEV(1,2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "AVERAGE(E2:E6)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "AVERAGEA(1,2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "BESSELI(3,1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "BESSELJ(2.5,1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "BESSELK(5,10)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "BESSELY(3,0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "BETADIST(0.5, 10, 1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "BETAINV(0.5, 1, 1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "BIN2DEC(10000000)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "BIN2HEX(111111)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "BIN2OCT(1110100)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "BINOMDIST(6,10,0.5,FALSE)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CEILING(1.23459, .05)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CELL(\"width\",C4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CHAR(70)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CHIDIST(9.6,10)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CHIINV(0.05,5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CHOOSE(2,\"Q1\", \"Q2\", \"Q3\", \"Q4\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CLEAN(\"Payments \" & CHAR(8) & \"Due\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CODE(\"A\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COLUMN(B3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COLUMNS(A1:D5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CONCATENATE (\"Sale \", \"Price\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CONFIDENCE(0.05, 2.5, 50)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COMPLEX(2,3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CONVERT(1,\"m\",\"yd\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COS(5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COSH(2.10)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COUNT(5, 6, \"Q2\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COUNTA(32, 45, \"Earnings\", \"\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COUNTBLANK(A1:D2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COUNTIF(C38:C40,0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COUPDAYBS(DATE(93,1,25),DATE(94,8,31),2,0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COUPDAYS(DATE(93,1,25),DATE(94,8,31),2,0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COUPDAYSNC(DATE(93,1,25),DATE(94,8,31),2,0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COUPNCD(DATE(93,1,25),DATE(94,8,31),2,0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COUPNUM(DATE(93,1,25),DATE(94,8,31),2,0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COUPPCD(DATE(93,1,25),DATE(94,8,31),2,0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "COVAR({1,2,3,4,5},{2,4,6,8,10})";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CRITBINOM(1000,0.5,0.3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CUMIPMT(0.009166667,60,17000,1,60,0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "CUMPRINC(0.009166667,60,17000,1,34,0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DATE(94, 6, 21)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DATEDIF(NOW(),DATE(2008,8,8),\"d\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DATEVALUE(\"3/6/05\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DAVERAGE(D1:F5,\"Salary\",F6:F7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DAY(34399)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DAYS360(\"1/11/06\", \"2/11/06\") ";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DB(10000, 1000, 7, 3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DCOUNT(D1:F5,\"Salary\",E6:E7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DCOUNTA(D1:F5,\"Employee\",F6:F7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DDB(10000,1000, 7, 3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DEC2BIN(256)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DEC2HEX(10,5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DEC2OCT(100)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DEGREES(6.283185307)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DELTA(6,7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DEVSQ(1, 2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DGET(D1:F5,\"Employee\",E6:E7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DISC(DATE(92,7,15),DATE(95,12,30),93,100)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DMAX(D1:F5,\"Salary\",F6:F7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DMIN(D1:F5,\"Salary\",F6:F7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DOLLAR(1023.789)"; //local currency
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DOLLARDE(25.3,4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DOLLARFR(25.25,4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DPRODUCT(D1:F5,\"Salary\",F6:F7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DSTDEV(D1:F5,\"Salary\",F6:F7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DSTDEVP(D1:F5,\"Salary\",F6:F7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DSUM(D1:F5,\"Salary\",F6:F7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "DURATION(DATE(89,3,17),DATE(99,3,17),0.07,0.08,2,1)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "EDATE(DATE(2007,1,1),2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "EFFECT(0.0675,12)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "EOMONTH(DATE(2007,1,1),2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ERF(2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ERFC(1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ERROR.TYPE(B76)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "EVEN(2.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "EXACT(\"Match\", \"Match\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "EXP(2.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "EXPONDIST(0.5,1,TRUE)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FACT(2.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FACTDOUBLE(15)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FALSE()";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FDIST(2, 3, 4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FIND(\"time\", \"There憇 no time like the present\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FINV(0.05, 1, 4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FISHER (0.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FISHERINV(10)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FIXED(2000.5, 3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FLOOR(1.23459, .05)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FORECAST(0.5, {1, 2, 4, 6, 7, 9}, {0, 2, 4, 5, 7, 8})";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FTEST({51,45,41,27},{91,37,89,82})";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FV(5%,8,-500)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "FVSCHEDULE(1000,{0.2,0.21,0.22})";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "GAMMADIST (12, 3, 7, TRUE)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "GAMMAINV (0.01, 8, 2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "GAMMALN(5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "GCD({1234567890,3000})";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "GEOMEAN(24, 6)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "GESTEP(6,7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "GROWTH({4,6,8,9},,3.5,TRUE)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);

nextFunction = "HARMEAN(5,4,25,60,14,26)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "HEX2BIN(10)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "HEX2DEC(10)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "HEX2OCT(100)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "HLOOKUP(\"Northeast\",B1:E5,3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "HOUR(34259.4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "HYPERLINK(\"http://www.smartxls.com\",\"java spreadsheet component\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IF(A1>10, \"Greater\", \"Less\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMABS(\"3+4i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMAGINARY(\"2+3i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMARGUMENT(\"1+1i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMCONJUGATE(\"2+3i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMCOS(\"2+i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMDIV(\"-10+10i\",\"1+2i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMEXP(\"2+3i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMLN(\"2+3i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMLOG10(\"2+3i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMLOG2(\"2+3i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMPOWER(\"1+2i\",2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMPRODUCT(\"1+2i\",30)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMREAL(\"2+3i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMSIN(\"2+3i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMSQRT(\"2+3i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMSUB(\"2+3i\",\"3+4i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IMSUM(\"2+3i\",\"3+4i\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "INDEX(A2:B6,3,2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "INDIRECT(B5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "INFO(\"release\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "INT(10.99)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "INTRATE(DATE(94,10,23),DATE(95,7,7),98.31,100)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IPMT(8%/12, 2, 48, 18000)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "IRR(E2:E5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ISBLANK(A1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ISERR(A1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ISERROR(1/0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ISEVEN(9.8)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ISLOGICAL(ISBLANK(A1))";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ISNA(A1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ISNONTEXT(A3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ISNUMBER(123.45)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ISODD(9.8)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ISREF(A3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ISTEXT(\"2nd Quarter\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "LCM(15,20)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "LEFT(\"2nd Quarter\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "LEN(\"3rd Quarter\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "LN(12.18)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "LOG(10)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "LOG10(260)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "LOGINV(0.223218,18,20)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "LOGNORMDIST(16,18,20)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "LOOKUP(\"Mike\", D2:D5, E2:E5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "LOWER(\"3rd Quarter\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MATCH(7600, B2:B7,1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MAX(50, 100, 150, 500, 200)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MAXA(50,100,150,\"500\",200)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MDURATION(DATE(89,3,17),DATE(99,3,17),0.07,0.08,2,1)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MEDIAN(1,2,3,4,5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MID(\"Travel Expenses\", 8, 8)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MIN(50, 100, 150, 500, 200)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MINA(50,100,150,\"500\",200)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MINUTE(34506.4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MIRR(E2:E5, 12%, 8%)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MOD(-23,3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MODE(1,2,3,3,4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MONTH(34626)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "MROUND(13,2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "N(A4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "NA()";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "NETWORKDAYS(1,365)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "NOMINAL(0.0696,2)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "NOT(TRUE())";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "NOW()";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "NPER(12%/12,-350,-300,16000,1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "NPV(8%,-12000,3000,3000,3000,7000)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "OCT2BIN(10)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "OCT2DEC(10)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "OCT2HEX(10)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ODD(3.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ODDFPRICE(DATE(8,11,11),DATE(21,3,1),DATE(8,10,15),DATE(9,3,1),0.07,0.06,100,2)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ODDFYIELD(DATE(8,11,11),DATE(21,3,1),DATE(8,10,15),DATE(9,3,1),0.07,98,100,2)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ODDLPRICE(DATE(8,2,7),DATE(8,6,15),DATE(7,10,15),0.05,99.8,100,4)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ODDLYIELD(DATE(8,2,7),DATE(8,6,15),DATE(7,10,15),0.07,98.4,100,2)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "OFFSET(B1, 3, 2, 1, 1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "OR(1 + 1 = 1, 5 + 5 = 10)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PEARSON({2,5,8},{3,6,7})";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PERCENTILE({1,2,3,4,5}, .25)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PERCENTRANK ({1, 2, 3, 4, 5}, 3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PERMUT(4,2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PI()";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PMT(8%/12, 48, 18000)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "POISSON(15,15,1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PPMT(8%/12, 2, 48, 18000)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "POWER(3,2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PRICE(DATE(97,4,19),DATE(2001,11,25),0.05,0.075,100,4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PRICEDISC(DATE(92,5,7),DATE(99,12,31),0.0575,100)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PRICEMAT(DATE(93,4,8),DATE(99,2,14),DATE(89,2,14),0.045,0.075)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PROPER(\"3rd Quarter\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "PV(8%/12, 48, 439.43)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "QUOTIENT(9,7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "RADIANS(-180)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "RAND()*10";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "RANDBETWEEN(15,47)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "RATE(48,-439.43,18000)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "RECEIVED(DATE(94,1,1),DATE(97,10,1),50,0.0575)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "REPLACE(\"For the year: 1993\",18,1,\"4\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "REPT(\"error-\", 3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "RIGHT(\"2nd Quarter\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ROMAN(499)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ROUND(123.456, 2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ROUNDDOWN(3.14159, 3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ROUNDUP(76.9,0)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ROW(B3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ROWS(A1:D5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SEARCH(\"?5\", \"Bin b45\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SECOND(.259)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SIGN(-123)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SIN(45)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SINH(1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SLN(10000, 1000, 7)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SQRT(9)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SQRTPI(2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "STANDARDIZE (95, 50, 4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "STDEV(4.0, 3.0, 3.0, 3.5, 2.5, 4.0, 3.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "STDEVA(4.0, 3.0, 3.0, 3.5, 2.5, \"4.0\", 3.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "STDEVP(4.0, 3.0, 3.0, 3.5, 2.5, 4.0, 3.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "STDEVPA(4.0, 3.0, 3.0, 3.5, 2.5, \"4.0\", 3.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SUBTOTAL(1,E2:E5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SUM(1000, 2000, 3000)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SUMIF(A1:B2,\"=0\",A4:B5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SUMPRODUCT(E2:E3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SUMSQ(9, 10, 11)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "SYD(10000, 1000, 7, 3)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "T(\"Report\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TAN(0.645)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TANH(-2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TBILLEQ(DATE(91,6,28),DATE(91,10,23),0.098)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TBILLPRICE(DATE(91,6,28),DATE(91,10,23),0.098)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TBILLYIELD(DATE(91,6,28),DATE(91,10,23),98.5)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TDIST(1.75,3,1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TEXT(123.62, \"0.000\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TIME(12, 26, 24)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TIMEVALUE(\"1:43:43 am\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TINV (0.01, 2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TODAY()";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TRIM(\" Level 3, Gate 45 \")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TRUE()";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TRUNC(123.456, 2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "TYPE(A1)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "UPPER(\"3rd Quarter\")";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "USDOLLAR(1023.789)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "VALUE(9800)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "VAR(4.0, 3.0, 3.5, 2.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "VARA(4.0, 3.0, \"3.5\", 2.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "VARP(4.0, 3.0, 3.5, 2.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "VARPA(4.0, 3.0, \"3.5\", 2.5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "VDB(10000, 1000, 7, 3, 4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "VLOOKUP(\"Jone\",D2:E5,2)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "WEEKDAY(34399.92)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "WEIBULL(100,3,120,TRUE)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "WORKDAY(DATEVALUE(\"2007/01/03\"),5)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "XIRR(E2:E5,G2:G5,0.2)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "XNPV(0.6,E2:E5,G2:G5)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "YEAR(34328)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "YEARFRAC(DATE(97,1,11),DATE(97,5,15))";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "YIELD(DATE(97,5,6),DATE(99,12,31),0.06,0.92,100,4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "YIELDDISC(DATE(94,10,23),DATE(95,7,7),98.31,100)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "YIELDMAT(DATE(86,9,19),DATE(94,2,28),DATE(84,2,28),0.0525,100.0154)";
m_view.setFormula(++rowIndex, 1, nextFunction);
m_view.setText(rowIndex, 2, nextFunction);
nextFunction = "ZTEST ({1,2,3}, 4)";
m_view.setFormula(++rowIndex, 1 , nextFunction);
m_view.setText(rowIndex, 2, nextFunction);

// Sample data
m_view.setText(0, 3, "Employee");
m_view.setText(0, 4, "Income");
m_view.setText(0, 5, "Salary");
m_view.setText(1, 3, "Mike");
m_view.setText(2, 3, "Jone");
m_view.setText(3, 3, "Peter");
m_view.setText(4, 3, "Johanson");
m_view.setNumber(1, 4, 15000);
m_view.setNumber(2, 4, 25000);
m_view.setNumber(3, 4, -65000);
m_view.setNumber(4, 4, 28000);
m_view.setNumber(1, 5, 15000);
m_view.setNumber(2, 5, 25000);
m_view.setNumber(3, 5, 30000);
m_view.setNumber(4, 5, 27000);
m_view.setNumber(1, 6, 31048);
m_view.setNumber(2, 6, 32874);
m_view.setNumber(3, 6, 34700);
m_view.setNumber(4, 6, 35796);
m_view.setText(5, 4, "Income");
m_view.setText(6, 4, "<0");
m_view.setText(5, 5, "Income");
m_view.setText(6, 5, ">0");
m_view.setSelection(0, 4, 4, 5);
RangeStyle rangeStyle = m_view.getRangeStyle();
rangeStyle.setMergeCells(true);
rangeStyle.setCustomFormat("$#,##0.00;[Red]$#,##0.00");
m_view.setRangeStyle(rangeStyle);
m_view.setAutoRecalc(false);
m_view.recalc();
m_view.write("formulatest.xls");

 
top
Formulas Calculation with external workbook

calculate formula with external workbook link

WorkBook workBook = new WorkBook();
WorkBook externalWk = new WorkBook();
externalWk.setNumber(0, 0, 458);
workBook.setWorkbookName("wb1");
externalWk.setWorkbookName("wb2");
workBook.setGroup("group");
externalWk.setGroup("group");
workBook.setFormula(0, 1, 1, "SUM('[wb2]Sheet1'!$A$1:$D$4)");
double result = workBook.getNumber(1, 1);
System.out.println("Result:" + result);

Hyperlink

add hyperlink to worksheet

//add an url link to F6
workBook.addHyperlink(5, 5, 5, 5, "http://www.smartxls.com/", HyperLink.kURLAbs,
                      "Hello,web url hyperlink!");

//add a file link to F7
workBook.addHyperlink(6, 5, 6, 5, "c:\\", HyperLink.kFileAbs, "file link");

 
top
Named Range

add name range(defined name) to worksheet/workbook

WorkBook workBook = new WorkBook();

workBook.setDefinedName("Products", "$A$1:$A$6");

//workBook.setDefinedName("Sheet1!One", "Sheet1!$C$3"); //sheet level defined name

workBook.setDefinedName("One", "$C$3");
workBook.setDefinedName("Two", "$D$3");
workBook.setSelection("E3");
workBook.setFormula(2, 4, "SUM(One, Two)");
workBook.recalc();

 
top
Data Sorting

sort range by specify the rows/columns

//sort one range with three keys
//keys value are the number of the row/column,
//0-indicates no key.
//Positive numbers as ascending sort key.
//Negative numbers as descending sort key.
workBook.sort(
4, //start row
3, //start col
7, //end row
6, //end col
true, //data is sorted by row
-1, //first row and descend
0, //no used
0 //no used
);

//sort one range with key array
int[] keys = new int[] {1, 2, -3, -4};
workBook.sort(
1, //start row
1, //start col
6, //end row
6, //end col
true, //data is sorted by row
keys //key array,1-asc,2-asc,3-dsc,4-dsc
);

 
top
Data Validation

add data validation to worksheet

WorkBook workBook = new WorkBook();

workBook.setText(0, 1, "Apple");
workBook.setText(0, 2, "Orange");
workBook.setText(0, 3, "Banana");

DataValidation dataValidation = workBook.CreateDataValidation();
dataValidation.setType(DataValidation.eUser);
dataValidation.setFormula1("\"dddd\u00000\u0000gggg\0hhh\"");
workBook.setSelection("A1:A5");
workBook.setDataValidation(dataValidation);

dataValidation = workBook.CreateDataValidation();
dataValidation.setType(DataValidation.eUser);
dataValidation.setFormula1("Sheet2!$B$1:$D$1");
dataValidation.setFormula1("D5");
workBook.setSelection("B1:B5");
workBook.setDataValidation(dataValidation);

dataValidation = workBook.CreateDataValidation();
dataValidation.setType(DataValidation.eDecimal);
dataValidation.setOperator(DataValidation.eBetween);
dataValidation.setFormula1("5");
dataValidation.setFormula2("10");
workBook.setSelection("C1:C5");
workBook.setDataValidation(dataValidation);

 
top
Add Row/Column Outlines

group and ungroup rows and columns

//group row(2-6)
workBook.setRowOutlineLevel(1, 5, 1, false);
//group column(3-9)
workBook.setColOutlineLevel(2, 8, 1, false);

 
top
Range Manipulation(insert range,copy range,delete range)

copy one range from source to destination,insert range,delete specified range,clear one cell to blank

//select B2:C3 and insert range with rows direction shift
workBook.insertRange(1, 1, 2, 2, WorkBook.ShiftRows);
//copy range from B2:D4 to E5:G7
workBook.copyRange(4, 4, 6, 6, 1, 1, 3, 3);
//clear range data of B2:E5
workBook.clearRange(1, 1, 4, 4);
//clear one single cell B2
workBook.clearCell(1, 1);

 
top
Iterator all cell's data in the workbook.

iterator all cell's data in the workbook(sheet by sheet,row by row)

int numsheets = workBook.getNumSheets();
for (int sheetIndex = 0; sheetIndex < numsheets; sheetIndex++)
{
//select sheet
workBook.setSheet(sheetIndex);
String sheetName = workBook.getSheetName(sheetIndex);
//get the last row of this sheet.
int lastRow = workBook.getLastRow();
for (int rowIndex = 0; rowIndex < lastRow; rowIndex++)
{
//get the last column of this row.
int lastColForRow = workBook.getLastColForRow(rowIndex);
for (int colIndex = 0; colIndex < lastColForRow; colIndex++)
{
double n;
String t, f;
int type = workBook.getType(rowIndex, colIndex);
if (type < 0)
{
f = workBook.getFormula(rowIndex, colIndex);
type -= 0;
}
switch (type)
{
case WorkBook.TypeNumber:
n = workBook.getNumber(rowIndex, colIndex);
continue;

case WorkBook.TypeText:
t = workBook.getText(rowIndex, colIndex);
continue;

case WorkBook.TypeLogical:
case WorkBook.TypeError:
n = workBook.getNumber(rowIndex, colIndex);
continue;

case WorkBook.TypeEmpty:
continue;
}
}
}
}

 
top
Date cell.

The date cell is the value stored as numeric(double) value with date formatting in excel.

//a double value representing the number of days since 1/1/1900.
//Fractional days represent hours,minutes,and seconds.
double dd = workBook.getNumber(1, 1);//B2 is a date cell.
System.out.println("days since 1900:" + dd);
String ds = workBook.getText(1, 1);
//the formatted text as it is showed in Excel.
String dfs = workBook.getFormattedText(1, 1);
System.out.println("Formatted text:" + dfs);

//method 1 to set date value
//set cell value with formatted text(mm/dd/yy).
workBook.setEntry(3, 2, "08/08/2009");

//method 2 to set date value
//set the cell with number value.
workBook.setNumber(3, 2, 40033.0);
//formatting the value to date 'yyyy/mm/dd'
RangeStyle rs = workBook.getRangeStyle();
rs.setCustomFormat("yyyy/mm/dd");
workBook.setRangeStyle(rs, 3, 2, 3, 2);

 
top
Misc.

read the calculated cell value stored in Excel;
clear one cell's vaue&format;
format cell address by index;
get cell index from address text;
set ranges with NumberStoredAsText attribute;

//get the value without recalc this cell
String originalText = workBook.getText(1, 1, false);

//clear the cell content&format
workBook.clearCell(2, 2);

//get the cell address by format the row and column reference
String cellAddress = workBook.formatRCNr(1, 1, true); // '$B$2'

//get the row and column reference from the cell address
RangeArea cellRange = workBook.getFormulaRange("$B$2");
int row1 = cellRange.getRow1();
int col1 = cellRange.getCol1();

//this will set 'NumberStoredAsText' attribute to the ranges(B2:C2,D4:D5,F6) in the currect selected sheet
java.util.ArrayList<RangeArea> rangesNumberStoredAsText = (java.util.ArrayList<RangeArea>) workBook.getSheetProperty(SheetVarType.ignoredError_numberStoredAsText);
rangesNumberStoredAsText.add(new RangeArea(1,1,1,2)); //B2:C2
rangesNumberStoredAsText.add(new RangeArea(3,3,4,3)); //D4:D5
rangesNumberStoredAsText.add(new RangeArea(5,5,5,5)); //F6