none
How to refer to first sheet of excel? RRS feed

  • Question

  • Known: Only one sheet in Workbook, how can I re-write below code to include something like Sheets(1) , I mean the syntax referring to the only sheet of workbook (without knowing sheetname).

    Thanks.

    OleDbCommand("Select * From [" + sheetname + "$]", con);

    Thursday, March 21, 2019 3:02 AM

Answers

  • You can get the sheetnames with code here. If that workbook has only one sheet, it should work well.

    If the workbook has multiple sheets, as mentioned in the same thread since OLEDB does not maintain sheet order, it's not possible to know whether certain sheet is the first or not.

    Thursday, March 21, 2019 3:50 AM
    Answerer
  • Hi VA_er,

    If you want to fetch the records from the first sheet into datatable, there is easy way to achieve it by   ExcelDataReader. like this:

    using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
    {
        // Auto-detect format, supports:
        //  - Binary Excel files (2.0-2003 format; *.xls)
        //  - OpenXml Excel files (2007 format; *.xlsx)
        using (var reader = ExcelReaderFactory.CreateReader(stream))
        {
            
            // Use the AsDataSet extension method
            var firsttable = reader.AsDataSet().Tables[0];
    
        }
    }


    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 21, 2019 7:51 AM
    Moderator

All replies

  • You can get the sheetnames with code here. If that workbook has only one sheet, it should work well.

    If the workbook has multiple sheets, as mentioned in the same thread since OLEDB does not maintain sheet order, it's not possible to know whether certain sheet is the first or not.

    Thursday, March 21, 2019 3:50 AM
    Answerer
  • Hi VA_er,

    If you want to fetch the records from the first sheet into datatable, there is easy way to achieve it by   ExcelDataReader. like this:

    using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
    {
        // Auto-detect format, supports:
        //  - Binary Excel files (2.0-2003 format; *.xls)
        //  - OpenXml Excel files (2007 format; *.xlsx)
        using (var reader = ExcelReaderFactory.CreateReader(stream))
        {
            
            // Use the AsDataSet extension method
            var firsttable = reader.AsDataSet().Tables[0];
    
        }
    }


    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 21, 2019 7:51 AM
    Moderator