Export Excel

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;

namespace WebSample.Pages.Test.Export
{
    public partial class test01 : BasePage
    {
        string sql = "select top 100000 * from ILP_LogFile";

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnGet_Click(object sender, EventArgs e)
        {
            DataSet ds = RunSql.ExecuteDataSet(MySettings.DBKey, sql);
            if (ds != null)
            {
                gvShow.DataSource = ds;
                gvShow.DataBind();
            }
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataSet ds = RunSql.ExecuteDataSet(MySettings.DBKey, sql);
            if (ds != null)
            {
                //this.ExportExcel00("test01.xls", ds.Tables[0]);
                this.ExportExcel02("test01.csv", ds.Tables[0]);
                ds.Dispose();
            }
        }

        private void ExportExcel01(string filename, System.Data.DataTable dtExeport)
        {
            StringBuilder content = null ;
            HttpResponse response;
            if (dtExeport == null || dtExeport.Columns == null || dtExeport.Columns.Count == 0)
            {
                return;
            }

            content = new StringBuilder();
            response = Page.Response;
            response.Clear();
            response.Buffer = true;
            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
            response.ContentEncoding = System.Text.Encoding.UTF7; // Encoding.UTF7: Word 2003;  Encoding.UTF8: Word 2010

            content.Append("<html><body><table border=1 cellpadding=0 cellspacing=0><tr>");
            for (int i = 0; i < dtExeport.Columns.Count; i++)
            {
                // content.Append("<td align=\"center\"><b>" + dtExeport.Columns[i].ColumnName.ToString() + "</b></td>"); // 加粗
                content.Append("<td align=\"center\">" + dtExeport.Columns[i].ColumnName.ToString() + "</td>"); // 不加粗
            }
            content.Append("</tr>");

            DataRow[] myRow = dtExeport.Select("");
            foreach (DataRow row in myRow)
            {
                content.Append("<tr>");
                for (int j = 0; j < dtExeport.Columns.Count; j++)
                {
                    content.Append("<td>" + row[j].ToString() + "</td>");
                }
                content.Append("</tr>");
            }
            content.Append("</table></body></html>");
            response.Write(content.ToString());
            System.IO.File.Delete(filename + ".xls");
            response.End();

            content = null;
        }

        protected void ExportExcel02(string filename, System.Data.DataTable dtExeport)
        {
            StringBuilder content = null;
            string tmp = "", sper = ",", br = "\r\n";
            HttpResponse response;
            if (dtExeport == null || dtExeport.Columns == null || dtExeport.Columns.Count == 0)
            {
                return;
            }

            content = new StringBuilder();
            response = Page.Response;
            response.Clear();
            response.Buffer = true;
            response.ContentType = "application/text/plain";
            response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
            response.ContentEncoding = System.Text.Encoding.UTF7; // Encoding.UTF7: Word 2003;  Encoding.UTF8: Word 2010

            for (int i = 0; i < dtExeport.Columns.Count; i++)
            {
                tmp += sper + dtExeport.Columns[i].ColumnName.ToString(); 
            }
            tmp = tmp.Substring(1, tmp.Length - 1);
            content.Append(tmp);

            DataRow[] myRow = dtExeport.Select("");
            foreach (DataRow row in myRow)
            {
                tmp = "";
                for (int j = 0; j < dtExeport.Columns.Count; j++)
                {
                    tmp += sper + row[j].ToString();
                }
                tmp = tmp.Substring(1, tmp.Length - 1) + br;
                content.Append(tmp);
            }
            response.Write(content.ToString());
            System.IO.File.Delete(filename + ".xls");
            response.End();

            content = null;
            tmp = null;
        }


 
    }
}

posted @ 2012-04-12 01:14  tiandong  阅读(211)  评论(0)    收藏  举报