// 新規ワークブックの作成 var workbook = new GrapeCity.Documents.Excel.Workbook(); IWorksheet ws1 = workbook.Worksheets["Sheet1"]; ws1.Name = "アドレスの検索結果"; // 値を設定 ws1.Range["A1:B7"].Value = new object[,] { { "品目", "売上"}, { "りんご", 10000}, { "バナナ", 19000}, { "レモン", 15000}, { "オレンジ", 13000}, { "グレープ", 22000}, { "ライム", 20000} }; ws1.Range["D1"].Value = "品目"; ws1.Range["D2"].Value = "売上"; ws1.Range["D3"].Value = "アドレス"; ws1.Range["E1"].Value = "オレンジ"; 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)))"; // テーブルを追加 ITable table = ws1.Tables.Add(ws1.Range["A1:B7"], true); // スタイルを設定 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; // 数式を設定 // 「オレンジ」に該当する売上を返す数式 ws1.Range["E2"].Formula2 = "=INDEX(B2:B7,MATCH(E1,A2:A7,0))"; // 上記数式の結果のセルアドレスを返す数式 ws1.Range["E3"].Formula2 = "=CELL(\"address\",INDEX(B2:B7,MATCH(E1,A2:A7,0)))"; IWorksheet ws2 = workbook.Worksheets.Add(); ws2.Name = "数値の表示形式"; // 値を設定 ws2.Range["A1:E30"].Value = new object[,] { { "実際の値", "表示される値", "表示形式", "関数の結果"}, { "数値", 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}, { "通貨", 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}, { "パーセンテージ", null, null, null}, { 0.0555, 0.0555, "0%", null}, { null, 0.0555, "0.00%", null}, { "指数", null, null, null}, { 5000.55500055, 5000.55500055, "0.00E+00", null}, { "分数", null, null, null}, { 50.555, 50.555, "# ?/?", null}, { "日付", null, null, null}, { new DateTime(2019, 2, 15), new DateTime(2019, 2, 15), "yy/m/d", null}, { null, new DateTime(2019, 2, 15), "yyyy\"年\"mm\"月\"dd\"日\";@", null}, { null, new DateTime(2019, 2, 15), "m\"月\"d\"日\";@", null}, { null, new DateTime(2019, 2, 15), "yy\"年\"m\"月\";@", null}, { null, new DateTime(2019, 2, 15), "mm/dd", null}, { "時刻", 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} }; // スタイルを設定 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; // 数値 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 = "@"; // 通貨 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)"; // パーセンテージ 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%"; // 指数 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"; // 分数 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 = "# ?/?"; // 日付 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["B21"].NumberFormat = "yy/m/d"; ws2.Range["B22"].NumberFormat = "yyyy\"年\"mm\"月\"dd\"日\";@"; ws2.Range["B23"].NumberFormat = "m\"月\"d\"日\";@"; ws2.Range["B24"].NumberFormat = "yy\"年\"m\"月\";@"; ws2.Range["B25"].NumberFormat = "mm/dd"; // 時刻 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"; // 数式を設定 ws2.Range["D3:D30"].Formula2 = "=CELL(\"format\",B3)"; // xlsx ファイルに保存 workbook.Save("CellFunction.xlsx");
' 新規ワークブックの作成 Dim workbook As New Workbook Dim ws1 As IWorksheet = workbook.Worksheets("Sheet1") ws1.Name = "アドレスの検索結果" ' 値を設定 ws1.Range("A1:B7").Value = New Object(,) { {"品目", "売上"}, {"りんご", 10000}, {"バナナ", 19000}, {"レモン", 15000}, {"オレンジ", 13000}, {"グレープ", 22000}, {"ライム", 20000} } ws1.Range("D1").Value = "品目" ws1.Range("D2").Value = "売上" ws1.Range("D3").Value = "アドレス" ws1.Range("E1").Value = "オレンジ" 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)))" ' テーブルを追加 Dim table As ITable = ws1.Tables.Add(ws1.Range("A1:B7"), True) ' スタイルを設定 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 ' 数式を設定 ' 「オレンジ」に該当する売上を返す数式 ws1.Range("E2").Formula2 = "=INDEX(B2:B7,MATCH(E1,A2:A7,0))" ' 上記数式の結果のセルアドレスを返す数式 ws1.Range("E3").Formula2 = "=CELL(""address"",INDEX(B2:B7,MATCH(E1,A2:A7,0)))" Dim ws2 As IWorksheet = workbook.Worksheets.Add() ws2.Name = "数値の表示形式" ' 値を設定 ws2.Range("A1:E30").Value = New Object(,) { {"実際の値", "表示される値", "表示形式", "関数の結果"}, {"数値", 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}, {"通貨", 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}, {"パーセンテージ", Nothing, Nothing, Nothing}, {0.0555, 0.0555, "0%", Nothing}, {Nothing, 0.0555, "0.00%", Nothing}, {"指数", Nothing, Nothing, Nothing}, {5000.55500055, 5000.55500055, "0.00E+00", Nothing}, {"分数", Nothing, Nothing, Nothing}, {50.555, 50.555, "# ?/?", Nothing}, {"日付", Nothing, Nothing, Nothing}, {New DateTime(2019, 2, 15), New DateTime(2019, 2, 15), "yy/m/d", Nothing}, {Nothing, New DateTime(2019, 2, 15), "yyyy""年""mm""月""dd""日"";@", Nothing}, {Nothing, New DateTime(2019, 2, 15), "m""月""d""日"";@", Nothing}, {Nothing, New DateTime(2019, 2, 15), "yy""年""m""月"";@", Nothing}, {Nothing, New DateTime(2019, 2, 15), "mm/dd", Nothing}, {"時刻", 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} } ' スタイルを設定 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 ' 数値 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 = "@" ' 通貨 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)" ' パーセンテージ 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%" ' 指数 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" ' 分数 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 = "# ?/?" ' 日付 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("B21").NumberFormat = "yy/m/d" ws2.Range("B22").NumberFormat = "yyyy""年""mm""月""dd""日"";@" ws2.Range("B23").NumberFormat = "m""月""d""日"";@" ws2.Range("B24").NumberFormat = "yy""年""m""月"";@" ws2.Range("B25").NumberFormat = "mm/dd" ' 時刻 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" ' 数式を設定 ws2.Range("D3:D30").Formula2 = "=CELL(""format"",B3)" ' xlsx ファイルに保存 workbook.Save("CellFunction.xlsx")