// GET: Stones public ActionResult Index(int?companyId) { if (companyId == null) { return(RedirectToAction("Index", "Home")); } StoneSorter stoneSorter = new StoneSorter(); companyId = companyId.Value; var stones = db.Stones.Where(st => st.CompanyId == companyId).Include(s => s.Company).Include(s => s.Shape).Include(s => s.Vendor); ViewBag.CompanyId = companyId; ViewBag.CompanyName = db._Companies.Find(companyId)?.Name; return(View(stones.AsEnumerable().OrderBy(g => g.Name).ThenBy(h => h.Shape.Name).ThenBy(i => i.StoneSize, stoneSorter).ToList())); }
public FileResult ExportStonesReport(int companyId, string sCurrDate) { byte[] b; sCurrDate = sCurrDate.Replace("'", ""); if (!DateTime.TryParse(sCurrDate, out DateTime curr)) { curr = DateTime.Now.ToLocalTime(); } string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}"; DCTSOpenXML oxl = new DCTSOpenXML(); StoneSorter stoneSorter = new StoneSorter(); using (MemoryStream memStream = new MemoryStream()) { using (SpreadsheetDocument document = SpreadsheetDocument.Create(memStream, SpreadsheetDocumentType.Workbook)) { // Build Excel File WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets()); // declare locals Row row; Cell cell; int rr; Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Stones" }; sheets.Append(sheet); Worksheet worksheet = new Worksheet(); SheetData sd = new SheetData(); // Build sheet // Title row = new Row(); cell = oxl.SetCellVal("A1", $"Export - Stones {currDate}"); row.Append(cell); sd.Append(row); row = new Row(); cell = oxl.SetCellVal("A2", ""); row.Append(cell); sd.Append(row); // Headers row = new Row(); UInt32 cn = 1; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Id"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Name"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Title"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Stone"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Shape"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Vendor"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Carat"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Size"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Price"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Setting Cost"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Qty"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Parent Handle"); row.Append(cell); cn++; oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = cn, Max = cn, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal(oxl.GetCellName(cn, 3), "Tags"); row.Append(cell); cn++; worksheet.Append(oxl.columns); sd.Append(row); var stones = db.Stones.Where(v => v.CompanyId == companyId).Include("Vendor").Include("Shape"); List <Stone> Stones = stones.AsEnumerable() .OrderBy(s => s.Name).ThenBy(s => s.Shape.Name).ThenBy(s => s.StoneSize, stoneSorter).ToList(); // Content for (int i = 0; i < Stones.Count(); i++) { cn = 1; row = new Row(); rr = 4 + i; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Id); row.Append(cell); cn++; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Label); row.Append(cell); cn++; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Title); row.Append(cell); cn++; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Name); row.Append(cell); cn++; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Shape.Name); row.Append(cell); cn++; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Vendor?.Name); row.Append(cell); cn++; if (Stones[i].CtWt == null) { cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), ""); } else { cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].CtWt.Value); } row.Append(cell); cn++; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].StoneSize); row.Append(cell); cn++; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Price); row.Append(cell); cn++; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].SettingCost); row.Append(cell); cn++; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Qty); row.Append(cell); cn++; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].ParentHandle); row.Append(cell); cn++; cell = oxl.SetCellVal(oxl.GetCellName(cn, rr), Stones[i].Tags); row.Append(cell); cn++; sd.Append(row); } worksheet.Append(sd); // Autofit columns - ss:AutoFitWidth="1" worksheetPart.Worksheet = worksheet; workbookPart.Workbook.Save(); document.Close(); b = memStream.ToArray(); return(File(b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Stones as of " + $"{currDate}" + ".xlsx")); } } }