poi使用笔记

红太狼 2022-05-27 04:19 177阅读 0赞

### 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.

![0a9351b6-c42f-3a4a-a1e4-a122b879e260.bmp][]

当对row设置了行高时，又设置自动换行，自动换行是不会生效的

自动换行是在默认行高的情况下，才会自动调节行高。如果已经手动调节了行高，设置自动换行后，双击单行标分界线，也能自动调节高度

XSSFRow 的 setHeight((short) 280); //设置行高

regionStyle.setWrapText(true); //自动换行

[0a9351b6-c42f-3a4a-a1e4-a122b879e260.bmp]: /images/20220527/6df15ca0e71640fd874d711f7ae563ef.png