Closed XML Reading in a table Through Empty Rows

Question

I have a method that reads from a spreadsheet to create an Auction for my application. I am getting the exception below, and I was wondering why the range of my sheet was set to that and then how to change the range so that I can read the rest of my excel file.

public ActionResult Upload(UploadFile UploadFile)
    {
        if (ModelState.IsValid)
        {

            if (UploadFile.ExcelFile.ContentLength > 0)
            {
                if (UploadFile.ExcelFile.FileName.EndsWith(".xlsx") || UploadFile.ExcelFile.FileName.EndsWith(".xls"))
                {
                    XLWorkbook wb;
                    //incase if the file is corrupt
                    try
                    {
                        wb = new XLWorkbook(UploadFile.ExcelFile.InputStream);
                    }
                    catch (Exception ex)
                    {
                        ModelState.AddModelError(String.Empty, $"Check your file. {ex.Message}");
                        return View();
                    }
                    IXLWorksheet ws = null;
                    try//incase if the sheet you are looking for is not found
                    {
                        ws = wb.Worksheet("sheet1");

                    }
                    catch
                    {
                        ModelState.AddModelError(String.Empty, "Sheet not found");
                        return View();
                    }
                    var firstRowUsed = ws.FirstRowUsed();
                    var auctionRow = firstRowUsed.RowUsed().RowBelow();

                    //create auction
                    string auctionName = auctionRow.Cell(1).Value.ToString();
                    DateTimeOffset startDate = DateTimeOffset.Parse(auctionRow.Cell(2).Value.ToString());
                    DateTimeOffset endDate = DateTimeOffset.Parse(auctionRow.Cell(3).Value.ToString());
                    string folderName = auctionRow.Cell(4).Value.ToString();

                    Models.Auction auction = new Models.Auction(auctionName, startDate, endDate, folderName);
                    db.Auctions.Add(auction);


                    //find the next table
                    var nextRow = auctionRow.RowBelow();
                    while (nextRow.IsEmpty())
                    {
                        nextRow = nextRow.RowBelow();
                    }

                    const int catNameCol = 1;
                    var firstCatRow = nextRow.RowUsed();
                    var catRow = firstCatRow.RowBelow();

                    //get categories from ws table and add to the auction
                    while (!catRow.Cell(catNameCol).IsEmpty())
                    {
                        string catName = catRow.Cell(1).Value.ToString();
                        int seqNo = Convert.ToInt32(catRow.Cell(2).Value.ToString());
                        string fileName = catRow.Cell(3).Value.ToString();

                        Cat cat = new Cat(auction.AuctionId, catName, seqNo, fileName);
                        auction.Cats.Add(cat);

                        catRow = catRow.RowBelow();
                    }

                    var findNextRow = catRow.RowBelow();
                    while (findNextRow.IsEmpty())
                    {
                        findNextRow = findNextRow.RowBelow();
                    }
                    const int itemNameCol = 1;

                    var itemRow = findNextRow.RowUsed().RowBelow();
                    while (!itemRow.Cell(itemNameCol).IsEmpty())
                    {
                        string itemName = itemRow.Cell(1).Value.ToString();
                        string itemDesc = itemRow.Cell(2).Value.ToString();
                        string catName = itemRow.Cell(3).Value.ToString();
                        string modelNo = itemRow.Cell(4).Value.ToString();
                        decimal retailValue = Convert.ToDecimal(itemRow.Cell(5).Value.ToString());
                        string fileName = itemRow.Cell(6).Value.ToString();
                        decimal initialBid = Convert.ToDecimal(itemRow.Cell(7).Value.ToString());
                        decimal increment = Convert.ToDecimal(itemRow.Cell(8).Value.ToString());
                        Cat itemCat = null;

                        foreach (var cat in auction.Cats)
                        {
                            if (catName == cat.CatName.Trim())
                            {
                                itemCat = cat;
                            }
                        }

                        Item item = new Item(itemName, itemDesc, modelNo, retailValue, fileName, startDate, endDate, initialBid, increment, null, null, null, itemCat);
                        itemCat.Items.Add(item);

                        itemRow = itemRow.RowBelow();
                    }
                }
                else
                {
                    ModelState.AddModelError(String.Empty, "Only .xlsx and .xls files are allowed");
                    return View();
                }
            }
            else
            {
                ModelState.AddModelError(String.Empty, "Not a valid file");
                return View();
            }
        }
        db.SaveChanges();
        return View();
    }

Exception: Format of Excel Doc: I am really new to this so let me know if there is some syntax to accomplish what I am trying to and fix this exception. Also let me know if there are anything that sticks out that I can change/improve. Thank you kindly

Answer 1

nextRow is a truncated row "C15: F15", and the data you are looking for is greater than this range. I think that there is also a ClosedXML error here. In your case, it helps to take the entire row of the worksheet and then get used cells. Try next:

var firstCatRow = nextRow.WorksheetRow().RowUsed();