public static void ApplyGridFormatCondition(Worksheet worksheet, GridFormatCondition formatCondition, CellRange formatRange, DevExpress.Export.Xl.CriteriaOperatorToXlExpressionConverter operatorConverter = null) { var conditionalFormattings = worksheet.ConditionalFormattings; var formatRule = Utils.NonNullString(formatCondition.FormatRule).ToLower(); switch (formatRule) { case "expression": var expression = formatCondition.Expression; if (!string.IsNullOrWhiteSpace(expression)) { if (operatorConverter != null) //If there is no operatorConverter - there is no Table and so expression is not allowed. { expression = AdjustFormatExpression(operatorConverter, expression); var formatExpression = conditionalFormattings.AddFormulaExpressionConditionalFormatting( formatRange, expression); ApplyFormatting(formatExpression); } } break; case "databar": var formatDataBar = conditionalFormattings.AddDataBarConditionalFormatting(formatRange, conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax), conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax), ColorExtensions.FromHtmlColor(formatCondition.BackColor, Color.Green)); var borderColor = ColorExtensions.FromHtmlColor(formatCondition.BorderColor, Color.Empty); if (borderColor != Color.Empty) { formatDataBar.BorderColor = borderColor; } formatDataBar.GradientFill = true; ApplyOptionalFormatting(); break; case "iconset": if (!Enum.TryParse(formatCondition.IconSet, out IconSetType iconSetType)) { iconSetType = IconSetType.Arrows5; formatCondition.IconSet = "Arrows5"; } if (!string.IsNullOrWhiteSpace(formatCondition.IconSet) && int.TryParse(new string(formatCondition.IconSet[^ 1], 1), out int stepCount))
protected void DoWriteText(string text, TextOptions options) { options ??= new TextOptions(); var book = options.Book?.Document ?? Document; book.BeginUpdate(); try { var range = book.AppendText(text); if (options != null) { if (!string.IsNullOrWhiteSpace(options.ParagraphStyle)) { var style = book.ParagraphStyles[options.ParagraphStyle] ?? throw new Exception($"Paragraph style '{options.ParagraphStyle}' does not exist."); var pp = book.BeginUpdateParagraphs(range); try { pp.Style = style; } finally { book.EndUpdateParagraphs(pp); } } var foregroundColor = ColorExtensions.FromHtmlColor(options.ForegroundColor, SystemColors.WindowText); var backgroundColor = ColorExtensions.FromHtmlColor(options.BackgroundColor, SystemColors.Window); var underlineColor = ColorExtensions.FromHtmlColor(options.UnderlineColor, Color.Empty); if (foregroundColor != SystemColors.WindowText || backgroundColor != SystemColors.Window || options.FontName != null || options.FontSize.HasValue || options.Bold.HasValue || options.Italic.HasValue || options.Underline.HasValue || options.Subscript.HasValue || options.Superscript.HasValue || options.UnderlineType.HasValue || underlineColor != Color.Empty || !string.IsNullOrWhiteSpace(options.CharacterStyle)) { var cp = book.BeginUpdateCharacters(range); try { if (!string.IsNullOrWhiteSpace(options.CharacterStyle)) { var style = book.CharacterStyles[options.CharacterStyle] ?? throw new Exception($"Character style '{options.CharacterStyle}' does not exist."); cp.Style = style; } if (foregroundColor != SystemColors.WindowText) { cp.ForeColor = foregroundColor; } if (backgroundColor != SystemColors.Window) { cp.BackColor = backgroundColor; } if (!string.IsNullOrWhiteSpace(options.FontName)) { cp.FontName = options.FontName; } if (options.FontSize.HasValue && options.FontSize.Value >= 4 && options.FontSize.Value <= 500) { cp.FontSize = options.FontSize.Value; } if (options.Bold.HasValue) { cp.Bold = options.Bold.Value; } if (options.Italic.HasValue) { cp.Italic = options.Italic; } if (options.Underline.HasValue) { cp.Underline = (DevExpress.XtraRichEdit.API.Native.UnderlineType)(options.Underline.Value ? UnderlineType.Single : UnderlineType.None); } if (options.UnderlineType.HasValue) { cp.Underline = (DevExpress.XtraRichEdit.API.Native.UnderlineType)options.UnderlineType; } if (underlineColor != Color.Empty) { cp.UnderlineColor = underlineColor; } if (options.Subscript.HasValue) { cp.Subscript = options.Subscript.Value; } if (options.Superscript.HasValue) { cp.Superscript = options.Superscript.Value; } } finally { book.EndUpdateCharacters(cp); } } if (options.ExpandFields && options.Snippets != null) { Script.Book.SCBook.ExpandFieldsInBookRange(range, Host?.Spreadsheet?.Workbook, options.Snippets); } } Script.Book.SCBook.AddComments(book, range, options); if (range?.End != null) { book.CaretPosition = range.End; Script.Book.SCBook.ResetBookFormatting(book, range.End); ScrollToCaret(); } WriteTextToConsole(text); } finally { book.EndUpdate(); } }
public static void LoadGridData(GridView viewTable, GridData data) { using (new UsingProcessor(() => viewTable.BeginUpdate(), () => viewTable.EndUpdate())) { viewTable.GridControl.ForceInitialize(); if (data.Parameters != null) { var gridProperties = new GridProperties((GridView)viewTable); gridProperties.ApplyParameters(data.Parameters); } if (data.ColumnOrder != null && data.ColumnOrder.Count > 0) { var visibleColumns = new List <GridColumn>(); bool hasSummaries = false; int i = 0; foreach (var field in data.ColumnOrder) { if (string.IsNullOrWhiteSpace(field.ColumnName)) { continue; } var column = viewTable.Columns.ColumnByFieldName(field.ColumnName); if (column != null) { column.Visible = true; column.VisibleIndex = i++; visibleColumns.Add(column); column.Summary.Clear(); if (!string.IsNullOrWhiteSpace(field.Summary)) { var summaries = Utils.SplitString(field.Summary, ','); foreach (var summary in summaries) { if (Enum.TryParse <SummaryItemType>(summary, out SummaryItemType summaryType)) { column.Summary.Add(summaryType); hasSummaries = true; } } } } } if (hasSummaries) { viewTable.OptionsView.ShowFooter = true; } } using (new UsingProcessor(() => viewTable.BeginSort(), () => viewTable.EndSort())) { viewTable.ClearGrouping(); viewTable.ClearSorting(); var sortInfos = new List <GridColumnSortInfo>(); int groupColumnCount = 0; if (data.GroupBy != null && data.GroupBy.Count > 0) { foreach (var group in data.GroupBy) { if (string.IsNullOrWhiteSpace(group.ColumnName)) { continue; } var sortAscending = true; var colName = group.ColumnName; if (string.Compare(group.SortOrder, "desc", true) == 0) { sortAscending = false; } var column = viewTable.Columns.ColumnByFieldName(colName); if (column != null) { var colSortInfo = new GridColumnSortInfo(column, sortAscending ? ColumnSortOrder.Ascending : ColumnSortOrder.Descending); sortInfos.Add(colSortInfo); groupColumnCount++; } } } if (data.OrderBy != null && data.OrderBy.Count > 0) { foreach (var orderBy in data.OrderBy) { if (string.IsNullOrWhiteSpace(orderBy.ColumnName)) { continue; } var sortAscending = true; var colName = orderBy.ColumnName; if (string.Compare(orderBy.SortOrder, "desc", true) == 0) { sortAscending = false; } var column = viewTable.Columns.ColumnByFieldName(colName); if (column != null) { var colSortInfo = new GridColumnSortInfo(column, sortAscending ? ColumnSortOrder.Ascending : ColumnSortOrder.Descending); sortInfos.Add(colSortInfo); } } } viewTable.SortInfo.ClearAndAddRange(sortInfos.ToArray(), groupColumnCount); } if (data.FormatConditions != null && data.FormatConditions.Count > 0) { viewTable.FormatConditions.Clear(); foreach (var formatCondition in data.FormatConditions) { GridColumn column = null, targetColumn = null; if (!string.IsNullOrWhiteSpace(formatCondition.ColumnName)) { column = viewTable.Columns.ColumnByFieldName(formatCondition.ColumnName); } if (!string.IsNullOrWhiteSpace(formatCondition.TargetColumn)) { targetColumn = viewTable.Columns.ColumnByFieldName(formatCondition.TargetColumn); } var formatRule = Utils.NonNullString(formatCondition.FormatRule).ToLower(); switch (formatRule) { case "expression": var expression = formatCondition.Expression; if (!string.IsNullOrWhiteSpace(expression)) { var formatExpression = new FormatConditionRuleExpression() { Expression = expression }; var ruleExpression = new GridFormatRule { Column = column, ColumnApplyTo = targetColumn, ApplyToRow = (column == null) || formatCondition.ApplyToRow, Rule = formatExpression }; ApplyFormatting(formatExpression, formatCondition); viewTable.FormatRules.Add(ruleExpression); } break; case "databar": if (column != null) { var ruleDataBar = new GridFormatRule() { Column = column, ColumnApplyTo = targetColumn, ApplyToRow = formatCondition.ApplyToRow }; var formatDataBar = new FormatConditionRuleDataBar(); if (string.IsNullOrWhiteSpace(formatCondition.BackColor)) { formatCondition.BackColor = formatCondition.DataBar; } if (!string.IsNullOrWhiteSpace(formatCondition.AppearanceName)) { formatDataBar.PredefinedName = formatCondition.AppearanceName; } else { ApplyAppearance(formatDataBar.Appearance, formatCondition); } ruleDataBar.Rule = formatDataBar; viewTable.FormatRules.Add(ruleDataBar); } break; case "iconset": if (column != null) { if (!Enum.TryParse(formatCondition.IconSet, out IconSetType iconSetType)) { iconSetType = IconSetType.Arrows5; } var ruleIconSet = new GridFormatRule() { Column = column, ColumnApplyTo = targetColumn, ApplyToRow = formatCondition.ApplyToRow }; var formatIconSet = new FormatConditionRuleIconSet() { IconSet = GetPredefinedIconSet(iconSetType) }; ruleIconSet.Rule = formatIconSet; viewTable.FormatRules.Add(ruleIconSet); } break; case "colorscale": if (column != null) { string colorScale = formatCondition.ColorScale; if (string.IsNullOrWhiteSpace(colorScale)) { colorScale = "White,Red"; } if (!string.IsNullOrWhiteSpace(colorScale)) { var scales = colorScale.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); if (scales != null && scales.Length == 2) { var ruleColorScale2 = new GridFormatRule() { Column = column, ColumnApplyTo = targetColumn, ApplyToRow = formatCondition.ApplyToRow }; var formatColorScale2 = new FormatConditionRule2ColorScale { MinimumColor = ColorExtensions.FromHtmlColor(scales[0], Color.White), MaximumColor = ColorExtensions.FromHtmlColor(scales[1], Color.Black) }; ruleColorScale2.Rule = formatColorScale2; viewTable.FormatRules.Add(ruleColorScale2); } else if (scales != null && scales.Length >= 3) //Use first 3 colors if more are specified { var ruleColorScale3 = new GridFormatRule() { Column = column, ColumnApplyTo = targetColumn, ApplyToRow = formatCondition.ApplyToRow }; var formatColorScale3 = new FormatConditionRule3ColorScale { MinimumColor = ColorExtensions.FromHtmlColor(scales[0], Color.White), MiddleColor = ColorExtensions.FromHtmlColor(scales[1], Color.Gray), MaximumColor = ColorExtensions.FromHtmlColor(scales[2], Color.Black) }; ruleColorScale3.Rule = formatColorScale3; viewTable.FormatRules.Add(ruleColorScale3); } } } break; case "aboveaverage": if (column != null) { var ruleAboveAverage = new GridFormatRule() { Column = column, ColumnApplyTo = targetColumn, ApplyToRow = formatCondition.ApplyToRow }; var formatAboveAverage = new FormatConditionRuleAboveBelowAverage() { AverageType = FormatConditionAboveBelowType.Above }; ApplyFormatting(formatAboveAverage, formatCondition); ruleAboveAverage.Rule = formatAboveAverage; viewTable.FormatRules.Add(ruleAboveAverage); } break; case "belowaverage": if (column != null) { var ruleBelowAverage = new GridFormatRule() { Column = column, ColumnApplyTo = targetColumn, ApplyToRow = formatCondition.ApplyToRow }; var formatBelowAverage = new FormatConditionRuleAboveBelowAverage() { AverageType = FormatConditionAboveBelowType.Below }; ApplyFormatting(formatBelowAverage, formatCondition); ruleBelowAverage.Rule = formatBelowAverage; viewTable.FormatRules.Add(ruleBelowAverage); } break; case "aboveorequalaverage": if (column != null) { var ruleAboveAverage = new GridFormatRule() { Column = column, ColumnApplyTo = targetColumn, ApplyToRow = formatCondition.ApplyToRow }; var formatAboveAverage = new FormatConditionRuleAboveBelowAverage() { AverageType = FormatConditionAboveBelowType.EqualOrAbove }; ApplyFormatting(formatAboveAverage, formatCondition); ruleAboveAverage.Rule = formatAboveAverage; viewTable.FormatRules.Add(ruleAboveAverage); } break; case "beloworequalaverage": if (column != null) { var ruleBelowAverage = new GridFormatRule() { Column = column, ColumnApplyTo = targetColumn, ApplyToRow = formatCondition.ApplyToRow }; var formatBelowAverage = new FormatConditionRuleAboveBelowAverage() { AverageType = FormatConditionAboveBelowType.EqualOrBelow }; ApplyFormatting(formatBelowAverage, formatCondition); ruleBelowAverage.Rule = formatBelowAverage; viewTable.FormatRules.Add(ruleBelowAverage); } break; case "unique": if (column != null) { var ruleUnique = new GridFormatRule() { Column = column, ColumnApplyTo = targetColumn, ApplyToRow = formatCondition.ApplyToRow }; var formatUnique = new FormatConditionRuleUniqueDuplicate() { FormatType = FormatConditionUniqueDuplicateType.Unique }; ApplyFormatting(formatUnique, formatCondition); ruleUnique.Rule = formatUnique; viewTable.FormatRules.Add(ruleUnique); } break; case "duplicate": if (column != null) { var ruleDuplicate = new GridFormatRule() { Column = column, ColumnApplyTo = targetColumn, ApplyToRow = formatCondition.ApplyToRow }; var formatDuplicate = new FormatConditionRuleUniqueDuplicate() { FormatType = FormatConditionUniqueDuplicateType.Unique }; ApplyFormatting(formatDuplicate, formatCondition); ruleDuplicate.Rule = formatDuplicate; viewTable.FormatRules.Add(ruleDuplicate); } break; case "top": if (column != null) { var rankTop = formatCondition.Rank; var isPercent = !string.IsNullOrWhiteSpace(rankTop) && rankTop.EndsWith("%"); var strRankTop = isPercent ? rankTop[0..^ 1].TrimEnd() : rankTop;