用以下语句调Excel表到DataGrid中,为什么不能调入数字,但字符却能调入.把数字栏调为文本,但也不行,为什么?
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=abc.xls;Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet,"mytable");
DataGrid3.DataSource = myDataSet.Tables["mytable"];
DataGrid3.DataBind();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=abc.xls;Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet,"mytable");
DataGrid3.DataSource = myDataSet.Tables["mytable"];
DataGrid3.DataBind();
解决方案 »
- C#用户登录验证能获取库中的名和密码,但不管密码对错都不能登录成功,新手望高手指教修改具体代码和位置
- 虚拟主机上是.net1.4322版本.我开发用的是.net2.0版本,能否正常浏览? 等待....
- 我想学习ASP.NET,希望大家能介绍几本好书,或网上的书籍!
- 请问
- 如何用C#操纵IIS创建应用程序?
- fckeditor无法正常显示
- 请看看这个,给我个思路,谢谢
- 急:谁能告诉我:办界面之间怎么样才能实现单值或者多值传递呀?用ASP.NET代码来实现?
- 为什么用ajax,总会弹出对话框 request timed out 之类如何解决
- 救命啊!一个用户控件和iframe的问题。
- 关于IIS和FileSystem的问题。
- 让页面所有文本类型控件状态无效asp.net(c#)
Inherits="aspxWeb.mengxianhui.com.DataGridToExcel"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title id="mengxianhui" runat="server"></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:Label id="Label1" runat="server"></asp:Label>
<asp:TextBox ID="xlfile" Runat="server"></asp:TextBox>
<br>
<br>
<asp:Button ID="ExportDataBase2Excel" Runat="server" />
<asp:Button ID="ExportDataGrid2Excel" Runat="server" />
<br>
<asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False" BorderColor="#CC9966"
BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="4">
<ItemStyle ForeColor="#330099" BackColor="White"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000"></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="Title"></asp:BoundColumn>
<asp:BoundColumn DataField="Author"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
</form>
</body>
</HTML>
Imports System.Data
Imports System.Data.OleDb
Imports OWCPublic Class DataGridToExcel
Inherits System.Web.UI.Page
Protected WithEvents xlfile As System.Web.UI.WebControls.TextBox
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents ExportDataGrid2Excel As System.Web.UI.WebControls.Button
Protected WithEvents ExportDataBase2Excel As System.Web.UI.WebControls.Button
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected mengxianhui As New HtmlGenericControl() Private cnn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="_
+ Server.MapPath("Test.mdb"))
Private sql As OleDbCommand = New OleDbCommand("SELECT TOP 50 Title,Author FROM Document", cnn)#Region " Web Form Designer Generated Code " 'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() End Sub Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub#End Region Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Load
Label1.Text = "请输入要保存得文件名字:"
ExportDataGrid2Excel.Text = "由DataGrid生成Excel文件"
ExportDataBase2Excel.Text = "数据库直接生成Excel文件"
DataGrid1.Columns(0).HeaderStyle.HorizontalAlign = HorizontalAlign.Center
DataGrid1.Columns(0).HeaderText = "文章名称"
DataGrid1.Columns(1).HeaderText = "作者"
DataGrid1.Columns(0).HeaderStyle.Font.Bold = True
DataGrid1.Style.Add("font-size", "9pt")
mengxianhui.InnerText = "【孟宪会之精彩世界】- 将DataGrid输出到Excel文件"
Me.BindDataGrid()
End Sub Private Sub BindDataGrid()
cnn.Open()
Dim reader As OleDbDataReader = sql.ExecuteReader()
Me.DataGrid1.DataSource = reader
Me.DataGrid1.DataBind()
reader.Close()
cnn.Close()
End Sub Private Sub WriteDataGrid2Excel()
Dim xlsheet As New SpreadsheetClass()
cnn.Open()
Dim reader As OleDbDataReader = Me.sql.ExecuteReader()
Dim numbercols As Integer = reader.FieldCount
Dim row As Integer = 2
Dim i As Integer = 0
' 输出标题
For i = 0 To numbercols - 1
xlsheet.ActiveSheet.Cells(1, i + 1) = reader.GetName(i).ToString()
Next ' 输出字段内容
While (reader.Read())
For i = 0 To numbercols - 1
xlsheet.ActiveSheet.Cells(row, i + 1) = reader.GetValue(i).ToString()
Next
row = row + 1
End While
reader.Close()
cnn.Close()
Try
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\Images\" + Me.xlfile.Text,_
OWC.SheetExportActionEnum.ssExportActionNone)
Catch e As System.Runtime.InteropServices.COMException
Response.Write("错误:" + e.Message)
End Try
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.ActiveSheet.Range(xlsheet.ActiveSheet.Cells(1, 1),_
xlsheet.ActiveSheet.Cells(1, i + 1)).Font.Bold = True
'设置格式
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("A2:B14").WrapText = True
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\" + Me.xlfile.Text,_
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 Object,_
ByVal e As System.EventArgs) Handles ExportDataGrid2Excel.Click
If (Me.xlfile.Text.Trim() <> "") Then
Me.WriteDataGrid2Excel2()
End If
End Sub Private Sub ExportDataBase2Excel_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ExportDataBase2Excel.Click
If (Me.xlfile.Text.Trim() <> "") Then
Me.WriteDataGrid2Excel()
End If
End SubEnd Class
SELECT a1,b1,c1 --excel中的列名,可以跟数据库中的不同
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\EXCEL\temp.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]
难道要i=-1才是第一行?for (int i=0;i<DataGrid3.Items.Count;i++)
{
string a = DataGrid3.Items[i].Cells[0].Text.ToString();
...}