Hi again,
Should have an outline of a XSSFChartSpace class to post soon, if
anybody is
interested. Managed to get a chart embedded on my Worksheets with a
few
mods to existing XSSF classes and the new one:
added:
public static final XSSFRelation CHARTSPACE = new XSSFRelation(
"application/vnd.openxmlformats-officedocument.drawingml.chart
+xml",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart
",
"/xl/charts/chart#.xml",
XSSFChartSpace.class
);
to XSSFRelation;
added:
public XSSFChartSpace createChartSpacePatriarch() {
int chartSpaceNumber =
getPackagePart
().getPackage().getPartsByContentType(XSSFRelation.CHARTSPACE.
getContentType()).size() + 1;
XSSFChartSpace chartSpace =
(XSSFChartSpace)createRelationship(XSSFRelation.CHARTSPACE,
XSSFFactory.getInstance(), chartSpaceNumber);
return chartSpace;
}
to XSSFDrawing;
Created an org.apache.poi.xssf.usermodel.XSSFChartSpace class,
modeled on
XSSFDrawing.
Changed:
CTGraphicalObjectData graphicData = graphic.addNewGraphicData();
graphicData.setUri("http://schemas.openxmlformats.org/drawingml/2006/chart
")
;
to:
CTGraphicalObjectData graphicData = graphic.addNewGraphicData();
try {
XmlToken xmlToken = XmlToken.Factory.parse("<c:chart
xmlns:c=\"http://schemas.openxmlformats.org/drawingml/2006/chart\";
xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationship
s\" r:id=\"rId1\"/>");
graphicData.set(xmlToken);
} catch (XmlException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
graphicData.setUri("http://schemas.openxmlformats.org/drawingml/2006/chart
")
;
Which I know is ugly, but it does the job.
Changed:
CTChartSpace chartSpace = CTChartSpace.Factory.newInstance();
to:
XSSFChartSpace chartSpace = drawing.createChartSpacePatriarch();
and fixed up any of the previous references to chartspace to become
chartSpace.getCTChartSpace().
That was it, the remainder of the code in my earlier mail was left
virtually
unchanged (there was a minor bug or two).
Cheers,
Bobby
-----Original Message-----
From: Bobby Kent [mailto:[email protected]]
Sent: Friday, October 09, 2009 12:28
To: [email protected]
Subject: Embedding a chart in a XSSFSheet - POI 3.5 FINAL
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
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
