I am trying to create a bar chart in excel file using Apache poi and following the code mentioned here: Create Bar Chart in Excel with Apache POI.
In my case, I need to use data values coming from a different source instead of the excel cells. How can I use those values in the BarChart?
Assume that the values will come as an array of float/double values. Also, I need to be able to set Bar colors independently. For example, some bars will be green while some others will be red.
I modified the data portion of Create Bar Chart in Excel with Apache POI code in the following manner:
CTBarSer ctBarSer = ctBarChart.addNewSer();
CTSerTx ctSerTx = ctBarSer.addNewTx();
CTStrRef ctStrRef = ctSerTx.addNewStrRef();
ctStrRef.setF("hardcoded" + r);
ctBarSer.addNewIdx().setVal(r-2);
CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
CTStrData ctStrData = cttAxDataSource.addNewStrLit();
ctStrData.addNewPt().setV("Val1");
ctStrData.addNewPt().setV("Val2");
ctStrData.addNewPt().setV("Val3");
CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
CTNumData ctNumData = ctNumDataSource.addNewNumLit();
CTNumVal ctNumVal = ctNumData.addNewPt();
ctNumVal.setV(String.valueOf(0.92));
ctNumData = ctNumDataSource.addNewNumLit();
ctNumVal = ctNumData.addNewPt();
ctNumVal.setV(String.valueOf(0.95));
ctNumData = ctNumDataSource.addNewNumLit();
ctNumVal = ctNumData.addNewPt();
ctNumVal.setV(String.valueOf(0.98));
Any help is greatly appreciated. Thanks!
When I output the excel file using print, it gives the following result:
<xml-fragment xmlns:char="http://schemas.openxmlformats.org/drawingml/2006/chart" xmlns:main="http://schemas.openxmlformats.org/drawingml/2006/main">
<char:plotArea>
<char:layout/>
<char:barChart>
<char:barDir val="col"/>
<char:varyColors val="false"/>
<char:ser>
<char:idx val="0"/>
<char:tx>
<char:strRef>
<char:f>hardcoded2</char:f>
</char:strRef>
</char:tx>
<char:spPr>
<main:ln>
<main:solidFill>
<main:srgbClr val="000000"/>
</main:solidFill>
</main:ln>
</char:spPr>
<char:cat>
<char:strLit>
<char:pt>
<char:v>Val1</char:v>
</char:pt>
<char:pt>
<char:v>Val2</char:v>
</char:pt>
<char:pt>
<char:v>Val3</char:v>
</char:pt>
</char:strLit>
</char:cat>
<char:val>
<char:numLit>
<char:pt>
<char:v>0.92</char:v>
</char:pt>
</char:numLit>
<char:numLit>
<char:pt>
<char:v>0.95</char:v>
</char:pt>
</char:numLit>
<char:numLit>
<char:pt>
<char:v>0.98</char:v>
</char:pt>
</char:numLit>
</char:val>
</char:ser>
<char:axId val="123456"/>
<char:axId val="123457"/>
</char:barChart>
<char:catAx>
<char:axId val="123456"/>
<char:scaling>
<char:orientation val="minMax"/>
</char:scaling>
<char:delete val="false"/>
<char:axPos val="b"/>
<char:tickLblPos val="nextTo"/>
<char:crossAx val="123457"/>
</char:catAx>
<char:valAx>
<char:axId val="123457"/>
<char:scaling>
<char:orientation val="minMax"/>
</char:scaling>
<char:delete val="false"/>
<char:axPos val="l"/>
<char:tickLblPos val="nextTo"/>
<char:crossAx val="123456"/>
</char:valAx>
</char:plotArea>
<char:plotVisOnly val="true"/>
</xml-fragment>
Main problems with your changings are:
You forgot setting the Idx of the CTStrVal and the CTNumVal, you have added using addNewPt.
CTNumData added to CTNumDataSource with addNewNumLit should contain as much CTNumVals like there are data points in the series. There should not be multiple CTNumDatain one series. With CTStrData in CTAxDataSource you have done it right - except the missing Idx, see 1.
Following code is creating the same chart as the one in the linked answer but without cell references:
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
/*
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
*/
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrData;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumData;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumVal;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrVal;
public class BarChartPlainValues {
public static void main(String[] args) throws Exception {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
/*
cell filing code deleted
*/
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 8, 20);
Chart chart = drawing.createChart(anchor);
CTChart ctChart = ((XSSFChart)chart).getCTChart();
CTPlotArea ctPlotArea = ctChart.getPlotArea();
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
ctBoolean.setVal(true);
ctBarChart.addNewBarDir().setVal(STBarDir.COL);
//new code start
for (int s = 1; s < 5; s++) {
CTBarSer ctBarSer = ctBarChart.addNewSer();
CTSerTx ctSerTx = ctBarSer.addNewTx();
ctSerTx.setV("Serie " + s);
ctBarSer.addNewIdx().setVal(s-1);
CTAxDataSource ctAxDataSource = ctBarSer.addNewCat();
CTStrData ctStrData = ctAxDataSource.addNewStrLit();
ctStrData.addNewPtCount().setVal(3);
CTStrVal ctStrVal = ctStrData.addNewPt();
ctStrVal.setIdx(0);
ctStrVal.setV("HEADER 1");
ctStrVal = ctStrData.addNewPt();
ctStrVal.setIdx(1);
ctStrVal.setV("HEADER 2");
ctStrVal = ctStrData.addNewPt();
ctStrVal.setIdx(2);
ctStrVal.setV("HEADER 3");
CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
CTNumData ctNumData = ctNumDataSource.addNewNumLit();
ctNumData.addNewPtCount().setVal(3);
CTNumVal ctNumVal = ctNumData.addNewPt();
ctNumVal.setIdx(0);
ctNumVal.setV("" + new java.util.Random().nextDouble());
ctNumVal = ctNumData.addNewPt();
ctNumVal.setIdx(1);
ctNumVal.setV("" + new java.util.Random().nextDouble());
ctNumVal = ctNumData.addNewPt();
ctNumVal.setIdx(2);
ctNumVal.setV("" + new java.util.Random().nextDouble());
//at least the border lines in Libreoffice Calc ;-)
ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});
}
//new code end
/*
chart reference code deleted
*/
//telling the BarChart that it has axes and giving them Ids
ctBarChart.addNewAxId().setVal(123456);
ctBarChart.addNewAxId().setVal(123457);
//cat axis
CTCatAx ctCatAx = ctPlotArea.addNewCatAx();
ctCatAx.addNewAxId().setVal(123456); //id of the cat axis
CTScaling ctScaling = ctCatAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctCatAx.addNewDelete().setVal(false);
ctCatAx.addNewAxPos().setVal(STAxPos.B);
ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis
ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
//val axis
CTValAx ctValAx = ctPlotArea.addNewValAx();
ctValAx.addNewAxId().setVal(123457); //id of the val axis
ctScaling = ctValAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctValAx.addNewDelete().setVal(false);
ctValAx.addNewAxPos().setVal(STAxPos.L);
ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
//legend
CTLegend ctLegend = ctChart.addNewLegend();
ctLegend.addNewLegendPos().setVal(STLegendPos.B);
ctLegend.addNewOverlay().setVal(false);
System.out.println(ctChart);
FileOutputStream fileOut = new FileOutputStream("BarChartPlainValues.xlsx");
wb.write(fileOut);
fileOut.close();
}
}
Problem: Normally arrays of string literals like ={"HEADER 1","HEADER 2","HEADER 3"} are not allowed as axis labels. Only references are allowed here. Excel accepts this while rendering the chart but you cannot edit those axis labels in Excel GUI.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments