protected String readStringCell(Cell cell) { return cell.getStringCellValue(); }
import com.monitorjbl.xlsx.StreamingReader; InputStream is = new FileInputStream(new File("/path/to/workbook.xlsx")); StreamingReader reader = StreamingReader.builder() .rowCacheSize(100) // number of rows to keep in memory (defaults to 10) .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024) .sheetIndex(0) // index of sheet to use (defaults to 0) .read(is); // InputStream or File for XLSX file (required) for (Row r : reader) { for (Cell c : r) { System.out.println(c.getStringCellValue()); } }
protected String readStringParameter(Row row, String key) { Cell keyCell = row.getCell(0); if (!key.equals(keyCell.getStringCellValue())) { throw new IllegalArgumentException("The keyCell (" + keyCell.getRow().getRowNum() + "," + keyCell.getColumnIndex() + ") with value (" + keyCell.getStringCellValue() + ") is expected to have the key (" + key + ")"); } Cell valueCell = row.getCell(1); return valueCell.getStringCellValue(); }
Cell cell = sheet.getRow(i).getCell(0); cell.setCellType ( Cell.CELL_TYPE_STRING ); String j_username = cell.getStringCellValue();
InputStream inp = new FileInputStream("wb.xls"); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt([sheet index]); Row row = sheet.getRow([row index]); Cell cell = row.getCell([cell index]); String cellContents = cell.getStringCellValue(); //Modify the cellContents here // Write the output to a file cell.setCellValue(cellContents); FileOutputStream fileOut = new FileOutputStream("wb.xls"); wb.write(fileOut); fileOut.close();
protected void assertCellConstant(Cell cell, String constant) { if (!constant.equals(cell.getStringCellValue())) { throw new IllegalArgumentException("The cell (" + cell.getRow().getRowNum() + "," + cell.getColumnIndex() + ") with value (" + cell.getStringCellValue() + ") is expected to have the constant (" + constant + ")"); } }
protected double readDoubleParameter(Row row, String key) { Cell keyCell = row.getCell(0); if (!key.equals(keyCell.getStringCellValue())) { throw new IllegalArgumentException("The keyCell (" + keyCell.getRow().getRowNum() + "," + keyCell.getColumnIndex() + ") with value (" + keyCell.getStringCellValue() + ") is expected to have the key (" + key + ")"); } Cell valueCell = row.getCell(1); return valueCell.getNumericCellValue(); } }
public boolean isValidValue(Cell cell, DataValidationContext context) { if (! isType(cell, CellType.STRING)) return false; String v = cell.getStringCellValue(); return isValidNumericValue(Double.valueOf(v.length()), context); } },
protected boolean currentRowIsEmpty() { if (currentRow.getPhysicalNumberOfCells() == 0) { return true; } for (Cell cell : currentRow) { if (cell.getCellTypeEnum() == CellType.STRING) { if (!cell.getStringCellValue().isEmpty()) { return false; } } else if (cell.getCellTypeEnum() != CellType.BLANK) { return false; } } return true; }
public Object getValue() { try { switch ( getType() ) { case BOOLEAN_FORMULA: case BOOLEAN: return Boolean.valueOf( cell.getBooleanCellValue() ); case DATE_FORMULA: case DATE: // Timezone conversion needed since POI doesn't support this apparently // long time = cell.getDateCellValue().getTime(); long tzOffset = TimeZone.getDefault().getOffset( time ); return new Date( time + tzOffset ); case NUMBER_FORMULA: case NUMBER: return Double.valueOf( cell.getNumericCellValue() ); case STRING_FORMULA: case LABEL: return cell.getStringCellValue(); case EMPTY: default: return null; } } catch ( Exception e ) { throw new RuntimeException( "Unable to get value of cell (" + cell.getColumnIndex() + ", " + cell.getRowIndex() + ")", e ); } }
break; case Cell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BLANK:
@Test @SuppressWarnings("resource") 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()); }
@Test @SuppressWarnings("resource") 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()); }
@Test @SuppressWarnings("resource") public void testXlsxStreamingView() throws Exception { View excelView = new AbstractXlsxStreamingView() { @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()); }
private void readTimeslotHours() { columnIndexToStartTimeMap.clear(); columnIndexToEndTimeMap.clear(); StreamSupport.stream(currentRow.spliterator(), false) .forEach(cell -> { if (!cell.getStringCellValue().isEmpty() && !cell.getStringCellValue().equals("Room")) { String[] startAndEndTimeStringArray = cell.getStringCellValue().split("-"); try { columnIndexToStartTimeMap.put(cell.getColumnIndex(), LocalTime.parse(startAndEndTimeStringArray[0], TIME_FORMATTER)); columnIndexToEndTimeMap.put(cell.getColumnIndex(), LocalTime.parse(startAndEndTimeStringArray[1], TIME_FORMATTER)); } catch (DateTimeParseException e) { throw new IllegalStateException(currentPosition() + ": The startTime (" + startAndEndTimeStringArray[0] + ") or endTime (" + startAndEndTimeStringArray[1] + ") doesn't parse as a time.", e); } } }); } }
|| isType(cell, CellType.BLANK) || (isType(cell,CellType.STRING) && (cell.getStringCellValue() == null || cell.getStringCellValue().isEmpty())
private ValueAndFormat getCellValue(Cell cell) { if (cell != null) { final String format = cell.getCellStyle().getDataFormatString(); CellType type = cell.getCellType(); if (type == CellType.FORMULA) { type = cell.getCachedFormulaResultType(); } switch (type) { case NUMERIC: return new ValueAndFormat(Double.valueOf(cell.getNumericCellValue()), format, decimalTextFormat); case STRING: case BOOLEAN: return new ValueAndFormat(cell.getStringCellValue(), format); default: break; } } return new ValueAndFormat("", ""); } /**
break; default: value = cell.getStringCellValue();
break; default: value = cell.getStringCellValue();
return apply(label, c.getStringCellValue()); default: return apply(label, "?");