NPOI 2.5.1 กับไฟล์ .xlsx

  1. ติดตั้ง Package
  2. สร้างไฟล์ .xlsx เปล่า
  3. ใส่ข้อความลงฟิลด์
  4. ใส่สไตล์ให้ฟิลด์
  5. อ่านไฟล์ .xlsx

ทดสอบบน .NET Framework 4.5

1. ติดตั้ง Package

ติดตั้ง NPOI 2.5.1

PM> Install-Package NPOI -Version 2.5.1

ติดตั้ง Newtonsoft.Json 12.0.3

PM> Install-Package Newtonsoft.Json -Version 12.0.3

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

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string filename = @"d:\Result.xlsx";
            WriteExcel(filename);
        }

        static void WriteExcel(string filename)
        {
            using (var fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
            {
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet1 = workbook.CreateSheet("Sheet1");
                ISheet sheet2 = workbook.CreateSheet("Sheet2");

                workbook.Write(fs);
            }
        }
    } // end class
}

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

using Newtonsoft.Json;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace ConsoleApp1
{
    class UserDetails
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            string filename = @"d:\Result.xlsx";

            List<UserDetails> persons = new List<UserDetails>()
            {
                new UserDetails() {ID="1001", Name="ABCD", City ="City1", Country="USA"},
                new UserDetails() {ID="1002", Name="PQRS", City ="City2", Country="INDIA"},
                new UserDetails() {ID="1003", Name="XYZZ", City ="City3", Country="CHINA"},
                new UserDetails() {ID="1004", Name="LMNO", City ="City4", Country="UK"},
           };

            // Lets converts our object data to Datatable for a simplified logic.
            // Datatable is most easy way to deal with complex datatypes for easy reading and formatting.
            DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));

            WriteExcel(filename, table);
        }

        static void WriteExcel(string filename, DataTable table)
        {
            using (var fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
            {
                IWorkbook workbook = new XSSFWorkbook();
                ISheet excelSheet = workbook.CreateSheet("Sheet1");

                List<String> columns = new List<string>();
                IRow row = excelSheet.CreateRow(0);
                int columnIndex = 0;

                foreach (System.Data.DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);
                    row.CreateCell(columnIndex).SetCellValue(column.ColumnName);
                    columnIndex++;
                }

                int rowIndex = 1;
                foreach (DataRow dsrow in table.Rows)
                {
                    row = excelSheet.CreateRow(rowIndex);
                    int cellIndex = 0;
                    foreach (String col in columns)
                    {
                        row.CreateCell(cellIndex).SetCellValue(dsrow[col].ToString());
                        cellIndex++;
                    }

                    rowIndex++;
                }
                workbook.Write(fs);
            }
        }
    } // end class
}

ถ้าจะใช้ชื่อคอลัมน์เป็นภาษาไทย ให้ปั้นข้อมูลเป็น DataTable

        static void Main(string[] args)
        {
            string filename = @"d:\Result.xlsx";

            DataTable table = new DataTable();
            table.Columns.Add("ไอดี");
            table.Columns.Add("ชื่อ");
            table.Columns.Add("เมือง");
            table.Columns.Add("ประเทศ");

            DataRow dr = table.NewRow();
            dr["ไอดี"] = "1001";
            dr["ชื่อ"] = "ABCD";
            dr["เมือง"] = "City1";
            dr["ประเทศ"] = "USA";
            table.Rows.Add(dr);

            WriteExcel(filename, table);
        }

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

using Newtonsoft.Json;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
namespace ConsoleApp1
{
    class UserDetails
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            string filename = @"d:\Result.xlsx";
            List<UserDetails> persons = new List<UserDetails>()
            {
                new UserDetails() {ID="1001", Name="ABCD", City ="City1", Country="USA"},
                new UserDetails() {ID="1002", Name="PQRS", City ="City2", Country="INDIA"},
                new UserDetails() {ID="1003", Name="XYZZ", City ="City3", Country="CHINA"},
                new UserDetails() {ID="1004", Name="LMNO", City ="City4", Country="UK"},
           };
            // Lets converts our object data to Datatable for a simplified logic.
            // Datatable is most easy way to deal with complex datatypes for easy reading and formatting.
            DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));
            WriteExcel(filename, table);
        }

        static void WriteExcel(string filename, DataTable table)
        {
            using (var fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
            {
                IWorkbook workbook = new XSSFWorkbook();
                ISheet excelSheet = workbook.CreateSheet("Sheet1");
                List<String> columns = new List<string>();
                IRow row = excelSheet.CreateRow(0);

                // 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.IsBold = true;
                customStyle.SetFont(customFont);

                int columnIndex = 0;
                foreach (System.Data.DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);
                    var cell = row.CreateCell(columnIndex);
                    cell.SetCellValue(column.ColumnName);
                    cell.CellStyle = customStyle;
                    columnIndex++;
                }
                int rowIndex = 1;
                foreach (DataRow dsrow in table.Rows)
                {
                    row = excelSheet.CreateRow(rowIndex);
                    int cellIndex = 0;
                    foreach (String col in columns)
                    {
                        row.CreateCell(cellIndex).SetCellValue(dsrow[col].ToString());
                        cellIndex++;
                    }
                    rowIndex++;
                }
                workbook.Write(fs);
            }
        }
    } // end class
}

5. อ่านไฟล์ .xlsx

using Newtonsoft.Json;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace ConsoleApp1
{
    class UserDetails
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            string filename = @"d:\Result.xlsx";
            string json = ReadExcel(filename);
            List<UserDetails> persons = JsonConvert.DeserializeObject<List<UserDetails>>(json);
        }

        static string ReadExcel(string filename)
        {
            DataTable dtTable = new DataTable();
            List<string> rowList = new List<string>();
            ISheet sheet;
            using (var stream = new FileStream(filename, FileMode.Open))
            {
                stream.Position = 0;
                XSSFWorkbook xssWorkbook = new XSSFWorkbook(stream);
                sheet = xssWorkbook.GetSheetAt(0);
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = headerRow.GetCell(j);
                    if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
                    {
                        dtTable.Columns.Add(cell.ToString());
                    }
                }
                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue;
                    if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            if (!string.IsNullOrEmpty(row.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
                            {
                                rowList.Add(row.GetCell(j).ToString());
                            }
                        }
                    }
                    if (rowList.Count > 0)
                        dtTable.Rows.Add(rowList.ToArray());
                    rowList.Clear();
                }
            }
            return JsonConvert.SerializeObject(dtTable);
        }

    } // end class
}