use of org.apache.poi.ss.usermodel.Cell in project spring-framework by spring-projects.
the class XlsViewTests method testXlsxView.
@Test
public void testXlsxView() throws Exception {
View excelView = new AbstractXlsxView() {
@Override
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
Sheet sheet = workbook.createSheet("Test Sheet");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Test Value");
}
};
excelView.render(new HashMap<>(), request, response);
Workbook wb = new XSSFWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
assertEquals("Test Sheet", wb.getSheetName(0));
Sheet sheet = wb.getSheet("Test Sheet");
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
assertEquals("Test Value", cell.getStringCellValue());
}
use of org.apache.poi.ss.usermodel.Cell in project spring-framework by spring-projects.
the class XlsViewTests method testXls.
@Test
public void testXls() throws Exception {
View excelView = new AbstractXlsView() {
@Override
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
Sheet sheet = workbook.createSheet("Test Sheet");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Test Value");
}
};
excelView.render(new HashMap<>(), request, response);
Workbook wb = new HSSFWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
assertEquals("Test Sheet", wb.getSheetName(0));
Sheet sheet = wb.getSheet("Test Sheet");
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
assertEquals("Test Value", cell.getStringCellValue());
}
use of org.apache.poi.ss.usermodel.Cell 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.Cell in project Robot-Scouter by SUPERCILEX.
the class SpreadsheetExporter method buildTeamAveragesSheet.
@AddTrace(name = "buildTeamAveragesSheet")
private void buildTeamAveragesSheet(Sheet averageSheet) {
Workbook workbook = averageSheet.getWorkbook();
Row headerRow = averageSheet.createRow(0);
headerRow.createCell(0);
List<Sheet> scoutSheets = getAdjustedList(workbook);
for (int i = 0; i < scoutSheets.size(); i++) {
Sheet scoutSheet = scoutSheets.get(i);
Row row = averageSheet.createRow(i + 1);
Cell rowHeaderCell = row.createCell(0);
rowHeaderCell.setCellValue(scoutSheet.getSheetName());
rowHeaderCell.setCellStyle(mCache.getRowHeaderStyle());
List<Row> metricsRows = getAdjustedList(scoutSheet);
rowIterator: for (Row averageRow : metricsRows) {
Cell averageCell = averageRow.getCell(averageRow.getLastCellNum() - 1);
Metric<?> keyMetric = mCache.getKeyMetric(averageRow.getCell(0));
if (TextUtils.isEmpty(getStringForCell(averageCell)) || keyMetric.getType() == TEXT) {
continue;
}
for (Cell keyCell : getAdjustedList(headerRow)) {
if (TextUtils.equals(keyMetric.getRef().getKey(), mCache.getMetricKey(keyCell))) {
setAverageFormula(scoutSheet, row.createCell(keyCell.getColumnIndex()), averageCell);
continue rowIterator;
}
}
Cell keyCell = headerRow.createCell(headerRow.getLastCellNum());
keyCell.setCellValue(averageRow.getCell(0).getStringCellValue());
keyCell.setCellStyle(mCache.getColumnHeaderStyle());
mCache.putKeyMetric(keyCell, keyMetric);
setAverageFormula(scoutSheet, row.createCell(keyCell.getColumnIndex()), averageCell);
}
}
buildAverageCharts(averageSheet);
}
use of org.apache.poi.ss.usermodel.Cell in project Robot-Scouter by SUPERCILEX.
the class SpreadsheetExporter method setupRow.
private void setupRow(Row row, TeamHelper teamHelper, Metric metric) {
Cell headerCell = row.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
mCache.putKeyMetric(headerCell, metric);
if (metric.getType() == HEADER) {
headerCell.setCellStyle(mCache.getHeaderMetricRowHeaderStyle());
int numOfScouts = mScouts.get(teamHelper).size();
if (numOfScouts > SINGLE_ITEM) {
int rowNum = row.getRowNum();
row.getSheet().addMergedRegion(new CellRangeAddress(rowNum, rowNum, 1, numOfScouts));
}
} else {
headerCell.setCellStyle(mCache.getRowHeaderStyle());
}
}
Aggregations