public ActionResult Import(ImportViewModel model) { if (ModelState.IsValid) { if (model.File != null) { try { ExcelPackage package = new ExcelPackage(model.File.InputStream); ExcelWorksheet sheet = package.Workbook.Worksheets[1]; int rows = sheet.Dimension.Rows; for (int i = 1; i <= rows; i++) { if (i > 1) { ComputerModel item = new ComputerModel(); item.LastSeen = DateTime.Now; // ID int id = -1; try { int.TryParse(sheet.Cells[i, 1].Value.ToString(), out id); } catch (Exception) { } if (id >= 0) { item.Id = id; } // Get types string typeName = sheet.Cells[i, 4].Value.ToString(); string colorName = sheet.Cells[i, 5].Value.ToString(); string locationName = sheet.Cells[i, 6].Value.ToString(); // Convert double price = 0; try { price = sheet.Cells[i, 7].Value != null?Convert.ToDouble(sheet.Cells[i, 7].Value) : 0; } catch (Exception) {} // Properties // We have to check for NULL on all the optional ones! item.Name = sheet.Cells[i, 2].Value.ToString(); item.Hostname = sheet.Cells[i, 3].Value.ToString(); item.Type = db.ComputerTypes.SingleOrDefault(x => x.Name == typeName); item.Color = db.Colors.SingleOrDefault(x => x.Name == colorName); item.Location = db.Locations.SingleOrDefault(x => x.Location == locationName); item.Price = price; item.PurchaseDate = DateTime.Parse(sheet.Cells[i, 8].Value.ToString()); item.Description = sheet.Cells[i, 9].Value != null ? sheet.Cells[i, 9].Value.ToString() : ""; item.Manufacturer = sheet.Cells[i, 10].Value != null ? sheet.Cells[i, 10].Value.ToString() : ""; item.Model = sheet.Cells[i, 11].Value != null ? sheet.Cells[i, 11].Value.ToString() : ""; item.CPU = sheet.Cells[i, 12].Value != null ? sheet.Cells[i, 12].Value.ToString() : ""; item.CPUCores = sheet.Cells[i, 13].Value != null ? sheet.Cells[i, 13].Value.ToString() : ""; item.RAM = sheet.Cells[i, 14].Value != null ? sheet.Cells[i, 14].Value.ToString() : ""; item.RAMSize = sheet.Cells[i, 15].Value != null ? sheet.Cells[i, 15].Value.ToString() : ""; item.Disk = sheet.Cells[i, 16].Value != null ? sheet.Cells[i, 16].Value.ToString() : ""; item.DiskSize = sheet.Cells[i, 17].Value != null ? sheet.Cells[i, 17].Value.ToString() : ""; item.EthernetCable = sheet.Cells[i, 18].Value != null ? sheet.Cells[i, 18].Value.ToString() : ""; item.EthernetWifi = sheet.Cells[i, 19].Value != null ? sheet.Cells[i, 19].Value.ToString() : ""; item.OS = sheet.Cells[i, 20].Value != null ? sheet.Cells[i, 20].Value.ToString() : ""; db.Computers.AddOrUpdate(item); db.SaveChanges(); } } } catch (Exception) { return(View(model)); } } // Event SysEvent ev = new SysEvent(); ev.Action = Enums.Action.Info; ev.Description = "Imported Computers"; ev.ActionStatus = ActionStatus.OK; LogsController.AddEvent(ev, User.Identity.GetUserId()); return(RedirectToAction("Index")); } return(View(model)); }
public void Export() { List <ComputerModel> model = db.Computers .Include(c => c.Type) .Include(c => c.Color) .Include(c => c.Location) .ToList(); try { ExcelPackage package = new ExcelPackage(); ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Computers"); // Header text sheet.Cells[1, 1].Value = "Id"; sheet.Cells[1, 2].Value = "Name"; sheet.Cells[1, 3].Value = "Hostname"; sheet.Cells[1, 4].Value = "Type"; sheet.Cells[1, 5].Value = "Color"; sheet.Cells[1, 6].Value = "Location"; sheet.Cells[1, 7].Value = "Price"; sheet.Cells[1, 8].Value = "Purchase Date"; sheet.Cells[1, 9].Value = "Description"; sheet.Cells[1, 10].Value = "Manufacturer"; sheet.Cells[1, 11].Value = "Model"; sheet.Cells[1, 12].Value = "CPU"; sheet.Cells[1, 13].Value = "CPU Cores"; sheet.Cells[1, 14].Value = "RAM"; sheet.Cells[1, 15].Value = "RAM Size"; sheet.Cells[1, 16].Value = "Disk"; sheet.Cells[1, 17].Value = "Disk Size"; sheet.Cells[1, 18].Value = "Ethernet Cable"; sheet.Cells[1, 19].Value = "Ethernet WiFi"; sheet.Cells[1, 20].Value = "OS"; // Format cells sheet.Cells[1, 1].Style.Font.Bold = true; sheet.Cells[1, 2].Style.Font.Bold = true; sheet.Cells[1, 3].Style.Font.Bold = true; sheet.Cells[1, 4].Style.Font.Bold = true; sheet.Cells[1, 5].Style.Font.Bold = true; sheet.Cells[1, 6].Style.Font.Bold = true; sheet.Cells[1, 7].Style.Font.Bold = true; sheet.Cells[1, 8].Style.Font.Bold = true; sheet.Cells[1, 9].Style.Font.Bold = true; sheet.Cells[1, 10].Style.Font.Bold = true; sheet.Cells[1, 11].Style.Font.Bold = true; sheet.Cells[1, 12].Style.Font.Bold = true; sheet.Cells[1, 13].Style.Font.Bold = true; sheet.Cells[1, 14].Style.Font.Bold = true; sheet.Cells[1, 15].Style.Font.Bold = true; sheet.Cells[1, 16].Style.Font.Bold = true; sheet.Cells[1, 17].Style.Font.Bold = true; sheet.Cells[1, 18].Style.Font.Bold = true; sheet.Cells[1, 19].Style.Font.Bold = true; sheet.Cells[1, 20].Style.Font.Bold = true; sheet.Cells[1, 1].Style.Font.Color.SetColor(Color.Orange); sheet.Cells[1, 2].Style.Font.Color.SetColor(Color.LightCoral); sheet.Cells[1, 3].Style.Font.Color.SetColor(Color.LightCoral); sheet.Cells[1, 4].Style.Font.Color.SetColor(Color.LightCoral); sheet.Cells[1, 5].Style.Font.Color.SetColor(Color.LightCoral); sheet.Cells[1, 6].Style.Font.Color.SetColor(Color.LightCoral); sheet.Cells[1, 7].Style.Font.Color.SetColor(Color.LightCoral); sheet.Cells[1, 8].Style.Font.Color.SetColor(Color.LightCoral); sheet.Column(2).Width = 15; sheet.Column(3).Width = 15; sheet.Column(8).Width = 22; sheet.Column(9).Width = 15; sheet.Column(10).Width = 15; sheet.Column(11).Width = 15; sheet.Column(13).Width = 10; sheet.Column(15).Width = 10; sheet.Column(18).Width = 15; sheet.Column(19).Width = 15; sheet.Column(20).Width = 15; // Add data int row = 2; // Start after headers foreach (ComputerModel item in model) { sheet.Cells[row, 1].Value = item.Id; sheet.Cells[row, 2].Value = item.Name; sheet.Cells[row, 3].Value = item.Hostname; sheet.Cells[row, 4].Value = item.Type.Name; sheet.Cells[row, 5].Value = item.Color.Name; sheet.Cells[row, 6].Value = item.Location.Location; sheet.Cells[row, 7].Value = item.Price; sheet.Cells[row, 8].Value = item.PurchaseDate.Date.ToString(); sheet.Cells[row, 9].Value = item.Description; sheet.Cells[row, 10].Value = item.Manufacturer; sheet.Cells[row, 11].Value = item.Model; sheet.Cells[row, 12].Value = item.CPU; sheet.Cells[row, 13].Value = item.CPUCores; sheet.Cells[row, 14].Value = item.RAM; sheet.Cells[row, 15].Value = item.RAMSize; sheet.Cells[row, 16].Value = item.Disk; sheet.Cells[row, 17].Value = item.DiskSize; sheet.Cells[row, 18].Value = item.EthernetCable; sheet.Cells[row, 19].Value = item.EthernetWifi; sheet.Cells[row, 20].Value = item.OS; row++; } DateTime date = DateTime.Now; // Event SysEvent ev = new SysEvent(); ev.Action = Enums.Action.Info; ev.Description = "Exported Computers"; ev.ActionStatus = ActionStatus.OK; LogsController.AddEvent(ev, User.Identity.GetUserId()); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=Computers_" + date.ToShortDateString() + ".xlsx"); Response.BinaryWrite(package.GetAsByteArray()); } catch (Exception) {} }
public ActionResult Edit(ScheduledModel model) { if (ModelState.IsValid) { bool valid = true; // Get info if (model.Type == ScheduledType.Individual) { try { List <int> clist = JsonConvert.DeserializeObject <List <int> >(model.JsonComputerList); if (clist.Count == 0) { ModelState.AddModelError(String.Empty, "The computer list cannot be empty."); valid = false; } try { string names = "|"; foreach (var id in clist) { names += ", " + db.Computers.SingleOrDefault(x => x.Id == id).Name; } model.ComputerListNames = names.Replace("|, ", ""); } catch (Exception) { } } catch (Exception) { ModelState.AddModelError(String.Empty, "The computer list cannot be empty."); valid = false; } } if (model.Type == ScheduledType.Color) { model.JsonComputerList = ""; if (model.ColorId == 0) { ModelState.AddModelError(String.Empty, "Plese select a color."); valid = false; } try { string names = "|"; ColorModel item = db.Colors.Include(x => x.Computers).SingleOrDefault(x => x.Id == model.ColorId); foreach (var computer in item.Computers) { names += ", " + computer.Name; } model.ComputerListNames = names.Replace("|, ", ""); } catch (Exception) { } } if (model.Type == ScheduledType.Location) { model.JsonComputerList = ""; if (model.LocationId == 0) { ModelState.AddModelError(String.Empty, "Plese select a location."); valid = false; } try { string names = "|"; LocationModel item = db.Locations.Include(x => x.Computers).SingleOrDefault(x => x.Id == model.LocationId); foreach (var computer in item.Computers) { names += ", " + computer.Name; } model.ComputerListNames = names.Replace("|, ", ""); } catch (Exception) { } } if (model.Type == ScheduledType.Type) { model.JsonComputerList = ""; if (model.TypeId == 0) { ModelState.AddModelError(String.Empty, "Plese select a Computer Type."); valid = false; } try { string names = "|"; ComputerTypeModel item = db.ComputerTypes.Include(x => x.Computers).SingleOrDefault(x => x.Id == model.TypeId); foreach (var computer in item.Computers) { names += ", " + computer.Name; } model.ComputerListNames = names.Replace("|, ", ""); } catch (Exception) { } } if (valid) { // Event SysEvent ev = new SysEvent(); ev.Action = Enums.Action.Info; ev.Description = "Edited schedule: " + model.Name; ev.ActionStatus = ActionStatus.OK; LogsController.AddEvent(ev, User.Identity.GetUserId()); model.LastRun = DateTime.Now.AddYears(-100); db.Entry(model).State = EntityState.Modified; db.SaveChanges(); return(RedirectToAction("Index")); } else { return(View(model)); } } return(View(model)); }