//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); //Load template file from resource var templateFile = this.GetResourceStream("xlsx\\Template_SalesDataGroup_DataSet.xlsx"); workbook.Open(templateFile); #region Init Data var table1 = new System.Data.DataTable(); table1.Columns.Add(new DataColumn("Area", typeof(string))); table1.Columns.Add(new DataColumn("City", typeof(string))); table1.Columns.Add(new DataColumn("Category", typeof(string))); table1.Columns.Add(new DataColumn("Name", typeof(string))); table1.Columns.Add(new DataColumn("Revenue", typeof(double))); table1.Rows.Add("North America", "Chicago", "Consumer Electronics", "Bose 785593-0050", 92800); table1.Rows.Add("North America", "New York", "Consumer Electronics", "Bose 785593-0050", 92800); table1.Rows.Add("South America", "Santiago", "Consumer Electronics", "Bose 785593-0050", 19550); table1.Rows.Add("North America", "Chicago", "Consumer Electronics", "Canon EOS 1500D", 98650); table1.Rows.Add("North America", "Minnesota", "Consumer Electronics", "Canon EOS 1500D", 89110); table1.Rows.Add("South America", "Santiago", "Consumer Electronics", "Canon EOS 1500D", 459000); table1.Rows.Add("North America", "Chicago", "Consumer Electronics", "Haier 394L 4Star", 367050); table1.Rows.Add("South America", "Quito", "Consumer Electronics", "Haier 394L 4Star", 729100); table1.Rows.Add("South America", "Santiago", "Consumer Electronics", "Haier 394L 4Star", 578900); table1.Rows.Add("North America", "Fremont", "Consumer Electronics", "IFB 6.5 Kg FullyAuto", 904930); table1.Rows.Add("South America", "Buenos Aires", "Consumer Electronics", "IFB 6.5 Kg FullyAuto", 673800); table1.Rows.Add("South America", "Medillin", "Consumer Electronics", "IFB 6.5 Kg FullyAuto", 82910); table1.Rows.Add("North America", "Chicago", "Consumer Electronics", "Mi LED 40inch", 550010); table1.Rows.Add("North America", "Minnesota", "Consumer Electronics", "Mi LED 40inch", 1784702); table1.Rows.Add("South America", "Santiago", "Consumer Electronics", "Mi LED 40inch", 102905); table1.Rows.Add("North America", "Chicago", "Consumer Electronics", "Sennheiser HD 4.40-BT", 178100); table1.Rows.Add("South America", "Quito", "Consumer Electronics", "Sennheiser HD 4.40-BT", 234459); var table2 = new System.Data.DataTable(); table2.Columns.Add(new DataColumn("Area", typeof(string))); table2.Columns.Add(new DataColumn("City", typeof(string))); table2.Columns.Add(new DataColumn("Category", typeof(string))); table2.Columns.Add(new DataColumn("Name", typeof(string))); table2.Columns.Add(new DataColumn("Revenue", typeof(double))); table2.Rows.Add("North America", "Minnesota", "Mobile", "Iphone XR", 1734621); table2.Rows.Add("South America", "Santiago", "Mobile", "Iphone XR", 109300); table2.Rows.Add("North America", "Chicago", "Mobile", "OnePlus 7Pro", 499100); table2.Rows.Add("South America", "Quito", "Mobile", "OnePlus 7Pro", 215000); table2.Rows.Add("North America", "Minnesota", "Mobile", "Redmi 7", 81650); table2.Rows.Add("South America", "Quito", "Mobile", "Redmi 7", 276390); table2.Rows.Add("North America", "Minnesota", "Mobile", "Samsung S9", 896250); table2.Rows.Add("South America", "Buenos Aires", "Mobile", "Samsung S9", 896250); table2.Rows.Add("South America", "Quito", "Mobile", "Samsung S9", 716520); var datasource = new System.Data.DataSet(); datasource.Tables.Add(table1); datasource.Tables.Add(table2); #endregion //Init template global settings workbook.Names.Add("TemplateOptions.KeepLineSize", "true"); //Add data source workbook.AddDataSource("ds", datasource); //Invoke to process the template workbook.ProcessTemplate(); // Save to an excel file workbook.Save("DataSet.xlsx");
' Create a new Workbook Dim workbook As New Workbook 'Load template file from resource Dim templateFile = GetResourceStream("xlsx\Template_SalesDataGroup_DataSet.xlsx") workbook.Open(templateFile) #Region "Init Data" Dim table1 = New Data.DataTable With table1.Columns .Add(New DataColumn("Area", GetType(String))) .Add(New DataColumn("City", GetType(String))) .Add(New DataColumn("Category", GetType(String))) .Add(New DataColumn("Name", GetType(String))) .Add(New DataColumn("Revenue", GetType(Double))) End With With table1.Rows .Add("North America", "Chicago", "Consumer Electronics", "Bose 785593-0050", 92800) .Add("North America", "New York", "Consumer Electronics", "Bose 785593-0050", 92800) .Add("South America", "Santiago", "Consumer Electronics", "Bose 785593-0050", 19550) .Add("North America", "Chicago", "Consumer Electronics", "Canon EOS 1500D", 98650) .Add("North America", "Minnesota", "Consumer Electronics", "Canon EOS 1500D", 89110) .Add("South America", "Santiago", "Consumer Electronics", "Canon EOS 1500D", 459000) .Add("North America", "Chicago", "Consumer Electronics", "Haier 394L 4Star", 367050) .Add("South America", "Quito", "Consumer Electronics", "Haier 394L 4Star", 729100) .Add("South America", "Santiago", "Consumer Electronics", "Haier 394L 4Star", 578900) .Add("North America", "Fremont", "Consumer Electronics", "IFB 6.5 Kg FullyAuto", 904930) .Add("South America", "Buenos Aires", "Consumer Electronics", "IFB 6.5 Kg FullyAuto", 673800) .Add("South America", "Medillin", "Consumer Electronics", "IFB 6.5 Kg FullyAuto", 82910) .Add("North America", "Chicago", "Consumer Electronics", "Mi LED 40inch", 550010) .Add("North America", "Minnesota", "Consumer Electronics", "Mi LED 40inch", 1784702) .Add("South America", "Santiago", "Consumer Electronics", "Mi LED 40inch", 102905) .Add("North America", "Chicago", "Consumer Electronics", "Sennheiser HD 4.40-BT", 178100) .Add("South America", "Quito", "Consumer Electronics", "Sennheiser HD 4.40-BT", 234459) End With Dim table2 = New Data.DataTable() With table2.Columns .Add(New DataColumn("Area", GetType(String))) .Add(New DataColumn("City", GetType(String))) .Add(New DataColumn("Category", GetType(String))) .Add(New DataColumn("Name", GetType(String))) .Add(New DataColumn("Revenue", GetType(Double))) End With With table2.Rows .Add("North America", "Minnesota", "Mobile", "Iphone XR", 1734621) .Add("South America", "Santiago", "Mobile", "Iphone XR", 109300) .Add("North America", "Chicago", "Mobile", "OnePlus 7Pro", 499100) .Add("South America", "Quito", "Mobile", "OnePlus 7Pro", 215000) .Add("North America", "Minnesota", "Mobile", "Redmi 7", 81650) .Add("South America", "Quito", "Mobile", "Redmi 7", 276390) .Add("North America", "Minnesota", "Mobile", "Samsung S9", 896250) .Add("South America", "Buenos Aires", "Mobile", "Samsung S9", 896250) .Add("South America", "Quito", "Mobile", "Samsung S9", 716520) End With Dim datasource = New Data.DataSet() datasource.Tables.Add(table1) datasource.Tables.Add(table2) #End Region 'Init template global settings workbook.Names.Add("TemplateOptions.KeepLineSize", "true") 'Add data source workbook.AddDataSource("ds", datasource) 'Invoke to process the template workbook.ProcessTemplate() ' save to an excel file workbook.Save("DataSet.xlsx")