.netCore导入excel文档结合Layui表格
导入excel文件并保存到数据库然后在layui表格中显示出来
提示:以下是本篇文章正文内容,下面案例可供参考
一、前端页面部分
通过layui中的文件上传按钮,来上传文件
<button type="button" class="layui-btn layui-btn-xs" id="test3"><i class="layui-icon"></i>导入</button>
layui.use(['upload', 'element', 'layer'], function () {
var $ = layui.jquery
, upload = layui.upload
, element = layui.element
, layer = layui.layer;
upload.render({ //允许上传的文件后缀
elem: '#test3'
, url: '/AreaGongyi/ImportCustomer' //此处配置你自己的上传接口即可
, accept: 'file' //普通文件
, exts: 'xls|xlsx|' //只允许上传excel文件
, done: function (res) {
layer.msg('上传成功');
console.log(res)
}
});
});
下面是copy一位博主的实现代码:https://www.cnblogs.com/mojie/p/10550268.html
二、控制器接收导入的文件
代码如下(示例):
//导入excel
public async Task<IActionResult> ImportCustomer(IFormFile file)
{
ResultData resultData = new ResultData();
DataTable dt = new DataTable();
//var json = new
//{
// okMsg = "添加成功!"
//};
var json = "";
if (file.Length > 0)
{
string strMsg;
//利用IFormFile里面的OpenReadStream()方法直接读取文件流
dt = ExcelHelper.ExcelToDatatable(file.OpenReadStream(), Path.GetExtension(file.FileName), out strMsg);
if (!string.IsNullOrEmpty(strMsg))
{
resultData.Code = -1;
resultData.Msg = strMsg;
//return dt;
}
if (dt.Rows.Count > 0)
{
for (int i = 0,j=0; i < dt.Rows.Count; i++,j++)
{
AreaGongyi areaGongyi = new AreaGongyi();
areaGongyi.AreaName = dt.Rows[i][0].ToString();
areaGongyi.TotalQuantity = decimal.Parse(dt.Rows[i][1].ToString());
areaGongyi.CDouble = decimal.Parse(dt.Rows[i][2].ToString());
areaGongyi.Hc_36 = decimal.Parse(dt.Rows[i][3].ToString());
areaGongyi.Kl_10= decimal.Parse(dt.Rows[i][4].ToString());
areaGongyi.Hc_1 = decimal.Parse(dt.Rows[i][5].ToString());
areaGongyi.C3D = decimal.Parse(dt.Rows[i][6].ToString());
areaGongyi.Kl_8 = decimal.Parse(dt.Rows[i][7].ToString());
conn.AreaGongyi.Add(areaGongyi);
await conn.SaveChangesAsync();
}
json = "{\"code\":0,\"count\":\"" + dt + "\",\"data\": " + dt.ToJson() + " }";
return Content(json);
}
else
{
resultData.Code = -1;
resultData.Msg = "Excel导入表无数据!";
}
}
return Content(json);
}
三、读取excel文件内容,并写入datatable
代码如下(示例):
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
namespace Core.DAL
{
public class ExcelHelper
{
public static DataTable ExcelToDatatable(Stream stream, string fileType, out string strMsg, string sheetName = null)
{
strMsg = "";
DataTable dt = new DataTable();
ISheet sheet = null;
IWorkbook workbook = null;
try
{
#region 判断excel版本
//2007以上版本excel
if (fileType == ".xlsx")
{
workbook = new XSSFWorkbook(stream);
}
//2007以下版本excel
else if (fileType == ".xls")
{
workbook = new HSSFWorkbook(stream);
}
else
{
throw new Exception("传入的不是Excel文件!");
}
#endregion
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum;
for (int i = firstRow.FirstCellNum; i < cellCount; i++)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue.Trim();
if (!string.IsNullOrEmpty(cellValue))
{
DataColumn dataColumn = new DataColumn(cellValue);
dt.Columns.Add(dataColumn);
}
}
}
DataRow dataRow = null;
//遍历行
for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
{
IRow row = sheet.GetRow(j);
dataRow = dt.NewRow();
if (row == null || row.FirstCellNum < 0)
{
continue;
}
//遍历列
for (int i = row.FirstCellNum; i < cellCount; i++)
{
ICell cellData = row.GetCell(i);
if (cellData != null)
{
//判断是否为数字型,必须加这个判断不然下面的日期判断会异常
if (cellData.CellType == CellType.Numeric)
{
//判断是否日期类型
if (DateUtil.IsCellDateFormatted(cellData))
{
dataRow[i] = cellData.DateCellValue;
}
else
{
dataRow[i] = cellData.ToString().Trim();
}
}
else
{
dataRow[i] = cellData.ToString().Trim();
}
}
}
dt.Rows.Add(dataRow);
}
}
else
{
throw new Exception("没有获取到Excel中的数据表!");
}
}
catch (Exception ex)
{
strMsg = ex.Message;
}
return dt;
}
}
}
总结
以上就是我记录的导入excel文件并保存到数据库,然后在layui表格中显示的代码,里面还是有些东西没有理解,也不熟悉,是在网上找的,好在拼拼凑凑功能实现了,先记录在这吧,日后会常看的,第一次记录就到这里,希望自己能坚持记录下去。