Is there any way to add color to cell from hex value like '#72fe9c' in my excel doc using NPOI

Question

As I am new with NPOI, and I want to add color to a cell in my excel sheet. I am having a hex value like '#ffeeff' and in ICellStyle.FillForegroundColor only short int value can be assigned.

System.OverflowException: Value was either too large or too small for an Int16.

I have tried code like this and it is working

style.FillForegroundColor = HSSFColor.Grey25Percent.Index;

but I only have hex value that can be converted to int, but it supports only short int values.

//it is working
style.FillForegroundColor = HSSFColor.Grey25Percent.Index;

// not working for me as '#ffeeff' canot be converted to short, it can only be converted to int
style.FillForegroundColor = short.Parse(fontcolorCode.Substring(1), NumberStyles.HexNumber)

style.FillForegroundColor = short.Parse(fontcolorCode.Substring(1), NumberStyles.HexNumber) 

It should not throw an error and in the excel sheet, the same color(fontcolorCode) must be applied to the cell

Answer 1

short (Int16) is not big enough for this value. Take int (Int32) instead:

string myHexColor = "#ffeeff";

int x = int.Parse(myHexColor.Substring(1), NumberStyles.HexNumber);

Console.WriteLine("Color is:  " + x);              //   16772863 
Console.WriteLine("Max short: " + short.MaxValue); //      32767
Console.WriteLine("Max int:   " + int.MaxValue);   // 2147483647

You have to create a Color-object:

string myHexColor = "#ffeeff";

byte r = Convert.ToByte(myHexColor.Substring(1, 2).ToUpper(), 16);
byte g = Convert.ToByte(myHexColor.Substring(3, 2), 16);
byte b = Convert.ToByte(myHexColor.Substring(5, 2), 16);
Console.WriteLine("{0} = {1}/{2}/{3}", myHexColor, r, g, b);

IWorkbook workbook = null;
NPOI.XSSF.UserModel.XSSFCellStyle style = (NPOI.XSSF.UserModel.XSSFCellStyle)workbook.CreateCellStyle();

// Here we create a color from RGB-values
IColor color = new NPOI.XSSF.UserModel.XSSFColor(new byte[] { r, g, b });

style.SetFillForegroundColor(color );

ICell cellToPaint = null; // Select your cell..
cellToPaint.CellStyle = style;