Create a Pivot Table in Excel - via script.

General
, ,
1

This script will help if you’re trying to create a basic pivot table (please refer to the attached .txt file) and if you need something a little advanced, the links below will help you.

Method parameters,

  • path - excel file path
  • workSheet - sheet number
  • dataStart - input data start cell
  • dataEnd - input data end/last cell
  • PivotStart - Pivot table start cell.

public void ExelPivot(string path, int workSheet, string dataStart, string dataEnd, string PivotStart)

  • Before this script is invoked, make sure to, save and close your excel.

Invoke script.jpg
Invoke script.jpg747×101 15.6 KB

Note: Add this line before the namespace, using Excel = Microsoft.Office.Interop.Excel;

Pivot table methods: https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.pivottables?view=excel-pia

Pivot fields: https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.tools.excel.namedrange.pivotfield?view=vsto-2017

Pivot table settings/options: https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.pivottable?view=excel-pia

PivotScript.txt (3.84 KB)

Invoke script.jpg
Invoke script.jpg747×101 15.6 KB

2

Very nice!
An alternative would be to program the pivot table in VBA and run the Openspan.Office.MicrosoftExcel.RunMacro method.