//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); //Load template file Template_Score.xlsx from resource 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 //Invoke to process the template workbook.ProcessTemplate(); workbook.Worksheets[0].Range["A:F"].ColumnWidth = 16; // Save to an excel file workbook.Save("SliceFilter.xlsx");
' Create a new Workbook Dim workbook As New Workbook 'Load template file Template_Score.xlsx from resource 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 'Invoke To process the template workbook.ProcessTemplate() workbook.Worksheets(0).Range("A:F").ColumnWidth = 16 ' save to an excel file workbook.Save("SliceFilter.xlsx")
