网站价位,wordpress视频采集插件,制作一个赚钱的网站,学校网站建设培训.NET Core 使用NPOI实现Excel的导入导出前言NPOI简介一、安装相对应的程序包1.1、在 “管理NuGet程序包” 中的浏览搜索#xff1a;“NPOI”二、新建Excel帮助类三、调用3.1、增加一个“keywords”模型类#xff0c;用作导出3.2、添加一个控制器3.3、编写导入导出的控制器代码… .NET Core 使用NPOI实现Excel的导入导出前言NPOI简介一、安装相对应的程序包1.1、在 “管理NuGet程序包” 中的浏览搜索“NPOI”二、新建Excel帮助类三、调用3.1、增加一个“keywords”模型类用作导出3.2、添加一个控制器3.3、编写导入导出的控制器代码3.3.1、重写“Close”函数3.3.2、添加控制器代码3.3.3、Excel导出效果3.3.4、Excel导入效果 前言
我们在日常开发中对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文件的数据导入到ListT对象集合中/// /summarypublic static class ExcelHelper{/// summary/// 导出列名/// /summarypublic static SortedList ListColumnsName;#region 从DataTable导出到excel文件中支持xls和xlsx格式#region 导出为xls文件内部方法/// summary/// 从DataTable 中导出到excel/// /summary/// param namestrFileNameexcel文件名/param/// param namedtSourcedatatabe源数据/param/// param namestrHeaderText表名/param/// param namesheetnumsheet的编号/param/// returns/returnsstatic MemoryStream ExportDT(string strFileName, DataTable dtSource, string strHeaderText, Dictionarystring, string dir, int sheetnum){//创建工作簿和sheetIWorkbook 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;}}#endregionrowIndex;}using (MemoryStream ms new MemoryStream()){workbook.Write(ms, true);ms.Flush();ms.Position 0;return ms;}}#endregion#region 导出为xlsx文件内部方法/// summary/// 从DataTable 中导出到excel/// /summary/// param namedtSourceDataTable数据源/param/// param namestrHeaderText表名/param/// param namefs文件流/param/// param namereadfs内存流/param/// param namesheetnumsheet索引/paramstatic void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs, MemoryStream readfs, Dictionarystring, 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);}}#endregionrowIndex 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;}}#endregionrowIndex;}workbook.Write(fs,true);fs.Close();}#endregion#region 导出excel表格/// summary/// DataTable导出到Excel文件xls文件/// /summary/// param namedtSource数据源/param/// param namestrHeaderText表名/param/// param namestrFileNameexcel文件名/param/// param namedirDataTable和excel列名对应字典/param/// param namesheetRow每个sheet存放的行数/parampublic static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName, Dictionarystring, string dir, bool isNew, int sheetRow 50000){int currentSheetCount GetSheetNumber(strFileName);//现有的页数sheetnumif (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 namedtSource数据源/param/// param namedir导出Excel表格的字段名和列名的字符串字典实例例如dir.Add(IllegalKeywords, 姓名);/parampublic static XSSFWorkbook ExportExcel(DataTable dtSource, Dictionarystring, 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 (KeyValuePairstring,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 namefilePath/paramprivate 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文件最后一次保存日期}#endregionreturn xssfworkbook;}/// summary/// 创建excel表头/// /summary/// param namedgv/param/// param nameexcelSheet/paramprivate 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/// 插入数据行/// /summaryprivate 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 namedtSource/param/// param namedrSource/param/// param namecurrentExcelRow/param/// param nameexcelSheet/param/// param nameexcelWorkBook/paramprivate 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 nameworkbookExcel文件对象/param/// param namecellStyleExcel文件中XSSFCellStyle对象/param/// param namehAlignment水平布局方式/param/// param namevAlignment垂直布局方式/param/// param namefontHeightInPoints字体大小/param/// param nameisAddBorder是否需要边框/param/// param nameboldWeight字体加粗 (None 0,Normal 400Bold 700/param/// param namefontName字体仿宋楷体宋体微软雅黑...与Excel主题字体相对应/param/// param nameisAddBorderColor是否增加边框颜色/param/// param nameisItalic是否将文字变为斜体/param/// param nameisLineFeed是否自动换行/param/// param nameisAddCellBackground是否增加单元格背景颜色/param/// param namefillPattern填充图案样式(FineDots 细点SolidForeground立体前景isAddFillPatterntrue时存在)/param/// param namecellBackgroundColor单元格背景颜色当isAddCellBackgroundtrue时存在/param/// param namefontColor字体颜色/param/// param nameunderlineStyle下划线样式无下划线[None],单下划线[Single],双下划线[Double],会计用单下划线[SingleAccounting],会计用双下划线[DoubleAccounting]/param/// param nametypeOffset字体上标下标(普通默认值[None],上标[Sub],下标[Super]),即字体在单元格内的上下偏移量/param/// param nameisStrikeout是否显示删除线/param/// param namedataFormat格式化日期显示/param/// returns/returnspublic 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 dataFormatyyyy-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文件中将数据导出到ListT对象集合/// summary/// 将制定sheet中的数据导出到DataTable中/// /summary/// param namesheet需要导出的sheet/param/// param nameHeaderRowIndex列头所在行号-1表示没有列头/param/// param namedirexcel列名和DataTable列名的对应字典/param/// returns/returnsstatic DataTable ImportDt(ISheet sheet, int HeaderRowIndex, Dictionarystring, 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 转换为ListT对象集合/// /summary/// typeparam nameTResult类型/typeparam/// param namedtDataTable/param/// returns/returnspublic static ListTResult DataTableToListTResult(this DataTable dt) where TResult : class, new(){//创建一个属性的列表ListPropertyInfo prlist new ListPropertyInfo();//获取TResult的类型实例 反射的入口Type t typeof(TResult);//获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表Array.ForEachPropertyInfo(t.GetProperties(), p { if (dt.Columns.IndexOf(p.Name) ! -1) prlist.Add(p); });//创建返回的集合ListTResult oblist new ListTResult();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 nameT实体对象/typeparam/// param namedtdatatable表/param/// param nameisStoreDB是否存入数据库datetime字段date字段没事取出不用判断/param/// returns返回list集合/returnsprivate static ListT DataTableToListT(DataTable dt, bool isStoreDB true){ListT list new ListT();Type type typeof(T);//Liststring listColums new Liststring();PropertyInfo[] pArray type.GetProperties(); //集合属性数组foreach (DataRow row in dt.Rows){T entity Activator.CreateInstanceT(); //新建对象实例 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 nameT/typeparam/// param nameds/param/// param nametableIndext/param/// returns/returnsprivate static ListT DataTable2ListT(DataTable dt){//确认参数有效 if (dt null || dt.Rows.Count 0){return null;}IListT list new ListT(); //实例化一个list // 在这里写 获取T类型的所有公有属性。 注意这里仅仅是获取T类型的公有属性不是公有方法也不是公有字段当然也不是私有属性 PropertyInfo[] tMembersAll typeof(T).GetProperties();for (int i 0; i dt.Rows.Count; i){//创建泛型对象。为什么这里要创建一个泛型对象呢是因为目前我不确定泛型的类型。 T t Activator.CreateInstanceT();//获取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(vvs.100).aspx/htmltMember.SetValue(t, Convert.ToString(dt.Rows[i][j]), null);}else{tMember.SetValue(t, null, null);}break;//注意这里的break是写在if语句里面的意思就是说如果列名和属性名称相同并且已经赋值了那么我就跳出foreach循环进行j1的下次循环 }}}list.Add(t);}dt.Dispose();return list.ToList();}/// summary/// 读取Excel文件特定名字sheet的内容到ListT对象集合/// /summary/// param namestrFileNameexcel文件路径/param/// param namedirexcel列名和DataTable列名的对应字典/param/// param nameSheetNameexcel表名/param/// param nameHeaderRowIndex列头所在行号-1表示没有列头/param/// returns/returnspublic static ListT ImportExceltoDtT(string strFileName, Dictionarystring, 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;}}ListT results DataTableToListT(table);table.Dispose();return results;}/// summary/// 读取Excel文件某一索引sheet的内容到DataTable/// /summary/// param namestrFileNameexcel文件路径/param/// param namesheet需要导出的sheet序号/param/// param nameHeaderRowIndex列头所在行号-1表示没有列头/param/// param namedirexcel列名和DataTable列名的对应字典/param/// returns/returnspublic static ListT ImportExceltoDtT(string strFileName, Dictionarystring, 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;}}ListT results DataTableToListT(table);table.Dispose();return results;}#endregion/// summary/// 获取excel文件的sheet数目/// /summary/// param nameoutputFile/param/// returns/returnspublic 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 namemessage/param/// param nameresult/param/// returns/returnspublic 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;}elsereturn false;}/// summary/// 验证导入的Excel是否有数据/// /summary/// param nameexcelFileStream/param/// returns/returnspublic 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/// 排序实现接口 不进行排序 根据添加顺序导出/// /summarypublic 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;//导入的文件地址路径可动态传入Dictionarystring, string dir new Dictionarystring, string();//申明excel列名和DataTable列名的对应字典dir.Add(IllegalKeywords,姓名);Listkeywords keyWordsList ExcelHelper.ImportExceltoDtkeywords(filepath, dir,Sheet1,0);#region 将List动态添加至数据库//……#endregionreturn Json(new { code 200, msg 导入成功 });
}/// summary
/// Excel导出的具体实现
/// /summary
/// returns/returns
public IActionResult export_excel()
{#region 添加测试数据Listkeywords keys new Listkeywords();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);//ListT对象集合转DataTable#endregionstring filename DateTime.Now.ToString(在线用户yyyyMMdd) .xlsx;Dictionarystring, string dir new Dictionarystring, 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再根据需求调用添加方法实现数据的添加
