List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:ambit2.core.test.io.POItest.java
License:Open Source License
@Test public void test1() throws Exception { InputStream in = getClass().getClassLoader() .getResourceAsStream("ambit2/core/data/misc/Debnath_smiles.xls"); Workbook workbook = new HSSFWorkbook(in); Sheet sheet = workbook.getSheetAt(0); //HSSFSheet sheet = workbook.getSheet("Sheet1"); Iterator i = sheet.rowIterator(); while (i.hasNext()) { Object o = i.next();//from w w w . ja va 2s.c o m Assert.assertTrue(o instanceof Row); Iterator j = ((Row) o).cellIterator(); while (j.hasNext()) { Object cell = j.next(); Assert.assertTrue(cell instanceof Cell); //System.out.println(cell); } } }
From source file:b01.officeLink.excel.FocExcelDocument.java
License:Apache License
public void exportLocally(FocObject object) { try {//from www . jav a2s . c o m Sheet sheet = workbook.getSheetAt(0); // Iterate over each row in the sheet Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = (Row) rows.next(); System.out.println("Row #" + row.getRowNum()); // Iterate over each cell in the row and print out the cell's content Iterator cells = row.cellIterator(); while (cells.hasNext()) { Cell cell = (Cell) cells.next(); System.out.println("Cell #" + cell.getColumnIndex()); /* System.out.println(String.valueOf(cell.getRichStringCellValue())); */ String str = null; try { str = String.valueOf(cell.getRichStringCellValue()); } catch (Exception e) { Globals.logExceptionWithoutPopup(e); str = ""; } String result = analyseContent(str, object); if (result != null) { if (getWorkbook() != null) { cell.setCellValue(getWorkbook().getCreationHelper().createRichTextString(result)); } } /* * switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: * System.out.println(cell.getNumericCellValue()); String result = * analyseContent(String.valueOf(cell.getNumericCellValue()), object); * if (result != null){ cell.setCellValue(Double.valueOf(result)); } * break; case HSSFCell.CELL_TYPE_STRING: * System.out.println(cell.getRichStringCellValue()); result = * analyseContent(String.valueOf(cell.getRichStringCellValue()), * object); if (result != null){ cell.setCellValue(new * HSSFRichTextString(result)); } break; default: * System.out.println("unsuported cell type"); break; } */} } } catch (Exception e) { e.printStackTrace(); } }
From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java
private String workbook2xml(org.apache.poi.ss.usermodel.Workbook workbook) { String result = null;/*from w w w. j a va 2 s . c o m*/ StringBuffer sb = null; Sheet sheet = null; if (workbook != null && workbook.getSheetAt(0) != null) { String newLine = System.getProperty("line.separator"); sb = new StringBuffer(); sb.append("<?xml version=\"1.0\" ?>"); sb.append(newLine); sb.append("<!DOCTYPE workbook SYSTEM \"workbook.dtd\">"); sb.append(newLine); sb.append(newLine); sb.append("<workbook>"); sb.append(newLine); for (int i = 0; i < workbook.getNumberOfSheets(); ++i) { sheet = workbook.getSheetAt(i); if (sheet != null && sheet.rowIterator().hasNext()) { sb.append("\t"); sb.append("<sheet>"); sb.append(newLine); sb.append("\t\t"); sb.append("<name><![CDATA[" + sheet.getSheetName() + "]]></name>"); sb.append(newLine); int j = 0; for (Iterator<Row> iterator = sheet.rowIterator(); iterator.hasNext();) { Row row = (Row) iterator.next(); int k = 0; if (row.getCell(0) != null && row.getCell(0).getStringCellValue() != null && row.getCell(0).getStringCellValue().trim().length() > 0) { sb.append("\t\t"); sb.append("<row number=\"" + j + "\">"); sb.append(newLine); for (Cell cell : row) { sb.append("\t\t\t"); sb.append("<col number=\"" + k + "\">"); sb.append("<![CDATA[" + cellToString(cell) + "]]>"); sb.append("</col>"); sb.append(newLine); k++; } sb.append("\t\t"); sb.append("</row>"); sb.append(newLine); } j++; } sb.append("\t"); sb.append("</sheet>"); sb.append(newLine); } } sb.append("</workbook>"); sb.append(newLine); result = sb.toString(); } return result; }
From source file:br.unesp.rc.desafio.utils.Spreadsheet.java
public static ArrayList<String> ReadXlsSpreadsheet(File spreadsheet) { /*// ww w. j a v a 2s . c o m Constructing File */ ArrayList values = new ArrayList<String>(); FileInputStream inputStr = null; try { inputStr = new FileInputStream(spreadsheet); } catch (FileNotFoundException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Workbook currentSpreadsheetFile = null; try { HSSFRow row; currentSpreadsheetFile = new HSSFWorkbook(inputStr); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Sheet sheet = currentSpreadsheetFile.getSheetAt(0); Iterator<Row> rowItr = sheet.rowIterator(); while (rowItr.hasNext()) { row = (HSSFRow) rowItr.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellValue = ""; switch (cell.getCellTypeEnum()) { default: // cellValue = cell.getCellFormula(); cellValue = Double.toString(cell.getNumericCellValue()); cell.setCellType(CellType.STRING); cell.setCellValue(cellValue); break; case NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); cell.setCellType(CellType.STRING); cell.setCellValue(cellValue); case BLANK: break; case STRING: break; } if (!cell.getStringCellValue().isEmpty()) { values.add(cell.getStringCellValue()); values.add(","); // System.out.println("HOLD IT"); } else { values.add("0"); values.add(","); // System.out.println("OBJECTION!!"); } //System.out.print(cell.getStringCellValue() + " \t\t " ); } //System.out.println(); values.add(";"); } try { inputStr.close(); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } //System.out.println(values.get(0)); return values; }
From source file:br.unesp.rc.desafio.utils.Spreadsheet.java
public static ArrayList<String> ReadXlsxSpreadsheet(File spreadsheet) { /*// w ww .j ava2s. c o m Constructing File */ ArrayList values = new ArrayList<String>(); FileInputStream inputStr = null; try { inputStr = new FileInputStream(spreadsheet); } catch (FileNotFoundException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Workbook currentSpreadsheetFile = null; try { XSSFRow row1; currentSpreadsheetFile = new XSSFWorkbook(inputStr); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Sheet sheet = currentSpreadsheetFile.getSheetAt(0); Iterator<Row> rowItr = sheet.rowIterator(); while (rowItr.hasNext()) { row1 = (XSSFRow) rowItr.next(); Iterator<Cell> cellIterator = row1.cellIterator(); while (cellIterator.hasNext()) { String cellValue = ""; Cell cell = cellIterator.next(); switch (cell.getCellTypeEnum()) { default: cellValue = cell.getCellFormula(); cell.setCellType(CellType.STRING); cell.setCellValue(cellValue); break; case BLANK: break; case STRING: break; } values.add(cell.getStringCellValue()); System.out.print(cell.getStringCellValue() + " \t\t "); } System.out.println(); } try { inputStr.close(); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } return values; }
From source file:business.SongExcelParser.java
private ArrayList<SongContainer> sheetToSongFiles(Sheet sheet) { SongFile songFile;/* ww w . j av a 2s . com*/ Row row; ArrayList<SongContainer> songFiles = new ArrayList<>(); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { row = rows.next(); if (row.getRowNum() != 0) { songFile = getSongFileFromRow(row); if (songFile != null && songFile.isValid()) { songFiles.add(songFile); } } } return songFiles; }
From source file:business.SongExcelParser.java
private ArrayList<Song> sheetToCDSongs(Sheet sheet) { Song song;//from www .ja v a 2 s .c o m Row row; ArrayList<Song> songs = new ArrayList<>(); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { row = rows.next(); if (row.getRowNum() != 0) { song = getSongFromRow(row); if (song != null && song.isValid()) { songs.add(song); } } } return songs; }
From source file:cn.afterturn.easypoi.excel.imports.ExcelImportService.java
License:Apache License
private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params, Map<String, PictureData> pictures) throws Exception { List collection = new ArrayList(); Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>(); List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>(); String targetId = null;/*w w w . j a v a 2s . c om*/ i18nHandler = params.getI18nHandler(); boolean isMap = Map.class.equals(pojoClass); if (!isMap) { Field[] fileds = PoiPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); if (etarget != null) { targetId = etarget.value(); } getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null, null); } Iterator<Row> rows = sheet.rowIterator(); for (int j = 0; j < params.getTitleRows(); j++) { rows.next(); } Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection, excelParams); checkIsValidTemplate(titlemap, excelParams, params, excelCollection); Row row = null; Object object = null; String picId; int readRow = 1; // for (int i = 0; i < params.getStartRows(); i++) { rows.next(); } //index ?,? if (excelCollection.size() > 0 && params.getKeyIndex() == null) { params.setKeyIndex(0); } if (params.isConcurrentTask()) { ForkJoinPool forkJoinPool = new ForkJoinPool(); int endRow = sheet.getLastRowNum() - params.getLastOfInvalidRow(); if (params.getReadRows() > 0) { endRow = params.getReadRows(); } ExcelImportForkJoinWork task = new ExcelImportForkJoinWork( params.getStartRows() + params.getHeadRows() + params.getTitleRows(), endRow, sheet, params, pojoClass, this, targetId, titlemap, excelParams); ExcelImportResult forkJoinResult = forkJoinPool.invoke(task); collection = forkJoinResult.getList(); failCollection = forkJoinResult.getFailList(); } else { StringBuilder errorMsg; while (rows.hasNext() && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) { if (params.getReadRows() > 0 && readRow > params.getReadRows()) { break; } row = rows.next(); // Fix row if (sheet.getLastRowNum() - row.getRowNum() < params.getLastOfInvalidRow()) { break; } /* ?? */ if (row.getLastCellNum() < 0) { continue; } if (isMap && object != null) { ((Map) object).put("excelRowNum", row.getRowNum()); } errorMsg = new StringBuilder(); // ???,?,? // keyIndex ??,?? if (params.getKeyIndex() != null && (row.getCell(params.getKeyIndex()) == null || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) { for (ExcelCollectionParams param : excelCollection) { addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg); } } else { object = PoiPublicUtil.createObject(pojoClass, targetId); try { Set<Integer> keys = titlemap.keySet(); for (Integer cn : keys) { Cell cell = row.getCell(cn); String titleString = (String) titlemap.get(cn); if (excelParams.containsKey(titleString) || isMap) { if (excelParams.get(titleString) != null && excelParams.get(titleString) .getType() == BaseEntityTypeConstants.IMAGE_TYPE) { picId = row.getRowNum() + "_" + cn; saveImage(object, picId, excelParams, titleString, pictures, params); } else { try { saveFieldValue(params, object, cell, excelParams, titleString, row); } catch (ExcelImportException e) { // ?,, if (params.isNeedVerify() && ExcelImportEnum.GET_VALUE_ERROR.equals(e.getType())) { errorMsg.append(" ").append(titleString) .append(ExcelImportEnum.GET_VALUE_ERROR.getMsg()); } } } } } //for (int i = row.getFirstCellNum(), le = titlemap.size(); i < le; i++) { //} if (object instanceof IExcelDataModel) { ((IExcelDataModel) object).setRowNum(row.getRowNum()); } for (ExcelCollectionParams param : excelCollection) { addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg); } if (verifyingDataValidity(object, row, params, isMap, errorMsg)) { collection.add(object); } else { failCollection.add(object); } } catch (ExcelImportException e) { LOGGER.error("excel import error , row num:{},obj:{}", readRow, ReflectionToStringBuilder.toString(object)); if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) { throw new ExcelImportException(e.getType(), e); } } catch (Exception e) { LOGGER.error("excel import error , row num:{},obj:{}", readRow, ReflectionToStringBuilder.toString(object)); throw new RuntimeException(e); } } readRow++; } } return collection; }
From source file:cn.bzvs.excel.imports.ExcelImportServer.java
License:Apache License
private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params, Map<String, PictureData> pictures) throws Exception { List collection = new ArrayList(); Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>(); List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>(); String targetId = null;/*from w ww .j a v a 2 s . c o m*/ if (!Map.class.equals(pojoClass)) { Field fileds[] = PoiPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); if (etarget != null) { targetId = etarget.value(); } getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null); } Iterator<Row> rows = sheet.rowIterator(); for (int j = 0; j < params.getTitleRows(); j++) { rows.next(); } Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection); checkIsValidTemplate(titlemap, excelParams, params, excelCollection); Row row = null; Object object = null; String picId; while (rows.hasNext() && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) { row = rows.next(); // ???,?,? // keyIndex ??,?? if (params.getKeyIndex() != null && (row.getCell(params.getKeyIndex()) == null || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) { for (ExcelCollectionParams param : excelCollection) { addListContinue(object, param, row, titlemap, targetId, pictures, params); } } else { object = PoiPublicUtil.createObject(pojoClass, targetId); try { for (int i = row.getFirstCellNum(), le = titlemap.size(); i < le; i++) { Cell cell = row.getCell(i); String titleString = (String) titlemap.get(i); if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) { if (excelParams.get(titleString) != null && excelParams.get(titleString).getType() == 2) { picId = row.getRowNum() + "_" + i; saveImage(object, picId, excelParams, titleString, pictures, params); } else { saveFieldValue(params, object, cell, excelParams, titleString, row); } } } for (ExcelCollectionParams param : excelCollection) { addListContinue(object, param, row, titlemap, targetId, pictures, params); } if (verifyingDataValidity(object, row, params, pojoClass)) { collection.add(object); } } catch (ExcelImportException e) { if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) { throw new ExcelImportException(e.getType(), e); } } } } return collection; }
From source file:cn.edu.zjnu.acm.judge.util.excel.ExcelUtil.java
License:Apache License
private static <T> List<T> parse(Workbook workbook, FormulaEvaluator evaluator, Class<T> type, Locale locale) { MetaInfo metaInfo = MetaInfo.forType(type, locale); Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); Iterator<Row> rows = sheet.rowIterator(); if (!rows.hasNext()) { return Collections.emptyList(); }//from w w w . j a va 2s .com Row firstRow = rows.next(); Map<Integer, String> columnIndexToFieldName = Maps.newHashMapWithExpectedSize(metaInfo.size()); for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) { Cell cell = it.next(); JsonElement jsonElement = parseAsJsonElement(cell, evaluator); if (jsonElement != null) { Field field = metaInfo.getField(jsonElement.getAsString()); if (field != null) { String name = field.getName(); int index = cell.getColumnIndex(); columnIndexToFieldName.put(index, name); } } } if (columnIndexToFieldName.isEmpty()) { return Collections.emptyList(); } List<T> result = new ArrayList<>(sheet.getLastRowNum() - sheet.getFirstRowNum()); while (rows.hasNext()) { result.add(parseRow(evaluator, rows.next(), columnIndexToFieldName, type)); } return result; }