Пример #1
0
        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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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");
        }