public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create workbook object from source excel file
Workbook workbook = new Workbook(sourceDir + "sampleChangingLayoutOfPivotTable.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(outputDir + "outputChangingLayoutOfPivotTable_CompactForm.xlsx");
// 2 - Show the pivot table in outline form
pivotTable.ShowInOutlineForm();
// Refresh the pivot table
pivotTable.RefreshData();
pivotTable.CalculateData();
// Save the output
workbook.Save(outputDir + "outputChangingLayoutOfPivotTable_OutlineForm.xlsx");
// 3 - Show the pivot table in tabular form
pivotTable.ShowInTabularForm();
// Refresh the pivot table
pivotTable.RefreshData();
pivotTable.CalculateData();
// Save the output
workbook.Save(outputDir + "outputChangingLayoutOfPivotTable_TabularForm.xlsx");
Console.WriteLine("ChangingLayoutOfPivotTable 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 + "sampleSettingPivotTableOption.xlsx");
PivotTable pt = wb.Worksheets[0].PivotTables[0];
// Indicating if or not display the empty cell value
pt.DisplayNullString = true;
// Indicating the null string
pt.NullString = "null";
pt.CalculateData();
pt.RefreshDataOnOpeningFile = false;
wb.Save(outputDir + "outputSettingPivotTableOption.xlsx");
Console.WriteLine("SettingPivotTableOption executed successfully.");
}
public static void Run()
{
// 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);
}
public static void Run()
{
//Load sample workbook
Workbook wb = new Workbook(sourceDir + "sampleGroupPivotFieldsInPivotTable.xlsx");
//Access the second worksheet
Worksheet ws = wb.Worksheets[1];
//Access the pivot table
PivotTable pt = ws.PivotTables[0];
//Specify the start and end date time
DateTime dtStart = new DateTime(2008, 1, 1); //1-Jan-2018
DateTime dtEnd = new DateTime(2008, 9, 5); //5-Sep-2018
//Specify the group type list, we want to group by months and quarters
ArrayList groupTypeList = new ArrayList();
groupTypeList.Add(PivotGroupByType.Months);
groupTypeList.Add(PivotGroupByType.Quarters);
//Apply the grouping on first pivot field
pt.SetManualGroupField(0, dtStart, dtEnd, groupTypeList, 1);
//Refresh and calculate pivot table
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = false;
//Save the output Excel file
wb.Save(outputDir + "outputGroupPivotFieldsInPivotTable.xlsx");
Console.WriteLine("GroupPivotFieldsInPivotTable executed successfully.");
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Load sample Excel file
Workbook wb = new Workbook(sourceDir + "sampleFindAndRefreshNestedOrChildrenPivotTables.xlsx");
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Access third pivot table
PivotTable ptParent = ws.PivotTables[2];
//Access the children of the parent pivot table
PivotTable[] ptChildren = ptParent.GetChildren();
//Refresh all the children pivot table
int count = ptChildren.Length;
for (int idx = 0; idx < count; idx++)
{
//Access the child pivot table
PivotTable ptChild = ptChildren[idx];
//Refresh the child pivot table
ptChild.RefreshData();
ptChild.CalculateData();
}
Console.WriteLine("FindAndRefreshNestedOrChildrenPivotTables executed successfully.");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook from source excel file
Workbook workbook = new Workbook(dataDir + "Book.xlsx");
// Access the first worksheet of the workbook
Worksheet worksheet = workbook.Worksheets[0];
// Access the first pivot table of the worksheet
PivotTable pivotTable = worksheet.PivotTables[0];
// Apply Average consolidation function to first data field
pivotTable.DataFields[0].Function = ConsolidationFunction.Average;
// Apply DistinctCount consolidation function to second data field
pivotTable.DataFields[1].Function = ConsolidationFunction.DistinctCount;
// Calculate the data to make changes affect
pivotTable.CalculateData();
// Saving the Excel file
workbook.Save(dataDir + "output.xlsx");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// directories
string sourceDir = RunExamples.Get_SourceDirectory();
string outputDir = RunExamples.Get_OutputDirectory();
// Load a template file
Workbook workbook = new Workbook(sourceDir + "PivotTableSample.xlsx");
// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
int pivotIndex = 0;
// Accessing the PivotTable
PivotTable pivotTable = worksheet.PivotTables[pivotIndex];
// Accessing the data fields.
PivotFieldCollection pivotFields = pivotTable.DataFields;
// Accessing the first data field in the data fields.
PivotField pivotField = pivotFields[0];
// Setting data display format
pivotField.DataDisplayFormat = PivotFieldDataDisplayFormat.RankLargestToSmallest;
pivotTable.CalculateData();
// Saving the Excel file
workbook.Save(outputDir + "PivotTableDataDisplayFormatRanking_out.xlsx");
// ExEnd:1
Console.WriteLine("PivotTableDataDisplayFormatRanking executed successfully.");
}
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
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
// Setting the value to the cells
sheet.Range["A1"].Value = "Product";
sheet.Range["B1"].Value = "Month";
sheet.Range["C1"].Value = "Count";
sheet.Range["A2"].Value = "SpireDoc";
sheet.Range["A3"].Value = "SpireDoc";
sheet.Range["A4"].Value = "SpireXls";
sheet.Range["A5"].Value = "SpireDoc";
sheet.Range["A6"].Value = "SpireXls";
sheet.Range["A7"].Value = "SpireXls";
sheet.Range["B2"].Value = "January";
sheet.Range["B3"].Value = "February";
sheet.Range["B4"].Value = "January";
sheet.Range["B5"].Value = "January";
sheet.Range["B6"].Value = "February";
sheet.Range["B7"].Value = "February";
sheet.Range["C2"].Value = "10";
sheet.Range["C3"].Value = "15";
sheet.Range["C4"].Value = "9";
sheet.Range["C5"].Value = "7";
sheet.Range["C6"].Value = "8";
sheet.Range["C7"].Value = "10";
// Adding a PivotTable to the worksheet
CellRange dataRange = sheet.Range["A1:C7"];
PivotCache cache = workbook.PivotCaches.Add(dataRange);
PivotTable pt = sheet.PivotTables.Add("Pivot Table", sheet.Range["C10"], cache);
// Draging the fields to the row area.
PivotField pf = pt.PivotFields["Product"] as PivotField;
pf.Axis = AxisTypes.Row;
PivotField pf2 = pt.PivotFields["Month"] as PivotField;
pf2.Axis = AxisTypes.Row;
//Draging the field to the data area.
pt.DataFields.Add(pt.PivotFields["Count"], "SUM of Count", SubtotalTypes.Sum);
//set PivotTable style
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;
//autofit columns generated by the pivot table
pt.CalculateData();
sheet.AutoFitColumn(3);
sheet.AutoFitColumn(4);
workbook.SaveToFile("Sample.xlsx", ExcelVersion.Version2010);
ExcelDocViewer(workbook.FileName);
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
Workbook wb = new Workbook(dataDir + "input.xlsx");
PivotTable pt = wb.Worksheets[0].PivotTables[0];
//Indicating if or not display the empty cell value
pt.DisplayNullString = true;
//Indicating the null string
pt.NullString = "null";
pt.CalculateData();
pt.RefreshDataOnOpeningFile = false;
wb.Save(dataDir + "output.out.xlsx");
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
Workbook wb = new Workbook(dataDir + "input.xlsx");
PivotTable pt = wb.Worksheets[0].PivotTables[0];
//Indicating if or not display the empty cell value
pt.DisplayNullString = true;
//Indicating the null string
pt.NullString = "null";
pt.CalculateData();
pt.RefreshDataOnOpeningFile = false;
wb.Save(dataDir + "output.xlsx");
}
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
}
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.");
}
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.");
}
private void button1_Click(object sender, EventArgs e)
{
MemoryStream mems = new MemoryStream();
string file_location = @"C:/Users/Ankita/Downloads/subtotals.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 + "!A5:" + columnName + row_last.ToString();
#region JSON
#endregion
int skiprows = 5;
int iPivotIndex = worksheet.PivotTables.Add(datasource, "A" + skiprows.ToString(), "PivotTable");
PivotTable pt = worksheet.PivotTables[iPivotIndex];
pt.RowGrand = true;
pt.ColumnGrand = true;
pt.ShowDrill = true;
pt.IsAutoFormat = true;
//int legendctr = 2;
//while (legendctr < skiprows)
//{
// if (true)
// {
// Cell report_title_cell = sheet0.Cells["A" + legendctr.ToString()];
// string report = report_title_cell.Value.ToString();
// Cell pivot_report_title_cell = sheet1.Cells["A" + legendctr.ToString()];
// legendctr++;
// pivot_report_title_cell.PutValue(report);
// }
// if (true)
// {
// Cell execution_date_cell = sheet0.Cells["A" + legendctr.ToString()];
// string execution = execution_date_cell.Value.ToString();
// Cell pivot_report_title_cell = sheet1.Cells["A" + legendctr.ToString()];
// legendctr++;
// pivot_report_title_cell.PutValue(execution);
// }
// if (true)
// {
// Cell filter_cell = sheet0.Cells["A" + legendctr.ToString()];
// string filter1 = filter_cell.Value.ToString();
// Cell pivot_report_title_cell = sheet1.Cells["A" + legendctr.ToString()];
// legendctr++;
// pivot_report_title_cell.PutValue(filter1);
// }
//}
//// Cell cell_title = sheet0.Cells["A" + 2.ToString()];
// // 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\Report1.json"))
* {
* string json = r.ReadToEnd();
*
* dynamic array = JsonConvert.DeserializeObject(json);
* try
* {
* int i = 0;
* if (array["LstReportObjectOnColumn"] != null)
* {
*
* foreach (var item in array["LstReportObjectOnColumn"])
* {
* pt.AddFieldToArea(PivotFieldType.Column, item["ReportObjectName"].ToString());
* //Console.WriteLine(item["ReportObjectName"]);
* }
*
* }
*
* if (array["LstReportObjectOnRow"] != null)
* {
*
* foreach (var item in array["LstReportObjectOnRow"])
* {
* pt.AddFieldToArea(PivotFieldType.Row, item["ReportObjectName"].ToString());
* //workbook.Worksheets[1].PivotTables[0].RowFields[i++].IsAutoSubtotals = false;
*
* }
*
* }
*
* if (array["LstReportObjectOnValue"] != null)
* {
* foreach (var item in array["LstReportObjectOnValue"])
* {
* pt.AddFieldToArea(PivotFieldType.Data, item["ReportObjectName"].ToString());
* }
* }
* }
* catch (Exception ex)
* {
* ex.ToString();
* }
* }**/
pt.AddFieldToArea(PivotFieldType.Row, 0);
pt.AddFieldToArea(PivotFieldType.Column, 1);
//pt.AddFieldToArea(PivotFieldType.Column, 1);
pt.AddFieldToArea(PivotFieldType.Data, 2);
//pt.DataFields[0].Number = 10;
pt.ColumnFields[0].Number = 14;
//CellArea ca = CellArea.CreateCellArea("A3", "D163");
//DataSorter sorter = workbook.DataSorter;
//int idx = CellsHelper.ColumnNameToIndex("A");
//sorter.AddKey(idx, Aspose.Cells.SortOrder.Ascending);
//sorter.Sort(sheet0.Cells, ca);
// pt.ColumnHeaderCaption = "Level 1 Category";
// 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.DataFields
// 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
//chganss
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.AutoFormatType =PivotTableAutoFormatType.Table10;
// workbook.Worksheets[1].PivotTables[0].RowFields[0].IsAutoSubtotals = false;
pt.ColumnFields[0].IsAutoSubtotals = false;
//pt.RefreshData();
pt.CalculateData();
workbook.Save(file_location);
//workbook.Save(mems, SaveFormat.Xlsx);
//Workbook wb2 = new Workbook(mems);
//Worksheet ws2 = wb2.Worksheets.Add("Pivot123");
//wb2.Save(mems, SaveFormat.Xlsx);
////MemoryStream newms = new MemoryStream();
//// mems.Seek(0, SeekOrigin.Begin);
// mems.Position = 0;
//using (FileStream fs = new FileStream(@"C:/Users/Ankita/Downloads/plswork1.xlsx", FileMode.OpenOrCreate))
//{
// mems.CopyTo(fs);
// fs.Flush();
//}
}
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
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()
{
// 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
}
