mkhuwaja / linqtoexcel

Automatically exported from code.google.com/p/linqtoexcel
0 stars 0 forks source link

No method to get worksheet name by index instead of alpha-bate? #97

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago
Hi, 

GetWorksheetNames() method of ExcelQueryFactory returns worksheet names by 
alphabate. 

There is noway to get the worksheet name by index in case of multiple 
worksheets (so that I can get the first worksheet of excel) 

Original issue reported on code.google.com by vishalps...@gmail.com on 19 Nov 2013 at 10:10

GoogleCodeExporter commented 8 years ago
[deleted comment]
GoogleCodeExporter commented 8 years ago
As far as I know, Worksheet names are not sorted, they are as they appear in 
Excel workbook, so you can do something like:

string strFirstWorksheetName =  
<ExcelQueryFactoryObjectName>.GetWorksheetNames().ToArray()[0];

Original comment by fwd...@gmail.com on 19 Nov 2013 at 1:25

GoogleCodeExporter commented 8 years ago
string strFirstWorksheetName =  
<ExcelQueryFactoryObjectName>.GetWorksheetNames().ToArray()[0]; should work

Original comment by paulyo...@gmail.com on 19 Nov 2013 at 4:25

GoogleCodeExporter commented 8 years ago
Sorry, this is wrong. I attach a simple example. The attached file contains 
"sheetone" and "sheetnumbertwo". My test code is:

            const string excelpath = "sheetordering.xlsx";
            var excel = new ExcelQueryFactory(excelpath);
            IEnumerable<string> worksheetNames = excel.GetWorksheetNames();
            foreach (string worksheetName in worksheetNames)
            {
                Console.WriteLine(worksheetName);
            }

It returns:

sheetnumbertwo
sheetone

So, the ordering is alphabetic and not the ordering of the sheets in the file.
Is there any way to get the first sheet, as of the ordering in the file?

Original comment by robin.ho...@gmail.com on 14 Mar 2014 at 1:02

Attachments:

GoogleCodeExporter commented 8 years ago
PS: It looks like this is impossible with OleDb:
https://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names
-in-sheet-order

Original comment by robin.ho...@gmail.com on 14 Mar 2014 at 1:19

GoogleCodeExporter commented 8 years ago
Yes, perhaps for you DocumentFormat.OpenXML is better choice.
Here is an example.

I used following namespaces:

 DocumentFormat.OpenXml
 DocumentFormat.OpenXml.Office.Excel
 DocumentFormat.OpenXml.Packaging
 DocumentFormat.OpenXml.Spreadsheet

And following code:

===

using (SpreadsheetDocument spreadsheetDocument = 
SpreadsheetDocument.Open(strFullPath,false))
    {               
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        List<Sheet> lstSheets = workbookPart.Workbook.Descendants<Sheet>()
                                    .OrderBy (d => d.SheetId.Value)
                                    .ToList();
        foreach (Sheet sheet in lstSheets)
        {
            Console.WriteLine(sheet.Name.ToString());
        }
    }

===

/* Result */
sheetone
sheetnumbertwo

See if works for you.

Regards.

Original comment by fwd...@gmail.com on 14 Mar 2014 at 3:23