//Importing
protected void BtnLoadItems_Click(object sender, EventArgs e)
{
//Collects current method for error tracking
string method = "BtnLoadItems_Click";
object[] objPageDetails = { Session["currPage"].ToString(), method };
try
{
int error = 0;
//Verifies file has been selected
if (fupItemSheet.HasFile)
{
//load the uploaded file into the memorystream
using (MemoryStream stream = new MemoryStream(fupItemSheet.FileBytes))
//Lets the server know to use the excel package
using (ExcelPackage xlPackage = new ExcelPackage(stream))
{
//Gets the first worksheet in the workbook
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
//Gets the row count
var rowCnt = worksheet.Dimension.End.Row;
//Gets the column count
var colCnt = worksheet.Dimension.End.Column;
//Beginning the loop for data gathering
for (int i = 2; i <= rowCnt; i++) //Starts on 2 because excel starts at 1, and line 1 is headers
{
//Array of the cells that will need to be checked
int[] cells = { 3, 5, 6, 12, 13, 15, 22 };
foreach (int column in cells)
{
//If there is no value in the column, proceed
if (worksheet.Cells[i, column].Value == null)
{
worksheet.Cells[i, column].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[i, column].Style.Fill.BackgroundColor.SetColor(Color.Red);
error = 1;
}
}
}
//xlPackage.SaveAs(new FileInfo(@"c:\temp\myFile.xls"));
if (error == 1)
{
//Sets the attributes and writes file
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=" + fupItemSheet.FileName + "_ErrorsFound" + ".xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(xlPackage.GetAsByteArray());
Response.End();
}
else
{
//Calls method to import the requested file
#pragma warning disable IDE0067 // Dispose objects before losing scope
DataTable errors = new DataTable();
#pragma warning restore IDE0067 // Dispose objects before losing scope
//errors.Columns.Add("sku");
//errors.Columns.Add("brandError");
//errors.Columns.Add("modelError");
//errors.Columns.Add("identifierError");
errors = R.CallUploadItems(fupItemSheet, CU, objPageDetails);
if (errors.Rows.Count != 0)
{
//Loops through the errors datatable pulling the sku's from there and entering them into an array
//Then loops through each row on the excel sheet and checks to compare against sku array
//If it is not in there, that row is deleted
object[,] errorList = new object[errors.Rows.Count, 4];
ArrayList errorSkus = new ArrayList();
for (int i = 0; i < errors.Rows.Count; i++)
{
errorSkus.Add(errors.Rows[i][0]); //SKUs used for row deletion
errorList[i, 0] = errors.Rows[i][0].ToNullSafeString(); //SKU
errorList[i, 1] = Convert.ToInt32(errors.Rows[i][1]); //Brand
errorList[i, 2] = Convert.ToInt32(errors.Rows[i][2]); //Model
errorList[i, 3] = Convert.ToInt32(errors.Rows[i][3]); //Secondary Identifier
}
//Loop through the Excel sheet
for (int i = rowCnt; i >= 2; i--)
{
//Loop through the error array
for (int j = 0; j < errorList.Length / 4; j++)
{
//Column 3 = SKU
if ((worksheet.Cells[i, 3].Value).ToString().Equals(errorList[j, 0].ToString()))
{
worksheet.Cells[i, 3].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[i, 3].Style.Fill.BackgroundColor.SetColor(Color.Red);
//If brand caused an error
if (Convert.ToInt32(errorList[j, 1]) == 1)
{
worksheet.Cells[i, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[i, 5].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
//If model caused an error
if (Convert.ToInt32(errorList[j, 2]) == 1)
{
worksheet.Cells[i, 6].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[i, 6].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
//If secondary identifier(Destination) caused an error
if (Convert.ToInt32(errorList[j, 3]) == 1)
{
worksheet.Cells[i, 22].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[i, 22].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
break;
}
}
//Check to see if the cell's sku is in the array, if it is not, delete the row
bool isInArray = errorSkus.IndexOf((worksheet.Cells[i, 3].Value).ToString()) != -1;
if (!isInArray)
{
worksheet.DeleteRow(i);
}
}
worksheet.Cells[1, 26].Value = "Errors Found. The skus that are highlighted in red have an issue with either their brand or model. This could be a spelling mistake or the brand and/or model are not in the database.";
//MessageBoxCustom.ShowMessage("Errors Found. The skus that are highlighted in red have an issue with either their brand or model. This could be a spelling mistake or the brand and/or model are not in the database.", this);
string fileName = fupItemSheet.FileName + "_ErrorsFound";
//Sets the attributes and writes file
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=" + fileName + ".xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(xlPackage.GetAsByteArray());
Response.End();
}
else
{
MessageBoxCustom.ShowMessage("Importing Complete", this);
}
}
}
}
}
//Exception catch
catch (ThreadAbortException tae) { }
catch (Exception ex)
{
//Log all info into error table
ER.CallLogError(ex, CU.employee.intEmployeeID, Convert.ToString(Session["currPage"]) + "-V3.2", method, this);
//Display message box
MessageBoxCustom.ShowMessage("An Error has occurred and been logged. "
+ "If you continue to receive this message please contact "
+ "your system administrator.", this);
}
imgLoadingItemImport.Visible = false;
}