Home > Articles > C# Articles > Create Excel Pivot Table using Third-party Dll

Create Excel Pivot Table using Third-party Dll

by Tyler.D   on Dec 21, 2014   Category: C#  | Level: Beginner  |  Views: 2022    |  Points: 100   
Like this article? Bookmark and Share:
Excel can be very useful for student interactive activities, interactive lectures, and instructor use for developing materials for class. As a teacher, I use Excel as a calculator to explore what mathematical equations can tell us about how the real world works for specific input conditions or for a range of possible values.

Introduction

Excel can be very useful for student interactive activities, interactive lectures, and instructor use for developing materials for class. As a teacher, I use Excel as a calculator to explore what mathematical equations can tell us about how the real world works for specific input conditions or for a range of possible values. Recently, I found a free library to create and manipulate an Excel document: Free Spire.XLS. It has plenty of features for me to meet the requirement, so I write this article to see if it could help someone.

Below are the steps you can follow

Step 1: Create an empty Excel and add a worksheet.

Workbook workbook = new Workbook();

 Worksheet sheet = workbook.Worksheets[0];

 sheet.Name = "Data";

Step 2: Fill sample data to cells.

//Country

 sheet.Range["C1"].Value = "Country";

 sheet.Range["C2"].Value = "Cuba";

 sheet.Range["C3"].Value = "Mexico";

 sheet.Range["C4"].Value = "France";

 sheet.Range["C5"].Value = "German";

 //Sep

 sheet.Range["D1"].Value = "Sep";

 sheet.Range["D2"].NumberValue = 3300;

 sheet.Range["D3"].NumberValue = 2300;

 sheet.Range["D4"].NumberValue = 4500;

 sheet.Range["D5"].NumberValue = 6700;

 //Oct

 sheet.Range["E1"].Value = "Oct";

 sheet.Range["E2"].NumberValue = 7500;

 sheet.Range["E3"].NumberValue = 2900;

 sheet.Range["E4"].NumberValue = 2300;

 sheet.Range["E5"].NumberValue = 4200;

//…..

Step 3: Set cells’ shading color and border style.

//Style

 sheet.Range["C1:G1"].Style.Font.IsBold = true;

 sheet.Range["C2:G2"].Style.KnownColor = ExcelColors.LightYellow;

 sheet.Range["C3:G3"].Style.KnownColor = ExcelColors.LightGreen;

 sheet.Range["C4:G4"].Style.KnownColor = ExcelColors.LightGreen1;

 sheet.Range["C5:G5"].Style.KnownColor = ExcelColors.LightOrange;

 //Border

 sheet.Range["C1:G5"].Borders[BordersLineType.EdgeBottom].Color = Color.Blue;

 sheet.Range["C1:G5"].Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;

 sheet.Range["C1:G5"].Borders[BordersLineType.EdgeTop].Color = Color.Blue;

 sheet.Range["C1:G5"].Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;

 sheet.Range["C1:G5"].Borders[BordersLineType.EdgeLeft].Color = Color.Blue;

 sheet.Range["C1:G5"].Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;

 sheet.Range["C1:G5"].Borders[BordersLineType.EdgeRight].Color = Color.Blue;

 sheet.Range["C1:G5"].Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

Step 4: Add a new chart to Excel, using data from C1 to G5.

//Add a new chart

Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);

//Set region of chart data

chart.DataRange = sheet.Range["C1:G5"];

//Set position of chart

chart.LeftColumn = 1;

chart.TopRow = 7;

chart.RightColumn = 10;

chart.BottomRow = 30;

Step 5: Set chart’s primary category axis and primary value axis.

//primary category axis

chart.PrimaryCategoryAxis.Title = "Month";

chart.PrimaryCategoryAxis.Font.IsBold = true;

chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

//primary value axis

chart.PrimaryValueAxis.Title = "Sales(in Dollars)";

chart.PrimaryValueAxis.HasMajorGridLines = false;

chart.PrimaryValueAxis.TitleArea.TextRotationAngle = -90;

chart.PrimaryValueAxis.MinValue = 1000;

chart.PrimaryValueAxis.TitleArea.IsBold = true;

Step 6: Add value to data labels and set the position of legend.

//data labels

foreach (ChartSerie cs in chart.Series)

{

    cs.Format.Options.IsVaryColor = true;

    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;

}

//legend

chart.Legend.Position = LegendPositionType.Top;


Step 7: Create pivot table using data from C1 to G5.

//create pivot table

Worksheet sheet02 = workbook.Worksheets[1];

sheet02.Name = "Pivot Table";

CellRange dataRange = sheet01.Range["C1:G5"];

PivotCache cache = workbook.PivotCaches.Add(dataRange);

PivotTable pt = sheet02.PivotTables.Add("Pivot Table", sheet02.Range["A1"], cache);

Step 8: Add row label to assign data information, add more fields to calculate data.

//row labels

var pf1 = pt.PivotFields["Country"];

pf1.Axis = AxisTypes.Row;

pt.Options.RowHeaderCaption = "Country";

//add data fields and set format

pt.DataFields.Add(pt.PivotFields["Sep"], "Sum of Sep", SubtotalTypes.Sum);

pt.DataFields.Add(pt.PivotFields["Oct"], "Sum of Oct", SubtotalTypes.Sum);

pt.DataFields.Add(pt.PivotFields["Nov"], "Sum of Nov", SubtotalTypes.Sum);

pt.DataFields.Add(pt.PivotFields["Dec"], "Sum of Dec", SubtotalTypes.Sum);

pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;

Step 9: Save it to disk and view.

workbook.SaveToFile("Sample.xlsx", ExcelVersion.Version2010);

 

Screenshot

Date and Chart:

Pivot Table



Like this article? Bookmark and Share:

Most viewed Articles

User Comments


No response found, be the first to review this article.

Submit feedback about this article

Please sign in to post feedback

Latest Posts