title: Closed XML Reading in a table Through Empty Rows
body: <p>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.</p>
  
  <pre><code>public ActionResult Upload(UploadFile UploadFile)
      {
          if (ModelState.IsValid)
          {
  
              if (UploadFile.ExcelFile.ContentLength &gt; 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();
      }
  </code></pre>
  
  <p>Exception:
  <a href="https://i.stack.imgur.com/habhR.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/habhR.jpg" alt="enter image description here"></a>
  Format of Excel Doc:
  <a href="https://i.stack.imgur.com/GrB6v.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GrB6v.jpg" alt="enter image description here"></a>
  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</p>