好心人请指教SQL导出Excel表格问题！！一生平安！

Question

我是用的这种方法来把sql数据库里面的数据导入到Excel
protected void DCWJ_Click(object sender, EventArgs e)
        {
            string typeid = "1";
            string FileName = "Members.xls";
            HttpResponse resp;
            resp = Page.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            string colHeaders = "", ls_item = "";
            int i = 0;            //定义表对象与行对像，同时用DataSet对其值进行初始化
            DataTable dt = new JobLord_Job.BLL.RL_LoveMember().GetList(100000, " State<>0" + WhereStr, "CreateDate Desc").Tables[0];
            DataRow[] myRow = dt.Select("");
            // typeid=="1"时导出为EXCEL格式文件；typeid=="2"时导出为XML格式文件
            if (typeid == "1")
            {
                ////取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
                colHeaders += "姓名\t性别\t出生年月\t公司名称\t联系方式\tQQ号码\tEmail\t";
                colHeaders += "\n";
                ////向HTTP输出流中写入取得的数据信息
                resp.Write(colHeaders);
                ////逐行处理数据 
                foreach (DataRow row in myRow)
                {
                    ////在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n
                    //JobLord_Job.Model.RL_Talent theMember = new JobLord_Job.BLL.RL_Talent().GetModel(Convert.ToInt16(id));
                    //ls_item += row["UserName"].ToString() + "\t";
                    ls_item += row["RealName"].ToString() + "\t";
                    ls_item += row["Sex"].ToString() + "\t";
                    ls_item += row["BirthDay"].ToString() + "\t";
                    ls_item += row["Company"].ToString() + "\t";
                    ls_item += row["Mobile"].ToString() + "\t";
                    ls_item += row["QQ"].ToString() + "\t";
                    ls_item += row["EMail"].ToString() + "\t";                    ls_item += "\n";                    ////当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 
                    resp.Write(ls_item);
                    ls_item = "";
                }
            }            //写缓冲区中的数据到HTTP头文件中
            resp.End();
            dataBind();
        }
但是如果这个里面有些字段需要特殊处理呢？
比如存储了一个ItemID字段，存储格式是1,2,3,这样都逗号分隔开的    关联到Item表主键ID    再把根据这些ID独处Item 表里面的name字段
应该怎么做？  

Answer 1

private void ExportDSToExcel(DataTable dt)
    {
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        Response.Clear();
        Response.Buffer = true;
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + (DateTime.Now.ToString("yyyyMMddHHmmss") + Server.UrlEncode("项目周报信息")) + ".xls");
        Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
        this.EnableViewState = false;
        DataGrid dg = new DataGrid();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            switch (dt.Columns[i].ColumnName)
            {
                case "Pro_Name":
                    dt.Columns[i].ColumnName = "项目名称";
                    break;
                case "Pro_NO":
                    dt.Columns[i].ColumnName = "协议号";
                    break;
                case "ProductNO":
                    dt.Columns[i].ColumnName = "产品号";
                    break;
                case "ProMgrName":
                    dt.Columns[i].ColumnName = "项目经理";
                    break;
                case "GroupMgrName":
                    dt.Columns[i].ColumnName = "导演";
                    break;
                case "Create_Date":
                    dt.Columns[i].ColumnName = "立项日期";
                    break;
                case "ScoreStatus":
                    dt.Columns[i].ColumnName = "打分状态";
                    break;
                case "PriceTime":
                    dt.Columns[i].ColumnName = "报价";
                    break;
                case "ModelTime":
                    dt.Columns[i].ColumnName = "模型";
                    break;
                case "PreviewTime":
                    dt.Columns[i].ColumnName = "预演";
                    break;
                case "RenderingTime":
                    dt.Columns[i].ColumnName = "渲染";
                    break;
                case "LateStageTime":
                    dt.Columns[i].ColumnName = "后期";
                    break;
                case "OneRoundTime":
                    dt.Columns[i].ColumnName = "第一轮修改";
                    break;
                case "ThreeRoundTime":
                    dt.Columns[i].ColumnName = "第二轮修改";
                    break;
                case "NRoundTime":
                    dt.Columns[i].ColumnName = "第N轮修改";
                    break;
                case "StopTime":
                    dt.Columns[i].ColumnName = "暂停";
                    break;
                case "CompletedTime":
                    dt.Columns[i].ColumnName = "完工";
                    break;
                default:
                    dt.Columns.RemoveAt(i);
                    i--;
                    break;
            }
        }
        dg.CssClass = "desktable";
        dg.HeaderStyle.CssClass = "header";
        dg.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        dg.PagerStyle.Position = PagerPosition.Bottom;
        dg.FooterStyle.CssClass = "header";
        dg.DataSource = dt;
        dg.DataBind();
        dg.RenderControl(htw);
        DataSet ds = new DataSet();
        ds.Tables.Add(dt);
        Response.Write(sw.ToString());
        Response.End();
    }