public void ExportToExcel(System.Web.UI.Control ctl) 
{ 
bool CurrCtlVisible=ctl.Visible; ctl.Visible=true; Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls"); Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); Response.ContentType = "application/ms-excel"; ctl.Page.EnableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(tw); ctl.RenderControl(hw); Response.Write(tw.ToString()); Response.End(); ctl.Page.EnableViewState = true; ctl.Visible=CurrCtlVisible;
}2.在引用里调用Microsoft.Office.Interop.Excel.dll，原理是把数据存到DataTable、DataView或DataGrid中，然后再把数据一格一格的赋到excel的cell里去。
见如下代码：
public class ExportToExcel {
#region 私有成员 // 数据的DataView private DataView dv=null; // 表格标题 private string title=null; // 输出文件路径 private string outFilePath=null; // 输入文件名 private string inputFilePath=System.Windows.Forms.Application.StartupPath+@" emplate.xls";
#endregion #region 公共属性 /// <summary> /// 数据的DataView /// </summary> public DataView DV { set { dv=value; } } /// <summary> /// 表格标题 /// </summary> public string Title { set { title=value; } get { return title; } } /// <summary> /// 输出文件路径 /// </summary> public string OutFilePath { set { outFilePath=value; } get { return outFilePath; } } /// <summary> /// 输入文件路径 /// </summary> private string InputFilePath { set { inputFilePath=value; } get { return inputFilePath; } } #endregion #region 构造函数 public ExportToExcel() { }// public OutputExcel(DataView dv,string title)// {//// } #endregion #region 公共方法 // public void CreateExcel() { int rowIndex=4;//行起始坐标 int colIndex=1;//列起始坐标 ApplicationClass myApp=null; Workbook myBook=null; Worksheet mySheet=null; //如果文件不存在，则将模板文件拷贝一份作为输出文件 if(!File.Exists(outFilePath)) { File.Copy(inputFilePath,outFilePath,true); } myApp= new ApplicationClass(); myApp.Visible=false; object oMissiong=System.Reflection.Missing.Value; myApp.Workbooks.Open(outFilePath,oMissiong,oMissiong,oMissiong,oMissiong, oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong, oMissiong,oMissiong,oMissiong); myBook=myApp.Workbooks[1]; mySheet=(Worksheet)myBook.ActiveSheet; //取得标题 foreach(DataColumn col in dv.Table.Columns) { colIndex++; mySheet.Cells[4,colIndex] = col.ColumnName; mySheet.get_Range(mySheet.Cells[4,colIndex],mySheet.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐 } //取得表格中的数据 foreach(DataRowView row in dv) { rowIndex ++; colIndex = 1; foreach(DataColumn col in dv.Table.Columns) { colIndex ++; if(col.DataType == System.Type.GetType("System.DateTime")) { mySheet.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); mySheet.get_Range(mySheet.Cells[rowIndex,colIndex],mySheet.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐 } else if(col.DataType == System.Type.GetType("System.String")) { mySheet.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString(); mySheet.get_Range(mySheet.Cells[rowIndex,colIndex],mySheet.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐 } else { mySheet.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString(); } } } //加载一个合计行 int rowSum = rowIndex + 1; int colSum = 2; mySheet.Cells[rowSum,2] = "合计"; mySheet.get_Range(mySheet.Cells[rowSum,2],mySheet.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter; //设置选中的部分的颜色 mySheet.get_Range(mySheet.Cells[rowSum,colSum],mySheet.Cells[rowSum,colIndex]).Select(); mySheet.get_Range(mySheet.Cells[rowSum,colSum],mySheet.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种 //取得整个报表的标题 mySheet.Cells[2,2] = title; //设置整个报表的标题格式 mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,2]).Font.Bold = true; mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,2]).Font.Size = 22; //设置报表表格为最适应宽度 mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Select(); mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Columns.AutoFit(); //设置整个报表的标题为跨列居中 mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,colIndex]).Select(); mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; //绘制边框 mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Borders.LineStyle = 1; mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗 mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗 mySheet.get_Range(mySheet.Cells[4,colIndex],mySheet.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗 mySheet.get_Range(mySheet.Cells[rowSum,2],mySheet.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗 myBook.Save(); myBook.Close( true,outFilePath,true); System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp); myApp.Quit(); GC.Collect(); } #endregion }3.另外，这就是另外一种方法了，建一个SqlServer的数据源，利用Excel的外部数据源让Excel自己从数据库取数据：
public void ExportToExcel(string pstrSql) { Excel.Application pApplication; Excel._Workbook xBk; Excel._Worksheet xSt; Excel._QueryTable xQt; string ExcelConn = "ODBC;DRIVER=SQL Server;SERVER=localhost;UID=sa;PWD=;APP=Microsoft(R) Windows (R) 2000 Operating System;WSID=me;DATABASE=pubs"; pApplication = new Excel.ApplicationClass(); xBk = pApplication.Workbooks.Add(true); xSt = (Excel._Worksheet)xBk.ActiveSheet; pApplication.Cells[2,2] = this.title; xSt.get_Range(pApplication.Cells[2,2],pApplication.Cells[2,2]).Font.Bold = true; xSt.get_Range(pApplication.Cells[2,2],pApplication.Cells[2,2]).Font.Name = "黑体"; xSt.get_Range(pApplication.Cells[2,2],pApplication.Cells[2,2]).Font.Size = 22; xQt = xSt.QueryTables.Add(ExcelConn,xSt.get_Range(pApplication.Cells[4,2],pApplication.Cells[4,2]),pstrSql); xQt.Name = "导出EXCEL"; xQt.FieldNames = true; xQt.RowNumbers = false; xQt.FillAdjacentFormulas = false; xQt.PreserveFormatting = false; xQt.BackgroundQuery = true; xQt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells; xQt.AdjustColumnWidth = true; xQt.RefreshPeriod = 0; xQt.PreserveColumnInfo = true; xQt.Refresh(xQt.BackgroundQuery); pApplication.Visible = true; }这里的pstrSql指的是sql语句。