asp.net生成Excel并导出下载的实现方法

最新推荐文章于 2018-12-17 22:33:27 发布

qianquan3

最新推荐文章于 2018-12-17 22:33:27 发布

阅读量4.5k

点赞数 2

分类专栏： asp.net sql server 文章标签： asp.net生成Excel并导出下载的

本文链接：https://blog.csdn.net/qianquan003/article/details/16847083

版权

sql server 同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

asp.net

9 篇文章 0 订阅

订阅专栏

outputExcel.aspx页面：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="outputExcel.aspx.cs" Inherits="study_outputExcel" %>

<!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:Button ID="hidExport" Runat="server" οnclick="hidExport_Click1" Text="导出"></asp:Button>

    </div>
    </form>
</body>
</html>

outputExcel.aspx.cs页面：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;

public partial class study_outputExcel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    //内容很好理解，只需当成Table来拼字符串即可 
private string getExcelContent() 
{
    clsDbAccept da = new clsDbAccept();
    DataTable dt = da.GetDataSet("select * from userInfo").Tables[0];
    StringBuilder sb = new StringBuilder();
    sb.Append("<table  border='1' >");
    sb.Append("<tr class='firstTR'><td>序号</td><td>用户名</td><td>密码</td><td>邮箱</td><td>性别</td><td>注册时间（varchar）</td><td>注册时间</td><td>备用</td></tr>");
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        sb.Append("<tr class='secondTR'><td bgColor='#ccfefe'>" + (i + 1) + "</td>");
        sb.Append("<td bgcolor='#FF99CC'>" + dt.Rows[i]["userName"] + "</td>");
        sb.Append("<td bgcolor='lightskyblue'>" + dt.Rows[i]["password"] + "</td>");
        sb.Append("<td >" + dt.Rows[i]["email"] + "</td>");
        sb.Append("<td >" + dt.Rows[i]["gender"] + "</td>");
        sb.Append("<td >" + dt.Rows[i]["regTime"] + "</td>");
        sb.Append("<td >" + dt.Rows[i]["testTime"] + "</td>");
        sb.Append("<td >" + dt.Rows[i]["remark"] + "</td></tr>");
    } 
    
    sb.Append("</table>"); 
    return sb.ToString(); 
    }

    protected void hidExport_Click1(object sender, EventArgs e)
    {
        string content = getExcelContent();
        string css = ".firstTR td{color:blue;widtd:100px;}.secondTR td{color:blue;widtd:100px;}";
        string filename = "Test.xls";

        CommonTool.ExportToExcel(filename, content, css);
    }

    public class CommonTool 
    { 
    /// <summary> 
    /// 以流的形式，可以设置很丰富复杂的样式 
    /// </summary> 
    /// <param name="content">Excel中内容(Table格式)</param> 
    /// <param name="filename">文件名</param> 
    /// <param name="cssText">样式内容</param> 
    public static void ExportToExcel(string filename, string content,string cssText) 
    { 
    var res = HttpContext.Current.Response; 
    content = String.Format("<style type='text/css'>{0}</style>{1}",cssText,content); 

    res.Clear(); 
    res.Buffer = true; 
    res.Charset = "GB2312"; 
    res.AddHeader("Content-Disposition", "attachment; filename=" + filename);
    res.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
    res.ContentType = "application/ms-excel;charset=GB2312"; 
    res.Write(content); 
    res.Flush(); 
    res.End(); 
    } 
    }


}

UserInfo表：

效果预览：