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