How to get an Excel cell color when the color is set by conditional formatting

Aspose.Cells Product Family
1

Hi Support,


I would like to know if your library supports reading an Excel cell color using Java when the color is set by conditional formatting ?

This message was posted using Email2Forum by Imran Rafique.
2

Hi,


Well, you may try to use Cell.getDisplayStyle() to get the displayed style related to conditional formatting and then get the background or foreground color accordingly. See the sample code snippet below for your reference:

e.g
Sample code:

//…
Cell cell=cells.get(“A1”);

style = cell.getDisplayStyle();

//Get the foreground/background color

Color foregroundColor = style.getForegroundColor();
Color backgroundColor = style.getBackgroundColor();




Thank you.
3

Thank you. I think this will just get the cell color before conditional formatting is applied ?

4

Getting the color of conditional formatting is very important to my project. I am using POI at the moment, but it does not support this feature. I will switch to Aspose if it support it.


I have not used your library before, so could please you try the solution you propose to see if it actually works with conditional formatting.
5

Hi,


Yes, as I told you that you may try to use Cell.getDisplayStyle() to get the displayed style related to conditional formatting (it will get the style/formatting of the cell if conditional formatting is applied to it). Please see my previous sample code and try it. If you find any issue, kindly let us know with sample code and template Excel file (if you have any), we will check it soon.

Thank you.
6
I am trying to make a simple example below, but new Workbook() never returns nor throws exception. I am new to Aspose. What am I doing wrong ?


com.aspose.cells.Workbook workbook = null ;

try {

System.out.println("start open workbook");

workbook = new com.aspose.cells.Workbook("test.xlsx");

System.out.println("end open workbook");

} catch (Exception e) {

System.out.println("Failed to open workbook");

e.printStackTrace();

}

7

Please ignore my previous message - I had jar file load issues which I have sorted.

8

Hi,

For your information, Aspose.Cells is a class library/component that does not provide any user interface. It is spreadsheet management library used to create, manipulate, render or print Excel file formats (XLS, XLSX, XLSM, XLSB, CSV, ODS, SpreadsheetML, PDF etc.). It supports almost all the features that MS Excel (97-2007/2010) supports. If you need to view Excel files in WYSIWYG (visual) manner, Aspose.Cells for .NET also provides a suit of Grid like controls (independent controls i.e…, Aspose.Cells.GridDesktop and Aspose.Cells.GridWeb) that provides this capability to view/display, create and manipulate Excel files on the fly.

Please see the docs and demos of GridWeb for your reference:

Aspose.Cells.GridDesktop
Aspose.Cells.GridWeb
Aspose.Cells-for-.NET

See the docs for Aspose.Cells.GridDesktop that is similar control as GridWeb but it is used for desktop/winform applications:

PS: We don’t have such controls in JAVA though.

Let us know if you still have any confusion or issue.

Thank you.

9

OK, I have now got the colors:

Color foregroundColor = style.getForegroundColor();
Color backgroundColor = style.getBackgroundColor();

I want to see what these  colors are in terms of RGB.  How do I do that?  
(All the documentation seems to be about setting the color not getting it )
10

Hi,


Well, you may try the following sample code for your needs:
e.g
Sample code:
Color foregroundColor = style.getForegroundColor();
System.out.println("Red : " + foregroundColor.getR() +
", Green : " + foregroundColor.getG() +
", Blue : " +foregroundColor.getB());


Thank you.
11

The cell.getDisplayStyle() works well when the condition is an expression (for example, if cell value is > 10) . But when I use a TOP 10 condition, it just returns the original colors of the cell ignoring conditional formatting ?

12

Hi,


Could you post your template Excel file and share the sample code here, we will check your issue soon.

Thank you.
13

The code:


try {

workbook = new com.aspose.cells.Workbook(path);

Worksheet worksheet = workbook.getWorksheets().get(0);

com.aspose.cells.Cell cell = worksheet.getCells().get("A1");

String text = cell.getDisplayStringValue();

System.out.println("Text: " + text);

Style style = cell.getDisplayStyle();

Color color = style.getBackgroundColor();

System.out.println("Color BG: " + String.format("#%02x%02x%02x", color.getR(), color.getG() , color.getB()) );

color = style.getForegroundColor();

System.out.println("Color FG: " + String.format("#%02x%02x%02x", color.getR(), color.getG() , color.getB()) );

} catch (Exception e) {

System.out.println("Failed to open workbook");

e.printStackTrace();

}


Run 1 without TOP 10 conditional formatting:

Text: 5

Color BG: #ffffff

Color FG: #808080


Run 2 with TOP 10 conditional formatting (set to highlight top 3 - Excel spreadsheet attched)

Text: 5

Color BG: #ffffff

Color FG: #808080

Does not pick up conditional formatting background change.

14

Hi,

Thanks for your posting and using Aspose.Cells for Java.

You are right. Cell.getDisplayStyle() is not returning the color of TOP 10 conditional formatting. We will look into it and resolve this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40671.

15

Hi,

Thanks for using Aspose.Cells.

We have fixed this issue.

Please download and try the latest version: Aspose.Cells for Java (Latest Version) and let us know your feedback.

16

The issues you have found earlier (filed as CELLSJAVA-40671) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.