//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); IWorksheet ws1 = workbook.Worksheets["Sheet1"]; ws1.Name = "Lookup result address"; //Set value. ws1.Range["A1:B7"].Value = new object[,] { { "Item", "Sales"}, { "Apples", 100}, { "Bananas", 190}, { "Lemons", 150}, { "Oranges", 130}, { "Grapes", 220}, { "Limes", 200} }; ws1.Range["D1"].Value = "Item"; ws1.Range["D2"].Value = "Sales"; ws1.Range["D3"].Value = "Address"; ws1.Range["E1"].Value = "Oranges"; ws1.Range["F2"].Value = "=INDEX(B2:B7,MATCH(E1,A2:A7,0))"; ws1.Range["F3"].Value = "=CELL(\"address\",INDEX(B2:B7,MATCH(E1,A2:A7,0)))"; //Add table. ITable table = ws1.Tables.Add(ws1.Range["A1:B7"], true); //Set styles. ws1.Range["B2:B7"].NumberFormat = "$#,##0"; ws1.Range["E2"].NumberFormat = "$#,##0"; ws1.Range["D1:D3"].Font.Bold = true; ws1.Range["E2:F3"].Font.ThemeColor = ThemeColor.Accent1; ws1.Range["F2:J2"].Merge(); ws1.Range["F3:K3"].Merge(); ws1.Range["E1:E3"].HorizontalAlignment = HorizontalAlignment.Right; ws1.Range["F2:F3"].HorizontalAlignment = HorizontalAlignment.Left; //Add formula //Returns the sales corresponding to "Oranges". ws1.Range["E2"].Formula2 = "=INDEX(B2:B7,MATCH(E1,A2:A7,0))"; //Returns the above result address. ws1.Range["E3"].Formula2 = "=CELL(\"address\",INDEX(B2:B7,MATCH(E1,A2:A7,0)))"; IWorksheet ws2 = workbook.Worksheets.Add(); ws2.Name = "Number formats"; //Set value. ws2.Range["A1:E30"].Value = new object[,] { { "Actual value", "Display value", "Format", "Result"}, { "Numbers", null, null, null}, { 5000.555, 5000.555, "General", null}, { null, 5000.555, "0", null}, { null, 5000.555, "#,##0", null}, { null, 5000.555, "0.00", null}, { null, 5000.555, "#,##0.00", null}, { "Currency", null, null, null}, { -5000.555, -5000.555, "$#,##0_);($#,##0)", null}, { null, -5000.555, "$#,##0_);[Red]($#,##0)", null}, { null, -5000.555, "$#,##0.00_);($#,##0.00)", null}, { null, -5000.555, "$#,##0.00_);[Red]($#,##0.00)", null}, { "Percentage", null, null, null}, { 0.0555, 0.0555, "0%", null}, { null, 0.0555, "0.00%", null}, { "Scientific format", null, null, null}, { 5000.55500055, 5000.55500055, "0.00E+00", null}, { "Fractions", null, null, null}, { 50.555, 50.555, "# ?/?", null}, { "Dates", null, null, null}, { new DateTime(2019, 2, 15), new DateTime(2019, 2, 15), "m/d/yy", null}, { null, new DateTime(2019, 2, 15), "d-mmm-yy", null}, { null, new DateTime(2019, 2, 15), "d-mmm", null}, { null, new DateTime(2019, 2, 15), "mmm-yy", null}, { null, new DateTime(2019, 2, 15), "mm/dd", null}, { "Times", null, null, null}, { new TimeSpan(16, 30, 15), new TimeSpan(16, 30, 15), "h:mm AM/PM", null}, { null, new TimeSpan(16, 30, 15), "h:mm:ss AM/PM", null}, { null, new TimeSpan(16, 30, 15), "h:mm", null}, { null, new TimeSpan(16, 30, 15), "h:mm:ss", null} }; //Set styles. ws2.Range["A1:D1"].Interior.Color = System.Drawing.Color.FromArgb(217, 225, 242); ws2.Range["A2"].Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242); ws2.Range["A8"].Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242); ws2.Range["A13"].Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242); ws2.Range["A16"].Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242); ws2.Range["A18"].Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242); ws2.Range["A20"].Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242); ws2.Range["A26"].Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242); ws2.Columns[0].ColumnWidth = 17; ws2.Columns[1].ColumnWidth = 17; ws2.Columns[2].ColumnWidth = 25; ws2.Columns[3].ColumnWidth = 9; ws2.Range["A1:D1"].Font.Bold = true; ws2.Range["E1:H1"].Merge(); ws2.Range["D3:D30"].Font.Bold = true; //Numbers ws2.Range["A2"].Font.Bold = true; ws2.Range["A2:D2"].Merge(); ws2.Range["A3:A7"].Merge(); ws2.Range["A2"].HorizontalAlignment = HorizontalAlignment.Center; ws2.Range["A3"].VerticalAlignment = VerticalAlignment.Center; ws2.Range["A3:D7"].HorizontalAlignment = HorizontalAlignment.Left; ws2.Range["B3"].NumberFormat = "General"; ws2.Range["B4"].NumberFormat = "0"; ws2.Range["B5"].NumberFormat = "#,##0"; ws2.Range["B6"].NumberFormat = "0.00"; ws2.Range["B7"].NumberFormat = "#,##0.00"; ws2.Range["C3:C7"].NumberFormat = "@"; //Currency ws2.Range["A8"].Font.Bold = true; ws2.Range["A8:D8"].Merge(); ws2.Range["A9:A12"].Merge(); ws2.Range["A8"].HorizontalAlignment = HorizontalAlignment.Center; ws2.Range["A9"].VerticalAlignment = VerticalAlignment.Center; ws2.Range["A9:D12"].HorizontalAlignment = HorizontalAlignment.Left; ws2.Range["B9"].NumberFormat = "$#,##0_);($#,##0)"; ws2.Range["B10"].NumberFormat = "$#,##0_);[Red]($#,##0)"; ws2.Range["B11"].NumberFormat = "$#,##0.00_);($#,##0.00)"; ws2.Range["B12"].NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"; //Percentage ws2.Range["A13"].Font.Bold = true; ws2.Range["A13:D13"].Merge(); ws2.Range["A14:A15"].Merge(); ws2.Range["A13"].HorizontalAlignment = HorizontalAlignment.Center; ws2.Range["A14"].VerticalAlignment = VerticalAlignment.Center; ws2.Range["A14:D15"].HorizontalAlignment = HorizontalAlignment.Left; ws2.Range["B14"].NumberFormat = "0%"; ws2.Range["B15"].NumberFormat = "0.00%"; //Scientific format ws2.Range["A16"].Font.Bold = true; ws2.Range["A16:D16"].Merge(); ws2.Range["A16"].HorizontalAlignment = HorizontalAlignment.Center; ws2.Range["A17:D17"].HorizontalAlignment = HorizontalAlignment.Left; ws2.Range["B17"].NumberFormat = "0.00E+00"; //Fractions ws2.Range["A18"].Font.Bold = true; ws2.Range["A18:D18"].Merge(); ws2.Range["A18"].HorizontalAlignment = HorizontalAlignment.Center; ws2.Range["A19:D19"].HorizontalAlignment = HorizontalAlignment.Left; ws2.Range["B19"].NumberFormat = "# ?/?"; //Dates ws2.Range["A20"].Font.Bold = true; ws2.Range["A20:D20"].Merge(); ws2.Range["A21:A25"].Merge(); ws2.Range["A20"].HorizontalAlignment = HorizontalAlignment.Center; ws2.Range["A21"].VerticalAlignment = VerticalAlignment.Center; ws2.Range["A21:D25"].HorizontalAlignment = HorizontalAlignment.Left; ws2.Range["A21"].NumberFormat = "[$-en-US]mmmm d, yyyy;@"; ws2.Range["B21"].NumberFormat = "m/d/yy"; ws2.Range["B22"].NumberFormat = "d-mmm-yy"; ws2.Range["B23"].NumberFormat = "d-mmm"; ws2.Range["B24"].NumberFormat = "mmm-yy"; ws2.Range["B25"].NumberFormat = "mm/dd"; //Times ws2.Range["A26"].Font.Bold = true; ws2.Range["A26:D26"].Merge(); ws2.Range["A27:A30"].Merge(); ws2.Range["A26"].HorizontalAlignment = HorizontalAlignment.Center; ws2.Range["A27"].VerticalAlignment = VerticalAlignment.Center; ws2.Range["A27:D30"].HorizontalAlignment = HorizontalAlignment.Left; ws2.Range["B27"].NumberFormat = "h:mm AM/PM"; ws2.Range["B28"].NumberFormat = "h:mm:ss AM/PM"; ws2.Range["B29"].NumberFormat = "h:mm"; ws2.Range["B30"].NumberFormat = "h:mm:ss"; //Set formula. ws2.Range["D3:D30"].Formula2 = "=CELL(\"format\",B3)"; // Save to an excel file workbook.Save("CellFunction.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim ws1 As IWorksheet = workbook.Worksheets("Sheet1") ws1.Name = "Lookup result address" 'Set value. ws1.Range("A1:B7").Value = New Object(,) { {"Item", "Sales"}, {"Apples", 100}, {"Bananas", 190}, {"Lemons", 150}, {"Oranges", 130}, {"Grapes", 220}, {"Limes", 200} } ws1.Range("D1").Value = "Item" ws1.Range("D2").Value = "Sales" ws1.Range("D3").Value = "Address" ws1.Range("E1").Value = "Oranges" ws1.Range("F2").Value = "=INDEX(B2:B7,MATCH(E1,A2:A7,0))" ws1.Range("F3").Value = "=CELL(""address"",INDEX(B2:B7,MATCH(E1,A2:A7,0)))" 'Add table. Dim table As ITable = ws1.Tables.Add(ws1.Range("A1:B7"), True) 'Set styles. ws1.Range("B2:B7").NumberFormat = "$#,##0" ws1.Range("E2").NumberFormat = "$#,##0" ws1.Range("D1:D3").Font.Bold = True ws1.Range("E2:F3").Font.ThemeColor = ThemeColor.Accent1 ws1.Range("F2:J2").Merge() ws1.Range("F3:K3").Merge() ws1.Range("E1:E3").HorizontalAlignment = HorizontalAlignment.Right ws1.Range("F2:F3").HorizontalAlignment = HorizontalAlignment.Left 'Add formula 'Returns the sales corresponding to "Oranges". ws1.Range("E2").Formula2 = "=INDEX(B2:B7,MATCH(E1,A2:A7,0))" 'Returns the above result address. ws1.Range("E3").Formula2 = "=CELL(""address"",INDEX(B2:B7,MATCH(E1,A2:A7,0)))" Dim ws2 As IWorksheet = workbook.Worksheets.Add() ws2.Name = "Number formats" 'Set value. ws2.Range("A1:E30").Value = New Object(,) { {"Actual value", "Display value", "Format", "Result"}, {"Numbers", Nothing, Nothing, Nothing}, {5000.555, 5000.555, "General", Nothing}, {Nothing, 5000.555, "0", Nothing}, {Nothing, 5000.555, "#,##0", Nothing}, {Nothing, 5000.555, "0.00", Nothing}, {Nothing, 5000.555, "#,##0.00", Nothing}, {"Currency", Nothing, Nothing, Nothing}, {-5000.555, -5000.555, "$#,##0_);($#,##0)", Nothing}, {Nothing, -5000.555, "$#,##0_);[Red]($#,##0)", Nothing}, {Nothing, -5000.555, "$#,##0.00_);($#,##0.00)", Nothing}, {Nothing, -5000.555, "$#,##0.00_);[Red]($#,##0.00)", Nothing}, {"Percentage", Nothing, Nothing, Nothing}, {0.0555, 0.0555, "0%", Nothing}, {Nothing, 0.0555, "0.00%", Nothing}, {"Scientific format", Nothing, Nothing, Nothing}, {5000.55500055, 5000.55500055, "0.00E+00", Nothing}, {"Fractions", Nothing, Nothing, Nothing}, {50.555, 50.555, "# ?/?", Nothing}, {"Dates", Nothing, Nothing, Nothing}, {New DateTime(2019, 2, 15), New DateTime(2019, 2, 15), "m/d/yy", Nothing}, {Nothing, New DateTime(2019, 2, 15), "d-mmm-yy", Nothing}, {Nothing, New DateTime(2019, 2, 15), "d-mmm", Nothing}, {Nothing, New DateTime(2019, 2, 15), "mmm-yy", Nothing}, {Nothing, New DateTime(2019, 2, 15), "mm/dd", Nothing}, {"Times", Nothing, Nothing, Nothing}, {New TimeSpan(16, 30, 15), New TimeSpan(16, 30, 15), "h:mm AM/PM", Nothing}, {Nothing, New TimeSpan(16, 30, 15), "h:mm:ss AM/PM", Nothing}, {Nothing, New TimeSpan(16, 30, 15), "h:mm", Nothing}, {Nothing, New TimeSpan(16, 30, 15), "h:mm:ss", Nothing} } 'Set styles. ws2.Range("A1:D1").Interior.Color = System.Drawing.Color.FromArgb(217, 225, 242) ws2.Range("A2").Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242) ws2.Range("A8").Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242) ws2.Range("A13").Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242) ws2.Range("A16").Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242) ws2.Range("A18").Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242) ws2.Range("A20").Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242) ws2.Range("A26").Interior.Color = System.Drawing.Color.FromArgb(242, 242, 242) ws2.Columns(0).ColumnWidth = 17 ws2.Columns(1).ColumnWidth = 17 ws2.Columns(2).ColumnWidth = 25 ws2.Columns(3).ColumnWidth = 9 ws2.Range("A1:D1").Font.Bold = True ws2.Range("E1:H1").Merge() ws2.Range("D3:D30").Font.Bold = True 'Numbers ws2.Range("A2").Font.Bold = True ws2.Range("A2:D2").Merge() ws2.Range("A3:A7").Merge() ws2.Range("A2").HorizontalAlignment = HorizontalAlignment.Center ws2.Range("A3").VerticalAlignment = VerticalAlignment.Center ws2.Range("A3:D7").HorizontalAlignment = HorizontalAlignment.Left ws2.Range("B3").NumberFormat = "General" ws2.Range("B4").NumberFormat = "0" ws2.Range("B5").NumberFormat = "#,##0" ws2.Range("B6").NumberFormat = "0.00" ws2.Range("B7").NumberFormat = "#,##0.00" ws2.Range("C3:C7").NumberFormat = "@" 'Currency ws2.Range("A8").Font.Bold = True ws2.Range("A8:D8").Merge() ws2.Range("A9:A12").Merge() ws2.Range("A8").HorizontalAlignment = HorizontalAlignment.Center ws2.Range("A9").VerticalAlignment = VerticalAlignment.Center ws2.Range("A9:D12").HorizontalAlignment = HorizontalAlignment.Left ws2.Range("B9").NumberFormat = "$#,##0_);($#,##0)" ws2.Range("B10").NumberFormat = "$#,##0_);[Red]($#,##0)" ws2.Range("B11").NumberFormat = "$#,##0.00_);($#,##0.00)" ws2.Range("B12").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" 'Percentage ws2.Range("A13").Font.Bold = True ws2.Range("A13:D13").Merge() ws2.Range("A14:A15").Merge() ws2.Range("A13").HorizontalAlignment = HorizontalAlignment.Center ws2.Range("A14").VerticalAlignment = VerticalAlignment.Center ws2.Range("A14:D15").HorizontalAlignment = HorizontalAlignment.Left ws2.Range("B14").NumberFormat = "0%" ws2.Range("B15").NumberFormat = "0.00%" 'Scientific format ws2.Range("A16").Font.Bold = True ws2.Range("A16:D16").Merge() ws2.Range("A16").HorizontalAlignment = HorizontalAlignment.Center ws2.Range("A17:D17").HorizontalAlignment = HorizontalAlignment.Left ws2.Range("B17").NumberFormat = "0.00E+00" 'Fractions ws2.Range("A18").Font.Bold = True ws2.Range("A18:D18").Merge() ws2.Range("A18").HorizontalAlignment = HorizontalAlignment.Center ws2.Range("A19:D19").HorizontalAlignment = HorizontalAlignment.Left ws2.Range("B19").NumberFormat = "# ?/?" 'Dates ws2.Range("A20").Font.Bold = True ws2.Range("A20:D20").Merge() ws2.Range("A21:A25").Merge() ws2.Range("A20").HorizontalAlignment = HorizontalAlignment.Center ws2.Range("A21").VerticalAlignment = VerticalAlignment.Center ws2.Range("A21:D25").HorizontalAlignment = HorizontalAlignment.Left ws2.Range("A21").NumberFormat = "[$-en-US]mmmm d, yyyy;@" ws2.Range("B21").NumberFormat = "m/d/yy" ws2.Range("B22").NumberFormat = "d-mmm-yy" ws2.Range("B23").NumberFormat = "d-mmm" ws2.Range("B24").NumberFormat = "mmm-yy" ws2.Range("B25").NumberFormat = "mm/dd" 'Times ws2.Range("A26").Font.Bold = True ws2.Range("A26:D26").Merge() ws2.Range("A27:A30").Merge() ws2.Range("A26").HorizontalAlignment = HorizontalAlignment.Center ws2.Range("A27").VerticalAlignment = VerticalAlignment.Center ws2.Range("A27:D30").HorizontalAlignment = HorizontalAlignment.Left ws2.Range("B27").NumberFormat = "h:mm AM/PM" ws2.Range("B28").NumberFormat = "h:mm:ss AM/PM" ws2.Range("B29").NumberFormat = "h:mm" ws2.Range("B30").NumberFormat = "h:mm:ss" 'Set formula ws2.Range("D3:D30").Formula2 = "=CELL(""format"",B3)" ' save to an excel file workbook.Save("CellFunction.xlsx")