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.");
}
