POI 条件格式 数据条 SheetConditionalFo

使用 POI 设置单元格条件格式 数据条，效果如图：

image.png

POI版本使用 4.1.2，
代码如下：

package com.example.poidemo.prosesscell;

import org.apache.poi.hssf.record.common.ExtendedColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDataBarFormatting;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @author longzhe
 * @Description 导出带进度条单元格的excel
 * @createTime 2021-12-12 22:37
 */
@RestController()
public class ProcessController {

    @GetMapping("/processCell")
    public void processCell(HttpServletResponse response) throws IOException {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("进度条测试");
        SXSSFRow row = sheet.createRow(0);
        // 单元格0
        SXSSFCell cell = row.createCell(0);
        cell.setCellValue(Double.valueOf("10086.123"));
        // 单元格1
        cell = row.createCell(1);
        cell.setCellValue(Double.valueOf("4386.345"));
        // 单元格2
        cell = row.createCell(2);
        cell.setCellValue(Double.valueOf("8000.82"));

        SheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();
        XSSFColor color = new XSSFColor(IndexedColors.LIGHT_BLUE, new DefaultIndexedColorMap());
        ConditionalFormattingRule rule = formatting.createConditionalFormattingRule(color);
        XSSFDataBarFormatting dataBarFormatting = (XSSFDataBarFormatting) rule.getDataBarFormatting();
        // 以下4行 可以设置具体的最大值最小值 而不是自动根据单元格范围自动取值
//        dataBarFormatting.getMinThreshold().setRangeType(ConditionalFormattingThreshold.RangeType.NUMBER);
//        dataBarFormatting.getMaxThreshold().setRangeType(ConditionalFormattingThreshold.RangeType.NUMBER);
//        dataBarFormatting.getMinThreshold().setValue(Double.valueOf("0"));
//        dataBarFormatting.getMaxThreshold().setValue(Double.valueOf("10086"));

        CellRangeAddress[] range = {
                CellRangeAddress.valueOf("A1:C1")
        };
        formatting.addConditionalFormatting(range,rule);

        response.setHeader("Content-type","application/octet-stream;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=test.xlsx");
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();
    }
}

update 2021-12-15
如果仔细看数据条，会发现当单元格数字大于等于条件格式最大值时，颜色并没有填满整个单元格，而是在右边还缺少10%左右，这是由于XSSFWorkbook引起的。具体可以参考 https://svn.apache.org/viewvc/poi/tags/REL_4_0_1/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataBarFormatting.java?view=markup#l57

解决方式是

        Field _databar = XSSFDataBarFormatting.class.getDeclaredField("_databar");
        _databar.setAccessible(true);
        org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataBar ctDataBar =
                (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataBar)_databar.get(dataBarFormatting);
        ctDataBar.setMinLength(0);
        ctDataBar.setMaxLength(100);

于是完整代码如下：

package com.example.poidemo.prosesscell;

import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.ConditionalFormattingThreshold;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDataBarFormatting;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;

/**
 * @author longzhe
 * @Description 导出带进度条单元格的excel
 * @createTime 2021-12-12 22:37
 */
@RestController
public class ProcessController {

    @GetMapping("/processCell")
    public void processCell(HttpServletResponse response) throws IOException, NoSuchFieldException, IllegalAccessException {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("进度条测试");
        SXSSFRow row = sheet.createRow(0);
        // 单元格0
        SXSSFCell cell = row.createCell(0);
        cell.setCellValue(Double.valueOf("10086"));
        // 单元格1
        cell = row.createCell(1);
        cell.setCellValue(Double.valueOf("4386"));
        // 单元格2
        cell = row.createCell(2);
        cell.setCellValue(Double.valueOf("8000"));

        SheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();
        XSSFColor color = new XSSFColor(IndexedColors.LIGHT_BLUE, new DefaultIndexedColorMap());
        ConditionalFormattingRule rule = formatting.createConditionalFormattingRule(color);
        XSSFDataBarFormatting dataBarFormatting = (XSSFDataBarFormatting) rule.getDataBarFormatting();
        // 以下4行 可以设置具体的最大值最小值 而不是自动根据单元格范围自动取值
        dataBarFormatting.getMinThreshold().setRangeType(ConditionalFormattingThreshold.RangeType.NUMBER);
        dataBarFormatting.getMaxThreshold().setRangeType(ConditionalFormattingThreshold.RangeType.NUMBER);
        dataBarFormatting.getMinThreshold().setValue(Double.valueOf(0));
        dataBarFormatting.getMaxThreshold().setValue(Double.valueOf(10085.9823));

        dataBarFormatting.setWidthMin(0);
        dataBarFormatting.setWidthMax(100);

        if (dataBarFormatting instanceof XSSFDataBarFormatting) {
            Field _databar = XSSFDataBarFormatting.class.getDeclaredField("_databar");
            _databar.setAccessible(true);
            org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataBar ctDataBar =
                    (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataBar)_databar.get(dataBarFormatting);
            ctDataBar.setMinLength(0);
            ctDataBar.setMaxLength(100);
        }

        CellRangeAddress[] range = {
                CellRangeAddress.valueOf("A1:C1")
        };
        formatting.addConditionalFormatting(range,rule);

        response.setHeader("Content-type","application/octet-stream;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=test.xlsx");
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();
    }

}