public void CreatePreserveCell(ExcelCell cell, IXlsxDocumentBuilder document)
{
string _PreservationSheetName_ = "PreservationSheet";
var preservationSheet = document.GetPreservationSheet() as XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder;
if (preservationSheet == null)
{
preservationSheet = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder(_PreservationSheetName_, false, true, true);
document.AppendSheet(preservationSheet);
}
else
{
preservationSheet.AddAndActivateNewTable(_PreservationSheetName_);
}
preservationSheet.AppendColumnHeaderRowItem(cell.CellHeader);
Dictionary <string, object> resolveRow = new Dictionary <string, object>();
resolveRow.Add(cell.CellHeader, cell.CellValue);
PopulateRows(resolveRow.Keys.ToList(), resolveRow, preservationSheet);
}
private void PopulateInnerObjectSheets(ExcelSheetInfoCollection sheetsInfo, IXlsxDocumentBuilder document, Type itemType)
{
if (sheetsInfo == null)
{
return;
}
foreach (var sheet in sheetsInfo)
{
if (!(sheet.ExcelSheetAttribute is ExcelSheetAttribute))
{
continue;
}
string sheetName = sheet.ExcelSheetAttribute != null ? (sheet.ExcelSheetAttribute as ExcelSheetAttribute).SheetName : itemType.Name;
if (sheetName == null)
{
sheetName = sheet.SheetName;
}
//sheetBuilder = new SqadXlsxSheetBuilder(document.AppendSheet(sheetName));
this.Serialize(sheet.SheetType, sheet.SheetObject, document, sheetName);//, sheetBuilder);
//sheetBuilder = null;
}
}
public void Serialize(Type itemType, object value, IXlsxDocumentBuilder document, string sheetName, string columnPrefix, XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheetBuilderOverride)
{
if (!(value is DataTable dataTable))
{
throw new ArgumentException($"{nameof(value)} has invalid type!");
}
var dataRows = dataTable.Rows.Cast <DataRow>();
var records = dataRows.Select(x => new FormattedExcelDataRow(x)).ToList();
var columns = dataTable.Columns;
columns.RemoveAt(columns.Count - 1);
var sheetBuilder = new SqadXlsxFormattedViewSheetBuilder(records.Count(x => x.IsHeader), _viewLabel);
document.AppendSheet(sheetBuilder);
sheetBuilder.AppendColumns(columns);
PopulateData(sheetBuilder, columns, records);
var scriptBuilder = new SqadXlsxFormattedViewScriptsSheetBuilder(_viewLabel);
document.AppendSheet(scriptBuilder);
}
public void Serialize(Type itemType,
object value,
IXlsxDocumentBuilder document,
string sheetName,
string columnPrefix,
XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheetBuilderOverride)
{
if (!(value is DataSet dataSet))
{
throw new ArgumentException($"{nameof(value)} has invalid type!");
}
var tables = dataSet.Tables;
if (!string.Equals(_dataTableName, ExportViewConstants.UnformattedViewDataSheetName))
{
var dataTable = tables[ExportViewConstants.UnformattedViewDataSheetName];
dataTable.TableName = _dataTableName;
}
ProcessInstructionsSheet(document, tables);
var settings = GetSettings(tables);
ProcessDataSheet(document, tables);
var needCreatePivotSheet = tables.Contains(PivotTableName);
var scriptBuilder = new SqadXlsxUnformattedViewScriptSheetBuilder(settings, needCreatePivotSheet, _dataTableName);
document.AppendSheet(scriptBuilder);
}
public void Serialize(Type itemType, object value, IXlsxDocumentBuilder document, string sheetName, string columnPrefix, XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheetBuilderOverride)
{
if (!(value is ActualExport ae))
{
throw new ArgumentException($"{nameof(value)} has invalid type!");
}
var export = (ActualExport)value;
CreatePropertySheet(document, export);
PopulateSheets(document, export);
CreateReferenceSheet(document, export.Id);
}
public void MapContent(IXlsxDocumentBuilder documentBuilder, IEnumerable <Person> content)
{
int row = 0;
foreach (var i in content)
{
int colum = 0;
documentBuilder.SetCellValue(row, colum, i.Name);
colum++;
documentBuilder.SetCellValue(row, colum, i.Surname);
row++;
}
}
public void Serialize(Type itemType,
object value,
IXlsxDocumentBuilder document,
string sheetName,
string columnPrefix,
SqadXlsxSheetBuilder sheetBuilderOverride)
{
if (!(value is ExportPlanRequest exportPlanRequest))
{
throw new ArgumentException($"{nameof(value)} has invalid type!");
}
document.AppendSheet(new FormattedPlanSheetBuilder("Formatted Plan", exportPlanRequest));
}
private void CreatePropertySheet(IXlsxDocumentBuilder document, ActualExport value)
{
string name = "Properties";
//var instructionsDataTable = tables[InstructionsTableName];
var properties = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder(name);
properties.ActualRow = true;
document.AppendSheet(properties);
AppendColumnsAndRows(properties, value);
}
private void ProcessInstructionsSheet(IXlsxDocumentBuilder document, DataTableCollection tables)
{
if (!tables.Contains(InstructionsTableName))
{
return;
}
var instructionsDataTable = tables[InstructionsTableName];
var instructionsSheetBuilder = new SqadXlsxUnformattedViewInstructionsSheetBuilder();
document.AppendSheet(instructionsSheetBuilder);
AppendColumnsAndRows(instructionsSheetBuilder, instructionsDataTable);
}
public void Serialize(Type itemType,
object value,
IXlsxDocumentBuilder document,
string sheetName,
string columnPrefix,
SqadXlsxSheetBuilder sheetBuilderOverride)
{
if (!(value is IEnumerable <ExportResultItem <ExcelRowBase> > exportResults))
{
throw new ArgumentException($"{nameof(value)} has invalid type!");
}
document.SetTemplateInfo(new XlsxTemplateInfo("ExcelTemplates/Internal_Database_Setup.xlsm",
null)); //todo: add password protection
document.AppendSheet(new SqadXlsxInternalDatabaseSetupReferenceSheetBuilder(exportResults));
}
public void Serialize(Type itemType,
object value,
IXlsxDocumentBuilder document,
string sheetName,
string columnPrefix,
XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheetbuilderOverride)
{
if (!(value is DeliverySourceExportDataModel exportData))
{
throw new ArgumentException($"{nameof(value)} has invalid type!");
}
var dataTable = CreateDataTable(exportData);
var sheetBuilder = new SqadDeliverySourceDataSheetBuilder("Data", dataTable, exportData.DeliveryPeriods.Count);
document.AppendSheet(sheetBuilder);
}
private void PopulateSheets(IXlsxDocumentBuilder document, ActualExport export)
{
foreach (var item in export.Sheets)
{
var sheet = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder(item.MediaType.Name);
sheet.ExternalActualsLabel = export.ExternalActualsLabel;
sheet.ActualRow = true;
sheet.ColNames = PopulateNameCollection(item);
// get offset
var offset = export.Flights.FirstOrDefault(x => x.MediaTypeID == item.MediaType.Id);
item.SetActualWorksheet(offset.CustomColumnsValues.Count);
FormatWorkSheet(sheet, item, offset.CustomColumnsValues);
PopulateSheetData(export.Flights.Where(x => x.MediaTypeID == item.MediaType.Id).ToList(), sheet, item);
//HideShowMeasureColumns(sheet, item);
document.AppendSheet(sheet);
}
}
private void ProcessDataSheet(IXlsxDocumentBuilder document,
DataTableCollection tables)
{
if (!tables.Contains(_dataTableName))
{
return;
}
var dataTable = tables[_dataTableName];
//note: dirty fix, remove dummy row for JSON deserialization
dataTable.Rows.RemoveAt(0);
var dataSheetBuilder = new SqadXlsxUnformattedViewDataSheetBuilder(_dataTableName);
document.AppendSheet(dataSheetBuilder);
AppendColumnsAndRows(dataSheetBuilder, dataTable);
}
private void CreateReferenceSheet(IXlsxDocumentBuilder document, int id)
{
var builder = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder("Reference", isPreservationSheet: true, isHidden: true);
builder.ActualRow = true;
document.AppendSheet(builder);
var excelinfo = new ExcelColumnInfo(id.ToString(), null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(" ", null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo("Version", null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo("2.1", null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
public void Serialize(Type itemType, object value, IXlsxDocumentBuilder document, string sheetName, string columnPrefix, XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheetbuilderOverride)
{
if (!(value is ApprovalReportExportRequestModel approvalReportExportRequest))
{
throw new ArgumentException($"{nameof(value)} has invalid type!");
}
var approvalReportDataTable = CreateApprovalReportDataTable(approvalReportExportRequest);
var columns = approvalReportDataTable.Columns;
var rows = approvalReportDataTable.Rows;
var startDateApprovalReport = approvalReportExportRequest.StartDate;
var endDateApprovalReport = approvalReportExportRequest.EndDate;
var approvalType = string.Join(',', approvalReportExportRequest.ApprovalReports.Select(item => item.ApprovalType).Distinct());
//We should minus 2 for count columns for delete IsEvenGroup and CurrencySymbol from Worksheet. Because it's flag field
var sheetBuilder = new SqadXlsxApprovalReportSheetBuilder(startHeaderIndex: 5, startDataIndex: 6,
totalCountColumns: columns.Count - 2, totalCountRows: rows.Count, startDateApprovalReport: startDateApprovalReport,
endDateApprovalReport: endDateApprovalReport, approvalType: approvalType);
document.AppendSheet(sheetBuilder);
sheetBuilder.AppendColumns(columns);
PopulateData(sheetBuilder, columns, rows.Cast <DataRow>().Select(item => new ExcelDataRow(item)));
}
public void CreateReferenceCell(ExcelColumnInfo info, string columnName, IXlsxDocumentBuilder document, ref ExcelCell cell)
{
if (_staticValuesResolver == null)
{
return;
}
DataTable columntResolveTable = null;
if (info.PropertyType != null && info.PropertyType.BaseType == typeof(Enum))
{
columntResolveTable = _staticValuesResolver.GetRecordsFromEnum(info.PropertyType);
info.ExcelColumnAttribute.ResolveFromTable = columnName;
}
else if (string.IsNullOrEmpty(info.ExcelColumnAttribute.ResolveFromTable) == false)
{
_resolveTables.TryGetValue(info.ExcelColumnAttribute.ResolveFromTable, out columntResolveTable);
if (columntResolveTable == default(DataTable))
{
columntResolveTable = _staticValuesResolver.GetRecordsByTableName(info.ExcelColumnAttribute.ResolveFromTable);
if (columntResolveTable != null)
{
_resolveTables.Add(info.ExcelColumnAttribute.ResolveFromTable, columntResolveTable);
}
}
}
if (!_isExportJsonToXls)
{
if (columntResolveTable != null)
{
columntResolveTable.TableName = info.ExcelColumnAttribute.ResolveFromTable;
if (string.IsNullOrEmpty(info.ExcelColumnAttribute.OverrideResolveTableName) == false)
{
columntResolveTable.TableName = info.ExcelColumnAttribute.OverrideResolveTableName;
}
cell.DataValidationSheet = columntResolveTable.TableName;
var referenceSheet = document.GetReferenceSheet() as XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder;
if (referenceSheet == null)
{
referenceSheet = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder(cell.DataValidationSheet, true);
document.AppendSheet(referenceSheet);
}
else
{
referenceSheet.AddAndActivateNewTable(cell.DataValidationSheet);
}
cell.DataValidationBeginRow = referenceSheet.GetNextAvailableRow();
this.PopulateReferenceSheet(referenceSheet, columntResolveTable);
cell.DataValidationRowsCount = referenceSheet.GetCurrentRowCount;
if (string.IsNullOrEmpty(info.ExcelColumnAttribute.ResolveName) == false)
{
cell.DataValidationNameCellIndex = referenceSheet.GetColumnIndexByColumnName(info.ExcelColumnAttribute.ResolveName);
}
if (string.IsNullOrEmpty(info.ExcelColumnAttribute.ResolveValue) == false)
{
cell.DataValidationValueCellIndex = referenceSheet.GetColumnIndexByColumnName(info.ExcelColumnAttribute.ResolveValue);
}
}
else if (string.IsNullOrEmpty(info.ExcelColumnAttribute.ResolveFromTable) == false)
{
columntResolveTable = _staticValuesResolver.GetRecordsByTableName(info.ExcelColumnAttribute.ResolveFromTable);
}
}
}
public XlsxDocumentGenerator(IXlsxDocumentBuilder documentBuilder,
IDocumentContentMapper <IXlsxDocumentBuilder, IEnumerable <Person> > documentContentMapper) : base(
documentBuilder, documentContentMapper)
{
}
public void Serialize(Type itemType, object value, IXlsxDocumentBuilder document, string sheetName = null, string columnPrefix = null, XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheetBuilderOverride = null)
{
ExcelColumnInfoCollection columnInfo = _columnResolver.GetExcelColumnInfo(itemType, value, sheetName);
XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder sheetBuilder = null;
if (sheetName == null)
{
var sheetAttribute = itemType.GetCustomAttributes(true).SingleOrDefault(s => s is ExcelSheetAttribute);
sheetName = sheetAttribute != null ? (sheetAttribute as ExcelSheetAttribute).SheetName : itemType.Name;
}
if (columnInfo.Any())
{
if (sheetBuilderOverride == null)
{
sheetBuilder = document.GetSheetByName(sheetName) as XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder;
}
else
{
sheetBuilder = sheetBuilderOverride;
}
if (sheetBuilder == null)
{
sheetBuilder = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder(sheetName);
//Move this to attribute hidden property
//if (new List<string>() { "Formulas", "LeftTableColumn", "Cells" }.Contains(sheetName))
//{
// sheetBuilder.IsHidden = true;
//}
document.AppendSheet(sheetBuilder);
}
//Convert Dictionary Column
foreach (var col in columnInfo)
{
if (col.PropertyName.EndsWith("_Dict_"))
{
string columnName = col.PropertyName.Replace("_Dict_", "");
object colValueDict = null;
if (sheetName == col.PropertyName.Replace("_Dict_", ""))
{
colValueDict = value;
}
else
{
colValueDict = GetFieldOrPropertyValue(value, col.PropertyName.Replace("_Dict_", ""));
}
if (columnName.Contains(":") && (colValueDict == null || (colValueDict != null && string.IsNullOrEmpty(colValueDict.ToString()))))
{
colValueDict = GetFieldPathValue(value, columnName);
}
if (colValueDict == null || string.IsNullOrEmpty(colValueDict.ToString()))
{
continue;
}
object dictionaryKeys = colValueDict.GetType().GetProperty("Keys").GetValue(colValueDict);
int count = 0;
foreach (var key in (System.Collections.IEnumerable)dictionaryKeys)
{
ExcelColumnInfo temlKeyColumn = col.Clone() as ExcelColumnInfo;
temlKeyColumn.PropertyName = temlKeyColumn.PropertyName.Replace("_Dict_", $":Key:{count}");
sheetBuilder.AppendColumnHeaderRowItem(temlKeyColumn);
var currentItem = colValueDict.GetType().GetProperty("Item").GetValue(colValueDict, new object[] { key });
if (FormatterUtils.IsSimpleType(currentItem.GetType()))
{
ExcelColumnInfo temlValueColumn = col.Clone() as ExcelColumnInfo;
temlValueColumn.PropertyName = temlValueColumn.PropertyName.Replace("_Dict_", $":Value:{count}");
sheetBuilder.AppendColumnHeaderRowItem(temlValueColumn);
}
else
{
string path = col.PropertyName.Replace("_Dict_", $":Value:{count}");
this.Serialize(currentItem.GetType(), value, document, sheetName, path, sheetBuilderOverride);
}
count++;
}
}
else if (col.PropertyName.EndsWith("_List_"))
{
string columnName = col.PropertyName.Replace("_List_", "");
List <object> colListValue = GetFieldOrPropertyValue(value, col.PropertyName.Replace("_List_", "")) as List <object>;
if (columnName.Contains(":") && (colListValue == null || (colListValue != null && string.IsNullOrEmpty(colListValue.ToString()))))
{
colListValue = GetFieldPathValue(value, columnName) as List <object>;
}
if (colListValue == null)
{
continue;
}
int dictColumnCount = colListValue.Count();
for (int i = 0; i < dictColumnCount; i++)
{
string listColumnPrefix = col.PropertyName.Replace("_List_", $":{i}");
if (FormatterUtils.IsSimpleType(colListValue[i].GetType()))
{
ExcelColumnInfo colToAppend = (ExcelColumnInfo)col.Clone();
colToAppend.PropertyName = listColumnPrefix;
sheetBuilder.AppendColumnHeaderRowItem(colToAppend);
}
else
{
this.Serialize(colListValue[i].GetType(), colListValue[i], document, null, listColumnPrefix, sheetBuilder);
}
}
}
else if (col.PropertyName.EndsWith("_CustomField_") || col.PropertyName.EndsWith("_CustomField_Single_"))
{
string columnName = col.PropertyName.Replace("_CustomField_", "").Replace("Single_", "");
List <object> colCustomFields = GetFieldOrPropertyValue(value, columnName) as List <object>;
if (columnName.Contains(":") && (colCustomFields == null || (colCustomFields != null && string.IsNullOrEmpty(colCustomFields.ToString()))))
{
colCustomFields = GetFieldPathValue(value, columnName) as List <object>;
}
if (colCustomFields == null)
{
continue;
}
foreach (var customField in colCustomFields)
{
int customFieldId = ((dynamic)customField).ID;
bool isActual = ((dynamic)customField).Actual;
ExcelColumnInfo temlKeyColumn = col.Clone() as ExcelColumnInfo;
string propetyActual = isActual ? ":Actual" : string.Empty;
string customFieldDef = _staticValuesResolver.GetCustomFieldName(customFieldId);
if (col.PropertyName.EndsWith("_CustomField_Single_"))
{
customFieldDef = string.Empty;
temlKeyColumn.PropertyName = temlKeyColumn.PropertyName.Replace("_CustomField_Single_", $"{propetyActual}");
temlKeyColumn.ExcelColumnAttribute.Header = temlKeyColumn.Header = temlKeyColumn.PropertyName;
}
else
{
temlKeyColumn.PropertyName = temlKeyColumn.PropertyName.Replace("_CustomField_", $"{propetyActual}:{customFieldId}");
temlKeyColumn.ExcelColumnAttribute.Header = temlKeyColumn.Header = temlKeyColumn.PropertyName + ":" + customFieldDef;
}
sheetBuilder.AppendColumnHeaderRowItem(temlKeyColumn);
}
}
else
{
if (columnPrefix != null)
{
ExcelColumnInfo temlKeyColumn = col.Clone() as ExcelColumnInfo;
temlKeyColumn.PropertyName = $"{columnPrefix}:{temlKeyColumn.PropertyName}";
sheetBuilder.AppendColumnHeaderRowItem(temlKeyColumn);
}
else
{
sheetBuilder.AppendColumnHeaderRowItem(col);
}
}
}
}
//if its recursive do not populate rows and return to parent
if (columnPrefix != null)
{
return;
}
if (sheetName != null && sheetBuilder == null)
{
sheetBuilder = (XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder)document.GetSheetByName(sheetName);
}
//adding rows data
if (value != null)
{
var columns = columnInfo.Keys.ToList();
if (value is IEnumerable <object> && (value as IEnumerable <object>).Count() > 0)
{
foreach (var dataObj in value as IEnumerable <object> )
{
PopulateRows(columns, dataObj, sheetBuilder, columnInfo, document);
var deepSheetsInfo = _sheetResolver.GetExcelSheetInfo(itemType, dataObj);
PopulateInnerObjectSheets(deepSheetsInfo, document, itemType);
}
}
else if (!(value is IEnumerable <object>))
{
PopulateRows(columns, value, sheetBuilder, columnInfo, document);
var sheetsInfo = _sheetResolver.GetExcelSheetInfo(itemType, value);
PopulateInnerObjectSheets(sheetsInfo, document, itemType);
}
}
if (sheetBuilder != null)
{
sheetBuilder.ShouldAddHeaderRow = true;
}
}
public void Serialise(Type itemType, object value, IXlsxDocumentBuilder document, string sheetName, string columnPrefix, SqadXlsxPlanSheetBuilder sheetOveride)
{
throw new NotImplementedException();
}
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());
}
}
