100分求助：datagrid导出到excel后 excel中的数字无法正常显示(VB语言)

fcyfl 2005-05-14 07:34:16

代码如下：前台页面：
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm6.aspx.vb" Inherits="liuyan.WebForm6" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title id="mengxianhui"></title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout" style="FONT-SIZE:9pt">
<form id="Form1" method="post" runat="server">


<asp:datagrid id="Datagrid1" style="Z-INDEX: 102; LEFT: 16px; POSITION: absolute; TOP: 80px" runat="server"
CellPadding="4" BackColor="White" BorderWidth="1px" BorderStyle="None" BorderColor="#CC9966"
AutoGenerateColumns="False" Width="537px" PageSize="5" AllowSorting="True" Height="135px">
<SelectedItemStyle Font-Bold="True" ForeColor="#663399" BackColor="#FFCC66"></SelectedItemStyle>
<ItemStyle ForeColor="#330099" BackColor="White"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000"></HeaderStyle>
<FooterStyle ForeColor="#330099" BackColor="#FFFFCC"></FooterStyle>
<Columns>
<asp:BoundColumn DataField="uid" HeaderText="学号"></asp:BoundColumn>
<asp:BoundColumn DataField="name" HeaderText="姓名"></asp:BoundColumn>
<asp:BoundColumn DataField="project" HeaderText="选报项目"></asp:BoundColumn>
<asp:BoundColumn DataField="time_num" HeaderText="上课时间"></asp:BoundColumn>
<asp:BoundColumn DataField="class_num" HeaderText="所在班级"></asp:BoundColumn>
</Columns>
<PagerStyle NextPageText="下一页" PrevPageText="上一页" HorizontalAlign="Center" ForeColor="#330099"
BackColor="#FFFFCC"></PagerStyle>
</asp:datagrid>
 
 
 
<asp:Button id="ExportDataGrid2Excel" style="Z-INDEX: 101; LEFT: 24px; POSITION: absolute; TOP: 24px"
runat="server" Text="ExportDataGrid2Excel"></asp:Button>
</form>
</body>
</HTML>

后台VB代码：要添加Excel引用的
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports OWC
Public Class WebForm6
Inherits System.Web.UI.Page
Protected WithEvents ExportDataGrid2Excel As System.Web.UI.WebControls.Button
Private cnn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Test.mdb"))
Protected WithEvents Datagrid1 As System.Web.UI.WebControls.DataGrid
Private sql As OleDbCommand = New OleDbCommand("SELECT * FROM Student_project", cnn)

#Region " Web 窗体设计器生成的代码 "

'该调用是 Web 窗体设计器所必需的。
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub

'注意: 以下占位符声明是 Web 窗体设计器所必需的。
'不要删除或移动它。
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: 此方法调用是 Web 窗体设计器所必需的
'不要使用代码编辑器修改它。
InitializeComponent()
End Sub

#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cnn.Open()
Dim reader As OleDbDataReader = sql.ExecuteReader()
Me.Datagrid1.DataSource = reader
Me.Datagrid1.DataBind()
reader.Close()
cnn.Close()
End Sub

Private Sub WriteDataGrid2Excel2()
Dim xlsheet As New SpreadsheetClass
Dim i As Integer = 0
Dim j As Integer = 0
'Response.End()
' 输出标题
Dim oItem As DataGridColumn
For Each oItem In DataGrid1.Columns
xlsheet.ActiveSheet.Cells(1, i + 1) = oItem.HeaderText
'设置格式
xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Bold = True
xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Color = "red"
i = i + 1
Next
Dim numbercols As Integer = Datagrid1.Items.Item(0).Cells.Count
' 输出字段内容
For j = 0 To DataGrid1.Items.Count - 1
For i = 0 To numbercols - 1
xlsheet.Range(xlsheet.Cells(2, 2), xlsheet.Cells(j + 2, i + 1)).Font.Color = "blue"
xlsheet.Range(xlsheet.Cells(2, 1), xlsheet.Cells(j + 2, i + 1)).AutoFitColumns()
xlsheet.ActiveSheet.Cells(j + 2, i + 1) = DataGrid1.Items.Item(j).Cells(i).Text.Replace(" ", " ")
Next
Next
Try
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\Images\" + "bbb.xls", OWC.SheetExportActionEnum.ssExportActionNone)
Catch e As System.Runtime.InteropServices.COMException
Response.Write("错误：" + e.Message)
End Try
End Sub

Private Sub ExportDataGrid2Excel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExportDataGrid2Excel.Click
Me.WriteDataGrid2Excel2()
End Sub
End Class

...全文

141 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

fcyfl 2005-05-14

打赏
举报

061302206 变成了 6E+06
0613022 变成了 ####

smx717616 你好谢谢你的提醒，我再试试看，你看我上面说的情况如你所说的情况吗？

smx717616 2005-05-14

打赏
举报

(1)要么都设置为文字列
'' 定义对象
oExcel = New Excel.Application
'' 选择所有
oExcel.Cells.Select()
'' 设置为文本
oExcel.Selection.NumberFormatLocal = "@" -----这个是你想要的

(2)要么加单引号（如：'20 )

smx717616 2005-05-14