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 + "sampleGetPivotTableCellByDisplayName.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access first pivot table inside the worksheet
PivotTable pivotTable = worksheet.PivotTables[0];
// Access cell by display name of 2nd data field of the pivot table
Cell cell = pivotTable.GetCellByDisplayName(pivotTable.DataFields[0].DisplayName);
// Access cell style and set its fill color and font color
Style style = cell.GetStyle();
style.BackgroundColor = Color.LightGreen;
style.Font.Color = Color.Black;
// Set the style of the cell
pivotTable.Format(cell.Row, cell.Column, style);
// Save workbook
workbook.Save(outputDir + "outputGetPivotTableCellByDisplayName.xlsx");
Console.WriteLine("GetPivotTableCellByDisplayName executed successfully.");
}
public static void Run()
{
// ExStart:GetCellObjectByDisplayName
// 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 + "source.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access first pivot table inside the worksheet
PivotTable pivotTable = worksheet.PivotTables[0];
// Access cell by display name of 2nd data field of the pivot table
Cell cell = pivotTable.GetCellByDisplayName(pivotTable.DataFields[0].DisplayName);
// Access cell style and set its fill color and font color
Style style = cell.GetStyle();
style.ForegroundColor = Color.LightBlue;
style.Font.Color = Color.Black;
// Set the style of the cell
pivotTable.Format(cell.Row, cell.Column, style);
// Save workbook
workbook.Save(dataDir + "output_out.xlsx");
// ExEnd:GetCellObjectByDisplayName
}
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");
}
public override string Export(string json)
{
string fileName = "";
try
{
if (string.IsNullOrEmpty(json))
{
throw new KMJXCException("没有销售数据，不能导出");
}
JArray jObject = JArray.Parse(json);
if (jObject == null || jObject.Count() == 0)
{
throw new KMJXCException("没有销售数据，不能导出");
}
Worksheet sheet = (Worksheet)this.WorkBook.ActiveSheet;
sheet.Name = "销售报表";
int startRow = 2;
object[,] os = new object[jObject.Count, 6];
for (int i = 0; i < jObject.Count(); i++)
{
JObject obj = (JObject)jObject[i];
string productName = obj["ProductName"].ToString();
string propName = obj["PropName"].ToString();
string shopName = obj["ShopName"].ToString();
string month = obj["Month"].ToString();
string quantity = obj["Quantity"].ToString();
string amount = obj["Amount"].ToString();
os[i, 0] = productName;
os[i, 1] = propName;
os[i, 2] = shopName;
os[i, 3] = month;
os[i, 4] = quantity;
os[i, 5] = amount;
}
Range range1 = sheet.Cells[startRow, 1];
Range range2 = sheet.Cells[startRow + jObject.Count - 1, 6];
Range range = sheet.get_Range(range1, range2);
range.Value2 = os;
Worksheet pivotTableSheet = (Worksheet)this.WorkBook.Worksheets[2];
pivotTableSheet.Name = "销售透视表";
PivotCaches pch = WorkBook.PivotCaches();
sheet.Activate();
pch.Add(XlPivotTableSourceType.xlDatabase, "'" + sheet.Name + "'!A1:'" + sheet.Name + "'!F" + (jObject.Count() + 1)).CreatePivotTable(pivotTableSheet.Cells[4, 1], "PivTbl_1", Type.Missing, Type.Missing);
PivotTable pvt = pivotTableSheet.PivotTables("PivTbl_1") as PivotTable;
pvt.Format(XlPivotFormatType.xlTable1);
pvt.TableStyle2 = "PivotStyleLight16";
pvt.InGridDropZones = true;
foreach (PivotField pf in pvt.PivotFields() as PivotFields)
{
pf.ShowDetail = false;
}
PivotField productField = (PivotField)pvt.PivotFields("产品");
productField.Orientation = XlPivotFieldOrientation.xlRowField;
productField.set_Subtotals(1, false);
PivotField propField = (PivotField)pvt.PivotFields("属性");
propField.Orientation = XlPivotFieldOrientation.xlRowField;
propField.set_Subtotals(1, false);
PivotField shopField = (PivotField)pvt.PivotFields("店铺");
shopField.Orientation = XlPivotFieldOrientation.xlRowField;
shopField.set_Subtotals(1, false);
PivotField monthField = (PivotField)pvt.PivotFields("年月");
monthField.Orientation = XlPivotFieldOrientation.xlRowField;
monthField.set_Subtotals(1, false);
pvt.AddDataField(pvt.PivotFields(5), "销量", XlConsolidationFunction.xlSum);
pvt.AddDataField(pvt.PivotFields(6), "销售额", XlConsolidationFunction.xlSum);
((PivotField)pvt.DataFields["销量"]).NumberFormat = "#,##0";
((PivotField)pvt.DataFields["销售额"]).NumberFormat = "#,##0";
pivotTableSheet.Activate();
this.WorkBook.Saved = true;
this.WorkBook.SaveCopyAs(this.ReportFilePath);
this.WorkBook.Close();
}
catch (Exception ex)
{
throw ex;
}
return(fileName);
}