search

nissl-lab / npoi

a .NET library that can read/write Office formats without Microsoft Office installed. No COM+, no interop.
Apache License 2.0
5.73k stars 1.43k forks source link

Cell color info is not correct. How to get correct info? #820

Open weix77 opened 2 years ago

weix77 commented 2 years ago

Reading colors of different cells gives same color index and RGB despite that actual colors in .xls file are different

Numbers inside cells are color indexes returned by NPOI. As you can see, several of the indexes repeat although all the colors are different. image

Sample project output: 1) Row=0, Col=0, ColorIndex=9, RGB=(255,255,255) 2) Row=0, Col=1, ColorIndex=8, RGB=(0,0,0) 3) Row=0, Col=2, ColorIndex=9, RGB=(255,255,255) 4) Row=0, Col=3, ColorIndex=54, RGB=(102,102,153) 5) Row=0, Col=4, ColorIndex=62, RGB=(51,51,153) 6) Row=0, Col=5, ColorIndex=53, RGB=(255,102,0) 7) Row=0, Col=6, ColorIndex=55, RGB=(150,150,150) 8) Row=0, Col=7, ColorIndex=51, RGB=(255,204,0) 9) Row=0, Col=8, ColorIndex=49, RGB=(51,204,204) 10) Row=0, Col=9, ColorIndex=57, RGB=(51,153,102) 11) Row=1, Col=0, ColorIndex=22, RGB=(192,192,192) 12) Row=1, Col=1, ColorIndex=23, RGB=(128,128,128) 13) Row=1, Col=2, ColorIndex=22, RGB=(192,192,192) 14) Row=1, Col=3, ColorIndex=22, RGB=(192,192,192) 15) Row=1, Col=4, ColorIndex=31, RGB=(204,204,255) 16) Row=1, Col=5, ColorIndex=47, RGB=(255,204,153) 17) Row=1, Col=6, ColorIndex=9, RGB=(255,255,255) 18) Row=1, Col=7, ColorIndex=26, RGB=(255,255,204) 19) Row=1, Col=8, ColorIndex=27, RGB=(204,255,255) 20) Row=1, Col=9, ColorIndex=42, RGB=(204,255,204) 21) Row=2, Col=0, ColorIndex=22, RGB=(192,192,192) 22) Row=2, Col=1, ColorIndex=63, RGB=(51,51,51) 23) Row=2, Col=2, ColorIndex=22, RGB=(192,192,192) 24) Row=2, Col=3, ColorIndex=22, RGB=(192,192,192) 25) Row=2, Col=4, ColorIndex=44, RGB=(153,204,255) 26) Row=2, Col=5, ColorIndex=47, RGB=(255,204,153) 27) Row=2, Col=6, ColorIndex=22, RGB=(192,192,192) 28) Row=2, Col=7, ColorIndex=43, RGB=(255,255,153) 29) Row=2, Col=8, ColorIndex=44, RGB=(153,204,255) 30) Row=2, Col=9, ColorIndex=43, RGB=(255,255,153)

Sample project with .xls file: NPOI_ColorIssue.zip .

tonyqus commented 2 years ago

The result in xlsx (XSSFWorkbook) is different

1) Row=0, Col=0,Value=9 Indexed=0 RGB=(255,255,255)
2) Row=0, Col=1,Value=8 Indexed=0 RGB=(0,0,0)
3) Row=0, Col=2,Value=9 Indexed=0 RGB=(231,230,230)
4) Row=0, Col=3,Value=54 Indexed=0 RGB=(68,84,106)
5) Row=0, Col=4,Value=62 Indexed=0 RGB=(68,114,196)
6) Row=0, Col=5,Value=53 Indexed=0 RGB=(237,125,49)
7) Row=0, Col=6,Value=55 Indexed=0 RGB=(165,165,165)
8) Row=0, Col=7,Value=51 Indexed=0 RGB=(255,192,0)
9) Row=0, Col=8,Value=49 Indexed=0 RGB=(91,155,213)
10) Row=0, Col=9,Value=57 Indexed=0 RGB=(112,173,71)
11) Row=1, Col=0,Value=22 Indexed=0 RGB=(255,255,255)
12) Row=1, Col=1,Value=23 Indexed=0 RGB=(0,0,0)
13) Row=1, Col=2,Value=22 Indexed=0 RGB=(231,230,230)
14) Row=1, Col=3,Value=22 Indexed=0 RGB=(68,84,106)
15) Row=1, Col=4,Value=31 Indexed=0 RGB=(68,114,196)
16) Row=1, Col=5,Value=47 Indexed=0 RGB=(237,125,49)
17) Row=1, Col=6,Value=9 Indexed=0 RGB=(165,165,165)
18) Row=1, Col=7,Value=26 Indexed=0 RGB=(255,192,0)
19) Row=1, Col=8,Value=27 Indexed=0 RGB=(91,155,213)
20) Row=1, Col=9,Value=42 Indexed=0 RGB=(112,173,71)
21) Row=2, Col=0,Value=22 Indexed=0 RGB=(255,255,255)
22) Row=2, Col=1,Value=63 Indexed=0 RGB=(0,0,0)
23) Row=2, Col=2,Value=22 Indexed=0 RGB=(231,230,230)
24) Row=2, Col=3,Value=22 Indexed=0 RGB=(68,84,106)
25) Row=2, Col=4,Value=44 Indexed=0 RGB=(68,114,196)
26) Row=2, Col=5,Value=47 Indexed=0 RGB=(237,125,49)
27) Row=2, Col=6,Value=22 Indexed=0 RGB=(165,165,165)
28) Row=2, Col=7,Value=43 Indexed=0 RGB=(255,192,0)
29) Row=2, Col=8,Value=44 Indexed=0 RGB=(91,155,213)
30) Row=2, Col=9,Value=43 Indexed=0 RGB=(112,173,71)
weix77 commented 2 years ago

Thanks for reply.

But does this mean that the correct color information cannot be read directly from the .xls file (XSSFWorkbook is not able to read .xls file)? Or is it possible to read an .xls file and use XSSFWorkbook for that?

And there seems to be a issue reading the .xlsx file as well. Namely, the color codes in the first row start repeating for the second and third rows.

For example: 1) Row=0, Col=0,Value=9 Indexed=0 RGB=(255,255,255) 2) Row=0, Col=1,Value=8 Indexed=0 RGB=(0,0,0) 3) Row=0, Col=2,Value=9 Indexed=0 RGB=(231,230,230)

11) Row=1, Col=0,Value=22 Indexed=0 RGB=(255,255,255) 12) Row=1, Col=1,Value=23 Indexed=0 RGB=(0,0,0) 13) Row=1, Col=2,Value=22 Indexed=0 RGB=(231,230,230)

21) Row=2, Col=0,Value=22 Indexed=0 RGB=(255,255,255) 22) Row=2, Col=1,Value=63 Indexed=0 RGB=(0,0,0) 23) Row=2, Col=2,Value=22 Indexed=0 RGB=(231,230,230)

tonyqus commented 2 years ago

This bug is weird, I have no clues yet.

Bykiev commented 1 year ago

I've researched the issue and I'm not 100% sure if it's a bug. When detecting the color it uses the standard palette of colors, that's why the color is different.

POI docs:

getFillForegroundColorColor() Gets the color object representing the current foreground fill, resolving indexes using the supplied workbook.

@tonyqus,what do you think?

tonyqus commented 1 year ago

I reviewed the code again.

I notice that HSSF is using CustomPallette while XSSF is using theme and patternfill in openxml. They are different logic.