How to eliminate zero value data bar in POI XSSFWorkbook

Question

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.

Axel Richter Axel Richter · Accepted Answer · 2019-02-19T16:57:19

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:

How to eliminate zero value data bar in POI XSSFWorkbook

1 Answers