public ActionResult GetAllUnProcessedRecords(List <RoboticsUnProcessedModel> lstRobotics) { try { RoboticsRepository objrep = new RoboticsRepository(); if (lstRobotics.Count > 0) { var lst = lstRobotics.Where(t => t.IsManualProcessed == true).ToList(); foreach (var q in lst) { objrep.updateManualProcessedData(q.Id, q.ConnectionType); } } List <RoboticsUnProcessedModel> lstunprocessData = new List <RoboticsUnProcessedModel>(); lstunprocessData = objrep.GetUnProcessedData(); RoboticProcessViewModel objviewModel = new RoboticProcessViewModel(); string message = string.Empty; message = objviewModel.ExportInRoboticFormat(lstunprocessData); ViewBag.Message = message; lstunprocessData = new List <RoboticsUnProcessedModel>(); lstunprocessData = objrep.GetUnProcessedData(); ModelState.Clear(); return(View(lstunprocessData)); } catch (Exception ex) { return(View()); } }
// GET: RoboticsProcess public ActionResult GetAllUnProcessedRecords() { try { RoboticsRepository objrep = new RoboticsRepository(); List <RoboticsUnProcessedModel> lstunprocessData = new List <RoboticsUnProcessedModel>(); lstunprocessData = objrep.GetUnProcessedData(); return(View(lstunprocessData)); } catch (Exception ex) { return(View()); } }
public ActionResult ExportToExcel() { try { RoboticsRepository objRep = new RoboticsRepository(); DataSet dsInvalidData = new DataSet(); dsInvalidData = objRep.GetInvalidData(); using (ExcelPackage pck = new ExcelPackage()) { DataTable dt = new DataTable(); ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Invalid_Other_Data"); if (dsInvalidData.Tables.Contains("Invalid_Other_Data")) { ws.Cells["A1"].LoadFromDataTable(dsInvalidData.Tables[0], true, TableStyles.Medium15); } else { ws.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium15); }//You can Use TableStyles property of your desire. //Read the Excel file in a byte array ExcelWorksheet ws1 = pck.Workbook.Worksheets.Add("Invalid_Hardware_Only_Data"); if (dsInvalidData.Tables.Contains("Invalid_Hardware_Only_Data")) { ws1.Cells["A1"].LoadFromDataTable(dsInvalidData.Tables[1], true, TableStyles.Medium15); } else { ws1.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium15); } Byte[] fileBytes = pck.GetAsByteArray(); Response.ClearContent(); Response.AddHeader("content-disposition", "attachment;filename=Invalid_Data" + DateTime.Now.ToString("M_dd_yyyy_H_M_s") + ".xlsx"); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.BinaryWrite(fileBytes); Response.End(); } return(View()); } catch (Exception ex) { return(View()); } }
public string Export_Add_CTN(List <RoboticsUnProcessedModel> lstUnprocessedData) { try { DataTable dt = new DataTable(); dt.TableName = "Add CTN"; dt.Clear(); dt.Columns.Add("Connection Type"); dt.Columns.Add("Ref Id"); dt.Columns.Add("Subscription"); dt.Columns.Add("Account Name"); dt.Columns.Add("BAN"); dt.Columns.Add("Tariff Code"); dt.Columns.Add("Commitment Length (Months)"); dt.Columns.Add("Commitment Start Date"); dt.Columns.Add("SOC Code 1"); dt.Columns.Add("SOC Code 2"); dt.Columns.Add("SOC Code 3"); dt.Columns.Add("SOC Code 4"); dt.Columns.Add("International Bar Remove?"); dt.Columns.Add("PAC"); dt.Columns.Add("Port In Date"); dt.Columns.Add("Accessory SKU"); dt.Columns.Add("Accessory Variant"); dt.Columns.Add("Accessory Charge"); dt.Columns.Add("Charging Account (Hardware/Airtime)"); dt.Columns.Add("Sim Card Required"); dt.Columns.Add("Sim Card SKU Code"); dt.Columns.Add("Existing SIM No."); dt.Columns.Add("Delivery Address"); dt.Columns.Add("Delivery Type"); dt.Columns.Add("Delivery Charge(£)"); dt.Columns.Add("Discount Amount Per Month (£)"); dt.Columns.Add("Discount Length(Months)"); foreach (var q in lstUnprocessedData) { DataRow dr = dt.NewRow(); dr["Connection Type"] = q.ConnectionType; dr["Ref Id"] = q.ReferenceNumber; dr["Subscription"] = q.SubmissionDate; dr["Account Name"] = q.AccountName; dr["BAN"] = q.BAN; dr["Tariff Code"] = q.TariffCode; dr["Commitment Length (Months)"] = q.CommitmentLength; dr["Commitment Start Date"] = q.CommitmentStartDate; dr["SOC Code 1"] = q.SOC1Code; dr["SOC Code 2"] = q.SOC2Code; dr["SOC Code 3"] = q.SOC3Code; dr["SOC Code 4"] = q.SOC4Code; dr["International Bar Remove?"] = q.InternationalBarRemove == true ? "Yes" : "No"; dr["PAC"] = q.PACCode; dr["Port In Date"] = q.PortDate; dr["Accessory SKU"] = q.AccessorySKU; dr["Accessory Variant"] = q.AccessoryVariant; dr["Accessory Charge"] = q.AccessoryCharge; dr["Charging Account (Hardware/Airtime)"] = q.ChargingAccount; dr["Sim Card Required"] = q.SimRequired; dr["Sim Card SKU Code"] = q.SimCardSKUCode; dr["Existing SIM No."] = q.ExistingSIMNo; dr["Delivery Address"] = q.DeliveryAddress; dr["Delivery Type"] = q.DeliveryType; dr["Delivery Charge(£)"] = q.DeliveryCharge; dr["Discount Amount Per Month (£)"] = q.Discount; dr["Discount Length(Months)"] = q.DiscountLength; dt.Rows.Add(dr); } string message = string.Empty; using (ExcelPackage objExcelPackage = new ExcelPackage()) { //Create the worksheet ExcelWorksheet objWorksheet = objExcelPackage.Workbook.Worksheets.Add(dt.TableName); //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1 objWorksheet.Cells["A1"].LoadFromDataTable(dt, true); objWorksheet.Cells.Style.Font.SetFromFont(new Font("Calibri", 10)); objWorksheet.Cells.AutoFitColumns(); //Format the header using (ExcelRange objRange = objWorksheet.Cells[1, dt.Columns.Count]) { objRange.Style.Font.Bold = true; objRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; objRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center; objRange.Style.Fill.PatternType = ExcelFillStyle.Solid; objRange.Style.Fill.BackgroundColor.SetColor(Color.Yellow); } string AddCTNFilePath = ConfigurationManager.AppSettings["AddCTNFilePath"].ToString(); //Write it back to the client if (File.Exists(AddCTNFilePath)) { message = "File is already exist to process for 'ADD CTN'"; return(message); } //Create excel file on physical disk FileStream objFileStrm = File.Create(AddCTNFilePath); objFileStrm.Close(); //Write content to excel file File.WriteAllBytes(AddCTNFilePath, objExcelPackage.GetAsByteArray()); } message = "File 'Add CTN' has processed succussfully."; //processed data in database RoboticsRepository objrep = new RoboticsRepository(); objrep.UpdateProcessedDataforAddCTN(lstUnprocessedData); return(message); } catch (Exception ex) { return(ex.ToString() + " Method Name: Export_Add_CTN"); } }
public string Export_Hardware_Credit(List <RoboticsUnProcessedModel> lstUnprocessedData) { try { DataTable dt = new DataTable(); dt.TableName = "Hardware Credit"; dt.Clear(); dt.Columns.Add("Connection Type"); dt.Columns.Add("Ref Id"); dt.Columns.Add("Airtime BAN"); dt.Columns.Add("Hardware BAN"); dt.Columns.Add("Hardware Fund (£)"); dt.Columns.Add("Airtime Fund (£)"); dt.Columns.Add("Buy Out Credit(VAT Exempt)(£)"); foreach (var q in lstUnprocessedData) { DataRow dr = dt.NewRow(); dr["Connection Type"] = q.ConnectionType; dr["Ref Id"] = q.ReferenceNumber; dr["Airtime BAN"] = q.AirtimeBAN; dr["Hardware BAN"] = q.HardwareBAN; dr["Hardware Fund (£)"] = q.HardwareFund; dr["Airtime Fund (£)"] = q.AirtimeFund; dr["Buy Out Credit(VAT Exempt)(£)"] = q.BuyOutCredit; dt.Rows.Add(dr); } string message = string.Empty; using (ExcelPackage objExcelPackage = new ExcelPackage()) { //Create the worksheet ExcelWorksheet objWorksheet = objExcelPackage.Workbook.Worksheets.Add(dt.TableName); //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1 objWorksheet.Cells["A1"].LoadFromDataTable(dt, true); objWorksheet.Cells.Style.Font.SetFromFont(new Font("Calibri", 10)); objWorksheet.Cells.AutoFitColumns(); //Format the header using (ExcelRange objRange = objWorksheet.Cells[1, dt.Columns.Count]) { objRange.Style.Font.Bold = true; objRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; objRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center; objRange.Style.Fill.PatternType = ExcelFillStyle.Solid; objRange.Style.Fill.BackgroundColor.SetColor(Color.Yellow); } string hardwareCreditPath = ConfigurationManager.AppSettings["HardwareCreditFilePath"].ToString(); //Write it back to the client if (File.Exists(hardwareCreditPath)) { message = "File is already exist to process for 'Hardware Credit'"; return(message); } //Create excel file on physical disk FileStream objFileStrm = File.Create(hardwareCreditPath); objFileStrm.Close(); //Write content to excel file File.WriteAllBytes(hardwareCreditPath, objExcelPackage.GetAsByteArray()); } message = "File 'Hardware Credit' has processed succussfully."; //processed data in database RoboticsRepository objrep = new RoboticsRepository(); objrep.UpdateProcessedDataforHardwareCredit(lstUnprocessedData); return(message); } catch (Exception ex) { return(ex.ToString() + " Method Name: Export_Hardware_Credit"); } }