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.

string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\SalesReport.accdb";
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:

static private DataTable GetData(string connStr)
{
OleDbConnection conn = new OleDbConnection(connStr);
conn.Open();
string cmdStr = "select * from parts";
OleDbCommand command = new OleDbCommand(cmdStr, conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
conn.Close();
return dataTable;
}

2. Create a workbook and initialize worksheets. Because I just need two worksheets, so move the last one.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
workbook.Worksheets[2].Remove();

3. Name sheet 1 and insert DataTable in this sheet.

sheet.Name = "Sales Info";
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.

int lastRow = dataTable.Rows.Count;
int lastCol = dataTable.Columns.Count;
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.

static private void SetFormat(Worksheet sheet, int lastRow, int lastCol) {
//Header Format
sheet.Range[1, 1, 1, lastCol].Style.Color = Color.DeepSkyBlue;
sheet.Range[1, 1, 1, lastCol].Style.Font.Size = 13;
sheet.Range[1, 1, 1, lastCol].Style.Font.IsBold = true;
sheet.Range[1, 1, 1, lastCol].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range[1, 1, 1, lastCol].Style.Font.Color = Color.White;
//Whole Data Range Format
sheet.Range[1, 1, lastRow + 1, lastCol].Style.HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range[1, 1, lastRow + 1, lastCol].Style.VerticalAlignment = VerticalAlignType.Center;
sheet.Range[1, 1, lastRow + 1, lastCol].Style.Font.FontName = "Calibri";
sheet.Range[1, 1, lastRow + 1, lastCol].Style.Font.Size = 12;
sheet.Range[1, 1, lastRow + 1, lastCol].AutoFitColumns();
sheet.Range[1, 1, lastRow + 1, lastCol].RowHeight = 18;
//Data Body Format
sheet.Range[2, 1, lastRow + 1, lastCol].Style.Color = Color.GhostWhite;
//Borders
sheet.AllocatedRange.Style.Borders.LineStyle = LineStyleType.Thin;
sheet.AllocatedRange.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.AllocatedRange.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.AllocatedRange.Style.Borders.Color = Color.DarkCyan;
}

5. Declare worksheet 2 and name it "Pivot Table".

Worksheet report = workbook.Worksheets[1];
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.

static private void CreatePivotTable(Workbook workbook, Worksheet sheet, Worksheet report, int lastRow, int lastCol)
{
//Choose DataRange
CellRange dataRange = sheet.Range[1, 1, lastRow, lastCol];
PivotCache cache = workbook.PivotCaches.Add(dataRange);
PivotTable pt = report.PivotTables.Add("report", sheet.Range["A1"], cache);
//Set Row Labels
var r1 = pt.PivotFields["VendorNo"];
r1.Axis = AxisTypes.Row;
pt.Options.RowHeaderCaption = "VendorNo";
var r2 = pt.PivotFields["Description"];
r2.Axis = AxisTypes.Row;
//Add Pivot Fields
pt.DataFields.Add(pt.PivotFields["OnHand"], "SUM of OnHand", SubtotalTypes.Sum);
pt.DataFields.Add(pt.PivotFields["OnOrder"], "SUM of OnOrder", SubtotalTypes.Sum);
pt.DataFields.Add(pt.PivotFields["Cost"], "SUM of Cost", SubtotalTypes.Sum);
pt.DataFields.Add(pt.PivotFields["ListPrice"], "Average of ListPrice", SubtotalTypes.Average);
//Set Style
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium6;
}

7. Save and launch file.

workbook.SaveToFile(@"..\..\result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start(@"..\..\result.xlsx");

Result

Datasheet

Pivot table sheet

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.

12 Comments

Join the conversation! Your thoughts help the community grow.

srikanth k6y ago
Is it free
Galib Tamboli8y ago
I want remove a datasheet and evaluation Waring sheet from excel.
Galib Tamboli8y ago
I want to remove a datasheet and evaluation Waring form excel
Bhagya Bakore10y ago
What is the version and PublicKeyToken used for provided Spire.XLS.dll here ? or what is the way to know it ? please help
Ankur Jain12y ago
I want to make borders visible in pivot table and also remove grand total, subtotal row. can you please help
Ankur Jain12y ago
Hi Lizz,
waqas memon12y ago
hi lizzy landy great work, i make pivot query http://codingresolved.com/discussion/2272/pivot-with-multiple-non-aggregate-columns-sql-server/p1and after it i thought to make some control like this but you already done this job good work and thanks again.
it tran14y ago
Very good job! Thank for Sharing
Manish Singh14y ago
Hi Lizzy really good job, thanks for sharing.
Amit Maheshwari14y ago
Hi, Lizzy It's a great effort to accfomplish such task, So keep going and thanks for sharing.......
Arjun Panwar14y ago
Really very good work.
Sharon White14y ago
Hi Lizzy Good Job