考勤管理中出差记录写入Excel文件

 /*
 * * 文 件 名：   export_cc.aspx.cs
 * * 类    名：   general_attendance_export_cc
 * * 创 建 人：   yaniue
 * * 创建时间：   2008-9-24
 * * 功能简介：   考勤管理中出差记录写入Excel文件
 * * 版    本：   1.0.0.00
 */
using System;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Collections;
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;

public partial class general_attendance_export_cc : PageBase
{
    StringBuilder strSql = new StringBuilder("");//定义strSql变量
    string[] columnsName ={ "部门", "姓名", "出差地点", "开始日期", "结束日期", "批示领导", "状态" };
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            base.Initialize();
            // 将考勤管理中出差记录写入Excel文件
            AttendInfo ai = new AttendInfo();
            DataTable ds = ai.EvectionAttend;
            if (ds != null && ds.Rows.Count != 0)
            {
                DataTable tabData = DeepDataTableCopy(ds);
                string strTable = GetHtmlTable(tabData);
                Response.Charset = "GB2312";//设置编码
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                Response.AppendHeader("Content-Disposition", "attachment;filename="
                    + HttpUtility.UrlEncode("出差申请" + ".xls", Encoding.UTF8).ToString());
                Response.ContentType = "application/x-excel";//设置输出格式
                Response.Write(strTable);//输出文件
                Response.End();
            }
        }
    }
    /// <summary>
    /// 数据表深层拷贝
    /// </summary>
    /// <param name="dt">摸班表</param>
    /// <returns>新表</returns>
    private DataTable DeepDataTableCopy(DataTable table)
    {
        DataTable newTab = new DataTable();
        for (int i = 0; i < table.Columns.Count-5; i++)//添加列
        {
            DataColumn newColumn = new DataColumn();
            newColumn.ColumnName = table.Columns[i].ColumnName;
            newColumn.DataType = typeof(System.String);
            newTab.Columns.Add(newColumn);
        }
        for (int i = 0; i < table.Rows.Count; i++)//添加数据
        {
            DataRow newRow = newTab.NewRow();
            for (int j = 0; j < table.Columns.Count - 5; j++)
            {
                newRow[j] = table.Rows[i][j] == null ? "" : table.Rows[i][j].ToString();
                //if (table.Rows[i][j].ToString() == "False")
                //{
                //    newRow[j] = "男";
                //}
                //if (table.Rows[i][j].ToString() == "True")
                //{
                //    newRow[j] = "女";
                //}
            }
            newTab.Rows.Add(newRow);
        }
        return newTab;
    }
    /// <summary>
    /// 生成HTML表格对象
    /// </summary>
    /// <param name="dt">数据表对象</param>
    /// <returns>字符串对象</returns>
    private string GetHtmlTable(DataTable dt)
    {
        int columnCount = 0;
        string strTable = "<table border='1' borderColor='black' style='font-size:11pt;'>";
        strTable += "<tr>";
        foreach (DataColumn column in dt.Columns)//添加选择的列
        {
                columnCount++;
                strTable += "<td align='center'><strong>" + column.ColumnName + "</strong></td>";
        }
        strTable += "</tr>";
        int i = 0;
        for (i = 0; i < dt.Rows.Count; i++)//向表中添加数据
        {
            strTable += "<tr>";
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                    strTable += "<td align='center'>" + (dt.Rows[i][j] == null ? "" : dt.Rows[i][j].ToString().Replace(" 0:00:00", "")) + "</td>";
            }
            strTable += "</tr>";
        }
        strTable += "<tr><td colspan='" + columnCount + "'><b>共计" + i + "条数据<b></td></tr>";
        strTable += "</table>";
        return strTable;
    }
}