use of org.apache.poi.ss.usermodel.charts.LineChartData 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);
}
}
}
use of org.apache.poi.ss.usermodel.charts.LineChartData in project poi by apache.
the class LineChart method main.
public static void main(String[] args) throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("linechart");
final int NUM_OF_ROWS = 3;
final int NUM_OF_COLUMNS = 10;
// Create a row and put some cells in it. Rows are 0 based.
Row row;
Cell cell;
for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
row = sheet.createRow((short) rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
cell = row.createCell((short) colIndex);
cell.setCellValue(colIndex * (rowIndex + 1));
}
}
Drawing<?> drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
Chart chart = drawing.createChart(anchor);
ChartLegend legend = chart.getOrCreateLegend();
legend.setPosition(LegendPosition.TOP_RIGHT);
LineChartData data = chart.getChartDataFactory().createLineChartData();
// Use a category axis for the bottom axis.
ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
data.addSeries(xs, ys1);
data.addSeries(xs, ys2);
chart.plot(data, bottomAxis, leftAxis);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("ooxml-line-chart.xlsx");
wb.write(fileOut);
fileOut.close();
wb.close();
}
use of org.apache.poi.ss.usermodel.charts.LineChartData in project poi by apache.
the class TestXSSFChartTitle method createWorkbookWithChart.
private Workbook createWorkbookWithChart() {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("linechart");
final int NUM_OF_ROWS = 3;
final int NUM_OF_COLUMNS = 10;
// Create a row and put some cells in it. Rows are 0 based.
Row row;
Cell cell;
for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
row = sheet.createRow((short) rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
cell = row.createCell((short) colIndex);
cell.setCellValue(colIndex * (rowIndex + 1));
}
}
Drawing<?> drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
Chart chart = drawing.createChart(anchor);
ChartLegend legend = chart.getOrCreateLegend();
legend.setPosition(LegendPosition.TOP_RIGHT);
LineChartData data = chart.getChartDataFactory().createLineChartData();
// Use a category axis for the bottom axis.
ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
data.addSeries(xs, ys1);
data.addSeries(xs, ys2);
chart.plot(data, bottomAxis, leftAxis);
return wb;
}
use of org.apache.poi.ss.usermodel.charts.LineChartData in project poi by apache.
the class TestXSSFLineChartData method testOneSeriePlot.
@Test
public void testOneSeriePlot() throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet sheet = new SheetBuilder(wb, plotData).build();
Drawing<?> drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
Chart chart = drawing.createChart(anchor);
ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
LineChartData lineChartData = chart.getChartDataFactory().createLineChartData();
ChartDataSource<String> xs = DataSources.fromStringCellRange(sheet, CellRangeAddress.valueOf("A1:J1"));
ChartDataSource<Number> ys = DataSources.fromNumericCellRange(sheet, CellRangeAddress.valueOf("A2:J2"));
LineChartSeries series = lineChartData.addSeries(xs, ys);
assertNotNull(series);
assertEquals(1, lineChartData.getSeries().size());
assertTrue(lineChartData.getSeries().contains(series));
chart.plot(lineChartData, bottomAxis, leftAxis);
wb.close();
}
use of org.apache.poi.ss.usermodel.charts.LineChartData in project Robot-Scouter by SUPERCILEX.
the class SpreadsheetExporter method buildTeamChart.
@AddTrace(name = "buildTeamChart")
private void buildTeamChart(Row row, TeamHelper teamHelper, Map<Chart, Pair<LineChartData, List<ChartAxis>>> chartData, Map<Metric<Void>, Chart> chartPool) {
if (isUnsupportedDevice())
return;
Sheet sheet = row.getSheet();
int rowNum = row.getRowNum();
int lastDataCellNum = row.getSheet().getRow(0).getLastCellNum() - 2;
Chart chart = null;
Pair<Integer, Metric<Void>> nearestHeader = null;
List<Row> rows = getAdjustedList(row.getSheet());
for (int i = row.getRowNum() - 1; i >= 0; i--) {
Metric metric = getMetricForScouts(mScouts.get(teamHelper), mCache.getMetricKey(rows.get(i)));
if (metric.getType() == HEADER) {
nearestHeader = Pair.create(i, metric);
Chart cachedChart = chartPool.get(metric);
if (cachedChart != null)
chart = cachedChart;
break;
}
}
chartFinder: if (nearestHeader == null) {
for (Chart possibleChart : chartData.keySet()) {
if (possibleChart instanceof XSSFChart) {
XSSFChart xChart = (XSSFChart) possibleChart;
if (xChart.getGraphicFrame().getAnchor().getRow1() == SINGLE_ITEM) {
nearestHeader = Pair.create(0, getMetricForChart(xChart, chartPool));
chart = xChart;
break chartFinder;
}
}
}
nearestHeader = Pair.create(0, new Metric.Header("", null));
}
LineChartData data;
if (chart == null) {
Drawing drawing = sheet.createDrawingPatriarch();
Integer headerIndex = nearestHeader.first + 1;
int startChartIndex = lastDataCellNum + 3;
chart = drawing.createChart(createChartAnchor(drawing, getChartRowIndex(headerIndex, new ArrayList<>(chartData.keySet())), startChartIndex, startChartIndex + 10));
LineChartData lineChartData = chart.getChartDataFactory().createLineChartData();
data = lineChartData;
ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
ChartLegend legend = chart.getOrCreateLegend();
legend.setPosition(LegendPosition.RIGHT);
chartData.put(chart, Pair.create(lineChartData, Arrays.asList(bottomAxis, leftAxis)));
chartPool.put(nearestHeader.second, chart);
} else {
data = chartData.get(chart).first;
}
ChartDataSource<String> categorySource = DataSources.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 1, lastDataCellNum));
data.addSeries(categorySource, DataSources.fromNumericCellRange(sheet, new CellRangeAddress(rowNum, rowNum, 1, lastDataCellNum))).setTitle(row.getCell(0).getStringCellValue());
}
Aggregations