使用NPOI进行Excel导入的前后端如何去写
- Html:这里由于原生的上传按钮实在是太丑了,我在这里将input隐藏,再使用一个button去点击触发;
<button class="btn btn-outline-dark" onclick="exportExcel()">批量导入</button>
<form id="form1" method="post" enctype="multipart/form-data">
<input type="file" hidden id="fileInput" accept=".xlsx" />
</form>
- JS:向后台发送请求,上传Excel
function exportExcel() {
return $("#fileInput").click();
}
$('body').on('change', '#fileInput', function (event) {
var formData = new FormData();
var name = $($(this)).val();
var files = $($(this))[0].files[0];
formData.append("file", files);
formData.append("name", name);
var index = layer.load();
$.ajax({
url: '@Url.Action("ExportExcel", "HotMessage")',
type: 'POST',
data: formData,
processData: false,
contentType: false,
dataType: 'json',
success: function (res) {
layer.close(index);
layer.msg('导入成功!');
event.target.value = "";//此处主要是为了处理重复不能重复上传同一文件的问题,如果没有这个需求,可以不需要
initialHotMessage(10, 1);//更新查询列表
}
, error: function (res) {
layer.msg('导入失败!');
}
});
})
- 使用NPOI来导入Excel,这里我们需要添加一些引用,大家可以去nuget官网去下载,注意下载的版本要保持一致,需要用到的一下dll如下,如果不想找的话,可以去我的主页下载DLL引用下载,给打包好了。:
ICSharpCode.SharpZipLib.dll
NPOI.dll
NPOI.OOXML.dll
NPOI.OpenXml4Net.dll
NPOI.OpenXmlFormats.dll
- 后台的具体写法,就是导入excel,然后
public JsonResult ExportExcel()
{
var xx = Request.Files.Get(0);
DataTable dt = new DataTable();
IWorkbook workbook;
var file = xx.FileName;
string fileExt = Path.GetExtension(file).ToLower();
file = Path.GetFileName(file);
file = Guid.NewGuid() + fileExt;
string filePath = "~/Files/" + file;
//获取文件的物理路径
string AbsolutePath = Server.MapPath(filePath);
LogHelper.WriteError("AbsolutePath:" + AbsolutePath);
//保存上传文件
xx.SaveAs(AbsolutePath);
//将excel文件导入处理成datatable,大家可以将这个封装一下,
using (FileStream fs = new FileStream(AbsolutePath, 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 Json(new { msg = "" }); }
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 = 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] = sheet.GetRow(i).GetCell(j);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return Json(ResolveDataTable(dt));
}
/// <summary>
/// 处理DataTable,做一些判断,然后批量插入数据库就搞定啦。
/// </summary>
/// <param name="dt"></param>
public string ResolveDataTable(DataTable dt)
{
//可做一些判断,我的这块删除了
var email=GetEmail();
List<MessageMaster> list=new List<MessageMaster>();
for (int i = 0; i < dt.Rows.Count; i++)
{
MessageMaster message = new MessageMaster();
message.MessageCategoryVal = dt.Rows[i]["分类"].ToString();
//等等等……………………
list.Add(message);
}
if (result == "" && list != null && list.Count > 0)
{
return messageDAL.BatchInsert(list) ? "success" : "fail";
}
return result;
}