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.

Is the intention to actually do something to the contents of the spreadsheet, or are you just copying it? — TheSoftwareJedi, Sep 11 '18 at 16:16
The sheet is like a report, we have applied formulas to the spreadsheet cells.so when copying the formulas should apply to the new file. — King_Fisher, Sep 11 '18 at 16:19

score 4 · Accepted Answer · answered Sep 11 '18 at 17:09

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.

Thanks for your effort, just now noticed, I will try this morning and let you know. thanks again. — King_Fisher, Sep 12 '18 at 18:16

Saleem Kalro · Answer 2 · 2018-09-12T17:37:25.530

1

Replace the following in your code

 string filePath = AppDomain.CurrentDomain.BaseDirectory + "" + "App_Data\\" + filename+ ".xlsx";

you have missed excel extension while creating excel file.
Also use TheSoftwareJedi solution.I have tested, Working.

edited Sep 12 '18 at 17:37

answered Sep 12 '18 at 17:09

Saleem Kalro

1,046
9
12

The filename is a parameter of the method, this input is filename with extension. Thanks – King_Fisher Sep 13 '18 at 06:05

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

2 Answers2