private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\PivotTable.xlsx");
XlsPivotTable pivotTable = workbook.Worksheets[0].PivotTables[0] as XlsPivotTable;
PivotReportFilter filter = new PivotReportFilter("Count", true);
pivotTable.ReportFilters.Add(filter);
workbook.SaveToFile("Sample.xlsx", ExcelVersion.Version2010);
ExcelDocViewer(workbook.FileName);
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\PivotTable.xlsx");
Worksheet sheet = workbook.Worksheets[0];
// Accessing the PivotTable
XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;
// Accessing the data field.
PivotDataField pivotDataField = pt.DataFields[0];
// Setting data display format
pivotDataField.ShowDataAs = PivotFieldFormatType.PercentageOfColumn;
workbook.SaveToFile("Sample.xlsx", ExcelVersion.Version2010);
ExcelDocViewer(workbook.FileName);
}
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");
}