Java poi excel cell color

How to Fill Background Color of Cells in Excel using Java and Apache POI?

Apache POI is an open-source java library to create and manipulate various file formats based on Microsoft Office. Using POI, one should be able to perform create, modify and display/read operations on the following file formats. For Example, Java doesn’t provide built-in support for working with excel files, so we need to look for open-source APIs for the job. In this article, we are going to discuss how to fill the background colors and foreground colors of the cells in Excel using the Apache POI. First, we will create an Excel file and enter the cell values and add the colors for each cell. Apache POI is providing the createcellstyle method in XSSFCellStyle.

Pre-Requisite

To work with the Apache POI, we need the following software in our system

• Check that your system has Java JDK
• Install the Eclipse IDE for Java developers from here.
• Create a Maven project

• Add the Apache POI and Apache-ooxml dependency into the POM.xml file

After installing all the dependencies, we are now ready to write the code, it is styled using the creatcellstyle method. The style method provides many designs such as setFillBackgroundColor, setFillForegroundColor and setFillPattern.

setFillgroundColor:

This method enables us to set the background color of the cell, the Apache POI dependency provides us with the Indexed color class that has all the colors.

style.setFillForegroundColor(IndexedColors.”COLOR.”getIndex());

setFillForegroundColor:

This method is also similar to the setBackgroundColor.

style.setFillForegroundColor(IndexedColors.“COLOR”.getIndex());

setFillPattern:

This method provides various types of design patterns like Big spots, Briks, Diamond, Fine Dots, etc.

Источник

Filling background color in excel rows using Java apache POI

I am trying to read a excel sheet and fill the background color for rows using following code:

when I run my code, the color is getting filled for only blank cells. For all cells which contains data there is no change in color. Can someone tell me why it is happening?

2 Answers 2

As it turns out, set setFillForegroundColor is for setting cell background color. Comment out setFillBackgroundColor and it should work.

Working test code

In short: setRowStyle is not doing what you assume it does.

All it does (see source) is register the style as the rows default style.

It does not iterate over all the cells in a row and changes their style. Thus the style only applies to not existing cells(1) and newly created cells which reference the row style by default.

As you can see from the code above styles are simply referenced by their index. In order to allow for different styles on the row and various cells, the cells must be able to reference a different style. The cell style then logically supersedes the row style. Thus to apply a style to all cells you must assign the style not only to the row but to all existing cells as well.

You said in your question that you are reading the document and then you try to color the row. So I will assume that your not actually creating new cells yourself, because POI should then copy the row style and this would probably be a bug.

In your case it’s probably a bit like this (simplified):
The existing cells in your document reference the style with index 0 . You now create a new style with index 1 and apply it to the row via setRowStyle .
All not existing(1) and new cells will then use the style with index 1 . The existing cells however still point to the style with index 0 , because they have not automatically been assigned the new style.

You probably expected setRowStyle to behave like the Excel application, where you can select the entire row and set the style on it. But that’s not how it works. You have to iterate manually and apply the change to all cells.

1 : Cells that exist only logically in the Excel application but not yet physically in the data structure in order to save space. This is why getCell can return null and doesn’t just return CELL_TYPE_BLANK by default.

Источник

POI setting Cell Background to a Custom Color

I want to set custom color to a cell’s background.
I use HSSFWorkbook (can’t use anything else).

I get this error: java.lang.RuntimeException: Could not find free color index

6 Answers 6

You get this error because pallete is full. What you need to do is override preset color. Here is an example of function I’m using:

And later use it for background color:

Don’t forget to call this.

Parameter may differ according to your need. Maybe CellStyle.FINE_DOTS or so.

Slot free in NPOI excel indexedcolors from 57+

As pointed in Vlad’s answer, you are running out of free color slots. One way to get around that would be to cache the colors: whenever you try a RGB combination, the routine should first check if the combination is in the cache; if it is in the cache, then it should use that one instead of creating a new one from scratch; new colors would then only be created if they’re not yet in cache.

Here’s the implementation I use; it uses XSSF plus Guava’s LoadingCache and is geared towards generationg XSSF colors from CSS rgb(r, g, b) declarations, but it should be relatively trivial to adapt it to HSSF:

Perhaps someone else could post a HSSF equivalent? 😉

You can set custom color using this-

Linked

Related

Hot Network Questions

Subscribe to RSS

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.3.20.43325

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник

How to set cell background color in excel using java + poi

So far I did bellow coding using Spring MVC.

Important- I am not using here HSSFWorkbook

So what is the equivalent of the POI method setCellStyle in StreamingReader

My requirement is suppose if a cell data is not formatted well example «wrong data format» so I want to change that particular cell background color.

But if I am using this am able to do it

But when I am using this code I am not able to load a huge amount of file.

In my above code only I need changes to add style.

2 Answers 2

Here’s a partial solution that might help you:

I have set rowCacheSize to 1000 (with your higher value, it took way too long)

I have created the cellStyle myself and used setCellStyle on it.

I have — however — not been able to find a save method so far, so I could not test it.

The Excel Streaming Reader is named Reader because it is for reading only. There is a StreamingWorkbook which implements Workbook but most of it’s methods are not implemented yet. So it is a draft until now.

If it comes to huge Excel files then we must consider a totally different approach than creating HSSFWorkbook or XSSFWorkbook from those huge files.

A Workbook can only be created as a whole. But of course we could take single parts of the Excel files like the sheets part (containing rows and cells) or the styles part (containing the styles of the cells, the fonts, fills, borders etc.) or the shared strings part (containing text contents of the cells) and parse those. This leads to less resource consuming since the single parts are less huge than the whole file. But it also needs more knowledge about the internal structure of the file. And the easiest task is reading and parsing the single parts but if it comes to changings and so to the need writing those into the part streams, then it gets quickly complicated because then we need also to know about and to take into account the part relationships.

Fortunately the XSSF ( *.xlsx ) parts are XML and as such to parse (read) and also to write using StAX.

The following code uses this approach. It gets the styles table part and the sheet1 part form the ZIP package of an *.xlsx file and then sets red background color in each cell of every 5th row from Sheet1 of this file. Also if the file maybe huge, the code should nevertheless running with as less possible resource amount.

Источник

ThinkTibits!

setFillBackgroundColor — Introduction

In this post, we will explain how to fill the background of a cell in Excel with a particular color, in Java using Apache POI, with example programs.Background color for a cell is set using setFillBackgroundColor method, and we will see how to use this method and pass different colors to it. We will provide two set of examples as usual, one for XLS worksheets and one for XLSX flavour. Let us get started.

XLS — Set Fill Color for Cell — Example Program

You can pass different values to setFillPattern. We will have a separate tutorial on setFillPattern later. Foreground color should be set first before background color.

XLSX Example — Cell Fill Color — Java Program

Here is a XLSX example for the cell fill color.

Here we use IndexedColors class to pass a color to background / foreground methods. This however, may not be a desired practice. The methods accept a XSSFColor object as input, so you may want to specify your color through XSSFColor. XSSFColor class has a constructor that accepts a color object of type java.awt.Color, plethora of possibilities. Here is a code snippet on how to do this in Java.

where, Color, is of type java.awt.Color.

You can run these programs yourself to see the output in action. Make sure you have all the POI JAR files in your classpath. See you in a different tutorial next time.

Источник