jiang_jiajia10漂亮姐姐帮帮我
直接从数据库查询数据导出到ＥＸＣＥＬ和．ＣＳＶ文件编码如：２００８０００００００１变成科学记数法
供应商编号：００００１变成1
我现在在他们前面加了＂'＂,可这样不利于数据再导入
帮帮忙漂亮，姐姐，我代码如下：
SqlConnection conn = new SqlConnection(Session["conString"].ToString());
SqlDataAdapter da = new SqlDataAdapter(Session["sql"].ToString(), conn);
DataSet ds = new DataSet();
da.Fill(ds, "table1");
DataTable dt = ds.Tables["table1"];
//存放到web.config中downloadurl指定的路径，文件格式为当前日期+4位随机数
string name = System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["downloadurl"]) + DateTime.Today.ToString("yyyyMMdd") + new Random(DateTime.Now.Millisecond).Next(10000).ToString();
if (this.drotype.SelectedIndex == 1)
{
name = name + ".xls";
}
if (this.drotype.SelectedIndex == 2)
{
name = name + ".csv";
}
FileStream fs = new FileStream(name, FileMode.Create, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.GetEncoding("gb2312"));
if (this.drotype.SelectedIndex == 1)
{
sw.WriteLine("报价单号\t供应商编号\t供应商名称\t编号\t商品条码\t商品名称\t单位\t规格\t包装\t供货价\t建议售价\t创建日期\t审核日期\t单据属性\t备注");
foreach (DataRow dr in dt.Rows)
{
sw.WriteLine(" '" + dr["id"].ToString() + "\t" + " '" + dr["vendorid"].ToString() + "\t" + dr["vendorname"].ToString() + "\t" + dr["no"].ToString() + "\t" + " '" + dr["barcode"].ToString() + "\t" + dr["comname"].ToString() + "\t" + dr["unit"].ToString() + "\t" + dr["standards"].ToString() + "\t" + dr["package"].ToString() + "\t" + dr["price"].ToString() + "\t" + dr["saleprice"].ToString() + "\t" + " " + dr["createdate"].ToString() + "\t" + " " + dr["makedate"].ToString() + "\t" + dr["flag"].ToString() + "\t" + dr["remark"].ToString());
}
}
if (this.drotype.SelectedIndex == 2)
{
sw.WriteLine("报价单号,供应商编号,供应商名称,编号,商品条码,商品名称,单位,规格,包装,供货价,建议售价,创建日期,审核日期,单据属性,备注");
foreach (DataRow dr in dt.Rows)
{
sw.WriteLine(" '" + dr["id"].ToString() + "," + " '" + dr["vendorid"].ToString() + "," + dr["vendorname"].ToString() + "," + dr["no"].ToString() + "," + " '" + dr["barcode"].ToString() + "," + dr["comname"].ToString() + "," + dr["unit"].ToString() + "," + dr["standards"].ToString() + "," + dr["package"].ToString() + "," + dr["price"].ToString() + "," + dr["saleprice"].ToString() + "," + " " + dr["createdate"].ToString() + "," + " " + dr["makedate"].ToString() + "," + dr["flag"].ToString() + "," + dr["remark"].ToString());
}
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name));
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(name);
Response.End(); --------------------编程问答-------------------- 哈哈。。漂亮姐姐~ --------------------编程问答-------------------- lz是汽车人...鉴定完毕... --------------------编程问答-------------------- 漂亮姐姐来了。嘿嘿 --------------------编程问答-------------------- ??? --------------------编程问答-------------------- 把excel对应列设置为文本格式，就没问题了 --------------------编程问答-------------------- 把excel对应列设置为文本格式，就没问题了 --------------------编程问答-------------------- 顶楼上 --------------------编程问答-------------------- 楼主这样写导出来的文件没什么格式的,我给你一个吧:
--------------------编程问答-------------------- 我上面的方法有给你解决科学记数法的问题的:
public static string ExportTable(DataSet ds)
{
string data = "";
//data = ds.DataSetName + "\n";
foreach (DataTable tb in ds.Tables)
{
//data += tb.TableName + "\n";
data += "<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">";
//写出列名
data += "<tr style=\"font-weight: bold; white-space: nowrap;\">";
foreach (DataColumn column in tb.Columns)
{
data += "<td>" + column.ColumnName + "</td>";
}
data += "</tr>";
//写出数据
foreach (DataRow row in tb.Rows)
{
data += "<tr>";
foreach (DataColumn column in tb.Columns)
{
if (column.ColumnName.Equals("证件编号") || column.ColumnName.Equals("报名编号"))
data += "<td style=\"vnd.ms-excel.numberformat:@\">" + row[column].ToString() + "</td>";
else
data += "<td>" + row[column].ToString() + "</td>";
}
data += "</tr>";
}
data += "</table>";
}
return data;
}
public static void ExportDsToXls(Page page, string sql)
{
ExportDsToXls(page, "FileName", sql);
}
public static void ExportDsToXls(Page page, string fileName, string sql)
{
DataSet ds = DBUtil.GetDataSet(sql);
if (ds != null) ExportDsToXls(page, fileName, ds);
}
public static void ExportDsToXls(Page page, DataSet ds)
{
ExportDsToXls(page, "FileName", ds);
}
public static void ExportDsToXls(Page page, string fileName, DataSet ds)
{
page.Response.Clear();
page.Response.Buffer = true;
page.Response.Charset = "GB2312";
//page.Response.Charset = "UTF-8";
page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + System.DateTime.Now.ToString("_yyMMdd_hhmm") + ".xls");
page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
page.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
page.EnableViewState = false;
page.Response.Write(ExportTable(ds));
page.Response.End();
}
//style="vnd.ms-excel.numberformat:@" 可以去除自动科学计数法的困扰
//输出为Table，能够最大限度的减少字段中数据对生成的文件格式的影响，在这里我没有处理数据中含有HTML标签的情况 在页面后台中，这样使用就可以了：
protected void lbtnToExcel_Click(object sender, EventArgs e)
{
string strWhere = BuildSearchWhereString();
string strOrder = this.hidOrderString.Value;
string sql = "SELECT 报名编号, 证件编号, 姓名, 考区考点, 报考类别, "
+ "行政区划名称 AS 行政区划, 单位名称 AS 工作单位, 毕业学校名称, 毕业专业名称 AS 毕业专业, 毕业年月, "
+ "通讯地址, 性别"
+ " from [VW报名]";
if (!string.IsNullOrEmpty(strWhere)) sql += " where " + strWhere;
if (!string.IsNullOrEmpty(strOrder)) sql += " order by " + strOrder;
else sql += " order by [报考类别]";
PageExport.ExportDsToXls(this.Page, "BaoMing", sql);
dataBind();
}
请注意: vnd.ms-excel.numberformat:@ 就是起这个作用的~... --------------------编程问答--------------------
同意 --------------------编程问答-------------------- 没看到美女过来啊 --------------------编程问答-------------------- 关注下 --------------------编程问答-------------------- 你可以导出前设置：
文本：vnd.ms-excel.numberformat:@
日期：vnd.ms-excel.numberformat:yyyy/mm/dd
数字：vnd.ms-excel.numberformat:#,##0.00
货币：vnd.ms-excel.numberformat:￥#,##0.00
百分比：vnd.ms-excel.numberformat: #0.00%
这里有一个孟老大给的例子，可以参考下：
http://dotnet.aspx.cc/article/8a4cbf47-b888-4832-3389-ed3a3a3c8aab/read.aspx
--------------------编程问答-------------------- 上面的格式你根据实际情况来设定的,并不是全部都要 --------------------编程问答-------------------- 我来看漂亮佳佳的
--------------------编程问答--------------------
冲漂亮姐姐来的..
--------------------编程问答--------------------
用com组件Excel.Range r1;
r1=yourexcel.get_Range(myexcel.Cells[i,n],myexcel.Cells[j,n]);
r1.NumberFormatLocal= "@";
参考
http://www.cnblogs.com/codingsilence/articles/1133003.html --------------------编程问答-------------------- 帮顶个， 吼吼 --------------------编程问答-------------------- 漂亮姐姐没来啊，哈哈 --------------------编程问答-------------------- 漂亮姐姐在哪 --------------------编程问答-------------------- 楼上是个漂亮姐姐 --------------------编程问答--------------------
说的挺好啊。你照着看看 --------------------编程问答--------------------
foreach (DataRow dr in dt.Rows)
{
sw.WriteLine(" '" + Convert.ToInt64(dr["id"]).ToString("E") + "," + " '" + Convert.ToInt32(dr["vendorid"]).ToString() + "," + dr["vendorname"].ToString() + "," + dr["no"].ToString() + "," + " '" + dr["barcode"].ToString() + "," + dr["comname"].ToString() + "," + dr["unit"].ToString() + "," + dr["standards"].ToString() + "," + dr["package"].ToString() + "," + dr["price"].ToString() + "," + dr["saleprice"].ToString() + "," + " " + dr["createdate"].ToString() + "," + " " + dr["makedate"].ToString() + "," + dr["flag"].ToString() + "," + dr["remark"].ToString());
}
--------------------编程问答-------------------- 这称呼叫的太恶心... --------------------编程问答-------------------- 嘿嘿~~！！！
顶顶更健康。。。
jiajia 你是man or woman？？？？
我感觉应该是佳佳哥哥或者弟弟吧 ^_^。。。 --------------------编程问答-------------------- 这个漂亮姐姐是个男的好不好！！ --------------------编程问答-------------------- LS2位，明确告诉你本人现在是女性。这号三个人在上。你们以后的帖子不会回了哦。你们也用不着我回能力都那么强。 --------------------编程问答--------------------
补充：.NET技术 , ASP.NET
