POI 获取单元格的背景色及设置单元格背景色
需求:利用poi读取excel表时,要获取到单元格的填充颜色?
XSSFCell xhf = columnRow.getCell(j); //获取这单元格
XSSFCellStyle eStyle = xhf.getCellStyle(); //获取该单元格的样式
XSSFColor standard_color = eStyle.getFillForegroundColorColor();//获取这个单元的格的XSSFColor 对象,(背景色信息在这里)
if(standard_color != null) {
System.out.println(standard_color.getARGBHex()); //输出格式为 FFFFFF00(代表填充色为黄色),FFFFFF00(红色)
}
记住要先判断一下XSSFColor 对象是不是为空,应为如果单元格没有填充色,即这对象为null。
short short = eStyle.getFillForegroundColor();
之前单元格中填充色只有黄色,一直使用这种方法,如果有填充色,返回0,反之返回64.该方法的缺陷就是不管填充色是什么颜色,它都返回一个0,即区分不开。
设置单元格背景色转载至:https://blog.csdn.net/drifterj/article/details/46662277
This example shows you Excel cell fills and colors using Apache POI.
In our example i have used all the possible colors and set it as Fills background colors of cells.
Below is the example code.
package com.java.connect.poi;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class POIFillAndColorExample {
public static void main(String[] args) throws IOException {
// Create a workbook object
Workbook workbook = new XSSFWorkbook();
// Create sheet
Sheet sheet = workbook.createSheet();
// Create a row and put some cells in it.
Row row = sheet.createRow((short) 1);
// Aqua background
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
Cell cell = row.createCell((short) 1);
cell.setCellValue("X1");
cell.setCellStyle(style);
// Orange "foreground", foreground being the fill foreground not the
// font color.
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.AUTOMATIC.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 2);
cell.setCellValue("X2");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 3);
cell.setCellValue("X3");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 4);
cell.setCellValue("X4");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 5);
cell.setCellValue("X5");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row2 = sheet.createRow((short) 2);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.BROWN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row2.createCell((short) 1);
cell.setCellValue("X6");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.CORAL.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row2.createCell((short) 2);
cell.setCellValue("X7");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row2.createCell((short) 3);
cell.setCellValue("X8");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row2.createCell((short) 4);
cell.setCellValue("X9");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.DARK_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row2.createCell((short) 5);
cell.setCellValue("X10");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row3 = sheet.createRow((short) 3);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row3.createCell((short) 1);
cell.setCellValue("X11");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.DARK_TEAL.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row3.createCell((short) 2);
cell.setCellValue("X12");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row3.createCell((short) 3);
cell.setCellValue("X13");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GOLD.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row3.createCell((short) 4);
cell.setCellValue("X14");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row3.createCell((short) 5);
cell.setCellValue("X15");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row4 = sheet.createRow((short) 4);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row4.createCell((short) 1);
cell.setCellValue("X16");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row4.createCell((short) 2);
cell.setCellValue("X17");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row4.createCell((short) 3);
cell.setCellValue("X18");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row4.createCell((short) 4);
cell.setCellValue("X19");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.INDIGO.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row4.createCell((short) 5);
cell.setCellValue("X20");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row5 = sheet.createRow((short) 5);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LAVENDER.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row5.createCell((short) 1);
cell.setCellValue("X21");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row5.createCell((short) 2);
cell.setCellValue("X22");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row5.createCell((short) 3);
cell.setCellValue("X23");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row5.createCell((short) 4);
cell.setCellValue("X24");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row5.createCell((short) 5);
cell.setCellValue("X25");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row6 = sheet.createRow((short) 6);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE
.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row6.createCell((short) 1);
cell.setCellValue("X26");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row6.createCell((short) 2);
cell.setCellValue("X27");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row6.createCell((short) 3);
cell.setCellValue("X28");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row6.createCell((short) 4);
cell.setCellValue("X29");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row6.createCell((short) 5);
cell.setCellValue("X30");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row7 = sheet.createRow((short) 7);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.LIME.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row7.createCell((short) 1);
cell.setCellValue("X31");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.MAROON.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row7.createCell((short) 2);
cell.setCellValue("X32");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.OLIVE_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row7.createCell((short) 3);
cell.setCellValue("X33");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row7.createCell((short) 4);
cell.setCellValue("X34");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORCHID.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row7.createCell((short) 5);
cell.setCellValue("X35");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row8 = sheet.createRow((short) 8);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row8.createCell((short) 1);
cell.setCellValue("X36");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.PINK.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row8.createCell((short) 2);
cell.setCellValue("X37");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.PLUM.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row8.createCell((short) 3);
cell.setCellValue("X38");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row8.createCell((short) 4);
cell.setCellValue("X39");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.ROSE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row8.createCell((short) 5);
cell.setCellValue("X40");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row9 = sheet.createRow((short) 9);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row9.createCell((short) 1);
cell.setCellValue("X41");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row9.createCell((short) 2);
cell.setCellValue("X42");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row9.createCell((short) 3);
cell.setCellValue("X43");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.TAN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row9.createCell((short) 4);
cell.setCellValue("X44");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.TEAL.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row9.createCell((short) 5);
cell.setCellValue("X45");
cell.setCellStyle(style);
// Create a row and put some cells in it.
Row row10 = sheet.createRow((short) 10);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.TURQUOISE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row10.createCell((short) 1);
cell.setCellValue("X46");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.VIOLET.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row10.createCell((short) 2);
cell.setCellValue("X47");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row10.createCell((short) 3);
cell.setCellValue("X48");
cell.setCellStyle(style);
style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row10.createCell((short) 3);
cell.setCellValue("X49");
cell.setCellStyle(style);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(
"POIFillAndColorExample.xlsx");
workbook.write(fileOut);
fileOut.close();
}
}
The generated excel files looks like below images.
当对row设置了行高时,又设置自动换行,自动换行是不会生效的
自动换行是在默认行高的情况下,才会自动调节行高。如果已经手动调节了行高,设置自动换行后,双击单行标分界线,也能自动调节高度
XSSFRow 的 setHeight((short) 280); //设置行高
regionStyle.setWrapText(true); //自动换行