Hi,
As my post's title indicates, I wanna read all cells of all sheets/pages in my excel file and just show the text content of each cell.
I have written my code but have 2 problems:
- -
Dim ExcelConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Excel.xls;Extended Properties=Excel 8.0;")
-
ExcelConnection.Open()
-
Dim MyCommands As New OleDbCommand("SELECT * FROM [Sheet1$]", ExcelConnection)
-
Dim MyReader As OleDbDataReader = MyCommands.ExecuteReader
-
While MyReader.Read()
-
'How should I read cells here?
-
End While
-
MyReader.Close()
-
ExcelConnection.Close()
-
-
1. How can I read cells inside the While?
2. My select command only selects Sheet1, how can I select ALL sheets?
Thank you guys/gals, as I am not expert in database.
2 3065
OK, I found a way for you to get the "schema" of the spreadsheed. Basically, this method takes your connection string, and returns an array of strings that is the list of your tables (sheets).
It's in C#, but you should be able to translate it. - static string[] GetSchemaTable(string connectionString)
-
{
-
using (OleDbConnection connection = new
-
OleDbConnection(connectionString))
-
{
-
connection.Open();
-
DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
-
List<string> tableList = new List<string>();
-
foreach (DataRow dr in schemaTable.Rows)
-
{
-
if (dr[3] as string == "TABLE")
-
tableList.Add(dr[2] as string);
-
}
-
return tableList.ToArray();
-
}
-
}
Now that you have all the table names, you could either do a UNION statement (bad idea if the sheets don't have exactly the same headers) or you could just do a loop, selecting all from each table.
As to the reading the cells, take a look at these articles:
Just remember that anything that is done with the SqlDataReader can be done with the OleDbReader.
VB Code of iA's example -
Function GetSchemaTable(ByVal connectionString As String) As String()
-
Dim connection As New OleDb.OleDbConnection(connectionString)
-
-
connection.Open()
-
Dim schemaTable As DataTable = connection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
-
Dim tableList As New List(Of String)
-
For Each dr As DataRow In schemaTable.Rows
-
If (String.Compare(CType(dr(3), String), "TABLE", True) = 0) Then
-
-
tableList.Add(CType(dr(2), String))
-
End If
-
Next
-
Return tableList.ToArray()
-
End Function
Sign in to post your reply or Sign up for a free account.
Similar topics
by: sarunnio |
last post by:
How do I read an excel file to display the worksheet on the web.
Can I make use of some excel automation to programmatically
manipulate the excel file via the web browser.
Thanks in advance.
...
|
by: Esmail Bonakarian |
last post by:
Greetings all,
What is the best way to access specific records in an Excel file?
I have an Excel file, I want to randomly and repeatedly (maybe around up to
50 times) draw some rows of data...
|
by: Seok Bee |
last post by:
Dear Experts,
I have created a script to extract the Event Logs from the system into an
excel sheet. The logs are separated into 2 worksheets (Application Log and
System Log). After this excel...
|
by: A_StClaire_ |
last post by:
hi everyone,
is this possible? I know there are third party tools such as
Aspose.Cells for .NET, but do you know of any cost-free alternatives?
should mention using OLEDB is not a preferred...
|
by: aotemp |
last post by:
Hi,
Im having a reaaally hard time with something... Im trying to read a
cell of data into a String variable. It seems like such a simple task
too... Get the excel spreadsheet, get the...
|
by: jagguy |
last post by:
Hi,
i cant read in data from excel 97 using vb.net 2003.
I get an exception error .
all i want to do is read in values from excel initially. I want to just test it using msgbox.
Dim XL...
|
by: ielamrani |
last post by:
Hi,
I am getting this error when I try to export to an excel sheet. When I click on a button to export the first time it's fine, I rename the exported excel sheet and I try to export it again and I...
|
by: keirnus |
last post by:
hello,
been searching for a solution on this but all i get are advices for importing data from excel directly to MS Access DB.
my case here is different...the sheet in Excel file is not ready...
|
by: atksamy |
last post by:
Hi,
I am trying to write some data into excel sheets using c#.
so far i have written the following code. But i am not able to proceed further
...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |