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.");
}
private void button1_Click(object sender, EventArgs e)
{
Workbook workbook = new Workbook("C:/Users/Ankita/Downloads/global.xlsx");
workbook.Worksheets.RemoveAt("Pivot");
Worksheet worksheet = workbook.Worksheets.Add("Pivot");
Worksheet sheet0 = workbook.Worksheets[0];
//Cells cells = sheet0.Cells;
Cell cell = sheet0.Cells.LastCell;
Cell cell_first = sheet0.Cells.FirstCell;
#region Lastcell 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
string sheetname = sheet0.Name;
string datasource = sheetname + "!A3:" + columnName + row_last.ToString();
//string datasource = sheetname + "!A" + row_first.ToString() + ":" + columnName + row_last.ToString();
int iPivotIndex = worksheet.PivotTables.Add(datasource, "A1", "PivotTable");
PivotTable pt = worksheet.PivotTables[iPivotIndex];
pt.RowGrand = true;
pt.ColumnGrand = true;
pt.IsAutoFormat = true;
pt.AddFieldToArea(PivotFieldType.Column, 0);
pt.AddFieldToArea(PivotFieldType.Row, 1);
pt.AddFieldToArea(PivotFieldType.Data, 2);
pt.AddFieldToArea(PivotFieldType.Data, 3);
pt.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark1;
pt.DataFields[0].NumberFormat = @"[>999999999]$#\,###\,###\,##0.00;[>=1000000]$###\,###\,##0.00;$#,###";
pt.DataFields[1].NumberFormat = @"[>999999999]$#\,###\,###\,##0.00;[>=1000000]$###\,###\,##0.00;$#,###";;
//workbook.Worksheets[0].IsVisible = false;
Style st = workbook.CreateStyle();
pt.FormatAll(st);
workbook.Save("C:/Users/Ankita/Downloads/global.xlsx");
}
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.");
}
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();
//}
}
