使用NPOI将多张图片导入execl

 protected void btn_Export_Click(object sender, EventArgs e)
    {
        List<BNXX_SJXJ_XJSJ> list = ViewState["data"] as List<BNXX_SJXJ_XJSJ>;


        string[] AppPaths = HttpRuntime.AppDomainAppPath.ToString().Split('\\');
        string PicPath = string.Empty;
        for (int i = 0; i < AppPaths.Length - 2; i++)
        {
            PicPath = PicPath + "/" + AppPaths[i];
        }
        PicPath = PicPath.Substring(1) + "/mytest/Photos";
        ///调用模板，先保存，后下载
        string path = Server.MapPath("../../template/SJingSJ.xls");
        using (FileStream fs = File.OpenRead(path))
        {
            using (Workbook wk = new HSSFWorkbook(fs))
            {
                int pictureIndex1 = AddPictureToWorkbook(list[0].XQSY, PicPath, wk);
                int pictureIndex2 = AddPictureToWorkbook(list[0].XZSY, PicPath, wk);
                int pictureIndex3 = AddPictureToWorkbook(list[0].XHSY, PicPath, wk);

                using (Sheet sheet = wk.GetSheetAt(0))
                {
                    sheet.GetRow(1).GetCell(3).SetCellValue(list[0].JH);
                    sheet.GetRow(1).GetCell(10).SetCellValue(list[0].FZR);
                    sheet.GetRow(1).GetCell(15).SetCellValue(list[0].XJRQ);
                    sheet.GetRow(2).GetCell(6).SetCellValue(list[0].GCZSQDYL1);
                    sheet.GetRow(3).GetCell(6).SetCellValue(list[0].GCZSQDYL2);
                    sheet.GetRow(4).GetCell(6).SetCellValue(list[0].GCZSQDYL3);
                    sheet.GetRow(5).GetCell(6).SetCellValue(list[0].GY);
                    sheet.GetRow(6).GetCell(6).SetCellValue(list[0].YY);
                    sheet.GetRow(7).GetCell(6).SetCellValue(list[0].PZ);
                    sheet.GetRow(8).GetCell(6).SetCellValue(list[0].SZ);
                    sheet.GetRow(9).GetCell(6).SetCellValue(list[0].XJYYJMB);
                    sheet.GetRow(2).GetCell(13).SetCellValue(list[0].LC);
                    sheet.GetRow(3).GetCell(13).SetCellValue(list[0].XJYB);
                    sheet.GetRow(4).GetCell(13).SetCellValue(list[0].JYGX);
                    sheet.GetRow(5).GetCell(13).SetCellValue(list[0].XQ);
                    sheet.GetRow(6).GetCell(13).SetCellValue(list[0].GCXJ);

                    sheet.GetRow(10).GetCell(5).SetCellValue(list[0].YXJSJ);
                    sheet.GetRow(10).GetCell(10).SetCellValue(list[0].YXJYL);
                    sheet.GetRow(10).GetCell(15).SetCellValue(list[0].YXJPL);

                    sheet.GetRow(11).GetCell(5).SetCellValue(list[0].EXJSJ);
                    sheet.GetRow(11).GetCell(10).SetCellValue(list[0].EXJYL);
                    sheet.GetRow(11).GetCell(15).SetCellValue(list[0].EXJPL);

                    sheet.GetRow(12).GetCell(5).SetCellValue(list[0].SXJSJ);
                    sheet.GetRow(12).GetCell(10).SetCellValue(list[0].SXJYL);
                    sheet.GetRow(12).GetCell(15).SetCellValue(list[0].SXJPL);

                    sheet.GetRow(13).GetCell(4).SetCellValue(list[0].YXJSJ2);
                    sheet.GetRow(13).GetCell(10).SetCellValue(list[0].YXJYL2);
                    sheet.GetRow(13).GetCell(15).SetCellValue(list[0].YJKPL);
                    sheet.GetRow(14).GetCell(15).SetCellValue(list[0].YCKPL);

                    sheet.GetRow(15).GetCell(4).SetCellValue(list[0].EXJSJ2);
                    sheet.GetRow(15).GetCell(10).SetCellValue(list[0].EXJYL2);
                    sheet.GetRow(15).GetCell(15).SetCellValue(list[0].EJKPL);
                    sheet.GetRow(16).GetCell(15).SetCellValue(list[0].ECKPL);

                    sheet.GetRow(17).GetCell(4).SetCellValue(list[0].SXJSJ2);
                    sheet.GetRow(17).GetCell(10).SetCellValue(list[0].SXJYL2);
                    sheet.GetRow(17).GetCell(15).SetCellValue(list[0].SJKPL);
                    sheet.GetRow(18).GetCell(15).SetCellValue(list[0].SCKPL);

                    sheet.GetRow(19).GetCell(4).SetCellValue(list[0].XJCH);
                    sheet.GetRow(20).GetCell(4).SetCellValue(list[0].XJXG);

                    sheet.GetRow(23).GetCell(2).SetCellValue(list[0].XQSYSJ);
                    sheet.GetRow(23).GetCell(5).SetCellValue(list[0].XZSYSJ);
                    sheet.GetRow(23).GetCell(12).SetCellValue(list[0].XHSYSJ);


                    InsertPictureToSheet(sheet, pictureIndex1, 2, 24, 4, 24);
                    InsertPictureToSheet(sheet, pictureIndex2, 5, 24, 11, 24);
                    InsertPictureToSheet(sheet, pictureIndex3, 12, 24, 16, 24);

                    //AddPictureToExecl(list[0].XQSY, PicPath, wk, sheet, 2, 24, 4, 24);
                    //AddPictureToExecl(list[0].XZSY, PicPath, wk, sheet, 5, 24, 11, 24);
                    //AddPictureToExecl(list[0].XHSY, PicPath, wk, sheet, 12, 24, 16, 24);
                    string outputPath = Server.MapPath("../../OutPut/注水井洗井设计报表.xls");
                    using (FileStream files = new FileStream(outputPath, FileMode.Create))
                    {
                        wk.Write(files);
                    }

                    Response.Clear();
                    Response.Buffer = true;
                    Response.Charset = "utf-8";
                    Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("注水井洗井设计报表", System.Text.Encoding.UTF8) + ".xls");
                    Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");     //设置输出流为简体中文
                    Response.ContentType = "application/ms-execl";    //设置输出文件类型为excel文件。 
                    Response.WriteFile(outputPath);
                    Response.End();
                }
            }
        }
    }

View Code

 //2, 24, 4, 24
    private int AddPictureToWorkbook(string picName, string PicPath, Workbook wk)
    {
        bool isExists = true;
        if (!string.IsNullOrEmpty(picName))
        {
            PicPath = PicPath + "/" + picName;
            isExists = Directory.Exists(PicPath);
        }
        else
        {
            isExists = false;
        }
        if (!isExists)
        {
            PicPath = Server.MapPath("../../Images/Button/Defalut .jpg");
        }
        byte[] bytes = System.IO.File.ReadAllBytes(PicPath);
        int pictureIdx = wk.AddPicture(bytes, PictureType.JPEG);
        return pictureIdx;
    }

View Code

private void InsertPictureToSheet(Sheet sheet, int pictureIdx, int StartX, int StartY, int EndX, int EndY)
    {
        Drawing patriarch = sheet.CreateDrawingPatriarch();
        HSSFClientAnchor anchor = new HSSFClientAnchor(255, 125, 1023, 150, StartX, StartY, EndX, EndY);
        Picture pict = patriarch.CreatePicture(anchor, pictureIdx);
        pict.Resize(1);
    }

View Code

 

转载于:https://www.cnblogs.com/gotoschool/p/4103063.html