Open XML Reading Excel file does not enter loop to read excel sheet

Question

I have a scenario where I need to read Excel file in an MVC Application, I need this to run on the server, therefore I am using Open XML, I have a issue where my code does not enter the loop of rows in the sheet, please see my code below and advice on how I can rectify my code.

if (file.ContentLength > 0)
{
    string path = file.FileName;    
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
    {
        WorkbookPart workbookPart = doc.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
        SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

        foreach (Row r in sheetData.Elements<Row>())
        {
            foreach (Cell c in r.Elements<Cell>())
            {
                string text = c.CellValue.Text;
            }
        }
    }
}

Any ideas, your help will be greatly appreciated, I have been trying multiple approaching but I am not getting in the foreach loop fr some odd reason.

I am using Excel 2013 and please see below image of my workbook.

Kolky · Accepted Answer · 2016-04-22 07:35:55Z

2

I have been using following code which works fine for me.

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(sFileNameWithPath, false))
{
    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
    WorksheetPart worksheetPart = GetWorksheetPart(workbookPart, sSheetName);

    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

    bool bHasChildren = sheetData.HasChildren;
    if (bHasChildren)
    {
        for (int iCounter1 = 1; iCounter1 < sheetData.Elements<Row>().Count(); iCounter1++)
        {
            Row rDataRow = sheetData.Elements<Row>().ElementAt(iCounter1);
            for (int iCounter = 0; iCounter < rDataRow.ChildElements.Count; iCounter++)
            {
                Cell oCell = (Cell)rDataRow.ChildElements[iCounter];
            }
        }
    }
}

Let me know if this helps.

Or you can use your code with following change

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(sFileNameWithPath, false))
{
    WorkbookPart workbookPart = doc.WorkbookPart;

    string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => "Claims".Equals(s.Name)).Id;
    WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(relId);

    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

    foreach (Row r in sheetData.Elements<Row>())
    {
        foreach (Cell c in r.Elements<Cell>())
        {
            string text = c.CellValue.Text;
        }
    }
}

Note that I have used the excel sheet name "Claims", so check whether it works and if yes; put it in another function to make it generic

edited Apr 22, 2016 at 7:35

Kolky

2,9771 gold badge22 silver badges43 bronze badges

answered Apr 1, 2016 at 10:18

A3006

1,0911 gold badge12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Papi Over a year ago

Its still not working, its doesn't enter the for loop, also where is the GetWorksheetPart(workbookPart, sSheetName); method defined? I have tried many different approaches,no luck. The HasChildrenSheets retuns false, therefore it skips the loop, if I remove the HasChildrenSheets code, it enters the expression but not the code in the loop

A3006 Over a year ago

That function is nothing, but returns the worksheet private static WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, string sheetName) { string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => sheetName.Equals(s.Name)).Id; return (WorksheetPart)workbookPart.GetPartById(relId); }

Papi Over a year ago

I am still battling to enter the loop, I have no idea why

A3006 Over a year ago

What is the version on Excel? Also is it possible to share the excel template (may be with junk data? Because the code above works well for me...

Papi Over a year ago

I am using Excel 2013, I have also updated post above and added excel template

|

Collectives™ on Stack Overflow

Open XML Reading Excel file does not enter loop to read excel sheet

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related