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;
