Accessing Excel Worksheets from C# Application

Create a class in C# that
1. loops through all of the files in a directory (all files are .xls)
2. Open these files one by one
3. Then loop through each worksheet in each file extracting the name of the worksheet and the first row of information (this may not be on the first row)

I have tried by looking at examples on the net but I am relatively new to the world of C#.
Thanks In Advance
Steven_PrescottAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tstoeckigtCommented:
If Excel is installed this is easy. You just have to use Excel as a COM object. If you use VS.NET you can add a reference to "Microsoft Excel XX.0 Object Library" to your project.
Then you can a Excel-application-object and use it to open the file and loop through the worksheets.

Ciao
Timo
0
Steven_PrescottAuthor Commented:
I can access the Excel Object Library and loop through all of the files, and count the number of worksheets in each file.
It is however looping through these worksheets and obtaining the name of each of them as well as the information that is in these worksheets.

This is what I currently have:

private Excel.Application ExcelObj = null;

foreach(FileInfo file in files)
{
    xlBook = ExcelObj.Workbooks.Open(srcdir + file.Name,
      Type.Missing, Type.Missing, Type.Missing, Type.Missing,
      Type.Missing, Type.Missing, Type.Missing, Type.Missing,
      Type.Missing, Type.Missing, Type.Missing, Type.Missing,
      Type.Missing, Type.Missing);

    Counter = xlBook.Worksheets.Count;
   
    // The problem is here now that I know how many sheets there are I am having problems accessing the names of the sheets as well as
    // the information that is in the sheets
    // If there are any examples of how to do this it would be much appreciated

}

Thanks again in advance
0
TheAvengerCommented:
This is a good example. It works with the Office Tools for .Net but the object model is the same so you can find some properties, etc.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Steven_PrescottAuthor Commented:
That page has some excellent examples, however I am now coming unstuck on the following line
      
    Excel.Range rng = ExcelObj.get_Range("Sheets", Type.Missing);

I am getting an HRESULT error - I feel that I am missing the boat as it were on a small but significant detail
Following the above line i am intending to run the following code (xlBook is of type Excel.Workbook), if I can get this to run that will be enough

    foreach (Excel.Worksheet sh in xlBook.Sheets)
    {
        Console.WriteLine(sh.Name);
    }

Thanks once again in advance for you help thus far.
0
TheAvengerCommented:
With the line:

Excel.Range rng = ExcelObj.get_Range("Sheets", Type.Missing);

you are getting a range with the name Sheets (must be defined in the Excel file). So you get an error probably because there is no such range defined in the Excel file.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steven_PrescottAuthor Commented:
Thanks "TheAvenger" you put me on the right track - here is the final answer to my problem

foreach(FileInfo file in files)
{
    xlBook = ExcelObj.Workbooks.Open(srcdir + file.Name,
     Type.Missing, Type.Missing, Type.Missing, Type.Missing,
     Type.Missing, Type.Missing, Type.Missing, Type.Missing,
     Type.Missing, Type.Missing, Type.Missing, Type.Missing,
     Type.Missing, Type.Missing);

      foreach (Excel.Worksheet sh in xlBook.Sheets)
      {
            Console.WriteLine(sh.Name.ToString());
      }    
}

Thanks Again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.