public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Load a template file Workbook workbook = new Workbook(dataDir + "Book1.xls"); // Get the first worksheet Worksheet sheet = workbook.Worksheets[0]; // Get the pivot tables in the sheet PivotTableCollection pivotTables = sheet.PivotTables; // Get the first PivotTable PivotTable pivotTable = pivotTables[0]; // Clear all the data fields pivotTable.DataFields.Clear(); // Add new data field pivotTable.AddFieldToArea(PivotFieldType.Data, "Betrag Netto FW"); // Set the refresh data flag on pivotTable.RefreshDataFlag = false; // Refresh and calculate the pivot table data pivotTable.RefreshData(); pivotTable.CalculateData(); // Saving the Excel file workbook.Save(dataDir + "output.xls"); // ExEnd:1 }
public static void Run() { // ExStart:1 string outputDir = RunExamples.Get_OutputDirectory(); // Instantiating a Workbook object Workbook workbook = new Workbook(); // Obtaining the reference of the newly added worksheet Worksheet sheet = workbook.Worksheets[0]; Cells cells = sheet.Cells; // Setting the value to the cells Cell cell = cells["A1"]; cell.PutValue("Sport"); cell = cells["B1"]; cell.PutValue("Quarter"); cell = cells["C1"]; cell.PutValue("Sales"); cell = cells["A2"]; cell.PutValue("Golf"); cell = cells["A3"]; cell.PutValue("Golf"); cell = cells["A4"]; cell.PutValue("Tennis"); cell = cells["A5"]; cell.PutValue("Tennis"); cell = cells["A6"]; cell.PutValue("Tennis"); cell = cells["A7"]; cell.PutValue("Tennis"); cell = cells["A8"]; cell.PutValue("Golf"); cell = cells["B2"]; cell.PutValue("Qtr3"); cell = cells["B3"]; cell.PutValue("Qtr4"); cell = cells["B4"]; cell.PutValue("Qtr3"); cell = cells["B5"]; cell.PutValue("Qtr4"); cell = cells["B6"]; cell.PutValue("Qtr3"); cell = cells["B7"]; cell.PutValue("Qtr4"); cell = cells["B8"]; cell.PutValue("Qtr3"); cell = cells["C2"]; cell.PutValue(1500); cell = cells["C3"]; cell.PutValue(2000); cell = cells["C4"]; cell.PutValue(600); cell = cells["C5"]; cell.PutValue(1500); cell = cells["C6"]; cell.PutValue(4070); cell = cells["C7"]; cell.PutValue(5000); cell = cells["C8"]; cell.PutValue(6430); PivotTableCollection pivotTables = sheet.PivotTables; // Adding a PivotTable to the worksheet int index = pivotTables.Add("=A1:C8", "E3", "PivotTable2"); // Accessing the instance of the newly added PivotTable PivotTable pivotTable = pivotTables[index]; // Unshowing grand totals for rows. pivotTable.RowGrand = false; // Draging the first field to the row area. pivotTable.AddFieldToArea(PivotFieldType.Row, 0); // Draging the second field to the column area. pivotTable.AddFieldToArea(PivotFieldType.Column, 1); // Draging the third field to the data area. pivotTable.AddFieldToArea(PivotFieldType.Data, 2); pivotTable.CalculateData(); // Saving the ODS file workbook.Save(outputDir + "PivotTableSaveInODS_out.ods"); // ExEnd:1 Console.WriteLine("PivotTableSaveInODS executed successfully."); }
public static void Run() { //Source directory string sourceDir = RunExamples.Get_SourceDirectory(); //Output directory string outputDir = RunExamples.Get_OutputDirectory(); Workbook wb = new Workbook(sourceDir + "sampleSpecifyAbsolutePositionOfPivotItem.xlsx"); Worksheet wsPivot = wb.Worksheets.Add("pvtNew Hardware"); Worksheet wsData = wb.Worksheets["New Hardware - Yearly"]; // Get the pivottables collection for the pivot sheet PivotTableCollection pivotTables = wsPivot.PivotTables; // Add PivotTable to the worksheet int index = pivotTables.Add("='New Hardware - Yearly'!A1:D621", "A3", "HWCounts_PivotTable"); // Get the PivotTable object PivotTable pvtTable = pivotTables[index]; // Add vendor row field pvtTable.AddFieldToArea(PivotFieldType.Row, "Vendor"); // Add item row field pvtTable.AddFieldToArea(PivotFieldType.Row, "Item"); // Add data field pvtTable.AddFieldToArea(PivotFieldType.Data, "2014"); // Turn off the subtotals for the vendor row field PivotField pivotField = pvtTable.RowFields["Vendor"]; pivotField.SetSubtotals(PivotFieldSubtotalType.None, true); // Turn off grand total pvtTable.ColumnGrand = false; /* * Please call the PivotTable.RefreshData() and PivotTable.CalculateData() * before using PivotItem.Position, * PivotItem.PositionInSameParentNode and PivotItem.Move(int count, bool isSameParent). */ pvtTable.RefreshData(); pvtTable.CalculateData(); pvtTable.RowFields["Item"].PivotItems["4H12"].PositionInSameParentNode = 0; pvtTable.RowFields["Item"].PivotItems["DIF400"].PositionInSameParentNode = 3; /* * As a result of using PivotItem.PositionInSameParentNode, * it will change the original sort sequence. * So when you use PivotItem.PositionInSameParentNode in another parent node. * You need call the method named "CalculateData" again. */ pvtTable.CalculateData(); pvtTable.RowFields["Item"].PivotItems["CA32"].PositionInSameParentNode = 1; pvtTable.RowFields["Item"].PivotItems["AAA3"].PositionInSameParentNode = 2; // Save file wb.Save(outputDir + "outputSpecifyAbsolutePositionOfPivotItem.xlsx"); Console.WriteLine("SpecifyAbsolutePositionOfPivotItem executed successfully."); }
public static void Run() { // ExStart:1 //Source directory string sourceDir = RunExamples.Get_SourceDirectory(); string outputDir = RunExamples.Get_OutputDirectory(); Workbook wb = new Workbook(sourceDir + "SamplePivotSort.xlsx"); // Obtaining the reference of the newly added worksheet Worksheet sheet = wb.Worksheets[0]; PivotTableCollection pivotTables = sheet.PivotTables; // source PivotTable // Adding a PivotTable to the worksheet int index = pivotTables.Add("=Sheet1!A1:C10", "E3", "PivotTable2"); //Accessing the instance of the newly added PivotTable PivotTable pivotTable = pivotTables[index]; // Unshowing grand totals for rows. pivotTable.RowGrand = false; pivotTable.ColumnGrand = false; // Dragging the first field to the row area. pivotTable.AddFieldToArea(PivotFieldType.Row, 1); PivotField rowField = pivotTable.RowFields[0]; rowField.IsAutoSort = true; rowField.IsAscendSort = true; // Dragging the second field to the column area. pivotTable.AddFieldToArea(PivotFieldType.Column, 0); PivotField colField = pivotTable.ColumnFields[0]; colField.NumberFormat = "dd/mm/yyyy"; colField.IsAutoSort = true; colField.IsAscendSort = true; // Dragging the third field to the data area. pivotTable.AddFieldToArea(PivotFieldType.Data, 2); pivotTable.RefreshData(); pivotTable.CalculateData(); // end of source PivotTable // sort the PivotTable on "SeaFood" row field values // Adding a PivotTable to the worksheet index = pivotTables.Add("=Sheet1!A1:C10", "E10", "PivotTable2"); // Accessing the instance of the newly added PivotTable pivotTable = pivotTables[index]; // Unshowing grand totals for rows. pivotTable.RowGrand = false; pivotTable.ColumnGrand = false; // Dragging the first field to the row area. pivotTable.AddFieldToArea(PivotFieldType.Row, 1); rowField = pivotTable.RowFields[0]; rowField.IsAutoSort = true; rowField.IsAscendSort = true; // Dragging the second field to the column area. pivotTable.AddFieldToArea(PivotFieldType.Column, 0); colField = pivotTable.ColumnFields[0]; colField.NumberFormat = "dd/mm/yyyy"; colField.IsAutoSort = true; colField.IsAscendSort = true; colField.AutoSortField = 0; //Dragging the third field to the data area. pivotTable.AddFieldToArea(PivotFieldType.Data, 2); pivotTable.RefreshData(); pivotTable.CalculateData(); // end of sort the PivotTable on "SeaFood" row field values // sort the PivotTable on "28/07/2000" column field values // Adding a PivotTable to the worksheet index = pivotTables.Add("=Sheet1!A1:C10", "E18", "PivotTable2"); // Accessing the instance of the newly added PivotTable pivotTable = pivotTables[index]; // Unshowing grand totals for rows. pivotTable.RowGrand = false; pivotTable.ColumnGrand = false; // Dragging the first field to the row area. pivotTable.AddFieldToArea(PivotFieldType.Row, 1); rowField = pivotTable.RowFields[0]; rowField.IsAutoSort = true; rowField.IsAscendSort = true; rowField.AutoSortField = 0; // Dragging the second field to the column area. pivotTable.AddFieldToArea(PivotFieldType.Column, 0); colField = pivotTable.ColumnFields[0]; colField.NumberFormat = "dd/mm/yyyy"; colField.IsAutoSort = true; colField.IsAscendSort = true; // Dragging the third field to the data area. pivotTable.AddFieldToArea(PivotFieldType.Data, 2); pivotTable.RefreshData(); pivotTable.CalculateData(); // end of sort the PivotTable on "28/07/2000" column field values //Saving the Excel file wb.Save(outputDir + "out.xlsx"); PdfSaveOptions options = new PdfSaveOptions(); options.OnePagePerSheet = true; wb.Save(outputDir + "out.pdf", options); // ExEnd:1 Console.WriteLine("PivotTableCustomSort executed successfully."); }