הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Tuesday, January 6, 2009 9:45 AM
Hello,
I want to try read an excel file with VB NET application.
But I have to know how many rows in that worksheet, so how to code it?
and how to read a value by a specific row and column?
Thanks
All replies (6)
Tuesday, January 6, 2009 12:26 PM ✅Answered | 1 vote
I am new to .net programming so please don't mind if does not help you
Dim ex As New Excel.Application
Dim wb As Excel.Workbook
wb = ex.Workbooks.open("d:\temp.XLS")
ex.Visible = True
Dim ws As New Excel.Worksheet
ws = wb.ActiveSheet
' to get used rows
MessageBox.Show(ws.UsedRange.Rows.Count)
' to get text of any particular cell
MessageBox.Show(ws.Range("A1").Text)
surinder singh
Wednesday, January 7, 2009 8:12 AM
Hello, thanks for the help....
Anyway does anybody know how to solve this?
We have 1 Excel file which will be used by 2 applications.
1st application will store data into that excel file and then 2nd application will retrieve data from excel file too.
1st application is from vendor so we develop for the 2nd application.
But I worry, I will get error when retrieving data.
Cause, when I running the application (automatically retrieve data by timer) and then I try to open that excel file,
that file is read-only so we can't update it, so I worry my vendor application can't update excel file when our application is retrieving data.
= So my planning is use OLEDB to open an excel file = is it okay?
But how I know that excel file has been updated or not? I want the latest updated data then retrieve it to our database.
Wednesday, January 7, 2009 8:36 AM
Office Binary File Formats (including Excel) are based on Compound File Binary format specification. I know of one open-source CFB implementation in DotNET currently underway, but none anywhere near complete. Same with all Office spec implementations. Open File Format specification forums can be found elsewhere in MSDN (parent-level forum list to this one).
There are a handful of for-sale CFB and Office binary interface SDKs, but they're pricey and as far as I know aren't very well distributed.
CFB is an OLE format, but to my knowledge OLEDB is not the scratch for this itch - there are also direct Windows API calls available via OLE32.DLL to access CFB format files, but this does not include Excel format spec.
Your best option then is to either automate Excel via the Microsoft Office Interop plus the Microsoft Excel Interop components that you can add as COM References in any Visual Studio Project, or else switch from standalone app straight to Excel Addin using either Interop and Extensibility components available with Office or a commercial SDK for Office Addins. I can recommend one Office Interop SDK pretty highly, but I don't know if the MSDN EULA allows me to reference such things by name.
Thursday, January 15, 2009 2:04 AM
Hello,
I am facing a problem again :)
I have a worksheet 'Text (Tab Delimited)(*.txt)', and its name is abc.xls.
And another file is .csv , I can't use OLE DB 4.0 to call it.
when I used an OLEDB 4.0, I can't get that sheet, I got error unexpected table format.
is it different from usual worksheet?
if yes, how I call it and read its cell value? Thanks...
Thursday, January 15, 2009 3:58 AM
This might help you
Read a csv file selected by openfiledialog with oledb connection
Dim ofd As New OpenFileDialog
ofd.Filter = "csv files|*.csv"
If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim fullpath As String = ofd.FileName
Dim pathonly As String = fullpath.Replace(ofd.SafeFileName, "")
Dim dt As New DataTable
Dim mySelectQuery As String = "SELECT * FROM " + ofd.SafeFileName
**Dim myconnection As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pathonly & "\Extended Properties=""text;HDR=Yes;FMT=CSVDelimited""")
**
Dim dsCmd As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(mySelectQuery, myconnection)
dsCmd.Fill(dt)
Me.DataGridView1.DataSource = dt
myconnection.Close()
End If
Also, take a look at spreadsheetgear for .net much better for many cases than excel automation.
Jeff - www.srsoft.us
FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us
Thursday, January 15, 2009 5:08 AM
Hi, I found an error at here..
'dsCmd.Fill(dt)
Error is 'Syntax error in FROM clause.'
I can't open it yet..
edited: If I set like this, the error is 'C:\abc.CSV\ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
Dim fullpath As String = "C:\abc.CSV"
Dim dt As New DataTable
Dim mySelectQuery As String = "SELECT * FROM [Sheet$]" '+ ofd.SafeFileName
Dim myconnection As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fullpath & "\Extended Properties=""text;HDR=Yes;FMT=CSVDelimited""")
Dim dsCmd As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(mySelectQuery, myconnection)
dsCmd.Fill(dt)
Me.DataGridView1.DataSource = dt
myconnection.Close()