Create a pivot table in Excel workbook with data exported using template markers
Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. Using this library, you can create a pivot table with data imported into Excel worksheet using Template Markers.
What is Template Marker?
Template Marker is a special marker symbol created in an Excel template that appends multiple records from data source into worksheet. This marker automatically maps the column name in the data source and names of the marker fields in the template Excel document and fills the data (text or image).
What is Pivot Table?
Pivot Table is a program tool that allows you to reorganize and summarize the data in a spreadsheet or database table to obtain a desired report. It is used to build a list of unique values and a good way to quickly see all the values that appear in the field.
Steps to import data into Excel worksheet using Template Marker and create a Pivot table using imported data, programmatically:
Step 1: Create a new C# ASP.NET Core Web application project.
Create a new C# ASP.NET Core Web Application project
Step 2: Name the project.
Name the project
Step 3: Select Web Application pattern as Model-View-Controller for the project.
Select the web application pattern as MVC
Step 4: Install the Syncfusion.XlsIO.Net.Core NuGet package as reference for .NET Standard project from NuGet.org.
Install NuGet package as reference to the project
Step 5: A default controller with named HomeController.cs gets added on creation of ASP.NET Core project. Include the following namespaces in this HomeController.cs file.
C#
using Syncfusion.XlsIO; using System.IO; using System.ComponentModel.DataAnnotations; using Microsoft.AspNetCore.Mvc;
Step 6: A default action method named Index will be present in HomeController.cs. Right click on Index method and select Go To View where you will be directed to its associated view page Index.cshtml.
Step 7: Add a new button in the Index.cshtml as shown below.
CSHTML
@{Html.BeginForm("CreateDocument", "Home", FormMethod.Get);
{
<div>
<input type="submit" value="Create Document" style="width:150px;height:27px" />
</div>
}
Html.EndForm();
}
Step 8: Add a new action method CreateDocument in HomeController.cs and include the below code snippet to import data into Excel worksheet using Template Markers and create a Pivot Table using the imported data.
C#
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
IList<provincemonthly> pivotData = GetData();
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
try
{
sheet.Range["A1"].Text = "ProvId";
sheet.Range["B1"].Text = "Province";
sheet.Range["C1"].Text = "Year";
sheet.Range["D1"].Text = "M1";
sheet.Range["E1"].Text = "M2";
sheet.Range["F1"].Text = "M3";
sheet.Range["G1"].Text = "M4";
sheet.Range["H1"].Text = "M5";
sheet.Range["I1"].Text = "M6";
sheet.Range["J1"].Text = "M7";
sheet.Range["K1"].Text = "M8";
sheet.Range["L1"].Text = "M9";
sheet.Range["M1"].Text = "M10";
sheet.Range["N1"].Text = "M11";
sheet.Range["O1"].Text = "M12";
sheet.Range["A2"].Text = "%Reports.ProvId";
sheet.Range["B2"].Text = "%Reports.Province";
sheet.Range["C2"].Text = "%Reports.Year";
sheet.Range["D2"].Text = "%Reports.M1";
sheet.Range["E2"].Text = "%Reports.M2";
sheet.Range["F2"].Text = "%Reports.M3";
sheet.Range["G2"].Text = "%Reports.M4";
sheet.Range["H2"].Text = "%Reports.M5";
sheet.Range["I2"].Text = "%Reports.M6";
sheet.Range["J2"].Text = "%Reports.M7";
sheet.Range["K2"].Text = "%Reports.M8";
sheet.Range["L2"].Text = "%Reports.M9";
sheet.Range["M2"].Text = "%Reports.M10";
sheet.Range["N2"].Text = "%Reports.M11";
sheet.Range["O2"].Text = "%Reports.M12";
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
marker.AddVariable("Reports", pivotData);
marker.ApplyMarkers();
sheet.Name = "Data";
}
catch (Exception)
{
throw;
}
IWorksheet pivotSheet = workbook.Worksheets[1];
pivotSheet.Name = "PivotTable";
pivotSheet["A2"].Text = "HF Level Request";
pivotSheet.Range["A2"].CellStyle.Font.Size = 14f;
pivotSheet.Range["A2"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].Text = "Data Extracted: " + DateTime.Now.ToString();
pivotSheet.Range["A3"].CellStyle.Font.Size = 10f;
pivotSheet.Range["A3"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].CellStyle.Font.Italic = true;
IPivotCache cash_data = workbook.PivotCaches.Add(sheet.UsedRange);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A5"], cash_data);
IPivotTableOptions options = pivotTable.Options;
options.ShowFieldList = false;
pivotTable.Fields["Province"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Row;
IPivotField M1 = pivotTable.Fields["M1"];
IPivotField M2 = pivotTable.Fields["M2"];
pivotTable.DataFields.Add(M1, "M1", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(M2, "M2", PivotSubtotalTypes.Sum);
pivotTable.ShowDataFieldInRow = true;
IPivotTableOptions option = pivotTable.Options;
option.ErrorString = "X";
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark20;
pivotSheet.Activate();
//Saving the Excel to the MemoryStream
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
//Set the position as '0'
stream.Position = 0;
//Download the Excel file in the browser
FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
fileStreamResult.FileDownloadName = "Output.xlsx";
return fileStreamResult;
}
Step 9: Include the following GetData method in HomeController class to import data.
C#
private static IList<provincemonthly> GetData()
{
List<provincemonthly> obj = new List<provincemonthly>();
obj.Add(new provincemonthly("1", "Province1", 2001, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112));
obj.Add(new provincemonthly("2", "Province2", 2002, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212));
obj.Add(new provincemonthly("3", "Province3", 2003, 301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 312));
obj.Add(new provincemonthly("4", "Province4", 2004, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412));
obj.Add(new provincemonthly("5", "Province5", 2005, 501, 502, 503, 504, 505, 506, 507, 508, 509, 510, 511, 512));
return obj;
}
Step 10: Include the following provincemonthly class in HomeController.cs file.
C#
public partial class provincemonthly
{
[Key]
public string ProvId { get; set; }
public string Province { get; set; }
public int Year { get; set; }
public Int32 M1 { get; set; }
public Int32 M2 { get; set; }
public Int32 M3 { get; set; }
public Int32 M4 { get; set; }
public Int32 M5 { get; set; }
public Int32 M6 { get; set; }
public Int32 M7 { get; set; }
public Int32 M8 { get; set; }
public Int32 M9 { get; set; }
public Int32 M10 { get; set; }
public Int32 M11 { get; set; }
public Int32 M12 { get; set; }
public provincemonthly(string provId, string provience, int year, Int32 m1, Int32 m2, Int32 m3, Int32 m4, Int32 m5, Int32 m6, Int32 m7, Int32 m8, Int32 m9, Int32 m10, Int32 m11, Int32 m12)
{
ProvId = provId;
Province = provience;
Year = year;
M1 = m1;
M2 = m2;
M3 = m3;
M4 = m4;
M5 = m5;
M6 = m6;
M7 = m7;
M8 = m8;
M9 = m9;
M10 = m10;
M11 = m11;
M12 = m12;
}
}
A complete working example to how to import data into Excel worksheet using Template Markers and create a Pivot Table with imported data can be downloaded from CreatePivotTable.zip.
By executing the program, you will get the Excel file as follows.
Output Excel document
Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions etc. with code examples.
Click here to explore the rich set of Syncfusion Excel (XlsIO) library features.
An online sample link for Excel to PDF conversion.
See Also:
Create Pivot Table with existing data using XlsIO.
Sort Pivot Fields using XlsIO.
Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.
