Using Excel OleDb to get sheet names IN SHEET ORDER

Question

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

User · Accepted Answer

Can t find this in actual MSDN documentation  but a moderator in the forums said      I am afraid that OLEDB does not preserve the sheet order as they were in Excel   Excel Sheet Names in Sheet Order  Seems like this would be a common enough requirement that there would be a decent workaround

User · Answer

This is short  fast  safe  and usable     public static List lt string gt  ToExcelsSheetList string excelFilePath        List lt string gt  sheets   new List lt string gt         using  OleDbConnection connection                new OleDbConnection  excelFilePath TrimEnd   ToLower   EndsWith  x                    Provider Microsoft ACE OLEDB 12 0 Data Source      excelFilePath           Extended Properties  Excel 12 0 Xml HDR YES                   provider Microsoft Jet OLEDB 4 0 Data Source      excelFilePath      Extended Properties Excel 8 0                   connection Open            DataTable dt   connection GetOleDbSchemaTable OleDbSchemaGuid Tables  null           foreach  DataRow drSheet in dt Rows              if  drSheet  TABLE NAME   ToString   Contains                                     string s   drSheet  TABLE NAME   ToString                    sheets Add s StartsWith      s Substring 1  s Length - 3   s Substring 0  s Length - 1                          connection Close              return sheets

User · Answer

Another way   a xls x  file is just a collection of   xml files stored in a   zip container  unzip the file  app xml  in the folder docProps    lt  xml version  1 0  encoding  UTF-8  standalone  true   gt  - lt Properties xmlns vt  http   schemas openxmlformats org officeDocument 2006 docPropsVTypes  xmlns  http   schemas openxmlformats org officeDocument 2006 extended-properties  gt   lt TotalTime gt 0 lt  TotalTime gt   lt Application gt Microsoft Excel lt  Application gt   lt DocSecurity gt 0 lt  DocSecurity gt   lt ScaleCrop gt false lt  ScaleCrop gt  - lt HeadingPairs gt    - lt vt vector baseType  variant  size  2  gt      - lt vt variant gt         lt vt lpstr gt Arbeitsbl  tter lt  vt lpstr gt       lt  vt variant gt      - lt vt variant gt         lt vt i4 gt 4 lt  vt i4 gt       lt  vt variant gt     lt  vt vector gt   lt  HeadingPairs gt  - lt TitlesOfParts gt    - lt vt vector baseType  lpstr  size  4  gt       lt vt lpstr gt Tabelle3 lt  vt lpstr gt       lt vt lpstr gt Tabelle4 lt  vt lpstr gt       lt vt lpstr gt Tabelle1 lt  vt lpstr gt       lt vt lpstr gt Tabelle2 lt  vt lpstr gt     lt  vt vector gt   lt  TitlesOfParts gt   lt Company  gt   lt LinksUpToDate gt false lt  LinksUpToDate gt   lt SharedDoc gt false lt  SharedDoc gt   lt HyperlinksChanged gt false lt  HyperlinksChanged gt   lt AppVersion gt 14 0300 lt  AppVersion gt   lt  Properties gt    The file is a german file  Arbeitsbl  tter   worksheets   The table names  Tabelle3 etc  are in the correct order  You just need to read these tags    regards

User · Answer

As per MSDN  In a case of spreadsheets inside of Excel it might not work because Excel files are not real databases  So you will be not able to get the sheets name in order of their visualization in workbook    Code to get sheets name as per their visual appearance using interop   Add reference to Microsoft Excel 12 0 Object Library   Following code will give the sheets name in the actual order stored in workbook  not the sorted name   Sample Code   using Microsoft Office Interop Excel   string filename    C   romil xlsx    object missing   System Reflection Missing Value   Microsoft Office Interop Excel Application excel   new Microsoft Office Interop Excel Application     Microsoft Office Interop Excel Workbook wb  excel Workbooks Open filename   missing   missing   missing   missing missing   missing   missing   missing   missing   missing   missing   missing   missing   missing    ArrayList sheetname   new ArrayList     foreach  Microsoft Office Interop Excel Worksheet  sheet in wb Sheets        sheetname Add sheet Name

User · Answer

Since above code do not cover procedures for extracting list of sheet name for Excel 2007 following code will be applicable for both Excel 97-2003  and Excel 2007 too   public List lt string gt  ListSheetInExcel string filePath       OleDbConnectionStringBuilder sbConnection   new OleDbConnectionStringBuilder       String strExtendedProperties   String Empty     sbConnection DataSource   filePath     if  Path GetExtension filePath  Equals   xls     for 97-03 Excel file            sbConnection Provider    Microsoft Jet OLEDB 4 0         strExtendedProperties    Excel 8 0 HDR Yes IMEX 1    HDR ColumnHeader IMEX InterMixed         else if  Path GetExtension filePath  Equals   xlsx       for 2007 Excel file            sbConnection Provider    Microsoft ACE OLEDB 12 0         strExtendedProperties    Excel 12 0 HDR Yes IMEX 1           sbConnection Add  Extended Properties  strExtendedProperties      List lt string gt  listSheet   new List lt string gt        using  OleDbConnection conn   new OleDbConnection sbConnection ToString               conn Open         DataTable dtSheet   conn GetOleDbSchemaTable OleDbSchemaGuid Tables  null                 foreach  DataRow drSheet in dtSheet Rows                 if  drSheet  TABLE NAME   ToString   Contains        checks whether row contains   xlnm  FilterDatabase  or sheet name i e  sheet name always ends with   sign                         listSheet Add drSheet  TABLE NAME   ToString                            return listSheet      Above function returns list of sheet in particular excel file for both excel type 97 2003 2007

User · Answer

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        lt summary gt      This method retrieves the excel sheet names from      an excel workbook       lt  summary gt       lt param name  excelFile  gt The excel file  lt  param gt       lt returns gt String   lt  returns gt  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  lt  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

User · Answer

I have created the below function using the information provided in the answer from  kraeppy  https   stackoverflow com a 19930386 2617732   This requires the  net framework v4 5 to be used and requires a reference to System IO Compression  This only works for xlsx files and not for the older xls files       using System IO Compression      using System Xml      using System Xml Linq       static IEnumerable lt string gt  GetWorksheetNamesOrdered string fileName                  open the excel file         using  FileStream data   new FileStream fileName  FileMode Open                           unzip             ZipArchive archive   new ZipArchive data                  select the correct file from the archive             ZipArchiveEntry appxmlFile   archive Entries SingleOrDefault e   gt  e FullName     docProps app xml                   read the xml             XDocument xdoc   XDocument Load appxmlFile Open                    find the titles element             XElement titlesElement   xdoc Descendants   Where e   gt  e Name LocalName     TitlesOfParts   Single                   extract the worksheet names             return titlesElement                  Elements   Where e   gt  e Name LocalName     vector   Single                    Elements   Where e   gt  e Name LocalName     lpstr                    Select e   gt  e Value

User · Answer

I don t see any documentation that says the order in app xml is guaranteed to be the order of the sheets  It PROBABLY is  but not according to the OOXML specification   The workbook xml file  on the other hand  includes the sheetId attribute  which does determine the sequence - from 1 to the number of sheets  This is according to the OOXML specification  workbook xml is described as the place where the sequence of the sheets is kept   So reading workbook xml after it is extracted form the XLSX would be my recommendation  NOT app xml  Instead of docProps app xml  use xl workbook xml and look at the  element  as shown here -      lt workbook xmlns  http   schemas openxmlformats org spreadsheetml 2006 main  xmlns r  http   schemas openxmlformats org officeDocument 2006 relationships  gt     lt fileVersion appName  xl  lastEdited  5  lowestEdited  5  rupBuild  9303    gt      lt workbookPr defaultThemeVersion  124226    gt   -  lt bookViews gt     lt workbookView xWindow  120  yWindow  135  windowWidth  19035  windowHeight  8445    gt      lt  bookViews gt  -  lt sheets gt     lt sheet name  By song  sheetId  1  r id  rId1    gt      lt sheet name  By actors  sheetId  2  r id  rId2    gt      lt sheet name  By pit  sheetId  3  r id  rId3    gt      lt  sheets gt  -  lt definedNames gt     lt definedName name   xlnm  FilterDatabase  localSheetId  0  hidden  1  gt  By song   A 1  O 59 lt  definedName gt      lt  definedNames gt     lt calcPr calcId  145621    gt      lt  workbook gt

User · Answer

This worked for me  Stolen from here  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

User · Answer

I like the idea of  deathApril to name the sheets as 1 Germany  2 UK  3 IRELAND  I also got your issue to do this rename for hundreds of sheets  If you don t have a problem to rename the sheet name then you can use this macro to do it for you  It will take less than seconds to rename all sheet names  unfortunately ODBC  OLEDB return the sheet name order by asc  There is no replacement for that  You have to either use COM or rename your name to be in the order   Sub Macro1       Macro1 Macro      Dim i As Integer For i   1 To Sheets Count  Dim prefix As String  prefix   i  If Len prefix   lt  4 Then   prefix    000   ElseIf Len prefix   lt  3 Then   prefix    00   ElseIf Len prefix   lt  2 Then   prefix    0   End If  Dim sheetName As String  sheetName   Sheets i  Name  Dim names  names   Split sheetName   -    If  UBound names   gt  0  And IsNumeric names 0   Then    do nothing  Else   Sheets i  Name   prefix  amp  i  amp   -   amp  Sheets i  Name  End If Next  End Sub   UPDATE  After reading  SidHoland comment regarding BIFF an idea flashed  The following steps can be done through code  Don t know if you really want to do that to get the sheet names in the same order  Let me know if you need help to do this through code    1  Consider XLSX as a zip file  Rename   xlsx into   zip 2  Unzip 3  Go to unzipped folder root and open  docprops app xml 4  This xml contains the sheet name in the same order of what you see  5  Parse the xml and get the sheet names   UPDATE  Another solution - NPOI might be helpful here http   npoi codeplex com    FileStream file   new FileStream   yourexcelfilename   FileMode Open  FileAccess Read          HSSFWorkbook  hssfworkbook   new HSSFWorkbook file           for  int i   0  i  lt  hssfworkbook NumberOfSheets  i                          Console WriteLine hssfworkbook GetSheetName i                      file Close      This solution works for xls  I didn t try xlsx   Thanks   Esen

User · Answer

Try this  Here is the code to get the sheet names in order   private Dictionary lt int  string gt  GetExcelSheetNames string fileName        Excel Application  excel   null      Excel Workbook  workBook   null      Dictionary lt int  string gt  excelSheets   new Dictionary lt int  string gt         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

[c#] Using Excel OleDb to get sheet names IN SHEET ORDER

Examples related to c#

Examples related to excel

Examples related to oledb

Examples related to server-side