NPOI 导出excel带图片，可控大小

https://www.cnblogs.com/soundcode/p/4067006.html

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

region 导出

    protected void btnexcel_Click(object sender, EventArgs e)
    {
        QQT_BLL.gg_content bll = new QQT_BLL.gg_content();
        DataSet ds = bll.getds("");
        DataTable dt = ds.Tables[0];
        string excelname = System.DateTime.Now.ToString().Replace(":", "").Replace("-", "").Replace(" ", "");
        string filePath = System.Web.HttpContext.Current.Server.MapPath("ReadExcel") + "\\" + excelname + ".xls";
        MemoryStream ms = RenderDataTableToExcel(dt) as MemoryStream;
        FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
        byte[] data = ms.ToArray();
        fs.Write(data, 0, data.Length);
        fs.Flush();
        fs.Close();
        data = null;
        ms = null;
        fs = null;

        #region 导出到客户端
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(excelname, System.Text.Encoding.UTF8) + ".xls");
        Response.ContentType = "Application/excel";
        Response.WriteFile(filePath);
        Response.End();
        #endregion
    }
    public Stream RenderDataTableToExcel(DataTable SourceTable)
    {
     
        MemoryStream ms = new MemoryStream();
        NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
        NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
        NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);

      //设置7列宽为100
        sheet.SetColumnWidth(7, 100);
     //加标题

        headerRow.CreateCell(0).SetCellValue("广告编号");
        headerRow.CreateCell(1).SetCellValue("广告标题");
        headerRow.CreateCell(2).SetCellValue("广告内容");
        headerRow.CreateCell(3).SetCellValue("所属广告商");
        headerRow.CreateCell(4).SetCellValue("广告电话");
        headerRow.CreateCell(5).SetCellValue("广告网址");
        headerRow.CreateCell(6).SetCellValue("广告小图片");
        headerRow.CreateCell(7).SetCellValue("图片显示");
        headerRow.CreateCell(8).SetCellValue("广告大图片");
        headerRow.CreateCell(9).SetCellValue("图片显示");
        headerRow.CreateCell(10).SetCellValue("审核状态");

        int rowIndex = 1;
        foreach (DataRow row in SourceTable.Rows)
        {
            NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
            dataRow.Height = 50;

           //列高50
            
            dataRow.CreateCell(0).SetCellValue(row["gg_id"].ToString());
            dataRow.CreateCell(1).SetCellValue(row["gg_title"].ToString());
            dataRow.CreateCell(2).SetCellValue(row["gg_memo"].ToString());
            dataRow.CreateCell(3).SetCellValue(row["ggs_name"].ToString());
            dataRow.CreateCell(4).SetCellValue(row["gg_tel"].ToString());
            dataRow.CreateCell(5).SetCellValue(row["gg_add"].ToString());
            dataRow.CreateCell(6).SetCellValue(row["p_name"].ToString());
            string picurl = row["p_url"].ToString();  //图片存储路径             
            dataRow.CreateCell(8).SetCellValue(row["gg_check"].ToString());
            AddPieChart(sheet,  workbook, picurl,rowIndex ,7);
            rowIndex++;
        }
        workbook.Write(ms);
        ms.Flush();
        ms.Position = 0;
        sheet = null;
        headerRow = null;
        workbook = null;
        return ms;
    }
  
    ///
    /// 向sheet插入图片
    ///
    ///
    ///
    private void AddPieChart(ISheet sheet,   HSSFWorkbook  workbook, string fileurl,int row,int col)
    {
        try
        {
            //add picture data to this workbook.
            string path = Server.MapPath("~/html/");
            if (fileurl.Contains("/"))
            {
                path += fileurl.Substring( fileurl.IndexOf ('/'));
            }
            string FileName = path;
            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, 100, 50, 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)
        {
            throw ex;
        }
    }
    #endregion

分类: ASP.NET