Data Manipulation(VB.Net)

 
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
Dim ra As 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);
Dim row, col As Integer
row = ra.Row1
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)

 
top
Importing data to worksheet

import data from datagrid/dataview into worksheet

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

 
top
Exporting data from worksheet to datagrid/dataview

export data from worksheet to datagrid/dataview

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

 
top
Data Filter

add data filters to worksheet

 Dim WorkBook As 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")

Dim col, row As Integer
For col = 1 To 6
For row = 1 To 6
WorkBook.setFormula(row, col, "RAND()")
Next
Next
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.

Dim workBook As New WorkBook
Dim rowIndex As Integer = 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")
Dim nextFunction As 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 = "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)
Dim cfm As RangeStyle
cfm = workBook.getRangeStyle()
cfm.CustomFormat = "$#,##0.00;[Red]$#,##0.00"
workBook.setRangeStyle(cfm)
workBook.recalc()
workBook.write("formulatest.xls");

 
top
Formulas Calculation with external workbook

calculate formula with external workbook link

Dim workBook, externalWk As 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)")
Dim result As Double
result = workBook.getNumber(1, 1)
Console.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")

 
top
Named Range

add name range(defined name) to worksheet/workbook

Dim workBook As 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()

 
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, 3, 7, 6, True, -1, 0, 0)
'start row
'start col
'end row
'end col
'data is sorted by row
'first row and descend
'no used
'no used

'sort one range with key array
Dim keys() As Integer = {1, 2, -3, -4}
workBook.sort(1, 1, 6, 6, True, keys)
'start row
'start col
'end row
'end col
'data is sorted by row
'key array,1-asc,2-asc,3-dsc,4-dsc

 
top
Data Validation

add data validation to worksheet

Dim workBook As New WorkBook

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

Dim validation1 As 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

Dim validation2 As 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

 
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)

Dim numsheets, sheetIndex, lastRow, lastColForRow, colIndex As Integer
Dim sheetName As String
numsheets = workBook.NumSheets
sheetIndex = 0
For sheetIndex = 0 To numsheets
'select sheet
workBook.Sheet = sheetIndex
sheetName = workBook.getSheetName(sheetIndex)
'get the last row of this sheet.
lastRow = workBook.LastRow
For rowIndex = 0 To lastRow
'get the last column of this row.
lastColForRow = workBook.getLastColForRow(rowIndex)
For colIndex = 0 To lastColForRow
Dim n As Double
Dim t, f As String
Dim type As Integer
type = workBook.getType(rowIndex, colIndex)
If (type < 0) Then
f = workBook.getFormula(rowIndex, colIndex)
type -= 0
End If
Select Case type
Case workBook.TypeNumber
n = workBook.getNumber(rowIndex, colIndex)

Case workBook.TypeText
t = workBook.getText(rowIndex, colIndex)

Case workBook.TypeLogical
Case workBook.TypeError
Case workBook.TypeNumber
n = workBook.getNumber(rowIndex, colIndex)

Case workBook.TypeEmpty
End Select
Next colIndex
Next rowIndex
Next sheetIndex