Parsing Excel Spreadsheet

  • Thread starter Thread starter Hugh McLaughlin
  • Start date Start date
H

Hugh McLaughlin

Hello Everyone and thanks for your help in advance. I am
working on an application that requires the parsing of an
Excel spreadsheet that will be loaded into a SQL Server
table. An example of the spreadsheet is located at:

http://ratesheets.sollen.com/script/ratesheets/interfirst/
ratesheets/IFRS1_20030722_094227.xls

At the heart of the problem is the sometimes varying
layout of the spreadsheet. For example, the application
requires that the date between cells H13 and D23 be
loaded into the table. However, the same data may be
located between H12 and D22 tomorrow. So the application
needs to be able to determine the starting point of the
data. Fortunately, the headings do not change, so one of
the obvious ways to handle this is to move through each
cell (i.e. A1, A2, B1, B2) until the known header value
is achieved. However, this seems to be a very
inefficient way to handle this. Is there another way to
identify the starting point to begin processing, i.e. in
English "Find the cell that equals 30 YEAR FIXED -
Program 100". Any ehlp would be greatly appreciated.
Thanks.
 
Excel has a find function. You can use that to set the active cell to the
cell containing the desired text.

Colin
 
Hello Hugh,

I don't think there is any easy to achieve this. If you know exactly where
the data is when you automate Excel, you could input it as a parameter in
your function to get the data. If you don't know, you have to search the
data by yourself. Or is it possible for you to create a special pane in
Excel file to contain the beginning area of valid data?

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! ¨C www.microsoft.com/security
This posting is provided ¡°as is¡± with no warranties and confers no rights.
 
Thanks for your response Tian. The Excel file is
generated by a thrid party, so I am unclear as to how I
could automate a pane. Does Colin's Find fucntion idea
make sense. How do you access it from an Asp.Net page?
 
I never use A1, A2, B1 and B2 when programming. I always use the row
and columns. If you use the Find method to find the header, then you
can find the row and the column with Cel.Row and Cel.Column. Column
would be the starting column. So the first column of your table would
be StartColumn + 0, the second column of your table would be
StartColumn + 1.

You can find a cell by ExcelApp.Workbooks(1).Sheets(1).Cells(Row,
Column)

Hope this helps.

Guillaume Hanique
 
Hugh,
Have you considered querying the Excel worksheet using ADO.NET? You
talk of headings so there's are chance your data is similar enough to
a database table i.e. rows of columns. With SQL you supply the name of
the column, not the Excel range/name. The 'English' phrase you used
sounds similar to a SQL query to me...
 
Thanks for your response. Actually, that is what I am
doing. However, the problem, unless I am missing
something, is that I need to define a range of cells.
Unfortunately, the range changes occasionally, thus a
static reference won't work. At that point, I need to
locate the appropraite starting cell for the range based
on a heading value. Any ideas?
 
Short answer: automate Excel and use its object model to call the Find
method to find the range, then plug the range into your SQL e.g.

SELECT * FROM [InterFirst Rates$D12:H23]

With ASP.NET there are many ways of automating Excel. I have no way of
telling which is the best approach for your cicumstances. Take a look
at the following KB article for a discussion of the various
approaches:

http://support.microsoft.com/default.aspx?scid=kb;en-us;311452
 
Back
Top