前言:在一些比较复杂的表格,直接使用指定路径的模板比动态生成效率要高,现在以三级表头的为例,
客户要求我们导出这样风格的表格
直接引用模板 套用样式,我这里前端使用的是layui
function Btn() {
//加载等待
layer.load(2);
let name = "工作簿的名字"
var formData = new FormData();
formData.append('parameter',parameter);
var url = //请求Url;
var xhr = new XMLHttpRequest();
xhr.open('POST', url, true); // 也可以使用POST方式,根据接口
xhr.send(formData);
xhr.responseType = "blob"; // 返回类型blob
xhr.onload = function () {
if (this.status === 200) {
var blob = this.response;
var reader = new FileReader();
reader.readAsDataURL(blob); // 转换为base64,可以直接放入a表情href
reader.onload = function (e) {
var a = document.createElement('a');
a.download = `${name}.xls`;//下载文件名
a.href = e.target.result;
$("body").append(a); // 修复firefox中无法触发click
a.click();
$(a).remove();
}
layer.confirm('下载成功!', { shade: 0 }, function (index) {
layer.close(layer.index);
})
layer.closeAll('loading');
}
else {
layer.closeAll('loading');
layer.msg("下载失败!", { icon: 5, shift: 5, offset: "auto" }, function () { });
//layer.msg("下载失败!");
}
};
}
后端代码:
[HttpPost]
public async Task<IActionResult> DownloadFile(string parameter)
{
try
{
var url = "文件的相对路径";
var path = _env.WebRootPath;
url = path + url;
IWorkbook workbook = null;
string extension = Path.GetExtension(url);
FileStream fs = System.IO.File.OpenRead(url);
if (extension.Equals(".xls"))
{
//把xls文件中的数据写入workbook中
workbook = new HSSFWorkbook(fs);
}
else
{
//把xlsx文件中的数据写入workbook中
workbook = new XSSFWorkbook(fs);
}
fs.Close();
//读取当前表数据
ISheet sheet = workbook.GetSheetAt(0);
IRow row = sheet.GetRow(0); //读取当前行数据
//LastRowNum 是当前表的总行数-1(注意)
int offset = 0;
ICellStyle style1 = workbook.CreateCellStyle();
style1.BorderBottom = BorderStyle.Thin;
style1.BorderRight = BorderStyle.Thin;
style1.BorderTop = BorderStyle.Thin;
style1.BorderLeft = BorderStyle.Thin;
style1.VerticalAlignment = VerticalAlignment.Center;
style1.Alignment = HorizontalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.FontName = "微软雅黑";
font1.FontHeight = 20 * 20;
font1.FontHeightInPoints = 12;
style1.SetFont(font1);
var allNumber = 0; //定义列数
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i); //读取当前行数据
if (row != null)
{
//LastCellNum 是当前行的总列数
allNumber=row.LastCellNum-1;
for (int j = 0; j < row.LastCellNum; j++)
{
row.GetCell(j).SetCellValue(row.GetCell(j).ToString());
}
}
}
var list =null; //数据源
for (int i = 0; i < list.Count; i++)
{
var getDetailsList = list[i].StaticInfos;
IRow row1 = sheet.CreateRow(i + 3);
for (int j = 0; j <= allNumber; j++)
{
ICell cell = row1.CreateCell(j);
cell.CellStyle = style1;
}
//匹配对应的列就行了
for (int k = 0; k <= allNumber; k++)
{
switch (k)
{
case 0:
row1.GetCell(k).SetCellValue((i + 1).ToString());
break;
case 1:
row1.GetCell(k).SetCellValue(list[i].?);
break;
//……
}
row1.GetCell(k).CellStyle = style1;
}
}
byte[] buffer = new byte[1024 * 2];
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
buffer = ms.ToArray();
ms.Close();
}
return File(buffer, "application/msword");
}
catch (Exception ex)
{
throw ex;
}
}
以上就是NPOI的简单使用方法。
注:必须要借助NPOI插件