DataTable数据导出到Excel

数据导出至Excel方法
本文介绍了两种将数据导出到Excel的方法,一种适用于Web环境,另一种则既可用于WinForm也可用于Web。第一种方法使用StringBuilder来构建HTML表格并直接写入到HTTP响应中;第二种方法通过Microsoft Excel Interop组件在内存中创建Excel文件。
最近没事Reflect了一个dll,发现数据导出到Excel的方法。写的还不错,可以在我的博客里下载Reflector
InBlock.gifpublic void DataBindTitleExcel(Page pPage, DataTable dt, string ExcelTitle, string strUserMsg) 
InBlock.gif
InBlock.gif        HttpResponse response = pPage.Response; 
InBlock.gif        if (dt.Rows.Count == 0) 
InBlock.gif        { 
InBlock.gif                response.Write("<script>alert('对不起,没有可用于导出的数据!')</script>"); 
InBlock.gif                response.End(); 
InBlock.gif        } 
InBlock.gif        response.ContentEncoding = Encoding.GetEncoding("GB2312"); 
InBlock.gif        response.ContentType = "application/ms-excel"
InBlock.gif        response.AppendHeader("Content-Disposition""attachment;filename=Export.xls"); 
InBlock.gif        int count = dt.Columns.Count; 
InBlock.gif        StringBuilder builder = new StringBuilder(); 
InBlock.gif        builder.Append("<html><head>\n"); 
InBlock.gif        builder.Append("<meta http-equiv=\"Content-Language\" content=\"zh-cn\">\n"); 
InBlock.gif        builder.Append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">\n"); 
InBlock.gif        builder.Append("</head>\n"); 
InBlock.gif        builder.Append("<table border=1>"); 
InBlock.gif        if (ExcelTitle != "") 
InBlock.gif        { 
InBlock.gif                string str = "<font size=4><b>" + ExcelTitle + "</b></font>"
InBlock.gif                if (strUserMsg != "") 
InBlock.gif                { 
InBlock.gif                        str = str + "(" + strUserMsg + ")"
InBlock.gif                } 
InBlock.gif                builder.Append(string.Concat(new object[] { "<tr><td colspan=", count, ">", str, "</td></tr>" })); 
InBlock.gif        } 
InBlock.gif        builder.Append("<tr><td colspan=" + count + " valign=middle height=24>"); 
InBlock.gif        builder.Append("查询时间:" + DateTime.Now.ToString("G") + "</td></tr>"); 
InBlock.gif        builder.Append("<tr>\n"); 
InBlock.gif        for (int i = 0; i < count; i++) 
InBlock.gif        { 
InBlock.gif                if (dt.Columns[i].Caption.ToString().ToLower() != "id"
InBlock.gif                { 
InBlock.gif                        builder.Append("<td bgcolor=#CCFFCC><b>" + dt.Columns[i].Caption.ToString() + "</b></td>\n"); 
InBlock.gif                } 
InBlock.gif        } 
InBlock.gif        foreach (DataRow row in dt.Rows) 
InBlock.gif        { 
InBlock.gif                builder.Append("<tr>"); 
InBlock.gif                for (int j = 0; j < count; j++) 
InBlock.gif                { 
InBlock.gif                        if (dt.Columns[j].Caption.ToString().ToLower() != "id"
InBlock.gif                        { 
InBlock.gif                                builder.Append("<td style='vnd.ms-excel.numberformat:@'>" + row[j].ToString() + "</td>"); 
InBlock.gif                        } 
InBlock.gif                } 
InBlock.gif                builder.Append("</tr>\n"); 
InBlock.gif        } 
InBlock.gif        builder.Append("</table>\n"); 
InBlock.gif        response.Write(builder.ToString()); 
InBlock.gif        response.End(); 
InBlock.gif
InBlock.gif 
再看看下面这个简单的,需要引入Microsoft Excel 11.0 liberary
InBlock.gifusing System; 
InBlock.gifusing System.Collections.Generic; 
InBlock.gifusing System.Linq; 
InBlock.gifusing System.Text; 
InBlock.gifusing System.Data; 
InBlock.gifnamespace Utility.Util 
InBlock.gif
InBlock.gif 
InBlock.gifpublic class Excelcs#region 
InBlock.gif        
public class Excelcs 
InBlock.gif        { 
InBlock.gif                public static bool DataGridviewShowToExcel(DataTable dt, bool isShowExcle) 
InBlock.gif                { 
InBlock.gif                        if (dt.Rows.Count == 0) 
InBlock.gif                                return false
InBlock.gif                        //建¡§立¢¡éExcel对?象¨®         
InBlock.gif                        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); 
InBlock.gif                        excel.Application.Workbooks.Add(true); 
InBlock.gif                        excel.Visible = isShowExcle; 
InBlock.gif                        //生¦¨²成¨¦字Á?段?名?称?         
InBlock.gif                        for (int i = 0; i < dt.Columns.Count; i++) 
InBlock.gif                        { 
InBlock.gif                                excel.Cells[1, i + 1] = dt.Columns[i].Caption; 
InBlock.gif                        } 
InBlock.gif                        //填¬?充?数ºy据Y         
InBlock.gif                        for (int i = 0; i < dt.Rows.Count - 1; i++) 
InBlock.gif                        { 
InBlock.gif                                for (int j = 0; j < dt.Columns.Count; j++) 
InBlock.gif                                { 
InBlock.gif                                        if (dt.Rows[j][i].GetType() == typeof(string)) 
InBlock.gif                                        { 
InBlock.gif                                                excel.Cells[i + 2, j + 1] = "'" + dt.Rows[j][i].ToString(); 
InBlock.gif                                        } 
InBlock.gif                                        else 
InBlock.gif                                        { 
InBlock.gif                                                excel.Cells[i + 2, j + 1] = dt.Rows[j][i].ToString(); 
InBlock.gif                                        } 
InBlock.gif                                } 
InBlock.gif                        } 
InBlock.gif                        return true
InBlock.gif                } 
InBlock.gif        } 
InBlock.gif        #endregion 
InBlock.gif
第一种只适用于web,第二种winform和web都可


本文转自 BruceAndLee 51CTO博客,原文链接:http://blog.51cto.com/leelei/325361,如需转载请自行联系原作者

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值