小計の作成

次のコードは、ブックのセルを書式設定する例を示します。

［表示］→［コード］を選択し、次のステートメントをフォームの先頭に追加します。

VisualBasic
C#

	コードのコピー
Import C1.WPF.Excel

	コードのコピー
using C1.WPF.Excel;

C1.WPF.Excel.4.dll/C1.Silverlight.Excel.5.dll への参照を追加し、C1XLBook を作成します。

C#	コードのコピー
// 新しいワークブックを作成します C1XLBook book = new C1XLBook();

Window_Loaded イベントに次のコードを追加します。

VisualBasic
C#

	コードのコピー
Private Sub Window_Loaded(sender As Object, e As EventArgs) Dim book As New C1XLBook() Dim sheet As XLSheet = book.Sheets(0) Dim totalStyle As New XLStyle(book) totalStyle.Font = New XLFont("Arial", 10, true, false) sheet(2, 1).Value = "計算" sheet(2, 2).Value = "ID" sheet(3, 1).Value = 12 sheet(3, 2).Value = 17 sheet.Rows(3).OutlineLevel = 2 sheet.Rows(3).Visible = False sheet(4, 1).Value = 12 sheet(4, 2).Value = 14 sheet.Rows(4).OutlineLevel = 2 sheet.Rows(4).Visible = False sheet(5, 1).Value = "12 小計" sheet(5, 1).Style = totalStyle sheet(5, 2).Value = 31 sheet(5, 2).Formula = "SUBTOTAL(9,C4:C5)" sheet.Rows(5).OutlineLevel = 1 sheet(6, 1).Value = 34 sheet(6, 2).Value = 109 sheet.Rows(6).OutlineLevel = 2 sheet(7, 1).Value = "34 小計" sheet(7, 1).Style = totalStyle sheet(7, 2).Value = 109 sheet(7, 2).Formula = "SUBTOTAL(9,C7:C7)" sheet.Rows(7).OutlineLevel = 1 sheet(8, 1).Value = "合計" sheet(8, 1).Style = totalStyle sheet(8, 2).Value = 140 sheet(8, 2).Formula = "SUBTOTAL(9,C4:C7)" sheet.Rows(8).OutlineLevel = 0 book.Save("C:\C1Excel\mybook.xlsx") System.Diagnostics.Process.Start("C:\C1Excel\mybook.xlsx") End Sub

コードのコピー

Private Sub Window_Loaded(sender As Object, e As EventArgs)
     Dim book As New C1XLBook()
     Dim sheet As XLSheet = book.Sheets(0)
     Dim totalStyle As New XLStyle(book)
     totalStyle.Font = New XLFont("Arial", 10, true, false)
     sheet(2, 1).Value = "計算"
     sheet(2, 2).Value = "ID"
     sheet(3, 1).Value = 12
     sheet(3, 2).Value = 17
     sheet.Rows(3).OutlineLevel = 2
     sheet.Rows(3).Visible = False
     sheet(4, 1).Value = 12
     sheet(4, 2).Value = 14
     sheet.Rows(4).OutlineLevel = 2
     sheet.Rows(4).Visible = False
     sheet(5, 1).Value = "12 小計"
     sheet(5, 1).Style = totalStyle
     sheet(5, 2).Value = 31
     sheet(5, 2).Formula = "SUBTOTAL(9,C4:C5)"
     sheet.Rows(5).OutlineLevel = 1
     sheet(6, 1).Value = 34
     sheet(6, 2).Value = 109
     sheet.Rows(6).OutlineLevel = 2
     sheet(7, 1).Value = "34 小計"
     sheet(7, 1).Style = totalStyle
     sheet(7, 2).Value = 109
     sheet(7, 2).Formula = "SUBTOTAL(9,C7:C7)"
     sheet.Rows(7).OutlineLevel = 1
     sheet(8, 1).Value = "合計"
     sheet(8, 1).Style = totalStyle
     sheet(8, 2).Value = 140
     sheet(8, 2).Formula = "SUBTOTAL(9,C4:C7)"
     sheet.Rows(8).OutlineLevel = 0
     book.Save("C:\C1Excel\mybook.xlsx")
     System.Diagnostics.Process.Start("C:\C1Excel\mybook.xlsx")
End Sub

	コードのコピー
private void Window_Loaded(object sender, EventArgs e) { C1XLBook book = new C1XLBook(); XLSheet sheet = book.Sheets[0]; XLStyle totalStyle = new XLStyle(book); totalStyle.Font = new XLFont("Arial", 10, true, false); sheet[2, 1].Value = "計算"; sheet[2, 2].Value = "ID"; sheet[3, 1].Value = 12; sheet[3, 2].Value = 17; sheet.Rows[3].OutlineLevel = 2; sheet.Rows[3].Visible = false; sheet[4, 1].Value = 12; sheet[4, 2].Value = 14; sheet.Rows[4].OutlineLevel = 2; sheet.Rows[4].Visible = false; sheet[5, 1].Value = "12 小計"; sheet[5, 1].Style = totalStyle; sheet[5, 2].Value = 31; sheet[5, 2].Formula = "SUBTOTAL(9,C4:C5)"; sheet.Rows[5].OutlineLevel = 1; sheet[6, 1].Value = 34; sheet[6, 2].Value = 109; sheet.Rows[6].OutlineLevel = 2; sheet[7, 1].Value = "34 小計"; sheet[7, 1].Style = totalStyle; sheet[7, 2].Value = 109; sheet[7, 2].Formula = "SUBTOTAL(9,C7:C7)"; sheet.Rows[7].OutlineLevel = 1; sheet[8, 1].Value = "合計"; sheet[8, 1].Style = totalStyle; sheet[8, 2].Value = 140; sheet[8, 2].Formula = "SUBTOTAL(9,C4:C7)"; sheet.Rows[8].OutlineLevel = 0; book.Save(@"C:\C1Excel\mybook.xlsx"); System.Diagnostics.Process.Start(@"C:\C1Excel\mybook.xlsx"); }

コードのコピー

private void Window_Loaded(object sender, EventArgs e)
        {
            C1XLBook book = new C1XLBook();
            XLSheet sheet = book.Sheets[0];
            XLStyle totalStyle = new XLStyle(book);
            totalStyle.Font = new XLFont("Arial", 10, true, false);
            sheet[2, 1].Value = "計算";
            sheet[2, 2].Value = "ID";
            sheet[3, 1].Value = 12;
            sheet[3, 2].Value = 17;
            sheet.Rows[3].OutlineLevel = 2;
            sheet.Rows[3].Visible = false;
            sheet[4, 1].Value = 12;
            sheet[4, 2].Value = 14;
            sheet.Rows[4].OutlineLevel = 2;
            sheet.Rows[4].Visible = false;
            sheet[5, 1].Value = "12 小計";
            sheet[5, 1].Style = totalStyle;
            sheet[5, 2].Value = 31;
            sheet[5, 2].Formula = "SUBTOTAL(9,C4:C5)";
            sheet.Rows[5].OutlineLevel = 1;
            sheet[6, 1].Value = 34;
            sheet[6, 2].Value = 109;
            sheet.Rows[6].OutlineLevel = 2;
            sheet[7, 1].Value = "34 小計";
            sheet[7, 1].Style = totalStyle;
            sheet[7, 2].Value = 109;
            sheet[7, 2].Formula = "SUBTOTAL(9,C7:C7)";
            sheet.Rows[7].OutlineLevel = 1;
            sheet[8, 1].Value = "合計";
            sheet[8, 1].Style = totalStyle;
            sheet[8, 2].Value = 140;
            sheet[8, 2].Formula = "SUBTOTAL(9,C4:C7)";
            sheet.Rows[8].OutlineLevel = 0;
            book.Save(@"C:\C1Excel\mybook.xlsx");
            System.Diagnostics.Process.Start(@"C:\C1Excel\mybook.xlsx");
        }

プログラムを実行します。スプレッドシートが開かれて、次のように表示されます。
SUBTOTAL 関数は、指定された行の合計を取得します。