Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EPPlus Excel Change cell color

Tags:

c#

epplus

I am trying to set the color of a given cell with the color of another cell (that is already colored in the template. But worksheet.Cells[row, col].Style.Fill.BackgroundColor doesn't seem to have a getproperty. Is it possible to do that or do I have to find the exact hexdecimal code of the color on the internet ?

EDIT

using the code in the answer, I get that error (it is written in French but it translate with what I wrote in my first comment) enter image description here

like image 649
NicolasR Avatar asked Jun 24 '15 11:06

NicolasR


People also ask

What is the use of EPPlus?

EPPlus is a very helpful open-source 3rd party DLL for writing data to excel. EPPlus supports multiple properties of spreadsheets like cell ranges, cell styling, charts, pictures, shapes, comments, tables, protection, encryption, pivot tables, data validation, conditional formatting, formula calculation, etc.

How do I merge cells in EPPlus?

If you want to merge cells dynamically, you can also use: worksheet. Cells[FromRow, FromColumn, ToRow, ToColumn].


2 Answers

How about something like this?

//get color from this cell
var rgb = ws.Cells[1, 2].Style.Fill.BackgroundColor.Rgb;
//Convert to system.drawing.color
var color = System.Drawing.ColorTranslator.FromHtml("#" + rgb);
//set color in this cell
ws.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(color);

Update: Seems that if you select a color from system colors or the palette as your fill color it works fine. If you select one of the 'Theme colors' in the fill drop down .Rgb returns an empty string

//get style
var style = ws.Cells[400, 1].Style;

//If color from System colors or palette
if (!string.IsNullOrEmpty(style.Fill.BackgroundColor.Rgb))
{
   //Convert to system.drawing.colow
   var color = System.Drawing.ColorTranslator.FromHtml("#" + style.Fill.BackgroundColor.Rgb);
   //set color in this cell
   ws.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
   ws.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(color);
}
else if (!string.IsNullOrEmpty(style.Fill.BackgroundColor.Theme))
{
   //No idea how to get color from Theme
}

I'm not sure it's supported... according to EPPlus Faqs :

What is NOT supported by the library (these are the most obvious features)? [...] * Themes

like image 131
BunkerMentality Avatar answered Sep 26 '22 14:09

BunkerMentality


For anyone getting here, since I had my round of fun with EPPlus and I wasn't satisfied with the answers above:

cell.Style.Fill.BackgroundColor.LookupColor()

returns #AARRGGBB color (I.E. red is #FFFF0000). The part you're interesdted in is probably the last 6 digits.

like image 35
Liquid Core Avatar answered Sep 26 '22 14:09

Liquid Core