java - 如何在 apache.poi 中为数据栏制作纯色
问题描述
我正在尝试创建一个带有进度条的工作表,它将代表一些进展。
我正在使用这些库:
org.apache.poi:poi:4.1.0
org.apache.poi:poi-ooxml:4.1.0
org.apache.poi:poi-ooxml-schemas:4.1.0
我得到的只是一个带有渐变的进度条,但我需要一个带有纯色而不是色阶的进度条。
解决方案
中定义的所有条件格式数据条Office Open XML
都使用渐变色。甚至没有属性或属性可以改变它。更高Excel
版本使用来自 namespace 的扩展x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"
。但这些不是Ecma Office Open XML File Formats Standard
.
Apache poi
直到现在只基于Ecma Office Open XML File Formats Standard
. 因此,为条件格式数据栏制作纯色的唯一方法是从头开始apache poi
创建XML
扩展数据栏条件格式。x14
完整示例:
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFDataBarFormatting;
import org.apache.poi.xssf.usermodel.XSSFConditionalFormattingRule;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
public class ConditionalFormattingDataBars {
public static void applyDataBars(SheetConditionalFormatting sheetCF, String region, ExtendedColor color) throws Exception {
CellRangeAddress[] regions = { CellRangeAddress.valueOf(region) };
ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(color);
DataBarFormatting dbf = rule.getDataBarFormatting();
dbf.getMinThreshold().setRangeType(ConditionalFormattingThreshold.RangeType.NUMBER);
dbf.getMinThreshold().setValue(0d);
dbf.getMaxThreshold().setRangeType(ConditionalFormattingThreshold.RangeType.NUMBER);
dbf.getMaxThreshold().setValue(100d);
dbf.setIconOnly(true);
dbf.setWidthMin(0); //cannot work for XSSFDataBarFormatting, see https://svn.apache.org/viewvc/poi/tags/REL_4_0_1/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataBarFormatting.java?view=markup#l57
dbf.setWidthMax(100); //cannot work for XSSFDataBarFormatting, see https://svn.apache.org/viewvc/poi/tags/REL_4_0_1/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataBarFormatting.java?view=markup#l64
if (dbf 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(dbf);
ctDataBar.setMinLength(0);
ctDataBar.setMaxLength(100);
}
// use extension from x14 namespace to set data bars not using gradient color
if (rule instanceof XSSFConditionalFormattingRule) {
Field _cfRule = XSSFConditionalFormattingRule.class.getDeclaredField("_cfRule");
_cfRule.setAccessible(true);
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule ctRule =
(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule)_cfRule.get(rule);
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExtensionList extList =
ctRule.addNewExtLst();
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExtension ext = extList.addNewExt();
String extXML =
"<x14:id"
+ " xmlns:x14=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/main\">"
+ "{00000000-000E-0000-0000-000001000000}"
+ "</x14:id>";
org.apache.xmlbeans.XmlObject xlmObject = org.apache.xmlbeans.XmlObject.Factory.parse(extXML);
ext.set(xlmObject);
ext.setUri("{B025F937-C7B1-47D3-B67F-A62EFF666E3E}");
Field _sh = XSSFConditionalFormattingRule.class.getDeclaredField("_sh");
_sh.setAccessible(true);
XSSFSheet sheet = (XSSFSheet)_sh.get(rule);
extList = sheet.getCTWorksheet().addNewExtLst();
ext = extList.addNewExt();
extXML =
"<x14:conditionalFormattings xmlns:x14=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/main\">"
+ "<x14:conditionalFormatting xmlns:xm=\"http://schemas.microsoft.com/office/excel/2006/main\">"
+ "<x14:cfRule type=\"dataBar\" id=\"{00000000-000E-0000-0000-000001000000}\">"
+ "<x14:dataBar minLength=\"" + 0 + "\" maxLength=\"" + 100 + "\" gradient=\"" + false + "\">"
+ "<x14:cfvo type=\"num\"><xm:f>" + 0 + "</xm:f></x14:cfvo>"
+ "<x14:cfvo type=\"num\"><xm:f>" + 100 + "</xm:f></x14:cfvo>"
+ "</x14:dataBar>"
+ "</x14:cfRule>"
+ "<xm:sqref>" + region + "</xm:sqref>"
+ "</x14:conditionalFormatting>"
+ "</x14:conditionalFormattings>";
xlmObject = org.apache.xmlbeans.XmlObject.Factory.parse(extXML);
ext.set(xlmObject);
ext.setUri("{78C0D931-6437-407d-A8EE-F0AAD7539E65}");
}
sheetCF.addConditionalFormatting(regions, rule);
}
public static void main(String[] args) throws Exception {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("new sheet");
double[] list = new double[]{0d, 10d, 20d, 30d, 40d, 50d, 60d, 70d, 80d, 90d, 100d};
for (int i = 0; i < list.length; i++) {
sheet.createRow(i+1).createCell(0).setCellValue(0d);
sheet.getRow(i+1).createCell(1).setCellValue(list[i]);
sheet.getRow(i+1).createCell(2).setCellValue(100d);
}
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
ExtendedColor color = workbook.getCreationHelper().createExtendedColor();
color.setARGBHex("FF80C279");
applyDataBars(sheetCF, "B2:B12", color);
sheet.setColumnWidth(1, 50*256);
FileOutputStream out = new FileOutputStream("ConditionalFormattingDataBars.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
}
此代码仅适用于新创建的XSSFWorkbook
. 如果XSSFWorkbook
是从现有工作簿创建的,则 this 可能已经包含org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExtensionList
扩展x14
。如果是这样,则必须考虑这些。但这将是一个更加复杂和具有挑战性的项目。
推荐阅读
- python - 如何为基于 CLI 的应用程序编写可安装的 python 包
- ansible - ansible重新启动机器时如何向用户显示消息
- java - 如何配置 DBeaver 以显示我的架构?
- python - 对相似类型的测试进行参数化
- javascript - 访问 locaStorage 中的另一个环境
- design-patterns - 以数据库为中心的架构与疲惫的架构
- csv - gocsv UnmarshalBytes 失败
- cluster-analysis - 使用节点的属性进行聚类
- angular - 冻结列并用 PrimeNG 包装 - 行高混杂
- javascript - 如何在three.js中将3D obj文件转换为粒子