NPOI 导出数据到Excel(包括图片)

当前位置 : 首页 > 网页制作 > CSS > NPOI 导出数据到Excel(包括图片)

NPOI 导出数据到Excel(包括图片)

来源: 作者: 时间:2016-01-29 09:12
RT,对于asp.net导出页面数据到Excel,图片一直是个问题,这里我把我的处理方法分享给大家,用第三方组件NPOI来实现,同时玩玩二维码生成。首先,新建一个webApplication,添加NPOI引用,

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

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

 

 

 

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

前台页面html源码如下:


[html]
<%@ 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> 

<%@ 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]
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;  
                /

Tag:
网友评论

<