Post
Ask Question

How to Export Data to One Worksheet and Create Pivot Table in Another Based on the Data

Introduction

 
A Pivot table is a kind of interactive table. It can be used to do some calculations, such as sum, count, etc. In a Pivot table, calculations are related to data order. For example, users can calculate each row/column's total values according to field values which are displayed horizontally or vertically. Also, users can take field values as row/column labels and then calculate each amount at the junction of a row and column.
 
MS Excel provides users with a function to insert a Pivot Table according to data existing in a worksheet. In this article, I want to introduce a method for exporting data from a database to a worksheet and insert a pivot table in another sheet according to the data using C#.
 
Background
 
There is a table in a database about a parts sales report, including PartNo, VendorNo, Description, OnHand, OnOrder, cost, and ListPrice. When creating a pivot table, I will set VendorNo as a row label to assign data information as two categories. Then, calculate the data, including the sum of OnHand, the sum of OnOrder, the sum of cost, and the average of ListPrice.
 
Steps This method is based on a component Spire.XLS. Therefore, I have added the Spire.XLS dll file as a reference in my project at the beginning.
 
1. Get data from the database by invoking the GetData() method and then save the data in the DataTable.
  1. string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\SalesReport.accdb";  
  2. DataTable dataTable = GetData(connStr); 
GetData Method Connect to the database with connstr and then choose the table to get the data from for the DataTable:
  1. static private DataTable GetData(string connStr)  
  2. {  
  3.      OleDbConnection conn = new OleDbConnection(connStr);  
  4.      conn.Open();  
  5.      string cmdStr = "select * from parts";  
  6.      OleDbCommand command = new OleDbCommand(cmdStr, conn);  
  7.      OleDbDataAdapter adapter = new OleDbDataAdapter(command);  
  8.      DataTable dataTable = new DataTable();  
  9.      adapter.Fill(dataTable);  
  10.      conn.Close();  
  11.      return dataTable;  
  12. } 
 2. Create a workbook and initialize worksheets. Because I just need two worksheets, so move the last one.
  1. Workbook workbook = new Workbook();  
  2. Worksheet sheet = workbook.Worksheets[0];  
  3. workbook.Worksheets[2].Remove(); 
 3. Name sheet 1 and insert DataTable in this sheet. 
  1. sheet.Name = "Sales Info";  
  2. sheet.InsertDataTable(dataTable, true, 1, 1); 
 4. To conveniently set the format, declare lastRow and lastCol. Then, invoke the SetFormat() method. There are three parameters passed to this method, worksheet, lastRow and lastCol.
  1. int lastRow = dataTable.Rows.Count;  
  2. int lastCol = dataTable.Columns.Count;  
  3. SetFormat(sheet, lastRow,lastCol); 
SetFormat Method Fix the data range and set the format for it, including cell fill color, font style, color and size, borders, column width and row height. Because the header format is different from the others, first set the header then set the others.
  1. static private void SetFormat(Worksheet sheet, int lastRow, int lastCol) {  
  2.     //Header Format  
  3.     sheet.Range[1, 1, 1, lastCol].Style.Color = Color.DeepSkyBlue;  
  4.     sheet.Range[1, 1, 1, lastCol].Style.Font.Size = 13;  
  5.     sheet.Range[1, 1, 1, lastCol].Style.Font.IsBold = true;  
  6.     sheet.Range[1, 1, 1, lastCol].HorizontalAlignment = HorizontalAlignType.Center;  
  7.     sheet.Range[1, 1, 1, lastCol].Style.Font.Color = Color.White;  
  8.   
  9.     //Whole Data Range Format  
  10.     sheet.Range[1, 1, lastRow + 1, lastCol].Style.HorizontalAlignment = HorizontalAlignType.Center;  
  11.     sheet.Range[1, 1, lastRow + 1, lastCol].Style.VerticalAlignment = VerticalAlignType.Center;  
  12.     sheet.Range[1, 1, lastRow + 1, lastCol].Style.Font.FontName = "Calibri";  
  13.     sheet.Range[1, 1, lastRow + 1, lastCol].Style.Font.Size = 12;  
  14.     sheet.Range[1, 1, lastRow + 1, lastCol].AutoFitColumns();  
  15.     sheet.Range[1, 1, lastRow + 1, lastCol].RowHeight = 18;  
  16.   
  17.     //Data Body Format  
  18.     sheet.Range[2, 1, lastRow + 1, lastCol].Style.Color = Color.GhostWhite;  
  19.   
  20.     //Borders  
  21.     sheet.AllocatedRange.Style.Borders.LineStyle = LineStyleType.Thin;  
  22.     sheet.AllocatedRange.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;  
  23.     sheet.AllocatedRange.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;  
  24.     sheet.AllocatedRange.Style.Borders.Color = Color.DarkCyan;  
  25. } 
 5. Declare worksheet 2 and name it "Pivot Table".
  1. Worksheet report = workbook.Worksheets[1];  
  2. report.Name = "Pivot Table"; 
 6. Create a pivot table by invoking the CreatePivotTable() method. There are five parameters passed to this method, workbook, worksheet 1, worksheet 2, lastRow, lastCol.     CreatePivotTable(workbook, sheet, report, lastRow, lastCol);
 

CreatePivotTable Method

 
First, get the data range and declare that the pivot table data information source is from this range. Secondly, add a pivot table in worksheet 2. When using the add method, three parameters, name string, location, and data range should be passed. Thirdly, set row labels. Because in the original table, all the parts are separated with two categories by VendorNo, so set VendorNo as row label. Also, the description is used to describe Parts, so set it as the row label too. Fourthly, add pivot fields, and calculate. There are four items to add, OnHand, OnOrder, Cost, and ListPrice. When using the add fields method, three parameters are passed; field, field name, and subtotal type.
 
Finally, set the format. I just use a built-in style.
  1. static private void CreatePivotTable(Workbook workbook, Worksheet sheet, Worksheet report, int lastRow, int lastCol)  
  2. {  
  3.      //Choose DataRange  
  4.      CellRange dataRange = sheet.Range[1, 1, lastRow, lastCol];  
  5.      PivotCache cache = workbook.PivotCaches.Add(dataRange);  
  6.      PivotTable pt = report.PivotTables.Add("report", sheet.Range["A1"], cache);  
  7.   
  8.      //Set Row Labels  
  9.      var r1 = pt.PivotFields["VendorNo"];  
  10.      r1.Axis = AxisTypes.Row;  
  11.      pt.Options.RowHeaderCaption = "VendorNo";  
  12.   
  13.      var r2 = pt.PivotFields["Description"];  
  14.      r2.Axis = AxisTypes.Row;  
  15.   
  16.      //Add Pivot Fields  
  17.      pt.DataFields.Add(pt.PivotFields["OnHand"], "SUM of OnHand", SubtotalTypes.Sum);  
  18.      pt.DataFields.Add(pt.PivotFields["OnOrder"], "SUM of OnOrder", SubtotalTypes.Sum);  
  19.      pt.DataFields.Add(pt.PivotFields["Cost"], "SUM of Cost", SubtotalTypes.Sum);  
  20.      pt.DataFields.Add(pt.PivotFields["ListPrice"], "Average of ListPrice", SubtotalTypes.Average);  
  21.   
  22.      //Set Style  
  23.      pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium6;  
  24. } 
7. Save and launch file.
  1. workbook.SaveToFile(@"..\..\result.xlsx", ExcelVersion.Version2010);  
  2. System.Diagnostics.Process.Start(@"..\..\result.xlsx"); 
Result
 
Datasheet
 
DataSheet.png
 
Pivot table sheet
 
PivotTable Sheet.png

Conclusion

 
This article focuses on how to create a pivot table in an Excel worksheet according to data that is exported to Excel from a database. This method is helpful for users who want to use a pivot table to do some simple calculations on data. Also, a pivot table can present a category clearly and make readers learn data information and calculation results more clearly. About the component I use in this method, you can DOWNLOAD from here. The whole solution, database, and result file has been attached.


Similar Articles