POI获取单元格颜色与设置单元格颜色

ExcelDTO

@Data
public class ExcelDto {
	String text;
	String color;

	public ExcelDto()  {
	}

	public ExcelDto(String text, String color) {
		this.text = text;
		this.color = color;
	}
}

读取跟设置颜色

public class ExcelUtils {

	private static final String EXCEL_XLS= ".xls";
	private static final String EXCEL_XLSX= ".xlsx";
	private static Pattern pattern = Pattern.compile("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");

	public static Map<String, List<List<ExcelDto>>> analyzeExcel(InputStream is, String fileType) throws Exception {
		try {
			Workbook wb = null;
			if (EXCEL_XLS.equals(fileType)) {
				wb = new HSSFWorkbook(is);
			} else if (EXCEL_XLSX.equals(fileType)) {
				wb = new XSSFWorkbook(is);
			} else {
				throw new ServiceException("文件格式錯誤");
			}
			Map<String, List<List<ExcelDto>>> sheets = new HashMap(wb.getNumberOfSheets());
			for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); ++sheetNum) {
				//读取sheet页
				Sheet sheet=wb.getSheetAt(sheetNum);
				if (sheet != null) {
					// 当前sheet页名
					String sheetName = sheet.getSheetName();
					List<List<ExcelDto>> data = new ArrayList<>();
					// 遍历行
					for(int i=0;i<=sheet.getLastRowNum();i++){
						//获得行
						Row row=sheet.getRow(i);
						List<ExcelDto> colData = new ArrayList<>();
						// 遍历列
						if (row != null) {
							for(int j=0;j<row.getLastCellNum();j++){
								//获取单元格
								Cell cell=row.getCell(j);
								colData.add(getCellValue(cell, fileType));
							}
							data.add(colData);
						}

					}
					sheets.put(sheetName,data);
				}
			}
			return sheets;
		} catch (Exception e){
			throw e;
		}
	}

	private static ExcelDto getCellValue(Cell cell, String fileType) {
		ExcelDto dto = new ExcelDto();
		if (cell == null) {
			return dto;
		} else {
			switch (cell.getCellType()) {
				case NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd", LocaleUtil.getUserLocale());
						sdf.setTimeZone(LocaleUtil.getUserTimeZone());
						dto.setText(sdf.format(cell.getDateCellValue()));
					} else {
						DataFormatter formatter = new DataFormatter();
						dto.setText(formatter.formatCellValue(cell));
					}
					break;
				case STRING:
					dto.setText(cell.getStringCellValue());
					break;
				case BOOLEAN:
					dto.setText(String.valueOf(cell.getBooleanCellValue()));
					break;
				case FORMULA:
					dto.setText(cell.getCellFormula());
					break;
				case BLANK:
					dto.setText("");
					break;
				case ERROR:
					dto.setText(ErrorEval.getText(cell.getErrorCellValue()));
					break;
				default:
					dto.setText("Unknown Cell Type: " + cell.getCellType());
			}
			if (EXCEL_XLS.equals(fileType)) {
				CellStyle cellStyle = cell.getCellStyle();
				HSSFColor hssfColor = (HSSFColor) cellStyle.getFillForegroundColorColor();
				if (hssfColor != null) {
					String color = hssfColor.getHexString();
					dto.setColor(color);
				}
			} else if (EXCEL_XLSX.equals(fileType)) {
				// xlsx 07版
				CellStyle cellStyle = cell.getCellStyle();
				XSSFColor xssfColor = (XSSFColor) cellStyle.getFillForegroundColorColor();
				byte[] bytes;
				if (xssfColor != null) {
					bytes = xssfColor.getRGB();
					dto.setColor(String.format("#%02X%02X%02X", bytes[0], bytes[1], bytes[2]));
				}
			}
			return dto;
		}
	}

	public static Workbook getWorkBook(Map<String, List<List<ExcelDto>>> sheets) throws IOException {

		XSSFWorkbook workbook = new XSSFWorkbook();
		for (String sheetName:sheets.keySet()) {
			List<List<ExcelDto>> list = sheets.get(sheetName);
			XSSFSheet sheet = workbook.createSheet(sheetName);

			for(int rowIndex = 0; rowIndex < list.size(); ++rowIndex) {
				List<ExcelDto> line = list.get(rowIndex);
				XSSFRow row = sheet.createRow(rowIndex);
				for(int cellIndex = 0; cellIndex < line.size(); ++cellIndex) {
					ExcelDto cellData = line.get(cellIndex);
					XSSFCell cell = row.createCell(cellIndex);
					cell.setCellValue(cellData.getText());
					if (StringUtils.isNotBlank(cellData.getColor())) {
						XSSFCellStyle cellStyle = workbook.createCellStyle();

						//设置前景填充样式
						cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
						byte[] rgb = new byte[3];
						rgb[0] = (byte) Integer.valueOf(cellData.getColor().substring(1,3),16).intValue();
						rgb[1] = (byte) Integer.valueOf(cellData.getColor().substring(3,5),16).intValue();
						rgb[2] = (byte) Integer.valueOf(cellData.getColor().substring(5),16).intValue();
						XSSFColor color = new XSSFColor(rgb, new DefaultIndexedColorMap());

						cellStyle.setFillForegroundColor(color);
						cell.setCellStyle(cellStyle);
					}

				}
			}
		}

		return workbook;
	}
}