C# DataTable to Excel

 **//// <summary>
 2        /// 把DataTable内容导出伟excel并返回客户端
 3        /// </summary>
 4        /// <param name="dgData">待导出的DataTable</param>
 5        /// 创 建 人： 
 6        /// 创建日期：
 7        /// 修 改 人：
 8        /// 修改日期：
 9        public static void DataTable2Excel(System.Data.DataTable dtData)
10        {
11            System.Web.UI.WebControls.DataGrid dgExport = null;
12            // 当前对话
13            System.Web.HttpContext curContext = System.Web.HttpContext.Current;
14            // IO用于导出并返回excel文件
15            System.IO.StringWriter strWriter = null;
16            System.Web.UI.HtmlTextWriter htmlWriter = null;
17
18            if (dtData != null)
19            {
20                // 设置编码和附件格式
21                curContext.Response.ContentType = "application/vnd.ms-excel";
22                curContext.Response.ContentEncoding =System.Text.Encoding.UTF8;
23                curContext.Response.Charset = "";
24                
25                // 导出excel文件
26                strWriter = new System.IO.StringWriter();
27                htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
28
29                // 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
30                dgExport = new System.Web.UI.WebControls.DataGrid();
31                dgExport.DataSource = dtData.DefaultView;
32                dgExport.AllowPaging = false;
33                dgExport.DataBind();
34
35                // 返回客户端
36                dgExport.RenderControl(htmlWriter);    
37                curContext.Response.Write(strWriter.ToString());
38                curContext.Response.End();
39            }
40        }

 

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.Diagnostics;
using Excel;
using System.Text;

/// <summary>
/// TableToExcel

///Table导入Excel

/// </summary>
public class TableToExcel
{
 public TableToExcel()
 {
  // TODO: 在此处添加构造函数逻辑
 }

    public void ToExcel(Table t,string title)
    {
        Excel.ApplicationClass ea = new ApplicationClass();
        Excel.Workbook wb = ea.Workbooks.Add(true);
       
      
        Excel.Worksheet ws = wb.ActiveSheet as Excel.Worksheet;
        int col = t.Rows[(t.Rows.Count - 1)].Cells.Count;
        Excel.Range range = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, col]);
        range.MergeCells = true;
        range.Value2 = title;
        for (int i = 1; i <= t.Rows.Count; i++)
        {
            int count = 1;//当前列
            for (int j = 1; j <= t.Rows[i - 1].Cells.Count; j++)
            {
                //ws.Cells[i, j] = "";
                int colspan = t.Rows[i - 1].Cells[j-1].ColumnSpan;
                if (colspan == 0) colspan = 1;
                if (colspan == 1)
                {
                    ws.Cells[i + 1, count] = cleanStr(t.Rows[i - 1].Cells[j - 1].Text);
                    Range _r = ws.Cells[i+1, count] as Range;
                    Excel.Style s = _r.Style as Excel.Style;
                    s.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    count++;
                }
                else
                {
                    Excel.Range _range = ws.get_Range(ws.Cells[i+1, count], ws.Cells[i+1, count + colspan - 1]);
                    _range.MergeCells = true;
                    _range.Value2 = cleanStr(t.Rows[i - 1].Cells[j - 1].Text);
                    count = count + colspan ;
                }
               
            }
        }
        wb.SaveCopyAs(HttpContext.Current.Server.MapPath("~/Courses/temp/Excel.xls"));

    }

    protected string cleanStr(String str)
    {
        StringBuilder bs = new StringBuilder(str);
        bs = bs.Replace("<br/>", " ");
        return bs.ToString();
    }

}