Having Issue while reading a excel file with .xls extension

[Sivaprasadnelli]

Hello team,

I have a query to ask. We are using NPOI Library to read .xls and .xlsx files in our .Net Core application. While we are uploading a .xls file and there is one empty cell in a single row but while reading the rows it is not considering the empty cell even though we are trying to check for the null or empty values. Could you let know is it expecting behavior?

// Iterate through rows and cells to read data from Excel file for (int i = 0; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row != null) { // Check if all cells in the row are empty or blank using TrueForAll if (Enumerable.Range(0, row.Cells.Count).All(index => row.Cells[index].CellType == CellType.Blank)) { continue; // Skip the row if all cells are empty } ICell cell = null; try { foreach (ICell currentCell in row.Cells) { if (string.IsNullOrEmpty(currentCell.ToString().Trim())) { throw new InvalidDataException(INVALID_DATA_UPLOADED); }
} lines.Add(row.Cells.Select(cell => cell.ToString()).ToArray()); } catch (Exception ex) { throw new Exception(); } } }

[Sivaprasadnelli]

Can Someone please help me here. It is bit urgent for us.

[Bykiev]

In your sample I don't see the check for null or blank cell

currentCell == null || currentCell.CellType == CellType.Blank

[Sivaprasadnelli]

Hello,

I couldn’t see the cell data while fetching row itself. I mean for example a row contains 6 cells and 1 cell is empty then we are getting only 5 cells data instead of 6 cells.

That’s why we couldn’t able to find the empty cell.

Is there anything you can do?

Thanks Siva.

On Thu, 14 Mar 2024 at 2:17 PM, Andrey Bykiev @.***> wrote:In your sample I don't see the check for null or blank cell

— Reply to this email directly, view it on GitHub https://github.com/nissl-lab/npoi/issues/1294#issuecomment-1996887604, or unsubscribe https://github.com/notifications/unsubscribe-auth/A2XBU4HULEYC47DENCZOT6LYYFP3TAVCNFSM6AAAAABEVTHCW2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOJWHA4DONRQGQ . You are receiving this because you authored the thread.Message ID: @.***>

[Bykiev]

I believe your code should be looking like this:

// Iterate through rows and cells to read data from Excel file
for (int i = 0; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
     // Check if all cells in the row are empty or blank using TrueForAll
     if (Enumerable.Range(0, row.Cells.Count).All(index => row.Cells[index].CellType == CellType.Blank))
     {
          continue; // Skip the row if all cells are empty
     }

     for (int i = 0; i < row.Cells.Count; i++) // possibly you should use fixed columns number
     {
          var currentCell = row.GetCell(i);

           if (currentCell == null || currentCell.CellType == CellType.Blank)
                throw new InvalidDataException(INVALID_DATA_UPLOADED);
      }
     lines.Add(row.Cells.Select(cell => cell.ToString()).ToArray());
}
}