List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCell
@Override public XSSFCell getCell(int cellnum)
From source file:org.ecocean.servlet.importer.ImportExcelMetadata.java
License:Open Source License
public Date getDate(XSSFRow row, int i) { try {/*ww w .j av a 2 s .c o m*/ Date date = row.getCell(i).getDateCellValue(); return date; } catch (Exception e) { } return null; }
From source file:org.exoplatform.services.document.impl.MSXExcelDocumentReader.java
License:Open Source License
/** * Returns only a text from .xlsx file content. * /*from w w w .ja va 2 s . c om*/ * @param is an input stream with .xls file content. * @return The string only with text from file content. */ public String getContentAsText(final InputStream is) throws IOException, DocumentReadException { if (is == null) { throw new IllegalArgumentException("InputStream is null."); } StringBuilder builder = new StringBuilder(""); SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT); try { if (is.available() == 0) { return ""; } XSSFWorkbook wb; try { wb = SecurityHelper.doPrivilegedIOExceptionAction(new PrivilegedExceptionAction<XSSFWorkbook>() { public XSSFWorkbook run() throws Exception { return new XSSFWorkbook(is); } }); } catch (IOException e) { throw new DocumentReadException("Can't open spreadsheet.", e); } catch (OpenXML4JRuntimeException e) { return builder.toString(); } for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { XSSFSheet sheet = wb.getSheetAt(sheetNum); if (sheet != null) { for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) { XSSFRow row = sheet.getRow(rowNum); if (row != null) { int lastcell = row.getLastCellNum(); for (int k = 0; k < lastcell; k++) { XSSFCell cell = row.getCell(k); if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: { double d = cell.getNumericCellValue(); if (isCellDateFormatted(cell)) { Date date = HSSFDateUtil.getJavaDate(d); String cellText = dateFormat.format(date); builder.append(cellText).append(" "); } else { builder.append(d).append(" "); } break; } case XSSFCell.CELL_TYPE_FORMULA: builder.append(cell.getCellFormula().toString()).append(" "); break; case XSSFCell.CELL_TYPE_BOOLEAN: builder.append(cell.getBooleanCellValue()).append(" "); break; case XSSFCell.CELL_TYPE_ERROR: builder.append(cell.getErrorCellValue()).append(" "); break; case XSSFCell.CELL_TYPE_STRING: builder.append(cell.getStringCellValue().toString()).append(" "); break; default: break; } } } } } } } } finally { if (is != null) { try { is.close(); } catch (IOException e) { if (LOG.isTraceEnabled()) { LOG.trace("An exception occurred: " + e.getMessage()); } } } } return builder.toString(); }
From source file:org.finra.jtaf.core.utilities.ExcelFileParser.java
License:Apache License
public List<List<String>> parseExcelFile(boolean isXlsx) throws Exception { List<List<String>> parsedExcelFile = new ArrayList<List<String>>(); if (isXlsx) { for (int i = 0, numberOfRows = workBookSheetXlsx.getPhysicalNumberOfRows(); i < numberOfRows + 1; i++) { XSSFRow row = workBookSheetXlsx.getRow(i); if (row != null) { List<String> parsedExcelRow = new ArrayList<String>(); for (int j = 0, numberOfColumns = row.getLastCellNum(); j < numberOfColumns; j++) { XSSFCell cell = row.getCell(j); if (cell != null) { try { if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { parsedExcelRow.add(cell.getStringCellValue()); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) { parsedExcelRow.add(""); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { parsedExcelRow.add(String.valueOf(cell.getBooleanCellValue())); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { parsedExcelRow.add(String.valueOf(cell.getNumericCellValue())); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) { parsedExcelRow.add(""); } else { parsedExcelRow.add(cell.getStringCellValue()); }//ww w. j a va 2 s .c o m } catch (Exception e) { logger.fatal("Oops! Can't read cell (row = " + i + ", column = " + j + ") in the excel file! Change cell format to 'Text', please!"); return null; } } else { parsedExcelRow.add(""); } } parsedExcelFile.add(parsedExcelRow); } } } else { for (int i = 0, numberOfRows = workBookSheetXls.getPhysicalNumberOfRows(); i < numberOfRows + 1; i++) { HSSFRow row = workBookSheetXls.getRow(i); if (row != null) { List<String> parsedExcelRow = new ArrayList<String>(); for (int j = 0, numberOfColumns = row.getLastCellNum(); j < numberOfColumns; j++) { HSSFCell cell = row.getCell(j); if (cell != null) { try { if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { parsedExcelRow.add(cell.getStringCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { parsedExcelRow.add(""); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { parsedExcelRow.add(String.valueOf(cell.getBooleanCellValue())); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { parsedExcelRow.add(String.valueOf(cell.getNumericCellValue())); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { parsedExcelRow.add(String.valueOf("")); } else { parsedExcelRow.add(cell.getStringCellValue()); } } catch (Exception e) { logger.fatal("Oops! Can't read cell (row = " + i + ", column = " + j + ") in the excel file! Change cell format to 'Text', please!"); return null; } } else { parsedExcelRow.add(""); } } parsedExcelFile.add(parsedExcelRow); } } } return parsedExcelFile; }
From source file:org.hlc.utility.excel.ExcelInputHandler.java
License:Apache License
/** * Import excel./*from www. j a v a 2 s . c om*/ * * @param <T> the generic type * @param type the type * @param in the in * @return the list */ @SuppressWarnings("rawtypes") public <T> List<T> importExcel2007(Class<T> type, InputStream in) { Excel excelAnn = type.getAnnotation(Excel.class); if (excelAnn == null) { throw new ExcelException("The Class <" + type + "> did not Excel"); } List<T> list = new ArrayList<T>(); Map<String, Method> mapping = new LinkedHashMap<String, Method>(); Map<String, TypeHandler> converters = new HashMap<String, TypeHandler>(); try { // Step1 ?? Field fileds[] = type.getDeclaredFields(); for (int i = 0; i < fileds.length; i++) { Field field = fileds[i]; ExcelColumn column = field.getAnnotation(ExcelColumn.class); if (column != null) { Method setMethod = ReflectionUtils.setValueMethod(field, type); mapping.put(column.value(), setMethod); if (column.converter() != TypeHandler.class) { converters.put(setMethod.getName().toString(), column.converter().newInstance()); } else { converters.put(setMethod.getName().toString(), TypeHandlerFactory.getHandler(field.getType())); } } } T temp = null; XSSFWorkbook hssfWorkbook = new XSSFWorkbook(in); for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // ?Sheet List<Method> methods = new ArrayList<Method>(); for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { XSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } // ? if (rowNum == 0) { for (int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++) { String title = hssfRow.getCell(cellNum).getStringCellValue(); Method me = mapping.get(title); if (me == null) { continue; } methods.add(me); } continue; } temp = type.newInstance(); for (int cellNum = 0; cellNum < methods.size(); cellNum++) { XSSFCell xh = hssfRow.getCell(cellNum); if (xh == null) { continue; } Method m = methods.get(cellNum); TypeHandler handler = converters.get(m.getName()); if (handler == null) { continue; } xh.setCellType(Cell.CELL_TYPE_STRING); String value = xh.getStringCellValue(); if (StringUtils.isEmpty(value)) { continue; } Object val = null; try { val = handler.stringToType(value); } catch (Exception e) { throw new ExcelException("" + (numSheet + 1) + "" + (rowNum + 1) + "" + (cellNum + 1) + "" + value + "??"); } methods.get(cellNum).invoke(temp, val); } list.add(temp); } } } catch (Exception e) { throw new ExcelException("Excel processing error?", e); } return list; }
From source file:org.kitodo.production.plugin.importer.massimport.PicaMassImport.java
License:Open Source License
private List<Record> getRecordsForXLSX(InputStream xls) throws IOException { List<Record> records = new ArrayList<>(); XSSFWorkbook wb = new XSSFWorkbook(xls); XSSFSheet sheet = wb.getSheetAt(0); // first sheet // loop over all rows for (int j = 0; j <= sheet.getLastRowNum(); j++) { // loop over all cells XSSFRow row = sheet.getRow(j); if (Objects.nonNull(row)) { for (int i = 0; i < row.getLastCellNum(); i++) { XSSFCell cell = row.getCell(i); // changing all cell types to String cell.setCellType(HSSFCell.CELL_TYPE_STRING); Record record = changeCellTypeToString(cell, i, j); if (Objects.nonNull(record)) { records.add(record); }/*from www.ja v a2 s .c o m*/ } } } return records; }
From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLSheet.java
License:Open Source License
@Override public Value getValueAt(int row, int column) { XSSFRow excelRow = sheet.getRow(row); if (excelRow == null) { BlankValue value = new BlankValue(); return value; }// w w w.j a va2 s .com XSSFCell excelCell = excelRow.getCell(column); if (excelCell == null) { BlankValue value = new BlankValue(); return value; } return POIUtil.getValueFormCell(excelCell); }
From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLSheet.java
License:Open Source License
@Override public void setFormualaAt(int row, int column, String formula) { XSSFRow excelRow = sheet.getRow(row); if (excelRow == null) { excelRow = sheet.createRow(row); }/*from w w w .j av a2 s.c o m*/ XSSFCell excelCell = excelRow.getCell(column); if (excelCell == null) { excelCell = excelRow.createCell(column); } excelCell.setCellFormula(formula); excelCell.setCellType(Cell.CELL_TYPE_FORMULA); }
From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLSheet.java
License:Open Source License
@Override public void setValueAt(int row, int column, String value) { XSSFRow excelRow = sheet.getRow(row); if (excelRow == null) { excelRow = sheet.createRow(row); }//from w ww . j av a2 s .c o m XSSFCell excelCell = excelRow.getCell(column); if (excelCell == null) { excelCell = excelRow.createCell(column); } XSSFRichTextString str = new XSSFRichTextString(value); excelCell.setCellValue(str); excelCell.setCellType(Cell.CELL_TYPE_STRING); }
From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLSheet.java
License:Open Source License
@Override public void setValueAt(int row, int column, double value) { XSSFRow excelRow = sheet.getRow(row); if (excelRow == null) { excelRow = sheet.createRow(row); }/* w w w . ja va2 s.c om*/ XSSFCell excelCell = excelRow.getCell(column); if (excelCell == null) { excelCell = excelRow.createCell(column); } excelCell.setCellValue(value); excelCell.setCellType(Cell.CELL_TYPE_NUMERIC); }
From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLSheet.java
License:Open Source License
@Override public void setValueAt(int row, int column, Date value, String format) { XSSFRow excelRow = sheet.getRow(row); if (excelRow == null) { excelRow = sheet.createRow(row); }// ww w. java2 s . co m XSSFCell excelCell = excelRow.getCell(column); if (excelCell == null) { excelCell = excelRow.createCell(column); } CreationHelper createHelper = this.sheet.getWorkbook().getCreationHelper(); CellStyle cellStyle = this.sheet.getWorkbook().createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format)); excelCell.setCellStyle(cellStyle); excelCell.setCellValue(value); excelCell.setCellType(Cell.CELL_TYPE_NUMERIC); }