List of usage examples for org.apache.poi.hssf.util CellReference CellReference
public CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol)
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Internal implementation to add a sunburst chart (actually a doughnut chart) to a slide, based on a template. * @param template the parsed template information. * @param slide the slide to add to.// w ww. j a v a 2 s. c o m * @param anchor optional bounding rectangle to draw onto, in PowerPoint coordinates. * If null, we'll use the bounds from the original template chart. * @param data the sunburst data. * @param shapeId the slide shape ID, should be unique within the slide. * @param relId the relation ID to the chart data. * @throws TemplateLoadException if we can't create the sunburst; most likely due to an invalid template. */ private static void addSunburst(final SlideShowTemplate template, final XSLFSlide slide, final Rectangle2D.Double anchor, final SunburstData data, final int shapeId, final String relId) throws TemplateLoadException { final String[] categories = data.getCategories(); final double[] values = data.getValues(); final String title = data.getTitle(); slide.getXmlObject().getCSld().getSpTree().addNewGraphicFrame() .set(template.getDoughnutChartShapeXML(relId, shapeId, "chart" + shapeId, anchor)); final XSSFWorkbook workbook = new XSSFWorkbook(); final XSSFSheet sheet = workbook.createSheet(); final XSLFChart baseChart = template.getDoughnutChart(); final CTChartSpace chartSpace = (CTChartSpace) baseChart.getCTChartSpace().copy(); final CTChart ctChart = chartSpace.getChart(); final CTPlotArea plotArea = ctChart.getPlotArea(); if (StringUtils.isEmpty(title)) { if (ctChart.getAutoTitleDeleted() != null) { ctChart.getAutoTitleDeleted().setVal(true); } ctChart.unsetTitle(); } final CTDoughnutChart donutChart = plotArea.getDoughnutChartArray(0); final CTPieSer series = donutChart.getSerArray(0); final CTStrRef strRef = series.getTx().getStrRef(); strRef.getStrCache().getPtArray(0).setV(title); sheet.createRow(0).createCell(1).setCellValue(title); strRef.setF(new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString()); final CTStrRef categoryRef = series.getCat().getStrRef(); final CTStrData categoryData = categoryRef.getStrCache(); final CTNumRef numRef = series.getVal().getNumRef(); final CTNumData numericData = numRef.getNumCache(); final String[] fillColors = data.getColors(); final String[] strokeColors = data.getStrokeColors(); final boolean overrideFill = ArrayUtils.isNotEmpty(fillColors); final boolean overrideStroke = ArrayUtils.isNotEmpty(strokeColors); final boolean overrideColors = overrideFill || overrideStroke; final List<CTDPt> dPtList = series.getDPtList(); final CTDPt templatePt = (CTDPt) dPtList.get(0).copy(); if (overrideColors) { dPtList.clear(); final CTShapeProperties spPr = templatePt.getSpPr(); final CTLineProperties ln = spPr.getLn(); // We need to unset any styles on the existing template if (overrideFill) { unsetSpPrFills(spPr); } if (overrideStroke) { unsetLineFills(ln); } } categoryData.setPtArray(null); numericData.setPtArray(null); CTLegend legend = null; final int[] showInLegend = data.getShowInLegend(); int nextLegendToShow = 0, nextLegendToShowIdx = -1; if (showInLegend != null) { // We need to write legendEntry elements to hide the legend for chart series we don't want. // Note this only works in PowerPoint, and not OpenOffice. legend = ctChart.isSetLegend() ? ctChart.getLegend() : ctChart.addNewLegend(); Arrays.sort(showInLegend); nextLegendToShow = showInLegend[++nextLegendToShowIdx]; } for (int idx = 0; idx < values.length; ++idx) { final CTStrVal categoryPoint = categoryData.addNewPt(); categoryPoint.setIdx(idx); categoryPoint.setV(categories[idx]); final CTNumVal numericPoint = numericData.addNewPt(); numericPoint.setIdx(idx); numericPoint.setV(Double.toString(values[idx])); if (overrideColors) { final CTDPt copiedPt = (CTDPt) templatePt.copy(); copiedPt.getIdx().setVal(idx); if (overrideFill) { final Color color = Color.decode(fillColors[idx % fillColors.length]); final CTSolidColorFillProperties fillClr = copiedPt.getSpPr().addNewSolidFill(); fillClr.addNewSrgbClr().setVal( new byte[] { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() }); } if (overrideStroke) { final Color strokeColor = Color.decode(strokeColors[idx % strokeColors.length]); final CTSolidColorFillProperties strokeClr = copiedPt.getSpPr().getLn().addNewSolidFill(); strokeClr.addNewSrgbClr().setVal(new byte[] { (byte) strokeColor.getRed(), (byte) strokeColor.getGreen(), (byte) strokeColor.getBlue() }); } dPtList.add(copiedPt); } if (legend != null) { // We're hiding some legend elements. Should we show this index? if (nextLegendToShow == idx) { // We show this index, find the next one to show. ++nextLegendToShowIdx; if (nextLegendToShowIdx < showInLegend.length) { nextLegendToShow = showInLegend[nextLegendToShowIdx]; } } else { // We hide this index. If there's already a matching legend entry in the XML, update it, // otherwise we create a new legend entry. boolean found = false; for (int ii = 0, max = legend.sizeOfLegendEntryArray(); ii < max; ++ii) { final CTLegendEntry legendEntry = legend.getLegendEntryArray(ii); final CTUnsignedInt idxLegend = legendEntry.getIdx(); if (idxLegend != null && idxLegend.getVal() == idx) { found = true; if (legendEntry.isSetDelete()) { legendEntry.getDelete().setVal(true); } else { legendEntry.addNewDelete().setVal(true); } } } if (!found) { final CTLegendEntry idxLegend = legend.addNewLegendEntry(); idxLegend.addNewIdx().setVal(idx); idxLegend.addNewDelete().setVal(true); } } } XSSFRow row = sheet.createRow(idx + 1); row.createCell(0).setCellValue(categories[idx]); row.createCell(1).setCellValue(values[idx]); } categoryData.getPtCount().setVal(categories.length); numericData.getPtCount().setVal(values.length); categoryRef.setF(new CellRangeAddress(1, values.length, 0, 0).formatAsString(sheet.getSheetName(), true)); numRef.setF(new CellRangeAddress(1, values.length, 1, 1).formatAsString(sheet.getSheetName(), true)); try { writeChart(template.getSlideShow(), slide, baseChart, chartSpace, workbook, relId); } catch (IOException | InvalidFormatException e) { throw new TemplateLoadException("Error writing chart in loaded template", e); } }
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Utility function to update a scatterplot line's data series. * @param data the datagraph data.//from w ww .java 2 s .c o m * @param sheet the Excel sheet which contains corresponding data from the scatterplot data series. * @param seriesIdx the index of the data in the dategraph data. * @param series the XML object representing the series in the chart. */ private static void updateCTScatterSer(final DategraphData data, final XSSFSheet sheet, final int seriesIdx, final CTScatterSer series) { final String sheetName = sheet.getSheetName(); // the series idx starts from 0 final DategraphData.Row row = data.getRows().get(seriesIdx); final String title = row.getLabel(); final Color color = Color.decode(row.getColor()); series.getOrder().setVal(seriesIdx); series.getIdx().setVal(seriesIdx); final CTSolidColorFillProperties fill = series.getSpPr().getLn().getSolidFill(); // We have to set any possible colour type, PowerPoint throws an error if there's multiple fills, and we don't // know what colour type the user may have used in their template slide. if (fill.getSchemeClr() != null) { fill.unsetSchemeClr(); } if (fill.getSrgbClr() != null) { fill.unsetSrgbClr(); } if (fill.getHslClr() != null) { fill.unsetHslClr(); } if (fill.getPrstClr() != null) { fill.unsetPrstClr(); } if (fill.getScrgbClr() != null) { fill.unsetScrgbClr(); } if (fill.getSysClr() != null) { fill.unsetSysClr(); } final CTSRgbColor fillClr = fill.addNewSrgbClr(); final byte[] colorBytes = { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() }; fillClr.setVal(colorBytes); final CTMarker marker = series.getMarker(); if (marker != null) { final CTShapeProperties markerSpPr = marker.getSpPr(); unsetSpPrFills(markerSpPr); markerSpPr.addNewSolidFill().addNewSrgbClr().setVal(colorBytes); final CTLineProperties markerLn = markerSpPr.getLn(); if (markerLn != null) { unsetLineFills(markerLn); markerLn.addNewSolidFill().addNewSrgbClr().setVal(colorBytes); } } final CTStrRef strRef = series.getTx().getStrRef(); strRef.getStrCache().getPtArray()[0].setV(title); strRef.setF(new CellReference(sheetName, 0, seriesIdx + 1, true, true).formatAsString()); final long[] timestamps = data.getTimestamps(); { final CTNumRef timestampCatNumRef = series.getXVal().getNumRef(); timestampCatNumRef.setF(new AreaReference(new CellReference(sheetName, 1, 0, true, true), new CellReference(sheetName, 1 + timestamps.length, 0, true, true)).formatAsString()); final CTNumData timeStampCatNumCache = timestampCatNumRef.getNumCache(); timeStampCatNumCache.getPtCount().setVal(timestamps.length); timeStampCatNumCache.setPtArray(null); for (int ii = 0; ii < timestamps.length; ++ii) { final CTNumVal pt = timeStampCatNumCache.addNewPt(); pt.setIdx(ii); pt.setV(sheet.getRow(1 + ii).getCell(0).getRawValue()); } } { final double[] seriesData = row.getValues(); final CTNumRef valuesNumRef = series.getYVal().getNumRef(); valuesNumRef.setF(new AreaReference(new CellReference(sheetName, 1, seriesIdx + 1, true, true), new CellReference(sheetName, 1 + timestamps.length, seriesIdx + 1, true, true)) .formatAsString()); final CTNumData valuesNumCache = valuesNumRef.getNumCache(); valuesNumCache.getPtCount().setVal(timestamps.length); valuesNumCache.setPtArray(null); for (int ii = 0; ii < timestamps.length; ++ii) { final CTNumVal pt = valuesNumCache.addNewPt(); pt.setIdx(ii); pt.setV(Double.toString(seriesData[ii])); } } }
From source file:org.drools.informer.load.spreadsheet.CellIdentifier.java
License:Apache License
public CellIdentifier(String sheetName, int row, int column) { super();/* www. j a v a 2s . c om*/ this.rowIdentifier = row; this.columnIdentifier = column; this.cellRef = new CellReference(sheetName, row, column, false, false); }