newbie: excel reading, writing, parsing

  • Thread starter Thread starter frank plank
  • Start date Start date
F

frank plank

Hi everyone,

I apologize if this is the wrong forum for this question. I have a
task to automate a reporting procedure that read various values out of
multiple excel documents (on different servers). It needs to parse
(fairly complex) the source excel documents and then write to a new
excel document. I need to learn C# & .NET for future work projects,
and I was wondering if this might be a suitable introductory C#
project. I have a pretty broad (less depth) programming background
and I am a quick study. Will C# be a reasonable way to approach this
problem? Your advice is greatly appreciated!

Frank Plank
 
Frank,

It depends on a few things. The first is how the data is stored in
Excel. If it is pretty much tabular (and you indicated it is not, that is
is fairly complex), then I would recommend that you use the Jet Ole Db
provider with the classes in the System.Data.OleDb namespace. This would be
the easiest for someone just entering (data access, pretty much).

You could always load an instance of Excel, and access the
workbooks/worksheets/cells directly. However, it might be daunting, as
there are issues with unreleased objects (not because of interop, but
because they are not disposed of properly, and it is hard to keep track of).

However, it is not impossible. If you want to go the second route, then
check out knowledge base article 302084, titled "How To Automate Microsoft
Excel from Microsoft Visual C# .NET", located at (watch for line wrap):

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

Also, I am not sure, but I don't believe the code in the article is
correctly disposing of the resources used by Excel either.

Hope this helps.
 
Hi Frank,

Well, first and foremost you will have to deal with Excel automation
objects, which are obviously the same despite the programming language you
use. For C#, you will need to create an COM Interop assembly for Excel, and
I'd recommend that you use the tlbimp.exe command-line utility for that.

The rest is data transformation, for which the choice of the language is not
that important. If you are good at OOP, you will definitely benefit from
C#'s rich OOP capabilities. If you have little or no experience with OOP,
and if this is your first .NET project, you should really give a good
thinking to whether you are ready to deal with advanced issues like COM
Interoperability at the outset.
 
I'm in the process of doing basically the same thing. Extracting data
from Excel programatically and in turn, creating Excel workbooks as
output.

Not really a project for the C# beginner ~ MSDN and intellisense provide
little information on the features of the Excel COM/Interop objects and
how they are used. Much trial and error to wade through.

Not to hijack the thread but... does anyone know how to extract the
worksheet names (from inside a workbook) programatically from the Excel
object? I know how to access the worksheet - through an index, but this
allows an index to extend 'out of bounds' and could cause an exception.

Thanks,

Tom
 
The Workbook object has a Sheets collection. It should be able to be
enumerated, or at least have a count property. Each member of it is a
Worksheet object. It has a Name property. Remember the Workbooks and Sheets
collections are COM objects in their own right, not just arrays.
 
Back
Top