如何消除POI XSSFWorkbook中的零值数据条 [英] How to eliminate zero value data bar in POI XSSFWorkbook

问题描述

我创建了一个 XSSFWorkbook，将数据条显示为官方示例.

I create a XSSFWorkbook to have data bars shown as the official example. http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java. My question is why the zero value also shows the bar, how can i eliminate it? Screenshot.

static void dataBars2(XSSFSheet sheet) {

		XSSFFont font = sheet.getWorkbook().createFont();
		font.setFontName("等线 Regular");
		font.setFontHeight(12.0);
		CellStyle cs = sheet.getWorkbook().createCellStyle();
		cs.setDataFormat((short) 10);
		cs.setAlignment(HorizontalAlignment.CENTER);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setLeftBorderColor(IndexedColors.BLACK.index);
		cs.setFont(font);
		CellStyle cs_m = sheet.getWorkbook().createCellStyle();
		cs_m.setDataFormat((short) 3);
		cs_m.setAlignment(HorizontalAlignment.CENTER);
		cs_m.setBorderLeft(BorderStyle.THIN);
		cs_m.setBorderTop(BorderStyle.THIN);
		cs_m.setBorderRight(BorderStyle.THIN);
		cs_m.setBorderBottom(BorderStyle.THIN);
		cs_m.setFont(font);

		CellStyle cs_header = sheet.getWorkbook().createCellStyle();

		cs_header.setBorderLeft(BorderStyle.THIN);
		cs_header.setBorderTop(BorderStyle.THIN);
		cs_header.setBorderRight(BorderStyle.THIN);
		cs_header.setBorderBottom(BorderStyle.THIN);

		XSSFColor color1 = new XSSFColor(new Color(218, 225, 240));
		cs_header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		((XSSFCellStyle) cs_header).setFillForegroundColor(color1);

		cs_header.setAlignment(HorizontalAlignment.CENTER);

		Row r = sheet.createRow(0);
		Cell c00 = r.createCell(0);
		c00.setCellValue("Data Bars");
		c00.setCellStyle(cs_header);
		Cell c01 = r.createCell(1);
		c01.setCellStyle(cs_header);
		c01.setCellValue("Green Positive");

		List<Double> list = Arrays.asList(0.279, 0.252, 0.187, 0.128, 0.078, 0.043, 0.022, 0.012, 0.011, 0.0, 0.0);
		for (int i = 0; i <= 10; i++) {
			r = sheet.createRow(i + 1);
			Cell c0 = r.createCell(0);
			c0.setCellValue(10000 + i);
			c0.setCellStyle(cs_m);
			Cell c = r.createCell(1);
			c.setCellValue(list.get(i));
			c.setCellStyle(cs);
		}

		sheet.setColumnWidth(0, 3000);
		sheet.setColumnWidth(1, 5000);

		XSSFSheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

		ExtendedColor colorA = sheet.getWorkbook().getCreationHelper().createExtendedColor();
		colorA.setARGBHex("FF80C279");
		CellRangeAddress[] regions1 = {CellRangeAddress.valueOf("B2:B12")};
		XSSFConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(colorA);
		XSSFDataBarFormatting db1 = rule1.getDataBarFormatting();
		db1.getMinThreshold().setRangeType(RangeType.MIN);
		db1.getMaxThreshold().setRangeType(RangeType.MAX);
		sheetCF.addConditionalFormatting(regions1, rule1);
	}

PS: The HSSFWorkbook don't show the zero value data bar with the same data.

解决方案

The obvious answer would be setting XSSFDataBarFormatting.setWidthMin to 0. But this cannot work since it is simply not implemented yet. See XSSFDataBarFormatting.java.

So we need doing this using low level underlying class org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataBar.

Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataBarFormatting;

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.MIN);
  dbf.getMaxThreshold().setRangeType(ConditionalFormattingThreshold.RangeType.MAX);

  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);
  }

  sheetCF.addConditionalFormatting(regions, rule);
 }

 public static void main(String[] args) throws Exception {
  Workbook workbook = new XSSFWorkbook();

  Sheet sheet = workbook.createSheet("new sheet");

  java.util.List<Double> list = java.util.Arrays.asList(0.279, 0.252, 0.187, 0.128, 0.078, 0.043, 0.022, 0.012, 0.011, 0.0, 0.0);
  for (int i = 0; i < list.size(); i++) {
   sheet.createRow(i+1).createCell(1).setCellValue(list.get(i));
  }

  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();

 }
}

Result:

这篇关于如何消除POI XSSFWorkbook中的零值数据条的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！