// 新規ワークブックの作成 var workbook = new GrapeCity.Documents.Excel.Workbook(); // 帳票テンプレートを読み込む var templateFile = GetResourceStream("xlsx\\SliceFilter.xlsx"); workbook.Open(templateFile); #region table order { var datasource = new DataTable(); datasource.Columns.Add(new DataColumn("oid", typeof(string))); datasource.Columns.Add(new DataColumn("cid", typeof(string))); datasource.Columns.Add(new DataColumn("pid", typeof(string))); datasource.Columns.Add(new DataColumn("count", typeof(double))); datasource.Rows.Add("TF00001", "C001", "R001", 1); datasource.Rows.Add("TF00002", "C001", "T002", 1); datasource.Rows.Add("TF00003", "C001", "W003", 1); datasource.Rows.Add("TF00004", "C001", "C004", 1); datasource.Rows.Add("TF00005", "C001", "O005", 1); datasource.Rows.Add("TF00006", "C002", "R001", 1); datasource.Rows.Add("TF00007", "C002", "T002", 1); datasource.Rows.Add("TF00008", "C002", "W003", 1); datasource.Rows.Add("TF00009", "C002", "C004", 1); datasource.Rows.Add("TF00010", "C002", "O005", 1); datasource.Rows.Add("TF00011", "C003", "W009", 5); datasource.Rows.Add("TF00012", "C003", "R001", 2); datasource.Rows.Add("TF00013", "C003", "T002", 1); datasource.Rows.Add("TF00014", "C003", "W003", 3); datasource.Rows.Add("TF00015", "C004", "L010", 10); datasource.Rows.Add("TF00016", "C005", "B008", 999); datasource.Rows.Add("TF00017", "C006", "C007", 23); datasource.Rows.Add("TF00018", "C007", "N011", 1); datasource.Rows.Add("TF00019", "C007", "G012", 10); datasource.Rows.Add("TF00020", "C008", "P013", 10); datasource.Rows.Add("TF00021", "C008", "P014", 15); datasource.Rows.Add("TF00022", "C008", "S015", 2); workbook.AddDataSource("order", datasource); } #endregion #region table customer { var datasource = new DataTable(); datasource.Columns.Add(new DataColumn("cid", typeof(string))); datasource.Columns.Add(new DataColumn("name", typeof(string))); datasource.Columns.Add(new DataColumn("country", typeof(string))); datasource.Columns.Add(new DataColumn("age", typeof(double))); datasource.Rows.Add("C001", "Tom", "USA", 4); datasource.Rows.Add("C002", "Jerry", "USA", 2); datasource.Rows.Add("C003", "Mario", "JP", 7); datasource.Rows.Add("C004", "Luigi", "JP", 8); datasource.Rows.Add("C005", "Jolyne", "USA", 19); datasource.Rows.Add("C006", "Enrico", "USA", 54); datasource.Rows.Add("C007", "Kira", "JP", 33); datasource.Rows.Add("C008", "Rohan", "JP", 20); workbook.AddDataSource("customer", datasource); } #endregion #region table product { var datasource = new DataTable(); datasource.Columns.Add(new DataColumn("pid", typeof(string))); datasource.Columns.Add(new DataColumn("name", typeof(string))); datasource.Columns.Add(new DataColumn("unitprice", typeof(double))); datasource.Rows.Add("R001", "Refrigerator", 3299.9); datasource.Rows.Add("T002", "Television", 899); datasource.Rows.Add("W003", "Washer", 599.9); datasource.Rows.Add("C004", "Computer", 5999.9); datasource.Rows.Add("O005", "Oven", 1388.8); datasource.Rows.Add("D006", "Dishwasher", 2399.9); datasource.Rows.Add("C007", "CD", 13); datasource.Rows.Add("B008", "Blue frog", 1); datasource.Rows.Add("W009", "Wrench", 19.9); datasource.Rows.Add("L010", "Lantern", 45); datasource.Rows.Add("N011", "Nail clippers", 2.99); datasource.Rows.Add("G012", "Gloves", 3.99); datasource.Rows.Add("P013", "Pigment", 99); datasource.Rows.Add("P014", "Pencil", 6.6); datasource.Rows.Add("S015", "Sketchbook", 23.5); workbook.AddDataSource("product", datasource); } #endregion // 帳票を作成 workbook.ProcessTemplate(); workbook.Worksheets[0].Range["A:F"].ColumnWidth = 16; // xlsx ファイルに保存 workbook.Save("SliceFilter.xlsx");
' 新規ワークブックの作成 Dim workbook As New Workbook ' 帳票テンプレートを読み込む Dim templateFile = GetResourceStream("xlsx\SliceFilter.xlsx") workbook.Open(templateFile) #Region "table order " Dim order As New DataTable order.Columns.Add(New DataColumn("oid", GetType(String))) order.Columns.Add(New DataColumn("cid", GetType(String))) order.Columns.Add(New DataColumn("pid", GetType(String))) order.Columns.Add(New DataColumn("count", GetType(Double))) order.Rows.Add("TF00001", "C001", "R001", 1) order.Rows.Add("TF00002", "C001", "T002", 1) order.Rows.Add("TF00003", "C001", "W003", 1) order.Rows.Add("TF00004", "C001", "C004", 1) order.Rows.Add("TF00005", "C001", "O005", 1) order.Rows.Add("TF00006", "C002", "R001", 1) order.Rows.Add("TF00007", "C002", "T002", 1) order.Rows.Add("TF00008", "C002", "W003", 1) order.Rows.Add("TF00009", "C002", "C004", 1) order.Rows.Add("TF00010", "C002", "O005", 1) order.Rows.Add("TF00011", "C003", "W009", 5) order.Rows.Add("TF00012", "C003", "R001", 2) order.Rows.Add("TF00013", "C003", "T002", 1) order.Rows.Add("TF00014", "C003", "W003", 3) order.Rows.Add("TF00015", "C004", "L010", 10) order.Rows.Add("TF00016", "C005", "B008", 999) order.Rows.Add("TF00017", "C006", "C007", 23) order.Rows.Add("TF00018", "C007", "N011", 1) order.Rows.Add("TF00019", "C007", "G012", 10) order.Rows.Add("TF00020", "C008", "P013", 10) order.Rows.Add("TF00021", "C008", "P014", 15) order.Rows.Add("TF00022", "C008", "S015", 2) workbook.AddDataSource("order", order) #End Region #Region "table customer " Dim customer As New DataTable customer.Columns.Add(New DataColumn("cid", GetType(String))) customer.Columns.Add(New DataColumn("name", GetType(String))) customer.Columns.Add(New DataColumn("country", GetType(String))) customer.Columns.Add(New DataColumn("age", GetType(Double))) customer.Rows.Add("C001", "Tom", "USA", 4) customer.Rows.Add("C002", "Jerry", "USA", 2) customer.Rows.Add("C003", "Mario", "JP", 7) customer.Rows.Add("C004", "Luigi", "JP", 8) customer.Rows.Add("C005", "Jolyne", "USA", 19) customer.Rows.Add("C006", "Enrico", "USA", 54) customer.Rows.Add("C007", "Kira", "JP", 33) customer.Rows.Add("C008", "Rohan", "JP", 20) workbook.AddDataSource("customer", customer) #End Region #Region "table product " Dim product As New DataTable product.Columns.Add(New DataColumn("pid", GetType(String))) product.Columns.Add(New DataColumn("name", GetType(String))) product.Columns.Add(New DataColumn("unitprice", GetType(Double))) product.Rows.Add("R001", "Refrigerator", 3299.9) product.Rows.Add("T002", "Television", 899) product.Rows.Add("W003", "Washer", 599.9) product.Rows.Add("C004", "Computer", 5999.9) product.Rows.Add("O005", "Oven", 1388.8) product.Rows.Add("D006", "Dishwasher", 2399.9) product.Rows.Add("C007", "CD", 13) product.Rows.Add("B008", "Blue frog", 1) product.Rows.Add("W009", "Wrench", 19.9) product.Rows.Add("L010", "Lantern", 45) product.Rows.Add("N011", "Nail clippers", 2.99) product.Rows.Add("G012", "Gloves", 3.99) product.Rows.Add("P013", "Pigment", 99) product.Rows.Add("P014", "Pencil", 6.6) product.Rows.Add("S015", "Sketchbook", 23.5) workbook.AddDataSource("product", product) #End Region ' 帳票を作成 workbook.ProcessTemplate() workbook.Worksheets(0).Range("A:F").ColumnWidth = 16 ' xlsx ファイルに保存 workbook.Save("SliceFilter.xlsx")