NPOI - Conditional Formatting not working when create new excel from template

Question

Code:

 private static void CreateExcel(string filename)
        {

            var workbook = new XSSFWorkbook();
            try
            {
                var sourcebook = AppDomain.CurrentDomain.BaseDirectory + "" + "\\App_Data\\Copy of VHC_modified_Report.xlsx";
                using (FileStream file = new FileStream(sourcebook, FileMode.Open, FileAccess.ReadWrite))
                {
                    workbook = new XSSFWorkbook(file);
                }
                XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);
                // evaluator.EvaluateAll();
                string filePath = AppDomain.CurrentDomain.BaseDirectory + "" + "App_Data\\" + filename;
                using (var fileData = new FileStream(filePath, FileMode.CreateNew, FileAccess.ReadWrite))
                {
                    workbook.Write(fileData);
                    workbook.Close();
                }

            }
            catch (Exception ex)
            {
                throw new Exception("ExportToExcel: \n" + ex.Message);
            }

        }

From the above I can able to create excel sheet from the template file.

But the Template Excel file, I have some conditional formatting rules and based on the rules applying some formats and filling colors.

These formats and colors are not appearing on the created excel file, but I can able to see the rules when check in conditional formatting but formats not applied.

Answer 1

There is a bug in NPOI in that when it writes out the styles.xml portion of the xlsx file, it adds the attribute fillPattern="none" (instead of solid, or the absence of this attribute), which results in no fill. I have reproduced this, and manually fixed by opening the resulting xlsx file and deleting that attribute from the appropriate nodes.

That said, there is an easy fix. In your template spreadsheet don't use the "solid color" fill. Either pick a pattern, or create a "two color" pattern and choose the same 2 colors. This writes out the file differently, with a gradientFill node, which doesn't have this bug.

Also, foreground color rules work fine also. This appears to only affect solid color background conditional formatting rules.

Someone should report this bug to the NPOI team. I unfortunately am not in a position to do this right now.