Search in sources :

Example 1 with CTChart

use of org.openxmlformats.schemas.drawingml.x2006.chart.CTChart in project Robot-Scouter by SUPERCILEX.

the class SpreadsheetExporter method buildAverageColumn.

@AddTrace(name = "buildAverageColumn")
private void buildAverageColumn(Sheet sheet, TeamHelper teamHelper) {
    int farthestColumn = 0;
    for (Row row : sheet) {
        int last = row.getLastCellNum();
        if (last > farthestColumn)
            farthestColumn = last;
    }
    Map<Chart, Pair<LineChartData, List<ChartAxis>>> chartData = new HashMap<>();
    Map<Metric<Void>, Chart> chartPool = new HashMap<>();
    Iterator<Row> rowIterator = sheet.rowIterator();
    for (int i = 0; rowIterator.hasNext(); i++) {
        Row row = rowIterator.next();
        Cell cell = row.createCell(farthestColumn);
        if (i == 0) {
            cell.setCellValue(getString(R.string.average));
            cell.setCellStyle(mCache.getColumnHeaderStyle());
            continue;
        }
        Cell first = row.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
        cell.setCellStyle(first.getCellStyle());
        int type = getMetricForScouts(mScouts.get(teamHelper), mCache.getMetricKey(row)).getType();
        String rangeAddress = getCellRangeAddress(first, row.getCell(cell.getColumnIndex() - 1, MissingCellPolicy.CREATE_NULL_AS_BLANK));
        switch(type) {
            case BOOLEAN:
                cell.setCellFormula("COUNTIF(" + rangeAddress + ", TRUE) / COUNTA(" + rangeAddress + ")");
                mCache.setCellFormat(cell, "0.00%");
                break;
            case NUMBER:
                cell.setCellFormula("SUM(" + rangeAddress + ")" + " / " + "COUNT(" + rangeAddress + ")");
                buildTeamChart(row, teamHelper, chartData, chartPool);
                break;
            case STOPWATCH:
                String excludeZeros = "\"<>0\"";
                cell.setCellFormula("IF(COUNTIF(" + rangeAddress + ", " + excludeZeros + ") = 0, 0, AVERAGEIF(" + rangeAddress + ", " + excludeZeros + "))");
                buildTeamChart(row, teamHelper, chartData, chartPool);
                break;
            case LIST:
                sheet.setArrayFormula("INDEX(" + rangeAddress + ", " + "MATCH(" + "MAX(" + "COUNTIF(" + rangeAddress + ", " + rangeAddress + ")" + "), " + "COUNTIF(" + rangeAddress + ", " + rangeAddress + ")" + ", 0))", new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex()));
                break;
            case HEADER:
            case TEXT:
                // Nothing to average
                break;
            default:
                throw new IllegalStateException();
        }
    }
    for (Chart chart : chartData.keySet()) {
        Pair<LineChartData, List<ChartAxis>> data = chartData.get(chart);
        chart.plot(data.first, data.second.toArray(new ChartAxis[data.second.size()]));
        if (chart instanceof XSSFChart) {
            XSSFChart xChart = (XSSFChart) chart;
            CTChart ctChart = xChart.getCTChart();
            CTPlotArea plotArea = ctChart.getPlotArea();
            setChartAxisTitle(plotArea.getValAxArray(0).addNewTitle(), "Values");
            setChartAxisTitle(plotArea.getCatAxArray(0).addNewTitle(), "Scouts");
            String name = getMetricForChart(xChart, chartPool).getName();
            if (!TextUtils.isEmpty(name))
                xChart.setTitle(name);
        }
    }
}
Also used : HashMap(java.util.HashMap) CTPlotArea(org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea) RichTextString(org.apache.poi.ss.usermodel.RichTextString) PreferencesUtilsKt.setShouldShowExportHint(com.supercilex.robotscouter.util.PreferencesUtilsKt.setShouldShowExportHint) PreferencesUtilsKt.shouldShowExportHint(com.supercilex.robotscouter.util.PreferencesUtilsKt.shouldShowExportHint) XSSFChart(org.apache.poi.xssf.usermodel.XSSFChart) CTChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTChart) ChartAxis(org.apache.poi.ss.usermodel.charts.ChartAxis) LineChartData(org.apache.poi.ss.usermodel.charts.LineChartData) Metric(com.supercilex.robotscouter.data.model.Metric) ArrayList(java.util.ArrayList) SpreadsheetUtils.getAdjustedList(com.supercilex.robotscouter.data.client.spreadsheet.SpreadsheetUtils.getAdjustedList) List(java.util.List) Row(org.apache.poi.ss.usermodel.Row) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) SpreadsheetUtils.getCellRangeAddress(com.supercilex.robotscouter.data.client.spreadsheet.SpreadsheetUtils.getCellRangeAddress) Cell(org.apache.poi.ss.usermodel.Cell) SpreadsheetUtils.getStringForCell(com.supercilex.robotscouter.data.client.spreadsheet.SpreadsheetUtils.getStringForCell) XSSFChart(org.apache.poi.xssf.usermodel.XSSFChart) Chart(org.apache.poi.ss.usermodel.Chart) CTChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTChart) SpreadsheetUtils.getMetricForChart(com.supercilex.robotscouter.data.client.spreadsheet.SpreadsheetUtils.getMetricForChart) Pair(android.util.Pair) AddTrace(com.google.firebase.perf.metrics.AddTrace)

Example 2 with CTChart

use of org.openxmlformats.schemas.drawingml.x2006.chart.CTChart in project poi by apache.

the class PieChartDemo method main.

public static void main(String[] args) throws Exception {
    if (args.length < 2) {
        usage();
        return;
    }
    BufferedReader modelReader = new BufferedReader(new FileReader(args[1]));
    XMLSlideShow pptx = null;
    try {
        // first line is chart title
        String chartTitle = modelReader.readLine();
        pptx = new XMLSlideShow(new FileInputStream(args[0]));
        XSLFSlide slide = pptx.getSlides().get(0);
        // find chart in the slide
        XSLFChart chart = null;
        for (POIXMLDocumentPart part : slide.getRelations()) {
            if (part instanceof XSLFChart) {
                chart = (XSLFChart) part;
                break;
            }
        }
        if (chart == null)
            throw new IllegalStateException("chart not found in the template");
        // embedded Excel workbook that holds the chart data
        POIXMLDocumentPart xlsPart = chart.getRelations().get(0);
        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            XSSFSheet sheet = wb.createSheet();
            CTChart ctChart = chart.getCTChart();
            CTPlotArea plotArea = ctChart.getPlotArea();
            CTPieChart pieChart = plotArea.getPieChartArray(0);
            //Pie Chart Series
            CTPieSer ser = pieChart.getSerArray(0);
            // Series Text
            CTSerTx tx = ser.getTx();
            tx.getStrRef().getStrCache().getPtArray(0).setV(chartTitle);
            sheet.createRow(0).createCell(1).setCellValue(chartTitle);
            String titleRef = new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString();
            tx.getStrRef().setF(titleRef);
            // Category Axis Data
            CTAxDataSource cat = ser.getCat();
            CTStrData strData = cat.getStrRef().getStrCache();
            // Values
            CTNumDataSource val = ser.getVal();
            CTNumData numData = val.getNumRef().getNumCache();
            // unset old axis text
            strData.setPtArray(null);
            // unset old values
            numData.setPtArray(null);
            // set model
            int idx = 0;
            int rownum = 1;
            String ln;
            while ((ln = modelReader.readLine()) != null) {
                String[] vals = ln.split("\\s+");
                CTNumVal numVal = numData.addNewPt();
                numVal.setIdx(idx);
                numVal.setV(vals[1]);
                CTStrVal sVal = strData.addNewPt();
                sVal.setIdx(idx);
                sVal.setV(vals[0]);
                idx++;
                XSSFRow row = sheet.createRow(rownum++);
                row.createCell(0).setCellValue(vals[0]);
                row.createCell(1).setCellValue(Double.valueOf(vals[1]));
            }
            numData.getPtCount().setVal(idx);
            strData.getPtCount().setVal(idx);
            String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true);
            val.getNumRef().setF(numDataRange);
            String axisDataRange = new CellRangeAddress(1, rownum - 1, 0, 0).formatAsString(sheet.getSheetName(), true);
            cat.getStrRef().setF(axisDataRange);
            // updated the embedded workbook with the data
            OutputStream xlsOut = xlsPart.getPackagePart().getOutputStream();
            try {
                wb.write(xlsOut);
            } finally {
                xlsOut.close();
            }
            // save the result
            OutputStream out = new FileOutputStream("pie-chart-demo-output.pptx");
            try {
                pptx.write(out);
            } finally {
                out.close();
            }
        } finally {
            wb.close();
        }
    } finally {
        if (pptx != null)
            pptx.close();
        modelReader.close();
    }
}
Also used : CTStrData(org.openxmlformats.schemas.drawingml.x2006.chart.CTStrData) CTNumDataSource(org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource) POIXMLDocumentPart(org.apache.poi.POIXMLDocumentPart) OutputStream(java.io.OutputStream) FileOutputStream(java.io.FileOutputStream) CTPlotArea(org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea) CTSerTx(org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx) CellReference(org.apache.poi.ss.util.CellReference) CTAxDataSource(org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) CTChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTChart) XSSFRow(org.apache.poi.xssf.usermodel.XSSFRow) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) FileReader(java.io.FileReader) CTNumData(org.openxmlformats.schemas.drawingml.x2006.chart.CTNumData) FileInputStream(java.io.FileInputStream) CTPieChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTPieChart) CTPieSer(org.openxmlformats.schemas.drawingml.x2006.chart.CTPieSer) CTStrVal(org.openxmlformats.schemas.drawingml.x2006.chart.CTStrVal) FileOutputStream(java.io.FileOutputStream) BufferedReader(java.io.BufferedReader) CTNumVal(org.openxmlformats.schemas.drawingml.x2006.chart.CTNumVal) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress)

Example 3 with CTChart

use of org.openxmlformats.schemas.drawingml.x2006.chart.CTChart in project ocvn by devgateway.

the class XSSFLineChartData method createNewSerie.

@Override
protected CustomChartSeries createNewSerie(final int id, final int order, final ChartDataSource<?> categories, final ChartDataSource<? extends Number> values) {
    return new AbstractSeries(id, order, categories, values) {

        @Override
        public void addToChart(final XmlObject ctChart) {
            final CTLineChart ctLineChart = (CTLineChart) ctChart;
            final CTLineSer ctLineSer = ctLineChart.addNewSer();
            ctLineSer.addNewIdx().setVal(this.id);
            ctLineSer.addNewOrder().setVal(this.order);
            // No marker symbol on the chart line.
            ctLineSer.addNewMarker().addNewSymbol().setVal(STMarkerStyle.CIRCLE);
            final CTAxDataSource catDS = ctLineSer.addNewCat();
            XSSFChartUtil.buildAxDataSource(catDS, this.categories);
            final CTNumDataSource valueDS = ctLineSer.addNewVal();
            XSSFChartUtil.buildNumDataSource(valueDS, this.values);
            if (isTitleSet()) {
                ctLineSer.setTx(getCTSerTx());
            }
        }
    };
}
Also used : CTLineChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart) CTNumDataSource(org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource) CTLineSer(org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer) XmlObject(org.apache.xmlbeans.XmlObject) CTAxDataSource(org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource)

Example 4 with CTChart

use of org.openxmlformats.schemas.drawingml.x2006.chart.CTChart in project ocvn by devgateway.

the class XSSFPieChartData method createNewSerie.

@Override
protected CustomChartSeries createNewSerie(final int id, final int order, final ChartDataSource<?> categories, final ChartDataSource<? extends Number> values) {
    return new AbstractSeries(id, order, categories, values) {

        @Override
        public void addToChart(final XmlObject ctChart) {
            final CTPieChart ctPieChart = (CTPieChart) ctChart;
            final CTPieSer ctPieSer = ctPieChart.addNewSer();
            ctPieSer.addNewIdx().setVal(this.id);
            ctPieSer.addNewOrder().setVal(this.order);
            final CTAxDataSource catDS = ctPieSer.addNewCat();
            XSSFChartUtil.buildAxDataSource(catDS, this.categories);
            final CTNumDataSource valueDS = ctPieSer.addNewVal();
            XSSFChartUtil.buildNumDataSource(valueDS, this.values);
            if (isTitleSet()) {
                ctPieSer.setTx(getCTSerTx());
            }
        }
    };
}
Also used : CTPieChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTPieChart) CTPieSer(org.openxmlformats.schemas.drawingml.x2006.chart.CTPieSer) CTNumDataSource(org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource) XmlObject(org.apache.xmlbeans.XmlObject) CTAxDataSource(org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource)

Example 5 with CTChart

use of org.openxmlformats.schemas.drawingml.x2006.chart.CTChart in project ocvn by devgateway.

the class XSSFAreaChartData method createNewSerie.

@Override
protected CustomChartSeries createNewSerie(final int id, final int order, final ChartDataSource<?> categories, final ChartDataSource<? extends Number> values) {
    return new AbstractSeries(id, order, categories, values) {

        @Override
        public void addToChart(final XmlObject ctChart) {
            final CTAreaChart ctAreaChart = (CTAreaChart) ctChart;
            final CTAreaSer ctAreaSer = ctAreaChart.addNewSer();
            ctAreaSer.addNewIdx().setVal(this.id);
            ctAreaSer.addNewOrder().setVal(this.order);
            final CTAxDataSource catDS = ctAreaSer.addNewCat();
            XSSFChartUtil.buildAxDataSource(catDS, this.categories);
            final CTNumDataSource valueDS = ctAreaSer.addNewVal();
            XSSFChartUtil.buildNumDataSource(valueDS, this.values);
            if (isTitleSet()) {
                ctAreaSer.setTx(getCTSerTx());
            }
        }
    };
}
Also used : CTNumDataSource(org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource) CTAreaChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTAreaChart) CTAreaSer(org.openxmlformats.schemas.drawingml.x2006.chart.CTAreaSer) XmlObject(org.apache.xmlbeans.XmlObject) CTAxDataSource(org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource)

Aggregations

CTChart (org.openxmlformats.schemas.drawingml.x2006.chart.CTChart)30 XSSFChart (org.apache.poi.xssf.usermodel.XSSFChart)28 Test (org.junit.Test)28 Sheet (org.apache.poi.ss.usermodel.Sheet)27 Workbook (org.apache.poi.ss.usermodel.Workbook)27 XSSFDrawing (org.apache.poi.xssf.usermodel.XSSFDrawing)27 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)27 ByteArrayInputStream (java.io.ByteArrayInputStream)25 LangYearFilterPagingRequest (org.devgateway.ocds.web.rest.controller.request.LangYearFilterPagingRequest)23 CTAxDataSource (org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource)16 CTNumDataSource (org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource)16 XmlObject (org.apache.xmlbeans.XmlObject)12 CTLineChart (org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart)4 CTLineSer (org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer)4 CTPieChart (org.openxmlformats.schemas.drawingml.x2006.chart.CTPieChart)4 CTPieSer (org.openxmlformats.schemas.drawingml.x2006.chart.CTPieSer)4 CellRangeAddress (org.apache.poi.ss.util.CellRangeAddress)3 CTPlotArea (org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea)3 OutputStream (java.io.OutputStream)2 POIXMLDocumentPart (org.apache.poi.POIXMLDocumentPart)2