Search in sources :

Example 1 with XSSFChart

use of org.apache.poi.xssf.usermodel.XSSFChart in project Robot-Scouter by SUPERCILEX.

the class SpreadsheetExporter method buildAverageCharts.

@AddTrace(name = "buildAverageCharts")
private void buildAverageCharts(Sheet sheet) {
    if (isUnsupportedDevice())
        return;
    Drawing drawing = sheet.createDrawingPatriarch();
    List<Cell> headerCells = getAdjustedList(sheet.getRow(0));
    for (Cell cell : headerCells) {
        int columnIndex = cell.getColumnIndex();
        String headerName = cell.getStringCellValue();
        ClientAnchor anchor = createChartAnchor(drawing, sheet.getLastRowNum() + 3, columnIndex, columnIndex + 1);
        anchor.setRow2(anchor.getRow2() + 30);
        Chart chart = drawing.createChart(anchor);
        chart.getOrCreateLegend().setPosition(LegendPosition.BOTTOM);
        ChartDataSource<String> categorySource = DataSources.fromArray(new String[] { headerName });
        ScatterChartData data = chart.getChartDataFactory().createScatterChartData();
        List<Row> dataRows = getAdjustedList(sheet);
        for (Row row : dataRows) {
            data.addSerie(categorySource, DataSources.fromNumericCellRange(sheet, new CellRangeAddress(row.getRowNum(), row.getRowNum(), columnIndex, columnIndex))).setTitle(row.getCell(0).getStringCellValue());
        }
        ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
        ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
        leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
        chart.plot(data, bottomAxis, leftAxis);
        if (chart instanceof XSSFChart) {
            CTPlotArea plotArea = ((XSSFChart) chart).getCTChart().getPlotArea();
            setChartAxisTitle(plotArea.getValAxArray(0).addNewTitle(), "Values");
            setChartAxisTitle(plotArea.getCatAxArray(0).addNewTitle(), headerName);
        }
    }
}
Also used : Drawing(org.apache.poi.ss.usermodel.Drawing) CTPlotArea(org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea) RichTextString(org.apache.poi.ss.usermodel.RichTextString) ScatterChartData(org.apache.poi.ss.usermodel.charts.ScatterChartData) PreferencesUtilsKt.setShouldShowExportHint(com.supercilex.robotscouter.util.PreferencesUtilsKt.setShouldShowExportHint) PreferencesUtilsKt.shouldShowExportHint(com.supercilex.robotscouter.util.PreferencesUtilsKt.shouldShowExportHint) XSSFChart(org.apache.poi.xssf.usermodel.XSSFChart) ClientAnchor(org.apache.poi.ss.usermodel.ClientAnchor) ChartAxis(org.apache.poi.ss.usermodel.charts.ChartAxis) ValueAxis(org.apache.poi.ss.usermodel.charts.ValueAxis) 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) AddTrace(com.google.firebase.perf.metrics.AddTrace)

Example 2 with XSSFChart

use of org.apache.poi.xssf.usermodel.XSSFChart 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 3 with XSSFChart

use of org.apache.poi.xssf.usermodel.XSSFChart in project poi by apache.

the class TestXSSFChartTitle method getChartFromWorkbook.

/**
     * Gets the first chart from the named sheet in the workbook.
     */
private XSSFChart getChartFromWorkbook(Workbook wb, String sheetName) {
    Sheet sheet = wb.getSheet(sheetName);
    if (sheet instanceof XSSFSheet) {
        XSSFSheet xsheet = (XSSFSheet) sheet;
        XSSFDrawing drawing = xsheet.getDrawingPatriarch();
        if (drawing != null) {
            List<XSSFChart> charts = drawing.getCharts();
            if (charts != null && charts.size() > 0) {
                return charts.get(0);
            }
        }
    }
    return null;
}
Also used : XSSFChart(org.apache.poi.xssf.usermodel.XSSFChart) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) Sheet(org.apache.poi.ss.usermodel.Sheet) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) XSSFDrawing(org.apache.poi.xssf.usermodel.XSSFDrawing)

Example 4 with XSSFChart

use of org.apache.poi.xssf.usermodel.XSSFChart in project poi by apache.

the class TestXSSFChartTitle method testExistingChartWithFormulaTitle.

@Test
public void testExistingChartWithFormulaTitle() throws IOException {
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("chartTitle_withTitleFormula.xlsx");
    XSSFChart chart = getChartFromWorkbook(wb, "Sheet1");
    assertNotNull(chart);
    XSSFRichTextString originalTitle = chart.getTitleText();
    assertNotNull(originalTitle);
    assertEquals("", originalTitle.toString());
    String formula = chart.getTitleFormula();
    assertNotNull(formula);
    assertEquals("Sheet1!$E$1", formula);
    wb.close();
}
Also used : XSSFChart(org.apache.poi.xssf.usermodel.XSSFChart) XSSFRichTextString(org.apache.poi.xssf.usermodel.XSSFRichTextString) XSSFRichTextString(org.apache.poi.xssf.usermodel.XSSFRichTextString) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Workbook(org.apache.poi.ss.usermodel.Workbook) Test(org.junit.Test)

Example 5 with XSSFChart

use of org.apache.poi.xssf.usermodel.XSSFChart in project poi by apache.

the class XSSFLineChartData method fillChart.

public void fillChart(Chart chart, ChartAxis... axis) {
    if (!(chart instanceof XSSFChart)) {
        throw new IllegalArgumentException("Chart must be instance of XSSFChart");
    }
    XSSFChart xssfChart = (XSSFChart) chart;
    CTPlotArea plotArea = xssfChart.getCTChart().getPlotArea();
    CTLineChart lineChart = plotArea.addNewLineChart();
    lineChart.addNewVaryColors().setVal(false);
    for (Series s : series) {
        s.addToChart(lineChart);
    }
    for (ChartAxis ax : axis) {
        lineChart.addNewAxId().setVal(ax.getId());
    }
}
Also used : XSSFChart(org.apache.poi.xssf.usermodel.XSSFChart) CTLineChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart) LineChartSeries(org.apache.poi.ss.usermodel.charts.LineChartSeries) ChartAxis(org.apache.poi.ss.usermodel.charts.ChartAxis) CTPlotArea(org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea)

Aggregations

XSSFChart (org.apache.poi.xssf.usermodel.XSSFChart)51 Workbook (org.apache.poi.ss.usermodel.Workbook)31 Test (org.junit.Test)31 CTChart (org.openxmlformats.schemas.drawingml.x2006.chart.CTChart)30 Sheet (org.apache.poi.ss.usermodel.Sheet)29 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)29 XSSFDrawing (org.apache.poi.xssf.usermodel.XSSFDrawing)28 ByteArrayInputStream (java.io.ByteArrayInputStream)25 LangYearFilterPagingRequest (org.devgateway.ocds.web.rest.controller.request.LangYearFilterPagingRequest)23 CTPlotArea (org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea)18 ChartAxis (org.apache.poi.ss.usermodel.charts.ChartAxis)17 CustomChartSeries (org.devgateway.toolkit.web.excelcharts.CustomChartSeries)14 CTValAx (org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx)10 CTLineChart (org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart)5 CTCatAx (org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx)4 AddTrace (com.google.firebase.perf.metrics.AddTrace)3 SpreadsheetUtils.getCellRangeAddress (com.supercilex.robotscouter.data.client.spreadsheet.SpreadsheetUtils.getCellRangeAddress)3 SpreadsheetUtils.getMetricForChart (com.supercilex.robotscouter.data.client.spreadsheet.SpreadsheetUtils.getMetricForChart)3 PreferencesUtilsKt.setShouldShowExportHint (com.supercilex.robotscouter.util.PreferencesUtilsKt.setShouldShowExportHint)3 PreferencesUtilsKt.shouldShowExportHint (com.supercilex.robotscouter.util.PreferencesUtilsKt.shouldShowExportHint)3