List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ??????<BR>/*from w ww.j av a 2 s . c o m*/ * Cell?CELL_TYPE_BLANK??????????????? * * @see Workbook#cloneSheet(int) cloneSheet(int) * @param sheet * @deprecated poi-3.5-beta7-20090607.jar?? */ public static void prepareCloneSheet(Sheet sheet) { Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { cell.setCellValue(""); } } } }
From source file:org.bbreak.excella.reports.ReportsTestUtil.java
License:Open Source License
/** * // ww w . j av a 2 s . co m * * @param expected * @param actual * @param isActCopyOfExp ??????true * @throws ReportsCheckException */ public static void checkSheet(Sheet expected, Sheet actual, boolean isActCopyOfExp) throws ReportsCheckException { List<CheckMessage> errors = new ArrayList<CheckMessage>(); Workbook expectedWorkbook = expected.getWorkbook(); Workbook actualWorkbook = actual.getWorkbook(); if (log.isDebugEnabled()) { log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)) + "] check start!"); } // ---------------------- // ???? // ---------------------- // ?? String eSheetName = expectedWorkbook.getSheetName(expectedWorkbook.getSheetIndex(expected)); String aSheetName = actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)); if (!isActCopyOfExp) { if (!eSheetName.equals(aSheetName)) { errors.add(new CheckMessage("??", eSheetName, aSheetName)); } } // ? String ePrintSetupString = getPrintSetupString(expected.getPrintSetup()); String aPrintSetupString = getPrintSetupString(actual.getPrintSetup()); if (!ePrintSetupString.equals(aPrintSetupString)) { errors.add(new CheckMessage("?", ePrintSetupString, aPrintSetupString)); } // ? String eHeaderString = getHeaderString(expected.getHeader()); String aHeaderString = getHeaderString(actual.getHeader()); if (!eHeaderString.equals(aHeaderString)) { errors.add(new CheckMessage("", eHeaderString, aHeaderString)); } String eFooterString = getFooterString(expected.getFooter()); String aFooterString = getFooterString(actual.getFooter()); if (!eFooterString.equals(aFooterString)) { errors.add(new CheckMessage("", eFooterString, aFooterString)); } // String eBreaksString = getBreaksString(expected); String aBreaksString = getBreaksString(actual); log.debug(eBreaksString + "/" + aBreaksString); if (!eBreaksString.equals(aBreaksString)) { errors.add(new CheckMessage("", eBreaksString, aBreaksString)); } // ? String expectedPrintArea = expectedWorkbook.getPrintArea(expectedWorkbook.getSheetIndex(expected)); String actualPrintArea = actualWorkbook.getPrintArea(actualWorkbook.getSheetIndex(actual)); if (expectedPrintArea != null || actualPrintArea != null) { // ????????Null????????????? // if ( expectedPrintArea == null || actualPrintArea == null || !equalPrintArea( expectedPrintArea, actualPrintArea, isActCopyOfExp)) { // errors.add( new CheckMessage( "?", expectedPrintArea, actualPrintArea)); // } if (!isActCopyOfExp) { if (expectedPrintArea == null || actualPrintArea == null || !expectedPrintArea.equals(actualPrintArea)) { errors.add(new CheckMessage("?", expectedPrintArea, actualPrintArea)); } } } // (?) String ePaneInformationString = getPaneInformationString(expected.getPaneInformation()); String aPaneInformationString = getPaneInformationString(actual.getPaneInformation()); if (!ePaneInformationString.equals(aPaneInformationString)) { errors.add(new CheckMessage("(?)", expectedPrintArea, actualPrintArea)); } // ?????? // ????? // ????? // // if (expected.isDisplayGridlines() ^ actual.isDisplayGridlines()) { errors.add(new CheckMessage("", String.valueOf(expected.isDisplayGridlines()), String.valueOf(actual.isDisplayGridlines()))); } // ? if (expected.isDisplayRowColHeadings() ^ actual.isDisplayRowColHeadings()) { errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayRowColHeadings()), String.valueOf(actual.isDisplayRowColHeadings()))); } // ? if (expected.isDisplayFormulas() ^ actual.isDisplayFormulas()) { errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayFormulas()), String.valueOf(actual.isDisplayFormulas()))); } // ?? if (expected.getNumMergedRegions() != actual.getNumMergedRegions()) { errors.add(new CheckMessage("??", String.valueOf(expected.getNumMergedRegions()), String.valueOf(actual.getNumMergedRegions()))); } for (int i = 0; i < actual.getNumMergedRegions(); i++) { CellRangeAddress actualAddress = null; if (expected instanceof HSSFSheet) { actualAddress = ((HSSFSheet) actual).getMergedRegion(i); } else if (expected instanceof XSSFSheet) { actualAddress = ((XSSFSheet) actual).getMergedRegion(i); } StringBuffer expectedAdressBuffer = new StringBuffer(); boolean equalAddress = false; for (int j = 0; j < expected.getNumMergedRegions(); j++) { CellRangeAddress expectedAddress = null; if (expected instanceof HSSFSheet) { expectedAddress = ((HSSFSheet) expected).getMergedRegion(j); } else if (expected instanceof XSSFSheet) { expectedAddress = ((XSSFSheet) expected).getMergedRegion(j); } if (expectedAddress.toString().equals(actualAddress.toString())) { equalAddress = true; break; } CellReference crA = new CellReference(expectedAddress.getFirstRow(), expectedAddress.getFirstColumn()); CellReference crB = new CellReference(expectedAddress.getLastRow(), expectedAddress.getLastColumn()); expectedAdressBuffer.append(" [" + crA.formatAsString() + ":" + crB.formatAsString() + "]"); } if (!equalAddress) { errors.add(new CheckMessage("??", expectedAdressBuffer.toString(), actualAddress.toString())); } } int maxColumnNum = -1; if (expected instanceof HSSFSheet) { maxColumnNum = HSSF_MAX_COLUMN_NUMBER; } else if (expected instanceof XSSFSheet) { maxColumnNum = XSSF_MAX_COLUMN_NUMBER; } for (int i = 0; i < maxColumnNum; i++) { try { // checkCellStyle(expected.getWorkbook(), expected.getColumnStyle(i), actual.getWorkbook(), actual.getColumnStyle(i)); } catch (ReportsCheckException e) { CheckMessage checkMessage = e.getCheckMessages().iterator().next(); checkMessage.setMessage("[" + i + "]" + checkMessage.getMessage()); errors.add(checkMessage); } // if (expected.getColumnWidth(i) != actual.getColumnWidth(i)) { errors.add(new CheckMessage("[" + i + "]", String.valueOf(expected.getColumnWidth(i)), String.valueOf(actual.getColumnWidth(i)))); } } // ??? if (expected.getLastRowNum() != actual.getLastRowNum()) { // ?????? if (expected.getLastRowNum() < actual.getLastRowNum()) { int lastRowIndex = -1; if (expected instanceof HSSFSheet) { lastRowIndex = 0; } Iterator<Row> rowIterator = actual.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // ????? Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellTypeEnum() != CellType.BLANK) { lastRowIndex = row.getRowNum(); break; } } } if (expected.getLastRowNum() != lastRowIndex) { errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()), String.valueOf(lastRowIndex))); } } else { errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()), String.valueOf(actual.getLastRowNum()))); } } if (errors.isEmpty()) { for (int i = 0; i <= expected.getLastRowNum(); i++) { try { checkRow(expected.getRow(i), actual.getRow(i)); } catch (ReportsCheckException e) { errors.addAll(e.getCheckMessages()); } } } if (!errors.isEmpty()) { if (log.isErrorEnabled()) { for (CheckMessage message : errors) { log.error("?[" + message.getMessage() + "]"); log.error(":" + message.getExpected()); log.error(":" + message.getActual()); } } throw new ReportsCheckException(errors); } if (log.isDebugEnabled()) { log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)) + "] check end."); } }
From source file:org.databene.formats.xls.XLSLineIterator.java
License:Open Source License
public XLSLineIterator(Sheet sheet, boolean headersIncluded, boolean formatted, Converter<String, ?> stringPreprocessor) { this.emptyMarker = "'"; this.nullMarker = null; this.formatted = formatted; if (stringPreprocessor == null) stringPreprocessor = new NoOpConverter<String>(); this.stringPreprocessor = stringPreprocessor; rowIterator = sheet.rowIterator(); if (!rowIterator.hasNext()) { close();//from w ww.j a v a2 s .c o m return; } else if (headersIncluded) { parseHeaders(); } }
From source file:org.databene.formats.xls.XLSUtil.java
License:Open Source License
public static int getColumnCount(Sheet sheet) { int columnCount = 0; Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) columnCount = Math.max(columnCount, rowIterator.next().getLastCellNum()); return columnCount; }
From source file:org.dhatim.fastexcel.reader.BenchmarksTest.java
License:Apache License
@Benchmark public long apachePoi() throws IOException { try (Workbook wb = WorkbookFactory.create(openResource(FILE))) { org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); long sum = StreamSupport .stream(Spliterators.spliteratorUnknownSize(sheet.rowIterator(), Spliterator.ORDERED), false) .skip(1).mapToLong(r -> (long) r.getCell(0).getNumericCellValue()).sum(); assertEquals(RESULT, sum);//from w w w . j av a2s .com return sum; } }
From source file:org.diffkit.diff.sns.DKPoiSheet.java
License:Apache License
/** * @return the raw rows direct from the SS. They are unsorted and unfiltered * (e.g. header could be present) *///w w w . j av a 2 s.co m @SuppressWarnings("unchecked") private List<Row> getRows() throws IOException { if (_rows != null) return _rows; Sheet sheet = this.getSheet(); if (sheet == null) throw new IOException(String.format("no sheet!")); Iterator<Row> rowIterator = sheet.rowIterator(); _rows = IteratorUtils.toList(rowIterator); _log.info("row count->{}", (_rows == null) ? 0 : _rows.size()); return _rows; }
From source file:org.interpss.util.reader.ExcelFileReader.java
License:Open Source License
/** * Load the spreadsheet and process the row, one row at a time, * using the File processor //from www . j a va 2 s .c o m * * @param procer * @return sheet name */ public String processFile(IExcelFileProcessor<Row> procer) throws InterpssException { final File file = new File(this.filepath); Workbook wb; try { final InputStream stream = new FileInputStream(file); wb = WorkbookFactory.create(stream); } catch (Exception e) { ipssLogger.severe(e.toString()); throw new InterpssException(e.toString()); } if (wb.getNumberOfSheets() <= this.indexSheet) // index starts from 0 throw new InterpssException(this.sheetname + " does not exit in " + this.filepath); Sheet sheet = wb.getSheetAt(this.indexSheet); Iterator<Row> rowIter = sheet.rowIterator(); while (rowIter.hasNext()) { procer.processRow(rowIter.next()); } return sheet.getSheetName(); }
From source file:org.jeecgframework.poi.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 w w . java 2 s.c om*/ 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); Row row = null; Object object = null; String picId; // int count = 4; while (rows.hasNext() && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) { count++; row = rows.next(); // ???,?,? if ((row.getCell(params.getKeyIndex()) == null || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) { for (ExcelCollectionParams param : excelCollection) { try { addListContinue(object, param, row, titlemap, targetId, pictures, params); } catch (Exception e) { // TODO: handle exception Exception f = new Exception("" + count + "" + e.getMessage()); throw f; } } } else { object = 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 { try { saveFieldValue(params, object, cell, excelParams, titleString, row); } catch (Exception e) { // TODO: handle exception Exception f = new Exception("" + count + "" + e.getMessage()); throw f; } } } } for (ExcelCollectionParams param : excelCollection) { try { addListContinue(object, param, row, titlemap, targetId, pictures, params); } catch (Exception e) { // TODO: handle exception Exception f = new Exception("" + count + "" + e.getMessage()); throw f; } } collection.add(object); } catch (ExcelImportException e) { if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) { throw new ExcelImportException(e.getType(), e); } } } } return collection; }
From source file:org.lisapark.octopus.util.json.ExcelSardineUtils.java
License:Open Source License
public static void main(String[] args) { Map<String, Integer> prodCellIndexMap = Maps.newHashMap(); prodCellIndexMap.put(SHOP, 0);//from ww w .j av a2 s.co m prodCellIndexMap.put(SHIFT, 0); prodCellIndexMap.put(MACHINE, 0); prodCellIndexMap.put(PRODUCT, 0); prodCellIndexMap.put(PRODUCT_TYPE, 0); prodCellIndexMap.put(MATERIAL_TYPE, 0); prodCellIndexMap.put(RAW_MATERIAL, 4); prodCellIndexMap.put(TOTAL_MATERIALS, 5); prodCellIndexMap.put(TOTAL_PRODUCTS, 6); Map<String, Integer> wrhCellIndexMap = Maps.newHashMap(); wrhCellIndexMap.put(WAREHOUSE, 1); wrhCellIndexMap.put(ITEM, 1); wrhCellIndexMap.put(ITEM_TYPE, 1); wrhCellIndexMap.put(BEGINING, 0); wrhCellIndexMap.put(INCOMING, 1); wrhCellIndexMap.put(OUTGOING, 2); wrhCellIndexMap.put(ENDING, 3); try { String excelFile = "http://173.72.110.131:8080/WebDavServer/iPlast/Warehouse/"; // Get all xml files Sardine sardine = SardineFactory.begin("", ""); List<DavResource> resources = sardine.getResources(excelFile); for (DavResource res : resources) { String url = res.getPath(); //getAbsoluteUrl(); if (res.isDirectory()) { continue; } else { Map<String, String> props = res.getCustomProps(); if (props.get(PROCESSED) == null) { InputStream isData = sardine.get(url); HSSFWorkbook book = new HSSFWorkbook(isData); int index = 0; int increament = 1; if (book.getNumberOfSheets() > index) { if (increament == 0) { // increament = PROD_OUTLINE_INCREAMENT; increament = WRH_OUTLINE_INCREAMENT; } Sheet sheet = book.getSheetAt(index); if (sheet == null) { continue; } // Iterate through the rows. int splitRowNumber = 0; if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) { splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition(); } Map<String, Object> rowMap = Maps.newHashMap(); int start = 2; Row dateRow = sheet.getRow(8); int end = dateRow.getLastCellNum(); for (int dateShift = start; dateShift < end - 4; dateShift = dateShift + 4) { rowMap.put(DATE, formatDate(dateRow.getCell(dateShift).getStringCellValue())); System.out.println(dateRow.getCell(dateShift).getStringCellValue()); Sheet _sheet = book.getSheetAt(index); for (Iterator<Row> rowsIt = _sheet.rowIterator(); rowsIt.hasNext();) { Row row = rowsIt.next(); if (row.getPhysicalNumberOfCells() <= 0 || row.getRowNum() < splitRowNumber) { continue; } Cell cell = row.getCell(1); int indent = cell.getCellStyle().getIndention(); int absIndent = indent / increament; // if (processRowWrhSs(rowMap, row, wrhCellIndexMap, absIndent, dateShift)) { System.out.println(rowMap); } } } } props.put(PROCESSED, TRUE); sardine.setCustomProps(url, props, null); } else { System.out.println("Property PROCESSED: " + props.get(PROCESSED)); List<String> removeProps = new ArrayList<String>(1); removeProps.add(PROCESSED); sardine.setCustomProps(url, null, removeProps); } break; } } } catch (FileNotFoundException ex) { Exceptions.printStackTrace(ex); } catch (IOException ex) { Exceptions.printStackTrace(ex); } }
From source file:org.lisapark.octopus.util.json.JsonUtils.java
License:Open Source License
/** * /*from w w w . j a va 2 s . c o m*/ * @param sheet * @param ontology * @return * @throws JSONException */ private String jsonFromSS(Sheet sheet, int increment) throws JSONException { // Return null, if sheet is null if (sheet == null) return null; String sheetName = sheet.getSheetName(); if (sheetName.isEmpty()) { sheetName = SPREAD_SHEET_ROWS; } // Iterate through the rows. StringBuilder stringBuilderRows = new StringBuilder(); List<String> stack = new ArrayList<String>(); Boolean first = Boolean.TRUE; int splitRowNumber = 0; if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) { splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition(); } for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) { Row row = rowsIT.next(); if (row.getPhysicalNumberOfCells() > 0 && row.getRowNum() >= splitRowNumber) { continue; } String stringCells = jsonFromRowAsString(row); if (stringCells.isEmpty()) { continue; } String stringRow = jsonFromRowAsTreeNode(row, stringCells, stack, increment); if (first) { stringBuilderRows.append("[").append(stringRow); first = Boolean.FALSE; } else { stringBuilderRows.append(",").append(stringRow); } } // Get the JSON text. stringBuilderRows = stringBuilderRows.append("]" // + "}" ); return //"{" + "\"" + sheetName + "\"" + " : " + stringBuilderRows.toString(); }