- ติดตั้ง Package
- สร้างไฟล์ .xlsx เปล่า
- ใส่ข้อความลงฟิลด์
- ใส่สไตล์ให้ฟิลด์
- อ่านไฟล์ .xlsx
- ป้องกันการแก้ไข sheet ด้วย Password
ทดสอบบน .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
}
ใส่สีให้กับ cell
... customStyle.FillForegroundColor = IndexedColors.LightGreen.Index; customStyle.FillPattern = FillPattern.SolidForeground; ...
Merge cell
... int rowNum = 1; var cra = new NPOI.SS.Util.CellRangeAddress(rowNum, rowNum, 2, 3); excelSheet.AddMergedRegion(cra); workbook.Write(fs);
Alignment
... customStyle.Alignment = HorizontalAlignment.Center; ...
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
}
6.ป้องกันการแก้ไข sheet ด้วย Password
ที่ sheet1 จะเปิดดูได้ แต่จะแก้ไขไม่ได้ ส่วน sheet2 แก้ไขได้ปกติ
ถ้าจะแก้ไข sheet1 ให้ไปที่เมนู Review | Unprotect Sheet แล้วใส่ password
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");
bool x = sheet1.Protect; // false
sheet1.ProtectSheet("Password");
bool y = sheet1.Protect; // ture
workbook.Write(fs);
}
}