//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); //Load template file from resource var templateFile = this.GetResourceStream("xlsx\\Template_SalesDataGroup_DataTable.xlsx"); workbook.Open(templateFile); #region Init Data Console.WriteLine("Creating test data."); var datasource = new System.Data.DataTable(); datasource.Columns.Add(new DataColumn("Area", typeof(string))); datasource.Columns.Add(new DataColumn("City", typeof(string))); datasource.Columns.Add(new DataColumn("Category", typeof(string))); datasource.Columns.Add(new DataColumn("Name", typeof(string))); datasource.Columns.Add(new DataColumn("Revenue", typeof(double))); var areas = new[] { "North America", "South America" }; var cities = new[] { "Chicago", "New York", "Santiago", "Quito", "Fremont", "Buenos Aires", "Medillin", "Minnesota" }; var categories = new[] { "Consumer Electronics", "Mobile" }; var category1NamePrefixes = new[] { "Bose ", "Canon ", "Haier ", "IFB ", "Mi ", "Sennheiser " }; var category2NamePrefixes = new[] { "iPhone ", "OnePlus ", "Redmi ", "Samsung " }; Random rand = new Random(); var rows = datasource.Rows; // You can increase the loop count if the demo is too fast on your computer. for (var i = 0; i < 30000; i++) { var area = areas[rand.Next(0, areas.Length)]; var city = cities[rand.Next(0, cities.Length)]; var categoryId = rand.Next(0, categories.Length); var category = categories[categoryId]; var names = (categoryId == 0) ? category1NamePrefixes : category2NamePrefixes; var name = names[rand.Next(0, names.Length)] + rand.Next(10, 10000).ToString(); var revenue = rand.Next(10000, 100000); rows.Add(area, city, category, name, revenue); } #endregion //Init template global settings workbook.Names.Add("TemplateOptions.KeepLineSize", "true"); //Add data source workbook.AddDataSource("ds", datasource); // Cancel when cancel key pressed or timeout reached. using (CancellationTokenSource cancellation = new CancellationTokenSource()) { void cancelHandler(object sender, ConsoleCancelEventArgs e) { // Exit process gracefully e.Cancel = true; // The following line prevents entering cancelHandler when ProcessTemplate is completed or canceled. // But it has deadlock on .NET Framework and some obsolete versions of .NET Core. // See https://github.com/dotnet/runtime/issues/24516#issuecomment-353967357 #if NETCOREAPP3_0_OR_GREATER Console.CancelKeyPress -= cancelHandler; #endif cancellation.Cancel(); }; Console.CancelKeyPress += cancelHandler; cancellation.CancelAfter(TimeSpan.FromSeconds(10)); Console.WriteLine("Start ProcessTemplate."); try { workbook.ProcessTemplate(cancellation.Token); Console.WriteLine("ProcessTemplate finished."); } catch (OperationCanceledException ex) when (ex.CancellationToken == cancellation.Token) { Console.WriteLine("ProcessTemplate was canceled."); } // The following line prevents entering cancelHandler when ProcessTemplate is completed or canceled. // But it has deadlock on .NET Framework and some obsolete versions of .NET Core. // See https://github.com/dotnet/runtime/issues/24516#issuecomment-353967357 #if NETCOREAPP3_0_OR_GREATER Console.CancelKeyPress -= cancelHandler; #endif }
' Create a new Workbook Dim workbook As New Workbook ' Load template file from resource Dim templateFile = GetResourceStream("xlsx\Template_SalesDataGroup_DataTable.xlsx") workbook.Open(templateFile) #Region "Init Data" Console.WriteLine("Creating test data.") Dim datasource As New DataTable With datasource.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 Dim areas = {"North America", "South America"} Dim cities = {"Chicago", "New York", "Santiago", "Quito", "Fremont", "Buenos Aires", "Medillin", "Minnesota"} Dim categories = {"Consumer Electronics", "Mobile"} Dim category1NamePrefixes = {"Bose ", "Canon ", "Haier ", "IFB ", "Mi ", "Sennheiser "} Dim category2NamePrefixes = {"iPhone ", "OnePlus ", "Redmi ", "Samsung "} Dim rand As New Random Dim rows = datasource.Rows ' You can increase the loop count if the demo is too fast on your computer. For i = 1 To 30000 Dim area = areas(rand.Next(0, areas.Length)) Dim city = cities(rand.Next(0, cities.Length)) Dim categoryId = rand.Next(0, categories.Length) Dim category = categories(categoryId) Dim names = If(categoryId = 0, category1NamePrefixes, category2NamePrefixes) Dim name = names(rand.Next(0, names.Length)) & rand.Next(10, 10000) Dim revenue = rand.Next(10000, 100000) rows.Add(area, city, category, name, revenue) Next #End Region ' Init template global settings workbook.Names.Add("TemplateOptions.KeepLineSize", "true") ' Add data source workbook.AddDataSource("ds", datasource) ' Cancel when cancel key pressed or timeout reached. Using cancellation As New CancellationTokenSource Dim cancelHandler As ConsoleCancelEventHandler = Sub(sender, e) ' Exit process gracefully e.Cancel = True cancellation.Cancel() ' The following line prevents entering cancelHandler when ProcessTemplate is completed or canceled. ' But it has deadlock on .NET Framework and some obsolete versions of .NET Core. ' See https://github.com/dotnet/runtime/issues/24516#issuecomment-353967357 #If NETCOREAPP3_0_OR_GREATER Then RemoveHandler Console.CancelKeyPress, cancelHandler #End If End Sub AddHandler Console.CancelKeyPress, cancelHandler cancellation.CancelAfter(TimeSpan.FromSeconds(10)) Console.WriteLine("Start ProcessTemplate.") Try workbook.ProcessTemplate(cancellation.Token) Console.WriteLine("ProcessTemplate finished.") Catch ex As OperationCanceledException When ex.CancellationToken = cancellation.Token Console.WriteLine("ProcessTemplate was canceled.") End Try ' The following line prevents entering cancelHandler when ProcessTemplate is completed or canceled. ' But it has deadlock on .NET Framework and some obsolete versions of .NET Core. ' See https://github.com/dotnet/runtime/issues/24516#issuecomment-353967357 #If NETCOREAPP3_0_OR_GREATER Then RemoveHandler Console.CancelKeyPress, cancelHandler #End If End Using