public virtual void Generate(TemplateReportVm model, string outputFilePath) { throw new NotImplementedException(); }
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 static TemplateReportVm FromCampaignTracking(Campaign campaign, CampaignTracking campaignTracking) { var model = new TemplateReportVm { CampaignId = campaign.Id.ToString(), TrackingId = campaignTracking.Id.ToString(), OrderNumber = string.IsNullOrEmpty(campaignTracking.SegmentNumber) ? campaignTracking.OrderNumber : campaignTracking.SegmentNumber, CampaignName = campaign.Approved.CampaignName, WhiteLabel = campaign.Approved.WhiteLabel, SubjectLine = campaign.Approved.SubjectLine, FromLine = campaign.Approved.FromLine, Quantity = campaignTracking.Quantity.ToString(), Status = ((CampaignStatus)campaign.Status).ToString(), OrderDate = campaign.CreatedAt.ToString(StringConstants.DateFormatSlashes), DateSent = campaignTracking.CreatedAt.ToString(StringConstants.DateFormatSlashes), SentOrder = campaignTracking.SentOrder, IsCreatedThroughApi = campaignTracking.IsCreatedThroughApi, QueuedCampaignId = campaignTracking.QueuedCampaignId, IoNumber = campaignTracking.IoNumber, StartDate = campaignTracking.StartDate?.ToString(StringConstants.DateFormatSlashes), Deployed = campaignTracking.Deployed.ToString(), Opened = campaignTracking.Opened.ToString(), Clicked = campaignTracking.Clicked.ToString(), Unsub = campaignTracking.Unsub.ToString(), Forwards = campaignTracking.Forwards.ToString(), Bounce = campaignTracking.Bounce.ToString(), Opt = campaignTracking.Opt.ToString(), Desktop = campaignTracking.Desktop.ToString(), Mobile = campaignTracking.Mobile.ToString(), IsRetargeting = campaign.Approved.ReBroadCast, RetargetingImpressions = campaignTracking.RetargetingImpressions.ToString(), RetargetingClicks = campaignTracking.RetargetingClicks.ToString(), DeliveryPercentage = campaignTracking.DeliveryPercentage.ToString("0.0000"), OpenedPercentage = campaignTracking.OpenedPercentage.ToString("0.0000"), ClickedPercentage = campaignTracking.ClickedPercentage.ToString("0.0000"), UnsubPercentage = campaignTracking.UnsubPercentage.ToString("0.0000"), ClickToOpenPercentage = campaignTracking.ClickToOpenPercentage.ToString("0.0000"), UnsubToOpenPercentage = campaignTracking.UnsubToOpenPercentage.ToString("0.0000"), Segments = new List <CampaignSegmentVm>(), PerLink = new List <TemplateReportDetailVm>() }; model.Segments = campaign.Segments.Where(x => !string.IsNullOrEmpty(x.SegmentDataFileUrl)) .Select(x => new CampaignSegmentVm() { SegmentNumber = x.SegmentNumber, SegmentDataFileUrl = x.SegmentDataFileUrl }).OrderBy(x => x.SegmentNumber).ToList(); var proDatas = campaign.ProDatas .Where(x => x.OrderNumber == campaignTracking.OrderNumber && x.SegmentNumber == campaignTracking.SegmentNumber) .OrderByDescending(x => x.ClickCount); //if (proDatas.Count() == 0) throw new AdsException("Tracking links not found."); foreach (var proData in proDatas) { model.PerLink.Add(new TemplateReportDetailVm() { Link = proData.Destination_URL, ClickCount = proData.ClickCount, UniqueCount = proData.UniqueCnt, MobileCount = proData.MobileCnt }); } return(model); }
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()); } }