How to handle optional columns in user import - empty column throws error. #9649

2 Answer(s)

• 

  0

  ismcagdas created 4 years ago

  Support Team

  Hi @marble68

  This is related to Excel library we are using. It is better to handle it on the user side. We couldn't find a solution until now but if you can create an issue on GitHub, we can take a look at this again. Maybe we can replace the library we are using.

  Thanks,

  Save Cancel
• 

  0

  marble68 created 4 years ago

  Hi @ismcagdas - I found a solution using the existing library.

  Basically - the NPOI library has a nuance with how you get cells.

  The nuance manifests in .cell[num] vs .GetCell[num] after GetRow.

  The Cells collection simply drops empty cells.

  Thus if you have an empty phone number field in the fourth column, the 5th column becomes the 4th column, and the import fails because it tries to read the column at the end and it doesn't exist.

  In your code, worksheet.GetRow(row).Cells[4] is used.

  However, if one were to do worksheet.GetRow(row).GetCall(4), in the above example, it returns a null.

  Thus, my solution was to use simple counters for the order of the cells, and handle thusly:

                  int colCheck = 0;
                  int cellIndex = 0;
                  if (worksheet.GetRow(row).GetCell(colCheck) ==null) throw new System.ArgumentException("User Name is required");
                  user.UserName = GetRequiredValueFromRowOrNull(worksheet, row, cellIndex, nameof(user.UserName), exceptionMessage);
                  cellIndex++;
                  colCheck++;
  
                  if (worksheet.GetRow(row).GetCell(colCheck) == null) throw new System.ArgumentException("First Name is required");
                  user.Name = GetRequiredValueFromRowOrNull(worksheet, row, cellIndex, nameof(user.Name), exceptionMessage);
                  cellIndex++;
                  colCheck++;
  
                  if (worksheet.GetRow(row).GetCell(colCheck) == null) throw new System.ArgumentException("Last Name is required");
                  user.Surname = GetRequiredValueFromRowOrNull(worksheet, row,cellIndex, nameof(user.Surname), exceptionMessage);
                  cellIndex++;
                  colCheck++;
  
                  if (worksheet.GetRow(row).GetCell(colCheck) == null) throw new System.ArgumentException("Email is required");
                  user.EmailAddress = GetRequiredValueFromRowOrNull(worksheet, row, cellIndex, nameof(user.EmailAddress), exceptionMessage);
                  cellIndex++;
                  colCheck++;
  
                  // TODO Handle no phone number
                  if (worksheet.GetRow(row).GetCell(colCheck) != null)
                  {
                      worksheet.GetRow(row).Cells[cellIndex].SetCellType(CellType.String);
                      user.PhoneNumber = worksheet.GetRow(row).Cells[cellIndex]?.StringCellValue;
                      cellIndex++;
  
                  }
                  colCheck++;
  
                  if (worksheet.GetRow(row).GetCell(colCheck) == null) throw new System.ArgumentException("A valid password is required");
                  user.Password = GetRequiredValueFromRowOrNull(worksheet, row, cellIndex, nameof(user.Password), exceptionMessage);
                  cellIndex++;
                  colCheck++;
  
                  if (worksheet.GetRow(row).GetCell(colCheck) != null)
                  {
                      user.AssignedRoleNames = GetAssignedRoleNamesFromRow(worksheet, row, cellIndex);
                      cellIndex++;
                  }
                  colCheck++;
  

  I've not refactored the code to be more elegant - but what this does is assume the column values are retreived in order.

  Using GetCell, I am able to determine if a column has a value (not null). If it does, I increment the cell being examined (cellIndex) and column I'm checking (colCheck) and continue.

  If the column is NULL after getcell, I know the cell has no value. In this case, I increment the column (colCheck) being examined, but do NOT increment the cells index (cellIndex) , since the cells array collapses empty values.

  Hope this helps.

  A more elegant approach maybe reconstruct the Cells array with a linq statement into a "rowvalues" array, or use the GetCell method.

  While not perfect, my current approach is simple, and allows me to tinker while I work up my solution.

  Save Cancel