How to 'Filter by Color' using Cells for Java

Aspose.Cells Product Family
1

I have a column of numbers that all have a color group assigned to them. I want to simulate clicking on the autoFilter arrow and selecting Filter by Color and then selecting the Red swatch using Cells for Java.

I’m having a problem with the two CellsColor arguments in the AutoFilter.addFillColorFilter, I’m not quite sure how to instantiate them and use them.

Any help would be appreciated! Sample code too! :slight_smile:

thank you!
Brian

2

@brian.howe

Thanks for considering Aspose APIs,

It seems this feature is supported by Aspose.Cells. We will look into it and provide you a sample code to fulfill your requirements.

3

Thank you Shakeel! We actually bought an OEM license for the JAVA Cells product shortly after I posted that, it is great! I look forward to learning how to make this call. Everything else is going smoothly!

4

@brian.howe

Please see the following Java and C# code, its input Excel file and output Excel file as well as screenshot for your reference.

Download Links:
Input and Output Excel Files.zip (12.5 KB)

Java

// Load source workbook
Workbook wb = new Workbook(dirPath + "ColorFilter.xlsx");

// Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

// Access any cell, try with A3, A4 or A12 etc.
Cell cell = ws.getCells().get("A3");

// Get the style of your choosen cell
Style st = cell.getStyle();

// Create foreground and background colors
CellsColor fg = wb.createCellsColor();
CellsColor bg = wb.createCellsColor();
fg.setColor(st.getForegroundColor());
bg.setColor(st.getBackgroundColor());

// Apply filter. Set range and apply color filter and then refresh it
ws.getAutoFilter().setRange("A1");
ws.getAutoFilter().addFillColorFilter(0, st.getPattern(), fg, bg);
ws.getAutoFilter().refresh();

// Save the workbook
wb.save(dirPath + "outputColorFilter.xlsx");

C#

//Load source workbook
Workbook wb = new Workbook("ColorFilter.xlsx");

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Access any cell, try with A3, A4 or A12 etc.
Cell cell = ws.Cells["A3"];

//Get the style of your choosen cell
Style st = cell.GetStyle();

//Create foreground and background colors
CellsColor fg = wb.CreateCellsColor();
CellsColor bg = wb.CreateCellsColor();
fg.Color = st.ForegroundColor;
bg.Color = st.BackgroundColor;

//Apply filter. Set range and apply color filter and then refresh it
ws.AutoFilter.Range = "A1";
ws.AutoFilter.AddFillColorFilter(0, st.Pattern,fg, bg);
ws.AutoFilter.Refresh();

//Save the workbook
wb.Save("outputColorFilter.xlsx");

Screenshot