import com.spire.xls.*;
import com.spire.xls.core.IPivotField;


public class CreatePivotTableDemo {
    public void createPivotTableExcel(String filePath, String resultFilePath){
        Workbook workbook = createPivotTable(filePath);
        workbook.saveToFile(resultFilePath);
    }

    public Workbook createPivotTable(String filePath){
        Workbook workbook = new Workbook();
        workbook.loadFromFile(filePath, ExcelVersion.Version2010);
        Worksheet worksheet = workbook.getWorksheets().get(0);
        worksheet.setName("Data Source");
        Worksheet worksheet1 = workbook.createEmptySheet();
        worksheet1.setName("Pivot Table");

        CellRange dataRange = worksheet.getRange().get("A1:G17");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);
        PivotTable pt = worksheet1.getPivotTables().add("Pivot Table",worksheet.getRange().get("A1"),cache);

        IPivotField r1 = pt.getPivotFields().get("Vendor No");
        r1.setAxis(AxisTypes.Row);
        pt.getOptions().setRowHeaderCaption("Vendor No");

        IPivotField r2 = pt.getPivotFields().get("Name");
        r2.setAxis(AxisTypes.Row);

        pt.getDataFields().add(pt.getPivotFields().get("Area"), "Average of Area", SubtotalTypes.Average);
        pt.getDataFields().add(pt.getPivotFields().get("Sales"), "SUM of Sales", SubtotalTypes.Sum);
        pt.getDataFields().add(pt.getPivotFields().get("OnHand"), "Max of OnHand", SubtotalTypes.Max);
        pt.getDataFields().add(pt.getPivotFields().get("OnOrder"), "Min of OnOrder", SubtotalTypes.Min);
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);
        pt.calculateData();
        worksheet1.getAllocatedRange().autoFitColumns();
        worksheet1.getAllocatedRange().autoFitRows();
        return workbook;
    }
}

using Spire.Xls;

namespace DemoOnlineCode
{
    class CreatePivotTableInExcel
    {
        public void demoCreatePivotTableInExcel(string filePath, string resultFilePath)
        {
            Workbook workbook = CreatePivotTable(filePath);
            workbook.SaveToFile(resultFilePath);
        }

        public Workbook CreatePivotTable(string filePath)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(filePath, ExcelVersion.Version2007);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Data Source";
            Worksheet sheet2 = workbook.CreateEmptySheet();
            sheet2.Name = "Pivot Table";

            CellRange dataRange = sheet.Range["A1:G17"];
            PivotCache cache = workbook.PivotCaches.Add(dataRange);
            PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);

            var r1 = pt.PivotFields["Vendor No"];
            r1.Axis = AxisTypes.Row;
            pt.Options.RowHeaderCaption = "Vendor No";

            var r2 = pt.PivotFields["Name"];
            r2.Axis = AxisTypes.Row;

            pt.DataFields.Add(pt.PivotFields["Area"], "Average of Area", SubtotalTypes.Average);
            pt.DataFields.Add(pt.PivotFields["Sales"], "SUM of Sales", SubtotalTypes.Sum);
            pt.DataFields.Add(pt.PivotFields["OnHand"], "Max of OnHand", SubtotalTypes.Max);
            pt.DataFields.Add(pt.PivotFields["OnOrder"], "Min of OnOrder", SubtotalTypes.Min);
            pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;
            pt.CalculateData();
            sheet2.AllocatedRange.AutoFitColumns();
            sheet2.AllocatedRange.AutoFitRows();
            return workbook;

        }
    }
}