public static void DrawConditions(Range pRange, Color color)
{
FormatConditions pConditions = pRange.FormatConditions;
Databar pCondition = pConditions.AddDatabar();
pCondition.MaxPoint.Modify(XlConditionValueTypes.xlConditionValueNumber, 0.5);
pCondition.MinPoint.Modify(XlConditionValueTypes.xlConditionValueNumber, 0);
pCondition.BarColor.Color = color;
pCondition.BarFillType = XlDataBarFillType.xlDataBarFillSolid;
}
public void ApplyGridFormatConditions(List <ConsoleCommands.BaseCommand> commands)
{
FormatConditions.Clear();
ComputedColumns.Clear();
var gridFormatConditions = LoadGridFormatConditions(commands);
FormatConditions.AddRange(gridFormatConditions);
ComputedColumns.AddRange(commands.OfType <ComputedColumn>());
}
public Boolean addFormatRule(String path)
{
try
{
Application excel = new Application();
Workbook workbook = excel.Workbooks.Open(path);
workbook.Worksheets[1].UsedRange.Hyperlinks.Delete();
FormatConditions fcs = workbook.Worksheets[1].UsedRange.FormatConditions;
fcs.Delete();
object formula1 = "=NOT(ISERROR(FIND(SUBSTITUTE(TEXT(ADDRESS(ROW(),COLUMN()), \"\")&\",\", \"$\",\"\"),hidden!$A$1)))";
var fc1 = (FormatCondition)fcs.Add(XlFormatConditionType.xlExpression, Type.Missing, formula1);
setFormatting(fc1, System.Drawing.Color.White, System.Drawing.ColorTranslator.FromHtml("#f0ad4e"), System.Drawing.ColorTranslator.FromHtml("#b97310"));
object formula2 = "=NOT(ISERROR(FIND(SUBSTITUTE(TEXT(ADDRESS(ROW(),COLUMN()), \"\")&\",\", \"$\",\"\"),hidden!$A$2)))";
var fc2 = (FormatCondition)fcs.Add(XlFormatConditionType.xlExpression, Type.Missing, formula2);
setFormatting(fc2, System.Drawing.Color.White, System.Drawing.ColorTranslator.FromHtml("#5cb85c"), System.Drawing.ColorTranslator.FromHtml("#367c36"));
fc1 = null;
fc2 = null;
fcs = null;
//Save and close xls file
workbook.Close(true, Type.Missing, false);
workbook = null;
excel.Quit();
excel = null;
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message + e.InnerException);
return(false);
}
}