//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); #region worksheet1 FILTER IWorksheet worksheet1 = workbook.ActiveSheet; // Set value. worksheet1.Name = "FILTER"; worksheet1.Range["A1"].Value = "FILTER Function Examples"; worksheet1.Range["B3"].Value = "Filter Syntax:"; worksheet1.Range["B4"].Value = "FILTER(array,include,[if_empty])"; worksheet1.Range["B5"].Value = "array: The array or reference to filter."; worksheet1.Range["B6"].Value = "include: The filter condtion expressed using an intersecting sub-range and conditional expression -- see examples below."; worksheet1.Range["B7"].Value = "if_empty: Optional value to return when the filter result is empty; if not specified then empty result shows #CALC! error."; worksheet1.Range["B9"].Value = "FilterData"; var tableHeader = new object[] { "Product Line", "Product", "Amount", "Units", "Total" }; worksheet1.Range["B11:E22"].Value = new object[,] { { "Spread.NET", "NewLicense", 1499d,2}, { "Spread.NET", "NewLicense", 1499d,4}, { "Spread.NET", "Upgrade", 899d,1}, { "SpreadJS", "NewLicense", 1499d,5}, { "SpreadJS", "DeploymentLicense", 1499d,5}, { "SpreadJS", "NewLicense", 1499d,4}, { "Spread.NET", "NewLicense", 1499d,6}, { "Spread.NET", "Upgrade", 899d,3}, { "Spread.NET", "NewLicense", 1499d,2}, { "SpreadJS", "DeploymentLicense", 1499d,5}, { "Spread.NET", "NewLicense", 1499d,2}, { "SpreadJS", "Upgrade", 899d,8} }; worksheet1.Range["H9"].Value = "Filter By Two Criteria"; worksheet1.Range["H14"].Value = "Use the '*' operator as shown above to combine two or more filter conditions."; worksheet1.Range["H32"].Value = "Note: these are NOT tables, as spilled array formulas are NOT supported in tables -- instead these ranges are formatted to appear like tables."; worksheet1.Range["O32"].Value = "Result is #CALC! error when no results returned and nothing specified for if_empty."; worksheet1.Range["B10:F10"].Value = tableHeader; ITable table1 = worksheet1.Tables.Add(worksheet1.Range["B10:F22"], true); worksheet1.Range["F11:F22"].Formula2 = "=[@Amount]*[@Units]"; table1.Name = "FilterData"; table1.ShowTotals = true; ITable table2 = worksheet1.Tables.Add(worksheet1.Range["H11:L13"], true); table2.ShowTotals = true; table2.ConvertToRange(); ITable table3 = worksheet1.Tables.Add(worksheet1.Range["B26:F33"], true); table3.ShowTotals = true; table3.ConvertToRange(); ITable table4 = worksheet1.Tables.Add(worksheet1.Range["H26:L31"], true); table4.ShowTotals = true; table4.ConvertToRange(); ITable table5 = worksheet1.Tables.Add(worksheet1.Range["N26:R28"], true); table5.ShowTotals = true; table5.ConvertToRange(); ITable table6 = worksheet1.Tables.Add(worksheet1.Range["N32:R33"], true); table6.ConvertToRange(); ITable table7 = worksheet1.Tables.Add(worksheet1.Range["B37:F42"], true); table7.ShowTotals = true; table7.ConvertToRange(); ITable table8 = worksheet1.Tables.Add(worksheet1.Range["H37:L39"], true); table8.ShowTotals = true; table8.ConvertToRange(); ITable table9 = worksheet1.Tables.Add(worksheet1.Range["N37:R38"], true); table9.ShowTotals = true; table9.ConvertToRange(); ITable table10 = worksheet1.Tables.Add(worksheet1.Range["T26:X27"], true); table10.ConvertToRange(); ITable table11 = worksheet1.Tables.Add(worksheet1.Range["T37:X39"], true); table11.ShowTotals = true; table11.ConvertToRange(); worksheet1.Range["B10:F10,H11:L11,B26:F26,H26:L26,N26:R26,N32:R32,,B37:F37,H37:L37,N37:R37,T26:X26,T37:X37"].Value = tableHeader; worksheet1.Range["H10,B25,H25,N25,T25,N31,B36,H36,N36,T36"].Value = "Formula:"; // Set Styles. worksheet1.Range["A1"].Font.Size = 18; worksheet1.Range["A1"].Font.Bold = true; worksheet1.Range["B4:C4,I10:P10,C25:F25,I25:L25,O25:R25,U25:Y25,O31:R31,C36:F36,I36:L36,O36:R36,U36:X36"].Interior.Color = Color.FromArgb(180, 198, 231); worksheet1.Range["B4:C4,I10:P10,C25:F25,I25:L25,O25:R25,U25:Y25,O31:R31,C36:F36,I36:L36,O36:R36,U36:X36"].Borders.LineStyle = BorderLineStyle.Thin; worksheet1.Range["B4:C4,I10:P10,C25:F25,I25:L25,O25:R25,U25:Y25,O31:R31,C36:F36,I36:L36,O36:R36,U36:X36"].Borders.Color = Color.Gray; worksheet1.Range["B5:C5,B6:I6,B7:I7,H15:L15,H10,B25,H25,N25,T25,N31,O33:U33,H34:R34,B36,H36,N36,T36"].Interior.Color = Color.FromArgb(231, 230, 230); worksheet1.Range["B5:C5,B6:I6,B7:I7,H15:L15,H10,B25,H25,N25,T25,N31,O33:U33,H34:R34,B36,H36,N36,T36"].Borders.LineStyle = BorderLineStyle.Thin; worksheet1.Range["B5:C5,B6:I6,B7:I7,H15:L15,H10,B25,H25,N25,T25,N31,O33:U33,H34:R34,B36,H36,N36,T36"].Borders.Color = Color.Gray; worksheet1.Range["B9,H9"].Font.Name = "Calibri Light"; worksheet1.Range["B9,H9"].Font.Size = 18; worksheet1.Range["B9,H9"].Font.Color = Color.FromArgb(68, 84, 106); worksheet1.Range["B3"].Font.Name = "Calibri Light"; worksheet1.Range["B3"].Font.Size = 11; worksheet1.Range["B3"].Font.Bold = true; worksheet1.Range["B3"].Font.Color = Color.FromArgb(68, 84, 106); worksheet1.Range["B:B,H:H,N:N,T:T"].ColumnWidthInPixel = 113d; worksheet1.Range["C:C,I:I,O:O,U:U"].ColumnWidthInPixel = 124d; worksheet1.Range["D:D,F:F,J:J,L:L,P:P,R:R,V:V,X:X"].NumberFormat = "$#,##0"; // Set formulas worksheet1.Range["H12"].Formula2 = "=FILTER(FilterData,(FilterData[Product Line]=\"SpreadJS\")*(FilterData[Product]=\"DeploymentLicense\"))"; worksheet1.Range["I10"].Formula = "=FORMULATEXT(H12)"; worksheet1.Range["L14"].Formula = "=SUM(L12:L13)"; worksheet1.Range["B27"].Formula2 = "=FILTER(FilterData,FilterData[Product Line]=\"Spread.NET\")"; worksheet1.Range["C25"].Formula = "=FORMULATEXT(B27)"; worksheet1.Range["F34"].Formula = "=SUM(F27:F33)"; worksheet1.Range["H27"].Formula2 = "=FILTER(B27#,B27# C:C=\"NewLicense\")"; worksheet1.Range["I25"].Formula = "=FORMULATEXT(H27)"; worksheet1.Range["L32"].Formula = "=SUM(L27:L31)"; worksheet1.Range["N27"].Formula2 = "=FILTER(B27#,B27# C:C=\"Upgrade\")"; worksheet1.Range["O25"].Formula = "=FORMULATEXT(N27)"; worksheet1.Range["R29"].Formula = "=SUM(R27:R28)"; worksheet1.Range["N33"].Formula2 = "=FILTER(B27#,B27# C:C=\"DeploymentLicense\")"; worksheet1.Range["O31"].Formula = "=FORMULATEXT(N33)"; worksheet1.Range["T27"].Formula2 = "=FILTER(B27#,B27# C:C=\"DeploymentLicense\",\"**NONE FOUND**\")"; worksheet1.Range["U25"].Formula = "=FORMULATEXT(T27)"; worksheet1.Range["B38"].Formula2 = "=FILTER(FilterData,FilterData[Product Line]=\"SpreadJS\")"; worksheet1.Range["C36"].Formula = "=FORMULATEXT(B38)"; worksheet1.Range["F43"].Formula = "=SUM(F38:F42)"; worksheet1.Range["H38"].Formula2 = "=FILTER(B38#,C38:C42=\"NewLicense\")"; worksheet1.Range["I36"].Formula = "=FORMULATEXT(H38)"; worksheet1.Range["L40"].Formula = "=SUM(L38:L39)"; worksheet1.Range["N38"].Formula2 = "=FILTER(B38#,C38:C42=\"Upgrade\")"; worksheet1.Range["O36"].Formula = "=FORMULATEXT(N38)"; worksheet1.Range["R39"].Formula = "=SUM(R38)"; worksheet1.Range["T38"].Formula2 = "=FILTER(B38#,C38:C42=\"DeploymentLicense\")"; worksheet1.Range["U36"].Formula = "=FORMULATEXT(T38)"; worksheet1.Range["X40"].Formula = "=SUM(X38:X39)"; #endregion #region worksheet2 RANDARRAY IWorksheet worksheet2 = workbook.Worksheets.Add(); // Set vlaue. worksheet2.Name = "RANDARRAY"; worksheet2.Range["A1"].Value = "RANDARRAY Function Example"; worksheet2.Range["B3:B11"].Value = new object[,] { { "RANDARRAY Syntax:"}, { "RANDARRAY([rows],[columns],[min],[max],[integer])"}, { "rows: Number of rows of random numbers to generate (default is 1)."}, { "columns: Number of columns of random numbers to generate (default is 1)."}, { "min: Minimum of values to generate (default is 0)."}, { "max: Maximum of values to generate (default is 1)."}, { "integer: True to return integer values (default is False)."}, { "The RANDARRAY function is a voltile function."}, { "This means that each time the worksheet calculates, the RANDARRAY function recalculates new values."} }; worksheet2.Range["A13:A17"].Value = new object[,] { { "Rows:"}, { "Columns:"}, { "Min:"}, { "Max:"}, { "Integer:"} }; worksheet2.Range["D13:D17"].Value = new object[,] { { "1. When nothing is specified for Rows, then the default value 1 is used."}, { "2. When nothing is specified for Columns, then the default value 1 is used."}, { "3. When nothing is specified for Min, then the default value 0 is used."}, { "4. When nothing is specified for Max, then the default value 1 is used."}, { "5. When nothing is specified for Integer, then the default value False is used and decimal values are returned."} }; worksheet2.Range["A19"].Value = "Formula:"; // Set style. worksheet2.Range["A1"].Font.Size = 18; worksheet2.Range["A1"].Font.Bold = true; worksheet2.Range["1:1"].RowHeight = 23.4d; worksheet2.Range["A:A"].ColumnWidthInPixel = 75d; worksheet2.Range["B4:F4,A19"].Interior.Color = Color.FromArgb(180, 198, 231); worksheet2.Range["B4:F4,A19"].Borders.LineStyle = BorderLineStyle.Thin; worksheet2.Range["B4:F4,A19"].Borders.Color = Color.Gray; worksheet2.Range["B19:O19,B5:H5,B6:H6,B7:F7,B8:F8,B9:F9,B10:F10,B11:K11,A20"].Interior.Color = Color.FromArgb(231, 230, 230); worksheet2.Range["B19:O19,B5:H5,B6:H6,B7:F7,B8:F8,B9:F9,B10:F10,B11:K11,A20"].Borders.LineStyle = BorderLineStyle.Thin; worksheet2.Range["B19:O19,B5:H5,B6:H6,B7:F7,B8:F8,B9:F9,B10:F10,B11:K11,A20"].Borders.Color = Color.Gray; worksheet2.Range["B3"].Font.Name = "Calibri Light"; worksheet2.Range["B3"].Font.Size = 11; worksheet2.Range["B3"].Font.Bold = true; worksheet2.Range["B3"].Font.Color = Color.FromArgb(68, 84, 106); worksheet2.Range["B13:B17"].Interior.Color = Color.FromArgb(189, 215, 238); worksheet2.Range["B13:B17"].Borders.LineStyle = BorderLineStyle.Thin; worksheet2.Range["B13:B17"].Borders.Color = Color.Gray; // Set array formula. worksheet2.Range["B19"].Formula = "=FORMULATEXT(A20)"; worksheet2.Range["A20"].Formula2 = "=RANDARRAY(IF(ISBLANK(B13),1,B13),IF(ISBLANK(B14),1,B14),IF(ISBLANK(B15),0,B15),IF(ISBLANK(B16),1,B16),IF(ISBLANK(B17),FALSE,B17))"; #endregion #region worksheet3 SEQUENCE IWorksheet worksheet3 = workbook.Worksheets.Add(); // Set data. worksheet3.Name = "SEQUENCE"; worksheet3.Range["A1"].Value = "SEQUENCE Function Example"; worksheet3.Range["B3:B8"].Value = new object[,] { { "SEQUENCE Syntax"}, { "SEQUENCE(rows,[columns],[start],[step])"}, { "rows: Number of rows to generate in the sequence (this value is required; if not provided the function returns #CALC! error)."}, { "columns: Optional number of columns to generate in the sequence (default is 1)."}, { "start: Optional starting value (default is 1)."}, { "step: Optional increment value (default is 1)."} }; worksheet3.Range["A10:A13"].Value = new object[,] { { "Rows:"}, { "Columns:"}, { "Start:"}, { "Step:"} }; worksheet3.Range["B10"].Value = 1d; worksheet3.Range["D10:D13"].Value = new object[,] { { "1. When nothing is specified for the first argument (Rows), then the function returns #CALC! error."}, { "2. When the optional Columns argument is specified, then the function returns an array with the specified number of columns."}, { "3. When the optional Start argument is specified, then SEQUENCE returns values starting with that specified value."}, { "4. When the optional Step argument is specified, then SEQUENCE returns values incremented with that specified value."} }; worksheet3.Range["A15:A19"].Value = new object[,] { { "Typically, SEQUENCE is used with hard-coded arguments to generate a specific sequence of values for the dynamic array formula."}, { "This example uses cell references for the arguments of SEQUENCE for illustrative purposes, so you can explore how the function works."}, { "The formula uses the values above if they are specified, and uses the default values for the arguments when the cells are empty."}, { "This shows how the function operates when particular arguments are included or excluded when hard-coding the arguments to be missing or skipped in the formula."}, { "For example, entering a value for Rows and Step while leaving cells B11 and B12 empty shows the result of SEQUENCE(B3,,,B6)."} }; worksheet3.Range["A21"].Value = "Formula:"; // Set styles. worksheet3.Range["A1"].Font.Size = 18; worksheet3.Range["A1"].Font.Bold = true; worksheet3.Range["1:1"].RowHeight = 23.4d; worksheet3.Range["A:A"].ColumnWidthInPixel = 75d; worksheet3.Range["B4:E4,A15:M15,A16:M16,A17:M17,A18:P18,A19:L19"].Interior.Color = Color.FromArgb(180, 198, 231); worksheet3.Range["B4:E4,A15:M15,A16:M16,A17:M17,A18:P18,A19:L19"].Borders.LineStyle = BorderLineStyle.Thin; worksheet3.Range["B4:E4,A15:M15,A16:M16,A17:M17,A18:P18,A19:L19"].Borders.Color = Color.Gray; worksheet3.Range["B5:M5,B6:I6,B7:E7,B8:E8,B21:J21,A22"].Interior.Color = Color.FromArgb(231, 230, 230); worksheet3.Range["B5:M5,B6:I6,B7:E7,B8:E8,B21:J21,A22"].Borders.LineStyle = BorderLineStyle.Thin; worksheet3.Range["B5:M5,B6:I6,B7:E7,B8:E8,B21:J21,A22"].Borders.Color = Color.Gray; worksheet3.Range["B10:B13"].Interior.Color = Color.FromArgb(189, 215, 238); worksheet3.Range["B10:B13"].Borders.LineStyle = BorderLineStyle.Thin; worksheet3.Range["B10:B13"].Borders.Color = Color.Gray; worksheet3.Range["B3"].Font.Name = "Calibri Light"; worksheet3.Range["B3"].Font.Size = 11; worksheet3.Range["B3"].Font.Bold = true; worksheet3.Range["B3"].Font.Color = Color.FromArgb(68, 84, 106); worksheet3.Range["1:1"].RowHeight = 23.4d; worksheet3.Range["A:A"].ColumnWidthInPixel = 75d; worksheet3.Range["E:E"].ColumnWidthInPixel = 76d; worksheet3.Range["T:T"].ColumnWidthInPixel = 485d; // Set Formula. worksheet3.Range["B21"].Formula = "=FORMULATEXT(A22)"; worksheet3.Range["A22"].Formula2 = "=SEQUENCE(B10,IF(ISBLANK(B11),1,B11),IF(ISBLANK(B12),1,B12),IF(ISBLANK(B13),1,B13))"; #endregion #region worksheet4 SINGLE IWorksheet worksheet4 = workbook.Worksheets.Add(); worksheet4.Name = "SINGLE"; worksheet4.Range["A1"].Value = "SINGLE Function Examples"; worksheet4.Range["B3:B6"].Value = new object[,] { { "SINGLE Syntax"}, { "SINGLE(value)"}, { "@value"}, { "value: Array (in this case, SINGLE returns the first element in the array) or range reference to intersect with the formula cell."} }; worksheet4.Range["C5"].Value = "When using the SINGLE function in a formula, it is automatically replaced with this syntax using the new '@' operator."; ; worksheet4.Range["A8"].Value = "Horizontal Array:"; worksheet4.Range["A10"].Value = "Formula:"; worksheet4.Range["E10:E12"].Value = new object[,] { { "This example references the horizontal array value in cell C8."}, { "This example references the same array value hard-coded."}, { "Note that in the case of a hard-coded array value, the SINGLE function always returns the first value."} }; worksheet4.Range["A13"].Value = "Vertical Array:"; worksheet4.Range["C14,E14,F21,A22,B26"].Value = "Formula:"; worksheet4.Range["A20"].Value = "Range Intersection:"; worksheet4.Range["B21"].Value = "2-Dimensional Array"; worksheet4.Range["G27:G29"].Value = new object[,] { { "The formulas above and to the left use SINGLE to perform explicit range intersection."}, { "The value returned is determined by intersecting the specified range with the formula cell."}, { "The value in the same column (see above) or row (see left) as the formula cell is returned."} }; // Set styles. worksheet4.Range["A1"].Font.Size = 18; worksheet4.Range["A1"].Font.Bold = true; worksheet4.Range["C5:M5,B6:L6,A10,E10:J10,E11:J11,E12:M12,C14,E14,F21,B26,G27:O29"].Interior.Color = Color.FromArgb(180, 198, 231); worksheet4.Range["C5:M5,B6:L6,A10,E10:J10,E11:J11,E12:M12,C14,E14,F21,B26,G27:O29"].Borders.LineStyle = BorderLineStyle.Thin; worksheet4.Range["C5:M5,B6:L6,A10,E10:J10,E11:J11,E12:M12,C14,E14,F21,B26,G27:O29"].Borders.Color = Color.Gray; worksheet4.Range["B4:B5,B10:B11,D10:D11,D14:D15,F14:G14,F15,B22,C22:E24,G21:I24,B27:E29"].Interior.Color = Color.FromArgb(231, 230, 230); worksheet4.Range["B4:B5,B10:B11,D10:D11,D14:D15,F14:G14,F15,B22,C22:E24,G21:I24,B27:E29"].Borders.LineStyle = BorderLineStyle.Thin; worksheet4.Range["B4:B5,B10:B11,D10:D11,D14:D15,F14:G14,F15,B22,C22:E24,G21:I24,B27:E29"].Borders.Color = Color.Gray; worksheet4.Range["B3"].Font.Name = "Calibri Light"; worksheet4.Range["B3"].Font.Size = 11; worksheet4.Range["B3"].Font.Bold = true; worksheet4.Range["B3"].Font.Color = Color.FromArgb(68, 84, 106); worksheet4.Range["1:1"].RowHeight = 23.4d; worksheet4.Range["B:B"].ColumnWidthInPixel = 135d; // Set formula. worksheet4.Range["C8"].Formula2 = "={1,1,2,2,3}"; worksheet4.Range["B10"].Formula = "=FORMULATEXT(D10)"; worksheet4.Range["B11"].Formula = "=FORMULATEXT(D11)"; worksheet4.Range["D10"].Formula2 = "=@C8#"; worksheet4.Range["D11"].Formula2 = "=@{1,1,2,2,3}"; worksheet4.Range["B14"].Formula2 = "={1;1;2;2;3}"; worksheet4.Range["D14"].Formula = "=FORMULATEXT(D15)"; worksheet4.Range["D15"].Formula2 = "=@B14#"; worksheet4.Range["F14"].Formula = "=FORMULATEXT(F15)"; worksheet4.Range["F15"].Formula2 = "=@{1;1;2;2;3}"; worksheet4.Range["G21"].Formula = "=FORMULATEXT(G22)"; worksheet4.Range["H21"].Formula = "=FORMULATEXT(H22)"; worksheet4.Range["I21"].Formula = "=FORMULATEXT(I22)"; worksheet4.Range["G22:G24"].Formula2 = "=@C:C"; worksheet4.Range["H22:H24"].Formula2 = "=@D:D"; worksheet4.Range["I22:I24"].Formula2 = "=@E:E"; worksheet4.Range["B22"].Formula = "=FORMULATEXT(C22)"; worksheet4.Range["C22"].Formula2 = "={1,2,3;4,5,6;7,8,9}"; worksheet4.Range["B27"].Formula = "=FORMULATEXT(C27)"; worksheet4.Range["C27:E27"].Formula2 = "=@22:22"; worksheet4.Range["B28"].Formula = "=FORMULATEXT(C28)"; worksheet4.Range["C28:E28"].Formula2 = "=@23:23"; worksheet4.Range["B29"].Formula = "=FORMULATEXT(C29)"; worksheet4.Range["C29:E29"].Formula2 = "=@24:24"; #endregion #region worksheet5 SORT IWorksheet worksheet5 = workbook.Worksheets.Add(); // Set values. worksheet5.Name = "SORT"; worksheet5.Range["A1"].Value = "SORT Function Examples"; worksheet5.Range["B3:B8"].Value = new object[,] { { "SORT Syntax"}, { "SORT(array,[sort_index],[sort_order],[by_col])"}, { "array: Array or range to sort."}, { "sort_index: Optional index of the row or column to sort by (default is 1)."}, { "sort_order: Optional -1 to sort descending (default is 1, ascending)."}, { "by_col: Optional True to sort by column (default is False, sort by row)."} }; worksheet5.Range["G4"].Value = "Note: sort_index and sort_order can be array arguments (which must be the same length) that specify multiple sort keys and corresponding sort key orders."; worksheet5.Range["I9"].Value = "Sort by ProductName"; worksheet5.Range["B10"].Value = "SortData"; var tableHeader_5 = new object[,] { { "Date","ProductName" ,"Quantity" ,"Price" ,"Total" ,"SalesPerson" } }; worksheet5.Range["B11:G11"].Value = tableHeader_5; worksheet5.Range["I11:N11"].Value = tableHeader_5; worksheet5.Range["B26:G26"].Value = tableHeader_5; worksheet5.Range["I26:N26"].Value = tableHeader_5; worksheet5.Range["B43:G43"].Value = tableHeader_5; worksheet5.Range["I43:N43"].Value = tableHeader_5; worksheet5.Range["B12:G22"].Value = new object[,] { { DateTime.Parse("2019/5/1 0:00:00"),"Spread.NET", 1d, 1499d,null,"Jim"}, { DateTime.Parse("2019/5/1 0:00:00"),"Spread.NET", 2d, 1499d,null,"Fred"}, { DateTime.Parse("2019/5/2 0:00:00"),"SpreadJS", 4d, 1499d,null,"Fred"}, { DateTime.Parse("2019/5/3 0:00:00"),"Spread.NET", 2d, 1499d,null,"Fred"}, { DateTime.Parse("2019/5/3 0:00:00"),"SpreadJS", 3d, 1499d,null,"Barry"}, { DateTime.Parse("2019/5/3 0:00:00"),"SpreadJS", 1d, 1499d,null,"Linda"}, { DateTime.Parse("2019/5/3 0:00:00"),"Spread.NET", 4d, 1499d,null,"Barry"}, { DateTime.Parse("2019/5/3 0:00:00"),"Spread.NET", 2d, 1499d,null,"Linda"}, { DateTime.Parse("2019/5/4 0:00:00"),"SpreadJS", 4d, 1499d,null,"Jim"}, { DateTime.Parse("2019/5/5 0:00:00"),"Spread.NET", 6d, 1499d,null,"Jim"}, { DateTime.Parse("2019/5/5 0:00:00"),"SpreadJS", 4d, 1499d,null,"Barry"} }; worksheet5.Range["B39"].Value = "Note: these are NOT tables, as spilled array formulas are NOT supported in tables -- instead these ranges are formatted to appear like tables."; worksheet5.Range["I10,B25,I25,B42,I42"].Value = "Formula:"; worksheet5.Range["B24"].Value = "Sort by Quantity Descending"; worksheet5.Range["I24"].Value = "Sort by SalesPerson"; worksheet5.Range["B41"].Value = "Sort by Quantity Descending then SalesPerson Ascending"; worksheet5.Range["I41"].Value = "Sort by SalesPerson Ascending then Quantity Descending"; ITable table = worksheet5.Tables.Add(worksheet5.Range["B11:G22"], true); table.Name = "SortData"; ITable table1_5 = worksheet5.Tables.Add(worksheet5.Range["I11:N22"], true); table1_5.ConvertToRange(); ITable table2_5 = worksheet5.Tables.Add(worksheet5.Range["B26:G37"], true); table2_5.ConvertToRange(); ITable table3_5 = worksheet5.Tables.Add(worksheet5.Range["I26:N37"], true); table3_5.ConvertToRange(); ITable table4_5 = worksheet5.Tables.Add(worksheet5.Range["B43:G54"], true); table4_5.ConvertToRange(); ITable table5_5 = worksheet5.Tables.Add(worksheet5.Range["I43:N54"], true); table5_5.ConvertToRange(); // Set Styles. worksheet5.Range["A1"].Font.Size = 18; worksheet5.Range["A1"].Font.Bold = true; worksheet5.Range["G4:M5"].Merge(); worksheet5.Range["G4"].WrapText = true; worksheet5.Range["B4:C4,I10,B25,I25,B42,I42,B39:J39"].Interior.Color = Color.FromArgb(180, 198, 231); worksheet5.Range["B4:C4,I10,B25,I25,B42,I42,B39:J39"].Borders.LineStyle = BorderLineStyle.Thin; worksheet5.Range["B4:C4,I10,B25,I25,B42,I42,B39:J39"].Borders.Color = Color.Gray; worksheet5.Range["B5:C5,B6:E8,G4:M4,J10,C25,J25,C42:D42,J42:K42"].Interior.Color = Color.FromArgb(231, 230, 230); worksheet5.Range["B5:C5,B6:E8,G4:M4,J10,C25,J25,C42:D42,J42:K42"].Borders.LineStyle = BorderLineStyle.Thin; worksheet5.Range["B5:C5,B6:E8,G4:M4,J10,C25,J25,C42:D42,J42:K42"].Borders.Color = Color.Gray; worksheet5.Range["B10,I9,B24,I24,B41,I41"].Font.Name = "Calibri Light"; worksheet5.Range["B10,I9,B24,I24,B41,I41"].Font.Size = 18; worksheet5.Range["B10,I9,B24,I24,B41,I41"].Font.Color = Color.FromArgb(68, 84, 106); worksheet5.Range["B3"].Font.Name = "Calibri Light"; worksheet5.Range["B3"].Font.Size = 11; worksheet5.Range["B3"].Font.Bold = true; worksheet5.Range["B3"].Font.Color = Color.FromArgb(68, 84, 106); worksheet5.Range["1:1,9:10,24:24,41:41"].RowHeight = 23.4d; worksheet5.Range["B:B,I:I"].ColumnWidthInPixel = 113d; worksheet5.Range["C:C,J:J"].ColumnWidthInPixel = 155d; worksheet5.Range["D:D,K:K"].ColumnWidthInPixel = 92d; worksheet5.Range["E:F,L:M"].ColumnWidthInPixel = 84d; worksheet5.Range["G:G,N:N"].ColumnWidthInPixel = 117d; worksheet5.Range["E:F,L:M"].NumberFormat = "$#,##0"; // Set Formulas. worksheet5.Range["J10"].Formula = "=FORMULATEXT(I12)"; worksheet5.Range["F12"].Formula = "=D12*E12"; worksheet5.Range["F13"].Formula = "=D13*E13"; worksheet5.Range["F14"].Formula = "=D14*E14"; worksheet5.Range["F15"].Formula = "=D15*E15"; worksheet5.Range["F16"].Formula = "=D16*E16"; worksheet5.Range["F17"].Formula = "=D17*E17"; worksheet5.Range["F18"].Formula = "=D18*E18"; worksheet5.Range["F19"].Formula = "=D19*E19"; worksheet5.Range["F20"].Formula = "=D20*E20"; worksheet5.Range["F21"].Formula = "=D21*E21"; worksheet5.Range["F22"].Formula = "=D22*E22"; worksheet5.Range["I12"].Formula2 = "=SORT(SortData,2)"; worksheet5.Range["C25"].Formula = "=FORMULATEXT(B27)"; worksheet5.Range["J25"].Formula = "=FORMULATEXT(I27)"; worksheet5.Range["B27"].Formula2 = "=SORT(SortData,3,-1)"; worksheet5.Range["I27"].Formula2 = "=SORT(SortData,6)"; worksheet5.Range["C42"].Formula = "=FORMULATEXT(B44)"; worksheet5.Range["J42"].Formula = "=FORMULATEXT(I44)"; worksheet5.Range["B44"].Formula2 = "=SORT(SortData,{3,6},{-1,1})"; worksheet5.Range["I44"].Formula2 = "=SORT(SortData,{6,3},{1,-1})"; #endregion #region worksheet6 SORTBY IWorksheet worksheet6 = workbook.Worksheets.Add(); // Set values. worksheet6.Name = "SORTBY"; worksheet6.Range["A1"].Value = "SORTBY Function Examples"; worksheet6.Range["B3:B10"].Value = new object[,] { { "SORTBY Syntax"}, { "SORTBY(array,by_array1,[order_array1],[by_array2, order_array2],…)"}, { "array: Array or range to be sorted (required)."}, { "by_array1: array or range of first sort key (required)."}, { "order_array1: Optional -1 for descending order (default is 1 asending)."}, { "by_array2: Optional array or range of second sort key."}, { "order_array2: -1 for decsending, 1 for ascending (required if by_array2 specified)."}, { "This function can accept additional arguments in pairs, specifying the next sort key range and order."} }; var tableHeader_6 = new object[,] { { "Date","ProductName" ,"Quantity" ,"Price" ,"Total" ,"SalesPerson" } }; worksheet6.Range["B13:G13"].Value = tableHeader_6; worksheet6.Range["J13:O13"].Value = tableHeader_6; worksheet6.Range["B28:G28"].Value = tableHeader_6; worksheet6.Range["J28:J28"].Value = tableHeader_6; worksheet6.Range["B14:G24"].Value = new object[,] { { DateTime.Parse("2019/5/1 0:00:00"),"Spread.NET", 1d, 1499d,null,"Jim"}, { DateTime.Parse("2019/5/1 0:00:00"),"Spread.NET", 2d, 1499d,null,"Fred"}, { DateTime.Parse("2019/5/2 0:00:00"),"SpreadJS", 4d, 1499d,null,"Fred"}, { DateTime.Parse("2019/5/3 0:00:00"),"Spread.NET", 2d, 1499d,null,"Fred"}, { DateTime.Parse("2019/5/3 0:00:00"),"SpreadJS", 3d, 1499d,null,"Barry"}, { DateTime.Parse("2019/5/3 0:00:00"),"SpreadJS", 1d, 1499d,null,"Linda"}, { DateTime.Parse("2019/5/3 0:00:00"),"Spread.NET", 4d, 1499d,null,"Barry"}, { DateTime.Parse("2019/5/3 0:00:00"),"Spread.NET", 2d, 1499d,null,"Linda"}, { DateTime.Parse("2019/5/4 0:00:00"),"SpreadJS", 4d, 1499d,null,"Jim"}, { DateTime.Parse("2019/5/5 0:00:00"),"Spread.NET", 6d, 1499d,null,"Jim"}, { DateTime.Parse("2019/5/5 0:00:00"),"SpreadJS", 4d, 1499d,null,"Barry"} }; worksheet6.Range["B12,J12,B27,J27"].Value = "Formula:"; worksheet6.Range["J11"].Value = "Sort by ProductName then Quantity Descending"; worksheet6.Range["B26"].Value = "Sort By Quantity Descending, then Product Name"; worksheet6.Range["J26"].Value = "Sort by SalesPerson then Quantity Descending"; worksheet6.Range["B41"].Value = "Note: these are NOT tables, as spilled array formulas are NOT supported in tables -- instead these ranges are formatted to appear like tables."; ITable table_6 = worksheet6.Tables.Add(worksheet6.Range["B13:G24"], true); table_6.Name = "SortByData"; ITable table1_6 = worksheet6.Tables.Add(worksheet6.Range["J13:O24"], true); table1_6.ConvertToRange(); ITable table2_6 = worksheet6.Tables.Add(worksheet6.Range["B28:G39"], true); table2_6.ConvertToRange(); ITable table3_6 = worksheet6.Tables.Add(worksheet6.Range["J28:O39"], true); table3_6.ConvertToRange(); // Set Styles. worksheet6.Range["A1"].Font.Size = 18; worksheet6.Range["A1"].Font.Bold = true; worksheet6.Range["B4:F4,J12,B27,J27,B41:K41"].Interior.Color = Color.FromArgb(180, 198, 231); worksheet6.Range["B4:F4,J12,B27,J27,B41:K41"].Borders.LineStyle = BorderLineStyle.Thin; worksheet6.Range["B4:F4,J12,B27,J27,B41:K41"].Borders.Color = Color.Gray; worksheet6.Range["B5:D5,B6:E6,B7:F7,B8:E8,B9:F9,B10:H10,K12:O12,C27:G27,K27:O27"].Interior.Color = Color.FromArgb(231, 230, 230); worksheet6.Range["B5:D5,B6:E6,B7:F7,B8:E8,B9:F9,B10:H10,K12:O12,C27:G27,K27:O27"].Borders.LineStyle = BorderLineStyle.Thin; worksheet6.Range["B5:D5,B6:E6,B7:F7,B8:E8,B9:F9,B10:H10,K12:O12,C27:G27,K27:O27"].Borders.Color = Color.Gray; worksheet6.Range["B12,J11,B26,J26"].Font.Name = "Calibri Light"; worksheet6.Range["B12,J11,B26,J26"].Font.Size = 18; worksheet6.Range["B12,J11,B26,J26"].Font.Color = Color.FromArgb(68, 84, 106); worksheet6.Range["B3"].Font.Name = "Calibri Light"; worksheet6.Range["B3"].Font.Size = 11; worksheet6.Range["B3"].Font.Bold = true; worksheet6.Range["B3"].Font.Color = Color.FromArgb(68, 84, 106); worksheet6.Range["1:1,11:12,26:26"].RowHeight = 23.4d; worksheet6.Range["B:B,J:J"].ColumnWidthInPixel = 113d; worksheet6.Range["C:C,K:K"].ColumnWidthInPixel = 124d; worksheet6.Range["D:D,L:L"].ColumnWidthInPixel = 88d; worksheet6.Range["E:F,M:N"].ColumnWidthInPixel = 101d; worksheet6.Range["G:G,O:O"].ColumnWidthInPixel = 116d; worksheet6.Range["E:F,M:N"].NumberFormat = "$#,##0"; // Set formulas. worksheet6.Range["K12"].Formula = "=FORMULATEXT(J14)"; worksheet6.Range["F14"].Formula = "=D14*E14"; worksheet6.Range["F15"].Formula = "=D15*E15"; worksheet6.Range["F16"].Formula = "=D16*E16"; worksheet6.Range["F17"].Formula = "=D17*E17"; worksheet6.Range["F18"].Formula = "=D18*E18"; worksheet6.Range["F19"].Formula = "=D19*E19"; worksheet6.Range["F20"].Formula = "=D20*E20"; worksheet6.Range["F21"].Formula = "=D21*E21"; worksheet6.Range["F22"].Formula = "=D22*E22"; worksheet6.Range["F23"].Formula = "=D23*E23"; worksheet6.Range["F24"].Formula = "=D24*E24"; worksheet6.Range["J14"].Formula2 = "=SORTBY(SortByData,SortByData[ProductName],1,SortByData[Quantity],-1)"; worksheet6.Range["C27"].Formula = "=FORMULATEXT(B29)"; worksheet6.Range["K27"].Formula = "=FORMULATEXT(J29)"; worksheet6.Range["B29"].Formula2 = "=SORTBY(SortByData,SortByData[Quantity],-1,SortByData[ProductName],1)"; worksheet6.Range["J29"].Formula2 = "=SORTBY(SortByData,SortByData[SalesPerson],1,SortByData[Quantity],-1)"; #endregion #region Worksheet7 Unique IWorksheet worksheet7 = workbook.Worksheets.Add(); worksheet7.Name = "UNIQUE"; worksheet7.Range["A1"].Value = "UNIQUE Function Example"; worksheet7.Range["B3:B7"].Value = new object[,] { { "UNIQUE Syntax"}, { "UNIQUE(array,[by_col],[unique_only])"}, { "array: Array or cell range containing values to be compared."}, { "by_col: Whether to compare values by column or by row (default is False, by row)."}, { "unique_only: Whether to return only the unique values (that is, return only the values which actually occur exactly once in the comparison set); the default is False."} }; worksheet7.Range["A9"].Value = "Horizontal Array:"; worksheet7.Range["A11:A16"].Value = new object[,] { { "If you specify a horizontal range of values, and don't specify TRUE for the 2nd argument, then UNIQUE won't generate the unique values."}, { "Formula:"}, { "You must specify TRUE for the 2nd argument to compare column values in the rows instead of row values in the columns:"}, { "Formula:"}, { "If you also specify TRUE for the 3rd argument, then ONLY the unique values will be returned:"}, { "Formula:"} }; worksheet7.Range["J12"].Value = "This doesn't work with only one row of values."; worksheet7.Range["A18"].Value = "If there are no unique items to return, then UNIQUE returns either #CALC! or #SPILL! Error."; worksheet7.Range["C20"].Value = "If you specify a vertical range of values, then you should skip the 2nd argument or use False to compare row values."; worksheet7.Range["A21"].Value = "Vertical Array:"; worksheet7.Range["C21,F21,I21,N21,Q21,A37,I37,A43,I43,A49,I49,A56,I56,A62"].Value = "Formula:"; worksheet7.Range["N20"].Value = "These combinations don't work with only one column of values:"; worksheet7.Range["A28"].Value = "Cell Range Array:"; worksheet7.Range["A35"].Value = "If you specify a cell range, then you must specify True for the 2nd argument if you want the column values unique in each row, or False if you want the row values unique in each column."; worksheet7.Range["C36"].Value = "(three unique rows returned since 1st/2nd and 3rd/4th same)"; worksheet7.Range["A36"].Value = "Case 1"; worksheet7.Range["I36"].Value = "Case 2"; worksheet7.Range["M37"].Value = "(same as case 1 to left)"; worksheet7.Range["A42"].Value = "Case 3"; worksheet7.Range["D43"].Value = "(same case 1 as above)"; worksheet7.Range["I42"].Value = "Case 4"; worksheet7.Range["M43"].Value = "(one unique row returned, 5th, since 1st/2nd and 3rd/4th rows not unique)"; worksheet7.Range["A48"].Value = "Case 5"; worksheet7.Range["D49"].Value = "(four unique columns returned since 2nd/4th same)"; worksheet7.Range["I48"].Value = "Case 6"; worksheet7.Range["M49"].Value = "(same as case 5 to left)"; worksheet7.Range["A55"].Value = "Case 7"; worksheet7.Range["D56"].Value = "(same as case 1/2/3)"; worksheet7.Range["I55"].Value = "Case 8"; worksheet7.Range["M56"].Value = "(returns three unique columns, 1st, 3rd, and 5th, since 2nd and 4th are same)"; worksheet7.Range["A61"].Value = "Case 9"; worksheet7.Range["D62"].Value = "(same as case 4)"; // Set styles. worksheet7.Range["A1"].Font.Size = 18; worksheet7.Range["A1"].Font.Bold = true; worksheet7.Range["B4:D4,A12,A13:J13,A14,A15:H15,A16,A18:H18,C20:L20,N20:R20,C21,F21,I21,N21,Q21,A35:O35,C36:H36,A37,I37,M37:O37,A43,D43:F43,I43,M43:S43,A49,D49:H49,I49,M49:N49,A56,D56:E56,I56,M56:R56,A62,D62:E62"].Interior.Color = Color.FromArgb(180, 198, 231); worksheet7.Range["B4:D4,A12,A13:J13,A14,A15:H15,A16,A18:H18,C20:L20,N20:R20,C21,F21,I21,N21,Q21,A35:O35,C36:H36,A37,I37,M37:O37,A43,D43:F43,I43,M43:S43,A49,D49:H49,I49,M49:N49,A56,D56:E56,I56,M56:R56,A62,D62:E62"].Borders.LineStyle = BorderLineStyle.Thin; worksheet7.Range["B4:D4,A12,A13:J13,A14,A15:H15,A16,A18:H18,C20:L20,N20:R20,C21,F21,I21,N21,Q21,A35:O35,C36:H36,A37,I37,M37:O37,A43,D43:F43,I43,M43:S43,A49,D49:H49,I49,M49:N49,A56,D56:E56,I56,M56:R56,A62,D62:E62"].Borders.Color = Color.Gray; worksheet7.Range["B5:F5,B6:H6,B7:N7,B12,D12:H12,B14:F14,B16:D16,D21:E21,G21:H21,J21:K21,O21:P21,R21:T21,E22:E24,H22:H24,K22,P22:P26,S22:S26,B37,J37:L37,B38:F40,J38:N40,B43:C43,B44:F46,J43:L43,J44:N44,B49:C49,J49:L49,B50:E54,J50:M54,B56:C56,J56:L56,B57:F59,J57:L61,B62:C62,B63:F63"].Interior.Color = Color.FromArgb(231, 230, 230); worksheet7.Range["B5:F5,B6:H6,B7:N7,B12,D12:H12,B14:F14,B16:D16,D21:E21,G21:H21,J21:K21,O21:P21,R21:T21,E22:E24,H22:H24,K22,P22:P26,S22:S26,B37,J37:L37,B38:F40,J38:N40,B43:C43,B44:F46,J43:L43,J44:N44,B49:C49,J49:L49,B50:E54,J50:M54,B56:C56,J56:L56,B57:F59,J57:L61,B62:C62,B63:F63"].Borders.LineStyle = BorderLineStyle.Thin; worksheet7.Range["B5:F5,B6:H6,B7:N7,B12,D12:H12,B14:F14,B16:D16,D21:E21,G21:H21,J21:K21,O21:P21,R21:T21,E22:E24,H22:H24,K22,P22:P26,S22:S26,B37,J37:L37,B38:F40,J38:N40,B43:C43,B44:F46,J43:L43,J44:N44,B49:C49,J49:L49,B50:E54,J50:M54,B56:C56,J56:L56,B57:F59,J57:L61,B62:C62,B63:F63"].Borders.Color = Color.Gray; worksheet7.Range["C9:G9,B22:B26,A29:E33"].Interior.Color = Color.FromArgb(189, 215, 238); worksheet7.Range["C9:G9,B22:B26,A29:E33"].Borders.LineStyle = BorderLineStyle.Thin; worksheet7.Range["C9:G9,B22:B26,A29:E33"].Borders.Color = Color.Gray; worksheet7.Range["B3,A9,A21,A28,A36,I36,A42,I42,A48,I48,A55,I55,A61"].Font.Name = "Calibri Light"; worksheet7.Range["B3,A9,A21,A28,A36,I36,A42,I42,A48,I48,A55,I55,A61"].Font.Size = 11; worksheet7.Range["B3,A9,A21,A28,A36,I36,A42,I42,A48,I48,A55,I55,A61"].Font.Bold = true; worksheet7.Range["B3,A9,A21,A28,A36,I36,A42,I42,A48,I48,A55,I55,A61"].Font.Color = Color.FromArgb(68, 84, 106); worksheet7.Range["1:1"].RowHeight = 23.4d; worksheet7.Range["B:B"].ColumnWidthInPixel = 123d; worksheet7.Range["D:D"].ColumnWidthInPixel = 81d; worksheet7.Range["H:H"].ColumnWidthInPixel = 89d; worksheet7.Range["K:K,N:N,Q:Q"].ColumnWidthInPixel = 92d; // Set formulas. worksheet7.Range["C9"].Formula2 = "={1,1,2,2,3}"; worksheet7.Range["B12"].Formula = "=FORMULATEXT(D12)"; worksheet7.Range["D12"].Formula2 = "=UNIQUE(C9#)"; worksheet7.Range["B14"].Formula = "=FORMULATEXT(D14)"; worksheet7.Range["D14"].Formula2 = "=UNIQUE(C9#,TRUE)"; worksheet7.Range["B16"].Formula = "=FORMULATEXT(D16)"; worksheet7.Range["D16"].Formula2 = "=UNIQUE(C9#,TRUE,TRUE)"; worksheet7.Range["D21"].Formula = "=FORMULATEXT(E22)"; worksheet7.Range["E22"].Formula2 = "=UNIQUE(B22#)"; worksheet7.Range["G21"].Formula = "=FORMULATEXT(H22)"; worksheet7.Range["H22"].Formula2 = "=UNIQUE(B22#,FALSE)"; worksheet7.Range["J21"].Formula = "=FORMULATEXT(K22)"; worksheet7.Range["K22"].Formula2 = "=UNIQUE(B22#,,TRUE)"; worksheet7.Range["B22"].Formula2 = "={1;1;2;2;3}"; worksheet7.Range["O21"].Formula = "=FORMULATEXT(P22)"; worksheet7.Range["P22"].Formula2 = "=UNIQUE(B22#,TRUE)"; worksheet7.Range["R21"].Formula = "=FORMULATEXT(S22)"; worksheet7.Range["S22"].Formula2 = "=UNIQUE(B22#,TRUE,TRUE)"; worksheet7.Range["A29"].Formula2 = "={1,1,2,1,3;1,1,2,1,3;1,2,3,2,5;1,2,3,2,5;1,2,2,2,5}"; worksheet7.Range["B37"].Formula = "=FORMULATEXT(B38)"; worksheet7.Range["B38"].Formula2 = "=UNIQUE(A29#)"; worksheet7.Range["J37"].Formula = "=FORMULATEXT(J38)"; worksheet7.Range["J38"].Formula2 = "=UNIQUE(A29#,FALSE,FALSE)"; worksheet7.Range["B43"].Formula = "=FORMULATEXT(B44)"; worksheet7.Range["B44"].Formula2 = "=UNIQUE(A29#,FALSE)"; worksheet7.Range["J43"].Formula = "=FORMULATEXT(J44)"; worksheet7.Range["J44"].Formula2 = "=UNIQUE(A29#,FALSE,TRUE)"; worksheet7.Range["B49"].Formula = "=FORMULATEXT(B50)"; worksheet7.Range["B50"].Formula2 = "=UNIQUE(A29#,TRUE)"; worksheet7.Range["J49"].Formula = "=FORMULATEXT(J50)"; worksheet7.Range["J50"].Formula2 = "=UNIQUE(A29#,TRUE,FALSE)"; worksheet7.Range["B56"].Formula = "=FORMULATEXT(B57)"; worksheet7.Range["B57"].Formula2 = "=UNIQUE(A29#,,FALSE)"; worksheet7.Range["J56"].Formula = "=FORMULATEXT(J57)"; worksheet7.Range["J57"].Formula2 = "=UNIQUE(A29#,TRUE,TRUE)"; worksheet7.Range["B62"].Formula = "=FORMULATEXT(B63)"; worksheet7.Range["B63"].Formula2 = "=UNIQUE(A29#,,TRUE)"; #endregion // Save to an excel file workbook.Save("UseCase2.xlsx");
' Create a new Workbook Dim workbook As New Workbook #Region "worksheet1 FILTER" Dim worksheet1 As IWorksheet = workbook.ActiveSheet ' Set value. worksheet1.Name = "FILTER" worksheet1.Range("A1").Value = "FILTER Function Examples" worksheet1.Range("B3").Value = "Filter Syntax:" worksheet1.Range("B4").Value = "FILTER(array,include,[if_empty])" worksheet1.Range("B5").Value = "array: The array or reference to filter." worksheet1.Range("B6").Value = "include: The filter condtion expressed using an intersecting sub-range and conditional expression -- see examples below." worksheet1.Range("B7").Value = "if_empty: Optional value to return when the filter result is empty; if not specified then empty result shows #CALC! error." worksheet1.Range("B9").Value = "FilterData" Dim tableHeader = New Object() {"Product Line", "Product", "Amount", "Units", "Total"} worksheet1.Range("B11:E22").Value = New Object(,) { {"Spread.NET", "NewLicense", 1499.0R, 2}, {"Spread.NET", "NewLicense", 1499.0R, 4}, {"Spread.NET", "Upgrade", 899.0R, 1}, {"SpreadJS", "NewLicense", 1499.0R, 5}, {"SpreadJS", "DeploymentLicense", 1499.0R, 5}, {"SpreadJS", "NewLicense", 1499.0R, 4}, {"Spread.NET", "NewLicense", 1499.0R, 6}, {"Spread.NET", "Upgrade", 899.0R, 3}, {"Spread.NET", "NewLicense", 1499.0R, 2}, {"SpreadJS", "DeploymentLicense", 1499.0R, 5}, {"Spread.NET", "NewLicense", 1499.0R, 2}, {"SpreadJS", "Upgrade", 899.0R, 8} } worksheet1.Range("H9").Value = "Filter By Two Criteria" worksheet1.Range("H14").Value = "Use the '*' operator as shown above to combine two or more filter conditions." worksheet1.Range("H32").Value = "Note: these are NOT tables, as spilled array formulas are NOT supported in tables -- instead these ranges are formatted to appear like tables." worksheet1.Range("O32").Value = "Result is #CALC! error when no results returned and nothing specified for if_empty." worksheet1.Range("B10:F10").Value = tableHeader Dim table1 As ITable = worksheet1.Tables.Add(worksheet1.Range("B10:F22"), True) worksheet1.Range("F11:F22").Formula2 = "=[@Amount]*[@Units]" table1.Name = "FilterData" table1.ShowTotals = True Dim table2 As ITable = worksheet1.Tables.Add(worksheet1.Range("H11:L13"), True) table2.ShowTotals = True table2.ConvertToRange() Dim table3 As ITable = worksheet1.Tables.Add(worksheet1.Range("B26:F33"), True) table3.ShowTotals = True table3.ConvertToRange() Dim table4 As ITable = worksheet1.Tables.Add(worksheet1.Range("H26:L31"), True) table4.ShowTotals = True table4.ConvertToRange() Dim table5 As ITable = worksheet1.Tables.Add(worksheet1.Range("N26:R28"), True) table5.ShowTotals = True table5.ConvertToRange() Dim table6 As ITable = worksheet1.Tables.Add(worksheet1.Range("N32:R33"), True) table6.ConvertToRange() Dim table7 As ITable = worksheet1.Tables.Add(worksheet1.Range("B37:F42"), True) table7.ShowTotals = True table7.ConvertToRange() Dim table8 As ITable = worksheet1.Tables.Add(worksheet1.Range("H37:L39"), True) table8.ShowTotals = True table8.ConvertToRange() Dim table9 As ITable = worksheet1.Tables.Add(worksheet1.Range("N37:R38"), True) table9.ShowTotals = True table9.ConvertToRange() Dim table10 As ITable = worksheet1.Tables.Add(worksheet1.Range("T26:X27"), True) table10.ConvertToRange() Dim table11 As ITable = worksheet1.Tables.Add(worksheet1.Range("T37:X39"), True) table11.ShowTotals = True table11.ConvertToRange() worksheet1.Range("B10:F10,H11:L11,B26:F26,H26:L26,N26:R26,N32:R32,,B37:F37,H37:L37,N37:R37,T26:X26,T37:X37").Value = tableHeader worksheet1.Range("H10,B25,H25,N25,T25,N31,B36,H36,N36,T36").Value = "Formula:" ' Set Styles. worksheet1.Range("A1").Font.Size = 18 worksheet1.Range("A1").Font.Bold = True worksheet1.Range("B4:C4,I10:P10,C25:F25,I25:L25,O25:R25,U25:Y25,O31:R31,C36:F36,I36:L36,O36:R36,U36:X36").Interior.Color = Color.FromArgb(180, 198, 231) worksheet1.Range("B4:C4,I10:P10,C25:F25,I25:L25,O25:R25,U25:Y25,O31:R31,C36:F36,I36:L36,O36:R36,U36:X36").Borders.LineStyle = BorderLineStyle.Thin worksheet1.Range("B4:C4,I10:P10,C25:F25,I25:L25,O25:R25,U25:Y25,O31:R31,C36:F36,I36:L36,O36:R36,U36:X36").Borders.Color = Color.Gray worksheet1.Range("B5:C5,B6:I6,B7:I7,H15:L15,H10,B25,H25,N25,T25,N31,O33:U33,H34:R34,B36,H36,N36,T36").Interior.Color = Color.FromArgb(231, 230, 230) worksheet1.Range("B5:C5,B6:I6,B7:I7,H15:L15,H10,B25,H25,N25,T25,N31,O33:U33,H34:R34,B36,H36,N36,T36").Borders.LineStyle = BorderLineStyle.Thin worksheet1.Range("B5:C5,B6:I6,B7:I7,H15:L15,H10,B25,H25,N25,T25,N31,O33:U33,H34:R34,B36,H36,N36,T36").Borders.Color = Color.Gray worksheet1.Range("B9,H9").Font.Name = "Calibri Light" worksheet1.Range("B9,H9").Font.Size = 18 worksheet1.Range("B9,H9").Font.Color = Color.FromArgb(68, 84, 106) worksheet1.Range("B3").Font.Name = "Calibri Light" worksheet1.Range("B3").Font.Size = 11 worksheet1.Range("B3").Font.Bold = True worksheet1.Range("B3").Font.Color = Color.FromArgb(68, 84, 106) worksheet1.Range("B:B,H:H,N:N,T:T").ColumnWidthInPixel = 113.0R worksheet1.Range("C:C,I:I,O:O,U:U").ColumnWidthInPixel = 124.0R worksheet1.Range("D:D,F:F,J:J,L:L,P:P,R:R,V:V,X:X").NumberFormat = "$#,##0" ' Set formulas worksheet1.Range("H12").Formula2 = "=FILTER(FilterData,(FilterData[Product Line]=""SpreadJS"")*(FilterData[Product]=""DeploymentLicense""))" worksheet1.Range("I10").Formula = "=FORMULATEXT(H12)" worksheet1.Range("L14").Formula = "=SUM(L12:L13)" worksheet1.Range("B27").Formula2 = "=FILTER(FilterData,FilterData[Product Line]=""Spread.NET"")" worksheet1.Range("C25").Formula = "=FORMULATEXT(B27)" worksheet1.Range("F34").Formula = "=SUM(F27:F33)" worksheet1.Range("H27").Formula2 = "=FILTER(B27#,B27# C:C=""NewLicense"")" worksheet1.Range("I25").Formula = "=FORMULATEXT(H27)" worksheet1.Range("L32").Formula = "=SUM(L27:L31)" worksheet1.Range("N27").Formula2 = "=FILTER(B27#,B27# C:C=""Upgrade"")" worksheet1.Range("O25").Formula = "=FORMULATEXT(N27)" worksheet1.Range("R29").Formula = "=SUM(R27:R28)" worksheet1.Range("N33").Formula2 = "=FILTER(B27#,B27# C:C=""DeploymentLicense"")" worksheet1.Range("O31").Formula = "=FORMULATEXT(N33)" worksheet1.Range("T27").Formula2 = "=FILTER(B27#,B27# C:C=""DeploymentLicense"",""**NONE FOUND**"")" worksheet1.Range("U25").Formula = "=FORMULATEXT(T27)" worksheet1.Range("B38").Formula2 = "=FILTER(FilterData,FilterData[Product Line]=""SpreadJS"")" worksheet1.Range("C36").Formula = "=FORMULATEXT(B38)" worksheet1.Range("F43").Formula = "=SUM(F38:F42)" worksheet1.Range("H38").Formula2 = "=FILTER(B38#,C38:C42=""NewLicense"")" worksheet1.Range("I36").Formula = "=FORMULATEXT(H38)" worksheet1.Range("L40").Formula = "=SUM(L38:L39)" worksheet1.Range("N38").Formula2 = "=FILTER(B38#,C38:C42=""Upgrade"")" worksheet1.Range("O36").Formula = "=FORMULATEXT(N38)" worksheet1.Range("R39").Formula = "=SUM(R38)" worksheet1.Range("T38").Formula2 = "=FILTER(B38#,C38:C42=""DeploymentLicense"")" worksheet1.Range("U36").Formula = "=FORMULATEXT(T38)" worksheet1.Range("X40").Formula = "=SUM(X38:X39)" #End Region #Region "worksheet2 RANDARRAY" Dim worksheet2 As IWorksheet = workbook.Worksheets.Add() ' Set vlaue. worksheet2.Name = "RANDARRAY" worksheet2.Range("A1").Value = "RANDARRAY Function Example" worksheet2.Range("B3:B11").Value = New Object(,) { {"RANDARRAY Syntax:"}, {"RANDARRAY([rows],[columns],[min],[max],[integer])"}, {"rows: Number of rows of random numbers to generate (default is 1)."}, {"columns: Number of columns of random numbers to generate (default is 1)."}, {"min: Minimum of values to generate (default is 0)."}, {"max: Maximum of values to generate (default is 1)."}, {"integer: True to return integer values (default is False)."}, {"The RANDARRAY function is a voltile function."}, {"This means that each time the worksheet calculates, the RANDARRAY function recalculates new values."} } worksheet2.Range("A13:A17").Value = New Object(,) { {"Rows:"}, {"Columns:"}, {"Min:"}, {"Max:"}, {"Integer:"} } worksheet2.Range("D13:D17").Value = New Object(,) { {"1. When nothing is specified for Rows, then the default value 1 is used."}, {"2. When nothing is specified for Columns, then the default value 1 is used."}, {"3. When nothing is specified for Min, then the default value 0 is used."}, {"4. When nothing is specified for Max, then the default value 1 is used."}, {"5. When nothing is specified for Integer, then the default value False is used and decimal values are returned."} } worksheet2.Range("A19").Value = "Formula:" ' Set style. worksheet2.Range("A1").Font.Size = 18 worksheet2.Range("A1").Font.Bold = True worksheet2.Range("1:1").RowHeight = 23.4R worksheet2.Range("A:A").ColumnWidthInPixel = 75.0R worksheet2.Range("B4:F4,A19").Interior.Color = Color.FromArgb(180, 198, 231) worksheet2.Range("B4:F4,A19").Borders.LineStyle = BorderLineStyle.Thin worksheet2.Range("B4:F4,A19").Borders.Color = Color.Gray worksheet2.Range("B19:O19,B5:H5,B6:H6,B7:F7,B8:F8,B9:F9,B10:F10,B11:K11,A20").Interior.Color = Color.FromArgb(231, 230, 230) worksheet2.Range("B19:O19,B5:H5,B6:H6,B7:F7,B8:F8,B9:F9,B10:F10,B11:K11,A20").Borders.LineStyle = BorderLineStyle.Thin worksheet2.Range("B19:O19,B5:H5,B6:H6,B7:F7,B8:F8,B9:F9,B10:F10,B11:K11,A20").Borders.Color = Color.Gray worksheet2.Range("B3").Font.Name = "Calibri Light" worksheet2.Range("B3").Font.Size = 11 worksheet2.Range("B3").Font.Bold = True worksheet2.Range("B3").Font.Color = Color.FromArgb(68, 84, 106) worksheet2.Range("B13:B17").Interior.Color = Color.FromArgb(189, 215, 238) worksheet2.Range("B13:B17").Borders.LineStyle = BorderLineStyle.Thin worksheet2.Range("B13:B17").Borders.Color = Color.Gray ' Set array formula. worksheet2.Range("B19").Formula = "=FORMULATEXT(A20)" worksheet2.Range("A20").Formula2 = "=RANDARRAY(IF(ISBLANK(B13),1,B13),IF(ISBLANK(B14),1,B14),IF(ISBLANK(B15),0,B15),IF(ISBLANK(B16),1,B16),IF(ISBLANK(B17),FALSE,B17))" #End Region #Region "worksheet3 SEQUENCE" Dim worksheet3 As IWorksheet = workbook.Worksheets.Add() ' Set data. worksheet3.Name = "SEQUENCE" worksheet3.Range("A1").Value = "SEQUENCE Function Example" worksheet3.Range("B3:B8").Value = New Object(,) { {"SEQUENCE Syntax"}, {"SEQUENCE(rows,[columns],[start],[step])"}, {"rows: Number of rows to generate in the sequence (this value is required; if not provided the function returns #CALC! error)."}, {"columns: Optional number of columns to generate in the sequence (default is 1)."}, {"start: Optional starting value (default is 1)."}, {"step: Optional increment value (default is 1)."} } worksheet3.Range("A10:A13").Value = New Object(,) { {"Rows:"}, {"Columns:"}, {"Start:"}, {"Step:"} } worksheet3.Range("B10").Value = 1.0R worksheet3.Range("D10:D13").Value = New Object(,) { {"1. When nothing is specified for the first argument (Rows), then the function returns #CALC! error."}, {"2. When the optional Columns argument is specified, then the function returns an array with the specified number of columns."}, {"3. When the optional Start argument is specified, then SEQUENCE returns values starting with that specified value."}, {"4. When the optional Step argument is specified, then SEQUENCE returns values incremented with that specified value."} } worksheet3.Range("A15:A19").Value = New Object(,) { {"Typically, SEQUENCE is used with hard-coded arguments to generate a specific sequence of values for the dynamic array formula."}, {"This example uses cell references for the arguments of SEQUENCE for illustrative purposes, so you can explore how the function works."}, {"The formula uses the values above if they are specified, and uses the default values for the arguments when the cells are empty."}, {"This shows how the function operates when particular arguments are included or excluded when hard-coding the arguments to be missing or skipped in the formula."}, {"For example, entering a value for Rows and Step while leaving cells B11 and B12 empty shows the result of SEQUENCE(B3,,,B6)."} } worksheet3.Range("A21").Value = "Formula:" ' Set styles. worksheet3.Range("A1").Font.Size = 18 worksheet3.Range("A1").Font.Bold = True worksheet3.Range("1:1").RowHeight = 23.4R worksheet3.Range("A:A").ColumnWidthInPixel = 75.0R worksheet3.Range("B4:E4,A15:M15,A16:M16,A17:M17,A18:P18,A19:L19").Interior.Color = Color.FromArgb(180, 198, 231) worksheet3.Range("B4:E4,A15:M15,A16:M16,A17:M17,A18:P18,A19:L19").Borders.LineStyle = BorderLineStyle.Thin worksheet3.Range("B4:E4,A15:M15,A16:M16,A17:M17,A18:P18,A19:L19").Borders.Color = Color.Gray worksheet3.Range("B5:M5,B6:I6,B7:E7,B8:E8,B21:J21,A22").Interior.Color = Color.FromArgb(231, 230, 230) worksheet3.Range("B5:M5,B6:I6,B7:E7,B8:E8,B21:J21,A22").Borders.LineStyle = BorderLineStyle.Thin worksheet3.Range("B5:M5,B6:I6,B7:E7,B8:E8,B21:J21,A22").Borders.Color = Color.Gray worksheet3.Range("B10:B13").Interior.Color = Color.FromArgb(189, 215, 238) worksheet3.Range("B10:B13").Borders.LineStyle = BorderLineStyle.Thin worksheet3.Range("B10:B13").Borders.Color = Color.Gray worksheet3.Range("B3").Font.Name = "Calibri Light" worksheet3.Range("B3").Font.Size = 11 worksheet3.Range("B3").Font.Bold = True worksheet3.Range("B3").Font.Color = Color.FromArgb(68, 84, 106) worksheet3.Range("1:1").RowHeight = 23.4R worksheet3.Range("A:A").ColumnWidthInPixel = 75.0R worksheet3.Range("E:E").ColumnWidthInPixel = 76.0R worksheet3.Range("T:T").ColumnWidthInPixel = 485.0R ' Set Formula. worksheet3.Range("B21").Formula = "=FORMULATEXT(A22)" worksheet3.Range("A22").Formula2 = "=SEQUENCE(B10,IF(ISBLANK(B11),1,B11),IF(ISBLANK(B12),1,B12),IF(ISBLANK(B13),1,B13))" #End Region #Region "worksheet4 SINGLE" Dim worksheet4 As IWorksheet = workbook.Worksheets.Add() worksheet4.Name = "SINGLE" worksheet4.Range("A1").Value = "SINGLE Function Examples" worksheet4.Range("B3:B6").Value = New Object(,) { {"SINGLE Syntax"}, {"SINGLE(value)"}, {"@value"}, {"value: Array (in this case, SINGLE returns the first element in the array) or range reference to intersect with the formula cell."} } worksheet4.Range("C5").Value = "When using the SINGLE function in a formula, it is automatically replaced with this syntax using the new '@' operator." worksheet4.Range("A8").Value = "Horizontal Array:" worksheet4.Range("A10").Value = "Formula:" worksheet4.Range("E10:E12").Value = New Object(,) { {"This example references the horizontal array value in cell C8."}, {"This example references the same array value hard-coded."}, {"Note that in the case of a hard-coded array value, the SINGLE function always returns the first value."} } worksheet4.Range("A13").Value = "Vertical Array:" worksheet4.Range("C14,E14,F21,A22,B26").Value = "Formula:" worksheet4.Range("A20").Value = "Range Intersection:" worksheet4.Range("B21").Value = "2-Dimensional Array" worksheet4.Range("G27:G29").Value = New Object(,) { {"The formulas above and to the left use SINGLE to perform explicit range intersection."}, {"The value returned is determined by intersecting the specified range with the formula cell."}, {"The value in the same column (see above) or row (see left) as the formula cell is returned."} } ' Set styles. worksheet4.Range("A1").Font.Size = 18 worksheet4.Range("A1").Font.Bold = True worksheet4.Range("C5:M5,B6:L6,A10,E10:J10,E11:J11,E12:M12,C14,E14,F21,B26,G27:O29").Interior.Color = Color.FromArgb(180, 198, 231) worksheet4.Range("C5:M5,B6:L6,A10,E10:J10,E11:J11,E12:M12,C14,E14,F21,B26,G27:O29").Borders.LineStyle = BorderLineStyle.Thin worksheet4.Range("C5:M5,B6:L6,A10,E10:J10,E11:J11,E12:M12,C14,E14,F21,B26,G27:O29").Borders.Color = Color.Gray worksheet4.Range("B4:B5,B10:B11,D10:D11,D14:D15,F14:G14,F15,B22,C22:E24,G21:I24,B27:E29").Interior.Color = Color.FromArgb(231, 230, 230) worksheet4.Range("B4:B5,B10:B11,D10:D11,D14:D15,F14:G14,F15,B22,C22:E24,G21:I24,B27:E29").Borders.LineStyle = BorderLineStyle.Thin worksheet4.Range("B4:B5,B10:B11,D10:D11,D14:D15,F14:G14,F15,B22,C22:E24,G21:I24,B27:E29").Borders.Color = Color.Gray worksheet4.Range("B3").Font.Name = "Calibri Light" worksheet4.Range("B3").Font.Size = 11 worksheet4.Range("B3").Font.Bold = True worksheet4.Range("B3").Font.Color = Color.FromArgb(68, 84, 106) worksheet4.Range("1:1").RowHeight = 23.4R worksheet4.Range("B:B").ColumnWidthInPixel = 135.0R ' Set formula. worksheet4.Range("C8").Formula2 = "={1,1,2,2,3}" worksheet4.Range("B10").Formula = "=FORMULATEXT(D10)" worksheet4.Range("B11").Formula = "=FORMULATEXT(D11)" worksheet4.Range("D10").Formula2 = "=@C8#" worksheet4.Range("D11").Formula2 = "=@{1,1,2,2,3}" worksheet4.Range("B14").Formula2 = "={1;1;2;2;3}" worksheet4.Range("D14").Formula = "=FORMULATEXT(D15)" worksheet4.Range("D15").Formula2 = "=@B14#" worksheet4.Range("F14").Formula = "=FORMULATEXT(F15)" worksheet4.Range("F15").Formula2 = "=@{1;1;2;2;3}" worksheet4.Range("G21").Formula = "=FORMULATEXT(G22)" worksheet4.Range("H21").Formula = "=FORMULATEXT(H22)" worksheet4.Range("I21").Formula = "=FORMULATEXT(I22)" worksheet4.Range("G22:G24").Formula2 = "=@C:C" worksheet4.Range("H22:H24").Formula2 = "=@D:D" worksheet4.Range("I22:I24").Formula2 = "=@E:E" worksheet4.Range("B22").Formula = "=FORMULATEXT(C22)" worksheet4.Range("C22").Formula2 = "={1,2,3;4,5,6;7,8,9}" worksheet4.Range("B27").Formula = "=FORMULATEXT(C27)" worksheet4.Range("C27:E27").Formula2 = "=@22:22" worksheet4.Range("B28").Formula = "=FORMULATEXT(C28)" worksheet4.Range("C28:E28").Formula2 = "=@23:23" worksheet4.Range("B29").Formula = "=FORMULATEXT(C29)" worksheet4.Range("C29:E29").Formula2 = "=@24:24" #End Region #Region "worksheet5 SORT" Dim worksheet5 As IWorksheet = workbook.Worksheets.Add() ' Set values. worksheet5.Name = "SORT" worksheet5.Range("A1").Value = "SORT Function Examples" worksheet5.Range("B3:B8").Value = New Object(,) { {"SORT Syntax"}, {"SORT(array,[sort_index],[sort_order],[by_col])"}, {"array: Array or range to sort."}, {"sort_index: Optional index of the row or column to sort by (default is 1)."}, {"sort_order: Optional -1 to sort descending (default is 1, ascending)."}, {"by_col: Optional True to sort by column (default is False, sort by row)."} } worksheet5.Range("G4").Value = "Note: sort_index and sort_order can be array arguments (which must be the same length) that specify multiple sort keys and corresponding sort key orders." worksheet5.Range("I9").Value = "Sort by ProductName" worksheet5.Range("B10").Value = "SortData" Dim tableHeader_5 = New Object(,) { {"Date", "ProductName", "Quantity", "Price", "Total", "SalesPerson"} } worksheet5.Range("B11:G11").Value = tableHeader_5 worksheet5.Range("I11:N11").Value = tableHeader_5 worksheet5.Range("B26:G26").Value = tableHeader_5 worksheet5.Range("I26:N26").Value = tableHeader_5 worksheet5.Range("B43:G43").Value = tableHeader_5 worksheet5.Range("I43:N43").Value = tableHeader_5 worksheet5.Range("B12:G22").Value = New Object(,) { {DateTime.Parse("2019/5/1 0:00:00"), "Spread.NET", 1.0R, 1499.0R, Nothing, "Jim"}, {DateTime.Parse("2019/5/1 0:00:00"), "Spread.NET", 2.0R, 1499.0R, Nothing, "Fred"}, {DateTime.Parse("2019/5/2 0:00:00"), "SpreadJS", 4.0R, 1499.0R, Nothing, "Fred"}, {DateTime.Parse("2019/5/3 0:00:00"), "Spread.NET", 2.0R, 1499.0R, Nothing, "Fred"}, {DateTime.Parse("2019/5/3 0:00:00"), "SpreadJS", 3.0R, 1499.0R, Nothing, "Barry"}, {DateTime.Parse("2019/5/3 0:00:00"), "SpreadJS", 1.0R, 1499.0R, Nothing, "Linda"}, {DateTime.Parse("2019/5/3 0:00:00"), "Spread.NET", 4.0R, 1499.0R, Nothing, "Barry"}, {DateTime.Parse("2019/5/3 0:00:00"), "Spread.NET", 2.0R, 1499.0R, Nothing, "Linda"}, {DateTime.Parse("2019/5/4 0:00:00"), "SpreadJS", 4.0R, 1499.0R, Nothing, "Jim"}, {DateTime.Parse("2019/5/5 0:00:00"), "Spread.NET", 6.0R, 1499.0R, Nothing, "Jim"}, {DateTime.Parse("2019/5/5 0:00:00"), "SpreadJS", 4.0R, 1499.0R, Nothing, "Barry"} } worksheet5.Range("B39").Value = "Note: these are NOT tables, as spilled array formulas are NOT supported in tables -- instead these ranges are formatted to appear like tables." worksheet5.Range("I10,B25,I25,B42,I42").Value = "Formula:" worksheet5.Range("B24").Value = "Sort by Quantity Descending" worksheet5.Range("I24").Value = "Sort by SalesPerson" worksheet5.Range("B41").Value = "Sort by Quantity Descending then SalesPerson Ascending" worksheet5.Range("I41").Value = "Sort by SalesPerson Ascending then Quantity Descending" Dim table As ITable = worksheet5.Tables.Add(worksheet5.Range("B11:G22"), True) table.Name = "SortData" Dim table1_5 As ITable = worksheet5.Tables.Add(worksheet5.Range("I11:N22"), True) table1_5.ConvertToRange() Dim table2_5 As ITable = worksheet5.Tables.Add(worksheet5.Range("B26:G37"), True) table2_5.ConvertToRange() Dim table3_5 As ITable = worksheet5.Tables.Add(worksheet5.Range("I26:N37"), True) table3_5.ConvertToRange() Dim table4_5 As ITable = worksheet5.Tables.Add(worksheet5.Range("B43:G54"), True) table4_5.ConvertToRange() Dim table5_5 As ITable = worksheet5.Tables.Add(worksheet5.Range("I43:N54"), True) table5_5.ConvertToRange() ' Set Styles. worksheet5.Range("A1").Font.Size = 18 worksheet5.Range("A1").Font.Bold = True worksheet5.Range("G4:M5").Merge() worksheet5.Range("G4").WrapText = True worksheet5.Range("B4:C4,I10,B25,I25,B42,I42,B39:J39").Interior.Color = Color.FromArgb(180, 198, 231) worksheet5.Range("B4:C4,I10,B25,I25,B42,I42,B39:J39").Borders.LineStyle = BorderLineStyle.Thin worksheet5.Range("B4:C4,I10,B25,I25,B42,I42,B39:J39").Borders.Color = Color.Gray worksheet5.Range("B5:C5,B6:E8,G4:M4,J10,C25,J25,C42:D42,J42:K42").Interior.Color = Color.FromArgb(231, 230, 230) worksheet5.Range("B5:C5,B6:E8,G4:M4,J10,C25,J25,C42:D42,J42:K42").Borders.LineStyle = BorderLineStyle.Thin worksheet5.Range("B5:C5,B6:E8,G4:M4,J10,C25,J25,C42:D42,J42:K42").Borders.Color = Color.Gray worksheet5.Range("B10,I9,B24,I24,B41,I41").Font.Name = "Calibri Light" worksheet5.Range("B10,I9,B24,I24,B41,I41").Font.Size = 18 worksheet5.Range("B10,I9,B24,I24,B41,I41").Font.Color = Color.FromArgb(68, 84, 106) worksheet5.Range("B3").Font.Name = "Calibri Light" worksheet5.Range("B3").Font.Size = 11 worksheet5.Range("B3").Font.Bold = True worksheet5.Range("B3").Font.Color = Color.FromArgb(68, 84, 106) worksheet5.Range("1:1,9:10,24:24,41:41").RowHeight = 23.4R worksheet5.Range("B:B,I:I").ColumnWidthInPixel = 113.0R worksheet5.Range("C:C,J:J").ColumnWidthInPixel = 155.0R worksheet5.Range("D:D,K:K").ColumnWidthInPixel = 92.0R worksheet5.Range("E:F,L:M").ColumnWidthInPixel = 84.0R worksheet5.Range("G:G,N:N").ColumnWidthInPixel = 117.0R worksheet5.Range("E:F,L:M").NumberFormat = "$#,##0" ' Set Formulas. worksheet5.Range("J10").Formula = "=FORMULATEXT(I12)" worksheet5.Range("F12").Formula = "=D12*E12" worksheet5.Range("F13").Formula = "=D13*E13" worksheet5.Range("F14").Formula = "=D14*E14" worksheet5.Range("F15").Formula = "=D15*E15" worksheet5.Range("F16").Formula = "=D16*E16" worksheet5.Range("F17").Formula = "=D17*E17" worksheet5.Range("F18").Formula = "=D18*E18" worksheet5.Range("F19").Formula = "=D19*E19" worksheet5.Range("F20").Formula = "=D20*E20" worksheet5.Range("F21").Formula = "=D21*E21" worksheet5.Range("F22").Formula = "=D22*E22" worksheet5.Range("I12").Formula2 = "=SORT(SortData,2)" worksheet5.Range("C25").Formula = "=FORMULATEXT(B27)" worksheet5.Range("J25").Formula = "=FORMULATEXT(I27)" worksheet5.Range("B27").Formula2 = "=SORT(SortData,3,-1)" worksheet5.Range("I27").Formula2 = "=SORT(SortData,6)" worksheet5.Range("C42").Formula = "=FORMULATEXT(B44)" worksheet5.Range("J42").Formula = "=FORMULATEXT(I44)" worksheet5.Range("B44").Formula2 = "=SORT(SortData,{3,6},{-1,1})" worksheet5.Range("I44").Formula2 = "=SORT(SortData,{6,3},{1,-1})" #End Region #Region "worksheet6 SORTBY" Dim worksheet6 As IWorksheet = workbook.Worksheets.Add() ' Set values. worksheet6.Name = "SORTBY" worksheet6.Range("A1").Value = "SORTBY Function Examples" worksheet6.Range("B3:B10").Value = New Object(,) { {"SORTBY Syntax"}, {"SORTBY(array,by_array1,[order_array1],[by_array2, order_array2],…)"}, {"array: Array or range to be sorted (required)."}, {"by_array1: array or range of first sort key (required)."}, {"order_array1: Optional -1 for descending order (default is 1 asending)."}, {"by_array2: Optional array or range of second sort key."}, {"order_array2: -1 for decsending, 1 for ascending (required if by_array2 specified)."}, {"This function can accept additional arguments in pairs, specifying the next sort key range and order."} } Dim tableHeader_6 = New Object(,) { {"Date", "ProductName", "Quantity", "Price", "Total", "SalesPerson"} } worksheet6.Range("B13:G13").Value = tableHeader_6 worksheet6.Range("J13:O13").Value = tableHeader_6 worksheet6.Range("B28:G28").Value = tableHeader_6 worksheet6.Range("J28:J28").Value = tableHeader_6 worksheet6.Range("B14:G24").Value = New Object(,) { {DateTime.Parse("2019/5/1 0:00:00"), "Spread.NET", 1.0R, 1499.0R, Nothing, "Jim"}, {DateTime.Parse("2019/5/1 0:00:00"), "Spread.NET", 2.0R, 1499.0R, Nothing, "Fred"}, {DateTime.Parse("2019/5/2 0:00:00"), "SpreadJS", 4.0R, 1499.0R, Nothing, "Fred"}, {DateTime.Parse("2019/5/3 0:00:00"), "Spread.NET", 2.0R, 1499.0R, Nothing, "Fred"}, {DateTime.Parse("2019/5/3 0:00:00"), "SpreadJS", 3.0R, 1499.0R, Nothing, "Barry"}, {DateTime.Parse("2019/5/3 0:00:00"), "SpreadJS", 1.0R, 1499.0R, Nothing, "Linda"}, {DateTime.Parse("2019/5/3 0:00:00"), "Spread.NET", 4.0R, 1499.0R, Nothing, "Barry"}, {DateTime.Parse("2019/5/3 0:00:00"), "Spread.NET", 2.0R, 1499.0R, Nothing, "Linda"}, {DateTime.Parse("2019/5/4 0:00:00"), "SpreadJS", 4.0R, 1499.0R, Nothing, "Jim"}, {DateTime.Parse("2019/5/5 0:00:00"), "Spread.NET", 6.0R, 1499.0R, Nothing, "Jim"}, {DateTime.Parse("2019/5/5 0:00:00"), "SpreadJS", 4.0R, 1499.0R, Nothing, "Barry"} } worksheet6.Range("B12,J12,B27,J27").Value = "Formula:" worksheet6.Range("J11").Value = "Sort by ProductName then Quantity Descending" worksheet6.Range("B26").Value = "Sort By Quantity Descending, then Product Name" worksheet6.Range("J26").Value = "Sort by SalesPerson then Quantity Descending" worksheet6.Range("B41").Value = "Note: these are NOT tables, as spilled array formulas are NOT supported in tables -- instead these ranges are formatted to appear like tables." Dim table_6 As ITable = worksheet6.Tables.Add(worksheet6.Range("B13:G24"), True) table_6.Name = "SortByData" Dim table1_6 As ITable = worksheet6.Tables.Add(worksheet6.Range("J13:O24"), True) table1_6.ConvertToRange() Dim table2_6 As ITable = worksheet6.Tables.Add(worksheet6.Range("B28:G39"), True) table2_6.ConvertToRange() Dim table3_6 As ITable = worksheet6.Tables.Add(worksheet6.Range("J28:O39"), True) table3_6.ConvertToRange() ' Set Styles. worksheet6.Range("A1").Font.Size = 18 worksheet6.Range("A1").Font.Bold = True worksheet6.Range("B4:F4,J12,B27,J27,B41:K41").Interior.Color = Color.FromArgb(180, 198, 231) worksheet6.Range("B4:F4,J12,B27,J27,B41:K41").Borders.LineStyle = BorderLineStyle.Thin worksheet6.Range("B4:F4,J12,B27,J27,B41:K41").Borders.Color = Color.Gray worksheet6.Range("B5:D5,B6:E6,B7:F7,B8:E8,B9:F9,B10:H10,K12:O12,C27:G27,K27:O27").Interior.Color = Color.FromArgb(231, 230, 230) worksheet6.Range("B5:D5,B6:E6,B7:F7,B8:E8,B9:F9,B10:H10,K12:O12,C27:G27,K27:O27").Borders.LineStyle = BorderLineStyle.Thin worksheet6.Range("B5:D5,B6:E6,B7:F7,B8:E8,B9:F9,B10:H10,K12:O12,C27:G27,K27:O27").Borders.Color = Color.Gray worksheet6.Range("B12,J11,B26,J26").Font.Name = "Calibri Light" worksheet6.Range("B12,J11,B26,J26").Font.Size = 18 worksheet6.Range("B12,J11,B26,J26").Font.Color = Color.FromArgb(68, 84, 106) worksheet6.Range("B3").Font.Name = "Calibri Light" worksheet6.Range("B3").Font.Size = 11 worksheet6.Range("B3").Font.Bold = True worksheet6.Range("B3").Font.Color = Color.FromArgb(68, 84, 106) worksheet6.Range("1:1,11:12,26:26").RowHeight = 23.4R worksheet6.Range("B:B,J:J").ColumnWidthInPixel = 113.0R worksheet6.Range("C:C,K:K").ColumnWidthInPixel = 124.0R worksheet6.Range("D:D,L:L").ColumnWidthInPixel = 88.0R worksheet6.Range("E:F,M:N").ColumnWidthInPixel = 101.0R worksheet6.Range("G:G,O:O").ColumnWidthInPixel = 116.0R worksheet6.Range("E:F,M:N").NumberFormat = "$#,##0" ' Set formulas. worksheet6.Range("K12").Formula = "=FORMULATEXT(J14)" worksheet6.Range("F14").Formula = "=D14*E14" worksheet6.Range("F15").Formula = "=D15*E15" worksheet6.Range("F16").Formula = "=D16*E16" worksheet6.Range("F17").Formula = "=D17*E17" worksheet6.Range("F18").Formula = "=D18*E18" worksheet6.Range("F19").Formula = "=D19*E19" worksheet6.Range("F20").Formula = "=D20*E20" worksheet6.Range("F21").Formula = "=D21*E21" worksheet6.Range("F22").Formula = "=D22*E22" worksheet6.Range("F23").Formula = "=D23*E23" worksheet6.Range("F24").Formula = "=D24*E24" worksheet6.Range("J14").Formula2 = "=SORTBY(SortByData,SortByData[ProductName],1,SortByData[Quantity],-1)" worksheet6.Range("C27").Formula = "=FORMULATEXT(B29)" worksheet6.Range("K27").Formula = "=FORMULATEXT(J29)" worksheet6.Range("B29").Formula2 = "=SORTBY(SortByData,SortByData[Quantity],-1,SortByData[ProductName],1)" worksheet6.Range("J29").Formula2 = "=SORTBY(SortByData,SortByData[SalesPerson],1,SortByData[Quantity],-1)" #End Region #Region "Worksheet7 Unique" Dim worksheet7 As IWorksheet = workbook.Worksheets.Add() worksheet7.Name = "UNIQUE" worksheet7.Range("A1").Value = "UNIQUE Function Example" worksheet7.Range("B3:B7").Value = New Object(,) { {"UNIQUE Syntax"}, {"UNIQUE(array,[by_col],[unique_only])"}, {"array: Array or cell range containing values to be compared."}, {"by_col: Whether to compare values by column or by row (default is False, by row)."}, {"unique_only: Whether to return only the unique values (that is, return only the values which actually occur exactly once in the comparison set); the default is False."} } worksheet7.Range("A9").Value = "Horizontal Array:" worksheet7.Range("A11:A16").Value = New Object(,) { {"If you specify a horizontal range of values, and don't specify TRUE for the 2nd argument, then UNIQUE won't generate the unique values."}, {"Formula:"}, {"You must specify TRUE for the 2nd argument to compare column values in the rows instead of row values in the columns:"}, {"Formula:"}, {"If you also specify TRUE for the 3rd argument, then ONLY the unique values will be returned:"}, {"Formula:"} } worksheet7.Range("J12").Value = "This doesn't work with only one row of values." worksheet7.Range("A18").Value = "If there are no unique items to return, then UNIQUE returns either #CALC! or #SPILL! Error." worksheet7.Range("C20").Value = "If you specify a vertical range of values, then you should skip the 2nd argument or use False to compare row values." worksheet7.Range("A21").Value = "Vertical Array:" worksheet7.Range("C21,F21,I21,N21,Q21,A37,I37,A43,I43,A49,I49,A56,I56,A62").Value = "Formula:" worksheet7.Range("N20").Value = "These combinations don't work with only one column of values:" worksheet7.Range("A28").Value = "Cell Range Array:" worksheet7.Range("A35").Value = "If you specify a cell range, then you must specify True for the 2nd argument if you want the column values unique in each row, or False if you want the row values unique in each column." worksheet7.Range("C36").Value = "(three unique rows returned since 1st/2nd and 3rd/4th same)" worksheet7.Range("A36").Value = "Case 1" worksheet7.Range("I36").Value = "Case 2" worksheet7.Range("M37").Value = "(same as case 1 to left)" worksheet7.Range("A42").Value = "Case 3" worksheet7.Range("D43").Value = "(same case 1 as above)" worksheet7.Range("I42").Value = "Case 4" worksheet7.Range("M43").Value = "(one unique row returned, 5th, since 1st/2nd and 3rd/4th rows not unique)" worksheet7.Range("A48").Value = "Case 5" worksheet7.Range("D49").Value = "(four unique columns returned since 2nd/4th same)" worksheet7.Range("I48").Value = "Case 6" worksheet7.Range("M49").Value = "(same as case 5 to left)" worksheet7.Range("A55").Value = "Case 7" worksheet7.Range("D56").Value = "(same as case 1/2/3)" worksheet7.Range("I55").Value = "Case 8" worksheet7.Range("M56").Value = "(returns three unique columns, 1st, 3rd, and 5th, since 2nd and 4th are same)" worksheet7.Range("A61").Value = "Case 9" worksheet7.Range("D62").Value = "(same as case 4)" ' Set styles. worksheet7.Range("A1").Font.Size = 18 worksheet7.Range("A1").Font.Bold = True worksheet7.Range("B4:D4,A12,A13:J13,A14,A15:H15,A16,A18:H18,C20:L20,N20:R20,C21,F21,I21,N21,Q21,A35:O35,C36:H36,A37,I37,M37:O37,A43,D43:F43,I43,M43:S43,A49,D49:H49,I49,M49:N49,A56,D56:E56,I56,M56:R56,A62,D62:E62").Interior.Color = Color.FromArgb(180, 198, 231) worksheet7.Range("B4:D4,A12,A13:J13,A14,A15:H15,A16,A18:H18,C20:L20,N20:R20,C21,F21,I21,N21,Q21,A35:O35,C36:H36,A37,I37,M37:O37,A43,D43:F43,I43,M43:S43,A49,D49:H49,I49,M49:N49,A56,D56:E56,I56,M56:R56,A62,D62:E62").Borders.LineStyle = BorderLineStyle.Thin worksheet7.Range("B4:D4,A12,A13:J13,A14,A15:H15,A16,A18:H18,C20:L20,N20:R20,C21,F21,I21,N21,Q21,A35:O35,C36:H36,A37,I37,M37:O37,A43,D43:F43,I43,M43:S43,A49,D49:H49,I49,M49:N49,A56,D56:E56,I56,M56:R56,A62,D62:E62").Borders.Color = Color.Gray worksheet7.Range("B5:F5,B6:H6,B7:N7,B12,D12:H12,B14:F14,B16:D16,D21:E21,G21:H21,J21:K21,O21:P21,R21:T21,E22:E24,H22:H24,K22,P22:P26,S22:S26,B37,J37:L37,B38:F40,J38:N40,B43:C43,B44:F46,J43:L43,J44:N44,B49:C49,J49:L49,B50:E54,J50:M54,B56:C56,J56:L56,B57:F59,J57:L61,B62:C62,B63:F63").Interior.Color = Color.FromArgb(231, 230, 230) worksheet7.Range("B5:F5,B6:H6,B7:N7,B12,D12:H12,B14:F14,B16:D16,D21:E21,G21:H21,J21:K21,O21:P21,R21:T21,E22:E24,H22:H24,K22,P22:P26,S22:S26,B37,J37:L37,B38:F40,J38:N40,B43:C43,B44:F46,J43:L43,J44:N44,B49:C49,J49:L49,B50:E54,J50:M54,B56:C56,J56:L56,B57:F59,J57:L61,B62:C62,B63:F63").Borders.LineStyle = BorderLineStyle.Thin worksheet7.Range("B5:F5,B6:H6,B7:N7,B12,D12:H12,B14:F14,B16:D16,D21:E21,G21:H21,J21:K21,O21:P21,R21:T21,E22:E24,H22:H24,K22,P22:P26,S22:S26,B37,J37:L37,B38:F40,J38:N40,B43:C43,B44:F46,J43:L43,J44:N44,B49:C49,J49:L49,B50:E54,J50:M54,B56:C56,J56:L56,B57:F59,J57:L61,B62:C62,B63:F63").Borders.Color = Color.Gray worksheet7.Range("C9:G9,B22:B26,A29:E33").Interior.Color = Color.FromArgb(189, 215, 238) worksheet7.Range("C9:G9,B22:B26,A29:E33").Borders.LineStyle = BorderLineStyle.Thin worksheet7.Range("C9:G9,B22:B26,A29:E33").Borders.Color = Color.Gray worksheet7.Range("B3,A9,A21,A28,A36,I36,A42,I42,A48,I48,A55,I55,A61").Font.Name = "Calibri Light" worksheet7.Range("B3,A9,A21,A28,A36,I36,A42,I42,A48,I48,A55,I55,A61").Font.Size = 11 worksheet7.Range("B3,A9,A21,A28,A36,I36,A42,I42,A48,I48,A55,I55,A61").Font.Bold = True worksheet7.Range("B3,A9,A21,A28,A36,I36,A42,I42,A48,I48,A55,I55,A61").Font.Color = Color.FromArgb(68, 84, 106) worksheet7.Range("1:1").RowHeight = 23.4R worksheet7.Range("B:B").ColumnWidthInPixel = 123.0R worksheet7.Range("D:D").ColumnWidthInPixel = 81.0R worksheet7.Range("H:H").ColumnWidthInPixel = 89.0R worksheet7.Range("K:K,N:N,Q:Q").ColumnWidthInPixel = 92.0R ' Set formulas. worksheet7.Range("C9").Formula2 = "={1,1,2,2,3}" worksheet7.Range("B12").Formula = "=FORMULATEXT(D12)" worksheet7.Range("D12").Formula2 = "=UNIQUE(C9#)" worksheet7.Range("B14").Formula = "=FORMULATEXT(D14)" worksheet7.Range("D14").Formula2 = "=UNIQUE(C9#,TRUE)" worksheet7.Range("B16").Formula = "=FORMULATEXT(D16)" worksheet7.Range("D16").Formula2 = "=UNIQUE(C9#,TRUE,TRUE)" worksheet7.Range("D21").Formula = "=FORMULATEXT(E22)" worksheet7.Range("E22").Formula2 = "=UNIQUE(B22#)" worksheet7.Range("G21").Formula = "=FORMULATEXT(H22)" worksheet7.Range("H22").Formula2 = "=UNIQUE(B22#,FALSE)" worksheet7.Range("J21").Formula = "=FORMULATEXT(K22)" worksheet7.Range("K22").Formula2 = "=UNIQUE(B22#,,TRUE)" worksheet7.Range("B22").Formula2 = "={1;1;2;2;3}" worksheet7.Range("O21").Formula = "=FORMULATEXT(P22)" worksheet7.Range("P22").Formula2 = "=UNIQUE(B22#,TRUE)" worksheet7.Range("R21").Formula = "=FORMULATEXT(S22)" worksheet7.Range("S22").Formula2 = "=UNIQUE(B22#,TRUE,TRUE)" worksheet7.Range("A29").Formula2 = "={1,1,2,1,3;1,1,2,1,3;1,2,3,2,5;1,2,3,2,5;1,2,2,2,5}" worksheet7.Range("B37").Formula = "=FORMULATEXT(B38)" worksheet7.Range("B38").Formula2 = "=UNIQUE(A29#)" worksheet7.Range("J37").Formula = "=FORMULATEXT(J38)" worksheet7.Range("J38").Formula2 = "=UNIQUE(A29#,FALSE,FALSE)" worksheet7.Range("B43").Formula = "=FORMULATEXT(B44)" worksheet7.Range("B44").Formula2 = "=UNIQUE(A29#,FALSE)" worksheet7.Range("J43").Formula = "=FORMULATEXT(J44)" worksheet7.Range("J44").Formula2 = "=UNIQUE(A29#,FALSE,TRUE)" worksheet7.Range("B49").Formula = "=FORMULATEXT(B50)" worksheet7.Range("B50").Formula2 = "=UNIQUE(A29#,TRUE)" worksheet7.Range("J49").Formula = "=FORMULATEXT(J50)" worksheet7.Range("J50").Formula2 = "=UNIQUE(A29#,TRUE,FALSE)" worksheet7.Range("B56").Formula = "=FORMULATEXT(B57)" worksheet7.Range("B57").Formula2 = "=UNIQUE(A29#,,FALSE)" worksheet7.Range("J56").Formula = "=FORMULATEXT(J57)" worksheet7.Range("J57").Formula2 = "=UNIQUE(A29#,TRUE,TRUE)" worksheet7.Range("B62").Formula = "=FORMULATEXT(B63)" worksheet7.Range("B63").Formula2 = "=UNIQUE(A29#,,TRUE)" #End Region ' save to an excel file workbook.Save("UseCase2.xlsx")