-
Dec 23rd, 2010, 05:43 AM
#1
Read Excel file except fist 14 rows
Hi,
I need to read an Excel file (.xls, 2003 format). The data in the excel file is composed of two parts:
- The first 14 rows consist of all kinds of stuff, buttons, merged columns, stuff I don't need. You can see this as a header for the worksheet.
- The next row (number 15 viewed in Excel) contains column header names
- All remaining rows contain the data I need to read.
I have tried reading this file using two different methods. The first method is simply using OleDb:
vb.net Code:
Private Function GetDataTable(ByVal sheetName As String) Dim dt As DataTable = Nothing Dim connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR={1};IMEX={2}"";", _ Me.FilePath, _ "No", _ "1") Using conn As New OleDbConnection(connString) Using cmd As New OleDbCommand() cmd.Connection = conn cmd.CommandType = CommandType.Text cmd.CommandText = String.Format("select * from [{0}$]", sheetName.Trim()) conn.Open() dt = New DataTable dt.Load(cmd.ExecuteReader) conn.Close() End Using End Using Return dt End Function
I have tried fiddling with the HDR and IMEX settings, but the result is always the same. I keep getting an error that says "Not a legal OleAut date", which is supposed to mean it cannot convert a date to .NET DateTime format.
I am sure this error occurs because it is trying to read all the stuff in the first 14 rows but fails, because it does not contain simple data.
So my first question is:
How can I tell it to only start reading after row 14?
The file comes from a third party so I have no control over the contents. I could make a copy of the file and delete the first 14 columns, then try to read that copy instead, but the only way I think that is possible (I haven't tried this though) is via Office Interop, which brings its own problems.
That brings me to the second method I tried to read the file: using Office Interop.
I simply create a new Excel.Application, open the Workbook (excel file), find the correct worksheet (there's multiple sheets that I have to read) and read the data from the cells directly.
This works. I can read the data. But there is a very big problem: this method seems to actually open an instance of Excel and the workbook, and after it is closed it actually closes this instance again. When that happens, Excel tells the user that the file has changed and asks whether it needs to be saved. It seems that merely opening the file changes something (because my code is only reading, not changing, and the same happens when I just open the file directly in windows), and when it is closed a dialog pops up asking for user input.
This is a big problem, because my code does not continue until the workbook is closed (workbook.Close method). And the user has to close the workbook. This application is supposed to be run as a service, and it must run once per day, and I cannot guarantee that a user will be around every day to close the Excel dialog...
EDIT
See post 2 for update
Thanks for any help!
Last edited by NickThissen; Dec 23rd, 2010 at 06:17 AM.
-
Dec 23rd, 2010, 06:20 AM
#2
Re: Read Excel file except fist 14 rows
Ok, I managed to supress the user input dialog (Excel.Application.DisplayAlerts = False).
So, I can now use office interop to read the excel file.
However, while I found this solution, I also found that Microsoft does not recommend using interop in an environment without a user, like I have:
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when run in this environment.
Besides the technical problems, you must also consider the feasibility of such a design with respect to licensing. Current licensing guidelines prevent Office Applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by the End User License Agreement (EULA).
So... Yeah, I don't really want to use this method!
So I still want a solution to my first question: how do I read the excel files but skip the first 14 lines so it does not error on them..?
As I said, I could copy the file and delete the first 14 rows, but for that I again need office automation, and we're back to my current problem...
-
Dec 23rd, 2010, 09:21 AM
#3
Re: Read Excel file except fist 14 rows
Have you tried any third party products? I have used the Aspose suite for Word documents on a server and that worked great for me. They have an excel version called Aspose.Cells.
It basically gives you access to all of the content of the file without needing the Microsoft product installed on the server. They are relatively inexpensive for a business to purchase, weighing it against the amount of time it may take you to find a workaround.
-
Dec 23rd, 2010, 09:23 AM
#4
Re: Read Excel file except fist 14 rows
That sounds good, thanks, but I can't do more than suggest it at this point. I am still looking for a different solution in the mean time
-
Dec 23rd, 2010, 09:27 AM
#5
Re: Read Excel file except fist 14 rows
Or you have gemboxsoftware (which I am investigating myself as it happens) which are far far cheaper if you have to release this as it has no royalty fees.
-
Dec 23rd, 2010, 09:28 AM
#6
Re: Read Excel file except fist 14 rows
They have a free trial, so you might be able to quickly proof it out to see if it works. If it does, then you can suggest spending $900 now or taking two weeks to see if you can write a workaround.
I've never had good luck with the OLE connection and an excel file that had anything more than really simple tabular data in it.
Technically you might be able to code something that does use excel and technically works, even though it is against the Microsoft recommendation. I have seen people do this and have it work for them.
Another option might be some sort of automation using OpenOffice, but I haven't looked into what kind of Automation they offer in a few years.
-
Dec 23rd, 2010, 09:33 AM
#7
Re: Read Excel file except fist 14 rows
Originally Posted by Negative0
They have a free trial, so you might be able to quickly proof it out to see if it works. If it does, then you can suggest spending $900 now or taking two weeks to see if you can write a workaround.
I've never had good luck with the OLE connection and an excel file that had anything more than really simple tabular data in it.
Technically you might be able to code something that does use excel and technically works, even though it is against the Microsoft recommendation. I have seen people do this and have it work for them.
Another option might be some sort of automation using OpenOffice, but I haven't looked into what kind of Automation they offer in a few years.
Unfortunately I think me spending two weeks will be cheaper then spending 900 bucks for them
OpenOffice might be a good idea, I'll check that out.
-
Dec 23rd, 2010, 09:35 AM
#8
Re: Read Excel file except fist 14 rows
Never tried this on a machine without Excel installed (and even that was years ago) but you used to be able to connected via ODBC/ADO.
-
Dec 23rd, 2010, 09:39 AM
#9
Re: Read Excel file except fist 14 rows
Originally Posted by Grimfort
Never tried this on a machine without Excel installed (and even that was years ago) but you used to be able to connected via ODBC/ADO.
That's my first method, where I get a "Not a legal OleAut date" error.
-
Dec 23rd, 2010, 09:41 AM
#10
Re: Read Excel file except fist 14 rows
Oh and if you are glutton for punishment, you could technically write your own parser for excel files:
http://www.microsoft.com/interop/doc...ryFormats.mspx
I know that this is not realistic, but in searching I found this and thought it was interesting. The Excel spec is over 1100 pages.
-
Dec 23rd, 2010, 10:06 AM
#11
Re: Read Excel file except fist 14 rows
The following reads a specific range into a DataTable. I tried it with a work sheet with several columns of integer, one column DateTime and one column of String and the data was read in fine. Not sure how you determine how many columns and rows but I guess you could widen the range of both row and col then figure out which ones are empty. Without IMEX there were issues
Here is a decent resource at Code Project.
http://www.codeproject.com/KB/miscct...ta_access.aspx
Code:
Private Sub ReadRange()
Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='C:\Data\Book1.xls';Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
MyConnection.Open()
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$A10:F17]", MyConnection)
Dim dt As New DataTable
dt.Load(cmd.ExecuteReader)
DataGridView2.DataSource = dt
End Using
End Sub
Last edited by kareninstructor; Dec 23rd, 2010 at 10:14 AM.
-
Dec 23rd, 2010, 10:11 AM
#12
Re: Read Excel file except fist 14 rows
Originally Posted by Negative0
Have you tried any third party products? I have used the Aspose suite for Word documents on a server and that worked great for me. They have an excel version called Aspose.Cells.
It basically gives you access to all of the content of the file without needing the Microsoft product installed on the server. They are relatively inexpensive for a business to purchase, weighing it against the amount of time it may take you to find a workaround.
Aspose Cells is a great library for working with Excel, I've been using and has saved me time and money. For what Nick is looking for it is overkill.
-
Dec 23rd, 2010, 10:22 AM
#13
Re: Read Excel file except fist 14 rows
Originally Posted by kevininstructor
The following reads a specific range into a DataTable. I tried it with a work sheet with several columns of integer, one column DateTime and one column of String and the data was read in fine. Not sure how you determine how many columns and rows but I guess you could widen the range of both row and col then figure out which ones are empty. Without IMEX there were issues
Here is a decent resource at Code Project.
http://www.codeproject.com/KB/miscct...ta_access.aspx
Code:
Private Sub ReadRange()
Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='C:\Data\Book1.xls';Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
MyConnection.Open()
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$A10:F17]", MyConnection)
Dim dt As New DataTable
dt.Load(cmd.ExecuteReader)
DataGridView2.DataSource = dt
End Using
End Sub
I see, I didn't know you could select a range in the query. I'll try that. I don't know how many rows there are however, I have to keep checking rows until an empty row is found. I suppose I could run the query several times, each times 100 rows at a time or something.
What concerns me more is this statement in the article you linked to:
A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheeet title above and to the left of the data in cell A1.
My data begins in column A, row 16 (15 are the column headers), and there is a whole load of data in rows above that, so it may not work after all...
I'll get back to you.
-
Dec 23rd, 2010, 10:36 AM
#14
Re: Read Excel file except fist 14 rows
You should be able too as shown here
Last edited by kareninstructor; Jul 20th, 2011 at 12:56 PM.
-
Dec 23rd, 2010, 11:10 AM
#15
Re: Read Excel file except fist 14 rows
Awesome, thanks, it worked Didn't expect to find a solution this quick!
-
Dec 23rd, 2010, 11:21 AM
#16
Re: Read Excel file except fist 14 rows
Originally Posted by NickThissen
Awesome, thanks, it worked Didn't expect to find a solution this quick!
Good to hear it works for you!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|