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 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 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 the creating pivot table, I will set VendorNo as a row label to assign data information as two categories. Then, calculate the data, including sum of OnHand, sum of OnOrder, sum of cost and average of ListPrice.

Steps

This method is based on a component Spire.XLS. Therefore, I have added 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 builtin 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 which 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.