• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

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
0
Steven_Prescott
Asked:
Steven_Prescott
  • 3
  • 2
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now