/// <summary>
///
/// </summary>
/// <param name="worksheet"></param>
/// <param name="tempColumn"></param>
public static void CopyColumnFromTemplate(this ExcelWorksheet worksheet, ExcelColumnInfo tempColumn)
{
worksheet.InsertColumn(tempColumn.Index, 1);
var column = worksheet.Column(tempColumn.Index);
column.CopyFromTemplate(tempColumn.Column);
}
protected virtual object FormatCellValue(object cellValue, ExcelColumnInfo info = null)
{
if (info != null)
{
// Boolean transformations.
if (info.ExcelColumnAttribute != null && info.ExcelColumnAttribute.TrueValue != null && cellValue.Equals("True"))
{
return(info.ExcelColumnAttribute.TrueValue);
}
else if (info.ExcelColumnAttribute != null && info.ExcelColumnAttribute.FalseValue != null && cellValue.Equals("False"))
{
return(info.ExcelColumnAttribute.FalseValue);
}
else if (!string.IsNullOrWhiteSpace(info.FormatString) & string.IsNullOrEmpty(info.ExcelNumberFormat))
{
return(string.Format(info.FormatString, cellValue));
}
else if (cellValue.GetType() == typeof(DateTime) || (!Double.TryParse(cellValue.ToString(), out var test1) && DateTime.TryParse(cellValue.ToString(), out var test)))
{
return(string.Format("{0:MM/dd/yyyy}", DateTime.Parse(cellValue.ToString())));
}
}
return(cellValue);
}
public void AppendColumnHeaderRowItem(ExcelColumnInfo column)
{
SheetColumns.Add(column);
var headerName = column.IsExcelHeaderDefined ? column.Header : column.PropertyName;
var dc = new DataColumn(headerName, typeof(ExcelCell));
if (column.IsHidden)
{
dc.ColumnMapping = MappingType.Hidden;
}
CurrentTable.Columns.Add(dc);
}
private IList <ExcelSheetInfo> GetSheetInfoList(string filePath)
{
var sheetList = new List <ExcelSheetInfo>();
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
var ext = Path.GetExtension(filePath);
var book = ext == ".xls" ? (IWorkbook) new HSSFWorkbook(stream) : new XSSFWorkbook(stream);
for (var i = 0; i < book.NumberOfSheets; ++i)
{
var sheet = book.GetSheetAt(i);
var sheetInfo = new ExcelSheetInfo();
var sheetName = sheet.SheetName.Trim().Replace(" ", "").Replace("\t", "");
var parts = sheetName.Split(new[] { '_' }, StringSplitOptions.RemoveEmptyEntries);
sheetInfo.Name = parts[0];
sheetInfo.SubName = parts.Length > 1 ? parts[1] : null;
sheetList.Add(sheetInfo);
if (!sheetInfo.NameValid || !sheetInfo.SubNameValid)
{
Debug.LogError($"Sheet Name is invalid! [{sheetName}]\neg. Name_Sub, Name, -Name_Sub, -Name");
}
if (!sheetInfo.Enabled)
{
continue;
}
var titleRow = sheet.GetRow(0); // 1st row [FieldName]
var typeRow = sheet.GetRow(1); // 2nd row [TypeSymbol]
for (var j = 0; j < titleRow.LastCellNum; j++)
{
var colInfo = new ExcelColumnInfo {
Index = j
};
var fieldName = titleRow.GetCell(j).StringCellValue;
fieldName = fieldName.Trim().Replace(" ", "").Replace("\t", "");
colInfo.Name = fieldName;
var typeStr = typeRow.GetCell(j).StringCellValue;
(colInfo.ValType, colInfo.IsArray, colInfo.ArraySep) = ParseValueType(typeStr);
sheetInfo.ColumnInfos.Add(colInfo);
}
}
}
return(sheetList);
}
private void SetDictionaryColumnInfo(List <ExcelColumnInfo> _props, object key)
{
var p = new ExcelColumnInfo();
p.ExcelColumnName = key?.ToString();
p.Key = key;
// TODO:Dictionary value type is not fiexed
//var _t =
//var gt = Nullable.GetUnderlyingType(p.PropertyType);
var isIgnore = false;
if (_configuration.DynamicColumns != null && _configuration.DynamicColumns.Length > 0)
{
var dynamicColumn = _configuration.DynamicColumns.SingleOrDefault(_ => _.Key == key.ToString());
if (dynamicColumn != null)
{
p.Nullable = true;
//p.ExcludeNullableType = item2[key]?.GetType();
if (dynamicColumn.Format != null)
{
p.ExcelFormat = dynamicColumn.Format;
}
if (dynamicColumn.Aliases != null)
{
p.ExcelColumnAliases = dynamicColumn.Aliases;
}
if (dynamicColumn.IndexName != null)
{
p.ExcelIndexName = dynamicColumn.IndexName;
}
p.ExcelColumnIndex = dynamicColumn.Index;
if (dynamicColumn.Name != null)
{
p.ExcelColumnName = dynamicColumn.Name;
}
isIgnore = dynamicColumn.Ignore;
p.ExcelColumnWidth = dynamicColumn.Width;
}
}
if (!isIgnore)
{
_props.Add(p);
}
}
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 string ToCsvString(object value, ExcelColumnInfo p)
{
if (value == null)
{
return("");
}
if (value is DateTime dateTime)
{
if (p?.ExcelFormat != null)
{
return(dateTime.ToString(p.ExcelFormat, _configuration.Culture));
}
return(_configuration.Culture.Equals(CultureInfo.InvariantCulture) ? dateTime.ToString("yyyy-MM-dd HH:mm:ss", _configuration.Culture) : dateTime.ToString(_configuration.Culture));
}
if (p?.ExcelFormat != null && value is IFormattable formattableValue)
{
return(formattableValue.ToString(p.ExcelFormat, _configuration.Culture));
}
return(Convert.ToString(value, _configuration.Culture));
}
private void WriteCell(MiniExcelStreamWriter writer, int rowIndex, int cellIndex, object value, ExcelColumnInfo p)
{
var v = string.Empty;
var t = "str";
var s = "2";
if (value == null)
{
v = "";
}
else if (value is string str)
{
v = ExcelOpenXmlUtils.EncodeXML(str);
}
else if (p?.ExcelFormat != null && value is IFormattable formattableValue)
{
var formattedStr = formattableValue.ToString(p.ExcelFormat, _configuration.Culture);
v = ExcelOpenXmlUtils.EncodeXML(formattedStr);
}
else
{
Type type = null;
if (p == null || p.Key != null)
{
// TODO: need to optimize
// Dictionary need to check type every time, so it's slow..
type = value.GetType();
type = Nullable.GetUnderlyingType(type) ?? type;
}
else
{
type = p.ExcludeNullableType; //sometime it doesn't need to re-get type like prop
}
if (type.IsEnum)
{
t = "str";
var description = CustomPropertyHelper.DescriptionAttr(type, value); //TODO: need to optimze
if (description != null)
{
v = description;
}
else
{
v = value.ToString();
}
}
else if (TypeHelper.IsNumericType(type))
{
if (_configuration.Culture != CultureInfo.InvariantCulture)
{
t = "str"; //TODO: add style format
}
else
{
t = "n";
}
if (type.IsAssignableFrom(typeof(decimal)))
{
v = ((decimal)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Int32)))
{
v = ((Int32)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Double)))
{
v = ((Double)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Int64)))
{
v = ((Int64)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(UInt32)))
{
v = ((UInt32)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(UInt16)))
{
v = ((UInt16)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(UInt64)))
{
v = ((UInt64)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Int16)))
{
v = ((Int16)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Single)))
{
v = ((Single)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Single)))
{
v = ((Single)value).ToString(_configuration.Culture);
}
else
{
v = (decimal.Parse(value.ToString())).ToString(_configuration.Culture);
}
}
else if (type == typeof(bool))
{
t = "b";
v = (bool)value ? "1" : "0";
}
else if (type == typeof(byte[]) && _configuration.EnableConvertByteArray)
{
var bytes = (byte[])value;
if (bytes != null)
{
// TODO: Setting configuration because it might have high cost?
var format = ImageHelper.GetImageFormat(bytes);
//it can't insert to zip first to avoid cache image to memory
//because sheet xml is opening.. https://github.com/shps951023/MiniExcel/issues/304#issuecomment-1017031691
//int rowIndex, int cellIndex
var file = new FileDto()
{
Byte = bytes,
RowIndex = rowIndex,
CellIndex = cellIndex,
SheetId = currentSheetIndex
};
if (format != ImageFormat.unknown)
{
file.Extension = format.ToString();
file.IsImage = true;
}
else
{
file.Extension = "bin";
}
_files.Add(file);
//TODO:Convert to base64
var base64 = $"@@@fileid@@@,{file.Path}";
v = ExcelOpenXmlUtils.EncodeXML(base64);
s = "4";
}
}
else if (type == typeof(DateTime))
{
if (_configuration.Culture != CultureInfo.InvariantCulture)
{
t = "str";
v = ((DateTime)value).ToString(_configuration.Culture);
}
else if (p == null || p.ExcelFormat == null)
{
t = null;
s = "3";
v = ((DateTime)value).ToOADate().ToString(CultureInfo.InvariantCulture);
}
else
{
// TODO: now it'll lose date type information
t = "str";
v = ((DateTime)value).ToString(p.ExcelFormat, _configuration.Culture);
}
}
else
{
//TODO: _configuration.Culture
v = ExcelOpenXmlUtils.EncodeXML(value.ToString());
}
}
var columname = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, rowIndex);
if (v != null && (v.StartsWith(" ", StringComparison.Ordinal) || v.EndsWith(" ", StringComparison.Ordinal))) /*Prefix and suffix blank space will lost after SaveAs #294*/
{
writer.Write($"<x:c r=\"{columname}\" {(t == null ? "" : $"t =\"{t}\"")} s=\"{s}\" xml:space=\"preserve\"><x:v>{v}</x:v></x:c>");
private void AppendColumnsAndRows(SqadXlsxSheetBuilderBase sheetBuilder, ActualExport value)
{
var exp = "Export Properties";
var sec = " ";
var excelinfo = new ExcelColumnInfo(exp, null, new ExcelColumnAttribute(), null);
sheetBuilder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(sec, null, new ExcelColumnAttribute(), null);
sheetBuilder.AppendColumnHeaderRowItem(excelinfo);
List <ExcelCell> row = new List <ExcelCell>();
ExcelCell cell = new ExcelCell();
cell = new ExcelCell();
cell.CellHeader = exp;
cell.CellValue = $"Resource Set";
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = sec;
cell.CellValue = $"{value.ResourceName}";
row.Add(cell);
sheetBuilder.AppendRow(row);
cell = new ExcelCell();
cell.CellHeader = exp;
cell.CellValue = $"Client";
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = sec;
cell.CellValue = $"{value.ClientName}";
row.Add(cell);
sheetBuilder.AppendRow(row);
cell = new ExcelCell();
cell.CellHeader = exp;
cell.CellValue = $"From Date";
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = sec;
cell.CellValue = $"{value.Request.FromDate.ToString("dddd, MMMM dd, yyyy")}";
row.Add(cell);
sheetBuilder.AppendRow(row);
cell = new ExcelCell();
cell.CellHeader = exp;
cell.CellValue = $"To Date";
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = sec;
cell.CellValue = $"{value.Request.ToDate.ToString("dddd, MMMM dd, yyyy")}";
row.Add(cell);
sheetBuilder.AppendRow(row);
cell = new ExcelCell();
cell.CellHeader = exp;
cell.CellValue = $"Created By";
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = sec;
cell.CellValue = $"{value.CreatedBy}";
row.Add(cell);
sheetBuilder.AppendRow(row);
cell = new ExcelCell();
cell.CellHeader = exp;
cell.CellValue = $"Created On";
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = sec;
cell.CellValue = $"{value.Request.CreateDate.ToString("dddd, MMMM dd, yyyy HH:mm")}";
row.Add(cell);
sheetBuilder.AppendRow(row);
cell = new ExcelCell();
cell.CellHeader = exp;
cell.CellValue = string.Empty;
row.Add(cell);
cell = new ExcelCell();
cell.CellHeader = sec;
cell.CellValue = string.Empty;
row.Add(cell);
sheetBuilder.AppendRow(row);
//PopulateNames(sheetBuilder, exp, sec, "Products", value.Flights.Select(x=>x.ProductName).Distinct().ToList());
PopulateNames(sheetBuilder, exp, sec, "Products", value.ProductNames);
if (value.PlanTypeNames.Any())
{
PopulateNames(sheetBuilder, exp, sec, "Plan Types", value.PlanTypeNames);
}
}
private void FormatWorkSheet(XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder builder, ActualWorksheet item, Dictionary <string, string> custom)
{
var excelinfo = new ExcelColumnInfo(item.Data, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlanName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.VehicleName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ProductName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.SubtypeName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.UnitName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.MarketName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.DemoName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
if (custom != null && custom.Count > 0)
{
foreach (var it in custom)
{
excelinfo = new ExcelColumnInfo(it.Key, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
}
else
{
excelinfo = new ExcelColumnInfo(item.CreativeName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.FundingSourceName, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
excelinfo = new ExcelColumnInfo(item.StartDate, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.EndDate, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
//planned
excelinfo = new ExcelColumnInfo(item.PlannedGRPs, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedTRPs, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
var hidden = !(Convert.ToBoolean(item.MediaType.EnableWeeklyReach) || Convert.ToInt16(item.MediaType.ReachType) > 0);
excelinfo = new ExcelColumnInfo(item.PlannedReach, null, new ExcelColumnAttribute()
{
IsHidden = hidden
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedFrequency, null, new ExcelColumnAttribute()
{
IsHidden = hidden
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
var shownet = Convert.ToBoolean(item.MediaType.ExternalActualsNet);
excelinfo = new ExcelColumnInfo(item.PlannedGross, null, new ExcelColumnAttribute()
{
IsHidden = shownet
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedNet, null, new ExcelColumnAttribute()
{
IsHidden = !shownet
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
PopulatePlannedActualColumns(builder, item);
//actual
excelinfo = new ExcelColumnInfo(item.ActualGRPs, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualTRPs, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualReach, null, new ExcelColumnAttribute()
{
IsHidden = hidden
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualFrequency, null, new ExcelColumnAttribute()
{
IsHidden = hidden
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
//if (!shownet)
//{
// excelinfo = new ExcelColumnInfo(item.ActualGross, null, new ExcelColumnAttribute(), null);
// builder.AppendColumnHeaderRowItem(excelinfo);
//}
//else
//{
// excelinfo = new ExcelColumnInfo(item.ActualNet, null, new ExcelColumnAttribute(), null);
// builder.AppendColumnHeaderRowItem(excelinfo);
//}
excelinfo = new ExcelColumnInfo(item.ActualGross, null, new ExcelColumnAttribute()
{
IsHidden = shownet
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualNet, null, new ExcelColumnAttribute()
{
IsHidden = !shownet
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
PopulatePlannedActualColumns(builder, item, true);
excelinfo = new ExcelColumnInfo(item.DateActualized, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualizedBy, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
private void PopulatePlannedActualColumns(XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder builder, ActualWorksheet item, bool actual = false)
{
switch (item.MediaType.MediaClass)
{
case (int)MediaClass.Print:
case (int)MediaClass.Newspaper:
case (int)MediaClass.ReachBased:
if (!actual)
{
//visible
var excelinfo = new ExcelColumnInfo(item.PlannedProduction, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
//unvisible
excelinfo = new ExcelColumnInfo(item.PlannedImpressions, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedClicks, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedLeads, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedRichMedia, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedAdServing, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
else
{
var excelinfo = new ExcelColumnInfo(item.ActualProduction, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualImpressions, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualClicks, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualLeads, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualRichMedia, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualAdServing, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
break;
case (int)MediaClass.Interactive:
if (!actual)
{
var excelinfo = new ExcelColumnInfo(item.PlannedProduction, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedImpressions, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedClicks, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedLeads, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedRichMedia, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedAdServing, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
else
{
var excelinfo = new ExcelColumnInfo(item.ActualProduction, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualImpressions, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualClicks, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualLeads, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualRichMedia, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualAdServing, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
break;
default:
if (!actual)
{
var excelinfo = new ExcelColumnInfo(item.PlannedProduction, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedImpressions, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedClicks, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedLeads, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedRichMedia, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.PlannedAdServing, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
else
{
var excelinfo = new ExcelColumnInfo(item.ActualProduction, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualImpressions, null, new ExcelColumnAttribute(), null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualClicks, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualLeads, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualRichMedia, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
excelinfo = new ExcelColumnInfo(item.ActualAdServing, null, new ExcelColumnAttribute()
{
IsHidden = true
}, null);
builder.AppendColumnHeaderRowItem(excelinfo);
}
break;
}
}
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 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;
}
}
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());
}
}