none
Sequence contains no elements error when reading an Excel file using DocumentFormat.OpenXml

    Question

  • From all the code samples I reveiwed it looks simple enough to load an excel file and read the data. All I want is the first Row so that I can determine the names of the columns ....

    I create a 3 column, 3 row spread sheet using Excel 2007 and saved as an .xlsx file

    The file loads, but all code to retrieve any Row or Column or Cell fails with the above error

    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
    {
      WorkbookPart workbook = doc.WorkbookPart;
      WorksheetPart worksheet = workbook.WorksheetParts.First();  // first work sheet 
      SheetData sheetdata = worksheet.Worksheet.Elements<SheetData>().First();
    
      foreach (Row r in sheetdata.Elements<Row>())
      {
           Cell c = r.Elements<Cell>().First();
           txt = GetColumnName(c.CellValue.Text);
      }
    
      Row r1 = worksheet.Worksheet.Descendants<Row>().FirstOrDefault();

    In the above code ... the foreach Row ... is empty .... there are no rows?

    and the Row r1 is null ?

    my spread sheet data:

    Barcode volume Pos Id
    12345 10.5 A1
    AAdd3 56 B1

    Tracey

    • Moved by Mike Feng Monday, February 11, 2013 6:25 AM
    Friday, February 08, 2013 11:40 PM

Answers

  • HI Tracey

    THe problem is that you're assuming "Sheet1" is the first sheet Open XML sees as the first sheet. That's a dangerous assumption and it turns out that this is not the case. What you need to do is get the rId value for the sheet you want to query and look up the worksheet part in workbook.xml.rels (here you'll also see the order of sheets Open XML is using).

    Of course, the Open XML SDK takes care of the "looking it up", but you do have to provide the ID. Here's some sample code to illustrate. I also recommend you use FirstOrDefault(), not just First() as this could cause issues if there is no corresponding object. (Note: I've fully qualified a lot of stuff with DocumentFormat.OpenXML.Spreadsheet because I didn't want to add a "using" for that namespace in my test project. You can leave that out.)

    private void btnReadExcel_Click(object sender, EventArgs e)
    {
        string path = @"C:\Test\01_ParseXL.xlsx";
        string txt = String.Empty;
        this.txtMessages.Text = txt;
        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
        {
            WorkbookPart bkPart = doc.WorkbookPart;
            DocumentFormat.OpenXml.Spreadsheet.Workbook workbook = bkPart.Workbook;
            DocumentFormat.OpenXml.Spreadsheet.Sheet s = workbook.Descendants<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Where(sht => sht.Name == "Sheet1").FirstOrDefault();
            WorksheetPart wsPart = (WorksheetPart)bkPart.GetPartById(s.Id);
            DocumentFormat.OpenXml.Spreadsheet.SheetData sheetdata = wsPart.Worksheet.Elements<DocumentFormat.OpenXml.Spreadsheet.SheetData>().FirstOrDefault();
    
            foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetdata.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>())
            {
                DocumentFormat.OpenXml.Spreadsheet.Cell c = r.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().First();
                txt += c.CellValue.Text + Environment.NewLine;
            }
            this.txtMessages.Text += txt;
        }
    
    }


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by Tracey Macias Monday, February 11, 2013 4:02 PM
    Monday, February 11, 2013 8:27 AM
    Moderator

All replies

  • Hi Tracey,

    Welcome to the MSDN Forum.

    I have moved this thread to a dedicated forum for more responses.

    Thank you.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 11, 2013 6:25 AM
  • HI Tracey

    THe problem is that you're assuming "Sheet1" is the first sheet Open XML sees as the first sheet. That's a dangerous assumption and it turns out that this is not the case. What you need to do is get the rId value for the sheet you want to query and look up the worksheet part in workbook.xml.rels (here you'll also see the order of sheets Open XML is using).

    Of course, the Open XML SDK takes care of the "looking it up", but you do have to provide the ID. Here's some sample code to illustrate. I also recommend you use FirstOrDefault(), not just First() as this could cause issues if there is no corresponding object. (Note: I've fully qualified a lot of stuff with DocumentFormat.OpenXML.Spreadsheet because I didn't want to add a "using" for that namespace in my test project. You can leave that out.)

    private void btnReadExcel_Click(object sender, EventArgs e)
    {
        string path = @"C:\Test\01_ParseXL.xlsx";
        string txt = String.Empty;
        this.txtMessages.Text = txt;
        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
        {
            WorkbookPart bkPart = doc.WorkbookPart;
            DocumentFormat.OpenXml.Spreadsheet.Workbook workbook = bkPart.Workbook;
            DocumentFormat.OpenXml.Spreadsheet.Sheet s = workbook.Descendants<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Where(sht => sht.Name == "Sheet1").FirstOrDefault();
            WorksheetPart wsPart = (WorksheetPart)bkPart.GetPartById(s.Id);
            DocumentFormat.OpenXml.Spreadsheet.SheetData sheetdata = wsPart.Worksheet.Elements<DocumentFormat.OpenXml.Spreadsheet.SheetData>().FirstOrDefault();
    
            foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetdata.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>())
            {
                DocumentFormat.OpenXml.Spreadsheet.Cell c = r.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().First();
                txt += c.CellValue.Text + Environment.NewLine;
            }
            this.txtMessages.Text += txt;
        }
    
    }


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by Tracey Macias Monday, February 11, 2013 4:02 PM
    Monday, February 11, 2013 8:27 AM
    Moderator