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