I want to set the color of the different columns headers in excel file so how can I set the indexed value to set the color of each column header and suppose if I am getting 4 different colors in hexadecimal(#FFF) format from API request then, how can I set those color into 4 different column headers.
Here is my code:
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.commons.codec.binary.Hex;
class CreateXSSFColor {
public static void main(String[] args) throws Exception {
try (Workbook workbook = new XSSFWorkbook();
FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {
String rgbS = "FFF000";
byte[] rgbB = Hex.decodeHex(rgbS); // get byte array from hex string
XSSFColor color = new XSSFColor(rgbB, null); //IndexedColorMap has no usage until now. So it can be set null.
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
cellStyle.setFillForegroundColor(color);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("yellow");
cell.setCellStyle(cellStyle);
workbook.write(fileout);
}
}
}
Your showed code creates one XSSFColor from hex string and creates one XSSFCellStyle having that XSSFColor as the fill color. Then it uses that XSSFCellStyle as the cell style of one cell.
If you have 4 hex strings, which shall be fill colors of cells, then you need creating 4 XSSFColors for 4 XSSFCellStyles, each XSSFCellStyle having one of the 4 XSSFColors as the fill foreground color. Then you can use those 4 different XSSFCellStyles as cell styles for different cells in the sheet.
Of course if the hex strings are dynamically coming from outside, then the same hex string can come multiple times. Then you should not creating the same cell styles over and over again. There are limits for the count of cell styles in Excel. See Excel specifications and limits.
The following example provides a method setFillForegroundXSSFColor which sets a given XSSFColor as the fill foreground color in cell style of given XSSFCell. It does not always creating a new cell style for this. Instead it checks whether there is a fitting cell style already in workbook, which is a combination of the current cell style of the cell plus required fill foreground color. If so, then that cell style is used. Only if not, a new cell style is created.
It also provides a method getRGBFromHexString which uses org.apache.commons.codec.binary.Hex to convert a hex string into a byte array. This uses string manipulation to support following hex string types for RGB: FFFFFF, FFF, #FFFFFF and #FFF.
Example code:
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.xssf.usermodel.*;
import org.apache.commons.codec.binary.Hex;
class CreateXSSFCellStyles {
static byte[] getRGBFromHexString(String rgbString) {
byte[] rgb = new byte[3];
if (rgbString.startsWith("#")) rgbString = rgbString.substring(1);
if (rgbString.length() == 3)
rgbString = rgbString.substring(0,1) + rgbString.substring(0,1)
+ rgbString.substring(1,2) + rgbString.substring(1,2)
+ rgbString.substring(2,3) + rgbString.substring(2,3);
System.out.println(rgbString);
try {
rgb = Hex.decodeHex(rgbString);
} catch (Exception ex) {
//decoder exception so byte[] rgb remains empty
}
return rgb;
}
static void setFillForegroundXSSFColor(XSSFCell cell, XSSFColor color, FillPatternType fillPatternType) {
//get original cell style of cell
XSSFCellStyle originalCellStyle = cell.getCellStyle();
//is there a fitting cell style already in workbook?
XSSFWorkbook workbook = cell.getSheet().getWorkbook();
for (int i = 0; i < workbook.getNumCellStyles(); i++) {
XSSFCellStyle cellStyle = workbook.getCellStyleAt(i);
//cell style needs to fit requested fill foreground color and fill pattern type
if (color.equals(cellStyle.getFillForegroundColorColor()) && cellStyle.getFillPattern() == fillPatternType) {
//also cell style needs to fit all other cell style properties of original cell style
if (
cellStyle.getAlignment() == originalCellStyle.getAlignment()
&& cellStyle.getBorderLeft() == originalCellStyle.getBorderLeft()
&& cellStyle.getBorderTop() == originalCellStyle.getBorderTop()
&& cellStyle.getBorderRight() == originalCellStyle.getBorderRight()
&& cellStyle.getBorderBottom() == originalCellStyle.getBorderBottom()
&& cellStyle.getLeftBorderColor() == originalCellStyle.getLeftBorderColor()
&& cellStyle.getTopBorderColor() == originalCellStyle.getTopBorderColor()
&& cellStyle.getRightBorderColor() == originalCellStyle.getRightBorderColor()
&& cellStyle.getBottomBorderColor() == originalCellStyle.getBottomBorderColor()
&& cellStyle.getDataFormat() == originalCellStyle.getDataFormat()
&& cellStyle.getFillBackgroundColor() == originalCellStyle.getFillBackgroundColor()
&& cellStyle.getFont() == originalCellStyle.getFont()
&& cellStyle.getHidden() == originalCellStyle.getHidden()
&& cellStyle.getIndention() == originalCellStyle.getIndention()
&& cellStyle.getLocked() == originalCellStyle.getLocked()
&& cellStyle.getQuotePrefixed() == originalCellStyle.getQuotePrefixed()
&& cellStyle.getReadingOrder() == originalCellStyle.getReadingOrder()
&& cellStyle.getRotation() == originalCellStyle.getRotation()
&& cellStyle.getShrinkToFit() == originalCellStyle.getShrinkToFit()
&& cellStyle.getVerticalAlignment() == originalCellStyle.getVerticalAlignment()
&& cellStyle.getWrapText() == originalCellStyle.getWrapText()
) {
cell.setCellStyle(cellStyle);
System.out.println("fitting cell style found");
return;
}
}
}
//no fitting cell style found, so create a new one
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.cloneStyleFrom(originalCellStyle);
cellStyle.setFillForegroundColor(color);
cellStyle.setFillPattern(fillPatternType);
cell.setCellStyle(cellStyle);
System.out.println("new cell style created");
}
public static void main(String[] args) throws Exception {
try (XSSFWorkbook workbook = new XSSFWorkbook();
FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {
XSSFCellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
XSSFFont font = workbook.createFont();
font.setColor(IndexedColors.WHITE.getIndex());
headerCellStyle.setFont(font);
System.out.println(workbook.getNumCellStyles()); // 2 == default + header cell style
XSSFSheet sheet = workbook.createSheet();
XSSFRow row = sheet.createRow(0);
XSSFCell cell;
for (int i = 0; i < 8; i++) {
cell = row.createCell(i);
cell.setCellValue("Header " + (i+1));
cell.setCellStyle(headerCellStyle);
}
String rgbS;
byte[] rgbB;
XSSFColor color;
rgbS = "#FF0000";
cell = sheet.getRow(0).getCell(1);
rgbB = getRGBFromHexString(rgbS);
color = new XSSFColor(rgbB, null);
setFillForegroundXSSFColor(cell, color, FillPatternType.SOLID_FOREGROUND);
System.out.println(workbook.getNumCellStyles()); // 3 == default + header cell style + this one new created
rgbS = "0000FF";
cell = sheet.getRow(0).getCell(3);
rgbB = getRGBFromHexString(rgbS);
color = new XSSFColor(rgbB, null);
setFillForegroundXSSFColor(cell, color, FillPatternType.SOLID_FOREGROUND);
System.out.println(workbook.getNumCellStyles()); // 4 == default + header cell style + one from above + this one new created
rgbS = "#F00";
cell = sheet.getRow(0).getCell(5);
rgbB = getRGBFromHexString(rgbS);
color = new XSSFColor(rgbB, null);
setFillForegroundXSSFColor(cell, color, FillPatternType.SOLID_FOREGROUND);
System.out.println(workbook.getNumCellStyles()); // 4 == default + header cell style + two from above, none new created
rgbS = "#0000FF";
cell = sheet.getRow(0).getCell(7);
rgbB = getRGBFromHexString(rgbS);
color = new XSSFColor(rgbB, null);
setFillForegroundXSSFColor(cell, color, FillPatternType.SOLID_FOREGROUND);
System.out.println(workbook.getNumCellStyles()); // 4 == default + header cell style + two from above, none new created
workbook.write(fileout);
}
}
}