https://www.nuget.org/packages/NPOI/
ติดตั้ง
PM> Install-Package NPOI -Version 2.4.1
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();
}
}
}