©2020-2024 ioDraw All rights reserved,
Privacy Policy
什么是NPOI？
NPOI是指构建在POI 3.x版本之上的一个程序，NPOI可以在没有安装Office的情况下对Word或Excel文档进行读写操作。
NPOI是一个开源的C#读写Excel、WORD等微软OLE2组件文档的项目。
使用NuGet安装NPOI
NuGet直接搜索NPOI，目前版本是v2.4.1，将其安装至项目即可。
安装完成后，项目会自动为我们添加这4个引用
同时还需要在程序中引入NPOI.SS.UserModel;NPOI.XSSF.UserModel;NPOI.HSSF.UserModel;三个命名空间
废话不多说，直接上代码
DataTable导出Excel
/// <summary> /// Datable导出成Excel /// </summary> /// <param
name="dt"></param> /// <param name="file">导出路径(包括文件名与扩展名)</param> public static
void TableToExcel(DataTable dt, string file) { IWorkbook workbook; string
fileExt = Path.GetExtension(file).ToLower(); if (fileExt == ".xlsx") { workbook
= new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new
HSSFWorkbook(); } else { workbook = null; } if (workbook == null) { return; }
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ?
workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName); //表头 IRow
row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell
cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); } //数据
for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString()); } } //转为字节数组 MemoryStream stream =
new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray();
//保存为Excel文件 using (FileStream fs = new FileStream(file, FileMode.Create,
FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } }
Excel导入DataTable
/// <summary> /// Excel导入成Datable /// </summary> /// <param
name="file">导入路径(包含文件名与扩展名)</param> /// <returns></returns> public static
DataTable ExcelToTable(string file) { DataTable dt = new DataTable(); IWorkbook
workbook; string fileExt = Path.GetExtension(file).ToLower(); using (FileStream
fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { //XSSFWorkbook
适用XLSX格式，HSSFWorkbook 适用XLS格式 if (fileExt == ".xlsx") { workbook = new
XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new
HSSFWorkbook(fs); } else { workbook = null; } if (workbook == null) { return
null; } ISheet sheet = workbook.GetSheetAt(0); //表头 IRow header =
sheet.GetRow(sheet.FirstRowNum); List<int> columns = new List<int>(); for (int
i = 0; i < header.LastCellNum; i++) { object obj =
GetValueType(header.GetCell(i)); if (obj == null || obj.ToString() ==
string.Empty) { dt.Columns.Add(new DataColumn("Columns" + i.ToString())); }
else dt.Columns.Add(new DataColumn(obj.ToString())); columns.Add(i); } //数据 for
(int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow dr =
dt.NewRow(); bool hasValue = false; foreach (int j in columns) { dr[j] =
GetValueType(sheet.GetRow(i).GetCell(j)); if (dr[j] != null && dr[j].ToString()
!= string.Empty) { hasValue = true; } } if (hasValue) { dt.Rows.Add(dr); } } }
return dt; } /// <summary> /// 获取单元格类型 /// </summary> /// <param
name="cell"></param> /// <returns></returns> private static object
GetValueType(ICell cell) { if (cell == null) return null; switch
(cell.CellType) { case CellType.Blank: //BLANK: return null; case
CellType.Boolean: //BOOLEAN: return cell.BooleanCellValue; case
CellType.Numeric: //NUMERIC: return cell.NumericCellValue; case
CellType.String: //STRING: return cell.StringCellValue; case CellType.Error:
//ERROR: return cell.ErrorCellValue; case CellType.Formula: //FORMULA: default:
return "=" + cell.CellFormula; } }