public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create workbook object from source file containing pivot table
Workbook workbook = new Workbook(sourceDir + "sampleFormatPivotTableCells.xlsx");
// Access the worksheet by its name
Worksheet worksheet = workbook.Worksheets[0];
// Access the pivot table
PivotTable pivotTable = worksheet.PivotTables[0];
// Create a style object with background color light blue
Style style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.LightBlue;
// Format entire pivot table with light blue color
pivotTable.FormatAll(style);
// Create another style object with yellow color
style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.Yellow;
// Format the cells of the first row of the pivot table with yellow color
//Format the pivot table cells from H6 to M6
string[] cells_names = new string[] { "H6", "I6", "J6", "K6", "L6", "M6" };
for (int i = 0; i < cells_names.Length; i++)
{
Cell cell = worksheet.Cells[cells_names[i]];
pivotTable.Format(cell.Row, cell.Column, style);
}
// Save the workbook object
workbook.Save(outputDir + "outputFormatPivotTableCells.xlsx");
Console.WriteLine("FormatPivotTableCells executed successfully.\r\n");
}
public static void Main()
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string filePath = dataDir + "pivotTable_test.xlsx";
//Create workbook object from source file containing pivot table
Workbook workbook = new Workbook(filePath);
//Access the worksheet by its name
Worksheet worksheet = workbook.Worksheets["PivotTable"];
//Access the pivot table
PivotTable pivotTable = worksheet.PivotTables[0];
//Create a style object with background color light blue
Style style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.LightBlue;
//Format entire pivot table with light blue color
pivotTable.FormatAll(style);
//Create another style object with yellow color
style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.Yellow;
//Format the cells of the first row of the pivot table with yellow color
for (int col = 0; col < 5; col++)
{
pivotTable.Format(1, col, style);
}
//Save the workbook object
workbook.Save(dataDir + "output.out.xlsx");
//ExEnd:1
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
string filePath = dataDir + "pivotTable_test.xlsx";
//Create workbook object from source file containing pivot table
Workbook workbook = new Workbook(filePath);
//Access the worksheet by its name
Worksheet worksheet = workbook.Worksheets["PivotTable"];
//Access the pivot table
PivotTable pivotTable = worksheet.PivotTables[0];
//Create a style object with background color light blue
Style style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.LightBlue;
//Format entire pivot table with light blue color
pivotTable.FormatAll(style);
//Create another style object with yellow color
style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.Yellow;
//Format the cells of the first row of the pivot table with yellow color
for (int col = 0; col < 5; col++)
{
pivotTable.Format(1, col, style);
}
//Save the workbook object
workbook.Save(dataDir + "output.xlsx");
}
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);
}
