How to Add negative value for DataBar in apache poi?

Question

i used this code to create a databar :

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

 }
}

And i fond it on the question provided Hear

And it is working !

However i tried to modify it to add negative value Like This :

but i did not fond any Class Or Object To do this !

is this even possible ?

Answer 1

This is a similar problem as this one: How to make solid color for databar in apache.poi.

The settings for negative values in data bars were not available when Office Open XML was published. It is a later added feature. Since apache poi uses the published features of Office Open XML only, this feature is not available directly. Also it is not available using the underlying org.openxmlformats.schemas.spreadsheetml.x2006.main.* classes as those also only are created from published Office Open XML.

How to see this? All Office Open XML files are ZIP archives. One can unzip them and have a look at the XML. So we set data bar settings as needed in Excel and save the *.xlsx file. Then we unzip the *.xlsx file and have a look at /xl/worksheets/sheet1.xml. There we find the settings for conditional formatting.

So we only can solve this problem the same way as in the linked answer above.

Complete example:

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 colorPos, ExtendedColor colorNeg) throws Exception {
  CellRangeAddress[] regions = { CellRangeAddress.valueOf(region) };
  ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(colorPos);
  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);
  }
  
  // use extension from x14 namespace to set data bars having setting for negative value
  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 + "\" border=\"1\" negativeBarBorderColorSameAsPositive=\"0\">"
    + "<x14:cfvo type=\"min\"/>"
    + "<x14:cfvo type=\"max\"/>"
    + "<x14:borderColor rgb=\"" + colorPos.getARGBHex() + "\"/>"
    + "<x14:negativeFillColor rgb=\"" + colorNeg.getARGBHex() + "\"/>"
    + "<x14:negativeBorderColor rgb=\"" + colorNeg.getARGBHex() + "\"/>"
    + "<x14:axisColor theme=\"1\"/>"
    + "</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");

  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 colorPos = workbook.getCreationHelper().createExtendedColor();
  colorPos.setARGBHex("FF80C279");
  ExtendedColor colorNeg = workbook.getCreationHelper().createExtendedColor();
  colorNeg.setARGBHex("FFFF0000");
  applyDataBars(sheetCF, "B2:B12", colorPos, colorNeg);

  sheet.setColumnWidth(1, 50*256);

  FileOutputStream out = new FileOutputStream("ConditionalFormattingDataBars.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();

 }
}

This code will only work properly always for new created XSSFWorkbook. If the XSSFWorkbook was created from an existing workbook, the this could contain org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExtensionList for x14 extensions already. If so, then these must be taken into account. But that would be a much more complex and challenging project.