Friday, February 29, 2008

Hidden Worksheet in an Excel File

I am automating a report where I have to pull data from excel file to database. When doing this, sometimes I get an error keep telling me that I get the wrong report format.

After debug on my program, I found out that some of the excel files do have a few hidden worksheet with different report format. That’s why I keep getting this error.

Assume that only visible worksheet will be used to pull data into database, so I need to know which worksheet is visible and which is not. This can be done by using Worksheet.Visible property, which can determine whether the worksheet is visible or hide.

Excel.Application xlsApp = new Excel.ApplicationClass();
Excel.Workbook xlsWorkbook;
Excel.Worksheet xlsWorksheet;

xlsWorkbook = xlsApp.Workbooks.Open("C:\\test.xls",0,false,5,"","",true,Excel.XlPlatform.xlWindows,"\t",false,false,0,true,1,0);

// get the collection of sheets in the workbook
Excel.Sheets sheets = xlsWorkbook.Worksheets;

foreach(Worksheet sht in sheets) {
     if (sht.Visible == Excel.XlSheetVisibility.xlSheetVisible) {
         // your process here…
     else {
         // Do nothing…

As you can see from the code above, if the worksheet is visible, I will continue with my process, else I just ignore the worksheet.



Get paid for your opinions! Click on the banner above to join Planet Pulse. Its totally free to sign up, and you can earn UNLIMITED. Find out more by visiting PLANET PULSE.
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template