How to read cell value from excel file using ClosedXML

Question

        string TablePath = Storage.ProjectPath + "\\Tables\\";
        string ObjectRepPath = TablePath + "ObjectRepository.xlsx";

        string locType = " ";
        string locParm = " ";

        var workbook  = new XLWorkbook(ObjectRepPath);
        var ws1       = workbook.Worksheet(1).RangeUsed().RowsUsed().Skip(1);
        var totalRows = ws1.Count();
        var row       = ws1.Row;

        for (int rCnt = 1; rCnt <= totalRows; rCnt++)
        {
            var objPage    = ws1.Cell(rCnt, 0).Value;
            var objElement = ws1.Cell(rCnt, 1).Value;


            if (objPage == page && objElement == element)
            {
                locType = ws1.Cell(rCnt, 2);
                locParm = ws1.Cell(rCnt, 3);
            }
        }

I'm attempting to use ClosedXML to read from an excel file. Every reference I've found says you can use Worksheet.Cell(r,c), but it doesn't contain a definition for .Cell. Neither is there one for .Row. I've tried including 'using' statements for everything under ClosedXML, but that didn't work either. What am I missing?

Error given ... 'IEnumerable[IXLRangeRow]' does not contain a definition for 'Cell' and no extension method 'Cell' accepting a first argument of type 'IEnumerable[IXLRangeRow]' could be found (are you missing a using directive or an assembly reference?)

Error on both ws1.Cell and ws1.Row

References that support this should work ... - Reading from Excel File using ClosedXML - https://github.com/closedxml/closedxml/wiki/Cell-Values

Do you encounter a specific (compiler) error? If so, please provide it in the question. If not please describe your problem more specifically, because at the moment it is very unclear. Thanks! — cramopy, Aug 10 '18 at 16:38
`ws1` is a sequence of `IXLRangeRow` which does not have a `Row` property. Likewise, the individual Rows in the sequence will have `Cell`, but the sequence does not. — Crowcoder, Aug 10 '18 at 17:47
Okay, so are you saying it's because I specified .RangeUsed().RowsUsed in defining my worksheet? In the references it shows .... var ws = workbook.Worksheets.Add("Cell Values"); var cellString = ws.Cell(6, 2); — HeadlyvonNoggin, Aug 10 '18 at 18:07
@HeadlyvonNoggin In the reference example `ws` is a worksheet, your `ws1` is not a worksheet. I don't know the API well but what you have obtained is an `IEnumerable[IXLRangeRow]`. — Crowcoder, Aug 10 '18 at 18:37
@Crowcoder Alright. I think that gives me enough information to refine my research on the subject. Thank you — HeadlyvonNoggin, Aug 10 '18 at 18:51

score 6 · Answer 1 · answered Aug 10 '18 at 19:19

Okay, found a solution. Thank you to @Crowcoder for the guidance...

        var workbook = new XLWorkbook(ObjectRepPath);
        var rows     = workbook.Worksheet(1).RangeUsed().RowsUsed().Skip(1);

        foreach (var row in rows)
        {
            var rowNumber  = row.RowNumber();

                objPage    = row.Cell(1).GetString();
                objElement = row.Cell(2).GetString();

            if (objPage == page && objElement == element)
            {
                locType = row.Cell(3).GetString();
                locParm = row.Cell(4).GetString();
            }
        }

How to read cell value from excel file using ClosedXML

1 Answers1