public static void Run()
{
// ExStart:ChangingLayoutOfPivotTable
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook object from source excel file
Workbook workbook = new Workbook(dataDir + "pivotTable_sample.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access first pivot table
PivotTable pivotTable = worksheet.PivotTables[0];
// 1 - Show the pivot table in compact form
pivotTable.ShowInCompactForm();
// Refresh the pivot table
pivotTable.RefreshData();
pivotTable.CalculateData();
// Save the output
workbook.Save(dataDir + "CompactForm_out.xlsx");
// 2 - Show the pivot table in outline form
pivotTable.ShowInOutlineForm();
// Refresh the pivot table
pivotTable.RefreshData();
pivotTable.CalculateData();
// Save the output
workbook.Save(dataDir + "OutlineForm_out.xlsx");
// 3 - Show the pivot table in tabular form
pivotTable.ShowInTabularForm();
// Refresh the pivot table
pivotTable.RefreshData();
pivotTable.CalculateData();
// Save the output
workbook.Save(dataDir + "TabularForm_out.xlsx");
// ExEnd:ChangingLayoutOfPivotTable
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
//Create load options
LoadOptions options = new LoadOptions();
//Set ParsingPivotCachedRecords true, default value is false
options.ParsingPivotCachedRecords = true;
//Load the sample Excel file containing pivot table cached records
Workbook wb = new Workbook(sourceDir + "sampleParsingPivotCachedRecordsWhileLoadingExcelFile.xlsx", options);
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Access first pivot table
PivotTable pt = ws.PivotTables[0];
//Set refresh data flag true
pt.RefreshDataFlag = true;
//Refresh and calculate pivot table
pt.RefreshData();
pt.CalculateData();
//Set refresh data flag false
pt.RefreshDataFlag = false;
//Save the output Excel file
wb.Save(outputDir + "outputParsingPivotCachedRecordsWhileLoadingExcelFile.xlsx");
Console.WriteLine("ParsingPivotCachedRecordsWhileLoadingExcelFile executed successfully.");
}
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
}
}//End CustomPivotTableGlobalizationSettings
public static void Run()
{
// ExStart:CustomizePivotTableGlobalSettings
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Load your excel file
Workbook wb = new Workbook(dataDir + "samplePivotTableGlobalizationSettings.xlsx");
//Setting Custom Pivot Table Globalization Settings
wb.Settings.GlobalizationSettings = new CustomPivotTableGlobalizationSettings();
//Hide first worksheet that contains the data of the pivot table
wb.Worksheets[0].IsVisible = false;
//Access second worksheet
Worksheet ws = wb.Worksheets[1];
//Access the pivot table, refresh and calculate its data
PivotTable pt = ws.PivotTables[0];
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = false;
//Pdf save options - save entire worksheet on a single pdf page
PdfSaveOptions options = new PdfSaveOptions();
options.OnePagePerSheet = true;
//Save the output pdf
wb.Save(dataDir + "outputPivotTableGlobalizationSettings.pdf", options);
// ExEnd:CustomizePivotTableGlobalSettings
}
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
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Load source excel file containing sample pivot table
Workbook wb = new Workbook(dataDir + "sample-pivot-table.xlsx");
// Access first worksheet that contains pivot table data
Worksheet dataSheet = wb.Worksheets[0];
// Access cell A3 and sets its data
Cells cells = dataSheet.Cells;
Cell cell = cells["A3"];
cell.PutValue("FooBar");
// Access cell B3, sets its data. We set B3 a very long string which has more than 255 characters
string longStr = "Very long text 1. very long text 2. very long text 3. very long text 4. very long text 5. very long text 6. very long text 7. very long text 8. very long text 9. very long text 10. very long text 11. very long text 12. very long text 13. very long text 14. very long text 15. very long text 16. very long text 17. very long text 18. very long text 19. very long text 20. End of text.";
cell = cells["B3"];
cell.PutValue(longStr);
// Print the length of cell B3 string
Console.WriteLine("Length of original data string: " + cell.StringValue.Length);
// Access cell C3 and sets its data
cell = cells["C3"];
cell.PutValue("closed");
// Access cell D3 and sets its data
cell = cells["D3"];
cell.PutValue("2016/07/21");
// Access the second worksheet that contains pivot table
Worksheet pivotSheet = wb.Worksheets[1];
// Access the pivot table
PivotTable pivotTable = pivotSheet.PivotTables[0];
// IsExcel2003Compatible property tells if PivotTable is compatible for Excel2003 while refreshing PivotTable.
// If it is true, a string must be less than or equal to 255 characters, so if the string is greater than 255 characters,
// it will be truncated. If false, a string will not have the aforementioned restriction. The default value is true.
pivotTable.IsExcel2003Compatible = true;
pivotTable.RefreshData();
pivotTable.CalculateData();
// Check the value of cell B5 of pivot sheet.
// It will be 255 because we have set IsExcel2003Compatible property to true. All the data after 255 characters has been truncated
Cell b5 = pivotSheet.Cells["B5"];
Console.WriteLine("Length of cell B5 after setting IsExcel2003Compatible property to True: " + b5.StringValue.Length);
// Now set IsExcel2003Compatible property to false and again refresh
pivotTable.IsExcel2003Compatible = false;
pivotTable.RefreshData();
pivotTable.CalculateData();
// Now it will print 383 the original length of cell data. The data has not been truncated now.
b5 = pivotSheet.Cells["B5"];
Console.WriteLine("Length of cell B5 after setting IsExcel2003Compatible property to False: " + b5.StringValue.Length);
// Set the row height and column width of cell B5 and also wrap its text
pivotSheet.Cells.SetRowHeight(b5.Row, 100);
pivotSheet.Cells.SetColumnWidth(b5.Column, 65);
Style st = b5.GetStyle();
st.IsTextWrapped = true;
b5.SetStyle(st);
// Save workbook in xlsx format
wb.Save(dataDir + "SpecifyCompatibility_out.xlsx", SaveFormat.Xlsx);
// ExEnd:1
}
private void button1_Click(object sender, EventArgs e)
{
string file_location = @"C:/Users/Ankita/Downloads/Testing2.xlsx";
Workbook workbook = new Workbook(file_location);
try
{
workbook.Worksheets.RemoveAt("Pivot");
}
catch
{
}
Worksheet worksheet = workbook.Worksheets.Add("Pivot");
#region removing hard coding
Worksheet sheet0 = workbook.Worksheets[0];
Worksheet sheet1 = workbook.Worksheets[1];
Cells all_cells = sheet0.Cells;
Cell cell = sheet0.Cells.LastCell;
Cell cell_first = sheet0.Cells.FirstCell;
#region Last cell calculation
int col_last = cell.Column + 1;
int row_last = cell.Row + 1;
#endregion
#region First calculation
int col_first = cell_first.Column + 1;
int row_first = cell_first.Row + 1;
#endregion
#region Calculate column character
int dividend = col_last;
string columnName = String.Empty;
while (dividend > 0)
{
var modulo = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
dividend = (dividend - modulo) / 26;
}
#endregion
//To read the sheet name
string sheetname = sheet0.Name;
#endregion
string datasource = sheetname + "!A4:" + columnName + row_last.ToString();
#region JSON
#endregion
int iPivotIndex = worksheet.PivotTables.Add(datasource, "A3", "PivotTable");
PivotTable pt = worksheet.PivotTables[iPivotIndex];
pt.RowGrand = true;
pt.ColumnGrand = true;
pt.ShowDrill = true;
pt.IsAutoFormat = true;
Cell cell_title = sheet0.Cells["A2"];
Cell cell_filter = sheet0.Cells["A3"];
string title;
title = cell_title.Value.ToString();
string filter;
filter = cell_filter.Value.ToString();
Cell pivot_cell_title = sheet1.Cells["A1"];
Cell pivot_cell_filter = sheet1.Cells["A2"];
pivot_cell_title.PutValue(title);
pivot_cell_filter.PutValue(filter);
int counter = 0;
using (StreamReader r = new StreamReader(@"C:\Users\Ankita\Downloads\AsposePivotDemoSolution-master-master\AsposePivotDemo\Report.json"))
{
string json = r.ReadToEnd();
dynamic array = JsonConvert.DeserializeObject(json);
try
{
if (array["LstReportObjectOnColumn"] != null)
{
foreach (var item in array["LstReportObjectOnColumn"])
{
pt.AddFieldToArea(PivotFieldType.Column, counter++);
}
}
if (array["LstReportObjectOnRow"] != null)
{
foreach (var item in array["LstReportObjectOnRow"])
{
pt.AddFieldToArea(PivotFieldType.Row, counter++);
}
}
if (array["LstReportObjectOnValue"] != null)
{
foreach (var item in array["LstReportObjectOnValue"])
{
pt.AddFieldToArea(PivotFieldType.Data, counter++);
}
}
}
catch
{
}
}
//pt.AddFieldToArea(PivotFieldType.Row, 0);
//pt.AddFieldToArea(PivotFieldType.Row, 1);
//pt.AddFieldToArea(PivotFieldType.Row, 2);
//pt.AddFieldToArea(PivotFieldType.Data,3);
// pt.ColumnHeaderCaption = "Level 1 Category";
// pt.AddFieldToArea(PivotFieldType.Row, 1);
//// pt.RowHeaderCaption = "Description";
//pt.AddFieldToArea(PivotFieldType.Data, 2);
// pt.AddFieldToArea(PivotFieldType.Data, 3);
//PivotField pf = pt.DataFields[0];
// PivotField pf1 = pt.DataFields[1];
//pt.AddFieldToArea(PivotFieldType.Column, pt.DataField);
// PivotField pf1= pt.DataFields[1];
// PivotFieldSubtotalType.Function = ConsolidationFunction.Sum;
// PivotFieldSubtotalType.Function = ConsolidationFunction.Sum;
// pt.RowFields[0].GetSubtotals(PivotFieldSubtotalType.Sum);
// pt.ColumnFields[0].(PivotFieldSubtotalType.Count, true);
#region Globalisation
//Cell cell1 = sheet0.Cells["D1"];
//Cell cell2 = sheet0.Cells["C1"];
// Style style = sheet0.Cells["C1"].GetStyle();
//string currencySymbol = System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencySymbol.ToString();
//style.Custom = "£#,##0;[Red]-£#,##0";
//style.Number = 5;
//string value = cell1.StringValue;
// string value1 = cell2.StringValue;
// if (value == "Spend USD")
// {
// workbook.Settings.LanguageCode = CountryCode.USA;
// workbook.Settings.Region = CountryCode.USA;
// //worksheet.Cells["C1"].SetStyle(style);
// pt.DataFields[1].NumberFormat = "$#,##0.00";
// // pt.DataFields[1].NumberFormat = @"[>999999999]$#\,###\,###\,##0.00;[>99999]$###\,###\,##0.00;$#,###.00";
// }
//if (value1 == "Spend (EUR)")
//{
// workbook.Settings.LanguageCode = CountryCode.France;
// workbook.Settings.Region = CountryCode.France;
// #region Comments
// //System.Threading.Thread.CurrentThread.CurrentCulture.ToString() ="United Kingdom";
// //worksheet.Cells["C1"].SetStyle(style);
// //pt.DataFields[0].NumberFormat= "_-[$€-2]*#.##0,00_-;-[$€-2]*#.##0,00_-;_-[$€-2]*"+"-"+"??_-;_-@_-";
// //Style style = cell2.GetStyle();
// //style.Custom= "_-[$€-2] * #.##0,00_-;-[$€-2] * #.##0,00_-;_-[$€-2] * " + " - " + "??_-;_-@_-";
// // pt.DataFields[0].NumberFormat = @"[>999999999]£#\.###\.###\.##0,00;[>999999]£###\.###\.##0,00;£#.###,00";
// // _ -[$€-2] * #,##0.00_-;-[$€-2] * #,##0.00_-;_-[$€-2] * "-"??_-;_-@_-
// #endregion
// pt.DataFields[0].NumberFormat = "£#,##0.00";
//}
#endregion
pt.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;
#region Protection level
//workbook.Worksheets[0].IsVisible = false;
// Restricting users to delete columns of the worksheet
sheet1.Protection.AllowDeletingColumn = true;
// Restricting users to delete row of the worksheet
sheet1.Protection.AllowDeletingRow = true;
// Restricting users to edit contents of the worksheet
sheet1.Protection.AllowEditingContent = true;
// Restricting users to edit objects of the worksheet
worksheet.Protection.AllowEditingObject = true;
// Restricting users to edit scenarios of the worksheet
sheet1.Protection.AllowEditingScenario = true;
// Restricting users to filter
sheet1.Protection.AllowFiltering = true;
// Allowing users to format cells of the worksheet
sheet1.Protection.AllowFormattingCell = true;
// Allowing users to format rows of the worksheet
sheet1.Protection.AllowFormattingRow = true;
// Allowing users to insert columns in the worksheet
sheet1.Protection.AllowFormattingColumn = true;
// Allowing users to insert hyper links in the worksheet
sheet1.Protection.AllowInsertingHyperlink = true;
// Allowing users to insert rows in the worksheet
sheet1.Protection.AllowInsertingRow = true;
// Allowing users to select locked cells of the worksheet
sheet1.Protection.AllowSelectingLockedCell = true;
// Allowing users to select unlocked cells of the worksheet
sheet1.Protection.AllowSelectingUnlockedCell = true;
// Allowing users to sort
sheet1.Protection.AllowSorting = true;
// Allowing users to use pivot tables in the worksheet
sheet1.Protection.AllowUsingPivotTable = true;
#endregion
Style st = workbook.CreateStyle();
pt.FormatAll(st);
pt.RefreshData();
pt.CalculateData();
workbook.Save(file_location);
}
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.");
}
