POI 條件格式 數(shù)據(jù)條 SheetConditionalFormatting DataBarFormatting

使用 POI 設(shè)置單元格條件格式 數(shù)據(jù)條，效果如圖：


image.png

POI版本使用 4.1.2，

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

代碼如下：

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 導(dǎo)出帶進(jìn)度條單元格的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("進(jìn)度條測試");
        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行 可以設(shè)置具體的最大值最小值 而不是自動(dòng)根據(jù)單元格范圍自動(dòng)取值
//        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
如果仔細(xì)看數(shù)據(jù)條，會(huì)發(fā)現(xiàn)當(dāng)單元格數(shù)字大于等于條件格式最大值時(shí)，顏色并沒有填滿整個(gè)單元格，而是在右邊還缺少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 導(dǎo)出帶進(jìn)度條單元格的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("進(jìn)度條測試");
        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行 可以設(shè)置具體的最大值最小值 而不是自動(dòng)根據(jù)單元格范圍自動(dòng)取值
        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();
    }

}
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容