ASP.NET个性化生成excel文件

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

网上共享的ASP.NET中将数据生成excel报表的代码，往往都是不能根据需求自己定制表头，而是直接从数据源读取的数据表中的表字段名。这里我共享一种生成excel方法，并且根据实际需要随意更改标题。

using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
using System.Text;

/// <summary>
/// ToExcel 的摘要说明
/// </summary>
public class ToExcel
{
public ToExcel()
{
  //
  // TODO: 在此处添加构造函数逻辑
  //
}
        //listname 和 cols 个数　要相等，顺序一一对应
    public static void tableToExcel(DataTable tb, string[] listname, string[] cols)
    {
        if ((tb == null) || (tb.Rows.Count == 0))
        {
            return;
        }

        FileStream file;
        StreamWriter filewrite;
        Random r = new Random();
        string t = r.NextDouble().ToString().Remove(0, 2);

        string filename = GetRandom() + ".xls";
        string path = System.Web.HttpContext.Current.Server.MapPath(".") + @"\tempdata\" + filename;
        int i, j;
        file = new FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write);
        filewrite = new StreamWriter(file, System.Text.Encoding.Unicode);
        StringBuilder strline = new StringBuilder();
        for (i = 1; i <= listname.Length; i++)
        {
            strline.Append(listname[i - 1]).Append(Convert.ToChar(9));
        }

filewrite.WriteLine(strline.ToString());

//表内容

        for (i = 1; i <= tb.Rows.Count; i++)
        {
            strline.Remove(0,strline.Length);//清空全部内容
            //strline = "";

for (j = 1; j <= cols.Length; j++)
{

                if (j == 1)
                {
                    strline.Append(tb.Rows[i-1][cols[j-1]]).Append(Convert.ToChar(9));
                }
                else
                {
                    strline.Append(tb.Rows[i - 1][cols[j - 1]]).Append(Convert.ToChar(9));
                }
            }
            filewrite.WriteLine(strline);
        }

filewrite.Close();
file.Close();

        if (File.Exists(path))
        {
            HttpContext.Current.Response.Clear();
            bool success = ResponseFile(HttpContext.Current.Request, HttpContext.Current.Response, filename, path, 1024000);
            if (!success)
                HttpContext.Current.Response.Write("下载文件出错！");
            HttpContext.Current.Response.End();
        }
        else
        {
            HttpContext.Current.Response.Write("文件不存在！");
        }

        string l_strHtml = "<script language='JavaScript'>";
        l_strHtml += " window.open('../tempdata/excel.xls','newwindow','height=800,width=1024,scrollbars=yes,resizable=yes,location=yes, status=yes,menubar=yes,toolbar=yes,titlebar=yes')";
        l_strHtml += "</script>";
        HttpContext.Current.Response.Write(l_strHtml);
    }

    public static bool ResponseFile(HttpRequest _Request, HttpResponse _Response, string _fileName, string _fullPath, long _speed)
        {
            try
            {
                FileStream myFile = new FileStream(_fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                BinaryReader br = new BinaryReader(myFile);
                try
                {
                    _Response.AddHeader("Accept-Ranges", "bytes");
                    _Response.Buffer = false;
                    long fileLength = myFile.Length;
                    long startBytes = 0;

                    int pack = 10240; //10K bytes
                    //int sleep = 200;   //每秒5次   即5*10K bytes每秒
                    int sleep = (int)System.Math.Floor(1000 * pack*1.0 / _speed) + 1;
                    if (_Request.Headers["Range"] != null)
                    {
                        _Response.StatusCode = 206;
                        string[] range = _Request.Headers["Range"].Split(new char[] { '=', '-' });
                        startBytes = Convert.ToInt64(range[1]);
                    }
                    _Response.AddHeader("Content-Length", (fileLength - startBytes).ToString());
                    if (startBytes != 0)
                    {
                        _Response.AddHeader("Content-Range", string.Format(" bytes {0}-{1}/{2}", startBytes, fileLength - 1, fileLength));
                    }

string filename = System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(_fileName)).Replace("+", "%20");

                    _Response.AddHeader("Connection", "Keep-Alive");
                    _Response.ContentType = "application/octet-stream";
                    _Response.AddHeader("Content-Disposition", "attachment;filename=" + filename); //HttpUtility.UrlEncode(_fileName));//HttpContext.Current.Server.UrlEncode(_fileName));//HttpUtility.UrlEncode(_fileName,System.Text.Encoding.Default));

br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
int maxCount = (int)System.Math.Floor((fileLength - startBytes) *1.0/ pack) + 1;

                    for (int i = 0; i < maxCount; i++)
                    {
                        if (_Response.IsClientConnected)
                        {
                            _Response.BinaryWrite(br.ReadBytes(pack));
                            System.Threading.Thread.Sleep(sleep);
                        }
                        else
                        {
                            i = maxCount;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.ToString());
                    return false;
                }
                finally
                {
                    br.Close();
                    myFile.Close();
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        #region 用当前时间生成随机文件名
        public static string GetRandom()//根据当前连接的用户的访问时间来生成excle文件，精确到秒，这样在tempdata文件夹里不会出现重复的excel文件，保证每次生成excel文件成功
        {
            string random = "";
            DateTime date = DateTime.Now;
            random = date.ToString().Replace("-", "").Replace(":", "").Replace(" ", "").Replace(" ", "");
            return random;
        }
        #endregion

}

OK，类库编写完毕，我们在每个aspx页面直接调用这个类库就行，需要输入数据集，标题和数据库表中的字段名称两个数组。代码如下:

//PubSql为sql查询语句

if (PubSql != "")
            {
                SqlDataAdapter sda=new SqlDataAdapter(PubSql,MyConnection);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    string[] listname = { "标题一", "标题二", "标题三};
                    string[] cols = { "columns1", "columns2", "columns3"};
                    ToExcel.tableToExcel(ds.Tables[0], listname, cols);
                }

}

第一次在博客园写blog好多格式还没熟悉，copy过来的代码没有了vs里面的格式风格了，多包含。