private void createMainInvoiceWorksheet(KrogerUnloadingInvoiceModel invoice, ExcelWorksheets sheets)
{
var sheet = sheets.First(w => w.Name == "Invoice");
var allOrders = invoice.AllWorkOrders;
var curRow = 21;
var subtotalRows = new List <int>();
if (invoice.BreakdownByDepartment)
{
// Create sub tables for each department
foreach (var group in invoice.AllWorkOrders.GroupBy(i => new { i.LocationDepartmentId, i.DepartmentName }))
{
curRow = buildMainInvoiceSubTable(invoice, sheet, curRow, group, group.Key.DepartmentName);
subtotalRows.Add(curRow);
curRow += 2;
}
}
else
{
curRow = buildMainInvoiceSubTable(invoice, sheet, curRow, invoice.AllWorkOrders);
subtotalRows.Add(curRow);
}
sheet.SetValue(13, 1, String.Format("{0} - {1}", invoice.HeaderInfo.Client, invoice.HeaderInfo.ClientLocation));
sheet.SetValue(14, 1, "Attn: " + invoice.HeaderInfo.NameForReceipts);
sheet.SetValue(15, 1, invoice.HeaderInfo.AddressLine1);
sheet.SetValue(16, 1, invoice.HeaderInfo.AddressLine2);
sheet.SetValue(17, 1, invoice.HeaderInfo.AddressLine3);
sheet.SetValue(12, 6, invoice.HeaderInfo.InvoiceNumber);
sheet.SetValue(12, 7, invoice.HeaderInfo.InvoiceDate.Value.ToString("MM/dd/yyyy"));
// Amount Due is calculated as sum of all subtotals
sheet.Cells[14, 6].Formula = String.Join("+", subtotalRows.Select(row => String.Format("G{0}", row)));
sheet.SetValue(14, 7, invoice.HeaderInfo.InvoiceTerms);
sheet.Cells[17, 6].Value = invoice.HeaderInfo.InvoiceAddress1;
sheet.Cells[18, 6].Value = invoice.HeaderInfo.InvoiceAddress2;
sheet.Calculate();
sheet.Select();
}
private void createSummaryWorksheet(KrogerUnloadingInvoiceModel invoice, ExcelWorksheets sheets)
{
var summarySheet = sheets.First(w => w.Name == "Summary");
summarySheet.Select();
if (invoice.BreakdownByDepartment)
{
var curRow = 2;
foreach (var group in invoice.AllWorkOrders.GroupBy(i => new { i.LocationDepartmentId, i.DepartmentName }))
{
curRow = buildSummarySubTable(invoice, summarySheet, curRow, group, group.Key.DepartmentName) + 3;
}
}
else
{
buildSummarySubTable(invoice, summarySheet, 2, invoice.AllWorkOrders);
}
}
public JsonResult UploadExcel()
{
string strresult = "", strPassResult = "";
HttpPostedFileBase file = Request.Files["excelFileUpload"];
if (file != null)
{
string fileName = file.FileName;
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
List <Employee> ReturnedByUploadedData = new List <Employee> {
};
using (var package = new ExcelPackage(file.InputStream))
{
ExcelWorksheets currentSheet = package.Workbook.Worksheets;
ExcelWorksheet workSheet = currentSheet.First();
int noOfCol = workSheet.Dimension.End.Column;
int noOfRow = workSheet.Dimension.End.Row;
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
{
Employee employee = new Employee();
employee.Name = (workSheet.Cells[rowIterator, 1].Value ?? string.Empty).ToString();
employee.Gender = (workSheet.Cells[rowIterator, 2].Value ?? string.Empty).ToString();
if (string.Empty != (workSheet.Cells[rowIterator, 3].Value ?? string.Empty).ToString())
{
DateTime conv = DateTime.FromOADate(Convert.ToDouble(workSheet.Cells[rowIterator, 3].Value));
employee.DOB = Convert.ToDateTime(conv);
employee.StrDOB = conv.ToString("yyyy/MM/dd");
}
else
{
employee.DOB = new DateTime();
}
employee.Email = (workSheet.Cells[rowIterator, 4].Value ?? string.Empty).ToString();
employee.Address = (workSheet.Cells[rowIterator, 5].Value ?? string.Empty).ToString();
employee.TechId = (workSheet.Cells[rowIterator, 6].Value ?? string.Empty).ToString();
employee.CompanyBranchCode = (workSheet.Cells[rowIterator, 7].Value ?? string.Empty).ToString();
employee.MaritalStatus = (workSheet.Cells[rowIterator, 8].Value ?? string.Empty).ToString();
employee.FilePath = "";
if (employee.Name == string.Empty)
{
employee.ExcelUploadStatus = "Name field required";
ReturnedByUploadedData.Add(employee);
}
else if (employee.Gender == string.Empty)
{
employee.ExcelUploadStatus = "Gender field required";
ReturnedByUploadedData.Add(employee);
}
else if (string.Empty == (workSheet.Cells[rowIterator, 3].Value ?? string.Empty).ToString())
{
employee.ExcelUploadStatus = "Date of birth field required";
ReturnedByUploadedData.Add(employee);
}
else if (employee.Email == string.Empty)
{
employee.ExcelUploadStatus = "Email field required";
ReturnedByUploadedData.Add(employee);
}
else if (employee.Gender == string.Empty)
{
employee.ExcelUploadStatus = "Gender field required";
ReturnedByUploadedData.Add(employee);
}
else if (employee.Address == string.Empty)
{
employee.ExcelUploadStatus = "Address field required";
ReturnedByUploadedData.Add(employee);
}
else if (employee.TechId == string.Empty)
{
employee.ExcelUploadStatus = "Technology field required";
ReturnedByUploadedData.Add(employee);
}
else if (employee.CompanyBranchCode == string.Empty)
{
employee.ExcelUploadStatus = "Branch field required";
ReturnedByUploadedData.Add(employee);
}
else if (employee.MaritalStatus == string.Empty)
{
employee.ExcelUploadStatus = "Marital Status field required";
ReturnedByUploadedData.Add(employee);
}
else
{
sqlCon.Open();
SqlCommand SP_M_InsertUser = new SqlCommand("SP_M_InsertUser", sqlCon);
SP_M_InsertUser.CommandType = CommandType.StoredProcedure;
SP_M_InsertUser.Parameters.AddWithValue("@EmpName", employee.Name);
SP_M_InsertUser.Parameters.AddWithValue("@Gender", employee.Gender);
SP_M_InsertUser.Parameters.AddWithValue("@DOB", employee.DOB);
SP_M_InsertUser.Parameters.AddWithValue("@Email", employee.Email);
SP_M_InsertUser.Parameters.AddWithValue("@EmpAddress", employee.Address);
SP_M_InsertUser.Parameters.AddWithValue("@Tech", employee.TechId);
SP_M_InsertUser.Parameters.AddWithValue("@Branch", employee.CompanyBranchCode);
SP_M_InsertUser.Parameters.AddWithValue("@Marital", employee.MaritalStatus);
SP_M_InsertUser.Parameters.AddWithValue("@ImageFilePath", employee.FilePath);
SP_M_InsertUser.Parameters.AddWithValue("@EmpDelete", false);
SP_M_InsertUser.Parameters.AddWithValue("@ActiveStatus", "Active");
SqlDataReader sqlData = SP_M_InsertUser.ExecuteReader();
while (sqlData.Read())
{
strresult = sqlData["result"].ToString();
}
sqlCon.Close();
if (strresult == "AlreadyExist")
{
strPassResult = "This " + employee.Email + " user already Exist";
employee.ExcelUploadStatus = "User Already Exist";
ReturnedByUploadedData.Add(employee);
}
else
{
IntiateUserInPrajectMapping(employee.Email);
strPassResult = "This " + employee.Email + " user added succefully";
employee.ExcelUploadStatus = "User Added Success";
ReturnedByUploadedData.Add(employee);
}
}
}
}
//Generate excel for uploaded data
ExcelPackage Ep = new ExcelPackage();
ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("Report");
Sheet.Cells["A1"].Value = "Name";
Sheet.Cells["B1"].Value = "Gender";
Sheet.Cells["C1"].Value = "Date of Birth";
Sheet.Cells["D1"].Value = "Email";
Sheet.Cells["E1"].Value = "Address";
Sheet.Cells["F1"].Value = "Technology";
Sheet.Cells["G1"].Value = "Branch";
Sheet.Cells["H1"].Value = "Marital";
Sheet.Cells["I1"].Value = "Active";
Sheet.Cells["J1"].Value = "Status of Process";
Sheet.Cells["A1:j1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
Sheet.Cells["A1:j1"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(129, 212, 26));
Sheet.Cells["A1:j1"].Style.Font.Color.SetColor(Color.Brown);
Sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
Sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
Sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
Sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
int row = 2;
foreach (var employee in ReturnedByUploadedData)
{
Sheet.Cells[string.Format("A{0}", row)].Value = employee.Name;
Sheet.Cells[string.Format("B{0}", row)].Value = employee.Gender;
Sheet.Cells[string.Format("C{0}", row)].Value = employee.StrDOB;
Sheet.Cells[string.Format("D{0}", row)].Value = employee.Email;
Sheet.Cells[string.Format("E{0}", row)].Value = employee.Address;
Sheet.Cells[string.Format("F{0}", row)].Value = employee.TechId;
Sheet.Cells[string.Format("G{0}", row)].Value = employee.CompanyBranchCode;
Sheet.Cells[string.Format("H{0}", row)].Value = employee.MaritalStatus;
Sheet.Cells[string.Format("I{0}", row)].Value = employee.ActiveStatus;
Sheet.Cells[string.Format("J{0}", row)].Value = employee.ExcelUploadStatus;
row++;
}
Sheet.Column(1).AutoFit();
Sheet.Column(2).AutoFit();
Sheet.Column(3).AutoFit();
Sheet.Column(5).AutoFit();
Sheet.Column(6).AutoFit();
Sheet.Column(7).AutoFit();
Sheet.Column(8).AutoFit();
Sheet.Column(9).AutoFit();
Sheet.Column(10).AutoFit();
row = row;
Sheet.Cells["J2:J" + row].Style.Fill.PatternType = ExcelFillStyle.Solid;
Sheet.Cells["J2:J" + row].Style.Fill.BackgroundColor.SetColor(Color.LightYellow);
Color colFromHex = ColorTranslator.FromHtml("#B7DEE8");
Sheet.Cells["A2:I" + row].Style.Fill.PatternType = ExcelFillStyle.Solid;
Sheet.Cells["A2:I" + row].Style.Fill.BackgroundColor.SetColor(colFromHex);
var filePath = System.Web.Hosting.HostingEnvironment.MapPath("~/SourceExcel/" + "StatusOfUploadData.xlsx");
FileInfo excelFile = new FileInfo(@filePath);
Ep.SaveAs(excelFile);
Response.End();
}
return(Json(null));
}
private IEnumerable <string> GetColumnList(ExcelWorksheets workSheets)
{
var masterSheet = workSheets.FirstOrDefault(x => x.Name.Equals("Masterlist", StringComparison.OrdinalIgnoreCase)) ?? workSheets.First();
Logger.LogInformation($"Master Sheet Name [{masterSheet.Name}]\n");
Logger.LogInformation($"Master Sheet Max Row [{masterSheet.Dimension.Rows}]\n");
Logger.LogInformation($"Master Sheet Max Column [{masterSheet.Dimension.Columns}]\n");
Logger.LogInformation($"Master Sheet Cell Address [{masterSheet.Cells.Address}]\n");
var columnCount = masterSheet.Dimension.Columns;
var columns = new List <string>();
for (var i = 1; i <= columnCount; i++)
{
columns.Add(GetCellContents(masterSheet, 1, i));
}
return(columns);
}
