{"id":2715,"date":"2019-10-24T13:53:32","date_gmt":"2019-10-24T12:53:32","guid":{"rendered":"https:\/\/solidt.eu\/site\/?p=2715"},"modified":"2022-07-25T08:33:25","modified_gmt":"2022-07-25T07:33:25","slug":"c-create-excel-using-epplus","status":"publish","type":"post","link":"https:\/\/solidt.eu\/site\/c-create-excel-using-epplus\/","title":{"rendered":"C# create excel using EPPlus"},"content":{"rendered":"\n<p><a href=\"https:\/\/www.nuget.org\/packages\/EPPlus\/\">https:\/\/www.nuget.org\/packages\/EPPlus\/<\/a><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"csharp\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">\/\/ using OfficeOpenXml;\n\/\/ using OfficeOpenXml.Style;\n\nusing (var excel = new ExcelPackage())\n{\n\tvar ws = excel.Workbook.Worksheets.Add(\"My worksheet\");\n\tws.DefaultColWidth = 22;\n\tws.Column(2).Width = 25.29;\n\tws.Cells.AutoFitColumns(15, 100); \/\/ Auto fit columns with minimum and maximum\n\n\tvar range = ws.Cells[1, 1, 1, 8]; \/\/ first row 8 columns\n\trange.Style.Font.Bold = true;\n\trange.AutoFilter = true;\n\n\trange.Style.Fill.PatternType = ExcelFillStyle.Solid; \/\/ must be set to apply background color\n\trange.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242));\n\trange.Style.Font.Color.SetColor(Color.White);\t\n\trange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center\n\trange.Style.Numberformat.Format = \"#,##0.00\";\n\n\tws.View.ShowGridLines = false; \/\/ hide excel grid\n\tws.PrinterSettings.Orientation = eOrientation.Landscape;\n\tws.PrinterSettings.PrintArea = ws.Cells[1, 1, 100, 8];\n\tws.PrinterSettings.FitToPage = true;\n\n\ttry\n\t{\n\t\t\/\/ Embed file by using: Properties > Build Action > Embedded Resource\n\t\tusing (Stream imageStream = GetType().Assembly.GetManifestResourceStream(\"NameSpace.FileName.png\")) \n\t\t{\n\t\t\tvar image = Image.FromStream(imageStream);\n\t\t\tvar picture = ws.Drawings.AddPicture(\"Picture\", image);\n\t\t\tpicture.SetPosition(100, 100);\n\t\t}\n\t}\n\tcatch (Exception ex)\n\t{\n\t\tLog.Write(LogLevel.Error, \"Failed to add image to Excel\", ex);\n\t}\n\tvar fileName = \"c:\\temp\\temp.xlsx\";\n\tusing (var stream = File.Open(fileName, FileMode.Create))\n\t{\n\t\texcel.SaveAs(stream);\n\t}\n}<\/pre>\n\n\n\n<p>Update 2020-03-03  Using a Pivot Table:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"csharp\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">\/*\nusing OfficeOpenXml;\nusing OfficeOpenXml.Drawing.Chart;\nusing OfficeOpenXml.Style;\nusing OfficeOpenXml.Table.PivotTable;\n*\/\n\npublic class BusinessUnitActivityResourceFlatData\n{\n\tpublic DateTime Date { get; set; }\n\tpublic string Week { get; set; }\n\tpublic string Month { get; set; }\n\tpublic int? BusinessUnitId { get; set; }\n\tpublic string BusinessUnitName { get; set; }\n\tpublic int? ActivityId { get; set; }\n\tpublic string ActivityDescription { get; set; }\n\tpublic int? ResourceId { get; set; }\n\tpublic string ResourceDescription { get; set; }\n\tpublic double Hours { get; set; }\n}\n\npublic byte[] ExportBusinessUnitActivityResourcePivotTable(string title, IEnumerable&lt;BusinessUnitActivityResourceFlatData> data, BusinessUnitActivityResourceGrouping grouping)\n{\n\tusing (var ep = new ExcelPackage())\n\t{\n\t\tvar showDate = false;\n\t\tvar showWeek = true;\n\t\tvar showActivity = false;\n\n\t\tif (grouping == BusinessUnitActivityResourceGrouping.Day_BusinessUnit_Activity_Resource || \n\t\t\tgrouping == BusinessUnitActivityResourceGrouping.Day_BusinessUnit_Resource)\n\t\t{\n\t\t\tshowDate = true;\n\t\t}\n\n\t\tif (grouping == BusinessUnitActivityResourceGrouping.Day_BusinessUnit_Activity_Resource ||\n\t\t\tgrouping == BusinessUnitActivityResourceGrouping.Week_BusinessUnit_Activity_Resource ||\n\t\t\tgrouping == BusinessUnitActivityResourceGrouping.Month_BusinessUnit_Activity_Resource)\n\t\t{\n\t\t\tshowActivity = true;\n\t\t}\n\n\t\tif (grouping == BusinessUnitActivityResourceGrouping.Month_BusinessUnit_Activity_Resource ||\n\t\t\tgrouping == BusinessUnitActivityResourceGrouping.Month_BusinessUnit_Resource)\n\t\t{\n\t\t\tshowWeek = false;\n\t\t}\n\n\t\tvar wsPivot = ep.Workbook.Worksheets.Add(\"Draaitabel\");\n\t\tvar wsData = ep.Workbook.Worksheets.Add(\"Gegevens\");\n\n\t\twsPivot.Cells[1, 1].Value = title;\n\t\twsPivot.Cells[1, 1].Style.Font.Bold = true;\n\n\t\twsPivot.DefaultColWidth = 20;\n\t\twsData.DefaultColWidth = 20;\n\t\twsData.View.FreezePanes(2, 1);\n\n\t\tvar y = 1;\n\t\tvar x = 1;\n\n\t\tif (showDate)\n\t\t{ \n\t\t\twsData.Cells[y, x++].Value = \"Datum\";\n\t\t}\n\t\tif (showWeek)\n\t\t{ \n\t\t\twsData.Cells[y, x++].Value = \"Week\";\n\t\t}\n\t\twsData.Cells[y, x++].Value = \"Maand\";\n\t\twsData.Cells[y, x++].Value = \"KostenplaatsId\";\n\t\twsData.Cells[y, x++].Value = \"Kostenplaats\";\n\t\tif (showActivity)\n\t\t{ \n\t\t\twsData.Cells[y, x++].Value = \"ActiviteitId\";\n\t\t\twsData.Cells[y, x++].Value = \"Activiteit\";\n\t\t}\n\t\twsData.Cells[y, x++].Value = \"MiddelId\";\n\t\twsData.Cells[y, x++].Value = \"Middel\";\n\t\twsData.Cells[y, x++].Value = \"Uren\";\n\n\t\ty = 2;\n\t\tforeach (var row in data)\n\t\t{\n\t\t\tx = 1;\n\t\t\tif (showDate)\n\t\t\t{ \n\t\t\t\twsData.Cells[y, x++].Value = row.Date.ToString(\"yyyy-MM-dd\");\n\t\t\t}\n\t\t\tif (showWeek)\n\t\t\t{ \n\t\t\t\twsData.Cells[y, x++].Value = row.Week;\n\t\t\t}\n\t\t\twsData.Cells[y, x++].Value = row.Month;\n\t\t\twsData.Cells[y, x++].Value = row.BusinessUnitId;\n\t\t\twsData.Cells[y, x++].Value = row.BusinessUnitName;\n\t\t\tif (showActivity)\n\t\t\t{ \n\t\t\twsData.Cells[y, x++].Value = row.ActivityId;\n\t\t\twsData.Cells[y, x++].Value = row.ActivityDescription;\n\t\t\t}\n\t\t\twsData.Cells[y, x++].Value = row.ResourceId;\n\t\t\twsData.Cells[y, x++].Value = row.ResourceDescription;\n\t\t\twsData.Cells[y, x++].Value = row.Hours;\n\t\t\ty++;\n\t\t}\n\n\t\tvar colCount = x - 1;\n\t\tfor (int i = 1; i &lt; colCount; i++)\n\t\t{\n\t\t\twsData.Column(i).AutoFit(10, 40);\n\t\t}\n\n\t\t\/\/ enable autofilter\n\t\twsData.Cells[1, 1, 1, colCount].Style.Font.Bold = true;\n\t\twsData.Cells[1, 1, y, colCount].AutoFilter = true;\n\n\t\tvar pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells[\"A2\"], wsData.Cells[1, 1, y, colCount], \"Draaitabel\");\n\n\t\tpivotTable.ShowHeaders = true;\n\t\tpivotTable.UseAutoFormatting = true;\n\t\tpivotTable.ApplyWidthHeightFormats = true;\n\t\tpivotTable.ShowDrill = true;\n\t\tpivotTable.FirstHeaderRow = 1;\n\t\tpivotTable.FirstDataRow = 2;\n\t\tpivotTable.FirstDataCol = 1;\n\t\tpivotTable.DataOnRows = false;\n\n\t\tvar groupByRows = new List&lt;string> { \"Middel\" };\n\t\tvar groupByColumns = new List&lt;string> { \"Kostenplaats\" };\n\t\tvar summaryColumns = new List&lt;string> { \"Uren\" };\n\n\t\tforeach (string row in groupByRows)\n\t\t{\n\t\t\tvar field = pivotTable.Fields[row];\n\t\t\tpivotTable.RowFields.Add(field);\n\t\t\tfield.Sort = eSortType.Ascending;\n\t\t}\n\n\t\tforeach (string row in groupByColumns)\n\t\t{\n\t\t\tvar field = pivotTable.Fields[row];\n\t\t\tpivotTable.ColumnFields.Add(field);\n\t\t\tfield.Sort = eSortType.Ascending;\n\t\t}\n\n\t\tforeach (string column in summaryColumns)\n\t\t{\n\t\t\tvar field = pivotTable.Fields[column];\n\t\t\tvar dataField = pivotTable.DataFields.Add(field);\n\t\t\tdataField.Function = DataFieldFunctions.Sum;\n\t\t}\n\n\t\tvar chart = wsPivot.Drawings.AddChart(\"Draaitabel grafiek\", eChartType.Line, pivotTable);\n\t\tchart.SetPosition(36, 0, 1, 0);\n\t\tchart.SetSize(1000, 600);\n\n\t\treturn ep.GetAsByteArray();\n\t}\n}<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>https:\/\/www.nuget.org\/packages\/EPPlus\/ Update 2020-03-03 Using a Pivot Table:<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[6,4,1],"tags":[],"class_list":["post-2715","post","type-post","status-publish","format-standard","hentry","category-dotnet","category-programming","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/2715","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/comments?post=2715"}],"version-history":[{"count":5,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/2715\/revisions"}],"predecessor-version":[{"id":3550,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/2715\/revisions\/3550"}],"wp:attachment":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/media?parent=2715"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/categories?post=2715"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/tags?post=2715"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}