Hi,
I appreciate charting is not yet POI functionality, and my questions are
most likely answered in the archives. I understand that if I had a template
workbook many of these issues might go away by "simply" importing the
template into my new workbook, unfortunately, not only am I unsure how this
is achieved, the workbook I'm working on has a data driven number of
worksheets that I would like the chart added to.
That being said, having already used POI to populate a Sheet with date
values in Column A and numerical data in column C, I think I'm almost there
with the (not very pretty) following:
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
import org.openxmlformats.schemas.drawingml.x2006.main.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.*;
import
org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
[...]
XSSFSheet xmlSheet = (XSSFSheet)sheet;
XSSFDrawing drawing = xmlSheet.createDrawingPatriarch();
CTDrawing ctDrawing = drawing.getCTDrawing();
CTTwoCellAnchor anchor = ctDrawing.addNewTwoCellAnchor();
CTMarker fromMarker = CTMarker.Factory.newInstance();
fromMarker.setCol(8);
fromMarker.setColOff(0);
fromMarker.setRow(1);
fromMarker.setRowOff(0);
anchor.setFrom(fromMarker);
CTMarker toMarker = CTMarker.Factory.newInstance();
toMarker.setCol(18);
toMarker.setColOff(0);
toMarker.setRow(21);
toMarker.setRowOff(0);
anchor.setTo(toMarker);
CTGraphicalObjectFrame graphicFrame = anchor.addNewGraphicFrame();
graphicFrame.setMacro("");
CTGraphicalObjectFrameNonVisual nvGraphic =
graphicFrame.addNewNvGraphicFramePr();
CTNonVisualDrawingProps props = nvGraphic.addNewCNvPr();
props.setId(2);
props.setName("Chart 1");
nvGraphic.addNewCNvGraphicFramePr();
CTTransform2D transform = graphicFrame.addNewXfrm();
CTPositiveSize2D extPoint = transform.addNewExt();
CTPoint2D offPoint = transform.addNewOff();
extPoint.setCx(0);
extPoint.setCy(0);
offPoint.setX(0);
offPoint.setY(0);
CTGraphicalObject graphic = graphicFrame.addNewGraphic();
CTGraphicalObjectData graphicData = graphic.addNewGraphicData();
graphicData.setUri("http://schemas.openxmlformats.org/drawingml/2006/chart";)
;
anchor.addNewClientData();
This creates a drawing.xml file that matches that generated by Excel 2007
except for:
<a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/chart";>
<c:chart xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart";
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships
" r:id="rId1"/>
</a:graphicData>
So first question, how do I get the "<c:chart ...>" entry added to
graphicData? It looks like it's describing a relationship which is
discussed further below the next code block.
Next, I have the following to generate my chart.xml:
CTChartSpace chartSpace = CTChartSpace.Factory.newInstance();
CTTextLanguageID languageId = chartSpace.addNewLang();
languageId.setVal("en-US");
CTChart chart = chartSpace.addNewChart();
CTTitle title = chart.addNewTitle();
title.addNewLayout();
CTPlotArea plotArea = chart.addNewPlotArea();
plotArea.addNewLayout();
CTLineChart lineChart = plotArea.addNewLineChart();
CTGrouping grouping = lineChart.addNewGrouping();
grouping.setVal(STGrouping.STANDARD);
CTLineSer lineSer = lineChart.addNewSer();
lineSer.addNewIdx().setVal(0);
lineSer.addNewOrder().setVal(0);
CTSerTx serTx = lineSer.addNewTx();
CTStrRef strRef = serTx.addNewStrRef();
strRef.setF(sheet.getSheetName + "!$C$1");
CTStrData strCache = strRef.addNewStrCache();
strCache.addNewPtCount().setVal(1);
CTStrVal strVal = strCache.addNewPt();
strVal.setIdx(0);
strVal.setV(sheet.getSheetName() + " Used");
CTAxDataSource cat = lineSer.addNewCat();
CTNumRef numRef = cat.addNewNumRef();
numRef.setF(sheet.getSheetName() + "!$A$2:$A$" + sheet.getLastRowNum() + 1);
CTNumData numCache = numRef.addNewNumCache();
numCache.setFormatCode("mm/dd/yyyy hh:mm:ss");
numCache.addNewPtCount().setVal(sheet.getLastRowNum());
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
CTNumVal pt = numCache.addNewPt();
pt.setIdx(rowNum - 1);
pt.setV("" + sheet.getRow(rowNum).getCell(0).getNumericCellValue());
}
CTNumDataSource val = lineSer.addNewVal();
numRef = val.addNewNumRef();
numRef.setF(sheet.getSheetName() + "!$C$2:$C$" + sheet.getLastRowNum() + 1);
numCache = numRef.addNewNumCache();
numCache.setFormatCode("#,##0");
numCache.addNewPtCount().setVal(sheet.getLastRowNum());
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
CTNumVal pt = numCache.addNewPt();
pt.setIdx(rowNum - 1);
pt.setV("" + sheet.getRow(rowNum).getCell(2).getNumericCellValue());
}
lineChart.addNewMarker().setVal(true);
lineChart.addNewAxId().setVal(193772544);
lineChart.addNewAxId().setVal(255226624);
CTDateAx dateAx = plotArea.addNewDateAx();
dateAx.addNewAxId().setVal(193772544);
dateAx.addNewScaling().addNewOrientation().setVal(STOrientation.MIN_MAX);
dateAx.addNewAxPos().setVal(STAxPos.B);
CTNumFmt numFmt = dateAx.addNewNumFmt();
numFmt.setFormatCode("mm/dd/yyyy hh:mm:ss");
numFmt.setSourceLinked(true);
dateAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
dateAx.addNewCrossAx().setVal(255226624);
dateAx.addNewCrosses().setVal(STCrosses.AUTO_ZERO);
dateAx.addNewAuto().setVal(true);
dateAx.addNewLblOffset().setVal(100);
CTValAx valAx = plotArea.addNewValAx();
valAx.addNewAxId().setVal(255226624);
valAx.addNewScaling().addNewOrientation().setVal(STOrientation.MIN_MAX);
valAx.addNewAxPos().setVal(STAxPos.L);
valAx.addNewMajorGridlines();
numFmt = valAx.addNewNumFmt();
numFmt.setFormatCode("#,##0");
numFmt.setSourceLinked(true);
valAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
valAx.addNewCrossAx().setVal(193772544);
valAx.addNewCrosses().setVal(STCrosses.AUTO_ZERO);
valAx.addNewCrossBetween().setVal(STCrossBetween.BETWEEN);
CTLegend legend = chart.addNewLegend();
legend.addNewLegendPos().setVal(STLegendPos.R);
legend.addNewLayout();
chart.addNewPlotVisOnly().setVal(true);
CTPrintSettings printSettings = chartSpace.addNewPrintSettings();
printSettings.addNewHeaderFooter();
CTPageMargins pageMargins = printSettings.addNewPageMargins();
pageMargins.setB(0.75);
pageMargins.setL(0.70);
pageMargins.setR(0.70);
pageMargins.setT(0.75);
pageMargins.setHeader(0.30);
pageMargins.setFooter(0.30);
printSettings.addNewPageSetup();
[...]
Again, not pretty, but I can see (in Eclipse Debug session) that it creates
xml that closely matches that generated by Excel. However, while I may have
the right xml, how do I get it into the workbook file itself? I think I
need to create a directory node for the chart (xl/charts), and then dump the
xml from the CTChartSpace object into a doc under that node
(xl/charts/chart1.xml), add a relationship to the drawing
(xl/drawings/_rels/drawing1.xml.rels), Excel's looks like:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships
xmlns="http://schemas.openxmlformats.org/package/2006/relationships";>
<Relationship Id="rId1"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ch
art" Target="../charts/chart1.xml"/>
</Relationships>
and possibly add a Part to [Contents_Types].xml, something along the lines
of:
<Override PartName="/xl/charts/chart1.xml"
ContentType="application/vnd.openxmlformats-officedocument.drawingml.chart+x
ml"/>
I'm not sure that the POIXML classes can help me out here but that may be
from my lack of understanding of how to use them.
Any and all suggestions welcome, thanks in advance,
Bobby
