把数据从页面导出到EXCEL时发现数据格式不对

heartflying201 2007-01-26 03:15:34

Private Function ConvertDtToTDF(ByVal dt As DataTable) As String
Dim dr As DataRow, ary() As Object, i As Integer
Dim iCol As Integer

' 输出列标题
For iCol = 0 To dt.Columns.Count - 1
Response.Write(dt.Columns(iCol).ToString & vbTab)
Next
Response.Write(vbCrLf)

'输出数据
For Each dr In dt.Rows
ary = dr.ItemArray
For i = 0 To UBound(ary)
Response.Write(ary(i).ToString & vbTab)
Next
Response.Write(vbCrLf)
Next

End Function

Private Sub toExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles del.Click
Response.Clear()
Response.Buffer = True
Response.Charset = "GB2312"
Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls")
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312")
'设置输出流为简体中文
Response.ContentType = "application/ms-excel"
'设置输出文件类型为excel文件。
Me.EnableViewState = False
Response.Write(ConvertDtToTDF(mytable))
Response.End()
End Sub

在datagrid绑定时小数位都正常显示，但是导出到excel后发现小数位都丢了，不知道怎么设置导出的格式？

...全文

1407 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

heartflying201 2007-01-26

打赏
举报

to cpp2017(慕白兄)
我用了孟子E章的：http://dotnet.aspx.cc/article/8a4cbf47-b888-4832-3389-ed3a3a3c8aab/read.aspx 这个和你提供的是一样的
试过之后，在excel中发现，如果是17.1确实变成了17.1,但是18.0却还是18
这个问题还是没有彻底的解决。

你看看导出的html是不是这种格式.：
<td style='height:14.25pt;mso-number-format:0\.00
</td>
在DataGrid的ItemDataBound中设置
e.Item.Cells[4].Style.Add("mso-number-format","0\.00") 也不行，郁闷

cpp2017 2007-01-26

打赏
举报

你看看导出的html是不是这种格式. <td style='height:14.25pt;mso-number-format:0\.00' >12.20 </td>

heartflying201 2007-01-26

打赏
举报

在DataGrid的ItemDataBound中.

如果第三列是数字列:

e.Item.Cells[2].Style.Add("mso-number-format","0\.00");

然后把整个datagrid导出.

---------------------

加上了导出的excel中还是显示为整数阿

yiyoo 2007-01-26

打赏
举报

学习中``

cpp2017 2007-01-26

打赏
举报

protected void ExportExcel(DataGrid dtgList, string strFileName) { DataTable dt = (DataTable)this.Session["GridToExcel"]; if (dt==null) return; Response.Clear(); Response.Buffer = true; Response.Charset = "gb2312"; Response.AppendHeader("Content-Disposition", "online; filename=" + this.GetFileName(strFileName) + ".xls"); Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); Response.ContentType = "application/ms-excel"; EnableViewState = false; System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("zh-CN",true); System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); dtgList.AllowPaging = false; // dtgList.DataSource = dt; // dtgList.DataBind(); dtgList.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.Buffer = false; Response.End(); }

cpp2017 2007-01-26