private void PopulateSheetData(List <ActualFlight> list, XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheet, ActualWorksheet item)
{
NumberFormatInfo format = CultureInfo.CurrentCulture.NumberFormat;
string currencyFormat = "#,##0.00";
currencyFormat = currencyFormat.Replace(",", format.CurrencyGroupSeparator).Replace(".", format.CurrencyDecimalSeparator);
List <ExcelCell> row = new List <ExcelCell>();
ExcelCell cell = new ExcelCell();
foreach (var rec in list)
{
cell = new ExcelCell();
cell.CellHeader = item.Data;
cell.CellValue = rec.DataToString();
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.PlanName;
cell.CellValue = rec.PlanName;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.VehicleName;
cell.CellValue = rec.VehicleName;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.ProductName;
cell.CellValue = rec.ProductName;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.SubtypeName;
cell.CellValue = rec.SubtypeName;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.UnitName;
cell.CellValue = rec.UnitName;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.MarketName;
cell.CellValue = rec.MarketName;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.DemoName;
cell.CellValue = rec.DemoName;
row.Add(cell);
foreach (var it in rec.CustomColumnsValues)
{
cell = new ExcelCell();
cell.CellHeader = it.Key;
cell.CellValue = it.Value;
row.Add(cell);
}
if (rec.CustomColumnsValues == null || rec.CustomColumnsValues.Count == 0)
{
cell = new ExcelCell();
cell.CellHeader = item.CreativeName;
cell.CellValue = rec.CreativeName;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.FundingSourceName;
cell.CellValue = rec.FundingSourceName;
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.StartDate;
cell.CellValue = string.Format("{0:MM/dd/yyyy}", rec.StartDate);
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.EndDate;
cell.CellValue = string.Format("{0:MM/dd/yyyy}", rec.EndDate);
row.Add(cell);
//planned
cell = new ExcelCell();
cell.CellHeader = item.PlannedGRPs;
cell.CellValue = rec.Planned.GRPs;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.PlannedGross;
cell.CellValue = rec.Planned.GrossCost;
if (sheet.SheetColumns.Any(x => x.Header == item.PlannedGross))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.PlannedNet;
cell.CellValue = rec.Planned.NetCost;
if (sheet.SheetColumns.Any(x => x.Header == item.PlannedNet))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.PlannedProduction;
cell.CellValue = rec.Planned.Production;
if (sheet.SheetColumns.Any(x => x.Header == item.PlannedProduction))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.PlannedImpressions;
cell.CellValue = rec.Planned.Impressions;
if (sheet.SheetColumns.Any(x => x.Header == item.PlannedImpressions))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.PlannedClicks;
cell.CellValue = rec.Planned.Clicks;
if (sheet.SheetColumns.Any(x => x.Header == item.PlannedClicks))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.PlannedLeads;
cell.CellValue = rec.Planned.Leads;
if (sheet.SheetColumns.Any(x => x.Header == item.PlannedLeads))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.PlannedRichMedia;
cell.CellValue = rec.Planned.RichMedia;
if (sheet.SheetColumns.Any(x => x.Header == item.PlannedRichMedia))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.PlannedAdServing;
cell.CellValue = rec.Planned.AdServing;
if (sheet.SheetColumns.Any(x => x.Header == item.PlannedAdServing))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.PlannedTRPs;
cell.CellValue = rec.Planned.TRPs;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.PlannedReach;
cell.CellValue = rec.Planned.Reach;
if (sheet.SheetColumns.Any(x => x.Header == item.PlannedReach))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.PlannedFrequency;
cell.CellValue = rec.Planned.Frequency;
if (sheet.SheetColumns.Any(x => x.Header == item.PlannedFrequency))
{
row.Add(cell);
}
if (rec.Actualized)
{
cell = new ExcelCell();
cell.CellHeader = item.DateActualized;
cell.CellValue = string.Format("{0:MM/dd/yyyy}", rec.DateActualized);
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.ActualizedBy;
cell.CellValue = rec.ActualizedBy;
row.Add(cell);
}
else
{
cell.CellHeader = item.DateActualized;
cell.CellValue = string.Empty;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.ActualizedBy;
cell.CellValue = string.Empty;
row.Add(cell);
}
////actual
//if (rec.Actual == null)
//{
// sheet.AppendRow(row);
// continue;
//}
cell = new ExcelCell();
cell.CellHeader = item.ActualGRPs;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.GRPs;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.ActualGross;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.GrossCost;
if (sheet.SheetColumns.Any(x => x.Header == item.ActualGross))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.ActualNet;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.NetCost;
if (sheet.SheetColumns.Any(x => x.Header == item.ActualNet))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.ActualProduction;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.Production;
if (sheet.SheetColumns.Any(x => x.Header == item.ActualProduction))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.ActualImpressions;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.Impressions;
if (sheet.SheetColumns.Any(x => x.Header == item.ActualImpressions))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.ActualClicks;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.Clicks;
if (sheet.SheetColumns.Any(x => x.Header == item.ActualClicks))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.ActualLeads;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.Leads;
if (sheet.SheetColumns.Any(x => x.Header == item.ActualLeads))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.ActualRichMedia;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.RichMedia;
if (sheet.SheetColumns.Any(x => x.Header == item.ActualRichMedia))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.ActualAdServing;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.AdServing;
if (sheet.SheetColumns.Any(x => x.Header == item.ActualAdServing))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.ActualTRPs;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.TRPs;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = item.ActualReach;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.Reach;
if (sheet.SheetColumns.Any(x => x.Header == item.ActualReach))
{
row.Add(cell);
}
cell = new ExcelCell();
cell.CellHeader = item.ActualFrequency;
cell.CellValue = (rec.Actual == null) ? 0 : rec.Actual.Frequency;
if (sheet.SheetColumns.Any(x => x.Header == item.ActualFrequency))
{
row.Add(cell);
}
sheet.AppendRow(row);
}
}
private void PopulateRows(List <string> columns, object value, XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheetBuilder, ExcelColumnInfoCollection columnInfo = null, IXlsxDocumentBuilder document = null, List <ExcelCell> rowOverride = null)
{
if (sheetBuilder == null)
{
return;
}
List <ExcelCell> row = new List <ExcelCell>();
if (rowOverride != null)
{
row = rowOverride;
}
for (int i = 0; i <= columns.Count - 1; i++)
{
string columnName = columns[i];
if (columnName.EndsWith("_Dict_"))
{
columnName = columnName.Replace("_Dict_", "");
object dictionaryObj = null;
if (sheetBuilder.GetCurrentTableName == columnName)
{
dictionaryObj = value;
}
else if (columnName.Contains(":"))
{
dictionaryObj = GetFieldPathValue(value, columnName);
}
else
{
dictionaryObj = (Dictionary <int, double>)GetFieldOrPropertyValue(value, columnName);
}
if (dictionaryObj == null || string.IsNullOrEmpty(dictionaryObj.ToString()))
{
continue;
}
object dictionaryKeys = dictionaryObj.GetType().GetProperty("Keys").GetValue(dictionaryObj);
int colCount = 0;
foreach (var key in (System.Collections.IEnumerable)dictionaryKeys)
{
ExcelCell keyCell = new ExcelCell();
keyCell.CellHeader = columnName + $":Key:{colCount}";
keyCell.CellValue = key;
ExcelColumnInfo info = null;
if (columnInfo != null)
{
info = columnInfo[i];
CreateReferenceCell(info, columnName, document, ref keyCell);
}
row.Add(keyCell);
var currentItem = dictionaryObj.GetType().GetProperty("Item").GetValue(dictionaryObj, new object[] { key });
if (FormatterUtils.IsSimpleType(currentItem.GetType()))
{
ExcelCell valueCell = new ExcelCell();
valueCell.CellHeader = columnName + $":Value:{colCount}";
valueCell.CellValue = currentItem;
row.Add(valueCell);
}
else
{
string path = columnName + $":Value:{colCount}";
ExcelColumnInfoCollection listInnerObjectColumnInfo = _columnResolver.GetExcelColumnInfo(currentItem.GetType(), currentItem, path, true);
PopulateRows(listInnerObjectColumnInfo.Keys.ToList(), currentItem, sheetBuilder, listInnerObjectColumnInfo, document, row);
}
colCount++;
}
}
else if (columnName.EndsWith("_List_"))
{
columnName = columnName.Replace("_List_", "");
List <object> listValue = new List <object>();
if (columnName.Contains(":"))
{
var valueObject = GetFieldPathValue(value, columnName);
if (valueObject != null && string.IsNullOrEmpty(valueObject.ToString()) == false)
{
listValue = (List <object>)valueObject;
}
}
else
{
listValue = (List <object>)GetFieldOrPropertyValue(value, columnName);
}
int colCount = 0;
foreach (var kv in listValue)
{
string listColumnPrefix = columnName + $":{colCount}";
if (FormatterUtils.IsSimpleType(kv.GetType()))
{
ExcelCell listValueCell = new ExcelCell();
listValueCell.CellHeader = listColumnPrefix;
listValueCell.CellValue = kv;
row.Add(listValueCell);
}
else
{
ExcelColumnInfoCollection listInnerObjectColumnInfo = _columnResolver.GetExcelColumnInfo(kv.GetType(), kv, listColumnPrefix, true);
PopulateRows(listInnerObjectColumnInfo.Keys.ToList(), kv, sheetBuilder, listInnerObjectColumnInfo, document, row);
}
colCount++;
}
}
else if (columnName.EndsWith("_CustomField_") || columnName.EndsWith("_CustomField_Single_"))
{
bool isSingleValue = columnName.Contains("Single_");
columnName = columnName.Replace("_CustomField_", "").Replace("Single_", "");
List <object> customFields = null;
if (columnName.Contains(":"))
{
var valueObject = GetFieldPathValue(value, columnName);
if (valueObject != null && string.IsNullOrEmpty(valueObject.ToString()) == false)
{
customFields = (List <object>)valueObject;
}
}
else
{
customFields = (List <object>)GetFieldOrPropertyValue(value, columnName);
}
if (customFields == null)
{
continue;
}
//need to get all custom columns
List <ExcelColumnInfo> allCustomColumns = null;
if (isSingleValue)
{
allCustomColumns = sheetBuilder.SheetColumns.Where(w => w.PropertyName == columnName).ToList();
}
else
{
allCustomColumns = sheetBuilder.SheetColumns.Where(w => w.PropertyName.StartsWith(columnName)).ToList();
}
var objID = GetFieldOrPropertyValue(value, "ID");
foreach (var customColumn in allCustomColumns)
{
object objectCustomField = customFields.Where(w => customColumn.PropertyName.EndsWith($":{((dynamic)w).ID}")).Where(w => customColumn.PropertyName.Contains("Actual") ? ((dynamic)w).Actual == true : ((dynamic)w).Actual == false).FirstOrDefault();
ExcelCell customValueHeaderCell = new ExcelCell();
if (objectCustomField == null && !isSingleValue)
{
customValueHeaderCell.IsLocked = true;
customValueHeaderCell.CellHeader = customColumn.Header;
customValueHeaderCell.CellValue = "n/a";
}
else
{
dynamic customFieldItem = (dynamic)objectCustomField;
string isActualText = string.Empty;
string columnNameCombined = string.Empty;
if (isSingleValue)
{
isActualText = columnName.Contains("Actual") ? ":Actual" : string.Empty;
columnNameCombined = $"{columnName}{isActualText}";
customFieldItem = (dynamic)customFields.First();
}
else
{
isActualText = customFieldItem.Actual ? ":Actual" : string.Empty;
columnNameCombined = $"{columnName}{isActualText}:{customFieldItem.ID}";
}
customValueHeaderCell.CellHeader = customColumn.Header;
if (customFieldItem is CustomFieldModel)
{
if (!String.IsNullOrEmpty((customFieldItem as CustomFieldModel).Key))
{
ExcelCell keyPreservationCell = new ExcelCell();
keyPreservationCell.CellHeader = $"{columnNameCombined}:Key:{objID}";
keyPreservationCell.CellValue = customFieldItem.Key?.ToString();
CreatePreserveCell(keyPreservationCell, document);
}
}
ExcelCell valuePreservationCell = new ExcelCell();
valuePreservationCell.CellHeader = $"{columnNameCombined}:Value:{objID}";
if (customFieldItem != null)
{
valuePreservationCell.CellValue = customFieldItem.Value;
customValueHeaderCell.CellValue = customFieldItem.Value;
if (customFieldItem.Override != null)
{
customValueHeaderCell.CellValue = customFieldItem.Override;
}
}
if (valuePreservationCell.CellValue != null && valuePreservationCell.CellValue.GetType() == typeof(DateTime))
{
valuePreservationCell.CellValue = valuePreservationCell.CellValue.ToString();
}
if (customValueHeaderCell.CellValue != null && customValueHeaderCell.CellValue.GetType() == typeof(DateTime))
{
customValueHeaderCell.CellValue = customValueHeaderCell.CellValue.ToString();
}
CreatePreserveCell(valuePreservationCell, document);
ExcelCell overridePreservationCell = new ExcelCell();
overridePreservationCell.CellHeader = $"{columnNameCombined}:Override:{objID}";
overridePreservationCell.CellValue = customFieldItem.Override?.ToString();
CreatePreserveCell(overridePreservationCell, document);
ExcelCell textPreservationCell = new ExcelCell();
textPreservationCell.CellHeader = $"{columnNameCombined}:Text:{objID}";
textPreservationCell.CellValue = customFieldItem.Text;
CreatePreserveCell(textPreservationCell, document);
ExcelCell hiddenTextPreservationCell = new ExcelCell();
hiddenTextPreservationCell.CellHeader = $"{columnNameCombined}:HiddenText:{objID}";
hiddenTextPreservationCell.CellValue = $"\"{customFieldItem.HiddenText}\"";
CreatePreserveCell(hiddenTextPreservationCell, document);
try
{
if (customFieldItem is CustomFieldModel &&
(customFieldItem as CustomFieldModel).ValueMix != null)
{
foreach (var valueM in (customFieldItem as CustomFieldModel).ValueMix)
{
string valueMKey = valueM.Key;
decimal valueMValue = valueM.Value;
ExcelCell mixedPropertyPreservationCell = new ExcelCell();
mixedPropertyPreservationCell.CellHeader = $"{columnNameCombined}:ValueMix:{valueM.Key}:{objID}";
mixedPropertyPreservationCell.CellValue = $"\"{valueM.Value.ToString()}\"";
CreatePreserveCell(mixedPropertyPreservationCell, document);
}
}
} catch (Exception ex)
{
string s = ex.Message;
}
ExcelCell commonPropertyPreservationCell = new ExcelCell();
commonPropertyPreservationCell.CellHeader = $"{columnNameCombined}:Common:{objID}";
commonPropertyPreservationCell.CellValue = $"\"{customFieldItem.Common}\"";
CreatePreserveCell(commonPropertyPreservationCell, document);
}
row.Add(customValueHeaderCell);
}
}
else
{
ExcelCell cell = new ExcelCell();
cell.CellHeader = columnName;
var cellValue = GetFieldOrPropertyValue(value, columnName);
if (columnName.Contains(":") && (cellValue == null || (cellValue != null && string.IsNullOrEmpty(cellValue.ToString()))))
{
cellValue = GetFieldPathValue(value, columnName);
}
ExcelColumnInfo info = null;
if (columnInfo != null)
{
info = columnInfo[i];
CreateReferenceCell(info, columnName, document, ref cell);
}
if (cellValue != null)
{
cell.CellValue = FormatCellValue(cellValue, info);
}
if (info != null)
{
if (info.IsExcelHeaderDefined)
{
cell.CellHeader = info.Header;
}
}
row.Add(cell);
}
}
if (row.Count() > 0 && rowOverride == null)
{
sheetBuilder.AppendRow(row.ToList());
}
}
