This project has moved and is read-only. For the latest updates, please go here.

Create custom conditionnal formating - C#

Aug 7 at 3:59 PM
Hi everybody !

I'm using Epplus and I have a question : I know in Excel is possible to create conditionnal formating ("CF"), but I don't find how to do this using Epplus library in C#, and apply it to cells.
I found some topics on web but nothing is relevant, because I want to create a custom CF with mixed default CF.

I attached pictures to show you what I want to do. The CF are directly created from Excel.

I hope any body can help me :) !

Thank you !

Image
Image
Aug 10 at 7:49 AM
Hello,

I found a solution which working fine for me. It is specific for me :
/// <summary>
        /// Permet d'appliquer la mise en forme conditionnelle (MeFC) pour une ou plusieurs cellule(s).
        /// </summary>
        /// <param name="xlWorksheet">Feuille dans laquelle appliquer la MeFC</param>
        /// <param name="adresse">Paramètre falcutatif précisant l'adresse d'une cellule en particulier.</param>
        private static void AppliquerMiseEnFormeConditionnelleTableaux(ExcelWorksheet xlWorksheet, string adresse = "")
        {
            //Liste des cellules à mettre en forme
            List<ExcelRangeBase> lstCells = new List<OfficeOpenXml.ExcelRangeBase>();

            if (adresse == "")
            {
                //Récupération de toutes les cellules avec le style préparé pour un MeFC
                lstCells = xlWorksheet.Cells.Where(c => (c.StyleID == _dicoStyles.ElementAt(23).Value) || 
                                                        (c.StyleID == _dicoStyles.ElementAt(24).Value)).ToList();
            }
            else
                lstCells.Add(xlWorksheet.Cells[adresse]); //Récupère seulement la cellule spécifiée

            foreach (var cell in lstCells)
            {
                bool isTendance = (cell.StyleID == _dicoStyles.ElementAt(23).Value); //Condition Tendance (flèche) ou objectif
                ExcelAddress addressCell = new ExcelAddress(cell.Address);
                AppliquerFormulesFeuille(xlWorksheet, addressCell.Address);
                double valueCell = Convert.ToDouble(cell.Value); //Valeur après application de la formule

                if (valueCell > 0)
                {
                    if (isTendance)
                    {
                        // Qn-1 (-) Qn = (> 0) -> Flèche en or vers le bas

                        var v = xlWorksheet.ConditionalFormatting.AddFourIconSet(addressCell, eExcelconditionalFormatting4IconsSetType.Arrows);
                        v.Reverse = true;
                        v.Icon4.Type = eExcelConditionalFormattingValueObjectType.Num;
                        v.ShowValue = false;
                    }
                    else
                    {
                        // YtdPays - Objectif = (> 0) -> Pastille 'v' verte

                        var v = xlWorksheet.ConditionalFormatting.AddThreeIconSet(addressCell, eExcelconditionalFormatting3IconsSetType.Symbols);
                        v.Reverse = true;
                        v.Icon1.Type = eExcelConditionalFormattingValueObjectType.Num;
                        v.ShowValue = false;
                    }
                }
                else if (valueCell == 0)
                {
                    if (isTendance)
                    {
                        // Qn-1 (-) Qn = (= 0) -> Rond rose

                        var v = xlWorksheet.ConditionalFormatting.AddFourIconSet(addressCell, eExcelconditionalFormatting4IconsSetType.RedToBlack);
                        v.Icon4.Type = eExcelConditionalFormattingValueObjectType.Num;
                        v.ShowValue = false;
                    }
                    else
                    {
                        // YtdPays - Objectif = (= 0) -> Pastille vide orange

                        var v = xlWorksheet.ConditionalFormatting.AddThreeIconSet(addressCell, eExcelconditionalFormatting3IconsSetType.TrafficLights1);
                        v.Icon3.Type = eExcelConditionalFormattingValueObjectType.Num;
                        v.ShowValue = false;
                    }
                }
                else if (valueCell < 0)
                {
                    if (isTendance)
                    {
                        // Qn-1 (-) Qn = (< 0) -> flèche en gris vers le haut
                        var v = xlWorksheet.ConditionalFormatting.AddFourIconSet(addressCell, eExcelconditionalFormatting4IconsSetType.ArrowsGray);
                        v.Reverse = false;
                        v.Icon4.Type = eExcelConditionalFormattingValueObjectType.Num;
                        v.ShowValue = false;
                    }
                    else
                    {
                        // YtdPays - Objectif = (> 0) -> Pastille 'x' rouge

                        var v = xlWorksheet.ConditionalFormatting.AddThreeIconSet(addressCell, eExcelconditionalFormatting3IconsSetType.Symbols);
                        v.Reverse = false;
                        v.Icon1.Type = eExcelConditionalFormattingValueObjectType.Num;
                        v.ShowValue = false;
                    }
                }
            }
        }