如何从VB.Net读取Excel单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2407293/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read Excel cell from VB.Net
提问by RedsDevils
How can I read specific cell from Excel file using OLEDB Connection with VB.NET?
如何使用带有 VB.NET 的 OLEDB 连接从 Excel 文件中读取特定单元格？
Can you show me sample code?
你能告诉我示例代码吗？
回答by HotTester
Try the following C# code:
尝试以下 C# 代码：
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection
MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & ExcelFilePath & "; " & _
"Extended Properties=Excel 8.0")
' Select the data from Sheet1 ([in-house$]) of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [in-house$]", MyConnection)
DS = New System.Data.DataSet
MyCommand.Fill(DS)
Dt = DS.Tables(0)
DataGrid1.DataSource = Dt
For particular cell try this (it will read cell D6). The point to note is that it is not using OLEDB connection rather it is directly accessing.
对于特定的单元格试试这个（它将读取单元格 D6）。需要注意的一点是它没有使用 OLEDB 连接，而是直接访问。
Namespace required using Microsoft.Office.Core;
使用 Microsoft.Office.Core所需的命名空间 ；
Add it by adding reference from COM to Microsoft Office 12.0 Object Library
通过添加从 COM 到Microsoft Office 12.0 对象库的引用来添加它
Dim oApp As New Excel.Application
Dim oWBa As Excel.Workbook = oApp.Workbooks.Open("c:\Test.XLS")
Dim oWS As Excel.Worksheet = DirectCast(oWBa.Worksheets(1),
Excel.Worksheet)
oApp.Visible = False
Dim oRng As Excel.Range
oRng = oWS.Range("D6")
MsgBox(oRng.Value)
回答by Joe Erickson
SpreadsheetGear for .NETis an Excel compatible spreadsheet component for .NET which you can use to get the formula, value, formatted text, etc... of any cell. Here is a simple example:
SpreadsheetGear for .NET是一个 Excel 兼容的 .NET 电子表格组件，您可以使用它来获取任何单元格的公式、值、格式化文本等。这是一个简单的例子：
using System;
using SpreadsheetGear;
namespace Program
{
class Program
{
static void Main(string[] args)
{
// Load a workbook from disk and get the first worksheet.
IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(@"C:\tmp\HelloWorld.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
// Get a reference to cell A1 and write the formatted value to the console.
IRange a1 = worksheet.Cells["A1"];
Console.WriteLine("A1={0}", a1.Text);
// Get a reference to B2 and write the formula / value / text to the console.
IRange b2 = worksheet.Cells[1, 1];
Console.WriteLine("B2 Formula={0}, Value={1}, Text={2}", b2.Formula, b2.Value, b2.Text);
}
}
}
You can see live samples hereor download the free trial hereif you want to try it yourself.
你可以看到现场的样品在这里或下载免费试用这里，如果你想自己尝试一下。
Disclaimer: I own SpreadsheetGear LLC
免责声明：我拥有 SpreadsheetGear LLC
回答by ketan italiya
try this c# code,
试试这个 c# 代码，
DimobjEXCELCon As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=EXCLE_FILE_PATH;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
ExcelConnection.Open()
Dim objQuery As String = "SELECT * FROM [Sheet1$]" 'get values from sheet1, here you can change your sheet name
Dim objCMD As OleDbCommand = New OleDbCommand(objQuery,objEXCELCon)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()
Dim szCON As String = "Connection string for database"
SQLconn.ConnectionString = szCON
SQLconn.Open()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLConn)
bulkCopy.DestinationTableName = "TableToWriteToInSQLSERVER"
Try
objDR = objCMD.ExecuteReader
bulCopy.WriteToServer(objDR)
objDR.Close()
SQLConn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
