好心人请指教SQL导出Excel表格问题!!一生平安!
我是用的这种方法来把sql数据库里面的数据导入到Excelprotected 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字段
应该怎么做? --------------------编程问答-------------------- 拼成HTML表格。 --------------------编程问答-------------------- 百度“Aspose.Cells”。 --------------------编程问答--------------------
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();
}
要做什么解析 自己做。。。我只能帮到这儿了。
补充:.NET技术 , ASP.NET