100分请教：.NET导出数据为Excell文件

Question

导出数据为Excell文件的程序代码我有了，但问题是我现在要一个 datalist嵌套datalist 的数据导出数据为Excell文件，还真有点无知呢？请高手指点！谢谢！
protected void Page_Load(object sender, EventArgs e)
    {        string strConn = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString;
        DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
        DbConnection     dbConn = dbProviderFactory.CreateConnection();
        dbConn.ConnectionString = strConn;
        dbConn.Open();
        DbCommand dbComm = dbProviderFactory.CreateCommand();
        dbComm.Connection = dbConn;
       
        DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
        adapter.SelectCommand = dbComm;
        DataSet ds = new DataSet();        string sql2 = "select  * from sc_order where fk=1 ";
        SqlDataAdapter sda1 = new SqlDataAdapter(sql2, strConn);        sda1.Fill(ds, "big");
        
        string sql3 = "select  * from sc_orderlist ";
        SqlDataAdapter sda2 = new SqlDataAdapter(sql3, strConn);
        sda2.Fill(ds, "small");        ds.Relations.Add("myrelation", ds.Tables["big"].Columns["id"], ds.Tables["small"].Columns["orderid"], false);
        dlCategories.DataSource = ds.Tables["big"].DefaultView;
        dlCategories.DataBind();
   
    }    protected void Button1_Click(object sender, EventArgs e)
    {
        OutputData.SaveAsExcel(Page,ds);
    }

Answer 1

OutputData.SaveAsExcel
.cs的代码是：
 public OutputData() { }        public static void SaveAsExcel(Page page, DataSet ds)
        {
            if (page == null || ds == null || ds.Tables.Count <= 0) return;            StringWriter sw = new StringWriter();            ///输出表格的标题
            StringBuilder column = new StringBuilder();
            foreach (DataColumn c in ds.Tables[0].Columns)
            {
                column.Append(c.ColumnName.ToLower());
                if (c.Ordinal < ds.Tables[0].Columns.Count - 1)
                {
                    column.Append(",");
                }
            }
            sw.WriteLine(column.ToString());            ///输出每一行数据
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                column = new StringBuilder();
                foreach (DataColumn c in ds.Tables[0].Columns)
                {
                    column.Append(r[c.ColumnName].ToString());
                    if (c.Ordinal < ds.Tables[0].Columns.Count - 1)
                    {
                        column.Append(",");
                    }
                }
                sw.WriteLine(column.ToString());
            }
            ///设置输出格式
            page.Response.AddHeader("Content-Disposition", "attachment;filename=data.csv");
            ///保存数据的格式为Excel
            page.Response.ContentType = "application/ms-excel";
            ///设置数据编码
            page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            ///输出数据
            page.Response.Write(sw);
            ///关闭流，并停止网页
            sw.Close();
            page.Response.End();
        }
    

Answer 2

这个东西需要怎么写才能导出呢？
 protected void Button1_Click(object sender, EventArgs e) 
    { 
        OutputData.SaveAsExcel(Page,ds); 
    }谢谢@！

Answer 3

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
namespace ConsoleApplication18
{
    class Program
    {
        static void Main(string[] args)
        {
            ExportDataToExcel("server=(local);uid=sa;pwd=sqlgis;database=master",
                "select * from sysobjects",@"c:\testADO.xls","sysobjects");
        }
         
        static void ExportDataToExcel(string connectionString,string sql,string fileName,string sheetName)
        {
            Excel.Application app = new Excel.ApplicationClass();
            Excel.Workbook wb = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
            Excel.Worksheet ws = wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
            ws.Name = sheetName;
            try
            {
                ADODB.Connection conn = new ADODB.ConnectionClass();
                conn.Open("driver={SQL Server};"+connectionString,"","",0);
                ADODB.Recordset rs = new ADODB.RecordsetClass();
                rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
                Excel.Range range = ws.get_Range("A2", Missing.Value);
                range.CopyFromRecordset(rs, 65535, 65535);
            }
            catch (Exception ex)
            {
                string str = ex.Message;
            }
            finally
            {
                wb.Saved = true;
                wb.SaveCopyAs(fileName);//保存
                app.Quit();//关闭进程
            }
        }
    }
}我也不怎么会弄，网上看到的一个。

Answer 4

有没有直接把网页控件上显示的数据导出成Excel呢，我觉得有时候不是一定要从dataset中导出数据啊
控件上的数据格式啊表头啊什么的都设计好了的，应该可以直接导出就好了吧？

Answer 5

 public void CreateExcel(DataSet ds, string FileName)
    {
        HttpResponse resp;
        resp = Page.Response;
        Response.Charset = "";
        Response.Buffer = true;
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");        resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlPathEncode(FileName));        string colHeaders = "", ls_item = "";
        int i = 0;        DataTable dt = ds.Tables[0];
        DataRow[] myRow = dt.Select("");        for (i = 0; i < dt.Columns.Count - 1; i++)
            colHeaders += dt.Columns[i].Caption.ToString() + "\t";
        colHeaders += dt.Columns[i].Caption.ToString() + "\n";        resp.Write(colHeaders);        foreach (DataRow row in myRow)
        {            for (i = 0; i < dt.Columns.Count - 1; i++)
                ls_item += row[i].ToString() + "\t";
            ls_item += row[i].ToString() + "\n";            resp.Write(ls_item);
            ls_item = "";
        }        resp.End();    }

Answer 6

protected void ButSave_Click(object sender, EventArgs e)
    {
        sendTableName = "Phone, Illness, See_Illness,Leechdom,Notes";
        sendStrSQL = this.Label8.Text;
        dataBase();
        DataView dv = new DataView(ds.Tables[0]);
        OutputExcel(dv,"我到处的excel报表");
    }
    public void OutputExcel(DataView dv, string str)
    {
        // 
        // TODO: 在此处添加构造函数逻辑 
        // 
        //dv为要输出到Excel的数据，str为标题名称 
        GC.Collect();
        //Application excel;// = new Application(); 
        int rowIndex = 2;
        int colIndex = 0;
        int SUM = 0;
        int number = 0; 
        _Workbook xBk;
        _Worksheet xSt;        Excel.ApplicationClass excel = new Excel.ApplicationClass();
        xBk = excel.Workbooks.Add(true);        xSt = (_Worksheet)xBk.ActiveSheet;        // 
        //取得标题 
        // 
        foreach (DataColumn col in dv.Table.Columns)
        {
            colIndex++;
            excel.Cells[2, colIndex] = col.ColumnName;
            xSt.get_Range(excel.Cells[2, colIndex], excel.Cells[2, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐 
        }        // 
        //取得表格中的数据 
        // 
        foreach (DataRowView row in dv)
        {
            rowIndex++;
            colIndex = 0;
            foreach (DataColumn col in dv.Table.Columns)
            {
                colIndex++;
                //if (col.DataType == System.Type.GetType("System.DateTime"))
                //{
                //    excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                //    xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐 
                //}
                //else
                    if (col.DataType == System.Type.GetType("System.String"))
                    {
                       
                        excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                          
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐 
                    }
                    else
                    {
                        excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        if (col.ColumnName.ToString() == "用药数量")
                        {
                            number = colIndex;
                        }
                    }
            }
        }
        // 
        //加载一个合计行 
        // 
        int rowSum = rowIndex + 1;
        int colSum = 1;
        if (number != 0)
        {
            excel.Cells[rowSum, number] = this.lblnumber.Text;
        }
        excel.Cells[rowSum, 1] = "合计";
        xSt.get_Range(excel.Cells[rowSum, 1], excel.Cells[rowSum, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
      
        //if (row[col.ColumnName].ToString() = "用药数量")
        //{        //}
        // 
        //设置选中的部分的颜色 
        // 
        xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
        xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种 
        // 
        //取得整个报表的标题 
        // 
        excel.Cells[1, 1] = str;
        // 
        //设置整个报表的标题格式 
        // 
        //xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Bold = true;
        xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 16;
        // 
        //设置报表表格为最适应宽度 
        // 
        xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Select();
        xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
        // 
        //设置整个报表的标题为跨列居中 
        // 
        xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Select();
        xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
        // 
        //绘制边框 
        // 
        xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
        xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, 1]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗 
        xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗 
        xSt.get_Range(excel.Cells[2, colIndex], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗 
        xSt.get_Range(excel.Cells[rowSum, 1], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗 
        // 
        //显示效果 
        // 
        excel.Visible = true;        xBk.SaveCopyAs(Server.MapPath(".") + "\\" + "2008.xls");        ds = null;
        xBk.Close(false, null, null);        excel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
        xBk = null;
        excel = null;
        xSt = null;
        GC.Collect();
        string path = Server.MapPath("2008.xls");        System.IO.FileInfo file = new System.IO.FileInfo(path);
        Response.Clear();
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
        // 添加头信息，指定文件大小，让浏览器能够显示下载进度 
        Response.AddHeader("Content-Length", file.Length.ToString());        // 指定返回的是一个不能被客户端读取的流，必须被下载 
        Response.ContentType = "application/ms-excel";
      
        // 把文件流发送到客户端 
        Response.WriteFile(file.FullName);
        // 停止页面的执行 
        
        Response.End();
      
    }

Answer 7

    private void ExportToExcel( )
    {
        Response.Clear( );
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
        Page.Response.AppendHeader("content-disposition" , "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode("用户投诉对策" , System.Text.Encoding.UTF8) + ".xls\"");
        StringWriter sw = new StringWriter( );
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        SearchMeasure( );
        Repeater1.RenderControl(htw);
        Response.Write(sw.ToString( ));
        Response.End( );
    }
试试看这样，使用repeater嵌套的，repeater里写的都是table的html代码，你把repeater改成datalist试试

Answer 8

http://www.watch-life.net/programming-notes/html-table-2-excel.html

Answer 9

导出的不是真的Excel文件，不能重新导入到数据库。

Answer 10

 protected void Button2_Click(object sender, EventArgs e)
    {
        ToExcel(GridView1, "sike.xls");
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        // Confirms that an HtmlForm control is rendered for
    }
    private void ToExcel(Control ctl, string FileName)
    {
        Response.Clear();
        Response.Buffer = false;
        Response.Charset = "GB2312";
        Response.AppendHeader("Content-Disposition", "attachment;filename=pkmv_de.xls");
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.ContentType = "application/ms-excel";
        Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=GB2312\">");
        this.EnableViewState = false;
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
        GridView1.RenderControl(oHtmlTextWriter);
        Response.Write(oStringWriter.ToString());
        Response.End();    }
保证可以用，因为我正在做东西！今天，嘿嘿

Answer 11

把你的输出结果HTML放在一个DIV里面，导出的时候将DIV的值取出存为EXCEL文件输出就OK了嘛，只是格式可能有点老火

Answer 12

如果是表格的话，应该是可以的，
Repeater1.RenderControl(htw);
红色的是控件ID
你用DataList的话，就用最外层的那个DataList的控件ID
我用GridView、Repeater、Table等控件都是可以的，我想DataList应该也是可以的。
大概是有什么地方没写对吧

Answer 13

   private void Export(string FileType, string FileName)
    {
        Response.ClearContent();
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.UTF7;
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
        Response.ContentType = FileType;
        this.EnableViewState = false;
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        GridView1.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();        #region 删除进程
        //Process[] myProcesses;
        //DateTime startTime;
        //myProcesses = Process.GetProcessesByName("Excel");        ////得不到Excel进程ID，暂时只能判断进程启动时间
        //foreach (Process myProcess in myProcesses)
        //{
            
        //       // myProcess.Kill();
           
        //}
        #endregion
       
    }

Answer 14

 ExportExcel(Finatable,path+ "aa.xls");
是个Datatable 

Answer 15

    protected void Button1_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=file.xls");        Response.Charset = "GB2312";
        Response.ContentEncoding = Encoding.UTF8;        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);        Page.EnableViewState = false;        rptDelivList.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
       
    }

Answer 16

你要明白导出为Excel是怎么做到的，其实是利用Excel对<table>的兼容性，把GridView输出的<table>塞给Excel作兼容性处理。无论你要导出什么数据，你输出为<table>就可以了，用什么服务器端控件不重要。