C#使用NPOI导出根据指定路径模板的Excel

前言：在一些比较复杂的表格，直接使用指定路径的模板比动态生成效率要高，现在以三级表头的为例,

客户要求我们导出这样风格的表格

直接引用模板 套用样式，我这里前端使用的是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插件