/// <summary>
/// Call-back handler that builds simple PivatTable in Excel
/// http://stackoverflow.com/questions/11650080/epplus-pivot-tables-charts
/// </summary>
public void CreatePivotTable(OfficeOpenXml.ExcelPackage pkg, string tableName, string pivotRangeName)
{
string pageName = "Pivot-" + tableName.Replace(" ", "");
var wsPivot = pkg.Workbook.Worksheets.Add(pageName);
pkg.Workbook.Worksheets.MoveBefore(PageName, tableName);
var dataRange = pkg.Workbook./*Worksheets[tableName].*/ Names[pivotRangeName];
var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["C3"], dataRange, "Pivot_" + tableName.Replace(" ", ""));
pivotTable.ShowHeaders = true;
pivotTable.UseAutoFormatting = true;
pivotTable.ApplyWidthHeightFormats = true;
pivotTable.ShowDrill = true;
pivotTable.FirstHeaderRow = 1; // first row has headers
pivotTable.FirstDataCol = 1; // first col of data
pivotTable.FirstDataRow = 2; // first row of data
foreach (string row in _GroupByColumns)
{
var field = pivotTable.Fields[row];
pivotTable.RowFields.Add(field);
field.Sort = eSortType.Ascending;
}
foreach (string column in _SummaryColumns)
{
var field = pivotTable.Fields[column];
ExcelPivotTableDataField result = pivotTable.DataFields.Add(field);
}
pivotTable.DataOnRows = false;
}
/// <summary>
/// Call-back handler that builds simple PivotTable in Excel
/// </summary>
public void CreatePivotTable(OfficeOpenXml.ExcelPackage excelPackage, ExcelWorksheet worksheet, string pivotRangeName)
{
string pivotWorksheetName = "Pivot-" + worksheet.Name.Replace(" ", "");
var wsPivot = excelPackage.Workbook.Worksheets.Add(pivotWorksheetName);
excelPackage.Workbook.Worksheets.MoveBefore(pivotWorksheetName, worksheet.Name);
ExcelRange dataRange = worksheet.Cells["A1:" + worksheet.Dimension.End.Address];
var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells[1, 1], dataRange, pivotWorksheetName);
pivotTable.ShowHeaders = true;
pivotTable.UseAutoFormatting = true;
pivotTable.ApplyWidthHeightFormats = true;
pivotTable.ShowDrill = true;
pivotTable.FirstHeaderRow = 1; // first row has headers
pivotTable.FirstDataCol = 1; // first col of data
pivotTable.FirstDataRow = 2; // first row of data
foreach (string row in groupByColumns)
{
var field = pivotTable.Fields[row];
pivotTable.RowFields.Add(field);
field.Sort = eSortType.Ascending;
}
foreach (string column in summaryColumns)
{
var field = pivotTable.Fields[column];
ExcelPivotTableDataField result = pivotTable.DataFields.Add(field);
}
pivotTable.DataOnRows = false;
}
/// <summary>
/// Calculates the total value of a cell.
/// </summary>
/// <param name="dataField">The datafield that the cell is calculated for.</param>
/// <param name="backingData">The data that backs the cell value.</param>
/// <param name="rowTotalType">The type of total function specified by the row used to calculate the cell.</param>
/// <param name="columnTotalType">The type of total function specified by the column used to calculate the cell.</param>
/// <returns>The calculated value.</returns>
public object CalculateCellTotal(ExcelPivotTableDataField dataField, PivotCellBackingData backingData,
string rowTotalType = null, string columnTotalType = null)
{
if (backingData == null)
{
return(null);
}
if (!string.IsNullOrEmpty(rowTotalType) && !rowTotalType.IsEquivalentTo("default"))
{
// Only calculate a value if the row and column functions match up, or if there is no column function specified.
if (string.IsNullOrEmpty(columnTotalType) || rowTotalType.IsEquivalentTo(columnTotalType))
{
// Calculate the value with rowTotalType as the function.
var function = ExcelPivotTableField.SubtotalFunctionTypeToDataFieldFunctionEnum[rowTotalType];
return(this.Calculate(function, backingData.GetBackingValues()));
}
// No value for this cell.
return(null);
}
else if (!string.IsNullOrEmpty(columnTotalType) && !columnTotalType.IsEquivalentTo("default"))
{
// We already know that the row subtotal function type is either empty or default because of the previous condition.
// Calculate the value with columnTotalType as the function.
var function = ExcelPivotTableField.SubtotalFunctionTypeToDataFieldFunctionEnum[columnTotalType];
return(this.Calculate(function, backingData.GetBackingValues()));
}
else if (string.IsNullOrEmpty(backingData.Formula))
{
return(this.Calculate(dataField.Function, backingData.GetBackingValues()));
}
else
{
return(this.EvaluateCalculatedFieldFormula(backingData.GetCalculatedCellBackingValues(), backingData.Formula));
}
}
/// <summary>
/// Writes the grand total for the specified <paramref name="backingData"/> in the cell at the specified <paramref name="index"/>.
/// </summary>
/// <param name="index">The major index of the cell to write the total to.</param>
/// <param name="dataField">The data field to use the number format of.</param>
/// <param name="backingData">The values to use to calculate the total.</param>
protected override void WriteCellTotal(int index, ExcelPivotTableDataField dataField, PivotCellBackingData backingData)
{
var cell = this.PivotTable.Worksheet.Cells[this.PivotTable.Address.End.Row, index];
var styles = this.PivotTable.Worksheet.Workbook.Styles;
base.TotalsCalculator.WriteCellTotal(cell, dataField, backingData, styles);
}
/// <summary>
/// Writes the grand total for the specified <paramref name="backingData"/> in the cell at the specified <paramref name="index"/>.
/// </summary>
/// <param name="index">The major index of the cell to write the total to.</param>
/// <param name="dataField">The data field to use the number format of.</param>
/// <param name="backingData">The data to use to calculate the total.</param>
protected override void WriteCellTotal(int index, ExcelPivotTableDataField dataField, PivotCellBackingData backingData)
{
var cell = this.PivotTable.Worksheet.Cells[index, this.PivotTable.Address.End.Column];
var cacheField = this.PivotTable.CacheDefinition.CacheFields[dataField.Index];
var styles = this.PivotTable.Worksheet.Workbook.Styles;
base.TotalsCalculator.WriteCellTotal(cell, dataField, backingData, styles);
}
internal static void addDataFieldToPivot(ExcelPivotTable pivot, string fieldName, DataFieldFunctions function, string displayName)
{
ExcelPivotTableDataField fieldD = pivot.DataFields.Add(pivot.Fields[fieldName]);
fieldD.Function = function;
if (displayName.Length != 0)
{
fieldD.Name = displayName;
}
}
public ExcelPivotTableFilter AddValueFilter(ePivotTableValueFilterType type, ExcelPivotTableDataField dataField, object value1, object value2 = null)
{
var dfIx = _table.DataFields._list.IndexOf(dataField);
if (dfIx < 0)
{
throw new ArgumentException("This datafield is not in the pivot tables DataFields collection", "dataField");
}
return(AddValueFilter(type, dfIx, value1, value2));
}
/// <summary>
/// Adds a top 10 filter to the field
/// </summary>
/// <param name="type">The top-10 filter type</param>
/// <param name="dataField">The datafield within the pivot table</param>
/// <param name="value">The top or bottom value to relate to </param>
/// <param name="isTop">Top or bottom. true is Top, false is Bottom</param>
/// <returns></returns>
public ExcelPivotTableFilter AddTop10Filter(ePivotTableTop10FilterType type, ExcelPivotTableDataField dataField, double value, bool isTop = true)
{
var dfIx = _table.DataFields._list.IndexOf(dataField);
if (dfIx < 0)
{
throw new ArgumentException("This data field is not in the pivot tables DataFields collection", "dataField");
}
return(AddTop10Filter(type, dfIx, value, isTop));
}
private void WriteCellValue(object value, ExcelRange cell, ExcelPivotTableDataField dataField, ExcelStyles styles)
{
cell.Value = value;
var style = styles.NumberFormats.FirstOrDefault(n => n.NumFmtId == dataField.NumFmtId);
if (style != null)
{
cell.Style.Numberformat.Format = style.Format;
}
}
/// <summary>
/// Calculates and writes the value for a cell into a worksheet.
/// </summary>
/// <param name="cell">The cell to write a value into.</param>
/// <param name="dataField">The data field that the value is under.</param>
/// <param name="backingData">The data used to calculated the cell's value.</param>
/// <param name="styles">The style to apply to the cell.</param>
public void WriteCellTotal(ExcelRange cell, ExcelPivotTableDataField dataField, PivotCellBackingData backingData, ExcelStyles styles)
{
if (backingData == null)
{
return;
}
if (string.IsNullOrEmpty(backingData.Formula))
{
cell.Value = this.Calculate(dataField.Function, backingData.GetBackingValues());
}
else
{
cell.Value = this.EvaluateCalculatedFieldFormula(backingData.GetCalculatedCellBackingValues(), backingData.Formula);
}
var style = styles.NumberFormats.FirstOrDefault(n => n.NumFmtId == dataField.NumFmtId);
if (style != null)
{
cell.Style.Numberformat.Format = style.Format;
}
}
/// <summary>
/// EPPlus doesn't support the Show Data As for pivot data fields. Set the xml for it with this method.
/// </summary>
/// <param name="dataField"></param>
/// <param name="pivot"></param>
/// <param name="showDataAs"></param>
public static void SetDataFieldShowDataAsAttribute(this ExcelPivotTableDataField dataField, ExcelPivotTable pivot, DataFieldShowDataAs showDataAs)
{
if (pivot != null & pivot.DataFields != null && pivot.DataFields.Contains(dataField))
{
string showDataAsAttributeValue = Enum.GetName(typeof(DataFieldShowDataAs), showDataAs);
var xml = pivot.PivotTableXml;
XmlNodeList elements = xml.GetElementsByTagName("dataField");
foreach (XmlNode elem in elements)
{
XmlAttribute fldAttribute = elem.Attributes["fld"];
if (fldAttribute != null && fldAttribute.Value == dataField.Index.ToString())
{
XmlAttribute showDataAsAttribute = elem.Attributes["showDataAs"];
if (showDataAsAttribute == null)
{
showDataAsAttribute = xml.CreateAttribute("showDataAs");
elem.Attributes.InsertAfter(showDataAsAttribute, fldAttribute);
}
showDataAsAttribute.Value = showDataAsAttributeValue;
}
}
}
}
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);
}
/// <summary> /// Writes the grand total for the specified <paramref name="backingData"/> in the cell at the specified <paramref name="index"/>. /// </summary> /// <param name="index">The major index of the cell to write the total to.</param> /// <param name="dataField">The data field to use the number format of.</param> /// <param name="backingData">The values to use to calculate the total.</param> protected abstract void WriteCellTotal(int index, ExcelPivotTableDataField dataField, PivotCellBackingData backingData);
