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 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 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);
}
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 ProcessInstructionsSheet(IXlsxDocumentBuilder document, DataTableCollection tables)
{
if (!tables.Contains(InstructionsTableName))
{
return;
}
var instructionsDataTable = tables[InstructionsTableName];
var instructionsSheetBuilder = new SqadXlsxUnformattedViewInstructionsSheetBuilder();
document.AppendSheet(instructionsSheetBuilder);
AppendColumnsAndRows(instructionsSheetBuilder, instructionsDataTable);
}
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);
}
}
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 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 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;
}
}