none
Excel Sheet Names in Sheet Order

    Question

  • Hi,

    I am reading excel file and getting sheet names using following code:

    string strConnString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
    
    DbProviderFactory objDbFactory = DbProviderFactories.GetFactory("System.Data.OleDb"); 
    
    DbDataAdapter objDbAdapter = null;
    
    objDbAdapter = factory.CreateDataAdapter();
    
    DbConnection objDbConnection = objDbFactory.CreateConnection();
    
    objDbConnection.ConnectionString = strConnString;
    
    objDbConnection.Open();
    
    Datatable objSheetNames = objDbConnection.GetSchema("Tables");
    

    The datatable "objSheetNames" contains list of sheet names in alphabetical order.

    Is there any way I can find list of sheet names in the order they appear in the excel sheet?

    I have tried to search this forum to find similar post but could not find answer to my question. I found that DAO could be used to retrieve the sheet names in the order they appear in the excel sheet (http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/711cf5f9-75fc-4a02-9a96-08aec48dad69/) .

    But I am interested to know if I am using OleDbConnection then can I get sheet names in original sheet order?

    Any help on this would be greatly appreciated. Thank you in advance.

    -Kailash

    Wednesday, August 18, 2010 4:15 PM

Answers

  • Hi Kailash,

    I am afraid that OLEDB does not preserve the sheet order as they were in Excel. Do you have to use OLEDB ? Another way to get the sheet names is using the office interop classes. For example:

    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open("D:\\Book1.xlsx"); 
    
    String[] excelSheets = new String[excelBook.Worksheets.Count];
    int i = 0;
    foreach(Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)    
    {
      excelSheets[i] = wSheet.Name;
      i++;
    }
    
    



    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, August 23, 2010 1:31 PM

All replies

  • If I remember correctly you will need sort the DataTable by the ORDINAL_POSITION column since the OLEDB Provider does not return them in that order.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, August 18, 2010 5:05 PM
  • Hi Paul,

    Thank you for the reply. I tried that but I don't see any ORDINAL_POSITION column in the datatable "objSheetNames". I see following table in the dataset viewer for the same.

    TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_GUID DESCRIPTION TABLE_PROPID DATE_CREATED DATE_MODIFIED
        D1 TABLE     8/18/2010 12:41 8/18/2010 12:41
        D1$ TABLE     8/18/2010 12:41 8/18/2010 12:41
        I1 TABLE     8/18/2010 12:41 8/18/2010 12:41
        I1$ TABLE     8/18/2010 12:41 8/18/2010 12:41
        P1 TABLE     8/18/2010 12:41 8/18/2010 12:41
        P1$ TABLE     8/18/2010 12:41 8/18/2010 12:41
        S1 TABLE     8/18/2010 12:41 8/18/2010 12:41
        S1$ TABLE     8/18/2010 12:41 8/18/2010 12:41

    But when I do objDbConnection.GetSchema("Columns"), then I do see the ORDINAL_POSITION column. I am interested in getting schema for tables and I want them in original order for table names.

    If you can help me getting table names in oiginal order that would be highly appreciated.

    Once again thank you for the reply.

    -Kailash

    Wednesday, August 18, 2010 6:16 PM
  • Sorry about that, I remembered incorrectly. Unfortunately, OLEDB does not preserve the Worksheet order as it appears in Excel. You can only do this via DAO code (a Reference to the Microsoft DAO 3.6 Object library is required):

     

      Private Sub ListExcelTablesDAO()
    
    
        Dim ExcelWB As dao.Database
        Dim tbl As dao.TableDef
        Dim JetEngine As New dao.DBEngine
    
        ExcelWB = JetEngine.OpenDatabase("C:\Test Files\Book20.xls", False, True, "Excel 8.0;")
    
        For Each tbl In ExcelWB.TableDefs
          Console.WriteLine(tbl.Name)
        Next tbl
    
        ExcelWB.Close()
    
      End Sub
    


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, August 19, 2010 6:24 PM
  • Hi Paul,

    Thank you for the reply.

    I was trying some other options like - when we export the data to a file, can we try to populate the TABLE_GUID column in the table "Tables"? 

    We can then sort by TABLE_GUID and still use the OleDbConnection object. But I was not sure how to populate the TABLE_GUID column and it always contains DBNull. If you think that we can use this approach then please let us know.

    Currently our project uses OleDbConnection object to talk to excel sheets. And recently we have been working on new enhancement where requirement is that we need to preserve the sheet order when we import the excel file to the application. So if we had to use DAO that would involve lot of changes.

    I really appreciate for your help.

    Thank you,

    Kailash

    Thursday, August 19, 2010 6:47 PM
  • Hi Kailash,

    I am afraid that OLEDB does not preserve the sheet order as they were in Excel. Do you have to use OLEDB ? Another way to get the sheet names is using the office interop classes. For example:

    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open("D:\\Book1.xlsx"); 
    
    String[] excelSheets = new String[excelBook.Worksheets.Count];
    int i = 0;
    foreach(Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)    
    {
      excelSheets[i] = wSheet.Name;
      i++;
    }
    
    



    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, August 23, 2010 1:31 PM
  • I was trying some other options like - when we export the data to a file, can we try to populate the TABLE_GUID column in the table "Tables"? 

    We can then sort by TABLE_GUID and still use the OleDbConnection object. But I was not sure how to populate the TABLE_GUID column and it always contains DBNull. If you think that we can use this approach then please let us know.


     

    I don't believe that you can set those schema properties since they likely do not exist in an Excel Workbook.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, August 24, 2010 10:09 PM