.NetCore WebAPI 导入、导出Excel文件
导入
思路：上传Excel文件，使用MemoryStream 在内存中加载，使用NPOI读取内容到Model类中。
/// <summary>
/// 导入Excel文件
/// </summary>
/// <param name="excelFile"></param>
/// <returns></returns>
[HttpPost]
public IActionResult UploadUserInfo(IFormFile excelFile)
{
try
{
var postFile = Request.Form.Files[0];
string extName = Path.GetExtension(postFile.FileName);
if (!new string[] { ".xls", ".xlsx" }.Contains(extName))
{
return Ok(new
{
error = 1,
msg = "必须是Excel文件"
});
}
MemoryStream ms = new MemoryStream();
postFile.CopyTo(ms);
ms.Position = 0;
IWorkbook wb = null;
if (extName.ToLower().Equals(".xls")) // 97-2003版本
{
wb = new HSSFWorkbook(ms);
}
else
{
wb = new XSSFWorkbook(ms); // 2007以上版本
}
ISheet sheet = wb.GetSheetAt(0);
//总行数（0开始）
int totalRow = sheet.LastRowNum;
// 总列数（1开始）
int totalColumn = sheet.GetRow(0).LastCellNum;
List<Stu> stuList = new();
for (int i = 1; i <= totalRow; i++)
{
IRow row = sheet.GetRow(i);
// 判定第5列的值是不是日期，日期的值类型可以按日期来读，也可以用数据的方式来读
var isDate = DateUtil.IsCellDateFormatted(row.GetCell(4));
string StuName = row.GetCell(0).StringCellValue;
int Sex = row.GetCell(1).StringCellValue == "男" ? 0 : 1;
string Phone = ((long)row.GetCell(2).NumericCellValue).ToString();
int CId = (int)row.GetCell(3).NumericCellValue;
DateTime InDate = row.GetCell(4).DateCellValue;
decimal JF = (decimal)row.GetCell(5).NumericCellValue;
// 第6列有可能是空的
string Pic = "";
if(row.GetCell(6) != null)
{
CellType type = row.GetCell(6).CellType;
if (type != CellType.Blank)
{
Pic = row.GetCell(6).StringCellValue;
}
}
int State = (int)row.GetCell(7).NumericCellValue;
var stu = new Stu
{
StuName = StuName,
Sex = Sex,
Phone = Phone,
CId = CId,
InDate = InDate,
JF = JF,
Pic =Pic,
State = State,
IsOk = true,
};
stuList.Add(stu);
}
db.Stu.AddRange(stuList);
db.SaveChanges();
wb.Close();
return Ok(new
{
error = 0,
importCount = stuList.Count,
msg = ""
});
}
catch (Exception)
{
throw;
}
}
导出
导出后端
思路：使用NPOI使用 IWorkBook ，一行一行写入要导出数据，最终返回 FileContentResult
默认（不使用模板）
/// <summary>
/// 导出所有的信息为Excel
/// </summary>
/// <returns></returns>
[HttpGet]
public IActionResult ExportExcel()
{
try
{
var list = db.Stu.Where(s => s.IsOk).ToList();
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
// 第一行 标题
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("姓名");
row.CreateCell(1).SetCellValue("性别");
row.CreateCell(2).SetCellValue("手机号码");
row.CreateCell(3).SetCellValue("学院");
row.CreateCell(4).SetCellValue("入学日期");
row.CreateCell(5).SetCellValue("综合积分");
row.CreateCell(6).SetCellValue("照片");
row.CreateCell(7).SetCellValue("状态");
// 第二行 写数据
int i = 1;
foreach (var item in list)
{
row = sheet.CreateRow(i);
row.CreateCell(0).SetCellValue(item.StuName);
row.CreateCell(1).SetCellValue(item.Sex == 0 ? "男" : "女");
row.CreateCell(2).SetCellValue(double.Parse(item.Phone));
row.CreateCell(3).SetCellValue(item.CId);
// 日期格式的导出
ICell cell = row.CreateCell(4);
ICellStyle style = wb.CreateCellStyle();
IDataFormat format = wb.CreateDataFormat();
style.DataFormat = format.GetFormat("yyyy-MM-dd");
cell.CellStyle = style;
cell.SetCellValue(DateTime.Parse(item.InDate.ToString("yyyy-MM-dd")));
row.CreateCell(5).SetCellValue((double)item.JF);
row.CreateCell(6).SetCellValue(item.Pic);
row.CreateCell(7).SetCellValue(item.State);
i++;
}
// 写 WorkBook信息到 内存流中
byte[] buffer = null;
using (MemoryStream ms = new MemoryStream())
{
wb.Write(ms);
buffer = ms.ToArray();
}
// .xlsx文件对应的Mime信息
var mime = new FileExtensionContentTypeProvider().Mappings[".xlsx"];
return File(buffer, mime, "学生信息.xlsx");
}
catch (Exception)
{
throw;
}
}
使用模板
/// <summary>
/// 导出Excel（使用模板）
/// </summary>
/// <returns></returns>
[HttpGet]
public IActionResult ExportExcelByTemplate()
{
try
{
IWorkbook wb = null;
var template = Directory.GetCurrentDirectory() + @"\wwwroot\Template\template.xlsx";
// 按模板内容创建 IWorkbook
using(FileStream fs = new FileStream(template, FileMode.OpenOrCreate))
{
wb = new XSSFWorkbook(fs);
}
var list = db.Stu.Where(s => s.IsOk).ToList();
ISheet sheet = wb.GetSheetAt(0);
int i = 1;
IRow row = null;
foreach (var item in list)
{
row = sheet.CreateRow(i);
row.CreateCell(0).SetCellValue(item.StuName);
row.CreateCell(1).SetCellValue(item.Sex == 0 ? "男" : "女");
row.CreateCell(2).SetCellValue(double.Parse(item.Phone));
row.CreateCell(3).SetCellValue(item.CId);
// 日期格式的导出
ICell cell = row.CreateCell(4);
ICellStyle style = wb.CreateCellStyle();
IDataFormat format = wb.CreateDataFormat();
style.DataFormat = format.GetFormat("yyyy-MM-dd");
cell.CellStyle = style;
cell.SetCellValue(DateTime.Parse(item.InDate.ToString("yyyy-MM-dd")));
row.CreateCell(5).SetCellValue((double)item.JF);
row.CreateCell(6).SetCellValue(item.Pic);
row.CreateCell(7).SetCellValue(item.State);
i++;
}
byte[] buffer = null;
using (MemoryStream ms = new MemoryStream())
{
wb.Write(ms);
buffer = ms.ToArray();
}
// .xlsx文件对应的Mime信息
var mime = new FileExtensionContentTypeProvider().Mappings[".xlsx"];
return File(buffer, mime, "学生信息.xlsx");
}
catch (Exception)
{
throw;
}
}
导出前端（调用）
使用 axios 调用
// 导出为Excel文件(.xlsx)
// 简单方法
exportExce() {
let url =
"http://localhost:23474/api/Stu/ExportExcel?page=1&size=4&bId=0"; //可以在路径中传递参数
window.location.href = url;
},
// 标准方法
exportExcel() {
this.axios
.get("http://localhost:23474/api/Stu/ExportExcel", {
responseType: "blob",
})
.then((res) => {
var blob = new Blob([res.data], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
});
var a = document.createElement("a"); // js创建一个a标签
var href = window.URL.createObjectURL(blob); // 文档流转化成Base64
a.href = href;
a.download = "学生数据.xlsx"; // 下载后文件名
document.body.appendChild(a);
a.click(); // 点击下载
document.body.removeChild(a); // 下载完成移除元素
window.URL.revokeObjectURL(href);
});
},
