首页新闻找找看学习计划

关于导出Excel的问题

0
[已关闭问题] 关闭于 2010-11-10 13:12

我在做导出Excel的时候,代码没问题,但是导出的时候遇到了这样一个问题。比较有一个数据为“000123”,但是导出结果显示为“123”,我要显示成广西形式,如何实现呢,不改变我原来的代码,附上原来的代码,大家看一下。

1 public static bool Export(int iUserID, DataTable dataTable, bool isExportFirstCol, Page page)
2 {
3 try
4 {
5 if (dataTable != null)
6 {
7 if (dataTable.Rows.Count > 0)
8 {
9 string strSavePath = page.MapPath("~/") + "Excel\\" + "user_" + iUserID.ToString();
10 string strSaveFileName = strSavePath + "\\export.xls";
11 string strDownloadFileName = "~/Excel/" + "user_" + iUserID.ToString() + "/export.xls";
12
13
14 if (!Directory.Exists(strSavePath))
15 {
16 Directory.CreateDirectory(strSavePath);
17 System.IO.DirectoryInfo dInfo = new System.IO.DirectoryInfo(strSavePath);
18 System.Security.AccessControl.DirectorySecurity dSecurity = new System.Security.AccessControl.DirectorySecurity();
19 dSecurity.AddAccessRule(new System.Security.AccessControl.FileSystemAccessRule("Everyone", System.Security.AccessControl.FileSystemRights.FullControl, System.Security.AccessControl.AccessControlType.Allow));
20 dInfo.SetAccessControl(dSecurity);
21 }
22
23 StreamWriter w = new StreamWriter(strSaveFileName, false, System.Text.Encoding.Default);
24
25 //表头列
26  
27 DataColumn dc = new DataColumn();
28 for (int i = 0; i < dataTable.Columns.Count; i++)
29 {
30 dc = dataTable.Columns[i];
31
32 if (!isExportFirstCol && i == 0)
33 {
34 continue;
35 }
36 else
37 {
38 w.Write(dc.Caption);
39 w.Write('\t');
40 }
41 }
42
43 //表头换行
44 w.Write("\r\n");
45
46 //列出记录
47 string strTemp = System.String.Empty;
48 foreach (DataRow dr in dataTable.Rows)
49 {
50 for (int i = 0; i < dataTable.Columns.Count; i++)
51 {
52
53 if (!isExportFirstCol && i == 0)
54 {
55 continue;
56 }
57 else
58 {
59 strTemp = System.Text.RegularExpressions.Regex.Replace(dr[i].ToString().Trim(), "<[^>]*>", "");
60 if (strTemp != null && strTemp != "NULL")
61 {
62 w.Write(strTemp);
63 }
64 else
65 {
66 w.Write("");
67 }
68
69 w.Write('\t');
70 }
71 }
72 //增加一条记录内容后换行
73 w.Write("\r\n");
74 }
75
76 w.Flush();
77 w.Close();
78
79 page.Response.Redirect(strDownloadFileName);
80 }
81 }
82 return true;
83 }
84 catch (Exception ex)
85 {
86 string a = ex.Message;
87 return false;
88 }
89
90 }

 

Joe_true的主页 Joe_true | 初学一级 | 园豆:98
提问于:2010-10-26 15:28
< >
分享
所有回答(6)
0

用string.Format("{0:6D}",i)

Launcher | 园豆:45040 (高人七级) | 2010-10-26 15:34
不行啊老大
支持(0) 反对(0) Joe_true | 园豆:98 (初学一级) | 2010-10-26 15:44
@Joe_true:打开excel,然后设置单元格格式,在数字标签中选择"自定义",然后在"类型"中输入 000000 就OK了.
支持(0) 反对(0) Launcher | 园豆:45040 (高人七级) | 2010-10-26 16:17
0

是否是保留前面的三个0,好像是excel问题,不是代码问题,你要在excel上设置:把要输入的单元定义为格式→单元格→数字→文本。不过这样的话每用到的时候都要预先设置,很麻烦的。

取巧的方法是第一位加个单引号“‘”什么的,在引号后面跟上你的数字。

萧鼎 | 园豆:106 (初学一级) | 2010-10-26 15:51
0

使用Open Office XML导出excel,设置格式为字符串

iImax | 园豆:3138 (老鸟四级) | 2010-10-26 15:52
0

转换特殊的列,示例:

public static void DataSetToExcel(DataSet oDS, HttpResponse response, string fileName)
{
if (oDS == null || oDS.Tables[0] == null || oDS.Tables[0].Rows.Count == 0) { return; }
response.Clear();
//Encoding pageEncode = Encoding.GetEncoding(PageEncode);
response.Charset = "UTF-8";
response.ContentType
= "application/vnd-excel";//"application/vnd.ms-excel";
//Response.ContentType = "application/x-octet-stream";//"application/vnd.ms-excel";
//Response.ContentType = "text/csv";//"application/vnd.ms-excel";
response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
System.IO.StringWriter oSW
= new System.IO.StringWriter();
HtmlTextWriter oHW
= new HtmlTextWriter(oSW);

System.Web.UI.WebControls.GridView gv
= new GridView();
gv.RowDataBound
+= new System.Web.UI.WebControls.GridViewRowEventHandler(gv_ItemDataBound); //绑定格式转换方法
gv.DataSource = oDS.Tables[0].DefaultView;
gv.DataBind();
gv.RenderControl(oHW);

response.Write(oSW.ToString());

response.Flush();
response.Close();
}
/// <summary>
/// 文本: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%
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private static void gv_ItemDataBound(object sender, System.Web.UI.WebControls.GridViewRowEventArgs e)
{
if (e.Row.RowIndex > -1)
{
e.Row.Cells[
4].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); //i表示列号,格式类型见上
e.Row.Cells[12].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); //i表示列号,格式类型见上
}
}

 

邀月 | 园豆:25318 (高人七级) | 2010-10-26 16:17
0

用宏跟踪一下,查看宏代码,再转换一下。涉及到特殊设置我都是这么做的。

大河 | 园豆:215 (菜鸟二级) | 2010-10-26 17:05
0

Excel 会自动做类型检查的,他会认为你的那个列的类型是number,而对于number,前面的0是被忽略的,如果想要导出的结果是0000123的话,你应该把导出的结果写成‘000123,在000123前面加上’单引号。

ad-hoc | 园豆:145 (初学一级) | 2010-10-27 20:25
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册