List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:com.medicaid.mmis.util.CodeMappingLoader.java
License:Apache License
private static void importSheet(EntityManager em, SequenceGeneratorBean sequence, Workbook workbook, String sheetName) {//from ww w .j av a2 s . c o m logger.info("Importing legacy mapping from worksheet: " + sheetName); Sheet sheet = workbook.getSheet(sheetName); Iterator<Row> rowIterator = sheet.rowIterator(); int total = 0; while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); if (row.getRowNum() < 1) { continue; } Cell systemIdCell = row.getCell(0); Cell codeTypeCell = row.getCell(1); Cell internalCodeCell = row.getCell(2); Cell externalCodeCell = row.getCell(4); if (systemIdCell == null || StringUtils.isBlank(systemIdCell.getStringCellValue())) { continue; } if (codeTypeCell == null || StringUtils.isBlank(codeTypeCell.getStringCellValue())) { continue; } if (internalCodeCell == null || StringUtils.isBlank(internalCodeCell.getStringCellValue())) { continue; } if (externalCodeCell == null || StringUtils.isBlank(externalCodeCell.getStringCellValue())) { continue; } LegacySystemMapping mapping = new LegacySystemMapping(); mapping.setId(sequence.getNextValue("LEGACY_MAPPING")); mapping.setSystemName(systemIdCell.getStringCellValue()); mapping.setExternalCode(externalCodeCell.getStringCellValue()); mapping.setInternalCode(internalCodeCell.getStringCellValue()); mapping.setCodeType(codeTypeCell.getStringCellValue()); logger.debug("Inserting mapping: " + mapping); em.persist(mapping); total++; } logger.info("Total records imported from sheet: " + total); System.out.println("Total records imported from sheet[" + sheetName + "] : " + total); }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public void addNew(String filePath, String testName, String cellValue[]) throws PhrescoException { try {// ww w. jav a2 s . c o m //FileInputStream myInput = new FileInputStream(filePath); int numCol; int cellno = 0; CellStyle tryStyle[] = new CellStyle[20]; String sheetName = testName; //String cellValue[] = {"","",testName,success, fail,"","","",total,testCoverage,"","",""}; Iterator<Row> rowIterator; File testDir = new File(filePath); StringBuilder sb = new StringBuilder(filePath); if (testDir.isDirectory()) { FilenameFilter filter = new PhrescoFileFilter("", "xlsx"); File[] listFiles = testDir.listFiles(filter); if (listFiles.length != 0) { for (File file1 : listFiles) { if (file1.isFile()) { sb.append(File.separator); sb.append(file1.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); XSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); numCol = 13; Row next; for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; } do { int flag = 0; next = rowIterator.next(); if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index") && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) { for (Cell cell : next) { cell.setCellType(1); if (cell.getStringCellValue().equalsIgnoreCase("total")) { mySheet.shiftRows((mySheet.getLastRowNum() - 1), (mySheet.getPhysicalNumberOfRows() - 1), 1); flag = 1; } if (flag == 1) break; } if (flag == 1) break; } } while (rowIterator.hasNext()); Row r = null; if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { r = mySheet.createRow(next.getRowNum() - 1); } else { r = mySheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); // used only when sheet is 'index' if (i == 2) sheetName = cellValue[i]; cell.setCellStyle(tryStyle[i]); } if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1)); Sheet toSheet = myWorkBook.createSheet(sheetName); int i = 0; Iterator<Row> copyFrom = fromSheet.rowIterator(); Row fromRow, toRow; CellStyle newSheetStyle[] = new CellStyle[20]; Integer newSheetType[] = new Integer[100]; String newSheetValue[] = new String[100]; do { fromRow = copyFrom.next(); if (fromRow.getRowNum() == 24) { break; } toRow = toSheet.createRow(i); int numCell = 0; for (Cell cell : fromRow) { Cell newCell = toRow.createCell(numCell); cell.setCellType(1); newSheetStyle[numCell] = cell.getCellStyle(); newCell.setCellStyle(newSheetStyle[numCell]); newSheetType[numCell] = cell.getCellType(); newCell.setCellType(newSheetType[numCell]); if (fromRow.getCell(0).getStringCellValue().length() != 1 && fromRow.getCell(0).getStringCellValue().length() != 2 && fromRow.getCell(0).getStringCellValue().length() != 3) { newSheetValue[numCell] = cell.getStringCellValue(); newCell.setCellValue(newSheetValue[numCell]); } numCell = numCell + 1; } i = i + 1; } while (copyFrom.hasNext()); } // write to file FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } else { FilenameFilter xlsFilter = new PhrescoFileFilter("", "xls"); File[] xlsListFiles = testDir.listFiles(xlsFilter); if (xlsListFiles.length != 0) { for (File file2 : xlsListFiles) { if (file2.isFile()) { sb.append(File.separator); sb.append(file2.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); HSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); numCol = 13; Row next; for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; } do { int flag = 0; next = rowIterator.next(); if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index") && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) { for (Cell cell : next) { cell.setCellType(1); if (cell.getStringCellValue().equalsIgnoreCase("total")) { mySheet.shiftRows((mySheet.getLastRowNum() - 1), (mySheet.getPhysicalNumberOfRows() - 1), 1); flag = 1; } if (flag == 1) break; } if (flag == 1) break; } } while (rowIterator.hasNext()); Row r = null; if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { r = mySheet.createRow(mySheet.getLastRowNum() - 2); } else { r = mySheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); // used only when sheet is 'index' if (i == 2) sheetName = cellValue[i]; cell.setCellStyle(tryStyle[i]); } if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1)); Sheet toSheet = myWorkBook.createSheet(sheetName); int i = 0; Iterator<Row> copyFrom = fromSheet.rowIterator(); Row fromRow, toRow; CellStyle newSheetStyle[] = new CellStyle[20]; Integer newSheetType[] = new Integer[100]; String newSheetValue[] = new String[100]; do { fromRow = copyFrom.next(); if (fromRow.getRowNum() == 24) { break; } toRow = toSheet.createRow(i); int numCell = 0; for (Cell cell : fromRow) { Cell newCell = toRow.createCell(numCell); cell.setCellType(1); newSheetStyle[numCell] = cell.getCellStyle(); newCell.setCellStyle(newSheetStyle[numCell]); newSheetType[numCell] = cell.getCellType(); newCell.setCellType(newSheetType[numCell]); if (fromRow.getCell(0).getStringCellValue().length() != 1 && fromRow.getCell(0).getStringCellValue().length() != 2 && fromRow.getCell(0).getStringCellValue().length() != 3) { newSheetValue[numCell] = cell.getStringCellValue(); newCell.setCellValue(newSheetValue[numCell]); } numCell = numCell + 1; if (numCell == 15) { break; } } i = i + 1; } while (copyFrom.hasNext()); } // write to file FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } else { FilenameFilter odsFilter = new PhrescoFileFilter("", "ods"); File[] odsListFiles = testDir.listFiles(odsFilter); for (File file1 : odsListFiles) { if (file1.isFile()) { sb.append(File.separator); sb.append(file1.getName()); break; } } File file = new File(sb.toString()); addTestSuiteToOds(file, cellValue); } } } } catch (Exception e) { // throw new PhrescoException(e); } }
From source file:com.qihang.winter.poi.excel.imports.ExcelImportServer.java
License:Apache License
private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, com.qihang.winter.poi.excel.entity.ImportParams params, Map<String, PictureData> pictures) throws Exception { List collection = new ArrayList(); Map<String, com.qihang.winter.poi.excel.entity.params.ExcelImportEntity> excelParams = new HashMap<String, com.qihang.winter.poi.excel.entity.params.ExcelImportEntity>(); List<com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams> excelCollection = new ArrayList<com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams>(); String targetId = null;/*from ww w . ja v a 2s. c o m*/ if (!Map.class.equals(pojoClass)) { Field fileds[] = com.qihang.winter.poi.util.PoiPublicUtil.getClassFields(pojoClass); com.qihang.winter.poi.excel.annotation.ExcelTarget etarget = pojoClass .getAnnotation(com.qihang.winter.poi.excel.annotation.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); Row row = null; Object object = null; String picId; while (rows.hasNext() && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) { row = rows.next(); // ???,?,? if ((row.getCell(params.getKeyIndex()) == null || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) { for (com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams param : excelCollection) { addListContinue(object, param, row, titlemap, targetId, pictures, params); } } else { object = com.qihang.winter.poi.util.PoiPublicUtil.createObject(pojoClass, targetId); try { for (int i = row.getFirstCellNum(), le = row.getLastCellNum(); 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 (com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams param : excelCollection) { addListContinue(object, param, row, titlemap, targetId, pictures, params); } collection.add(object); } catch (com.qihang.winter.poi.exception.excel.ExcelImportException e) { if (!e.getType() .equals(com.qihang.winter.poi.exception.excel.enums.ExcelImportEnum.VERIFY_ERROR)) { throw new com.qihang.winter.poi.exception.excel.ExcelImportException(e.getType(), e); } } } } return collection; }
From source file:com.qualogy.qafe.service.DocumentServiceImpl.java
License:Apache License
private DocumentOutput handleExcelData(Sheet sheetData, boolean hasRowHeader) { DocumentOutput docOutput = new DocumentOutput(); // Determine the column names List<String> columnNameList = new ArrayList<String>(); if (sheetData.rowIterator().hasNext()) { Row row = sheetData.rowIterator().next(); int emptyColCountChain = 0; String colName = null;//from w ww .j a v a2 s. c o m for (Iterator<Cell> itr = row.cellIterator(); itr.hasNext();) { Cell cell = itr.next(); boolean cellHasData = (cell.getCellType() != Cell.CELL_TYPE_BLANK); if (hasRowHeader && cellHasData) { colName = getCellValue(cell); } else { colName = DEFAULT_FIELD_NAME + cell.getColumnIndex(); } columnNameList.add(colName); if (cellHasData) { emptyColCountChain = 0; } else { emptyColCountChain++; } if (emptyColCountChain > EMPTY_NUMCOLUMNS_TOLERANCE) { break; } } } // Get the data from sheet List<Map<String, String>> data = new ArrayList<Map<String, String>>(); boolean[] columnsHaveData = new boolean[columnNameList.size()]; for (Iterator<Row> itr = sheetData.rowIterator(); itr.hasNext();) { Row row = itr.next(); if (hasRowHeader && (row.getRowNum() == 0)) { continue; } Map<String, String> rowData = new LinkedHashMap<String, String>(); boolean rowHasData = false; for (Iterator<Cell> itr2 = row.cellIterator(); itr2.hasNext();) { Cell cell = itr2.next(); if (cell.getColumnIndex() < columnNameList.size()) { String colName = columnNameList.get(cell.getColumnIndex()); String cellValue = null; if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { cellValue = getCellValue(cell); } boolean cellHasData = ((cellValue != null) && (cellValue.length() > 0)); columnsHaveData[cell.getColumnIndex()] = columnsHaveData[cell.getColumnIndex()] || cellHasData; rowHasData = rowHasData || cellHasData; rowData.put(colName, cellValue); } else { break; } } if (rowHasData) { data.add(rowData); } } removeEmptyColumns(columnNameList, data, columnsHaveData); printData(data); docOutput.setData(data); return docOutput; }
From source file:com.r573.enfili.common.doc.spreadsheet.SpreadsheetHelper.java
License:Apache License
public static void calculate(Sheet sheet) { FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); for (Iterator<Row> rowIterator = sheet.rowIterator(); rowIterator.hasNext();) { Row row = rowIterator.next();//w w w . j av a 2 s . c om for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) { Cell cell = cellIterator.next(); if (cell != null && cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) { evaluator.evaluateInCell(cell); } } } }
From source file:com.radaee.excel.ToHtml.java
License:Apache License
public void printStyles() { ensureOut();/* w w w .j av a 2 s .c o m*/ // First, copy the base css BufferedReader in = null; try { in = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream("excelStyle.css"))); // in = new BufferedReader(new InputStreamReader()) String line; while ((line = in.readLine()) != null) { out.format("%s%n", line); } } catch (IOException e) { throw new IllegalStateException("Reading standard css", e); } finally { if (in != null) { try { in.close(); } catch (IOException e) { //noinspection ThrowFromFinallyBlock throw new IllegalStateException("Reading standard css", e); } } } // now add css for each used style Set<CellStyle> seen = new HashSet<CellStyle>(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) { CellStyle style = cell.getCellStyle(); if (!seen.contains(style)) { printStyle(style); seen.add(style); } } } } }
From source file:com.radaee.excel.ToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();// w w w . ja v a 2s. co m out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); //Set the value that is rendered for the cell //also applies the format // CellFormat cf = CellFormat.getInstance( // style.getDataFormatString()); // CellFormatResult result = cf.apply(cell); // content = result.text; content = getText(cell); if (content.equals("")) content = " "; } } out.format(" <td class=%s %s>%s</td>%n", styleName(style), attrs, content); } out.format(" </tr>%n"); } out.format("</tbody>%n"); }
From source file:com.runwaysdk.dataaccess.io.ExcelImporter.java
License:Open Source License
private void readSheet(ImportContext context) { Sheet sheet = context.getImportSheet(); Iterator<Row> rowIterator = sheet.rowIterator(); // Parse the header rows readHeaders(context, rowIterator);/*from w w w . j a v a 2 s. c om*/ // The main loop where we import each row as an instance while (rowIterator.hasNext()) { try { readRow(context, rowIterator.next()); } catch (ProgrammingErrorException e) { Throwable cause = e.getCause(); if (!(cause instanceof StopTransactionException)) { throw e; } } catch (StopTransactionException e) { // This is thrown only to keep the Transaction from committing. We can // ignore it. } } }
From source file:com.runwaysdk.facade.InvokeMethodTest.java
License:Open Source License
public void testInvokeMethodWithByteArrayReturnType() throws Exception { BusinessDTO collectionObj1 = clientRequest.newBusiness(collectionType); collectionObj1.setValue("aCharacter", "some value"); clientRequest.createBusiness(collectionObj1); BusinessDTO collectionObj2 = clientRequest.newBusiness(collectionType); collectionObj2.setValue("aCharacter", "some other value"); clientRequest.createBusiness(collectionObj2); try {/*from ww w. ja v a 2s . c om*/ Class<?> collectionClass = WebTestGeneratedClassLoader.load(collectionDTO); Method getCount = collectionClass.getMethod("getCollectionObjectCount", ClientRequestIF.class); Integer recordCount = (Integer) getCount.invoke(null, clientRequest); Method getExcelBytes = collectionClass.getMethod("getExcelFile", ClientRequestIF.class); Byte[] excelBytes = (Byte[]) getExcelBytes.invoke(null, clientRequest); // FileOutputStream fileBytes = new FileOutputStream(new // File(ExcelTest.path+"/ValueQueryTest.xls")); byte[] bytes = new byte[excelBytes.length]; for (int i = 0; i < bytes.length; i++) { bytes[i] = excelBytes[i]; } // fileBytes.write(bytes); // // fileBytes.flush(); // fileBytes.close(); InputStream stream = new ByteArrayInputStream(bytes); POIFSFileSystem fileSystem = new POIFSFileSystem(stream); Workbook workbook = new HSSFWorkbook(fileSystem); Sheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.rowIterator(); Integer rowCount = 0; while (rowIterator.hasNext()) { rowIterator.next(); rowCount++; } // Minus 1 for the header row rowCount--; assertEquals(recordCount, rowCount); } finally { clientRequest.delete(collectionObj1.getId()); clientRequest.delete(collectionObj2.getId()); } }
From source file:com.runwaysdk.facade.InvokeMethodTest.java
License:Open Source License
public void testInvokeMethodWithInputStreamReturnType() throws Exception { BusinessDTO collectionObj1 = clientRequest.newBusiness(collectionType); collectionObj1.setValue("aCharacter", "some value"); clientRequest.createBusiness(collectionObj1); BusinessDTO collectionObj2 = clientRequest.newBusiness(collectionType); collectionObj2.setValue("aCharacter", "some other value"); clientRequest.createBusiness(collectionObj2); try {/*w w w.j a va2 s .c o m*/ Class<?> collectionClass = WebTestGeneratedClassLoader.load(collectionDTO); Method getCount = collectionClass.getMethod("getCollectionObjectCount", ClientRequestIF.class); Integer recordCount = (Integer) getCount.invoke(null, clientRequest); Method getExcelBytes = collectionClass.getMethod("getFileStream", ClientRequestIF.class); InputStream stream = (InputStream) getExcelBytes.invoke(null, clientRequest); POIFSFileSystem fileSystem = new POIFSFileSystem(stream); Workbook workbook = new HSSFWorkbook(fileSystem); Sheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.rowIterator(); Integer rowCount = 0; while (rowIterator.hasNext()) { rowIterator.next(); rowCount++; } // Minus 1 for the header row rowCount--; assertEquals(recordCount, rowCount); } finally { clientRequest.delete(collectionObj1.getId()); clientRequest.delete(collectionObj2.getId()); } }