Access row data of excel.

Not the most ideal way but some basic implementation (Open XML SDK) would help in your case.

Only change you need to make is , instead of printing data to console, try creating models to hold these properties from excel and store it in list.

Also, try to make some null checks for empty columns (if cell is modified with no values in it).

public class ReadExcelFile
{
public ReadExcelFile(string FileName)
{
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(FileName, false)) // false, to disable editing of this file
{
WorkbookPart wbPart = spreadSheetDocument.WorkbookPart;
IEnumerable sheets = wbPart.Workbook.GetFirstChild().Elements();
string id = sheets.ElementAt(0).Id.Value; // zero based index for number of sheets
WorksheetPart wshPart = (WorksheetPart)wbPart.GetPartById(id);
SheetData shData = wshPart.Worksheet.GetFirstChild();
IEnumerable rows = shData.Descendants();
foreach (Row row in rows)
{
foreach (Cell cell in row.Descendants())
{
Console.Write($"{GetCellValue(wbPart, cell)}\t");
}
Console.WriteLine();
}
}
}
private string GetCellValue(WorkbookPart wbPart, Cell cell)
{
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return wbPart.SharedStringTablePart.SharedStringTable.ElementAt(Int32.Parse(cell.CellValue.InnerXml)).InnerText;
}
else
return cell.CellValue.InnerXml;
}
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
Accepted answer
Not the most ideal way but some basic implementation (Open XML SDK) would help in your case.

Only change you need to make is , instead of printing data to console, try creating models to hold these properties from excel and store it in list.

Also, try to make some null checks for empty columns (if cell is modified with no values in it).
public class ReadExcelFile
{
    public ReadExcelFile(string FileName)
    {
        using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(FileName, false)) // false, to disable editing of this file
        {
            WorkbookPart wbPart = spreadSheetDocument.WorkbookPart;
            IEnumerable sheets = wbPart.Workbook.GetFirstChild().Elements();
            string id = sheets.ElementAt(0).Id.Value; // zero based index for number of sheets
            WorksheetPart wshPart = (WorksheetPart)wbPart.GetPartById(id);
            SheetData shData = wshPart.Worksheet.GetFirstChild();
            IEnumerable rows = shData.Descendants();

            foreach (Row row in rows)
            {
                foreach (Cell cell in row.Descendants())
                {
                    Console.Write($"{GetCellValue(wbPart, cell)}\t");
                }
                Console.WriteLine();
            }


        }
    }

    private string GetCellValue(WorkbookPart wbPart, Cell cell)
    {
        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            return wbPart.SharedStringTablePart.SharedStringTable.ElementAt(Int32.Parse(cell.CellValue.InnerXml)).InnerText;
        }
        else
            return cell.CellValue.InnerXml;
    }
}
Shantanu Dhole9y ago
what if I want to do it with oledb.

I have excel data in this fromat. From website I getting these Id not same at all time. I want to get these mail id for particular emp id in C# function.