public void setpivottable(string xlsxPath, string dataSource, string pivotSheetName)
{
//Create a workbook
Workbook workbook = new Workbook();
//Load an excel file including pivot table
workbook.LoadFromFile(@xlsxPath);
//Modify data of data source
Worksheet data = workbook.Worksheets[dataSource];
//Get the sheet in which the pivot table is located
Worksheet sheet = workbook.Worksheets[pivotSheetName];
XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;
//Refresh and calculate
pt.Cache.IsRefreshOnLoad = true;
pt.CalculateData();
string time = DateTime.Now.ToString("yyyyMMddHHmmss");
sheet.SaveToHtml("D:\\report\\" + time + ".html");
Workbook wk = new Workbook();
wk.LoadFromFile("D:\\report\\" + time + ".html");
wk.SaveToFile("D:\\report\\" + time + ".xlsx");
//Workbook workbookexport = new Workbook();
//workbookexport.Version = workbook.Version;
//Worksheet sheetexport = workbookexport.Worksheets.Add(pivotSheetName);
//sheetexport.Name = pivotSheetName;
//workbookexport.SaveToFile("D:\\report\\" + time + ".xlsx");
//Save to file
//workbook.SaveToFile("D:\\report\\" + time + ".xlsx", ExcelVersion.Version2013);
//FileViewer("D:\\report\\" + time + ".xlsx");
}
