สร้างไฟล์ Excel ด้วย C#, NPOI และคลาส NpoiExport

Program.cs

using NPOI.HSSF.UserModel;
using System;
using System.Data;
using System.IO;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            var sheet1 = workbook.CreateSheet("Sheet1");
            var sheet2 = workbook.CreateSheet("Sheet2");

            DataTable dt = new DataTable();
            dt.Columns.Add("ชื่อ");
            dt.Columns.Add("Email");
            DataRow row1 = dt.NewRow();
            row1["ชื่อ"] = "Jack";
            row1["Email"] = "mr.phaisarn@gmail.com";
            dt.Rows.Add(row1);
            DataRow row2 = dt.NewRow();
            row2["ชื่อ"] = "Example";
            row2["Email"] = "example@gmail.com";
            dt.Rows.Add(row2);

            string fixedWidthColumn = "0-4000,1-4000,2-6000";
            var exporter = new NpoiExport(fixedWidthColumn);
            exporter.ExportDataTableToWorkbook(dt, "Export_Data");
            exporter.ExportDataTableToWorkbook(dt, "Export_Data2");

            string localFullPath = AppDomain.CurrentDomain.BaseDirectory;
            FileStream FileStreamObject = new System.IO.FileStream(localFullPath + @"Export_Excel.xls", FileMode.Create, FileAccess.Write);
            FileStreamObject.Write(exporter.GetBytes(), 0, exporter.GetBytes().Length);
            FileStreamObject.Close();
        }
    }
}

NpoiExport.cs

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace ConsoleApp1
{
    class NpoiExport
    {
        const int MaximumNumberOfRowsPerSheet = 65500;
        const int MaximumSheetNameLength = 25;
        protected IWorkbook Workbook { get; set; }
        protected Dictionary<int, int> FixColumnExcel { get; set; }


        public NpoiExport(string configColumnExcel)
        {
            this.Workbook = new HSSFWorkbook();
            if (!string.IsNullOrEmpty(configColumnExcel))
            {
                var arr = configColumnExcel.Split(new char[] { ',' });
                if (arr != null && arr.Length > 0)
                {
                    this.FixColumnExcel = new Dictionary<int, int>();
                    foreach (string map in arr)
                    {
                        var _arr = map.Split(new char[] { '-' });
                        this.FixColumnExcel.Add(Convert.ToInt32(_arr[0]), Convert.ToInt32(_arr[1]));
                    }
                }
            }
        }

        protected string EscapeSheetName(string sheetName)
        {
            var escapedSheetName = sheetName
                                        .Replace("/", "-")
                                        .Replace("\\", " ")
                                        .Replace("?", string.Empty)
                                        .Replace("*", string.Empty)
                                        .Replace("[", string.Empty)
                                        .Replace("]", string.Empty)
                                        .Replace(":", string.Empty);

            if (escapedSheetName.Length > MaximumSheetNameLength)
                escapedSheetName = escapedSheetName.Substring(0, MaximumSheetNameLength);

            return escapedSheetName;
        }

        protected ISheet CreateExportDataTableSheetAndHeaderRow(DataTable exportData, string sheetName, ICellStyle headerRowStyle)
        {
            var sheet = this.Workbook.CreateSheet(EscapeSheetName(sheetName));

            // Create the header row
            var row = sheet.CreateRow(0);

            for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
            {
                var cell = row.CreateCell(colIndex);
                cell.SetCellValue(exportData.Columns[colIndex].ColumnName);
                if (headerRowStyle != null)
                {
                    cell.CellStyle = headerRowStyle;
                }

            }
            return sheet;
        }

        public void ExportDataTableToWorkbook(DataTable exportData, string sheetName)
        {
            // Create the header row cell style
            var headerLabelCellStyle = this.Workbook.CreateCellStyle();
            headerLabelCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            headerLabelCellStyle.Alignment = HorizontalAlignment.Center;

            var headerLabelFont = this.Workbook.CreateFont();
            headerLabelFont.Boldweight = (short)FontBoldWeight.Bold;
            headerLabelCellStyle.SetFont(headerLabelFont);
            //headerLabelCellStyle.FillBackgroundColor = 

            var sheet = CreateExportDataTableSheetAndHeaderRow(exportData, sheetName, headerLabelCellStyle);
            var currentNPOIRowIndex = 1;
            var sheetCount = 1;

            //if (this.FixColumnExcel != null && this.FixColumnExcel.Count > 0)
            //{
            //    foreach (var map in FixColumnExcel)
            //    {
            //        sheet.SetColumnWidth(map.Key, map.Value);
            //    }
            //}

            for (var rowIndex = 0; rowIndex < exportData.Rows.Count; rowIndex++)
            {
                if (currentNPOIRowIndex >= MaximumNumberOfRowsPerSheet)
                {
                    sheetCount++;
                    currentNPOIRowIndex = 1;

                    sheet = CreateExportDataTableSheetAndHeaderRow(exportData,
                                                                   sheetName + " - " + sheetCount,
                                                                   headerLabelCellStyle);
                }

                var row = sheet.CreateRow(currentNPOIRowIndex++);

                for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
                {
                    var cell = row.CreateCell(colIndex);
                    cell.SetCellValue(exportData.Rows[rowIndex][colIndex].ToString());

                    if (rowIndex == exportData.Rows.Count - 1)
                    {
                        sheet.AutoSizeColumn(colIndex);
                    }
                }
            }
        }

        protected ISheet UploadDataCreateExportDataTableSheetAndHeaderRow(DataTable exportData, string sheetName, ICellStyle headerRowStyle, ICellStyle TopDataRowStyle, ICellStyle DataCellStyle)
        {
            var sheet = this.Workbook.CreateSheet(EscapeSheetName(sheetName));

            for (var rowIndex = 0; rowIndex < 4; rowIndex++)
            {
                // Create Data Top
                var row = sheet.CreateRow(rowIndex);
                for (var colIndex = 0; colIndex < 4; colIndex++)
                {
                    var cell = row.CreateCell(colIndex);
                    if (rowIndex == 0 && colIndex == 0)
                    {
                        cell.SetCellValue("Visit Event:");
                        cell.CellStyle = TopDataRowStyle;
                    }
                    else if (rowIndex == 0 && colIndex == 1)
                    {
                        cell.SetCellValue(exportData.Rows[0]["Upload_Date"].ToString());
                        cell.CellStyle = DataCellStyle;
                    }
                    else if (rowIndex == 1 && colIndex == 0)
                    {
                        cell.SetCellValue("Visit Event:");
                        cell.CellStyle = TopDataRowStyle;
                    }
                    else if (rowIndex == 1 && colIndex == 1)
                    {
                        cell.SetCellValue(exportData.Rows[0]["Visit_Event_Name"].ToString());
                        cell.CellStyle = DataCellStyle;
                    }
                    else if (rowIndex == 1 && colIndex == 2)
                    {
                        cell.SetCellValue("Upload Total:");
                        cell.CellStyle = TopDataRowStyle;
                    }
                    else if (rowIndex == 1 && colIndex == 3)
                    {
                        cell.SetCellValue(exportData.Rows[0]["Data_Rows_Total"].ToString());
                        cell.CellStyle = DataCellStyle;
                    }
                    else if (rowIndex == 2 && colIndex == 0)
                    {
                        cell.SetCellValue("Visit Event Start Date:");
                        cell.CellStyle = TopDataRowStyle;
                    }
                    else if (rowIndex == 2 && colIndex == 1)
                    {
                        cell.SetCellValue(exportData.Rows[0]["Visit_Event_Start_Date"].ToString());
                        cell.CellStyle = DataCellStyle;
                    }
                    else if (rowIndex == 2 && colIndex == 2)
                    {
                        cell.SetCellValue("Upload Success:");
                        cell.CellStyle = TopDataRowStyle;
                    }
                    else if (rowIndex == 2 && colIndex == 3)
                    {
                        cell.SetCellValue(exportData.Rows[0]["Data_Upload_Success_Total"].ToString());
                        cell.CellStyle = DataCellStyle;
                    }
                    else if (rowIndex == 3 && colIndex == 0)
                    {
                        cell.SetCellValue("Visit Event Finish Date:");
                        cell.CellStyle = TopDataRowStyle;
                    }
                    else if (rowIndex == 3 && colIndex == 1)
                    {
                        cell.SetCellValue(exportData.Rows[0]["Visit_Event_Finish_Date"].ToString());
                        cell.CellStyle = DataCellStyle;
                    }
                    else if (rowIndex == 3 && colIndex == 2)
                    {
                        cell.SetCellValue("Upload Error:");
                        cell.CellStyle = TopDataRowStyle;
                    }
                    else if (rowIndex == 3 && colIndex == 3)
                    {
                        cell.SetCellValue(exportData.Rows[0]["Data_Upload_Error_Total"].ToString());
                        cell.CellStyle = DataCellStyle;
                    }
                }
            }

            // Create the header row
            var rowHeader = sheet.CreateRow(5);

            //Fix Column, Select more than use
            for (var colIndexHeader = 0; colIndexHeader < 10; colIndexHeader++)
            {
                var cellHeader = rowHeader.CreateCell(colIndexHeader);
                if (colIndexHeader == 0)
                {
                    cellHeader.SetCellValue("No.");
                }
                else if (colIndexHeader == 1)
                {
                    cellHeader.SetCellValue("Title Name");
                }
                else if (colIndexHeader == 2)
                {
                    cellHeader.SetCellValue("First Name");
                }
                else if (colIndexHeader == 3)
                {
                    cellHeader.SetCellValue("Last Name");
                }
                else if (colIndexHeader == 4)
                {
                    cellHeader.SetCellValue("Nick Name");
                }
                else if (colIndexHeader == 5)
                {
                    cellHeader.SetCellValue("Agency Name");
                }
                else if (colIndexHeader == 6)
                {
                    cellHeader.SetCellValue("Telephone Number");
                }
                else if (colIndexHeader == 7)
                {
                    cellHeader.SetCellValue("E-mail");
                }
                else if (colIndexHeader == 8)
                {
                    cellHeader.SetCellValue("Error Flag");
                }
                else if (colIndexHeader == 9)
                {
                    cellHeader.SetCellValue("Error Detail");
                }


                if (headerRowStyle != null)
                {
                    cellHeader.CellStyle = headerRowStyle;
                }

            }

            return sheet;
        }

        public void UploadDataExportDataTableToWorkbook(DataTable exportData, string sheetName)
        {
            // Create the header row cell style
            var headerLabelCellStyle = this.Workbook.CreateCellStyle();
            headerLabelCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; ;
            headerLabelCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; ;
            headerLabelCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; ;
            headerLabelCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; ;
            headerLabelCellStyle.Alignment = HorizontalAlignment.Center;

            // Create the Data Top cell style
            var DataTopLabelCellStyle = this.Workbook.CreateCellStyle();
            DataTopLabelCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; ;
            DataTopLabelCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; ;
            DataTopLabelCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; ;
            DataTopLabelCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; ;

            var headerLabelFont = this.Workbook.CreateFont();
            headerLabelFont.Boldweight = (short)FontBoldWeight.Bold;

            headerLabelCellStyle.SetFont(headerLabelFont);
            DataTopLabelCellStyle.SetFont(headerLabelFont);

            // Create the Data Top cell style
            var DataLabelCellStyle = this.Workbook.CreateCellStyle();
            DataLabelCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; ;
            DataLabelCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; ;
            DataLabelCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; ;
            DataLabelCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; ;

            var sheet = UploadDataCreateExportDataTableSheetAndHeaderRow(exportData, sheetName, headerLabelCellStyle, DataTopLabelCellStyle, DataLabelCellStyle);
            var currentNPOIRowIndex = 6;
            var sheetCount = 1;

            for (var rowIndex = 0; rowIndex < exportData.Rows.Count; rowIndex++)
            {
                //Data Rows > 65500, New Sheet
                if (currentNPOIRowIndex >= MaximumNumberOfRowsPerSheet)
                {
                    sheetCount++;
                    currentNPOIRowIndex = 1;

                    sheet = CreateExportDataTableSheetAndHeaderRow(exportData,
                                                                   sheetName + " - " + sheetCount,
                                                                   headerLabelCellStyle);
                }

                //Open Rows
                var row = sheet.CreateRow(currentNPOIRowIndex++);

                for (var colIndex = 0; colIndex < 10; colIndex++)
                {
                    var cell = row.CreateCell(colIndex);

                    if (colIndex == 0)
                    {
                        cell.SetCellValue(exportData.Rows[rowIndex]["Rows_No"].ToString());
                    }
                    else if (colIndex == 1)
                    {
                        cell.SetCellValue(exportData.Rows[rowIndex]["Title_Name"].ToString());
                    }
                    else if (colIndex == 2)
                    {
                        cell.SetCellValue(exportData.Rows[rowIndex]["First_Name"].ToString());
                    }
                    else if (colIndex == 3)
                    {
                        cell.SetCellValue(exportData.Rows[rowIndex]["Last_Name"].ToString());
                    }
                    else if (colIndex == 4)
                    {
                        cell.SetCellValue(exportData.Rows[rowIndex]["Nick_Name"].ToString());
                    }
                    else if (colIndex == 5)
                    {
                        cell.SetCellValue(exportData.Rows[rowIndex]["Agency_Name"].ToString());
                    }
                    else if (colIndex == 6)
                    {
                        cell.SetCellValue(exportData.Rows[rowIndex]["Telephone_No"].ToString());
                    }
                    else if (colIndex == 7)
                    {
                        cell.SetCellValue(exportData.Rows[rowIndex]["Email"].ToString());
                    }
                    else if (colIndex == 8)
                    {
                        cell.SetCellValue(exportData.Rows[rowIndex]["Error_Flag"].ToString());
                    }
                    else if (colIndex == 9)
                    {
                        cell.SetCellValue(exportData.Rows[rowIndex]["Error_Description"].ToString());
                    }

                    cell.CellStyle = DataLabelCellStyle;

                    //Last Rows Set AutoSizeColumn
                    if (rowIndex == exportData.Rows.Count - 1)
                    {
                        sheet.AutoSizeColumn(colIndex);
                    }
                }
            }
        }

        public byte[] GetBytes()
        {
            using (var buffer = new MemoryStream())
            {
                this.Workbook.Write(buffer);
                return buffer.GetBuffer();
            }
        }

        public MemoryStream GetStream()
        {
            var buffer = new MemoryStream();
            this.Workbook.Write(buffer);
            return buffer;
        }

        public void Dispose()
        {
            if (this.Workbook != null)
                this.Workbook.Dispose();
        }

    }
}