ADO.NET读取Excel数据
最近的系统需要在SQL数据库与Execl之间互导数据，找了一些资料，ADO.NET可以使用使用Microsoft.Jet.OleDb访问访问Excel，网上已经有很多类似的资源，俺整理了两种方法，第一种方法可以从Excel中批量导入数据到SQL中，实现如下：
' 连接字符串
Dim xlsPath As String = Server.MapPath("~/app_data/ExcelFile.xls") ' 绝对物理路径
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlsPath & "; Extended Properties=Excel 8.0;"
' 查询语句
Dim sql As String = "SELECT * FROM [Sheet1$]"
Dim ds As DataSet = New DataSet()
Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, connStr)
da.Fill(ds) ' 填充DataSet
' 在这里对DataSet中的数据进行操作
' 输出，绑定数据
GridView1.DataSource = ds.Tables(0)
GridView1.DataBind()
Dim xlsPath As String = Server.MapPath("~/app_data/ExcelFile.xls") ' 绝对物理路径
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlsPath & "; Extended Properties=Excel 8.0;"
' 查询语句
Dim sql As String = "SELECT * FROM [Sheet1$]"
Dim ds As DataSet = New DataSet()
Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, connStr)
da.Fill(ds) ' 填充DataSet
' 在这里对DataSet中的数据进行操作
' 输出，绑定数据
GridView1.DataSource = ds.Tables(0)
GridView1.DataBind()
俺只整理了绑定到GridView中的代码。
第二种方法针对自定义Excel数据表的导入，此方法在读取Excel文件时系统自动会打开一个Excel.exe进程，使用myExcel.Workbooks.Close无法关闭，俺也整理了如何关闭Excel.exe进程的代码，HTML代码如下：
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Excel.aspx.vb" Inherits="_Excel" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>读取Excel数据</title>
</head>
<body>
<form id="form1" runat="server">
<div>
第<asp:TextBox ID="TextBox3" runat="server" Width="60px" Text="1" />工作表
第<asp:TextBox ID="TextBox1" runat="server" Width="60px" Text="1" />行
第<asp:TextBox ID="TextBox2" runat="server" Width="60px" Text="1" />列<br />
Excel数据:<input id="File1" runat="server" name="File1" size="30" type="file" />
<asp:Button ID="BtnRExcel" runat="server" Text="读取Excel的某行某列" /></div>
</form>
</body>
</html>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>读取Excel数据</title>
</head>
<body>
<form id="form1" runat="server">
<div>
第<asp:TextBox ID="TextBox3" runat="server" Width="60px" Text="1" />工作表
第<asp:TextBox ID="TextBox1" runat="server" Width="60px" Text="1" />行
第<asp:TextBox ID="TextBox2" runat="server" Width="60px" Text="1" />列<br />
Excel数据:<input id="File1" runat="server" name="File1" size="30" type="file" />
<asp:Button ID="BtnRExcel" runat="server" Text="读取Excel的某行某列" /></div>
</form>
</body>
</html>
VB.NET代码：
Imports Microsoft.Office.Interop
Partial Class _Excel
Inherits System.Web.UI.Page
Protected Sub BtnRExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnRExcel.Click
Dim strPath As String = File1.PostedFile.FileName
If strPath = "" Then
Me.Response.Write("请选择要导入的Excel数据文件！")
Else
Dim excelFilePath As String = strPath
Dim myExcel As Excel.Application = New Excel.ApplicationClass() '创建一个Excel对象
Dim oMissing As Object = System.Reflection.Missing.Value
myExcel.Application.Workbooks.Open(excelFilePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing)
Dim myBook As Excel.Workbook = myExcel.Workbooks(1) '定义一个工作表组
Dim mySheet As Excel.Worksheet = CType(myBook.Worksheets(CInt(TextBox3.Text)), Excel.Worksheet) '定义一个工作表
Dim r As Excel.Range = CType(mySheet.Cells(CInt(TextBox1.Text), CInt(TextBox2.Text)), Excel.Range)
Me.Response.Write("Excel中第" & TextBox1.Text & "行 第" & TextBox2.Text & "列的值：" & (r.Value).ToString)
ExcelGC(myExcel, myBook, mySheet, r)
End If
End Sub
'关闭Excel进程
Private Shared Sub ExcelGC(ByRef myExcel As Excel.Application, ByRef myBook As Excel.Workbook, ByRef mySheet As Excel.Worksheet, ByRef r As Excel.Range)
myBook.Close(False, Type.Missing, Type.Missing)
myExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(r)
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel)
System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook)
mySheet = Nothing
myBook = Nothing
myExcel = Nothing
r = Nothing
GC.Collect()
End Sub
End Class
Partial Class _Excel
Inherits System.Web.UI.Page
Protected Sub BtnRExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnRExcel.Click
Dim strPath As String = File1.PostedFile.FileName
If strPath = "" Then
Me.Response.Write("请选择要导入的Excel数据文件！")
Else
Dim excelFilePath As String = strPath
Dim myExcel As Excel.Application = New Excel.ApplicationClass() '创建一个Excel对象
Dim oMissing As Object = System.Reflection.Missing.Value
myExcel.Application.Workbooks.Open(excelFilePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing)
Dim myBook As Excel.Workbook = myExcel.Workbooks(1) '定义一个工作表组
Dim mySheet As Excel.Worksheet = CType(myBook.Worksheets(CInt(TextBox3.Text)), Excel.Worksheet) '定义一个工作表
Dim r As Excel.Range = CType(mySheet.Cells(CInt(TextBox1.Text), CInt(TextBox2.Text)), Excel.Range)
Me.Response.Write("Excel中第" & TextBox1.Text & "行 第" & TextBox2.Text & "列的值：" & (r.Value).ToString)
ExcelGC(myExcel, myBook, mySheet, r)
End If
End Sub
'关闭Excel进程
Private Shared Sub ExcelGC(ByRef myExcel As Excel.Application, ByRef myBook As Excel.Workbook, ByRef mySheet As Excel.Worksheet, ByRef r As Excel.Range)
myBook.Close(False, Type.Missing, Type.Missing)
myExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(r)
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel)
System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook)
mySheet = Nothing
myBook = Nothing
myExcel = Nothing
r = Nothing
GC.Collect()
End Sub
End Class
实现如图:
浙公网安备 33010602011771号