C# - Excel - how to create Excel File with Pivot Table programmatically

Watch this on YouTube:
Add the following reference to your project - Microsoft.Office.Interop.Excel - ensure Excel is installed on your computer






You will need these 2 "usings":

using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

Add button and on click copy and paste the following code:
            string fileTest = @"C:\Temp\ExcelTest\test.xlsx";
            if (File.Exists(fileTest))
            {
                File.Delete(fileTest);
            }

            Excel.Application oApp;
            Excel.Worksheet oSheet;
            Excel.Workbook oBook;

            oApp = new Excel.Application();
            oBook = oApp.Workbooks.Add();
            oSheet = (Excel.Worksheet)oBook.Worksheets.get_Item(1);

            oSheet.Cells[1, 1] = "Name";
            oSheet.Cells[1, 2] = "Salary";

            oSheet.Cells[2, 1] = "Frank";
            oSheet.Cells[2, 2] = 150000;

            oSheet.Cells[3, 1] = "Ann";
            oSheet.Cells[3, 2] = 300000;

            // now capture range of the first sheet = I will need this to create pivot table
            Excel.Range oRange = oSheet.Range["A1", "B3"];

            // create second sheet
            if (oApp.Application.Sheets.Count < 2)
            {
                oSheet = (Excel.Worksheet)oBook.Worksheets.Add();
            }
            else
            {
                oSheet = oApp.Worksheets[2];
            }
            oSheet.Name = "Pivot Table";

            // specify first cell for pivot table
            Excel.Range oRange2 = oSheet.Cells[1, 1];

            // create Pivot Cache and Pivot Table
            Excel.PivotCache oPivotCache = (Excel.PivotCache)oBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange);
            Excel.PivotTable oPivotTable = (Excel.PivotTable)oSheet.PivotTables().Add(PivotCache: oPivotCache, TableDestination: oRange2, TableName: "Summary");

            // create Pivot Field, note that name will be the same as column name on sheet one
            Excel.PivotField oPivotField = (Excel.PivotField)oPivotTable.PivotFields("Salary");
            oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
            oPivotField.Function = Excel.XlConsolidationFunction.xlSum;
            oPivotField.Name = " Salary";
            // by default name will be something like sumOfSalary, to change it, assign new name to it
            // name cannot be the same as Pivot Fields, therefore I added empty space in front of it.
            // name cannot be empty either

            // save
            oBook.SaveAs(fileTest);
            oBook.Close();
            oApp.Quit();
This program will create simple Excel file with 2 worksheets.  Second worksheet will have pivot table.

Watch this on YouTube: