引用
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Reflection;
/// <summary>
/// 读取Sheet数据行
/// </summary>
// <param name="workbook">工作区</param>
/// <param name="sheet">sheet名</param>
public List<T> ReadExcelSheet<T>(string filePath, string sheetName = null)
{
if (!File.Exists(filePath))
{
//添加错误日志代码--无法找到该文件
return null;
}
//获取文件信息
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
IWorkbook workbook = WorkbookFactory.Create(fs);
List<T> list = new List<T>();
//获取sheet信息
ISheet sheet = null;
DataSet ds = new DataSet();
//查询指定Sheet
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
//添加错误日志代码--无法找到该sheet
return null;
}
return ReadExcel<T>(workbook, sheet);
}
//遍历所有sheet
else
{
//获取sheet名
int sheetCount = workbook.NumberOfSheets;
for (int i = 0; i < sheetCount; i++)
{
sheet = workbook.GetSheetAt(i);
if (sheet != null)
{
return ReadExcel<T>(workbook, sheet);
}
}
}
return list;
}
/// <summary>
/// 读取Sheet数据行
/// </summary>
/// <param name="workbook">工作区</param>
/// <param name="sheet">sheet名</param>
private List<T> ReadExcel<T>(IWorkbook workbook, ISheet sheet)
{
var t = Activator.CreateInstance<T>();
DataTable dt = new DataTable();
IRow cells = sheet.GetRow(sheet.FirstRowNum); //获取列信息
int cellsCount = cells.PhysicalNumberOfCells;//总列数
int cellIndex = sheet.FirstRowNum; //获取Sheet数
List<string> listColumns = new List<string>();
for (int i = 0; i < cellsCount; i++)
{
listColumns.Add(cells.GetCell(i+1).StringCellValue);
}
List<T> list = new List<T>();
Type ty = t.GetType();
int rowsCount = sheet.PhysicalNumberOfRows;//统计数据行
for (int i = cellIndex + 1; i < rowsCount; i++)
{
var entity = Activator.CreateInstance<T>();
cells = sheet.GetRow(i);
foreach (var item in ty.GetProperties())
{
string DisplayName = item.GetCustomAttribute<DisplayNameAttribute>()?.DisplayName; //获取字段特性中文名称
int index = listColumns.IndexOf(DisplayName); //查找是否存在 ? -1 : 下标
if (index > -1)
{
var cell = sheet.GetRow(i).GetCell(index+1).ToString();
item.SetValue(entity, cell);
}
}
list.Add(entity);
}
return list;
}
控制器代码(判断条件根据需求自行添加吧)
public string Import(IFormFile formFile)
{
string sWebRootFolder = _IHostingEnvironment.WebRootPath;
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, formFile.FileName));
//文件上传服务器
using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
{
formFile.CopyTo(fs);
fs.Close();
List<Class> list = ReadExcelSheet<Class>(file.ToString());
for (int i = 0; i < list.Count(); i++)
{
//循环添加
}
file.Delete();//删除服务器临时文件
return "导出成功";
}
}
前端代码
ajax配置请求头
方式一
var config = {
headers:{
"content-type":"multipart/form-data"
}
}
$.post(url,参数,config ){ }
方式二
ajax{
method: 'Post',
url: '/SysCompany/ImportExcel',
data:formFile,
headers:{
"content-type":"multipart/form-data"
}
}
Vue控件作为代码演示
<el-button
@click="ImportExcel"
>
导入
</el-button>
<input v-show="false" ref="fileRef" id="fileid" type="file" value="" @change="fileChange($event)">
//导入
ImportExcel() {
//调用File控件
this.$refs.fileRef.dispatchEvent(new MouseEvent('click'));
},
fileChange(e) {
var form = new FormData();
form.append("formFile",e.target.files[0])
form.append("ABC","测试代码")
codeService.Import(form).then(res => {
alert(res)
}).catch(err => {
console.log(err)
})
},
参数名必须与后端参数对应