/**
* List of validations as requested in task detail:
*
* Mandatory Fields => PID, Contract Number, Product Id, MfrPN, Mfr Name, Vendor Name, Vendor PN, Cost, Short Description
*
* - contract number => User XXXX for all products
* - Coo => If empty use default value ‘TW’
* - Long Description => If not present use Short Description
* - UOM => If not present use ‘EA’
*
*/
public ValidatorResponse validateRow(InputDataXlsx inputData)
{
ValidatorResponse response = new ValidatorResponse();
int status = 0;
string message = "";
// . required fields
string requiredFieldResult = validateRequiredFields(inputData);
if (!string.IsNullOrEmpty(requiredFieldResult))
{
message = resourceMgr.GetString("ErrorRequiredField") + " " + requiredFieldResult;
}
else
{
// . validate attribute length
string errorFields = validateAttributeLength(inputData);
if (!string.IsNullOrEmpty(errorFields))
{
message = resourceMgr.GetString("ErrorInvalidField") + " " + errorFields;
}
else
{
status = 1;
message = resourceMgr.GetString("SuccessValidation");
}
}
response.Status = status;
response.Message = message;
return(response);
}
public string validateRequiredFields(InputDataXlsx inputData)
{
string requiredFields = "";
if (inputData.PID == 0)
{
requiredFields = requiredFields + "PID, ";
}
if (inputData.Cost == 0)
{
requiredFields = requiredFields + "Cost, ";
}
if (string.IsNullOrEmpty(inputData.ContractNumber.Trim()))
{
requiredFields = requiredFields + "Contract Number, ";
}
if (string.IsNullOrEmpty(inputData.ProductId.Trim()))
{
requiredFields = requiredFields + "Product Id, ";
}
if (string.IsNullOrEmpty(inputData.MfrPN.Trim()))
{
requiredFields = requiredFields + "Mfr PN, ";
}
if (string.IsNullOrEmpty(inputData.MfrName.Trim()))
{
requiredFields = requiredFields + "Mfr Name, ";
}
if (string.IsNullOrEmpty(inputData.VendorName.Trim()))
{
requiredFields = requiredFields + "Vendor Name, ";
}
if (string.IsNullOrEmpty(inputData.VendorPN.Trim()))
{
requiredFields = requiredFields + "Vendor PN, ";
}
if (string.IsNullOrEmpty(inputData.ShortDescription.Trim()))
{
requiredFields = requiredFields + "Short Description";
}
return(requiredFields);
}
public static ValidatorResponse convertXlsxToCsv(string filePath, ProgressBar progressBar, ListBox errorBox)
{
// standard response to return from each sub function
ValidatorResponse response = new ValidatorResponse();
try {
// get executable directory path so that we can use it to create files
string executablePath = System.IO.Path.GetDirectoryName(Application.ExecutablePath);
// this is our directory where we are going to create ProductList.csv and error.xlsx
string uploadDirectory = executablePath + "\\uploads\\" + Util.getCurrentTimestamp();
// as mentioned in task detail maximum number of rows allowed per csv
const int maxRowsPerCsv = Constants.maxRowsPerCsv;
// gonna use this to switch file names while saving when record count crosses max allowed
int rowCounter = 0;
// default file name as requested in task detail
string fileNameCsv = Constants.fileNameCsv;
// get csv seperator from constants, so that we can change it easily in future (if needed)
string csvSeparator = Constants.csvSeparator;
// get header row titles from constants and join them by the separator
var csvHeaderRow = String.Join(csvSeparator, Constants.headerTitleCsv);
// gonna use this variable to identify when to insert header row for each new file
bool flagInsertHeaderRow = true;
/**
* I have used NPOI for excel operations because Interop requires actual Excel application to be installed on server, which is not always a good idea ar may even be not possible at all (i.e. if you are using Azure Web Apps). NPOI works directly with Excel files. It is much faster than Interop.
*/
XSSFWorkbook xssfworkbook;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
xssfworkbook = new XSSFWorkbook(file);
}
// read data
XSSFSheet sheet = (XSSFSheet)xssfworkbook.GetSheetAt(0);
IRow headerRow = sheet.GetRow(0);
Validator validator = new Validator();
// validate header row as per given details in the task description
response = validator.validateHeaderRow(headerRow);
if (response.Status == 1)
{
var csvData = new StringBuilder();
progressBar.Maximum = (sheet.LastRowNum - 1);
// create a new workbook, as we need to store failed records in error.xlsx
IWorkbook errorWorkbook = new HSSFWorkbook();
// create new sheet to add failed records
ISheet errorSheet = errorWorkbook.CreateSheet("Sheet1");
if (sheet.LastRowNum > maxRowsPerCsv)
{
// as per requirement if supplied number of rows count exceed the max (i. e 10,000), then all file names should be different
fileNameCsv = Util.getCurrentTimestamp() + ".csv";
}
// loop through rows
for (int i = 1; i < sheet.LastRowNum; i++)
{
string errorMessage = "";
// create new directory, as we are going to save both error.xlsx and csv files in this folder.
Directory.CreateDirectory(uploadDirectory);
if (sheet.GetRow(i) != null) //null is when the row only contains empty cells
{
IRow row = sheet.GetRow(i);
int cellCount = row.LastCellNum;
if (cellCount >= 11)
{
int pId;
Double salesPrice = 0;
Double cost;
Int32.TryParse(row.GetCell(0).ToString(), out pId);
Double.TryParse(row.GetCell(6).ToString(), out cost);
if (13 <= (cellCount - 1))
{
Double.TryParse(row.GetCell(13).ToString(), out salesPrice);
}
var ProductId = row.GetCell(1) != null?row.GetCell(1).ToString() : "";
var MfrName = row.GetCell(2) != null?row.GetCell(2).ToString() : "";
var VendorName = row.GetCell(3) != null?row.GetCell(3).ToString() : "";
var MfrPN = row.GetCell(4) != null?row.GetCell(4).ToString() : "";
var VendorPN = row.GetCell(5) != null?row.GetCell(5).ToString() : "";
var Coo = row.GetCell(7) != null?row.GetCell(7).ToString() : "";
var ShortDescription = row.GetCell(8) != null?row.GetCell(8).ToString() : "";
var UPC = (cellCount - 1) >= 9 && row.GetCell(9) != null?row.GetCell(9).ToString() : "";
var UOM = (cellCount - 1) >= 10 && row.GetCell(10) != null?row.GetCell(10).ToString() : "";
var SaleStartDate = (cellCount - 1) >= 11 && row.GetCell(11) != null?row.GetCell(11).ToString() : "";
var SaleEndDate = (cellCount - 1) >= 12 && row.GetCell(12) != null?row.GetCell(12).ToString() : "";
// note: we dont have any field for large description in the given template
var LongDescription = ShortDescription;
// Price Should be 20 % more than cost
var Price = cost + ((cost * 20) / 100);
// Coo => If empty use default value ‘TW’
Coo = string.IsNullOrEmpty(Coo) ? "TW" : Coo;
// UOM => If not present use ‘EA’
UOM = string.IsNullOrEmpty(UOM) ? "EA" : UOM;
// i like building objects, as it is difficult to identify/ remember values by index 0,1, etc
InputDataXlsx inputData = new InputDataXlsx
{
PID = pId,
ProductId = ProductId,
MfrName = MfrName,
VendorName = VendorName,
MfrPN = MfrPN,
VendorPN = VendorPN,
Coo = Coo,
ShortDescription = ShortDescription,
LongDescription = LongDescription,
UPC = UPC,
UOM = UOM,
SaleStartDate = SaleStartDate,
SaleEndDate = SaleEndDate,
SalesPrice = salesPrice,
Cost = Price,
};
response = validator.validateRow(inputData);
if (response.Status == 1)
{
if (flagInsertHeaderRow)
{
// this flag true means, the file is new and we need to insert new header row on top
csvData.AppendLine(csvHeaderRow);
// set this flag to false, so that it doesnt insert title in eah row
flagInsertHeaderRow = false;
}
var newLine = $"{inputData.PID.ToString()}{csvSeparator}{inputData.ContractNumber}{csvSeparator}{inputData.ProductId}{csvSeparator}{inputData.MfrPN}{csvSeparator}{inputData.MfrName}{csvSeparator}{inputData.VendorName}{csvSeparator}{inputData.VendorPN}{csvSeparator}{inputData.Cost}{csvSeparator}{inputData.Coo}{csvSeparator}{inputData.ShortDescription}{csvSeparator}{inputData.LongDescription}{csvSeparator}{inputData.UPC}{csvSeparator}{inputData.UOM}{csvSeparator}{inputData.SaleStartDate}{csvSeparator}{inputData.SaleEndDate}{csvSeparator}{inputData.SalesPrice}";
csvData.AppendLine(newLine);
progressBar.Value = i;
rowCounter = rowCounter + 1;
// if rows counter is greater than equal to or the index reaches at the last, then we need to generate the csv
if (rowCounter >= maxRowsPerCsv || i == (sheet.LastRowNum - 1))
{
// create and save data in csv file
File.AppendAllText(uploadDirectory + "\\" + fileNameCsv, csvData.ToString());
// after each csv generation we need to set a new name for csv file
fileNameCsv = Util.getCurrentTimestamp() + ".csv";
// reset these values for new file
csvData = new StringBuilder();
flagInsertHeaderRow = true;
rowCounter = 0;
}
}
else
{
errorMessage = $"Error at row {i + 1}: {response.Message}";
}
}
else
{
errorMessage = $"Error at row {i + 1}: Invaid number of columns";
}
}
else
{
errorMessage = $"Error at row {i + 1}: Invaid number of columns";
}
if (!string.IsNullOrEmpty(errorMessage))
{
/**
* log failed records in error.xlsx file
*/
// add row titles on top
if (errorBox.Items.Count == 0)
{
// create title row
IRow errorRowTitle = errorSheet.CreateRow(0);
for (int k = 0; k < Constants.headerTitleXlsx.Length; k++)
{
// get title names from the constant and insert in top row for error file
ICell cellTitle = errorRowTitle.CreateCell(k);
cellTitle.SetCellValue(Constants.headerTitleXlsx[k]);
}
// add a column to state the error detail, so that user dont have to find out whats wrong for each row
ICell cellTitleDetail = errorRowTitle.CreateCell(Constants.headerTitleXlsx.Length);
cellTitleDetail.SetCellValue("Error Detail");
}
// for each new error create a new row and replicate the failed row to this one
IRow errorRow = errorSheet.CreateRow(errorBox.Items.Count + 1);
for (int j = 0; j < sheet.GetRow(i).LastCellNum; j++)
{
ICell cell = errorRow.CreateCell(j);
dynamic cellValue = sheet.GetRow(i).GetCell(j) != null?sheet.GetRow(i).GetCell(j).ToString() : "";
cell.SetCellValue(cellValue);
}
ICell cellError = errorRow.CreateCell(sheet.GetRow(i).LastCellNum);
cellError.SetCellValue(errorMessage);
// add message to the error list in windows form for user readability
errorBox.Items.Add(errorMessage);
}
}
if (errorBox.Items.Count > 0)
{
// if there are errors in the uploaded file, generate error.xlsx and push failed records in it
using (FileStream stream = new FileStream(uploadDirectory + "\\" + "error.xlsx", FileMode.Create, FileAccess.Write))
{
errorWorkbook.Write(stream);
}
}
}
else
{
errorBox.Items.Add("Error: " + response.Message);
}
return(response);
}
catch (Exception ex)
{
response.Message = ex.Message;
return(response);
}
}
public string validateAttributeLength(InputDataXlsx inputData)
{
string errorFields = "";
if (inputData.PID.ToString().Length > 20)
{
errorFields = errorFields + "PID, ";
}
if (inputData.ContractNumber.Length > 20)
{
errorFields = errorFields + "Contract Number, ";
}
if (inputData.ProductId.Length > 50)
{
errorFields = errorFields + "Product Id, ";
}
if (inputData.MfrPN.Length > 50)
{
errorFields = errorFields + "Mfr PN, ";
}
if (inputData.MfrName.Length > 50)
{
errorFields = errorFields + "Mfr Name, ";
}
if (inputData.VendorName.Length > 50)
{
errorFields = errorFields + "Vendor Name, ";
}
if (inputData.VendorPN.Length > 50)
{
errorFields = errorFields + "Vendor PN, ";
}
if (inputData.Cost.ToString().Length > 20)
{
errorFields = errorFields + "Cost, ";
}
if (inputData.Coo.Length > 2)
{
errorFields = errorFields + "Coo, ";
}
if (inputData.ShortDescription.Length > 300)
{
errorFields = errorFields + "Short Description, ";
}
if (inputData.LongDescription.Length > 3000)
{
errorFields = errorFields + "Long Description, ";
}
if (inputData.UPC.Length > 12)
{
errorFields = errorFields + "UPC, ";
}
if (inputData.UOM.Length > 2)
{
errorFields = errorFields + "UOM, ";
}
if (inputData.SaleStartDate.Length > 20)
{
errorFields = errorFields + "Sale Start Date, ";
}
if (inputData.SaleEndDate.Length > 20)
{
errorFields = errorFields + "Sale End Date, ";
}
if (inputData.SalesPrice.ToString().Length > 20)
{
errorFields = errorFields + "Sales Price, ";
}
return(errorFields);
}