コード例 #1
0
        public static byte[] ToExcelWorkbook <T>(this IEnumerable <T> list, ExcelFormattingSettings formattingSettings = null)
        {
            if (formattingSettings == null)
            {
                formattingSettings = ExcelFormattingSettings.Default <T>();
            }

            // Create Excel workbook
            using var wb = new XLWorkbook();
            AddPage(wb, formattingSettings.SheetName, list, formattingSettings);

            // Return as byte array
            return(wb.GetAsByteArray());
        }
コード例 #2
0
        public static byte[] ToExcelPackage <T>(this IEnumerable <T> list, ExcelFormattingSettings formattingSettings = null)
        {
            if (formattingSettings == null)
            {
                formattingSettings = ExcelFormattingSettings.Default <T>();
            }

            // Create Excel package
            using (var p = new ExcelPackage())
            {
                AddPage(p, formattingSettings.SheetName, list, formattingSettings);

                // Return as byte array
                return(p.GetAsByteArray());
            }
        }
コード例 #3
0
        public static byte[] ToExcelWorkbook(this IEnumerable <QueryResult> queryResults, ExcelFormattingSettings formattingSettings = null)
        {
            if (formattingSettings == null)
            {
                formattingSettings = ExcelFormattingSettings.Default("Sheet1");
            }

            // Create Excel package
            using var wb = new XLWorkbook();
            foreach (var result in queryResults)
            {
                AddQueryResultPage(wb, result, formattingSettings);
            }

            // Return as byte array
            return(wb.GetAsByteArray());
        }
コード例 #4
0
        public static byte[] ToExcelPackage(this IEnumerable <QueryResult> queryResults, ExcelFormattingSettings formattingSettings = null)
        {
            if (formattingSettings == null)
            {
                formattingSettings = ExcelFormattingSettings.Default("Sheet1");
            }

            // Create Excel package
            using (var p = new ExcelPackage())
            {
                foreach (var result in queryResults)
                {
                    AddQueryResultPage(p, result, formattingSettings);
                }

                // Return as byte array
                return(p.GetAsByteArray());
            }
        }
コード例 #5
0
        public static byte[] ToExcelPackage <T>(this Dictionary <string, IEnumerable <T> > lists, ExcelFormattingSettings formattingSettings = null)
        {
            if (formattingSettings == null)
            {
                formattingSettings = ExcelFormattingSettings.Default <T>();
            }

            // Create Excel package
            using (var p = new ExcelPackage())
            {
                // Create a sheet
                var ws = p.Workbook.Worksheets.Add(formattingSettings.SheetName);
                foreach (var list in lists)
                {
                    AddPage(p, list.Key, list.Value, formattingSettings);
                }

                // Return as byte array
                return(p.GetAsByteArray());
            }
        }
コード例 #6
0
        private static void AddPage <T>(XLWorkbook workbook, string pageName, IEnumerable <T> data, ExcelFormattingSettings formattingSettings)
        {
            var type  = typeof(T);
            var props = type.GetProperties();

            //Create a sheet
            var ws = workbook.Worksheets.Add(pageName);

            // Default style
            ws.Style.Font.FontSize = formattingSettings.DefaultFont.Size;
            ws.Style.Font.FontName = formattingSettings.DefaultFont.Family;
            ws.Style.Font.Bold     = formattingSettings.DefaultFont.IsBold;
            ws.Style.Font.Italic   = formattingSettings.DefaultFont.IsItalic;

            int iColumn = 1;
            int iRow    = 1;

            // Add headers
            if (formattingSettings.HeaderSettings.AddHeaders)
            {
                //Header font settings
                ws.Row(iRow).Style.Font.FontName = formattingSettings.HeaderSettings.Font.Family;
                ws.Row(iRow).Style.Font.FontSize = formattingSettings.HeaderSettings.Font.Size;
                ws.Row(iRow).Style.Font.Bold     = formattingSettings.HeaderSettings.Font.IsBold;
                ws.Row(iRow).Style.Font.Italic   = formattingSettings.HeaderSettings.Font.IsItalic;

                foreach (var prop in props)
                {
                    var columnSettings = prop.GetCustomAttribute <ColumnSettingsAttribute>();
                    ws.Cell(iRow, iColumn++).Value = columnSettings == null ? prop.Name : columnSettings.Header;
                }

                iRow++;
            }

            // Add data
            foreach (var obj in data)
            {
                iColumn = 1;
                for (int i = 0; i < props.Length; i++)
                {
                    ws.Cell(iRow, iColumn).Value = props[i].GetValue(obj);
                    if (props[i].PropertyType == typeof(DateTime))
                    {
                        ws.Cell(iRow, iColumn).Style.DateFormat.Format = formattingSettings.DateFormat;
                    }
                    iColumn++;
                }
                iRow++;
            }

            // Column widths
            if (formattingSettings.AutoFit)
            {
                ws.Columns().AdjustToContents();
            }

            // Freeze headers
            if (formattingSettings.HeaderSettings.AddHeaders)
            {
                ws.RangeUsed().SetAutoFilter(true);
                ws.SheetView.Freeze(1, 0);
            }
        }
コード例 #7
0
        private static void AddQueryResultPage(XLWorkbook wb, QueryResult result, ExcelFormattingSettings formattingSettings)
        {
            //Create a sheet
            var ws = wb.Worksheets.Add(result.Name);

            // Columns
            int length = result.Columns.Count();

            if (length == 0)
            {
                return;
            }

            // Default style
            ws.Style.Font.FontSize = formattingSettings.DefaultFont.Size;
            ws.Style.Font.FontName = formattingSettings.DefaultFont.Family;
            ws.Style.Font.Bold     = formattingSettings.DefaultFont.IsBold;
            ws.Style.Font.Italic   = formattingSettings.DefaultFont.IsItalic;

            int iColumn = 1;
            int iRow    = 1;
            var columns = result.Columns.ToList();

            // Add headers
            if (formattingSettings.HeaderSettings.AddHeaders)
            {
                //Header font settings
                ws.Row(iRow).Style.Font.FontName = formattingSettings.HeaderSettings.Font.Family;
                ws.Row(iRow).Style.Font.FontSize = formattingSettings.HeaderSettings.Font.Size;
                ws.Row(iRow).Style.Font.Bold     = formattingSettings.HeaderSettings.Font.IsBold;
                ws.Row(iRow).Style.Font.Italic   = formattingSettings.HeaderSettings.Font.IsItalic;

                foreach (var column in result.Columns)
                {
                    var md = result.Metadata.Columns.FirstOrDefault(c => c.Name == column.Name);
                    ws.Cell(iRow, iColumn++).Value = md?.Label ?? column.Name;
                }

                iRow++;
            }

            // Add data
            foreach (IDictionary <string, object> obj in result.Data)
            {
                iColumn = 1;
                for (int i = 0; i < length; i++)
                {
                    ws.Cell(iRow, iColumn).Value = obj[columns[i].Name];
                    if (columns[i].ColumnType?.Equals(typeof(DateTime)) == true)
                    {
                        ws.Cell(iRow, iColumn).Style.DateFormat.Format = formattingSettings.DateFormat;
                    }
                    iColumn++;
                }
                iRow++;
            }

            // Check for totals
            if (result.Metadata.Columns.Any(c => c.HasTotals))
            {
                iRow++;
                ws.Cell(iRow, 1).Value = result.Metadata.TotalsLabel;

                var totals = result.Metadata.Columns.Where(c => c.HasTotals);
                foreach (var total in totals)
                {
                    var columnIndex = columns
                                      .FindIndex(c => c.Name.Equals(total.Name, StringComparison.OrdinalIgnoreCase)) + 1;
                    var columnName  = (char)('A' - 1 + columnIndex);
                    var startingRow = formattingSettings.HeaderSettings.AddHeaders ? 2 : 1;
                    ws.Cell(iRow, columnIndex).FormulaA1 = $"=SUM({columnName}{startingRow}:{columnName}{iRow -2})";
                }

                ws.Row(iRow).Style.Font.Bold = true;
            }

            // Check for fixed rows
            if (result.Metadata.FixedColumns > 0)
            {
                ws.SheetView.FreezeColumns(result.Metadata.FixedColumns);
            }

            // Column widths
            if (formattingSettings.AutoFit)
            {
                ws.Columns().AdjustToContents();
            }

            // Freeze headers
            if (formattingSettings.HeaderSettings.AddHeaders)
            {
                ws.RangeUsed().SetAutoFilter(true);
                ws.SheetView.FreezeRows(1);
            }
        }
コード例 #8
0
        private static void AddPage <T>(ExcelPackage package, string pageName, IEnumerable <T> data, ExcelFormattingSettings formattingSettings)
        {
            var type  = typeof(T);
            var props = type.GetProperties();

            //Create a sheet
            var ws = package.Workbook.Worksheets.Add(pageName);

            // Default style
            ws.Cells.Style.Font.Size   = formattingSettings.DefaultFont.Size;
            ws.Cells.Style.Font.Name   = formattingSettings.DefaultFont.Family;
            ws.Cells.Style.Font.Bold   = formattingSettings.DefaultFont.IsBold;
            ws.Cells.Style.Font.Italic = formattingSettings.DefaultFont.IsItalic;
            int iColumn = 0;


            // Add headers
            if (formattingSettings.HeaderSettings.AddHeaders)
            {
                //Header font settings
                ws.Cells[1, 1, 1, props.Length].Style.Font.Name   = formattingSettings.HeaderSettings.Font.Family;
                ws.Cells[1, 1, 1, props.Length].Style.Font.Size   = formattingSettings.HeaderSettings.Font.Size;
                ws.Cells[1, 1, 1, props.Length].Style.Font.Bold   = formattingSettings.HeaderSettings.Font.IsBold;
                ws.Cells[1, 1, 1, props.Length].Style.Font.Italic = formattingSettings.HeaderSettings.Font.IsItalic;

                foreach (var prop in props)
                {
                    ws.Cells[1, ++iColumn].Value = prop.Name;
                }
            }

            // Add data
            int iRow = 2;

            foreach (var obj in data)
            {
                iColumn = 0;
                for (int i = 0; i < props.Length; i++)
                {
                    ws.Cells[iRow, ++iColumn].Value = props[i].GetValue(obj);
                    if (props[i].PropertyType.Equals(typeof(DateTime)))
                    {
                        ws.Cells[iRow, iColumn].Style.Numberformat.Format = formattingSettings.DateFormat;
                    }
                }
                iRow++;
            }

            // Fix widths
            if (formattingSettings.AutoFit)
            {
                for (int i = 0; i < props.Length; i++)
                {
                    ws.Column(i + 1).AutoFit();
                }
            }

            // Freeze headers
            ws.View.FreezePanes(2, 1);
        }
コード例 #9
0
        private static void AddQueryResultPage(ExcelPackage package, QueryResult result, ExcelFormattingSettings formattingSettings)
        {
            // Columns
            int length = result.Columns.Count();

            //Create a sheet
            var ws = package.Workbook.Worksheets.Add(result.Name);

            // Default style
            ws.Cells.Style.Font.Size   = formattingSettings.DefaultFont.Size;
            ws.Cells.Style.Font.Name   = formattingSettings.DefaultFont.Family;
            ws.Cells.Style.Font.Bold   = formattingSettings.DefaultFont.IsBold;
            ws.Cells.Style.Font.Italic = formattingSettings.DefaultFont.IsItalic;
            int iColumn = 0;

            // Add headers
            if (formattingSettings.HeaderSettings.AddHeaders)
            {
                //Header font settings
                ws.Cells[1, 1, 1, length].Style.Font.Name   = formattingSettings.HeaderSettings.Font.Family;
                ws.Cells[1, 1, 1, length].Style.Font.Size   = formattingSettings.HeaderSettings.Font.Size;
                ws.Cells[1, 1, 1, length].Style.Font.Bold   = formattingSettings.HeaderSettings.Font.IsBold;
                ws.Cells[1, 1, 1, length].Style.Font.Italic = formattingSettings.HeaderSettings.Font.IsItalic;

                foreach (var column in result.Columns)
                {
                    ws.Cells[1, ++iColumn].Value = column.Name;
                }
            }

            // Add data
            int iRow    = 2;
            var columns = result.Columns.ToArray();

            foreach (IDictionary <string, object> obj in result.Data)
            {
                iColumn = 0;
                for (int i = 0; i < length; i++)
                {
                    ws.Cells[iRow, ++iColumn].Value = obj[columns[i].Name];
                    if (columns[i].ColumnType.Equals(typeof(DateTime)))
                    {
                        ws.Cells[iRow, iColumn].Style.Numberformat.Format = formattingSettings.DateFormat;
                    }
                }
                iRow++;
            }

            // Fix widths
            if (formattingSettings.AutoFit)
            {
                for (int i = 0; i < length; i++)
                {
                    ws.Column(i + 1).AutoFit();
                }
            }

            // Freeze headers
            ws.View.FreezePanes(2, 1);
        }