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); }