public async Task <FileResult> ExportCollectionReport(int companyId, string sCurrDate) { byte[] b; DateTime curr; sCurrDate = sCurrDate.Replace("'", ""); if (!DateTime.TryParse(sCurrDate, out curr)) { curr = DateTime.Now.ToLocalTime(); } string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}"; DCTSOpenXML oxl = new DCTSOpenXML(); List <Memo> memos = db.Memos.ToList(); using (MemoryStream memStream = new MemoryStream()) { using (SpreadsheetDocument document = SpreadsheetDocument.Create(memStream, SpreadsheetDocumentType.Workbook)) { // Build Excel File WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); // Stylesheet Stylesheet stylesheet = oxl.CreateStyleSheet(); WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>(); Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets()); workbookStylesPart.Stylesheet = stylesheet; workbookStylesPart.Stylesheet.Save(); // declare locals Row row; Cell cell; string loc; int rr; uint shNo = 1; //oxl.columns = columns; //for each collection foreach (Collection collection in db.Collections.Where(c => c.CompanyId == companyId).OrderBy(c => c.Name).Include("Styles")) { WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = shNo++, Name = collection.Name // collection name" }; sheets.Append(sheet); // Clear out the drawing object form each page drawing = null; //columns = new Columns(); Columns columns = new Columns(); Worksheet worksheet = new Worksheet(); SheetData sd = new SheetData(); List <Bitmap> images = new List <Bitmap>(); string imageName = ""; // Build sheet // Title row = new Row(); cell = oxl.SetCellVal("A1", $"Export - Collection {collection.Name} {currDate}", false); row.Append(cell); sd.Append(row); row = new Row(); cell = oxl.SetCellVal("A2", ""); row.Append(cell); sd.Append(row); // Headers row = new Row(); cell = oxl.SetCellVal("A3", "", false, 3); row.Append(cell); columns.Append(new Column() { Width = 15.3D, Min = 1, Max = 1, BestFit = false, CustomWidth = false }); cell = oxl.SetCellVal("B3", "Name", false, 3); row.Append(cell); columns.Append(new Column() { Width = 20.9D, Min = 2, Max = 2, BestFit = false, CustomWidth = false }); cell = oxl.SetCellVal("C3", "Desc", false, 3); row.Append(cell); columns.Append(new Column() { Width = 20.9D, Min = 3, Max = 3, BestFit = false, CustomWidth = false }); //cell = oxl.SetCellVal("D3", "Style No.", false, 3); row.Append(cell); columns.Append(new Column() { Width = 17.2D, Min = 4, Max = 4, BestFit = false, CustomWidth = false }); cell = oxl.SetCellVal("D3", "Inv", false, 3); row.Append(cell); columns.Append(new Column() { Width = 7.67, Min = 4, Max = 4, BestFit = false, CustomWidth = false }); sd.Append(row); worksheet.Append(columns); oxl.columns = columns; List <Style> Styles = collection.Styles.OrderBy(s => s.StyleName).ThenBy(s => s.Desc).ToList(); // Content for (int i = 0; i < Styles.Count(); i++) { row = new Row(); rr = 4 + i; Bitmap image; if (Styles[i].Image == null) { image = new Bitmap(Server.MapPath("/Images") + "/logo.png"); } else { // get the file off storage image = new Bitmap(await Singletons.azureBlobStorage.Download(Styles[i].Image)); //image = Image.FromFile(Server.MapPath("/Images") + "/logo.png"); } images.Add(image); row.Height = oxl.ComputeExcelCellHeight(pixelRowHeight); row.CustomHeight = true; loc = "A" + rr; cell = oxl.SetCellVal(loc, image, pixelRowHeight, false, 3); row.Append(cell); loc = "B" + rr; cell = oxl.SetCellVal(loc, Styles[i].StyleName, false, 3); row.Append(cell); loc = "C" + rr; cell = oxl.SetCellVal(loc, Styles[i].Desc, false, 3); row.Append(cell); //loc = "D" + rr; cell = oxl.SetCellVal(loc, Styles[i].StyleNum, false, 3); row.Append(cell); //List<Memo> memos = db.Memos.Where(m => m.StyleID.Value == Styles[i].Id).ToList(); int styleId = Styles[i].Id; double quantity = memos.Where(m => m.StyleID == styleId).Sum(m => m.Quantity); loc = "D" + rr; cell = oxl.SetCellVal(loc, quantity, false, 3); row.Append(cell); sd.Append(row); } worksheet.Append(sd); worksheetPart.Worksheet = worksheet; // Images Column col0 = columns.ElementAt(0) as Column; double col0Width = col0.Width; for (int i = 0; i < Styles.Count(); i++) { if (Styles[i].Image == null) { imageName = Server.MapPath("/Images") + "/logo.png"; } else { // get the file off storage imageName = Styles[i].Image; //imageName = Server.MapPath("/Images") + "/logo.png"; } rr = 4 + i; // place images in column A string contentType = MimeMapping.GetMimeMapping(imageName); PlaceImageOnCell(worksheet, images[i], 0, rr - 1, col0Width, pixelRowHeight, contentType, Styles[i].StyleName, Styles[i].Desc); } } workbookPart.Workbook.Save(); document.Close(); b = memStream.ToArray(); string compName = db.FindCompany(companyId).Name; return(File(b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", compName + " Collection as of " + $"{currDate}" + ".xlsx")); } } }
public FileResult ExportVendorReport(int companyId, string sCurrDate) { byte[] b; DateTime curr; sCurrDate = sCurrDate.Replace("'", ""); if (!DateTime.TryParse(sCurrDate, out curr)) { curr = DateTime.Now.ToLocalTime(); } string currDate = $"{curr.ToShortDateString()} {curr.ToShortTimeString()}"; DCTSOpenXML oxl = new DCTSOpenXML(); using (MemoryStream memStream = new MemoryStream()) { using (SpreadsheetDocument document = SpreadsheetDocument.Create(memStream, SpreadsheetDocumentType.Workbook)) { // Build Excel File WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); workbookPart.AddNewPart <WorkbookStylesPart>(); workbookPart.WorkbookStylesPart.Stylesheet = oxl.CreateStyleSheet(); workbookPart.WorkbookStylesPart.Stylesheet.Save(); 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; string loc; int rr; Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Vendors" }; sheets.Append(sheet); Worksheet worksheet = new Worksheet(); SheetData sd = new SheetData(); // Build sheet // Title row = new Row(); cell = oxl.SetCellVal("A1", $"Export - Vendors {currDate}"); row.Append(cell); sd.Append(row); row = new Row(); cell = oxl.SetCellVal("A2", ""); row.Append(cell); sd.Append(row); // Headers row = new Row(); oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 1, Max = 1, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal("A3", "Name"); row.Append(cell); oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 2, Max = 2, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal("B3", "Phone"); row.Append(cell); oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 3, Max = 3, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal("C3", "Email"); row.Append(cell); oxl.columns.Append(new Column() { Width = oxl.ComputeExcelCellWidth(oxl.minWidth), Min = 4, Max = 4, BestFit = true, CustomWidth = true }); cell = oxl.SetCellVal("D3", "Sells"); row.Append(cell); worksheet.Append(oxl.columns); sd.Append(row); List <Vendor> vendors = db.Vendors.Where(v => v.CompanyId == companyId).OrderBy(vv => vv.Name).ToList(); // Content int offset = 4; for (int i = 0; i < vendors.Count(); i++) { if (vendors[i].Name == "") // don't print default vendor { offset--; continue; } row = new Row(); rr = offset + i; loc = "A" + rr; cell = oxl.SetCellVal(loc, vendors[i].Name); row.Append(cell); loc = "B" + rr; cell = oxl.SetCellVal(loc, vendors[i].Phone); row.Append(cell); loc = "C" + rr; cell = oxl.SetCellVal(loc, vendors[i].Email); row.Append(cell); loc = "D" + rr; cell = oxl.SetCellVal(loc, vendors[i].Type.Type.ToString()); row.Append(cell); 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", "Vendors as of " + $"{currDate}" + ".xlsx")); } } }