输出EXCEL文件常用的几种方法

1、利用控件的RenderControl方法，将数据绑定控件中的内容输出到EXCEL文件

     Response.Clear();
        Response.Buffer  =   true ;
        Response.AddHeader( " content-disposition " ,  " attachment;filename=order.xls " );
        Response.Charset  =   " GB2312 " ;
        Response.ContentEncoding  =  System.Text.Encoding.GetEncoding( " gb2312 " );
        Response.ContentType  =   " application/ms-excel " ;
        System.IO.StringWriter stringWrite  =   new  System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite  =   new  HtmlTextWriter(stringWrite);
        GVDetail.AllowPaging  =   false ;     // 如果是数据源分页则设置无效
        BindDt(); // 绑定数据源
        GVDetail.RenderControl(htmlWrite);
        Response.Write(stringWrite);
        Response.End();
        GVDetail.AllowPaging  =   true ;
        BindDt(); // 绑定数据源

注意在使用改方法时需重写VerifyRenderingInServerForm方法
    public override void VerifyRenderingInServerForm(Control control)
    {
        // Confirms that an HtmlForm control is rendered for
    }
这种方法常用也比较简单，适合一些需求相对简单输出Excel文件的要求。 

2、组合HTML输出Excel文件

   DataTable dt  =   new  BankInfo().SumList(Stime, Etime);
        StringBuilder strHtml  =   new  StringBuilder();
         decimal  a1  =   0.00m ;
         decimal  a2  =   0.00m ;
         decimal  a3  =   0.00m ;
         decimal  a4  =   0.00m ; 
         decimal  a5  =   0.00m ;
         decimal  a6  =   0.00m ;
        strHtml.AppendLine( " <table border="1" cellpadding="1" cellspacing="1" bordercolor="#996600"> " );
        strHtml.AppendLine( "   <tr> " );
        strHtml.AppendLine( "     <td width="75" align="center" bgcolor="#CCCC66"><strong>编号</strong></td> " );
        strHtml.AppendLine( "     <td width="140" align="center" bgcolor="#CCCC66"><strong>分支机构</strong></td> " );
        strHtml.AppendLine( "     <td width="120" align="center" bgcolor="#CCCC66"><strong>当前余额</strong></td> " );
        strHtml.AppendLine( "     <td width="120" height="25" align="center" bgcolor="#CCCC66"><strong><font color="#000000">定单金额</font></strong></td> " );
        strHtml.AppendLine( "     <td width="120" height="25" align="center" bgcolor="#CCCC66"><strong>充值金额</strong></td> " );
        strHtml.AppendLine( "     <td width="120" height="25" align="center" bgcolor="#CCCC66"><strong>扣款金额</strong></td> " );
        strHtml.AppendLine( "     <td width="120" height="25" align="center" bgcolor="#CCCC66"><strong>返款金额</strong></td> " );
        strHtml.AppendLine( "     <td width="120" height="25" align="center" bgcolor="#CCCC66"><strong>退款金额</strong></td> " );
        strHtml.AppendLine( "   </tr> " );

         for  ( int  n  =   0 ; n  <  dt.Rows.Count; n ++ )
         ... {

            DataRow dr=dt.Rows[n];
            strHtml.AppendLine("  <tr> ");
            strHtml.AppendLine("    <td width="75" height="27" align="center" bgcolor="#FBFAF2">"+dr["AreaID"]+"</td> ");
            strHtml.AppendLine("    <td width="140" bgcolor="#FBFAF2">" + dr["Company"] + "</td> ");
            strHtml.AppendLine("    <td align="right" bgcolor="#FBFAF2"><font color="#FF0000">" + string.Format("{0:C}", dr["num"]) + "</font></td> ");
            strHtml.AppendLine("    <td align="right"><font color="#FF0000">" + string.Format("{0:C}", dr["dt"]) + "</font></td> ");
            strHtml.AppendLine("    <td width="120" align="right"><font color="#FF0000">" + string.Format("{0:C}", dr["cz"]) + "</font></td> ");
            strHtml.AppendLine("    <td align="right"><font color="#FF0000">" + string.Format("{0:C}", dr["kk"]) + "</font></td> ");
            strHtml.AppendLine("    <td align="right"><font color="#FF0000">" + string.Format("{0:C}", dr["fk"]) + "</font></td> ");
            strHtml.AppendLine("    <td align="right"><font color="#FF0000">" + string.Format("{0:C}", dr["tk"]) + "</font></td> ");
            strHtml.AppendLine("  </tr> ");
            a1 += Convert.ToDecimal(dr["num"]);
            a2 += Convert.ToDecimal(dr["dt"]);
            a3 += Convert.ToDecimal(dr["cz"]);
            a4 += Convert.ToDecimal(dr["kk"]);
            a5 += Convert.ToDecimal(dr["fk"]);
            a6 += Convert.ToDecimal(dr["tk"]);
        }
        strHtml.AppendLine( "   <tr> " );
        strHtml.AppendLine( "     <td colspan="2" align="center" bgcolor="#FBFAF2">&nbsp;</td> " );
        strHtml.AppendLine( "     <td align="right" bgcolor="#FBFAF2">&nbsp; "   +   string .Format( " {0:C} " , a1)  +   " </td> " );
        strHtml.AppendLine( "     <td height="21" align="center">&nbsp; "   +   string .Format( " {0:C} " , a2)  +   " </td> " );
        strHtml.AppendLine( "     <td align="right">&nbsp; "   +   string .Format( " {0:C} " , a3)  +   " </td> " );
        strHtml.AppendLine( "     <td align="center">&nbsp; "   +   string .Format( " {0:C} " , a4)  +   " </td> " );
        strHtml.AppendLine( "     <td align="right">&nbsp; "   +   string .Format( " {0:C} " , a5)  +   " </td> " );
        strHtml.AppendLine( "     <td align="right">&nbsp; "   +   string .Format( " {0:C} " , a6)  +   " </td> " );
        strHtml.AppendLine( "   </tr> " );
        strHtml.AppendLine( " </table> " );
        Response.Clear();
        Response.AddHeader( " content-disposition " ,  " attachment;filename=Sum.xls " );
        Response.Charset  =   " gb2312 " ;
        Response.ContentEncoding  =  Encoding.GetEncoding( " gb2312 " );
        Response.ContentType  =   " application/ms-excel " ;
        Response.Write(strHtml.ToString());
        Response.End();

 

这也是一种比较长用的输出Excel的方法、很容易掌握，方便Excel布局，只是写起来比较麻烦，不过有时候挺管用。

3、利用OleDbConnection对象操作Excel文件

using  System;
using  System.Data;
using  System.Configuration;
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.OleDb;
/**/ /// <summary>
/// Summary description for Handle
/// </summary>
public   class  Handle
... {
    public Handle()
    ...{
        //
        // TODO: Add constructor logic here
        //
    }
    /**//// <summary>
    /// 读取Excel文档
    /// </summary>
    /// <param name="Path">文件名称</param>
    /// <returns>返回一个数据集</returns>
    public DataSet ExcelToDS(string Path)
    ...{
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        string strExcel = "";
        OleDbDataAdapter myCommand = null;
        DataSet ds = null;
        strExcel = "select * from [sheet1$]";
        myCommand = new OleDbDataAdapter(strExcel, strConn);
        ds = new DataSet();
        myCommand.Fill(ds, "table1");
        conn.Close();
        conn.Dispose();
        return ds;
    }
    public DataTable GetTables(string Path)
    ...{
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;"; 
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open(); 
        DataTable schemaTable =  conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
        conn.Close();
        conn.Dispose();
        return schemaTable;
    }
    /**//// <summary>
    /// 写入Excel文档
    /// </summary>
    /// <param name="Path">文件名称</param>
    public bool InsertExcel(string Path)
    ...{
        try
        ...{
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = "insert into [sheet1$](isdn,Title,Price,Author) values('33','SOA体系',50,'AA')";
            cmd.ExecuteNonQuery();
          
            conn.Close();
            return true;
        }
        catch (System.Data.OleDb.OleDbException ex)
        ...{
            System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
        }
        return false;
    }
    /**//// <summary>
    /// 创建并下载EXCEL文件
    /// </summary>
    /// <param name="ds"></param>
    public void DownExcel()
    ...{
        
            string urlPath = HttpContext.Current.Request.ApplicationPath + "/Temp/";
            string physicPath = HttpContext.Current.Server.MapPath("~/");
            string fileName = new Random().Next(1000) + ".Xls";
            string sql = "CREATE TABLE Product( ID int,Product varchar(100) )";
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + physicPath + fileName + ";Extended Properties=Excel 8.0;";
            OleDbConnection objConn = new OleDbConnection(connString);
            OleDbCommand objCommand = new OleDbCommand(sql, objConn);
            OleDbDataAdapter myCommand = new OleDbDataAdapter(objCommand);
            objConn.Open();
            objCommand.ExecuteNonQuery();
            objCommand.CommandText = "select * from Product ";
            DataSet ds = new DataSet();
            myCommand.Fill(ds, "Product");
            myCommand.Update(ds, "Product");
            objConn.Close();
            objConn.Dispose();
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();
            response.WriteFile(physicPath + fileName);
            string httpHeader = "attachment;filename=backup.Xls";
            response.AppendHeader("Content-Disposition", httpHeader);
            response.Flush();
            System.IO.File.Delete(physicPath + fileName);//删除临时文件
            response.End();
    }
}

 

很多人认为这是操作Ecel文件的最佳方案。不过我认为该方法用于读入、查询EXCEL是比较方便的，但如果只是输出Excel文件的需求，感觉不如其它的方法好用。

4、利用Excel.Application操作Excel文件

using  System;
using  System.Data;
using  System.Configuration;
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  Microsoft.Office.Interop;
/**/ /// <summary>
/// Summary description for sHandle
/// </summary>
public   class  sHandle
... {
    public sHandle()
    ...{
        //
        // TODO: Add constructor logic here
        //
    }
    public static void Save(DataSet ds)
    ...{
        Excel.Application app = new Excel.Application();
        Excel.Workbooks workBoks=(Excel.Workbooks)app.Workbooks;
        Excel.Workbook workBook = workBoks.Add(Type.Missing);
        Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
     
        foreach (DataTable dt in ds.Tables)
        ...{
            workSheet = (Excel.Worksheet)workBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            for (int i = 0; i < dt.Columns.Count; i++)
            ...{
                workSheet.Cells[1, 1+i] = dt.Columns[i].ColumnName;
            }
            workSheet.Name = dt.TableName;
        }
        workBook.SaveAs(@"D:MycodeExcelBook2.xls", Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing);
        workBook.Close(Type.Missing, Type.Missing, Type.Missing);
        app.Workbooks.Close();

        app.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
        workBook = null;
        app = null;
        GC.Collect();    //控制回收垃圾
    }
}

注意利用改方法以前必须在服务器上安装Excel2000或其更高版本。在项目中添加COM引用，Microsoft Office web Compents 11
改方法功能比较强大，但比较占用系统资源。所以必须留心不要忘记释放资源。