public override void Generate(TemplateReportVm model, string outputFilePath) { //ImageResizer.Resize(LogoFilePath, LogoResized, LogoWidth, LogoHeight, true); File.Copy(TemplateFile, outputFilePath, true); byte[] byteArray = File.ReadAllBytes(outputFilePath); using (MemoryStream stream = new MemoryStream()) { stream.Write(byteArray, 0, byteArray.Length); using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(stream, true)) { spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; // Do work here IEnumerable <Sheet> sheets = spreadSheet.WorkbookPart.Workbook .GetFirstChild <Sheets>().Elements <Sheet>() .Where(s => s.Name == "TrackingReport") .ToArray(); if (!sheets.Any()) { throw new AdsException("No sheets"); } string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(relationshipId); #region First Page //ExcelHelper.AddImage(worksheetPart, LogoResized, 6, 1); Cell cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "J", 1); cell.CellValue = new CellValue(DateTime.Now.ToString(StringConstants.DateFormatSlashes)); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 4); cell.CellValue = new CellValue(model.StartDate); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 5); cell.CellValue = new CellValue(model.SubjectLine); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 6); cell.CellValue = new CellValue(model.FromLine); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 7); cell.CellValue = new CellValue(model.WhiteLabel); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 8); cell.CellValue = new CellValue(model.OrderNumber); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 9); cell.CellValue = new CellValue(model.CampaignName); cell.DataType = new EnumValue <CellValues>(CellValues.String); if (!string.IsNullOrEmpty(model.IoNumber) && !model.IoNumber.EndsWith("RDP")) { uint rowNumber = 10; foreach (var segment in model.Segments) { cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", rowNumber); cell.CellValue = new CellValue(segment.SegmentNumber); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", rowNumber); cell.CellValue = new CellValue(segment.SegmentDataFileUrl); cell.DataType = new EnumValue <CellValues>(CellValues.String); rowNumber++; } } // right side cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "J", 2); cell.CellValue = new CellValue(model.Quantity); cell.DataType = new EnumValue <CellValues>(CellValues.Number); // key stats cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 16); cell.CellValue = new CellValue(model.Quantity); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 19); cell.CellValue = new CellValue(model.Opened); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "G", 19); cell.CellValue = new CellValue(model.Desktop); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "J", 19); cell.CellValue = new CellValue(model.Mobile); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 21); cell.CellValue = new CellValue(model.Clicked); cell.DataType = new EnumValue <CellValues>(CellValues.Number); // Shared and Un-sub stats cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 24); cell.CellValue = new CellValue(model.Bounce); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "H", 24); cell.CellValue = new CellValue(model.Opt); cell.DataType = new EnumValue <CellValues>(CellValues.Number); if (model.IsRetargeting) { cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "G", 27); cell.CellValue = new CellValue(model.RetargetingImpressions); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "J", 27); cell.CellValue = new CellValue(model.RetargetingClicks); } else { cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "D", 27); cell.CellValue = new CellValue(""); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "I", 27); cell.CellValue = new CellValue(""); cell.DataType = new EnumValue <CellValues>(CellValues.String); } #endregion uint start = 32; #region Second Page foreach (var vm in model.PerLink) { PopulateRowTemplate(worksheetPart.Worksheet, vm, start); start++; } #endregion #region Third Page if (File.Exists(ScreenshotFilePath)) { ExcelHelper.AddImage(worksheetPart, ScreenshotFilePath, 69, 2); } #endregion worksheetPart.Worksheet.Save(); spreadSheet.Close(); } File.WriteAllBytes(outputFilePath, stream.ToArray()); } }
public override void Generate(TemplateReportVm model, string outputFilePath) { ImageResizer.Resize(LogoFilePath, LogoResized, LogoWidth, LogoHeight, true); File.Copy(TemplateFile, outputFilePath, true); byte[] byteArray = File.ReadAllBytes(outputFilePath); using (MemoryStream stream = new MemoryStream()) { stream.Write(byteArray, 0, byteArray.Length); using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(stream, true)) { spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; // Do work here IEnumerable <Sheet> sheets = spreadSheet.WorkbookPart.Workbook .GetFirstChild <Sheets>().Elements <Sheet>() .Where(s => s.Name == "TrackingReport") .ToArray(); if (!sheets.Any()) { throw new AdsException("No sheets"); } string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(relationshipId); #region First Page ExcelHelper.AddImage(worksheetPart, LogoResized, 3, 1); ExcelHelper.AddImage(worksheetPart, LogoResized, 68, 1); Cell cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "J", 8); cell.CellValue = new CellValue(model.OrderNumber); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "J", 9); cell.CellValue = new CellValue(model.StartDate); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "J", 10); cell.CellValue = new CellValue(DateTime.Now.ToString(StringConstants.DateFormatSlashes)); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 8); cell.CellValue = new CellValue(model.CampaignName); cell.DataType = new EnumValue <CellValues>(CellValues.String); // send / delviery //cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 14); //cell.CellValue = new CellValue(model.DeliveryPercentage); //cell.DataType = new EnumValue<CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 15); cell.CellValue = new CellValue(model.Quantity); // model.Deployed cell.DataType = new EnumValue <CellValues>(CellValues.Number); //cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 16); //cell.CellValue = new CellValue(model.Quantity); //cell.DataType = new EnumValue<CellValues>(CellValues.String); // opened cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 22); cell.CellValue = new CellValue(model.OpenedPercentage); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 23); cell.CellValue = new CellValue(model.Opened); cell.DataType = new EnumValue <CellValues>(CellValues.Number); // clicks cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "A", 26); cell.CellValue = new CellValue(model.Clicked); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 26); cell.CellValue = new CellValue(model.ClickedPercentage); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 26); cell.CellValue = new CellValue(model.ClickToOpenPercentage); cell.DataType = new EnumValue <CellValues>(CellValues.Number); // bounce & opt cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "A", 29); cell.CellValue = new CellValue(model.Bounce); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 29); cell.CellValue = new CellValue(model.Opt); cell.DataType = new EnumValue <CellValues>(CellValues.Number); // desktop & mobile cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 37); cell.CellValue = new CellValue(model.Desktop); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 38); cell.CellValue = new CellValue(model.Mobile); cell.DataType = new EnumValue <CellValues>(CellValues.Number); // subject from cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "H", 14); cell.CellValue = new CellValue(model.SubjectLine); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "H", 15); cell.CellValue = new CellValue(model.FromLine); cell.DataType = new EnumValue <CellValues>(CellValues.String); // Template 1 Data Files if (Template.Equals(ReportTemplate.Tracking1.ToString()) && (!string.IsNullOrEmpty(model.IoNumber) && !model.IoNumber.EndsWith("RDP"))) { uint rowNumber = 43; foreach (var segment in model.Segments) { cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "A", rowNumber); cell.CellValue = new CellValue(segment.SegmentNumber); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", rowNumber); cell.CellValue = new CellValue(segment.SegmentDataFileUrl); cell.DataType = new EnumValue <CellValues>(CellValues.String); rowNumber++; } } // Template ReTargetting if (Template.Equals(ReportTemplate.TrackingReTargeting.ToString())) { cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 44); cell.CellValue = new CellValue(model.RetargetingImpressions); cell.DataType = new EnumValue <CellValues>(CellValues.Number); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "C", 45); cell.CellValue = new CellValue(model.RetargetingClicks); cell.DataType = new EnumValue <CellValues>(CellValues.Number); } #endregion #region Second Page if (File.Exists(ScreenshotFilePath)) { ExcelHelper.AddImage(worksheetPart, ScreenshotFilePath, 17, 7); } cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "B", 73); cell.CellValue = new CellValue(model.CampaignName); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "J", 73); cell.CellValue = new CellValue(model.OrderNumber); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "J", 74); cell.CellValue = new CellValue(model.StartDate); cell.DataType = new EnumValue <CellValues>(CellValues.String); cell = ExcelHelper.GetCell(worksheetPart.Worksheet, "J", 75); cell.CellValue = new CellValue(DateTime.Now.ToString(StringConstants.DateFormatSlashes)); cell.DataType = new EnumValue <CellValues>(CellValues.String); uint start = 82; int total = 117; foreach (var vm in model.PerLink) { PopulateRowTemplate(worksheetPart.Worksheet, vm, start); start++; } #endregion ExcelHelper.DeleteRows(start, total - (int)start, worksheetPart.Worksheet.GetFirstChild <SheetData>()); worksheetPart.Worksheet.Save(); spreadSheet.Close(); } File.WriteAllBytes(outputFilePath, stream.ToArray()); } }