vs aspx页面数据如何用NPOI导出到Excel（包含图片导出）

最新推荐文章于 2021-06-19 07:25:33 发布

xpursue

最新推荐文章于 2021-06-19 07:25:33 发布

阅读量317

点赞数

CC 4.0 BY-SA版权

分类专栏： VS

本文链接：https://blog.csdn.net/xpursue/article/details/107952135

VS 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用NPOI库将数据从DataSet导出到Excel的方法，并详细讲解了如何在Excel中嵌入图片和处理视频链接。通过具体代码示例，展示了如何设置单元格值，添加图片至单元格，以及根据列名调整单元格内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

头引用文件：

using NPOI.HSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

具体方法：

protected void btnExport_Click(object sender, EventArgs e)
    {
        var data = GetData();//DataSet集合
        if (rptSaleorder.Items.Count == 0)
        {
            this.Response.Write("<script>alert('没有可导出的数据或者请先点击查询按钮！');window.opener.location.href=window.opener.location.href;</script>");
            return;
        }
        DataTable dt = data.Tables[0];
        Dictionary<string, string> headerMap = new Dictionary<string, string>();
        headerMap.Add("new_code", "编码");
        headerMap.Add("CreatedOn", "创建时间");
        headerMap.Add("images", "图片");
        headerMap.Add("video", "视频");
        try
        {
            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet("sheet1");

            var row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var x = 0;
                var rowData = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (headerMap.ContainsKey(dt.Columns[j].ColumnName))
                    {
                        row.CreateCell(x).SetCellValue(headerMap[dt.Columns[j].ColumnName]);
                        if (dt.Columns[j].ColumnName == "images")
                        {
                            #region 图片处理
                            string imgPath = dt.Rows[i][j].ToString();
                            if (string.IsNullOrWhiteSpace(imgPath)) continue;
                            rowData.Height = 40 * 20;//50*20
                            //sheet.SetColumnWidth(i+1, 100 * 256);
                            Uri uri = new Uri(imgPath); //imgPath :网络图片地址

                            WebRequest webRequest = WebRequest.Create(uri);

                            using (WebResponse webResponse = webRequest.GetResponse())
                            {
                                Bitmap bitmap = new Bitmap(webResponse.GetResponseStream());

                                using (MemoryStream ms = new MemoryStream())

                                {

                                    bitmap.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

                                    byte[] bytes = ms.ToArray();

                                    int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG);  //添加图片

                                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();

                                    //HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 100, 240, x, i + 1, x + 1, i + 2);
                                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 800, 255, x, i + 1, x, i + 1);

                                    //图片位置，图片左上角为（col, row）

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

                                    //pict.Resize(); //用图片原始大小来显示
                                    //pict.Resize(0.1);
                                }

                            }
                            #endregion

                        }
                        else
                        {
                            rowData.CreateCell(x).SetCellValue(dt.Rows[i][j].ToString());
                        }
                        if(dt.Columns[j].ColumnName == "video")
                        {
                            if (!string.IsNullOrWhiteSpace(dt.Rows[i][j].ToString()))
                            {
                                rowData.CreateCell(x).SetCellValue("下载链接");
                            }
                        }
                        x++;

                    }

                }
            }

            string filename = "Excel文件名称" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
            //转为字节数组  
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();

            //流方式下载文件
            HttpContext.Current.Response.ContentType = "application/octet-stream";
            //通知浏览器下载文件而不是打开
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8));
            HttpContext.Current.Response.BinaryWrite(buf);
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
            return;

        }
        catch (Exception ex)
        {
            this.Response.Write("<script>alert('" + ex.Message + "');window.opener.location.href=window.opener.location.href;</script>");
            return;
        }


    }