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

ติดตั้ง Package
สร้างไฟล์ Excel เปล่า
ใส่ข้อความลงฟิลด์
ใส่สไตล์ให้ฟิลด์
แยกเป็นฟังก์ชัน
แยกเป็นคลาส

1. ติดตั้ง Package

NPOI

PM> Install-Package NPOI -Version 2.4.1

2.สร้างไฟล์ Excel เปล่า

สร้าง Workbook และ worksheet ชื่อ Sheet1 และ Sheet2 จากนั้นสั่ง save ที่ Workbook

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

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

            string filename = @"d:\Book.xls";
            using (var fileData = new FileStream(filename, FileMode.Create))
            {
                workbook.Write(fileData);
            }
        }
    }
}

ไฟล์ที่ได้จะนามสกุล .xls

3.ใส่ข้อความลงฟิลด์

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

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

            var rowIndex = 0;
            var row = sheet1.CreateRow(rowIndex);
            row.CreateCell(0).SetCellValue("Username");
            row.CreateCell(1).SetCellValue("Email");
            rowIndex++;

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

            foreach (DataRow dr in dt.Rows)
            {
                row = sheet1.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue(dr["Name"].ToString());
                row.CreateCell(1).SetCellValue(dr["Email"].ToString());
                rowIndex++;
            }

            string filename = @"d:\Book.xls";
            using (var fileData = new FileStream(filename, FileMode.Create))
            {
                workbook.Write(fileData);
            }
        }
    }
}

บรรทัดที่ 16-18 สร้าง row แล้วกำหนดข้อมูลให้แต่ละคอลัมน์

บรรทัดที่ 21-31 สร้าง DataTable

บรรทัดที่ 33 นำข้อมูลจาก DataTable มาใส่ลงฟิลด์

4.ใส่สไตล์ให้ฟิลด์

กำหนดตัวหนา เส้นบน เส้นล่าง

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
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");

            var rowIndex = 0;
            var row = sheet1.CreateRow(rowIndex);
            row.CreateCell(0).SetCellValue("Username");
            row.CreateCell(1).SetCellValue("Email");
            rowIndex++;

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

            foreach (DataRow dr in dt.Rows)
            {
                row = sheet1.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue(dr["Name"].ToString());
                row.CreateCell(1).SetCellValue(dr["Email"].ToString());
                rowIndex++;
            }

            // Create the style object
            var customStyle = workbook.CreateCellStyle();
            // Define a thin border for the top and bottom of the cell
            customStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            customStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            // Create a font object and make it bold
            var customFont = workbook.CreateFont();
            customFont.Boldweight = (short)FontBoldWeight.Bold;
            customStyle.SetFont(customFont);

            // Add a row for the detail row
            row = sheet1.CreateRow(rowIndex);
            // Create the first cell – and apply the style
            var cell = row.CreateCell(0);
            cell.SetCellValue("Summary:");
            cell.CellStyle = customStyle;
            // Create the second cell , which is empty
            cell = row.CreateCell(1);
            cell.CellStyle = customStyle;

            string filename = @"d:\Book.xls";
            using (var fileData = new FileStream(filename, FileMode.Create))
            {
                workbook.Write(fileData);
            }
        }
    }
}

5.แยกเป็นฟังก์ชัน

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

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("Email");
            dt.Columns.Add("Flag");

            DataRow row1 = dt.NewRow();
            row1["Name"] = "Jack";
            row1["Email"] = "mr.phaisarn@gmail.com";
            row1["Flag"] = "true";
            dt.Rows.Add(row1);

            DataRow row2 = dt.NewRow();
            row2["Name"] = "Example";
            row2["Email"] = "example@gmail.com";
            row2["Flag"] = "false";
            dt.Rows.Add(row2);

            string filename = @"D:\tmp\Book.xls";
            CreateExcel(filename, dt);
        }

        private static void CreateExcel(string filename, DataTable dt)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            var sheet1 = workbook.CreateSheet("Sheet1");

            // Create the style object
            var customStyle = workbook.CreateCellStyle();
            // Define a thin border for the top and bottom of the cell
            customStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            customStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            // Create a font object and make it bold
            var customFont = workbook.CreateFont();
            customFont.Boldweight = (short)FontBoldWeight.Bold;
            customStyle.SetFont(customFont);

            int rowHeader = 0;
            IRow row;

            //--- add 1 header row
            row = sheet1.CreateRow(rowHeader);
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                var cellc = row.CreateCell(c);
                cellc.SetCellValue(dt.Columns[c].ColumnName);
                cellc.CellStyle = customStyle;
            }
            rowHeader++;

            //--- add data rows
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                row = sheet1.CreateRow(r + rowHeader);
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    ICell cell = row.CreateCell(c);
                    cell.SetCellValue(dt.Rows[r][c].ToString().Trim());

                    if (r == dt.Rows.Count - 1)
                    {
                        sheet1.AutoSizeColumn(c);
                    }
                }
            }

            using (FileStream fileData = new FileStream(filename, FileMode.Create))
            {
                workbook.Write(fileData);
            }
        } // end CreateExcel
    } // end class
}

6.แยกเป็นคลาส

ถ้าจำนวน row มีมากกว่า MaxRowsPerSheet ก็จะสร้าง Sheet เพิ่ม

using System.Data;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("Email");

            for (int i = 0; i < 100; i++)
            {
                DataRow rowi = dt.NewRow();
                rowi["Name"] = "name" + i;
                rowi["Email"] = "email" + i;
                dt.Rows.Add(rowi);
            }

            string filename = @"D:\tmp\Book.xls";
            NpoiExcel npoiExcel = new NpoiExcel();
            npoiExcel.CreateExcel(filename, dt);
        }
    } // end class
}

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

namespace ConsoleApp1
{
    public class NpoiExcel
    {
        private int MaxRowsPerSheet = 65500;
        private HSSFWorkbook workbook { get; set; }

        private ISheet createSheetAndHeader(DataTable dt, string sheetName, ICellStyle customStyle)
        {
            ISheet sheeti = this.workbook.CreateSheet(sheetName);

            //--- add 1 header row
            IRow row = sheeti.CreateRow(0);
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                var cellc = row.CreateCell(c);
                cellc.SetCellValue(dt.Columns[c].ColumnName);
                cellc.CellStyle = customStyle;
            }

            return sheeti;
        }

        public void CreateExcel(string filename, DataTable dt)
        {
            //HSSFWorkbook
            this.workbook = new HSSFWorkbook();

            // Create the style object
            ICellStyle customStyle = this.workbook.CreateCellStyle();
            // Define a thin border for the top and bottom of the cell
            customStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            customStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            // Create a font object and make it bold
            var customFont = this.workbook.CreateFont();
            customFont.Boldweight = (short)FontBoldWeight.Bold;
            customStyle.SetFont(customFont);


            int sheetCount = 1;

            int currRow = 0;
            string sheetName = "Sheet" + sheetCount++;
            ISheet sheeti = createSheetAndHeader(dt, sheetName, customStyle);
            currRow++;

            IRow row;
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                if (currRow > MaxRowsPerSheet)
                {
                    currRow = 0;
                    sheetName = "Sheet" + sheetCount++;
                    sheeti = createSheetAndHeader(dt, sheetName, customStyle);
                    currRow++;
                }

                //--- Add Data Rows
                row = sheeti.CreateRow(currRow);
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    ICell cell = row.CreateCell(c);
                    cell.SetCellValue(dt.Rows[r][c].ToString().Trim());

                    if ((r == dt.Rows.Count - 1) || (currRow == MaxRowsPerSheet))
                    {
                        sheeti.AutoSizeColumn(c);
                    }
                }
                currRow++;
            }

            using (FileStream fileData = new FileStream(filename, FileMode.Create))
            {
                this.workbook.Write(fileData);
            }
        } // end CreateExcel
    }
}

Link

NPOI nuget
NPOI github
How To Create Excel Spreadsheets Using NPOI