Easyexcel 设置单元格颜色 ，固定列，样式设置，设置公式处理器。

最新推荐文章于 2024-04-28 10:43:00 发布
最新推荐文章于 2024-04-28 10:43:00 发布
阅读量924 收藏 2
点赞数
文章标签： excel java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45754541/article/details/134313741
版权

在这里插入图片描述

使用Easyexcel导出数据 设置任一单元格颜色 ，固定列，设置样式，设置公式，一个处理器搞定。

使用
 EasyExcel.write(outputStream, MaBillSocialSecurityInfoExcelVo.class)
          .registerWriteHandler(new MaBillFormulaHandler(insurePlaceDigitMap, insurePlaceMap, supplementPayConfigMap))
          .sheet()
          .doWrite(excelVoList);
源码

传进入的三个集合，用于设置公式的取整方式和保留位数。

@Slf4j
public class MaBillFormulaHandler implements RowWriteHandler {

    private final short BLUE_HEAD_1 = covertHSSFColor("#305496");
    private final short BLUE_HEAD_2 = covertHSSFColor("#00B0F0");
    private final short BLUE_BODY_1 = covertHSSFColor("#D9E1F2");
    private final short WHITE = covertHSSFColor("#FFFFFF");
    private final short ORANGE_HEAD = covertHSSFColor("#FFC000");

    private short covertHSSFColor(String color) {
        java.awt.Color color1 = java.awt.Color.decode(color);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFPalette palette = wb.getCustomPalette();
        return palette.findSimilarColor((byte) color1.getRed(),
                (byte) color1.getGreen(), (byte) color1.getBlue()).getIndex();
    }


    private Map<String, MaBillSocialSecurityInsurePlaceDigit> dataMap;
    Map<String, MaBaseProportion> proportionMap;
    Map<Short, CellStyle> cellStyleMap;
    Map<String, MaSocialSecuritySupplementPayConfig> supplementPayConfigMap;

    public MaBillFormulaHandler(Map<String, MaBillSocialSecurityInsurePlaceDigit> dataMap,
                                Map<String,MaBaseProportion> proportionMap,
                                Map<String, MaSocialSecuritySupplementPayConfig> supplementPayConfigMap) {
        this.dataMap = dataMap;
        this.proportionMap = proportionMap;
        this.supplementPayConfigMap = supplementPayConfigMap;
        this.cellStyleMap= new ConcurrentHashMap<>();
    }
    private Map<String,Integer> map = new HashMap<>();

    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        Sheet sheet = writeSheetHolder.getSheet();
        sheet.createFreezePane(8, 0, 8, 0);
        sheet.setAutoFilter(CellRangeAddress.valueOf("1:1"));
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        if (isHead){
            for (int index = row.getFirstCellNum(); index < row.getLastCellNum(); index++) {
                Cell cell = row.getCell(index);
                map.put(cell.getStringCellValue(), cell.getColumnIndex());

                if(index > 66){
                    continue;
                }
                int i = index;
                if (i < 13 || i >= 50) {
                    this.setColor(i, row, BLUE_HEAD_1);
                } else if (i == 15 ||i == 18 || i == 23 || i == 27 || i == 31 || i == 34 ||i == 38 || i == 41|| i == 46 || i == 49 ) {
                    this.setColor(i, row, ORANGE_HEAD);
                } else if (i == 19 || i == 28 || i == 35 || i == 42 || i == 43) {
                    this.setColor(i, row, BLUE_HEAD_2);
                } else {
                    this.setColor(i, row, WHITE);
                }
            }
        }else {
            for (int columnIndex = row.getFirstCellNum(); columnIndex < row.getLastCellNum(); columnIndex++) {
                if(columnIndex > 66){
                    continue;
                }
                if (columnIndex == 4 || columnIndex == 54) {
                    this.setColor(columnIndex, row, ORANGE_HEAD);
                } else if (columnIndex == 19 || columnIndex == 28 || columnIndex == 35 || columnIndex == 42 || columnIndex == 43) {
                    this.setColor(columnIndex, row, BLUE_BODY_1);
                } else {
                    this.setColor(columnIndex, row, WHITE);
                }
            }
            String name = row.getCell(map.get("参保地")).getStringCellValue();
            setIndex(row,"序号");
            if (dataMap.containsKey(name)){
                MaBaseProportion proportion = proportionMap.get(name);
                MaBillSocialSecurityInsurePlaceDigit digit = new MaBillSocialSecurityInsurePlaceDigit();
                if (proportionMap.containsKey(name)){
                    digit = dataMap.get(name);
                }
                BeanMap digitObjectMap = BeanMap.create(digit);
                BeanMap proportionObjectMap = BeanMap.create(proportion);
                setMultiplyFormula1(row,digitObjectMap,"pensionUnit","养老企业汇缴","养老企业基数","养老企业比例");
                setMultiplyFormula1(row,digitObjectMap,"pensionPerson","养老个人汇缴","养老个人基数","养老个人比例");
                setSumFormula(row,digitObjectMap,"pension","养老合计","养老企业汇缴","养老个人汇缴");
                setMultiplyFormula2(row,digitObjectMap,"healthUnit","医疗企业汇缴","医疗企业基数","医疗企业比例","医疗企业大病");
                setMultiplyFormula2(row,digitObjectMap,"healthPerson","医疗个人汇缴","医疗个人基数","医疗个人比例","医疗个人大病");
                setSumFormula(row,digitObjectMap,"health","医疗医疗合计","医疗企业汇缴","医疗个人汇缴");
                if (supplementPayConfigMap.get(name).getIllnessPay() == 1){
                    setIllnessFormula(row,digitObjectMap,"unitIllness",proportionObjectMap,"医疗企业大病","医疗企业基数");
                    setIllnessFormula(row,digitObjectMap,"personIllness",proportionObjectMap,"医疗个人大病","医疗个人基数");
                }
                setMultiplyFormula1(row,digitObjectMap,"unemploymentUnit","失业企业汇缴","失业企业基数","失业企业比例");
                setMultiplyFormula1(row,digitObjectMap,"unemploymentPerson","失业个人汇缴","失业个人基数","失业个人比例");
                setSumFormula(row,digitObjectMap,"unemployment","失业合计","失业企业汇缴","失业个人汇缴");
                setMultiplyFormula1(row,digitObjectMap,"procreateUnit","生育企业汇缴","生育企业基数","生育企业比例");
                setMultiplyFormula1(row,digitObjectMap,"injuryUnit","工伤企业汇缴","工伤企业基数","工伤企业比例");
                setFormula(row,digitObjectMap,"unitAmount","单位社保",new String[]{"养老企业汇缴","医疗企业汇缴","失业企业汇缴","生育企业汇缴","工伤企业汇缴"});
                setFormula(row,digitObjectMap,"personAmount","个人社保",new String[]{"养老个人汇缴","医疗个人汇缴","失业个人汇缴"});
                setSumFormula(row,digitObjectMap,"socialAmount","社保合计","单位社保","个人社保");
                setMultiplyFormula1(row,digitObjectMap,"fundUnit","公积金企业汇缴","公积金企业基数","公积金企业比例");
                setMultiplyFormula1(row,digitObjectMap,"fundPerson","公积金个人汇缴","公积金个人基数","公积金个人比例");
                setSumFormula(row,digitObjectMap,"fundAmount","公积金合计","公积金企业汇缴","公积金个人汇缴");
                if (proportion.getDisabilityBenefitRule().equals(MaSocialSecurityDisabilityBenefitRule.PROPORTIONAL_CHARGE.rule)){
                    setDisabilityBenefitFormula(row,digitObjectMap,proportion,"disability","残保金","养老企业基数");
                }
                setFormula(row,digitObjectMap,"subtotal","小计",new String[]{"社保合计","公积金合计","残保金","采暖费","滞纳金","其它费用"});
                setFormula(row,digitObjectMap,"total","合计",new String[]{"小计","服务费","服务费增值税"});
                setAgeFormula(row,"年龄","身份证号");
                setSexFormula(row,"性别","身份证号");
            }
        }
    }

    private void setColor(Integer index, Row row, short colorIndex) {
        Cell cell = row.getCell(index);
        if(Objects.isNull(cell)){
            return;
        }
        SXSSFSheet sheet = (SXSSFSheet) row.getSheet();
        sheet.trackAllColumnsForAutoSizing();
        sheet.autoSizeColumn(0);
//            sheet.setColumnWidth(index,70);
        CellStyle cellStyle;
        if (cellStyleMap.containsKey(colorIndex)) {
            cellStyle = cellStyleMap.get(colorIndex);
        } else {
            Workbook workbook = sheet.getWorkbook();
            cellStyle = workbook.createCellStyle();
            cellStyleMap.put(colorIndex, cellStyle);
            cellStyle.setFillForegroundColor(colorIndex);
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER.CENTER);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);

            if (colorIndex == BLUE_HEAD_1) {
                Font font = workbook.createFont();
                font.setColor(IndexedColors.WHITE.index);
                cellStyle.setFont(font);
            } else {
                Font font = workbook.createFont();
                font.setColor(IndexedColors.BLACK.index);
                cellStyle.setFont(font);
            }
        }
        if(index == 4 && row.getRowNum() != 0) {
            String name = row.getCell(index).getStringCellValue();
            if (!"补缴".equals(name)) {
                cellStyle = cellStyleMap.get(WHITE);
            }
        }
        row.getCell(index).setCellStyle(cellStyle);
        row.setHeightInPoints(20);
    }
    
    public void setIndex(Row row,String name){
        Cell cell = row.getCell(map.get(name));
        int rowNum = row.getRowNum();
        cell.setCellValue(rowNum);
    }

    public void setMultiplyFormula1(Row row,BeanMap objectMap,String name,String amount,String base,String ratio){
        Cell cell = row.getCell(map.get(amount));
        int rowNum = row.getRowNum()+1;
        String formula = getWay((Integer) objectMap.get(name+"Way"))+"("+getColumnName(map.get(base)+1)+rowNum+"*"+getColumnName(map.get(ratio)+1)+rowNum+","+objectMap.get(name+"Digit")+")";
        cell.setCellFormula(formula);
    }
    public void setMultiplyFormula2(Row row,BeanMap objectMap,String name,String amount,String base,String ratio,String illness){
        Cell cell = row.getCell(map.get(amount));
        int rowNum = row.getRowNum()+1;
        String formula = getWay((Integer) objectMap.get(name+"Way"))+"("+getColumnName(map.get(base)+1)+rowNum+"*"+getColumnName(map.get(ratio)+1)+rowNum+","+objectMap.get(name+"Digit")+")"+"+"+getColumnName(map.get(illness)+1)+rowNum;
        cell.setCellFormula(formula);
    }

    public void setSumFormula(Row row,BeanMap objectMap,String name,String amount,String amount1,String amount2){
        Cell cell = row.getCell(map.get(amount));
        int rowNum = row.getRowNum()+1;
        String formula = getWay((Integer) objectMap.get(name+"Way"))+"("+getColumnName(map.get(amount1)+1)+rowNum+"+"+getColumnName(map.get(amount2)+1)+rowNum+","+objectMap.get(name+"Digit")+")";
        cell.setCellFormula(formula);
    }

    public void setAgeFormula(Row row,String age,String idNumber) {
        Cell cell = row.getCell(map.get(age));
        int rowNum = row.getRowNum() + 1;
        String formula = "DATEDIF(TEXT(MID(" + getColumnName(map.get(idNumber) + 1) + rowNum + ",7,8),\"#-00-00\"),NOW(),\"y\")&\"岁\"&DATEDIF(TEXT(MID(" + getColumnName(map.get(idNumber) + 1) + rowNum + ",7,8),\"#-00-00\"),NOW(),\"ym\")&\"月\"&DATEDIF(TEXT(MID(" + getColumnName(map.get(idNumber) + 1) + rowNum + ",7,8),\"#-00-00\"),NOW(),\"md\")&\"日\"";
        cell.setCellFormula(formula);
    }
    public void setDisabilityBenefitFormula(Row row,BeanMap objectMap,MaBaseProportion proportion , String name,String disabilityBenefit,String pensionUnitBase){
        Cell cell = row.getCell(map.get(disabilityBenefit));
        int rowNum = row.getRowNum()+1;
        String formula = getWay((Integer) objectMap.get(name+"Way"))+"("+getColumnName(map.get(pensionUnitBase)+1)+rowNum+"*"+proportion.getDisabilityBenefit()+","+objectMap.get(name+"Digit")+")";
        cell.setCellFormula(formula);
    }

    public void setIllnessFormula(Row row,BeanMap objectMap,String name,BeanMap proportion,String illness,String base){
        Cell cell = row.getCell(map.get(illness));
        int rowNum = row.getRowNum()+1;
        String ratio = (String) proportion.get(name);
        if (ratio.contains("%")){
            String formula = getWay((Integer) objectMap.get(name+"Way"))+"("+getColumnName(map.get(base)+1)+rowNum+"*"+getHealthIllness(ratio)+","+objectMap.get(name+"Digit")+")";
            cell.setCellFormula(formula);
        }
    }

    public void setSexFormula(Row row,String sex,String idNumber){
        Cell cell = row.getCell(map.get(sex));
        int rowNum = row.getRowNum()+1;
        String formula = "IF(MOD(MID("+getColumnName(map.get(idNumber)+1)+rowNum+",17,1),2),\"男\",\"女\")";
        cell.setCellFormula(formula);
    }
    public void setFormula(Row row,BeanMap objectMap,String name,String amount ,String [] params){
        Cell cell = row.getCell(map.get(amount));
        int rowNum = row.getRowNum()+1;
        StringBuilder formula = new StringBuilder(getWay((Integer) objectMap.get(name+"Way")) + "(");
        for (String s : params) {
            formula.append("+").append(getColumnName(map.get(s) + 1)).append(rowNum);
        }
        formula.append(",").append(objectMap.get(name+"Digit")).append(")");
        cell.setCellFormula(formula.toString());
    }

    public String getWay(Integer way){
        if (way == 1){
            return "ROUNDDOWN";
        }
        if (way == 2){
            return "ROUND";
        }
        if (way == 3){
            return "ROUNDUP";
        }
        return "0";
    }

    public static String getColumnName(int columnIndex) {
        StringBuilder columnNameBuilder = new StringBuilder();

        while (columnIndex > 0) {
            int remainder = columnIndex % 26;
            if (remainder == 0) {
                columnNameBuilder.append('Z');
                columnIndex = (columnIndex / 26) - 1;
            } else {
                columnNameBuilder.append((char) ('A' + remainder - 1));
                columnIndex = columnIndex / 26;
            }
        }

        return columnNameBuilder.reverse().toString();
    }
    private  String getHealthIllness(String healthIllness) {
        //提取数字和百分比的正则表达式模式
        String pattern = "\\d*\\.?\\d*%?";
        String percent = "%";
        // 创建 Pattern 对象
        Pattern regexPattern = Pattern.compile(pattern);
        // 创建 Matcher 对象
        Matcher matcher = regexPattern.matcher(healthIllness);
        // 查找匹配的数字和百分比
        while (matcher.find()) {
            // 提取整个匹配的内容，包括百分比符号
            String match = matcher.group();
            if (isNotBlank(match)) {
                if (match.contains(percent)) {
                    //带百分号，百分比数据
                    return match;
                }
            }
        }
        return "";
    }
}
云想衣裳7
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
easyExcel自定义导出，指定列，设置请求头背景色，加入合计行，设置合计行字体，背景色等等
cf
11-28 3234
2.工具类-自定义样式handler-CustomCellWriteHandler。3.工具类-导出 ExcelUtils。1.引入easyExcel pom。
easyExcel导出合并单元格策略
12-21
easyExcel导出合并单元格策略 WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)) .registerWriteHandler(new CustomCellWriteHandler()) //设置合并单元格策略 .registerWriteHandler(new ...
EasyExcel 批量设置单元格样式（字体样式、背景颜色、边框样式、对齐方式、自动换行）
tiger_Angle
07-14 1万+
EasyExcel 单元格背景颜色、字体颜色使用2种设置颜色方法（IndexedColors中定义的颜色，自定义RGB颜色）实现。EasyExcel 设置字体样式（字体、字体大小、字体颜色、字体加粗、字体斜体、字体下划线、字体上标下标、字体删除线）EasyExcel 设置边框样式（线条类型和线条颜色）（2）有关自定义RGB颜色的使用请查看以下博客。（1）有关字体样式更详细的设置请查看以下博客。（3）有关边框样式更详细的设置请查看以下博客。
Easyexcel3.2.1版本，设置单元格样式，公式，字体颜色，自定义标题
最新发布
m0_47786753的博客
04-28 1999
Easyexcel3.2.1版本，设置单元格样式，公式，字体颜色，自定义标题
EasyExcel：利用模板进行填充字段，生成公式处理，监听单元格填充后触发事件，相同日期单元格合并
yunnuo的博客
12-29 7088
EasyExcel EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 github地址:https://github.com/alibaba/easyexcel 官方文档：https://www.yuque.com/easyexcel/doc/easyexcel 1. 依赖引入 <!-- easyexcel --> <dependency> <grou
Java easyexcel填充Excel（包含公式）
weixin_50272584的博客
05-09 3580
【代码】Java easyexcel填充excel（包含公式）
Easyexcel设置某一行样式
m0_68985723的博客
04-20 794
data.add(List.of("内容4", "内容5", "内容6"));// 第二行数据，将应用样式。data.add(List.of("内容1", "内容2", "内容3"));data.add(List.of("标题1", "标题2", "标题3"));// 创建ExcelWriterBuilder。// 写入数据前，先设置第二行的样式。// 创建ExcelWriter。// 关闭writer，释放内存。// 创建WriteSheet。// 写入数据，应用样式策略。// 写入数据到Excel。
easyExcel给表格的每一列设置不同样式
睡竹的博客
04-22 1万+
easyExcel给表格的每一列设置不同样式
EasyExcel-合并单元格.doc
07-10
EasyExcel-合并单元格技术详解 EasyExcel 是一个功能强大的 Excel 处理库，提供了许多便捷的 API 来处理 Excel 文件。在实际应用中，我们经常需要合并单元格，以便更好地展示数据。在本文中，我们将详细介绍如何...
poi导入/导出Excel表格，合并单元格的读取和设置
03-25
poi导入/导出Excel表格，合并单元格的读取和设置
Excel添加，给指定行插入一行，合并及样式
05-06
Java代码对Excel进行增，删，改。及样式
poi excel 模板读取并导出带公式的excel文档
05-23
poi excel通过模板导出excel表格 且带有公式 导出的excel表格里面的公式均可计算 有简单的公式也有难的公司 在此例子里面都可以跟着excel一起到处并可计算
java设置日期格式
06-05
java语言中设置显示系统时间的各种日期格式，也可以自定义日期格式
【EasyExcel】导出excel并支持自定义设置数据行背景颜色等
欢迎来到三十七度的博客
11-27 6210
1、在 beforeCellCreate 方法中统计出 每个数据行的 name 出现的次数，在 afterCellDispose 方法中处理满足设置背景颜色数据的背景颜色。由于目前系统中多处使用的是file插件导出类，是由统一形成的，直接改动，对其它项目引入，从而导出的性能产生一定的影响，所以选择自定义策略实现，故选1。note：原本考虑的是选择1，但是在实现的时候，统计name出现次数一直会出现问题，在easeExcel的源码中：----------------------
EasyExcel的导入导出,改变单元格样式,导出到不同的sheet
weixin_41040119的博客
06-17 1110
EasyExcel的相关操作
EasyExcel设置不同列不同样式，自定义样式策略
wjs_007的博客
12-20 3824
在 EasyExcel 中提供的样式策略中，HorizontalCellStyleStrategy 是提供的一个水平样式策略，这个是常用的，网上使用的比较多，通过设置构建这个策略对象基本上可以满足一般的要求了，比如：比如设置表头和内容的边框、底色、对齐方式、文字字体、文字颜色、文字大小等。设置完之后，需要创建 HorizontalCellStyleStrategy 对象，然后在导出文件时注册这个策略的 handler 即可。
Easy Excel导出时修改指定单元格字体格式（颜色）
Icutey的博客
04-24 2308
Easy Excel导出时修改指定单元格字体格式（颜色）
js 判断字符串为空和不为空（两种方法）
热门推荐
旭东怪的博客
08-18 6万+
1 判断字符串为空 （1）第一种方法 var test=""; if(test==""||test==null||test==undefined){ alert("为空"); } （2）第二种方法 var test=""; if(!test){ alert("为空"); } 2 判断字符串不为空 （1）第一种方法 var test=""; if(test==""||test==null||test==undefined){ alert("为空"); } .
easyexcel设置单元格
11-09
使用EasyExcel导出excel模板时，我们可以通过以下步骤设置单元格格式： 1. 设置空白单元格的格式为文本： ```java Sheet sheet = workbook.createSheet("sheet1"); CellStyle cellStyle = workbook.createCellStyle(); DataFormat format = workbook.createDataFormat(); cellStyle.setDataFormat(format.getFormat("@")); Cell cell = sheet.createRow(0).createCell(0); cell.setCellStyle(cellStyle); cell.setCellValue(""); ``` 2. 设置含有数据的单元格的格式为文本： ```java Sheet sheet = workbook.createSheet("sheet1"); CellStyle cellStyle = workbook.createCellStyle(); DataFormat format = workbook.createDataFormat(); cellStyle.setDataFormat(format.getFormat("@")); Cell cell = sheet.createRow(0).createCell(0); cell.setCellStyle(cellStyle); cell.setCellValue("data"); ``` 其中，`setDataFormat`方法设置单元格格式为文本格式，`setCellValue`方法设置单元格的值。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值