public void CreatePivotTable(string curFileName, string sheetName)
{
using (ExcelPackage ep = new ExcelPackage(new FileInfo(curFileName)))
{
var wsData = ep.Workbook.Worksheets[sheetName];
var tblData = wsData.Tables["tblData"];
var dataCells = wsData.Cells[tblData.Address.Address];
// workbook
var wb = ep.Workbook;
// new worksheet
var ws = wb.Worksheets.Add("Tiempos por cliente");
// default font
// ws.Cells.Style.Font.SetFromFont(font);
// cells borders
ws.View.ShowGridLines = false;
// tab color
ws.TabColor = Color.FromArgb(91, 155, 213);
// columns width
ws.Column(1).Width = 2.5;
ExcelPivotTable pivotTable = ws.PivotTables.Add(ws.Cells["B4"], dataCells, "pvtTiemposPorCliente");
// headers
pivotTable.ShowHeaders = true;
pivotTable.RowHeaderCaption = "Cliente";
// grand total
pivotTable.ColumGrandTotals = true;
pivotTable.GrandTotalCaption = "Total";
// data fields are placed in columns
pivotTable.DataOnRows = false;
// style
pivotTable.TableStyle = OfficeOpenXml.Table.TableStyles.Medium9;
ExcelPivotTableField territoryGroupPageField = pivotTable.PageFields.Add(pivotTable.Fields["Empresa"]);
territoryGroupPageField.Sort = eSortType.Ascending;
ExcelPivotTableField salesPersonRowField = pivotTable.RowFields.Add(pivotTable.Fields["Cliente"]);
ExcelPivotTableDataField revenueDataField = pivotTable.DataFields.Add(pivotTable.Fields["Tiempo"]);
revenueDataField.Function = DataFieldFunctions.Sum;
revenueDataField.Format = string.Format("{0};{1}", "#,##0_)", "(#,##0)");
revenueDataField.Name = "Tiempo";
pivotTable.SortOnDataField(salesPersonRowField, revenueDataField, true);
pivotTable.Top10(salesPersonRowField, revenueDataField, 3, false, false);
//Crear el chart table
CreateChart(ws, pivotTable);
#region TemporalPruebas
//string rootapath = HttpContext.Current.Request.MapPath("~");
//string newFileName = string.Concat(rootapath, @"Temp\Plantilla.xlsx");
FileInfo excelFile = new FileInfo(curFileName);
//if (excelFile.Exists)
// excelFile.Delete();
ep.SaveAs(excelFile);
#endregion
//AdventureWorks8_PivotChart_SalesBySalesperson(ws, pivotTable);
}
}
private int Sort()
{
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;//EEplus license
var file = new FileInfo(Application.StartupPath + $"\\總表{DateTime.Now.ToString("yyyy-MM-dd")}.xlsx");
using (var excel = new ExcelPackage())
{
var ws = excel.Workbook.Worksheets.Add("Sheet1");
ws.Cells["B2"].LoadFromDataTable(ds.Tables[0], true);
/*
* ExcelWorksheet worksheetPivot =excel.Workbook.Worksheets.Add("Pivot");
* var worksheetData = excel.Workbook.Worksheets.Add("Sheet2");
*
* worksheetData.Cells["A1"].Value = "Column A";
* worksheetData.Cells["A2"].Value = "Group A";
* worksheetData.Cells["A3"].Value = "Group B";
* worksheetData.Cells["A4"].Value = "Group C";
* worksheetData.Cells["A5"].Value = "Group A";
* worksheetData.Cells["A6"].Value = "Group B";
* worksheetData.Cells["A7"].Value = "Group C";
* worksheetData.Cells["A8"].Value = "Group A";
* worksheetData.Cells["A9"].Value = "Group B";
* worksheetData.Cells["A10"].Value = "Group C";
* worksheetData.Cells["A11"].Value = "Group D";
*
* worksheetData.Cells["B1"].Value = "Column B";
* worksheetData.Cells["B2"].Value = "emc";
* worksheetData.Cells["B3"].Value = "fma";
* worksheetData.Cells["B4"].Value = "h2o";
* worksheetData.Cells["B5"].Value = "emc";
* worksheetData.Cells["B6"].Value = "fma";
* worksheetData.Cells["B7"].Value = "h2o";
* worksheetData.Cells["B8"].Value = "emc";
* worksheetData.Cells["B9"].Value = "fma";
* worksheetData.Cells["B10"].Value = "h2o";
* worksheetData.Cells["B11"].Value = "emc";
*
* worksheetData.Cells["C1"].Value = "Column C";
* worksheetData.Cells["C2"].Value = 299;
* worksheetData.Cells["C3"].Value = 792;
* worksheetData.Cells["C4"].Value = 458;
* worksheetData.Cells["C5"].Value = 299;
* worksheetData.Cells["C6"].Value = 792;
* worksheetData.Cells["C7"].Value = 458;
* worksheetData.Cells["C8"].Value = 299;
* worksheetData.Cells["C9"].Value = 792;
* worksheetData.Cells["C10"].Value = 458;
* worksheetData.Cells["C11"].Value = 299;
*
* worksheetData.Cells["D1"].Value = "Column D";
* worksheetData.Cells["D2"].Value = 40075;
* worksheetData.Cells["D3"].Value = 31415;
* worksheetData.Cells["D4"].Value = 384400;
* worksheetData.Cells["D5"].Value = 40075;
* worksheetData.Cells["D6"].Value = 31415;
* worksheetData.Cells["D7"].Value = 384400;
* worksheetData.Cells["D8"].Value = 40075;
* worksheetData.Cells["D9"].Value = 31415;
* worksheetData.Cells["D10"].Value = 384400;
* worksheetData.Cells["D11"].Value = 40075;
* //---------------------------------------------------------------------------------------------------------------------------------
* var dataRange = worksheetData.Cells[worksheetData.Dimension.Address];
*
*
* var pivotTable = worksheetPivot.PivotTables.Add(worksheetPivot.Cells["B2"], dataRange, "PivotTable");
*
* //label field
* pivotTable.RowFields.Add(pivotTable.Fields["Column A"]);
* pivotTable.DataOnRows = false;
*
* //data fields
* var field = pivotTable.DataFields.Add(pivotTable.Fields["Column B"]);
* field.Name = "Count of Column B";
* field.Function = DataFieldFunctions.Count;
*
*
* field = pivotTable.DataFields.Add(pivotTable.Fields["Column C"]);
* field.Name = "Sum of Column C";
* field.Function = DataFieldFunctions.Sum;
* field.Format = "0.00";
*
* field = pivotTable.DataFields.Add(pivotTable.Fields["Column D"]);
* field.Name = "Sum of Column D";
* field.Function = DataFieldFunctions.Sum;
* field.Format = "€#,##0.00";
*
*/
//------------------------------------------------------------------------------------------------------------------------------
var dataRange1 = ws.Cells[ws.Dimension.Address];
var pivotTable1 = ws.PivotTables.Add(ws.Cells["X2"], dataRange1, "PivotTable1");
//label field
pivotTable1.RowFields.Add(pivotTable1.Fields["ERROR_DESC"]);
pivotTable1.RowFields.Add(pivotTable1.Fields["ERROR_CODE"]);
pivotTable1.DataOnRows = false;
//data fields
var field1 = pivotTable1.DataFields.Add(pivotTable1.Fields["SERIAL_NUMBER"]);
field1.Name = "Count of SERIAL_NUMBER";
field1.Function = DataFieldFunctions.Count;
//field1 = pivotTable1.DataFields.Add(pivotTable.Fields["Column C"]);
//field1.Name = "Sum of Column C";
//field1.Function = DataFieldFunctions.Sum;
//field1.Format = "0.00";
//field1 = pivotTable.DataFields.Add(pivotTable.Fields["Column D"]);
// field1.Name = "Sum of Column D";
// field1.Function = DataFieldFunctions.Sum;
// field1.Format = "€#,##0.00";
//worksheetPivot.Cells["A1:Z50"].AutoFitColumns
// ws.Cells[ws.Dimension.Address].Sort(6);
//----------------------------------------------------------------------------------------------------
//data Cells
//var wsData = excel.Workbook.Worksheets["Dataa"];
//wsData.Cells.LoadFromDataTable(ds.Tables[0],true);
ExcelRange dataCells = ws.Cells[ws.Dimension.Address];
//pivot table
//ExcelRange pvtLocation = new ExcelPackage(n).Workbook.Worksheets.cell["B2"];
//string pvtName = "Name";
ExcelPivotTable pivotTable = ws.PivotTables.Add(ws.Cells["AB4"], dataCells, "myPvt");
//header
pivotTable.ShowHeaders = true;
pivotTable.RowHeaderCaption = "ERROR";
//grand total
pivotTable.ColumnGrandTotals = true;
pivotTable.GrandTotalCaption = "Total";
//data field are plcae in columns
pivotTable.DataOnRows = false;
//style
pivotTable.TableStyle = OfficeOpenXml.Table.TableStyles.Medium9;
//filter
ExcelPivotTableField TitleField = pivotTable.PageFields.Add(pivotTable.Fields["TEST_GROUP"]);
TitleField.Sort = eSortType.Ascending;
//row:Error
ExcelPivotTableField ErroeCodefiled = pivotTable.RowFields.Add(pivotTable.Fields["ERROR_CODE"]);
ExcelPivotTableField ErroeCodefiled2 = pivotTable.RowFields.Add(pivotTable.Fields["ERROR_DESC"]);
//valure serial number
ExcelPivotTableDataField SerialnumberField = pivotTable.DataFields.Add(pivotTable.Fields["SERIAL_NUMBER"]);
//column
ExcelPivotTableField Dutyfield = pivotTable.ColumnFields.Add(pivotTable.Fields["DUTY_TYPE"]);
SerialnumberField.Function = DataFieldFunctions.Count;
//SerialnumberField.Format = "#,##0_);(#,##0)";
SerialnumberField.Name = "EEEE";
pivotTable.SortOnDataField(ErroeCodefiled, SerialnumberField, true);
/*
* ExcelPivotTableField fields = ErroeCodefiled;
* ExcelPivotTableDataField dataField = SerialnumberField;
*
* bool descending = true;
*
* var xdoc = pivotTable.PivotTableXml;
* var nsm = new XmlNamespaceManager(xdoc.NameTable);
*
* var schemaMain = xdoc.DocumentElement.NamespaceURI;
* if (nsm.HasNamespace("x") == false)
* nsm.AddNamespace("x", schemaMain);
*
* // <x:pivotField sortType="descending">
* var pivotField = xdoc.SelectSingleNode(
* "/x:pivotTableDefinition/x:pivotFields/x:pivotField[position()=" + (fields.Index + 1) + "]",
* nsm
* );
*
*
*
* pivotField.("sortType", (descending ? "descending" : "ascending"));
*
* // <x:autoSortScope>
* var autoSortScope = pivotField.AppendElement(schemaMain, "x:autoSortScope");
*
* // <x:pivotArea>
* var pivotArea = autoSortScope.AppendElement(schemaMain, "x:pivotArea");
*
* // <x:references count="1">
* var references = pivotArea.AppendElement(schemaMain, "x:references");
* references.AppendAttribute("count", "1");
*
* // <x:reference field="4294967294">
* var reference = references.AppendElement(schemaMain, "x:reference");
* // Specifies the index of the field to which this filter refers.
* // A value of -2 indicates the 'data' field.
* // int -> uint: -2 -> ((2^32)-2) = 4294967294
* reference.AppendAttribute("field", "4294967294");
*
* // <x:x v="0">
* var x = reference.AppendElement(schemaMain, "x:x");
* int v = 0;
* foreach (ExcelPivotTableDataField pivotDataField in pivotTable.DataFields)
* {
* if (pivotDataField == dataField)
* {
* x.AppendAttribute("v", v.ToString());
* break;
* }
* v++;
* }
*/
ws.Cells[ws.Dimension.Start.Row, ws.Dimension.Start.Column, ws.Dimension.End.Row, ws.Dimension.End.Column].AutoFitColumns();//寬度自動調整
try
{
excel.SaveAs(file);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Pivot Sort ERROR! || Save File ERROR");
return(1);
}
}
return(0);
}