Java中对POI的单元格设置背景色

矫情吗；* 2023-10-18 17:47 20阅读 0赞

## 场景 ##

SpringBoot中使用POI实现Excel导入到数据库（图文教程已实践）：

[https://blog.csdn.net/BADAO\_LIUMANG\_QIZHI/article/details/88660466 ][https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_88660466]

### 需求 ###

在进行导入时，在导入数据库之前需要进行格式的验证，格式不正确则将单元格背景色设置为红色。

## 实现 ##

### 主要实现代码 ###

CellStyle style =  workbook.createCellStyle();
         style.setFillForegroundColor(IndexedColors.RED.getIndex());
         style.setFillPattern(CellStyle.SOLID_FOREGROUND);
         deliveryTimeCell.setCellStyle(style);

### 示例代码 ###

Workbook workbook = null;
    //获取文件格式
    String fileType = path.substring(path.lastIndexOf(".") + 1, path.length());
    InputStream stream = new FileInputStream(path);
    //如果后缀名为xls，使用HSSF
    if (fileType.equals("xls")) {
          workbook = new HSSFWorkbook(stream);//如果后缀名是xlsx，使用XSSF
       }else if (fileType.equals("xlsx")){
           workbook = new XSSFWorkbook(stream);
       }
    Sheet sheet= workbook.getSheet("sheet1");
    //获取行数
    int rows=sheet.getPhysicalNumberOfRows();
    //获取第二行数据
    Row row2 =sheet.getRow(1);
    if(row2!=null){
    //日期格式加校验
    Cell deliveryTimeCell = row2.getCell(3);
    if(deliveryTimeCell!=null){
    //如果是数值类型
        if(deliveryTimeCell.getCellType()==0){
             if(HSSFDateUtil.isCellDateFormatted(deliveryTimeCell)){
                 //获取送货日期
                  Date deliveryTime =deliveryTimeCell.getDateCellValue();
                  receiveOrder.setDeliveryTime(deliveryTime);
             }else{
        //设置送货时间为红色
        CellStyle style =  workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        deliveryTimeCell.setCellStyle(style);
        isValidatePass=false;
                     }
       }else{
         //设置送货时间为红色
         CellStyle style =  workbook.createCellStyle();
         style.setFillForegroundColor(IndexedColors.RED.getIndex());
         style.setFillPattern(CellStyle.SOLID_FOREGROUND);
         deliveryTimeCell.setCellStyle(style);
         isValidatePass=false;
              }
    }

### 输出设置后的excel ###

FileOutputStream fileOut = new FileOutputStream(path);
                        workbook.write(fileOut);
                        fileOut.close();

### 示例效果 ###

![Image 1][]![20190428164338635.jpg][]

### 颜色参照表 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70][]

[https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_88660466]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/88660466
[Image 1]: 
[20190428164338635.jpg]: https://img-blog.csdnimg.cn/20190428164338635.jpg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190428165120141.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70