/// <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);