番外-asp.net使用npoi读取excel模板并导出下载

1，Nuget安装NPOI

2，添加引用

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System.IO;

3，代码

// 导出
private void ExportAbnorData2Excel() {    
       HttpContext context = HttpContext.Current;
	   var workbook = new HSSFWorkbook();
            
            using (FileStream file = new FileStream(PathHelper.CurrentPath + "xxx/xxx.xls", FileMode.Open, FileAccess.Read))
            {
                //将文件流中模板加载到工作簿对象中
                workbook = new HSSFWorkbook(file);
            } 
            string FileName =DateTime.Now.ToString("yyyyMMddhhss") + ".xls";
            string stringPath = PathHelper.CurrentPath + "xxx/";
            string filePath = $"{stringPath}";
            if (!System.IO.Directory.Exists(filePath))
            {
                Directory.CreateDirectory(filePath);
            }
            using (FileStream fs = System.IO.File.Create(filePath + FileName))
            {

                #region 考核列表
                ISheet sheet = workbook.GetSheetAt(0);//获取sheet
                IRow rowHeader = sheet.GetRow(2);
                //创建头部样式和列宽度
                ICellStyle titleStyle = workbook.CreateCellStyle();
                titleStyle.Alignment = HorizontalAlignment.Center; // 居中
                short defHeight = sheet.DefaultRowHeight;
                //创建内容样式
                ICellStyle style = workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center; ;//垂直居中   
                //赋值
                IRow rowTime = sheet.GetRow(1);
                rowTime.GetCell(10).SetCellValue("生成时间：" + DateTime.Now.ToString("yyyy-MM-dd"));
                for (int i = 0; i < list.Count; i++)
                {

                    IRow rowData = sheet.CreateRow(i + 3);
                    rowData.CreateCell(0).SetCellValue(i+1);
                    rowData.GetCell(0).CellStyle = style;

                    rowData.CreateCell(1).SetCellValue(list[i].XNUMBER);
                    rowData.GetCell(1).CellStyle = style;

                    rowData.CreateCell(2).SetCellValue(list[i].NAME);
                    rowData.GetCell(2).CellStyle = style;

                    string picurl = list[i].IMG;  //图片存储路径
                    if (picurl!=null)
                    {
                        rowData.Height = (short)(40.5 * 20);
                        AddPieChart(sheet, workbook, picurl, i + 3, 3);
                    }
                    else
                    {
                        rowData.CreateCell(3).SetCellValue("未找到图片");
                        rowData.GetCell(3).CellStyle = style;
                    }
                    rowData.CreateCell(4).SetCellValue(list[i].ANAME);
                    rowData.GetCell(4).CellStyle = style;

                    rowData.CreateCell(5).SetCellValue(list[i].BNAME);
                    rowData.GetCell(5).CellStyle = style;

                    rowData.CreateCell(6).SetCellValue(list[i].CNAME);
                    rowData.GetCell(6).CellStyle = style;

                    rowData.CreateCell(7).SetCellValue(list[i].DNAME);
                    rowData.GetCell(7).CellStyle = style;

                    rowData.CreateCell(8).SetCellValue(list[i].XATIME.ToString());
                    rowData.GetCell(8).CellStyle = style;

                    rowData.CreateCell(9).SetCellValue(list[i].XBTIME.ToString());
                    rowData.GetCell(9).CellStyle = style;

                    rowData.CreateCell(10).SetCellValue(list[i].XCTIME.ToString());
                    rowData.GetCell(10).CellStyle = style;
                }
                #endregion
                workbook.Write(fs);
                context.Response.Write("../../../../xxx/" + FileName);

            }

}

       // 导出图片

        private void AddPieChart(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col)
        {
            try
            {
                string stringPath = PathHelper.CurrentPath + $"{fileurl.Replace("/", "\\")}";
                if (System.IO.File.Exists(stringPath))
                {
                    string FileName = stringPath;
                    byte[] bytes = System.IO.File.ReadAllBytes(FileName);
                    if (!string.IsNullOrEmpty(FileName))
                    {
                        int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
                        HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 30, 30, col, row, col + 1, row + 1);
                        //##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50

                        HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

                        // pict.Resize();这句话一定不要，这是用图片原始大小来显示
                    }
                }

            }
            catch (Exception ex)
            {

            }
        }

4，模板

5，结果