views:

5171

answers:

5

I'm using OleDb to read from an excel workbook with many sheets.

I need to read the sheet names, but I need them in the order they are defined in the spreadsheet; so If I have a file that looks like this;

|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
\__GERMANY__/\__UK__/\__IRELAND__/

Then I need to get the dictionary

1="GERMANY", 
2="UK", 
3="IRELAND"

I've tried using OleDbConnection.GetOleDbSchemaTable(), and that gives me the list of names, but it alphabetically sorts them. The alpha-sort means I don't know which sheet number a particular name corresponds to. So I get;

GERMANY, IRELAND, UK

which has changed the order of UK and IRELAND.

The reason I need it to be sorted is that I have to let the user choose a range of data by name or index; they can ask for 'all the data from GERMANY to IRELAND' or 'data from sheet 1 to sheet 3'.

Any ideas would be greatly appreciated.

if I could use the office interop classes, this would be straightforward. Unfortunately, I can't because the interop classes don't work reliably in non-interactive environments such as windows services and ASP.NET sites, so I needed to use OLEDB.

+5  A: 

Can you not just loop through the sheets from 0 to Count of names -1? that way you should get them in the correct order.

Edit

I noticed through the comments that there are a lot of concerns about using the Interop classes to retrieve the sheet names. Therefore here is an example using OLEDB to retrieve them:

/// <summary>
/// This method retrieves the excel sheet names from 
/// an excel workbook.
/// </summary>
/// <param name="excelFile">The excel file.</param>
/// <returns>String[]</returns>
private String[] GetExcelSheetNames(string excelFile)
{
    OleDbConnection objConn = null;
    System.Data.DataTable dt = null;

    try
    {
        // Connection String. Change the excel file to the file you
        // will search.
        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
          "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
        // Create connection object by using the preceding connection string.
        objConn = new OleDbConnection(connString);
        // Open connection with the database.
        objConn.Open();
        // Get the data table containg the schema guid.
        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if(dt == null)
        {
           return null;
        }

        String[] excelSheets = new String[dt.Rows.Count];
        int i = 0;

        // Add the sheet name to the string array.
        foreach(DataRow row in dt.Rows)
        {
           excelSheets[i] = row["TABLE_NAME"].ToString();
           i++;
        }

        // Loop through all of the sheets if you want too...
        for(int j=0; j < excelSheets.Length; j++)
        {
            // Query each excel sheet.
        }

        return excelSheets;
   }
   catch(Exception ex)
   {
       return null;
   }
   finally
   {
      // Clean up.
      if(objConn != null)
      {
          objConn.Close();
          objConn.Dispose();
      }
      if(dt != null)
      {
          dt.Dispose();
      }
   }
}

Extracted from Article on the CodeProject.

James
That's code I'd like to see! How can you query for 'the Nth sheet' and the number of sheets?
Steve Cooper
You are saying you are retrieving the list of names from the GetOledbSchemaTable...hence you have the count of sheets in the Workbook. So its just a case of looping through from 0 to the count of the sheet names-1. Personally I wouldn't even read in the sheet names, I would just loop through the sheets and just get the Name property from the Worksheet object...
James
This won't work unless you know the name of the first sheet.
eviljack
@James -- if I could use the office interop classes, this would be straightforward. Unfortunately, I can't because the interop classes don't work reliably on server platforms (windows services, ASP.NET sites) so I needed to use OLEDB.
Steve Cooper
See updated answer.
James
Hi, James. This is pretty much my original problem -- while the GetOleDbSchemaTable() method gets the names, the row number doesn't correspond to the workbook sheet number. So Sheet 4 would be row 0, if it came first in the alphabet.
Steve Cooper
A: 

To James: I have the same problem as Steve Cooper. When I loop through the list of names until I hit the Nth element, the list is still sorted in alphabetical order. So, in Steve's example, he and I need get the second sheet name and need it to be "UK", but looping returns "Ireland".

Maybe I misunderstood what you mean by "looping through the sheets"?

Hi Tom, please see my updated answer.
James
A: 

This worked for me. Stolen from here: http://stackoverflow.com/questions/1427348/how-do-you-get-the-name-of-the-first-page-of-an-excel-workbook

        object opt = System.Reflection.Missing.Value;
        Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
        Excel.Workbook workbook = app.Workbooks.Open(WorkBookToOpen,
                                                 opt, opt, opt, opt, opt, opt, opt,
                                                 opt, opt, opt, opt, opt, opt, opt);
        Excel.Worksheet worksheet = workbook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
        string firstSheetName = worksheet.Name;
eviljack
Hi. Glad you've got working code, but that uses the Interop classes, and they don't work reliably on a server; you can't run this code on, say, Windows Server 2008. So you can't use it in a web app or in server-side code. That's why I was going for oledb, rather than Interop.
Steve Cooper
A: 

Hi Steve,

Were you able to get the sheet names in order as they exist in the file? I got same problem and cann't figure out how to get the sheet names in the original order(unsorted) using OLEDB. Anybody else got the solution?

Thanks, AQ78

AQ78
Hi. No, I think this is straight impossible. We ended up buying a library which reads things on the server.
Steve Cooper
A: 

Try this. Here is the code to get the sheet names in order.

    private Dictionary<int,string> GetExcelSheetNames(string fileName)
    {
     Excel.Application _excel = null;
     Excel.Workbook _workBook = null;
     Dictionary<int,string> excelSheets = new Dictionary<int,string>();
     try
     {
      object missing = Type.Missing;
      object readOnly = true;

  Excel.XlFileFormat.xlWorkbookNormal
  _excel = new Excel.ApplicationClass();
  _excel.Visible = false;
  _workBook = _excel.Workbooks.Open(fileName, 0, readOnly, 5, missing,
     missing, true, Excel.XlPlatform.xlWindows, "\\t", false, false, 0, true, true, missing);
  if (_workBook != null)
  {

                   int index = 0; 

   foreach (Excel.Worksheet sheet in _workBook.Sheets)
   {
    // Can get sheet names in order they are in workbook
    excelSheets.Add(++index, sheet.Name);
   }
  }
 }
 catch (Exception e)
 {
  return null;
 }
 finally
 {
  if (_excel != null)
  {

   if (_workBook != null)
   {
    _workBook.Close(false, Type.Missing, Type.Missing);
   }
   _excel.Application.Quit();
  }
  _excel = null;
  _workBook = null;
 }
    return excelSheets;
    }
Ravi Shankar