jdescopingtool/OLD/WorkerService/Helpers/ExcelHelpers.cs

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Reflection;
using OfficeOpenXml;
using OfficeOpenXml.Table;
using Fasterflect;
using OfficeOpenXml.Style;
using WorkerService.Models.Reporting;

namespace WorkerService.Helpers
{
    /// <summary>
    /// Excel generation helper functions
    /// </summary>
    public static class ExcelHelpers
    {
        /// <summary>
        /// Loads the given data into a new table in a new Excel tab
        /// </summary>
        /// <typeparam name="T">Type of data</typeparam>
        /// <param name="workbook">Excel workbook to create new tab in</param>
        /// <param name="data">Data to load</param>
        /// <param name="tabName">Name of new Excel tab to create</param>
        /// <param name="tableName">Name of new Excel table to create</param>
        /// <param name="protect">Whether or not procection should be aded to new tab</param>
        /// <returns>Created tab</returns>
        public static ExcelWorksheet LoadTab<T>(this ExcelWorkbook workbook, IEnumerable<T> data, string tabName = null, string tableName = null, bool protect = false)
        {
            if (string.IsNullOrEmpty(tabName))
            {
                //No override tab name given => lookup from attribute
                if (typeof(T).GetCustomAttribute(typeof(OutputTableAttribute)) is OutputTableAttribute outputTableAttribute)
                {
                    tabName = outputTableAttribute.TabName;
                }
            }

            ExcelWorksheet worksheet = workbook.Worksheets.Add(tabName);
            LoadTable(worksheet.Cells[1, 1], data, tableName);

            if (protect)
            {
                //Set worksheet/tab as protected
                worksheet.Protection.SetPassword("JDE_SCOPING_TOOL_PASS");
            }
            
            return worksheet;
        }

        /// <summary>
        /// Loads the given data into a new table in the given range
        /// </summary>
        /// <typeparam name="T">Type of data</typeparam>
        /// <param name="range">Excel range to load data into</param>
        /// <param name="data">Data to load</param>
        /// <param name="tableName">Name of new Excel table to create</param>
        /// <returns>Created table</returns>
        public static ExcelTable LoadTable<T>(this ExcelRangeBase range, IEnumerable<T> data, string tableName = null, bool? showHeader = null, string headerText = null)
        {
            OutputTableAttribute outputTableAttribute = typeof(T).GetCustomAttribute(typeof(OutputTableAttribute)) as OutputTableAttribute;
            if (string.IsNullOrEmpty(tableName))
            {
                //No override table name given => lookup from attribute
                tableName = outputTableAttribute != null ? outputTableAttribute.TableName : string.Empty;
            }

            if (!showHeader.HasValue)
            {
                //No override show header indicator given => lookup from attribute
                showHeader = outputTableAttribute != null && outputTableAttribute.ShowHeader;
            }

            if (string.IsNullOrEmpty(headerText))
            {
                //No override header text given => lookup from attribute
                headerText = outputTableAttribute != null ? outputTableAttribute.TabName : string.Empty;
            }

            //Try to load column definitions for the given data
            List<OutputColumn> columnDefinitions = new List<OutputColumn>();
            foreach (PropertyInfo propertyInfo in typeof(T).PropertiesWith(Flags.AllMembers, typeof(OutputColumnAttribute)))
            {
                columnDefinitions.Add(new OutputColumn()
                {
                    Name = propertyInfo.Name,
                    Property = propertyInfo,
                    Attribute = propertyInfo.GetCustomAttribute<OutputColumnAttribute>()
                });
            }
            columnDefinitions = columnDefinitions.OrderBy(cd => cd.Attribute.Order).ThenBy(cd => cd.Name).ToList();

            if (columnDefinitions.Count == 0)
            {
                //No column definitions provided => load as generic collection
                range.LoadFromCollection(data, true, TableStyles.Medium1);

                //Find the generated table
                foreach (ExcelTable wsTable in range.Worksheet.Tables)
                {
                    if (wsTable.Address.Table == range.Table)
                    {
                        return wsTable;
                    }
                }

                throw new Exception("failed to find created table in given range");
            }

            ExcelWorksheet ws = range.Worksheet;
            int baseRow = range.Start.Row, baseColumn = range.Start.Column;
            int row, col;
            string[] propertyNames = columnDefinitions.Select(cd => cd.Name).ToArray();

            //Write headers & formats for columns

            if (showHeader.Value)
            {
                ExcelRangeBase mergedHeaderRange = ws.Cells[baseRow, baseColumn, baseRow, baseColumn + columnDefinitions.Count - 1];
                mergedHeaderRange.Value = headerText;
                mergedHeaderRange.Merge = true;
                baseRow++;
            }
            row = baseRow;
            col = baseColumn;
            foreach (OutputColumn outputColumn in columnDefinitions)
            {
                ws.Cells[row, col].Value = outputColumn.Attribute.HeaderText;

                ws.Column(col).Style.WrapText = outputColumn.Attribute.WrapText;
                if (!outputColumn.Attribute.AutoWidth)
                {
                    ws.Column(col).Width = outputColumn.Attribute.Width;
                }

                col++;
            }

            //Format headers
            ExcelRangeBase headerRange = ws.Cells[baseRow + (showHeader.Value ? -1 : 0), baseColumn, baseRow, baseColumn + columnDefinitions.Count - 1];
            headerRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            headerRange.Style.Font.Bold = true;
            headerRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
            headerRange.Style.Fill.BackgroundColor.SetColor(Color.Gainsboro);

            //Write data
            List<object[]> rowDatas = new List<object[]>
            {
                columnDefinitions.Select(cd => cd.Attribute.HeaderText).ToArray()
            };
            rowDatas.AddRange(data.Select(dataItem =>
            {
                //Load values for row
                object[] rowValues = new object[columnDefinitions.Count];
                for (int i = 0; i < propertyNames.Length; i++)
                {
                    rowValues[i] = dataItem.GetPropertyValue(propertyNames[i]);
                }

                return rowValues;
            }));
            ExcelRangeBase dataRange = ws.Cells[baseRow, baseColumn].LoadFromArrays(rowDatas);

            //Set column widths
            col = baseColumn;
            foreach (OutputColumn outputColumn in columnDefinitions)
            {
                if (outputColumn.Attribute.AutoWidth)
                {
                    ws.Column(col).AutoFit();
                    ws.Column(col).Width *= 1.3;
                }

                col++;
            }

            //Format as table
            ExcelTable table = ws.Tables.Add(dataRange, tableName);
            table.TableStyle = TableStyles.Light18;

            int startRow = table.Address.Start.Row;
            int endRow = table.Address.End.Row;
            col = baseColumn;
            foreach (OutputColumn outputColumn in columnDefinitions)
            {
                ws.Cells[startRow, col, endRow, col].Style.Numberformat.Format = outputColumn.Attribute.Format;
                col++;
            }

            return table;
        }
    }
}