SmartXLS Software

• Find & Replace cell content.
• Importing data to worksheet.
• Exporting data from worksheet.
• Data Filter.
• Formulas.
• Formulas Calculation with external workbook.
• Hyperlinks.
• Named Ranges.
• Data Sorting.
• Data Validation.
• Add Row/Column Outlines.
• Range Manipulation.
• Iterator all cell's data in the workbook.

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.Row1;
int col = ra.Col1;

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

Importing data to worksheet

import data from datagrid/dataview into worksheet

  //import data from the data table to worksheet
workBook.ImportDataTable(
dataTable, //data table
true, //field name as first row
0, //row number of the start location
0, //column number of the start location
-1, //-1 for all data rows
dataTable.Columns.Count //column count
);

Exporting data from worksheet to datagrid/dataview

export data from worksheet to datagrid/dataview

//export the worksheet data to data table
DataTable dt = workBook.ExportDataTable(
0, //first row
0, //first col
68898, //last row
35567, //last col
true, //first row as header
true //convert to DateTime object if it match date pattern
);

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

Formulas

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

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

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

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

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

// Sample data
workBook.setText(0, 3, "Employee");
workBook.setText(0, 4, "Income");
workBook.setText(0, 5, "Salary");
workBook.setText(1, 3, "Mike");
workBook.setText(2, 3, "Jone");
workBook.setText(3, 3, "Peter");
workBook.setText(4, 3, "Johanson");
workBook.setNumber(1, 4, 15000);
workBook.setNumber(2, 4, 25000);
workBook.setNumber(3, 4, - 65000);
workBook.setNumber(4, 4, 28000);
workBook.setNumber(1, 5, 15000);
workBook.setNumber(2, 5, 25000);
workBook.setNumber(3, 5, 30000);
workBook.setNumber(4, 5, 27000);
workBook.setNumber(1, 6, 31048);
workBook.setNumber(2, 6, 32874);
workBook.setNumber(3, 6, 34700);
workBook.setNumber(4, 6, 35796);
workBook.setText(5, 4, "Income");
workBook.setText(6, 4, "<0");
workBook.setText(5, 5, "Income");
workBook.setText(6, 5, ">0");
workBook.setSelection(0, 4, 4, 5);

RangeStyle cfm = workBook.getRangeStyle();
cfm.CustomFormat = "$#,##0.00;[Red]$#,##0.00";
workBook.setRangeStyle(cfm);

workBook.recalc();

workBook.write("formulatest.xls");

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);
Console.Out.WriteLine("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");

Named Range

add name range(defined name) to worksheet/workbook

WorkBook workBook = new WorkBook();

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

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

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

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 validation1 = workBook.CreateDataValidation();
validation1.Type = DataValidation.eUser;
validation1.Formula1 = "\"dddd\x0000gggg\x0000hhh\"";
workBook.setSelection("A1:A5");
workBook.DataValidation = validation1;

validation1 = workBook.CreateDataValidation();
validation1.Type = DataValidation.eUser;
validation1.Formula1 = "$B$1:$D$1";
workBook.setSelection("B1:D5");
workBook.DataValidation = validation1;

DataValidation validation2 = workBook.CreateDataValidation();
validation2.Type = DataValidation.eDecimal;
validation2.Operator = DataValidation.eBetween;
validation2.Formula1 = "0";
validation2.Formula2 = "10";
workBook.setSelection(10, 10, 10, 10);
workBook.DataValidation = validation2;

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

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

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.NumSheets;
for (int sheetIndex = 0; sheetIndex < numsheets; sheetIndex++)
{
//select sheet
workbook.Sheet = sheetIndex;
string sheetName = workbook.getSheetName(sheetIndex);
//get the last row of this sheet.
int lastRow = workbook.LastRow;
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;
}
}
}
}