CODEWITHSUNDEEP
c#excelopenxml
Papi
Papi

Reputation: 555

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

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.

Here is my Excel sheet template

Upvotes: 0

Views: 2001

Answers (1)

A3006
A3006

Reputation: 1079

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

Upvotes: 2

Related Questions