Kit.Core/LibCommon/Kit.Helpers.OpenXml/OpenXml/Excel/ExcelWriter.cs

namespace Kit.Helpers
{
    using System;
    using System.IO;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Collections.Generic;
    using System.Data;

    public class ExcelWriter
    {
        private string ColumnLetter(int intCol)
        {
            var intFirstLetter = ((intCol) / 676) + 64;
            var intSecondLetter = ((intCol % 676) / 26) + 64;
            var intThirdLetter = (intCol % 26) + 65;

            var firstLetter = (intFirstLetter > 64) ? (char)intFirstLetter : ' ';
            var secondLetter = (intSecondLetter > 64) ? (char)intSecondLetter : ' ';
            var thirdLetter = (char)intThirdLetter;

            return string.Concat(firstLetter, secondLetter, thirdLetter).Trim();
        }

        private Cell CreateTextCell(string header, UInt32 index, string text)
        {
            var cell = new Cell
            {
                DataType = CellValues.InlineString,
                CellReference = header + index
            };

            var istring = new InlineString();
            var t = new Text { Text = text };
            istring.AppendChild(t);
            cell.AppendChild(istring);
            return cell;
        }

        public byte[] GenerateExcel(ExcelData data)
        {
            var stream = new MemoryStream();
            using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
            {
                var workbookpart = document.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
                var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();

                worksheetPart.Worksheet = new Worksheet(sheetData);

                var sheets = document.WorkbookPart.Workbook.
                    AppendChild<Sheets>(new Sheets());

                var sheet = new Sheet()
                {
                    Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = data.SheetName ?? "Sheet 1"
                };
                sheets.AppendChild(sheet);

                // Add header
                UInt32 rowIdex = 0;
                var row = new Row { RowIndex = ++rowIdex };
                sheetData.AppendChild(row);
                var cellIdex = 0;

                foreach (var header in data.Headers)
                {
                    row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, header ?? string.Empty));
                }
                if (data.Headers.Count > 0)
                {
                    // Add the column configuration if available
                    if (data.ColumnConfigurations != null)
                    {
                        var columns = (Columns)data.ColumnConfigurations.Clone();
                        worksheetPart.Worksheet
                            .InsertAfter(columns, worksheetPart.Worksheet.SheetFormatProperties);
                    }
                }

                // Add sheet data
                foreach (var rowData in data.DataRows)
                {
                    cellIdex = 0;
                    row = new Row { RowIndex = ++rowIdex };
                    sheetData.AppendChild(row);
                    foreach (var callData in rowData)
                    {
                        var cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, callData ?? string.Empty);
                        row.AppendChild(cell);
                    }
                }

                workbookpart.Workbook.Save();
                document.Save();
            }

            return stream.ToArray();
        }

        public byte[] GenerateExcel(IEnumerable<ExcelData> datas)
        {
            var stream = new MemoryStream();
            using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
            {
                var workbookpart = document.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
                int pos = 0;


                Sheets sheets = document.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                UInt32Value sheetCount = 1; // Mimimum value is 1

                foreach (var data in datas)
                {
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    var sheetData = new SheetData();

                    worksheetPart.Worksheet = new Worksheet(sheetData);
                    var sheet = new Sheet()
                    {
                        Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = sheetCount,
                        Name = data.SheetName ?? $"Sheet {pos++}"
                    };
                    sheets.AppendChild(sheet);

                    sheetCount++;
                    // Add header
                    UInt32 rowIdex = 0;
                    var row = new Row { RowIndex = ++rowIdex };
                    sheetData.AppendChild(row);
                    var cellIdex = 0;

                    foreach (var header in data.Headers)
                    {
                        row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, header ?? string.Empty));
                    }
                    if (data.Headers.Count > 0)
                    {
                        // Add the column configuration if available
                        if (data.ColumnConfigurations != null)
                        {
                            var columns = (Columns)data.ColumnConfigurations.Clone();
                            worksheetPart.Worksheet
                                .InsertAfter(columns, worksheetPart.Worksheet.SheetFormatProperties);
                        }
                    }

                    // Add sheet data
                    foreach (var rowData in data.DataRows)
                    {
                        cellIdex = 0;
                        row = new Row { RowIndex = ++rowIdex };
                        sheetData.AppendChild(row);
                        foreach (var callData in rowData)
                        {
                            var cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, callData ?? string.Empty);
                            row.AppendChild(cell);
                        }
                    }
                }

                workbookpart.Workbook.Save();
                document.Save();
            }

            return stream.ToArray();
        }

        public byte[] GenerateExcel(DataTable dataTable, string sheetName = null)
        {
            var excelData = new ExcelData
            {
                Headers = new List<string>(),
                DataRows = new List<List<string>>(),
                SheetName = sheetName
            };

            foreach (DataColumn column in dataTable.Columns) excelData.Headers.Add(column.ColumnName);

            foreach (DataRow dataRow in dataTable.Rows)
            {
                var excelRow = new List<string>();
                foreach (DataColumn column in dataTable.Columns) excelRow.Add(dataRow[column.ColumnName].ToString());
                excelData.DataRows.Add(excelRow);
            }

            return GenerateExcel(excelData);
        }
    }
}