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