Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 5
0
        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));
        }
Ejemplo n.º 6
0
        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);
     }
 }
Ejemplo n.º 8
0
        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);
        }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 12
0
        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)));
        }
Ejemplo n.º 13
0
        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);
                }
            }
        }
Ejemplo n.º 14
0
        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;
            }
        }