Common/Excel.cs

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Data;
using System.Collections;
using System.Windows.Forms;
using System.IO;

namespace Common
{
    public class Excel
    {
        public Excel() { }

        /// <summary>
        /// 导出Excel方法 【web版本】
        /// </summary>
        /// <param name="dt">Datatable数据集</param>
        /// <param name="hsTable">表字段对应的列名</param>
        /// <param name="Title">导出EXCEL的名称 不需要要带有扩展名_xls</param>
        public static void DataTableToExcel(DataTable dt, DataTable hsTable, string Title)
        {
            HttpResponse resp = System.Web.HttpContext.Current.Response;
            resp.Clear();////清除所有缓存区的内容
            string ExcelName = Title + "_" + DateTime.Now.ToString("yyyyMMddHHmmss");
            resp.ContentType = "application/ms-excel";
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");//
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(ExcelName, System.Text.Encoding.UTF8) + ".xls");
            string colHeaders = "", ls_item = "";
            DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int cl = hsTable.Rows.Count;
            //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符 添加网格
            resp.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\"><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />" +
                 "<!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>" + Title +
                 "</x:Name><x:WorksheetOptions><x:Print><x:ValidPrinterInfo /></x:Print></x:WorksheetOptions></x:ExcelWorksheet>" +
                 "</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table><tr ");
            foreach (DataRow dicEntry in hsTable.Rows)
            {
                colHeaders += "<th>" + dicEntry["Value"].ToString() + "</th>";
            }
            resp.Write(colHeaders + "</tr>");
            //向HTTP输出流中写入取得的数据信息
            //逐行处理数据
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
                ls_item = "<tr>";
                foreach (DataRow dic in hsTable.Rows)
                {
                    ls_item += "<td>" + row[dic["Key"].ToString()].ToString() + "</td>";
                }
                ls_item += "</tr>";
                resp.Write(ls_item);
            }
            resp.Write("</table></body></html>");
            resp.End();//终止响应，将之前缓存中的数据发给浏览器，End()之后的代码不会被继续执行
        }
    }