Cham
Reputation: 857
Validate Excel(Spreadsheet) File Using EPPlus
Scenario: have an excel sheet which contains user data. Need to validate the excel sheet before inserting(if new) or updating(if existing) any user data in the database. If any cell has incorrect data or empty data, that cell will be highlighted with red and has a comment explaining the error.
e.g: email is not in correct format
Upvotes: 3
Views: 6910
Answers (1)
Cham
Reputation: 857
I thought of posting this as a solution, so that this will help to anyone having trouble validating excel sheet using EPPlus.
EPPlus version: 4.5.2.1
Following is the excel sheet structure.
Following is the full code to validate the excel sheet.
[HttpPost]
public ActionResult BulkUserUpload(HttpPostedFileBase uploadFile)
{
string tempFileName = string.Empty;
string errorMessage = string.Empty;
if (uploadFile != null && uploadFile.ContentLength > 0)
{
//ExcelExtension contains array of excel extension types
if ( Config.ExcelExtension.Any(p => p.Trim() == Path.GetExtension(uploadFile.FileName)) )
{
//save the uploaded excel file to temp location
SaveExcelTemp(uploadFile, out tempFileName);
//validate the excel sheet
if (ValidateExcel(tempFileName, out errorMessage))
{
//save the data
SaveExcelDataToDatabase(tempFileName);
//spreadsheet is valid, show success message or any logic here
}
else
{
//set error message to shown in front end
ViewBag.ErrorMessage = errorMessage;
}
}
else
{
//excel sheet is not uploaded, show error message
}
}
else
{
//file is not uploaded, show error message
}
return View();
}
private bool ValidateExcel(string tempFileName, out string errorMessage)
{
bool result = true;
string error = string.Empty;
string filePath = GetTempFilePath(tempFileName);
FileInfo fileInfo = new FileInfo(filePath);
ExcelPackage excelPackage = new ExcelPackage(fileInfo);
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.First();
int totalRows = worksheet.Dimension == null ? -1 : worksheet.Dimension.End.Row; //worksheet total rows
int totalCols = worksheet.Dimension == null ? -1 : worksheet.Dimension.End.Column; // total columns
//check spread sheet has rows (empty spreadsheet uploaded)
if (totalRows == -1)
{
result = false;
error = "Empty spread sheet uploaded.";
}
//check rows are more than or equal 2 (spread sheet has only header row)
else if (totalRows < 2)
{
result = false;
error = "Spread sheet does not contain any data";
}
//check total columns equal to headers defined (less columns)
else if (totalCols > 0 && totalCols != GetColumnHeaders().Count)
{
result = false;
error = "Spread sheet column header value mismatch.";
}
if (result)
{
//validate header columns
result &= ValidateColumns(worksheet, totalCols);
if (result)
{
//validate data rows, skip the header row (data rows start from 2)
result &= ValidateRows(worksheet, totalRows, totalCols);
}
if (!result)
{
error = "There are some errors in the uploaded file. Please correct them and upload again.";
}
}
errorMessage = error;
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
excelPackage.Save();
return result;
}
Following are the validation methods
private bool SetError(ExcelRange cell, string errorComment)
{
var fill = cell.Style.Fill;
fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.Red);
cell.AddComment(errorComment, "");
return false;
}
private bool ValidateHeaderColumns(ExcelWorksheet worksheet, int totlaColumns)
{
bool result = true;
List<string> listColumnHeaders = GetColumnHeaders();
for (int i = 1; i < totlaColumns; i++)
{
var cell = worksheet.Cells[1, i]; //header columns are in first row
if (cell.Value != null)
{
//column header has a value
if (!listColumnHeaders.Contains(cell.Value.ToString()))
{
result &= SetError(cell, "Invalid header. Please correct.");
}
}
else
{
//empty header
result &= SetError(cell, "Empty header. Remove the column.");
}
}
return result;
}
private bool ValidateRows(ExcelWorksheet worksheet, int totalRows, int totalCols)
{
bool result = true;
for (int i = 2; i <= totalRows; i++) //data rows start from 2`
{
for (int j = 1; j <= totalCols; j++)
{
var cell = worksheet.Cells[i, j];
switch (j)
{
//email address
case 1:
{
result &= ValidateEmailAddress(cell, "Email address");
break;
}
//first name
case 2:
{
result &= ValidateText(cell, "First name");
break;
}
//last name
case 3:
{
result &= ValidateText(cell, "Last name");
break;
}
//address line 1
case 4:
{
result &= ValidateText(cell, "Address line 1");
break;
}
//address line 2
case 5:
{
result &= ValidateText(cell, "Address line 2");
break;
}
//city
case 6:
{
result &= ValidateText(cell, "City");
break;
}
//telephone number
case 7:
{
result &= ValidateText(cell, "Telephone number");
break;
}
//mobile number
case 8:
{
result &= ValidateText(cell, "Mobile number");
break;
}
//job title
case 9:
{
result &= ValidateJobTitle(cell, "Job title");
break;
}
//salary
case 10:
{
result &= ValidateNumber(cell, "Salary");
break;
}
//role
case 11:
{
result &= ValidateRole(cell, "Role");
break;
}
//branch
case 12:
{
result &= ValidateBranch(cell, "Branch");
break;
}
//joined date
case 13:
{
result &= ValidateDate(cell, "Joined date");
break;
}
}
}
}
return result;
}
private bool ValidateEmailAddress(ExcelRange cell, string columnName)
{
bool result = true;
result = ValidateText(cell, columnName); //validate if empty or not
if (result)
{
if (!ValidateEmail(cell.Value.ToString())) //ValidateEmail => true, if email format is correct
{
result = SetError(cell, "Email address format is invalid.");
}
else if (cell.Value.ToString().Length > 150)
{
result = SetError(cell, "Email address too long. Max characters 150.");
}
}
return result;
}
private bool ValidateText(ExcelRange cell, string columnName)
{
bool result = true;
string error = string.Format("{0} is empty", columnName);
if (cell.Value != null)
{
//check if cell value has a value
if (string.IsNullOrWhiteSpace(cell.Value.ToString()))
{
result = SetError(cell, error);
}
}
else
{
result = SetError(cell, error);
}
return result;
}
private bool ValidateNumber(ExcelRange cell, string columnName)
{
bool result = true;
double value = 0.0;
string error = string.Format("{0} format is incorrect.", columnName);
result = ValidateText(cell, columnName);
if (result)
{
if (!double.TryParse(cell.Value.ToString(), out value))
{
result = SetError(cell, error);
}
}
return result;
}
private bool ValidateDate(ExcelRange cell, string columnName)
{
bool result = true;
DateTime date = DateTime.MinValue;
string error = string.Format("{0} format is incorrect.", columnName);
result = ValidateText(cell, columnName);
if (result)
{
if (!DateTime.TryParse(cell.Value.ToString(), out date))
{
result = SetError(cell, error);
}
}
return result;
}
private bool ValidateJobTitle(ExcelRange cell, string columnName)
{
bool result = true;
string error = "Job title does not exist.";
List<JobTitle> listJobTitle = JobTitle.GetJobTitles((int)JobTitle.JobTitleStatus.Active);
result = ValidateText(cell, columnName);
if (result)
{
if (!listJobTitle.Any(x => x.Name.ToLowerInvariant() == cell.Value.ToString().ToLowerInvariant()))
{
result = SetError(cell, error);
}
}
return result;
}
Same implementation for the methods ValidateBranch() and ValidateRole() like in the ValidateJobTitle() method.
Following is the validated excel sheet with errors.
Hope this will help to anyone.
Upvotes: 7
Related Questions
- Error dialog displayed when opening an excel file generated with EPPlus
- Downloading Excel file after creating using EPPlus
- Data validation for each cell to check whether the value is available or not using EPplus
- Data validation for data type in each cell using EPplus
- EPPLus Not Returning File
- adding validations to data that is imported through excel file
- How to validate an excel file uploaded using FileUpload Control
- Generating an excel file with EPPlus is failing
- Importing an Excel Sheet and Validate the Imported Data with Loosely Coupled
- Checking if file actually is an Excel file using EPPlus