目錄前言NPOI簡介一、安裝相對應的程式包1.1、在 “管理NuGet程式包” 中的瀏覽搜索：“NPOI”二、新建Excel幫助類三、調用3.1、增加一個“keywords”模型類，用作導出3.2、添加一個控制器3.3、編寫導入導出的控制器代碼3.3.1、重寫“Close”函數3.3.2、添加控制器 ...
目錄
前言
我們在日常開發中對Excel的操作可能會比較頻繁，好多功能都會涉及到Excel的操作。在.Net Core中大家可能使用Npoi比較多，這款軟體功能也十分強大，而且接近原始編程。但是直接使用Npoi大部分時候我們可能都會自己封裝一下，畢竟根據二八原則，我們百分之八十的場景可能都是進行簡單的導入導出操作，這裡就引出我們的主角Npoi。
NPOI簡介
NPOI是指構建在POI 3.x版本之上的一個程式，NPOI可以在沒有安裝Office的情況下對Word或Excel文檔進行讀寫操作。NPOI是一個開源的C#讀寫Excel、WORD等微軟OLE2組件文檔的項目。
一、安裝相對應的程式包
在 .Net Core 中使用NPOI首先必須先安裝NPOI；如下圖所示：
1.1、在 “管理NuGet程式包” 中的瀏覽搜索：“NPOI”
點擊安裝以上兩個即可，安裝完成之後最好重新編譯一下項目以防出錯。
二、新建Excel幫助類
在項目中新建“ExcelHelper”類；此類用於封裝導入導出以及其他配置方法。代碼如下：
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using NPOI;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Text.RegularExpressions;
using System.Reflection;
using System.Collections;
using NPOI.HSSF.Util;
namespace WebApplication1 //命名空間依據自己的項目進行修改
{
/// <summary>
/// Excel幫助類
/// 功能：
/// 1、導出數據到Excel文件中
/// 2、將Excel文件的數據導入到List<T>對象集合中
/// </summary>
public static class ExcelHelper
{
/// <summary>
/// 導出列名
/// </summary>
public static SortedList ListColumnsName;
#region 從DataTable導出到excel文件中，支持xls和xlsx格式
#region 導出為xls文件內部方法
/// <summary>
/// 從DataTable 中導出到excel
/// </summary>
/// <param name="strFileName">excel文件名</param>
/// <param name="dtSource">datatabe源數據</param>
/// <param name="strHeaderText">表名</param>
/// <param name="sheetnum">sheet的編號</param>
/// <returns></returns>
static MemoryStream ExportDT(string strFileName, DataTable dtSource, string strHeaderText, Dictionary<string, string> dir, int sheetnum)
{
//創建工作簿和sheet
IWorkbook workbook = new HSSFWorkbook();
using (Stream writefile = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read))
{
if (writefile.Length > 0 && sheetnum > 0)
{
workbook = WorkbookFactory.Create(writefile);
}
}
ISheet sheet = null;
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(Convert.ToString(item.ColumnName)).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(Convert.ToString(dtSource.Rows[i][j])).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表頭，填充列頭，樣式
if (rowIndex == 0)
{
string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
if (workbook.GetSheetIndex(sheetName) >= 0)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
}
sheet = workbook.CreateSheet(sheetName);
#region 表頭及樣式
{
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
rowIndex = 1;
}
#endregion
#region 列頭及樣式
if (rowIndex == 1)
{
IRow headerRow = sheet.CreateRow(1);//第二行設置列名
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//寫入列標題
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//設置列寬
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
}
rowIndex = 2;
}
#endregion
}
#endregion
#region 填充內容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
NPOI.SS.UserModel.ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字元串類型
double result;
if (isNumeric(drValue, out result))
{
//數字字元串
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期類型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化顯示
break;
case "System.Boolean": //布爾型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮點型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值處理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue.ToString());
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms, true);
ms.Flush();
ms.Position = 0;
return ms;
}
}
#endregion
#region 導出為xlsx文件內部方法
/// <summary>
/// 從DataTable 中導出到excel
/// </summary>
/// <param name="dtSource">DataTable數據源</param>
/// <param name="strHeaderText">表名</param>
/// <param name="fs">文件流</param>
/// <param name="readfs">記憶體流</param>
/// <param name="sheetnum">sheet索引</param>
static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs, MemoryStream readfs, Dictionary<string, string> dir, int sheetnum)
{
IWorkbook workbook = new XSSFWorkbook();
if (readfs.Length > 0 && sheetnum > 0)
{
workbook = WorkbookFactory.Create(readfs);
}
ISheet sheet = null;
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列寬
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(Convert.ToString(item.ColumnName)).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(Convert.ToString(dtSource.Rows[i][j])).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表頭，填充列頭，樣式
if (rowIndex == 0)
{
#region 表頭及樣式
{
string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
if (workbook.GetSheetIndex(sheetName) >= 0)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
}
sheet = workbook.CreateSheet(sheetName);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
}
#endregion
#region 列頭及樣式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//設置列寬
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充內容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
NPOI.SS.UserModel.ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字元串類型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期類型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化顯示
break;
case "System.Boolean": //布爾型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮點型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值處理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue.ToString());
break;
}
}
#endregion
rowIndex++;
}
workbook.Write(fs,true);
fs.Close();
}
#endregion
#region 導出excel表格
/// <summary>
/// DataTable導出到Excel文件，xls文件
/// </summary>
/// <param name="dtSource">數據源</param>
/// <param name="strHeaderText">表名</param>
/// <param name="strFileName">excel文件名</param>
/// <param name="dir">DataTable和excel列名對應字典</param>
/// <param name="sheetRow">每個sheet存放的行數</param>
public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName, Dictionary<string, string> dir, bool isNew, int sheetRow = 50000)
{
int currentSheetCount = GetSheetNumber(strFileName);//現有的頁數sheetnum
if (sheetRow <= 0)
{
sheetRow = dtSource.Rows.Count;
}
string[] temp = strFileName.Split('.');
string fileExtens = temp[temp.Length - 1];
int sheetCount = (int)Math.Ceiling((double)dtSource.Rows.Count / sheetRow);//sheet數目
if (temp[temp.Length - 1] == "xls" && dtSource.Columns.Count < 256 && sheetRow < 65536)
{
if (isNew)
{
currentSheetCount = 0;
}
for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++)
{
DataTable pageDataTable = dtSource.Clone();
int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow;
for (int j = 0; j < hasRowCount; j++)
{
pageDataTable.ImportRow(dtSource.Rows[(i - currentSheetCount) * sheetRow + j]);
}
using (MemoryStream ms = ExportDT(strFileName, pageDataTable, strHeaderText, dir, i))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
}
else
{
if (temp[temp.Length - 1] == "xls")
strFileName = strFileName + "x";
if (isNew)
{
currentSheetCount = 0;
}
for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++)
{
DataTable pageDataTable = dtSource.Clone();
int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow;
for (int j = 0; j < hasRowCount; j++)
{
pageDataTable.ImportRow(dtSource.Rows[(i - currentSheetCount) * sheetRow + j]);
}
FileStream readfs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read);
MemoryStream readfsm = new MemoryStream();
readfs.CopyTo(readfsm);
readfs.Close();
using (FileStream writefs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
ExportDTI(pageDataTable, strHeaderText, writefs, readfsm, dir, i);
}
readfsm.Close();
}
}
}
/// <summary>
/// 導出Excel（//超出10000條數據 創建新的工作簿）
/// </summary>
/// <param name="dtSource">數據源</param>
/// <param name="dir">導出Excel表格的欄位名和列名的字元串字典實例；例如：dir.Add("IllegalKeywords", "姓名");</param>
public static XSSFWorkbook ExportExcel(DataTable dtSource, Dictionary<string, string> dir)
{
XSSFWorkbook excelWorkbook = new XSSFWorkbook();
//int columnsCount = columnsNames.GetLength(0);
int columnsCount = dir.Count;
if (columnsCount > 0)
{
ListColumnsName = new SortedList(new NoSort());
//for (int i = 0; i < columnsCount; i++)
//{
// ListColumnsName.Add(columnsNames[i, 0], columnsNames[i, 1]);
//}
foreach (KeyValuePair<string,string> item in dir)
{
ListColumnsName.Add(item.Key, item.Value);
}
if (ListColumnsName == null || ListColumnsName.Count == 0)
{
throw (new Exception("請對ListColumnsName設置要導出的列明！"));
}
else
{
excelWorkbook = InsertRow(dtSource);
}
}
else
{
throw (new Exception("請對ListColumnsName設置要導出的列明！"));
}
return excelWorkbook;
}
#endregion
/// <summary>
/// 創建Excel文件
/// </summary>
/// <param name="filePath"></param>
private static XSSFWorkbook CreateExcelFile()
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
//右擊文件“屬性”信息
#region 文件屬性信息
{
POIXMLProperties props = xssfworkbook.GetProperties();
props.CoreProperties.Creator = "Joy";//Excel文件的創建作者
props.CoreProperties.Title = "";//Excel文件標題
props.CoreProperties.Description = "";//Excel文件備註
props.CoreProperties.Category = "";//Excel文件類別信息
props.CoreProperties.Subject = "";//Excel文件主題信息
props.CoreProperties.Created = DateTime.Now;//Excel文件創建時間
props.CoreProperties.Modified = DateTime.Now;//Excel文件修改時間
props.CoreProperties.SetCreated(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
props.CoreProperties.LastModifiedByUser = "Joy";//Excel文件最後一次保存者
props.CoreProperties.SetModified(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//Excel文件最後一次保存日期
}
#endregion
return xssfworkbook;
}
/// <summary>
/// 創建excel表頭
/// </summary>
/// <param name="dgv"></param>
/// <param name="excelSheet"></param>
private static void CreateHeader(XSSFSheet excelSheet, XSSFWorkbook excelWorkbook, XSSFCellStyle cellStyle)
{
int cellIndex = 0;
//迴圈導出列
foreach (System.Collections.DictionaryEntry de in ListColumnsName)
{
XSSFRow newRow = (XSSFRow)excelSheet.CreateRow(0);
XSSFCellStyle? headTopStyle = CreateStyle(excelWorkbook, cellStyle,HorizontalAlignment.Center, VerticalAlignment.Center, 18, true, true, "宋體", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Grey25Percent.Index, HSSFColor.Black.Index,FontUnderlineType.None, FontSuperScript.None, false);
XSSFCell newCell = (XSSFCell)newRow.CreateCell(cellIndex);
newCell.SetCellValue(de.Value.ToString());
newCell.CellStyle = headTopStyle;
cellIndex++;
}
}
/// <summary>
/// 插入數據行
/// </summary>
private static XSSFWorkbook InsertRow(DataTable dtSource)
{
XSSFWorkbook excelWorkbook = CreateExcelFile();
int rowCount = 0;
int sheetCount = 1;
XSSFSheet newsheet = null;
//迴圈數據源導出數據集
newsheet = (XSSFSheet)excelWorkbook.CreateSheet("Sheet" + sheetCount);
XSSFCellStyle headCellStyle = (XSSFCellStyle)excelWorkbook.CreateCellStyle(); //創建列頭單元格實例樣式
CreateHeader(newsheet, excelWorkbook, headCellStyle);
//單元格內容信息
foreach (DataRow dr in dtSource.Rows)
{
rowCount++;
//超出10000條數據 創建新的工作簿
if (rowCount == 10000)
{
rowCount = 1;
sheetCount++;
newsheet = (XSSFSheet)excelWorkbook.CreateSheet("Sheet" + sheetCount);
CreateHeader(newsheet, excelWorkbook, headCellStyle);
}
XSSFRow newRow = (XSSFRow)newsheet.CreateRow(rowCount);
XSSFCellStyle cellStyle = (XSSFCellStyle)excelWorkbook.CreateCellStyle(); //創建單元格實例樣式
XSSFCellStyle? style = CreateStyle(excelWorkbook, cellStyle, HorizontalAlignment.Center, VerticalAlignment.Center, 14, true, false);
InsertCell(dtSource, dr, newRow, style, excelWorkbook);
}
//自動列寬
//for (int i = 0; i <= dtSource.Columns.Count; i++)
//{
// newsheet.AutoSizeColumn(i, true);
//}
return excelWorkbook;
}
/// <summary>
/// 導出數據行
/// </summary>
/// <param name="dtSource"></param>
/// <param name="drSource"></param>
/// <param name="currentExcelRow"></param>
/// <param name="excelSheet"></param>
/// <param name="excelWorkBook"></param>
private static void InsertCell(DataTable dtSource, DataRow drSource, XSSFRow currentExcelRow, XSSFCellStyle cellStyle, XSSFWorkbook excelWorkBook)
{
for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
{
//列名稱
string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
XSSFCell newCell = null;
System.Type rowType = drSource[columnsName].GetType();
string drValue = drSource[columnsName].ToString().Trim();
switch (rowType.ToString())
{
case "System.String"://字元串類型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
newCell.CellStyle = cellStyle;
break;
case "System.DateTime"://日期類型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(dateV);
newCell.CellStyle = cellStyle;
break;
case "System.Boolean"://布爾型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(boolV);
newCell.CellStyle = cellStyle;
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(intV.ToString());
newCell.CellStyle = cellStyle;
break;
case "System.Decimal"://浮點型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(doubV);
newCell.CellStyle = cellStyle;
break;
case "System.DBNull"://空值處理
newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue("");
newCell.CellStyle = cellStyle;
break;
default:
throw (new Exception(rowType.ToString() + "：類型數據無法處理!"));
}
}
}
/// <summary>
/// 行內單元格常用樣式設置
/// </summary>
/// <param name="workbook">Excel文件對象</param>
/// <param name="cellStyle">Excel文件中XSSFCellStyle對象</param>
/// <param name="hAlignment">水平佈局方式</param>
/// <param name="vAlignment">垂直佈局方式</param>
/// <param name="fontHeightInPoints">字體大小</param>
/// <param name="isAddBorder">是否需要邊框</param>
/// <param name="boldWeight">字體加粗 (None = 0,Normal = 400，Bold = 700</param>
/// <param name="fontName">字體（仿宋，楷體，宋體，微軟雅黑...與Excel主題字體相對應）</param>
/// <param name="isAddBorderColor">是否增加邊框顏色</param>
/// <param name="isItalic">是否將文字變為斜體</param>
/// <param name="isLineFeed">是否自動換行</param>
/// <param name="isAddCellBackground">是否增加單元格背景顏色</param>
/// <param name="fillPattern">填充圖案樣式(FineDots 細點，SolidForeground立體前景，isAddFillPattern=true時存在)</param>
/// <param name="cellBackgroundColor">單元格背景顏色（當isAddCellBackground=true時存在）</param>
/// <param name="fontColor">字體顏色</param>
/// <param name="underlineStyle">下劃線樣式（無下劃線[None],單下劃線[Single],雙下劃線[Double],會計用單下劃線[SingleAccounting],會計用雙下劃線[DoubleAccounting]）</param>
/// <param name="typeOffset">字體上標下標(普通預設值[None],上標[Sub],下標[Super]),即字體在單元格內的上下偏移量</param>
/// <param name="isStrikeout">是否顯示刪除線</param>
/// <param name="dataFormat">格式化日期顯示</param>
/// <returns></returns>
public static XSSFCellStyle CreateStyle(XSSFWorkbook workbook, XSSFCellStyle cellStyle, HorizontalAlignment hAlignment, VerticalAlignment vAlignment, short fontHeightInPoints, bool isAddBorder, bool boldWeight, string fontName = "宋體", bool isAddBorderColor = true, bool isItalic = false, bool isLineFeed = true, bool isAddCellBackground = false, FillPattern fillPattern = FillPattern.NoFill, short cellBackgroundColor = HSSFColor.Yellow.Index, short fontColor = HSSFColor.Black.Index, FontUnderlineType underlineStyle =
FontUnderlineType.None, FontSuperScript typeOffset = FontSuperScript.None, bool isStrikeout = false,string dataFormat="yyyy-MM-dd HH:mm:ss")
{
cellStyle.Alignment = hAlignment; //水平居中
cellStyle.VerticalAlignment = vAlignment; //垂直居中
cellStyle.WrapText = isLineFeed;//自動換行
//格式化顯示
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat(dataFormat);
//背景顏色，邊框顏色，字體顏色都是使用 HSSFColor屬性中的對應調色板索引，關於 HSSFColor 顏色索引對照表，詳情參考：https://www.cnblogs.com/Brainpan/p/5804167.html
//TODO：引用了NPOI後可通過ICellStyle 介面的 FillForegroundColor 屬性實現 Excel 單元格的背景色設置，FillPattern 為單元格背景色的填充樣式
//TODO:十分註意，要設置單元格背景色必須是FillForegroundColor和FillPattern兩個屬性同時設置，否則是不會顯示背景顏色
if (isAddCellBackground)
{
cellStyle.FillForegroundColor = cellBackgroundColor;//單元格背景顏色
cellStyle.FillPattern = fillPattern;//填充圖案樣式(FineDots 細點，SolidForeground立體前景)
}
else
{
cellStyle.FillForegroundColor = HSSFColor.White.Index;//單元格背景顏色
}
//是否增加邊框
if (isAddBorder)
{
//常用的邊框樣式 None(沒有),Thin(細邊框，瘦的),Medium(中等),Dashed(虛線),Dotted(星羅棋佈的),Thick(厚的),Double(雙倍),Hair(頭髮)[上右下左順序設置]
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
}
//是否設置邊框顏色
if (isAddBorderColor)
{
//邊框顏色[上右下左順序設置]
cellStyle.TopBorderColor = XSSFFont.DEFAULT_FONT_COLOR;//DarkGreen(黑綠色)
cellStyle.RightBorderColor = XSSFFont.DEFAULT_FONT_COLOR;
cellStyle.BottomBorderColor = XSSFFont.DEFAULT_FONT_COLOR;
cellStyle.LeftBorderColor = XSSFFont.DEFAULT_FONT_COLOR;
}
/**
* 設置相關字體樣式
*/
var cellStyleFont = (XSSFFont)workbook.CreateFont(); //創建字體
//假如字體大小隻需要是粗體的話直接使用下麵該屬性即可
//cellStyleFont.IsBold = true;
cellStyleFont.IsBold = boldWeight; //字體加粗
cellStyleFont.FontHeightInPoints = fontHeightInPoints; //字體大小
cellStyleFont.FontName = fontName;//字體（仿宋，楷體，宋體 ）
cellStyleFont.Color = fontColor;//設置字體顏色
cellStyleFont.IsItalic = isItalic;//是否將文字變為斜體
cellStyleFont.Underline = underlineStyle;//字體下劃線
cellStyleFont.TypeOffset = typeOffset;//字體上標下標
cellStyleFont.IsStrikeout = isStrikeout;//是否有刪除線
cellStyle.SetFont(cellStyleFont); //將字體綁定到樣式
return cellStyle;
}
#endregion
#region 從excel文件中將數據導出到List<T>對象集合
/// <summary>
/// 將制定sheet中的數據導出到DataTable中
/// </summary>
/// <param name="sheet">需要導出的sheet</param>
/// <param name="HeaderRowIndex">列頭所在行號，-1表示沒有列頭</param>
/// <param name="dir">excel列名和DataTable列名的對應字典</param>
/// <returns></returns>
static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, Dictionary<string, string> dir)
{
DataTable table = new DataTable();
IRow headerRow;
int cellCount;
try
{
//沒有標頭或者不需要表頭用excel列的序號（1,2,3..）作為DataTable的列名
if (HeaderRowIndex < 0)
{
headerRow = sheet.GetRow(0);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
//有表頭，使用表頭做為DataTable的列名
else
{
headerRow = sheet.GetRow(HeaderRowIndex);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <cellCount; i++)
{
//如果excel某一列列名不存在：以該列的序號作為DataTable的列名，如果DataTable中包含了這個序列為名的列，那麼列名為重覆列名+序號
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重覆列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
//excel中的某一列列名不為空，但是重覆了：對應的DataTable列名為“重覆列名+序號”
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重覆列名" + i));
table.Columns.Add(column);
}
else
//正常情況，列名存在且不重覆：用excel中的列名作為DataTable中對應的列名
{
string aaa = headerRow.GetCell(i).ToString();
string colName = dir.Where(s => s.Value == headerRow.GetCell(i).ToString()).First().Key;
DataColumn column = new DataColumn(colName);
table.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)//excel行遍歷
{
try
{
IRow row;
if (sheet.GetRow(i) == null)//如果excel有空行，則添加缺失的行
{
row = sheet.CreateRow(i);
}
else
{
row = sheet.GetRow(i);
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)//excel列遍歷
{
try
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.String://字元串
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = default(string);
}
break;
case CellType.Numeric://數字
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))//時間戳數字
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula://公式
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch (Exception exception)
{
//loger.Error(exception.ToString());
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
//loger.Error(exception.ToString());
}
}
}
catch (Exception exception)
{
//loger.Error(exception.ToString());
}
return table;
}
/// <summary>
/// DataTable 轉換為List<T>對象集合
/// </summary>
/// <typeparam name="TResult">類型</typeparam>
/// <param name="dt">DataTable</param>
/// <returns></returns>
public static List<TResult> DataTableToList<TResult>(this DataTable dt) where TResult : class, new()
{
//創建一個屬性的列表
List<PropertyInfo> prlist = new List<PropertyInfo>();
//獲取TResult的類型實例 反射的入口
Type t = typeof(TResult);
//獲得TResult 的所有的Public 屬性 並找出TResult屬性和DataTable的列名稱相同的屬性(PropertyInfo) 並加入到屬性列表
Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });
//創建返回的集合
List<TResult> oblist = new List<TResult>();
foreach (DataRow row in dt.Rows)
{
//創建TResult的實例
TResult ob = new TResult();
//找到對應的數據 並賦值
prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
//放入到返回的集合中.
oblist.Add(ob);
}
return oblist;
}
/// <summary>
/// DataTable轉化為List集合
/// </summary>
/// <typeparam name="T">實體對象</typeparam>
/// <param name="dt">datatable表</param>
/// <param name="isStoreDB">是否存入資料庫datetime欄位，date欄位沒事，取出不用判斷</param>
/// <returns>返回list集合</returns>
private static List<T> DataTableToList<T>(DataTable dt, bool isStoreDB = true)
{
List<T> list = new List<T>();
Type type = typeof(T);
//List<string> listColums = new List<string>();
PropertyInfo[] pArray = type.GetProperties(); //集合屬性數組
foreach (DataRow row in dt.Rows)
{
T entity = Activator.CreateInstance<T>(); //新建對象實例
foreach (PropertyInfo p in pArray)
{
if (!dt.Columns.Contains(p.Name) || row[p.Name] == null || row[p.Name] == DBNull.Value)
{
continue; //DataTable列中不存在集合屬性或者欄位內容為空則，跳出迴圈，進行下個迴圈
}
if (isStoreDB && p.PropertyType == typeof(DateTime) && Convert.ToDateTime(row[p.Name]) < Convert.ToDateTime("1753-01-01"))
{
continue;
}
try
{
var obj = Convert.ChangeType(row[p.Name], p.PropertyType);//類型強轉，將table欄位類型轉為集合欄位類型
p.SetValue(entity, obj, null);
}
catch (Exception)
{
// throw;
}
}
list.Add(entity);
}
return list;
}
/// <summary>
/// DataSet 轉換成List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ds"></param>
/// <param name="tableIndext"></param>
/// <returns></returns>
private static List<T> DataTable2List<T>(DataTable dt)
{
//確認參數有效
if (dt == null || dt.Rows.Count <= 0)
{
return null;
}
IList<T> list = new List<T>(); //實例化一個list
// 在這裡寫 獲取T類型的所有公有屬性。 註意這裡僅僅是獲取T類型的公有屬性，不是公有方法，也不是公有欄位，當然也不是私有屬性
PropertyInfo[] tMembersAll = typeof(T).GetProperties();
for (int i = 0; i < dt.Rows.Count; i++)
{
//創建泛型對象。為什麼這裡要創建一個泛型對象呢？是因為目前我不確定泛型的類型。
T t = Activator.CreateInstance<T>();
//獲取t對象類型的所有公有屬性。但是我不建議吧這條語句寫在for迴圈里，因為沒迴圈一次就要獲取一次，占用資源，所以建議寫在外面
//PropertyInfo[] tMembersAll = t.GetType().GetProperties();
for (int j = 0; j < dt.Columns.Count; j++)
{
//遍歷tMembersAll
foreach (PropertyInfo tMember in tMembersAll)
{
//取dt表中j列的名字，並把名字轉換成大寫的字母。整條代碼的意思是：如果列名和屬性名稱相同時賦值
if (dt.Columns[j].ColumnName.ToUpper().Equals(tMember.Name.ToUpper()))
{
//dt.Rows[i][j]表示取dt表裡的第i行的第j列；DBNull是指資料庫中當一個欄位沒有被設置值的時候的值，相當於資料庫中的“空值”。
if (dt.Rows[i][j] != DBNull.Value)
{
//SetValue是指：將指定屬性設置為指定值。 tMember是T泛型對象t的一個公有成員，整條代碼的意思就是：將dt.Rows[i][j]賦值給t對象的tMember成員,參數詳情請參照http://msdn.microsoft.com/zh-cn/library/3z2t396t(v=vs.100).aspx/html
tMember.SetValue(t, Convert.ToString(dt.Rows[i][j]), null);
}
else
{
tMember.SetValue(t, null, null);
}
break;//註意這裡的break是寫在if語句裡面的，意思就是說如果列名和屬性名稱相同並且已經賦值了，那麼我就跳出foreach迴圈，進行j+1的下次迴圈
}
}
}
list.Add(t);
}
dt.Dispose();
return list.ToList();
}
/// <summary>
/// 讀取Excel文件特定名字sheet的內容到List<T>對象集合
/// </summary>
/// <param name="strFileName">excel文件路徑</param>
/// <param name="dir">excel列名和DataTable列名的對應字典</param>
/// <param name="SheetName">excel表名</param>
/// <param name="HeaderRowIndex">列頭所在行號，-1表示沒有列頭</param>
/// <returns></returns>
public static List<T> ImportExceltoDt<T>(string strFileName, Dictionary<string, string> dir, string SheetName, int HeaderRowIndex = 0)
{
DataTable table = new DataTable();
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (file.Length > 0)
{
IWorkbook wb = WorkbookFactory.Create(file);
ISheet isheet = wb.GetSheet(SheetName);
table = ImportDt(isheet, HeaderRowIndex, dir);
isheet = null;
}
}
List<T> results = DataTableToList<T>(table);
table.Dispose();
return results;
}
/// <summary>
/// 讀取Excel文件某一索引sheet的內容到DataTable
/// </summary>
/// <param name="strFileName">excel文件路徑</param>
/// <param name="sheet">需要導出的sheet序號</param>
/// <param name="HeaderRowIndex">列頭所在行號，-1表示沒有列頭</param>
/// <param name="dir">excel列名和DataTable列名的對應字典</param>
/// <returns></returns>
public static List<T> ImportExceltoDt<T>(string strFileName, Dictionary<string, string> dir, int HeaderRowIndex = 0, int SheetIndex = 0)
{
DataTable table = new DataTable();
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (file.Length > 0)
{
IWorkbook wb = WorkbookFactory.Create(file);
ISheet isheet = wb.GetSheetAt(SheetIndex);
table = ImportDt(isheet, HeaderRowIndex, dir);
isheet = null;
}
}
List<T> results = DataTableToList<T>(table);
table.Dispose();
return results;
}
#endregion
/// <summary>
/// 獲取excel文件的sheet數目
/// </summary>
/// <param name="outputFile"></param>
/// <returns></returns>
public static int GetSheetNumber(string outputFile)
{
int number = 0;
using (FileStream readfile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Read))
{
if (readfile.Length > 0)
{
IWorkbook wb = WorkbookFactory.Create(readfile);
number = wb.NumberOfSheets;
}
}
return number;
}
/// <summary>
/// 判斷內容是否是數字
/// </summary>
/// <param name="message"></param>
/// <param name="result"></param>
/// <returns></returns>
public static bool isNumeric(String message, out double result)
{
Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
result = -1;
if (rex.IsMatch(message))
{
result = double.Parse(message);
return true;
}
else
return false;
}
/// <summary>
/// 驗證導入的Excel是否有數據
/// </summary>
/// <param name="excelFileStream"></param>
/// <returns></returns>
public static bool HasData(Stream excelFileStream)
{
using (excelFileStream)
{
IWorkbook workBook = new HSSFWorkbook(excelFileStream);
if (workBook.NumberOfSheets > 0)
{
ISheet sheet = workBook.GetSheetAt(0);
return sheet.PhysicalNumberOfRows > 0;
}
}
return false;
}
}
/// <summary>
/// 排序實現介面 不進行排序 根據添加順序導出
/// </summary>
public class NoSort : IComparer
{
public int Compare(object x, object y)
{
return -1;
}
}
}
三、調用
3.1、增加一個“keywords”模型類，用作導出
public class keywords
{
[Column("姓名")]
public string IllegalKeywords { get; set; }
}
3.2、添加一個控制器
3.3、編寫導入導出的控制器代碼
3.3.1、重寫“Close”函數
在導出時，為了防止MemoryStream無法關閉從而報錯，所以我們繼承MemoryStream；代碼如下：
namespace WebApplication1 //命名空間依據自己的項目進行修改
{
public class NpoiMemoryStream : MemoryStream
{
public NpoiMemoryStream()
{
AllowClose = true;
}
public bool AllowClose { get; set; }
public override void Close()
{
if (AllowClose)
base.Close();
}
}
}
3.3.2、添加控制器代碼
/// <summary>
/// 本地環境
/// </summary>
private IHostingEnvironment _hostingEnv;
/// <summary>
/// Excel導入的具體實現
/// </summary>
/// <returns></returns>
public IActionResult import_excel()
{
string filepath = _hostingEnv.WebRootPath + "/線上用戶20230324.xlsx";//導入的文件地址路徑，可動態傳入
Dictionary<string, string> dir = new Dictionary<string, string>();//申明excel列名和DataTable列名的對應字典
dir.Add("IllegalKeywords","姓名");
List<keywords> keyWordsList = ExcelHelper.ImportExceltoDt<keywords>(filepath, dir,"Sheet1",0);
#region 將List動態添加至資料庫
//……
#endregion
return Json(new { code = 200, msg = "導入成功" });
}
/// <summary>
/// Excel導出的具體實現
/// </summary>
/// <returns></returns>
public IActionResult export_excel()
{
#region 添加測試數據
List<keywords> keys = new List<keywords>();
for (int i = 0; i < 6; i++)
{
keywords keyword = new keywords();
keyword.IllegalKeywords = "測試_" + i;
keys.Add(keyword);
}
#endregion
#region 實例化DataTable併進行賦值
DataTable dt = new DataTable();
dt = listToDataTable(keys);//List<T>對象集合轉DataTable
#endregion
string filename = DateTime.Now.ToString("線上用戶yyyyMMdd") + ".xlsx";
Dictionary<string, string> dir = new Dictionary<string, string>();
dir.Add("IllegalKeywords", "姓名");
XSSFWorkbook book= ExcelHelper.ExportExcel(dt, dir);
dt.Dispose();//釋放DataTable所占用的數據資源
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
book.Write(ms, true);
ms.Flush();
ms.Position = 0;
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
book.Dispose();//使用由XSSFWorkbook所占用的資源
return File(ms, "application/vnd.ms-excel", Path.GetFileName(filename));//進行瀏覽器下載
}
3.3.3、Excel導出效果
3.3.4、Excel導入效果
導入後的List再根據需求調用添加方法實現數據的添加
本文來自博客園，作者：TomLucas，轉載請註明原文鏈接：https://www.cnblogs.com/lucasDC/p/17707810.html
