| /* | |
| * You may amend and distribute as you like, but don't remove this header! | |
| * | |
| * EPPlus provides server-side generation of Excel 2007 spreadsheets. | |
| * See http://www.codeplex.com/EPPlus for details. | |
| * | |
| * All rights reserved. | |
| * | |
| * EPPlus is an Open Source project provided under the | |
| * GNU General Public License (GPL) as published by the | |
| * Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA | |
| * | |
| * The GNU General Public License can be viewed at http://www.opensource.org/licenses/gpl-license.php | |
| * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html | |
| * | |
| * The code for this project may be used and redistributed by any means PROVIDING it is | |
| * not sold for profit without the author's written consent, and providing that this notice | |
| * and the author's name and all copyright notices remain intact. | |
| * | |
| * All code and executables are provided "as is" with no warranty either express or implied. | |
| * The author accepts no liability for any damage or loss of business that this product may cause. | |
| * | |
| * Code change notes: | |
| * | |
| * Author Change Date | |
| * ****************************************************************************** | |
| * Jan Källman Added 2011-04-18 | |
| *******************************************************************************/ | |
| using System; | |
| using System.Collections.Generic; | |
| using System.Linq; | |
| using System.Text; | |
| using System.IO; | |
| using OfficeOpenXml; | |
| using System.Data.SqlClient; | |
| using OfficeOpenXml.Table.PivotTable; | |
| using OfficeOpenXml.Drawing.Chart; | |
| namespace EPPlusSamples | |
| { | |
| /// <summary> | |
| /// This class shows how to use pivottables | |
| /// </summary> | |
| public static class Sample12 | |
| { | |
| public class SalesDTO | |
| { | |
| public string Title { get; set; } | |
| public string FirstName { get; set; } | |
| public string MiddleName { get; set; } | |
| public string LastName { get; set; } | |
| public string Name | |
| { | |
| get | |
| { | |
| return string.IsNullOrEmpty(MiddleName) ? FirstName + " " + LastName : FirstName + " " + MiddleName + " " + LastName; | |
| } | |
| } | |
| public DateTime OrderDate { get; set; } | |
| public decimal SubTotal { get; set; } | |
| public decimal Tax { get; set; } | |
| public decimal Freight { get; set; } | |
| public decimal Total | |
| { | |
| get | |
| { | |
| return SubTotal + Tax + Freight; | |
| } | |
| } | |
| } | |
| public static string RunSample12(string sqlServerName, DirectoryInfo outputDir) | |
| { | |
| var list = new List<SalesDTO>(); | |
| if (sqlServerName == "") | |
| { | |
| list = GetRandomData(); | |
| } | |
| else | |
| { | |
| list = GetDataFromSQL(sqlServerName); | |
| } | |
| string file = outputDir.FullName + @"\sample12.xlsx"; | |
| if (File.Exists(file)) File.Delete(file); | |
| FileInfo newFile = new FileInfo(file); | |
| using (ExcelPackage pck = new ExcelPackage(newFile)) | |
| { | |
| // get the handle to the existing worksheet | |
| var wsData = pck.Workbook.Worksheets.Add("SalesData"); | |
| var dataRange = wsData.Cells["A1"].LoadFromCollection( | |
| from s in list | |
| orderby s.LastName, s.FirstName | |
| select s, | |
| true, OfficeOpenXml.Table.TableStyles.Medium2); | |
| wsData.Cells[2, 6, dataRange.End.Row, 6].Style.Numberformat.Format = "mm-dd-yy"; | |
| wsData.Cells[2, 7, dataRange.End.Row, 11].Style.Numberformat.Format = "#,##0"; | |
| dataRange.AutoFitColumns(); | |
| var wsPivot = pck.Workbook.Worksheets.Add("PivotSimple"); | |
| var pivotTable1 = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "PerEmploee"); | |
| pivotTable1.RowFields.Add(pivotTable1.Fields[4]); | |
| var dataField = pivotTable1.DataFields.Add(pivotTable1.Fields[6]); | |
| dataField.Format="#,##0"; | |
| pivotTable1.DataOnRows = true; | |
| var chart = wsPivot.Drawings.AddChart("PivotChart", eChartType.Pie, pivotTable1); | |
| chart.SetPosition(1, 0, 4, 0); | |
| chart.SetSize(600, 400); | |
| var wsPivot2 = pck.Workbook.Worksheets.Add("PivotDateGrp"); | |
| var pivotTable2 = wsPivot2.PivotTables.Add(wsPivot2.Cells["A3"], dataRange, "PerEmploeeAndQuarter"); | |
| pivotTable2.RowFields.Add(pivotTable2.Fields["Name"]); | |
| //Add a rowfield | |
| var rowField = pivotTable2.RowFields.Add(pivotTable2.Fields["OrderDate"]); | |
| //This is a date field so we want to group by Years and quaters. This will create one additional field for years. | |
| rowField.AddDateGrouping(eDateGroupBy.Years | eDateGroupBy.Quarters); | |
| //Get the Quaters field and change the texts | |
| var quaterField = pivotTable2.Fields.GetDateGroupField(eDateGroupBy.Quarters); | |
| quaterField.Items[0].Text = "<"; //Values below min date, but we use auto so its not used | |
| quaterField.Items[1].Text = "Q1"; | |
| quaterField.Items[2].Text = "Q2"; | |
| quaterField.Items[3].Text = "Q3"; | |
| quaterField.Items[4].Text = "Q4"; | |
| quaterField.Items[5].Text = ">"; //Values above max date, but we use auto so its not used | |
| //Add a pagefield | |
| var pageField = pivotTable2.PageFields.Add(pivotTable2.Fields["Title"]); | |
| //Add the data fields and format them | |
| dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["SubTotal"]); | |
| dataField.Format = "#,##0"; | |
| dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["Tax"]); | |
| dataField.Format = "#,##0"; | |
| dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["Freight"]); | |
| dataField.Format = "#,##0"; | |
| //We want the datafields to appear in columns | |
| pivotTable2.DataOnRows = false; | |
| pck.Save(); | |
| } | |
| return file; | |
| } | |
| private static List<SalesDTO> GetRandomData() | |
| { | |
| List<SalesDTO> ret = new List<SalesDTO>(); | |
| var firstNames = new string[] {"John", "Gunnar", "Karl", "Alice"}; | |
| var lastNames = new string[] {"Smith", "Johansson", "Lindeman"}; | |
| Random r = new Random(); | |
| for (int i = 0; i < 500; i++) | |
| { | |
| ret.Add( | |
| new SalesDTO() | |
| { | |
| FirstName = firstNames[r.Next(4)], | |
| LastName = lastNames[r.Next(3)], | |
| OrderDate = new DateTime(2002, 1, 1).AddDays(r.Next(1000)), | |
| Title="Sales Representative", | |
| SubTotal = r.Next(100, 10000), | |
| Tax = 0, | |
| Freight = 0 | |
| }); | |
| } | |
| return ret; | |
| } | |
| private static List<SalesDTO> GetDataFromSQL(string sqlServerName) | |
| { | |
| string connectionStr = string.Format(@"server={0};database=AdventureWorks;Integrated Security=true;", sqlServerName); | |
| var ret = new List<SalesDTO>(); | |
| // lets connect to the AdventureWorks sample database for some data | |
| using (SqlConnection sqlConn = new SqlConnection(connectionStr)) | |
| { | |
| sqlConn.Open(); | |
| using (SqlCommand sqlCmd = new SqlCommand("select h.Title, FirstName, MiddleName, LastName, SubTotal, OrderDate, TaxAmt, Freight, TotalDue from Sales.SalesOrderHeader s inner join HumanResources.Employee h on s.SalesPersonID = h.EmployeeID inner join Person.Contact c on c.ContactID = h.ContactID order by LastName, FirstName, MiddleName;", sqlConn)) | |
| { | |
| using (SqlDataReader sqlReader = sqlCmd.ExecuteReader()) | |
| { | |
| //Get the data and fill rows 5 onwards | |
| while (sqlReader.Read()) | |
| { | |
| ret.Add(new SalesDTO | |
| { | |
| Title = sqlReader["Title"].ToString(), | |
| FirstName=sqlReader["FirstName"].ToString(), | |
| MiddleName=sqlReader["MiddleName"].ToString(), | |
| LastName=sqlReader["LastName"].ToString(), | |
| OrderDate = (DateTime)sqlReader["OrderDate"], | |
| SubTotal = (decimal)sqlReader["SubTotal"], | |
| Tax=(decimal)sqlReader["TaxAmt"], | |
| Freight=(decimal)sqlReader["Freight"] | |
| }); | |
| } | |
| } | |
| } | |
| } | |
| return ret; | |
| } | |
| } | |
| } |