Hutool如何给Excel按行设置背景颜色

墨蓝 2023-10-09 21:34 41阅读 0赞

> 在利用hutool根据某列给Excel设置 背景色时，你可能会遇见，设置背景色不生效或者后面填充的背景色会覆盖前面填充的背景色。下面给出一个已经实现了根据某列条件设置某行背景颜色的案例代码。

**目录**

1、案例测试实体类

2、案例测试主方法

3、案例导出Excel效果展示

--------------------

## 1、案例测试实体类 ##

package com.ruoyi.cms.test;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    /**
     * @author Roc-xb
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class SapTaxResultTest {
    
        /**
         * 凭证编号
         */
        private String sapCretNo;
        /**
         * 用户名称
         */
        private String sapUserName;
        /**
         * 本币金额
         */
        private String sapAmount;
        /**
         * 发票号码
         */
        private String sapInvoiceNo;
    
        private String resultFlag;
    
    }

## 2、案例测试主方法 ##

package com.ruoyi.cms.test;
    
    import cn.hutool.core.collection.CollUtil;
    import cn.hutool.core.util.RandomUtil;
    import cn.hutool.poi.excel.BigExcelWriter;
    import cn.hutool.poi.excel.ExcelUtil;
    import cn.hutool.poi.excel.ExcelWriter;
    import org.apache.poi.ss.usermodel.*;
    
    import java.util.List;
    
    /**
     * @author Roc-xb
     */
    public class DemoTest {
    
        /**
         * 自定义设置单元格样式
         *
         * @param writer hutool-Excel写入器
         * @param x      x_坐标
         * @param y      y_坐标
         * @param index  背景色
         */
        private static void setCellStyle(ExcelWriter writer, int x, int y, short index) {
            CellStyle cellStyle = writer.createCellStyle(x, y);
            // 填充背景色
            cellStyle.setFillForegroundColor(index);
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 解决填充背景色没有边框问题
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
    
        }
    
    
        public static void main(String[] args) {
            // 构造测试数据
            SapTaxResultTest sapTaxResultTest1 = new SapTaxResultTest("12321432", "发票1", "562.45", "3412354", "1");
            SapTaxResultTest sapTaxResultTest2 = new SapTaxResultTest("12321432", "发票2", "562.45", "3412354", "2");
            SapTaxResultTest sapTaxResultTest3 = new SapTaxResultTest("12321432", "发票3", "562.45", "3412354", "1");
            SapTaxResultTest sapTaxResultTest4 = new SapTaxResultTest("12321432", "发票4", "562.45", "3412354", "1");
            SapTaxResultTest sapTaxResultTest5 = new SapTaxResultTest("12321432", "发票5", "562.45", "3412354", "2");
            List<SapTaxResultTest> list = CollUtil.newArrayList(sapTaxResultTest1, sapTaxResultTest2, sapTaxResultTest3, sapTaxResultTest4, sapTaxResultTest5);
            BigExcelWriter writer = ExcelUtil.getBigWriter("C:/Users/Administrator/Desktop/bzbm/test" + RandomUtil.randomNumbers(2) + ".xlsx", "test表");
            // 是否写入表头
            writer.write(list, true);
            // 获取表格有多少列
            int rowSize = writer.getColumnCount();
            // 遍历数据行，设置单元格样式
            for (int i = 1; i <= list.size(); i++) {
                Row row = writer.getOrCreateRow(i);
                // 假设 resultFlag 列的索引为 4
                String resultFlag = row.getCell(4).getStringCellValue();
                if ("1".equals(resultFlag)) {
                    // 设置行背景颜色
                    for (int j = 0; j < rowSize; j++) {
                        setCellStyle(writer, j, i, IndexedColors.RED.getIndex());
                    }
                } else if ("2".equals(resultFlag)) {
                    // 设置行背景颜色
                    for (int j = 0; j < rowSize; j++) {
                        setCellStyle(writer, j, i, IndexedColors.YELLOW.getIndex());
                    }
                }
            }
            // 关闭 writer 以释放内存
            writer.close();
        }
    
    
    }

## 3、案例导出Excel效果展示 ##

![75b3523d528242af9b166dde86af4b96.png][]

[75b3523d528242af9b166dde86af4b96.png]: https://img-blog.csdnimg.cn/75b3523d528242af9b166dde86af4b96.png