NPOI 导出数据到Excel（包括图片）

来源：互联网发布：mac rvm装ruby 失败编辑：程序博客网时间：2024/05/21 10:55

RT，对于asp.net导出页面数据到Excel，图片一直是个问题，这里我把我的处理方法分享给大家，用第三方组件NPOI来实现，同时玩玩二维码生成。

首先，新建一个webApplication，添加NPOI引用，这里还要添加一个二维码的dll

下载地址：点击打开链接

添加引用之后，新建一个aspx页面，我取名为QrEncoderPage.aspx

前台页面html源码如下：

[html] view plaincopy
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="QrEncoderPage.aspx.cs"  
    Inherits="WebQrCodeNet.QrEncoderPage" %>  
  
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
<html xmlns="http://www.w3.org/1999/xhtml">  
<head runat="server">  
    <title></title>  
</head>  
<body>  
    <form id="form1" runat="server">  
    <div>  
        <asp:GridView ID="gvCardList" runat="server" Width="100%" AutoGenerateColumns="False"  
            RowStyle-CssClass="DataAlign" OnRowCommand="gvCardList_RowCommand" Style="margin-top: 15px">  
            <Columns>  
                <asp:BoundField DataField="CardCode" HeaderText="卡号" SortExpression="CardCode" ReadOnly="True"  
                    ControlStyle-CssClass="text">  
                    <ControlStyle CssClass="text"></ControlStyle>  
                    <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" />  
                </asp:BoundField>  
                <asp:BoundField DataField="Password" HeaderText="密码" SortExpression="Password" ReadOnly="True"  
                    ControlStyle-CssClass="text">  
                    <ControlStyle CssClass="text"></ControlStyle>  
                    <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" />  
                </asp:BoundField>  
                <asp:TemplateField ShowHeader="False">  
                    <ItemTemplate>  
                        <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandArgument='<%# Eval("CardCode") %>'  
                            Text="选择"></asp:LinkButton>  
                    </ItemTemplate>  
                </asp:TemplateField>  
            </Columns>  
            <RowStyle CssClass="GridViewRowStyle" />  
            <SelectedRowStyle CssClass="GridViewSelectedRowStyle" />  
            <PagerTemplate>  
            </PagerTemplate>  
            <AlternatingRowStyle CssClass="GridViewAlternatingRowStyle" />  
            <HeaderStyle CssClass="GridViewHeaderStyle" />  
        </asp:GridView>  
    </div>  
    <div>  
        <%-- 输入QR code内容：  
        <asp:TextBox ID="txtContent" runat="server"></asp:TextBox>--%>  
        <asp:Button ID="btnGenerateCode" runat="server" Text="生成二维码" OnClick="btnGenerateCode_Click" />  
    </div>  
    </form>  
</body>  
</html>  

接着，解释后台代码，很简单，我就不解释了，大家应该看的懂，

[csharp] view plaincopy
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Web;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
using Gma.QrCodeNet.Encoding;  
using System.IO;  
using System.Drawing.Imaging;  
using System.Drawing;  
using NPOI;  
using NPOI.HSSF.UserModel;  
using Gma.QrCodeNet.Encoding.Windows.Render;  
using NPOI.SS.UserModel;  
using System.Text;  
using NPOI.SS.Util;  
using System.Collections;  
using System.Text.RegularExpressions;  
using NPOI.HPSF;  
  
namespace WebQrCodeNet  
{  
    public partial class QrEncoderPage : System.Web.UI.Page  
    {  
        private static readonly string imageBasePath = "D:\\PathConfig\\";  
        private static readonly string excelBasePath = "D:\\PathConfig\\";  
        protected void Page_Load(object sender, EventArgs e)  
        {  
            if (!IsPostBack)  
            {  
                Bind();  
            }  
        }  
  
        protected void Bind()  
        {  
            //string jsonobjstr = HttpHelper.QueryString("json", "");  
            List<PrintCard> cards = new List<PrintCard>()   
            {  
                new PrintCard{ CardCode="0230010900010336",Password= "123456"},  
                new PrintCard{ CardCode="0230010900010339",Password="123456"},  
                new PrintCard{ CardCode="0230010900010340",Password= "123456"},  
                new PrintCard{ CardCode="0230000900010340",Password= "123456"},  
                new PrintCard{ CardCode="0230020900010349",Password="123456"}  
            };  
            try  
            {  
                // cards = JsonHelper.JsonDeserialize<List<Card>>(jsonobjstr);  
                Session["ListPrintCard"] = cards;  
  
                gvCardList.DataSource = cards;  
                gvCardList.DataBind();  
            }  
            catch (Exception ex)  
            {  
                Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('" + ex.Message.ToString() + "');</script>");  
                return;  
            }  
        }  
        public class PrintCard  
        {  
            public string CardCode { get; set; }  
            public string Password { get; set; }  
        }  
        /// <summary>  
        /// 生成二维码  
        /// </summary>  
        /// <param name="QrCodeStr">二维码字符串</param>  
        /// <returns></returns>  
        public string GetQrCode(string QrCodeStr)  
        {  
            string FileName = imageBasePath + Guid.NewGuid().ToString() + ".png";  
            try  
            {  
                QrEncoder qrEncoder = new QrEncoder(ErrorCorrectionLevel.L);  
                QrCode qrCode = new QrCode();  
                qrEncoder.TryEncode(QrCodeStr, out qrCode);  
                GraphicsRenderer renderer = new GraphicsRenderer(new FixedModuleSize(5, QuietZoneModules.Two), Brushes.Black, Brushes.White);  
                using (FileStream stream = new FileStream(FileName, FileMode.Create))  
                {  
                    renderer.WriteToStream(qrCode.Matrix, ImageFormat.Png, stream);  
                }  
            }  
            catch (Exception ex)  
            {  
                FileName = "";  
                throw ex;  
            }  
            return FileName;  
        }  
  
  
        protected void btnGenerateCode_Click(object sender, EventArgs e)  
        {  
            //GenerateQrCodeNet();  
            AddPicture();  
        }  
        /// <summary>  
        /// 二维码导出到Excel  
        /// </summary>  
        protected void AddPicture()  
        {  
            try  
            {  
                if (!Directory.Exists(imageBasePath))  
                {  
                    Directory.CreateDirectory(imageBasePath);  
                }  
                if (!Directory.Exists(excelBasePath))  
                {  
                    Directory.CreateDirectory(excelBasePath);  
                }  
                //创建工作薄  
                HSSFWorkbook workbook = new HSSFWorkbook();  
  
                //create sheet  
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");  
                string FileName = excelBasePath + DateTime.Now.ToString("yyyyMMddhh24mss") + ".xls";  
 
                #region 右击文件 属性信息  
                //{  
                //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();  
                //    dsi.Company = "http://....../";  
                //    workbook.DocumentSummaryInformation = dsi;  
  
                //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();  
                //    if (HttpContext.Current.Session["realname"] != null)  
                //    {  
                //        si.Author = HttpContext.Current.Session["realname"].ToString();  
                //    }  
                //    else  
                //    {  
                //        if (HttpContext.Current.Session["username"] != null)  
                //        {  
                //            si.Author = HttpContext.Current.Session["username"].ToString();  
                //        }  
                //    }                                       //填加xls文件作者信息       
                //    si.ApplicationName = "NPOI";            //填加xls文件创建程序信息       
                //    si.LastAuthor = "OA系统";           //填加xls文件最后保存者信息       
                //    si.Comments = "OA系统自动创建文件";      //填加xls文件作者信息       
                //    si.Title = "ddd";               //填加xls文件标题信息       
                //    si.Subject = "ddd";              //填加文件主题信息       
                //    si.CreateDateTime = DateTime.Now;  
                //    workbook.SummaryInformation = si;  
                //}  
                #endregion  
  
  
                string strQrCodePath = "";  
                //填充列标题以及样式  
                int rowsNum = 0;  //行号  
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowsNum);  
                HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();  
                headStyle.Alignment = (HorizontalAlignment)HorizontalAlignment.CENTER;  
                headerRow.HeightInPoints = 30;  
  
                HSSFFont font = (HSSFFont)workbook.CreateFont();  
                font.FontHeightInPoints = 13;  
                font.Boldweight = 700;  
                headStyle.SetFont(font);  
  
                headerRow.CreateCell(0, CellType.STRING).SetCellValue("卡号");  
                headerRow.CreateCell(1, CellType.STRING).SetCellValue("密码");  
                headerRow.CreateCell(2, CellType.STRING).SetCellValue("卡二维码");  
                //合并列头单元格  
                //CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 2, 4);  
                //sheet.AddMergedRegion(cellRangeAddress);  
  
                //设置列的宽度  
                for (int columnindex = 0; columnindex < 3; columnindex++)  
                {  
                    headerRow.GetCell(columnindex).CellStyle = headStyle;  
                    sheet.SetColumnWidth(columnindex, 5000);  
                }  
  
                //填充数据行  
                HSSFRow row = null;  
                rowsNum = 1;  //行号，从第2行开始  
                List<PrintCard> list = (List<PrintCard>)Session["ListPrintCard"];  
                foreach (PrintCard p in list)  
                {  
                    //写入字段值  
                    row = (HSSFRow)sheet.CreateRow(rowsNum);  
                    HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();  
                    cellStyle.Alignment = (HorizontalAlignment)HorizontalAlignment.CENTER;  
                    row.HeightInPoints = 120;  
                    HSSFFont cellfont = (HSSFFont)workbook.CreateFont();  
                    cellfont.FontHeightInPoints = 10;  
                    cellStyle.SetFont(cellfont);  
  
                    row.CreateCell(0, CellType.STRING).SetCellValue(p.CardCode);  
                    row.CreateCell(1, CellType.STRING).SetCellValue(p.Password);  
                    row.CreateCell(2, CellType.BLANK).SetCellValue("");  
  
                    //合并单元格  
                    //CellRangeAddress rowCellRangeAddress = new CellRangeAddress(rowsNum, rowsNum, 2, 4);  
                    //sheet.AddMergedRegion(rowCellRangeAddress);  
  
                    strQrCodePath = GetQrCode(p.CardCode);  
                    byte[] bytes = System.IO.File.ReadAllBytes(strQrCodePath);  
                    int pictureIdx = workbook.AddPicture(bytes, PictureType.PNG);  
  
                    // Create the drawing patriarch.  This is the top level container for all shapes.   
                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();  
  
                    //add a picture  
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 10, 1023, 0, 2, rowsNum, 2, rowsNum);  
  
                    HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);  
                    pict.Resize();  
  
                    //设置行的高度  
                    for (int rowindex = 0; rowindex < 3; rowindex++)  
                    {  
                        row.GetCell(rowindex).CellStyle = cellStyle;  
                    }  
                    rowsNum++;  
                    //删除图片文件  
                    if (File.Exists(strQrCodePath))  
                    {  
                        File.Delete(strQrCodePath);  
                    }  
                }  
                //供浏览器下载Excel  
                if (HttpContext.Current.Request.Browser.Browser == "IE")  
                    FileName = HttpUtility.UrlEncode(FileName);  
                using (MemoryStream ms = new MemoryStream())  
                {  
                    workbook.Write(ms);  
                    ms.Flush();  
                    ms.Position = 0;  
                    HttpContext curContext = HttpContext.Current;  
  
                    // 设置编码和附件格式  
                    curContext.Response.ContentType = "application/vnd.ms-excel";  
                    curContext.Response.ContentEncoding = Encoding.UTF8;  
                    curContext.Response.Charset = "";  
                    curContext.Response.AppendHeader("Content-Disposition",  
                         "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8));  
                    curContext.Response.BinaryWrite(ms.GetBuffer());  
                    ms.Close();  
                    ms.Dispose();  
                    curContext.Response.End();  
                }  
            }  
            catch (Exception ex)  
            {  
                Page.ClientScript.RegisterStartupScript(this.GetType(), "key", "<script>alert('" + ex.Message.ToString() + "');</script>");  
            }  
  
  
  
        }  
  
        protected void gvCardList_RowCommand(object sender, GridViewCommandEventArgs e)  
        {  
            string cardCode = e.CommandArgument.ToString();  
  
        }  
    }  
}  

0 0