Search code examples
excelvb.netepplus

EPPlus changes cell interior color

I found something really strange while using EPPlus and I couldn't find the solution anywhere online (I also didn't find anyone with the same problem to be fair). Luckily, after some time, I found out how to fix it and because I couldn't find anything about it, I want to save someone else his/her time and document it here. Note, the past week was my first experience with EPPlus so I'm sorry if this question is very trivial and everyone knew this already.

The Issue

I'm trying to put data into an existing Excel template using EPPlus. One of the sheets of the Excel file looks like this: enter image description here

Nothing strange about it you would say. However, when I open this Excel file in my code and use EPPlus to add values and save it, it comes out like this:

enter image description here

Of course the numbers in the table are intentional, but how does it suddenly have a purple background!? Also why doesn't it happen to particular cells. There is nothing in the code that does anything with the styling. I only add values to the table.

Another strange thing is that this happened on multiple pc's, but not on all.

Solution

  • The underlying problem

    After looking around, I found out that the standard styling suddenly showed the same purple color.

    Before

    Normal styling

    After

    enter image description here

    I still don't understand why it changes the color, but it does. Then somewhere on github, someone mentioned that you need to change the code of EPPlus to get rid of this. It mentioned that because the Excel spreadsheet was created in another language than English, it couldn't find the "Normal" style, since it's named "Standaard" in Dutch in this case, and you have to change "Normal" into "Standaard" in the code.

    But I just used Nuget to get a package and didn't want to create my own package, and more important: What if I have a spreadsheet next time that is created in the English version?

    The Solution

    For such a long story, the solution is just to easy: In you workbook, in VBA in the immediate window, type the following code

    Thisworkbook.Styles.Add("Normal")

    The workbook now has a custom format called "Normal". Since it's likely that your normal format would be just blank, this will show you the proper styling.