Return cell with value not null using linq - Epplus

Question

I'm using the Linq code below to return the last filled cell in my row. However, I do not know for what reason (maybe some formatting, or something of the sort) cell with null value is being returned. Example

My row starts at B16 and goes to AG16, the code in thesis should return AG16, but this is returning AI16, and this cell is blank, has no value, formula, nothing.

I would like to ignore cells with the Blank Value, so to return AG16.

I'm doing according to the code below, which returns AI16 even though it has no value

var lastRowCellValue = worksheet.Cells.Last(c => c.End.Row == 16)

16 is the line row that he will check

I have already tried to use the code below, but it returns NULL instead of AG16, which is my last value cell

 var lastRowCellValue = worksheet.Cells.Last(c => c.End.Row == 16).Where(c => c.Value != null);

What am I doing wrong?

Instead of `.. c.Value != null)` try `.. c.Value != DBNull.Value)` — Pikoh, Mar 13 '17 at 14:43
@Pikoh The return was AI16. I honestly do not know what happens because this cell is totally empty, I do not know why, it is returned. — G. Sena, Mar 13 '17 at 14:45
Then maybe you have two empty lines..check for `c.Value != DBNull.Value || c.Value != String.Empty)` — Pikoh, Mar 13 '17 at 14:46
@Pikoh Still returning AI16. It's really weird, I do not know why, he's conning this cell — G. Sena, Mar 13 '17 at 14:51
What is the value? Literally? Maybe we have something like a crlf or some such? — Trey, Mar 13 '17 at 14:57
@Trey In Linq the AI16 cell is with the Null Value. In AG16 the Value cell is a string....Is that what you would like to know? — G. Sena, Mar 13 '17 at 15:05

score 3 · Accepted Answer · edited May 23 '17 at 12:00

3

Assuming that the issue is similar to this, with empty cells or formatting causing the problem, you could use a function derived from this answer:

int LastColumnByRow(ExcelWorksheet sheet, int rownum) {
    var col = sheet.Dimension.End.Column;

    while (col >= 1) {
        if (!string.IsNullOrEmpty(sheet.Cells[rownum, col].Text)) {
            break;
        }
        col--;
    }
    return col;
}

Called like so:

var lastRowCellValue = worksheet.Cells[16, LastColumnByRow(ws, 16)];

edited May 23 '17 at 12:00

Community

1
1

answered Mar 13 '17 at 15:19

Chawin

1,438
1
21
33

This really seems to be a path, but I tested it here and an exception occurs. "Column out of range" . The function return is 0 I tried to change some things but without success – G. Sena Mar 13 '17 at 15:34
@G.Sena apologies, I left the row set as 2 from my testing, try using it with the parameter of 16. – Chawin Mar 13 '17 at 15:40
Yes, I did the test using row 16, but the return is the same. 0 Would not it be due to the While doing until it reaches 0? That this decreases the variable to 0? – G. Sena Mar 13 '17 at 15:44
@G.Sena are you able to upload the file you're using, but remove any confidential information? The function runs fine against a new workbook with information populated in row 16. – Chawin Mar 13 '17 at 15:46
1

Sorry man, it was my mistake .. it's working perfectly. AG16 returned. Thank you very much – G. Sena Mar 13 '17 at 15:58
No worries, glad I could help! – Chawin Mar 13 '17 at 15:59

Return cell with value not null using linq - Epplus

1 Answers1