List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum
int getLastRowNum();
From source file:net.sf.excelutils.tags.ForeachTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { int forstart = curRow.getRowNum(); int forend = -1; int forCount = 0; String foreach = ""; boolean bFind = false; LOG.debug("ForeachTag: start=" + forstart); for (int rownum = forstart; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum);// ww w.j a va 2s . c om if (null == row) continue; for (short colnum = row.getFirstCellNum(); colnum <= row.getLastCellNum(); colnum++) { Cell cell = row.getCell(colnum, Row.RETURN_NULL_AND_BLANK); if (null == cell) continue; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String cellstr = cell.getStringCellValue(); // get the tag instance for the cellstr ITag tag = ExcelParser.getTagClass(cellstr); if (null != tag) { if (tag.hasEndTag()) { if (0 == forCount) { forstart = rownum; foreach = cellstr; } forCount++; break; } } if (cellstr.startsWith(KEY_END)) { forend = rownum; forCount--; if (forstart >= 0 && forend >= 0 && forend > forstart && forCount == 0) { bFind = true; } break; } } } if (bFind) break; } if (!bFind) return new int[] { 0, 0, 1 }; String properties = ""; String property = ""; // parse the collection an object StringTokenizer st = new StringTokenizer(foreach, " "); int pos = 0; while (st.hasMoreTokens()) { String str = st.nextToken(); if (pos == 1) { property = str; } if (pos == 3) { properties = str; } pos++; } // get collection Object collection = ExcelParser.parseStr(context, properties); if (null == collection) { return new int[] { 0, 0, 1 }; } // get the iterator of collection Iterator iterator = ExcelParser.getIterator(collection); // iterator int shiftNum = forend - forstart - 1; // set the start row number ExcelUtils.addValue(context, property + "StartRowNo", new Integer(forstart + 1)); int old_forend = forend; int propertyId = 0; int shift = 0; if (null != iterator) { while (iterator.hasNext()) { Object obj = iterator.next(); ExcelUtils.addValue(context, property, obj); // Iterator ID ExcelUtils.addValue(context, property + "Id", new Integer(propertyId)); // Index start with 1 ExcelUtils.addValue(context, property + "Index", new Integer(propertyId + 1)); // shift the #foreach #end block sheet.shiftRows(forstart, sheet.getLastRowNum(), shiftNum, true, true); // copy the body fo #foreach #end block WorkbookUtils.copyRow(sheet, forstart + shiftNum + 1, forstart, shiftNum); // parse shift = ExcelParser.parse(context, wb, sheet, forstart, forstart + shiftNum - 1); forstart += shiftNum + shift; forend += shiftNum + shift; propertyId++; } ExcelUtils.addValue(context, property + "Size", new Integer(propertyId)); } // set the end row number ExcelUtils.addValue(context, property + "EndRowNo", new Integer(forstart)); // delete #foreach #end block for (int rownum = forstart; rownum <= forend; rownum++) { sheet.removeRow(WorkbookUtils.getRow(rownum, sheet)); } // remove merged region in forstart & forend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= forstart && r.getLastRow() <= forend) { sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } if (forend + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(forend + 1, sheet.getLastRowNum(), -(forend - forstart + 1), true, true); } return new int[] { ExcelParser.getSkipNum(forstart, forend), ExcelParser.getShiftNum(old_forend, forstart), 1 }; }
From source file:net.sf.excelutils.tags.IfTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { int ifstart = curRow.getRowNum(); int ifend = -1; int ifCount = 0; String ifstr = ""; boolean bFind = false; for (int rownum = ifstart; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum);//from w w w . j a va 2 s . c om if (null == row) continue; for (short colnum = row.getFirstCellNum(); colnum <= row.getLastCellNum(); colnum++) { Cell cell = row.getCell(colnum, Row.RETURN_NULL_AND_BLANK); if (null == cell) continue; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String cellstr = cell.getStringCellValue(); // get the tag instance for the cellstr ITag tag = ExcelParser.getTagClass(cellstr); if (null != tag) { if (tag.hasEndTag()) { if (0 == ifCount) { ifstart = rownum; ifstr = cellstr; } ifCount++; break; } } if (cellstr.startsWith(KEY_END)) { ifend = rownum; ifCount--; if (ifstart >= 0 && ifend >= 0 && ifend > ifstart && ifCount == 0) { bFind = true; } break; } } } if (bFind) break; } if (!bFind) return new int[] { 0, 0, 1 }; // test if condition boolean bResult = false; // remove #if tag and get condition expression String expr = ifstr.trim().substring(KEY_IF.length()).trim(); // parse the condition expression expr = (String) ExcelParser.parseStr(context, expr, true); // use beanshell to eval expression value try { Interpreter in = createInterpreter(context); LOG.debug("IfTag test expr=" + expr); Object v = in.eval(expr); bResult = ((Boolean) v).booleanValue(); } catch (Exception e) { LOG.error("IfTag test expr error", e); bResult = false; } if (bResult) { // if condition is true // remove #if tag and #end tag only sheet.removeRow(WorkbookUtils.getRow(ifstart, sheet)); sheet.removeRow(WorkbookUtils.getRow(ifend, sheet)); // remove merged region in ifstart & ifend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == ifstart && r.getLastRow() == ifstart || r.getFirstRow() == ifend && r.getLastRow() == ifend) { sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } if (ifend + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(ifend + 1, sheet.getLastRowNum(), -1, true, true); } if (ifstart + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(ifstart + 1, sheet.getLastRowNum(), -1, true, true); } return new int[] { 1, -2, 1 }; } else { // if condition is false // remove #if #end block for (int rownum = ifstart; rownum <= ifend; rownum++) { sheet.removeRow(WorkbookUtils.getRow(rownum, sheet)); } // remove merged region in ifstart & ifend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= ifstart && r.getLastRow() <= ifend) { sheet.removeMergedRegion(i); // we have to back up now since we removed one i = i - 1; } } if (ifend + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(ifend + 1, sheet.getLastRowNum(), -(ifend - ifstart + 1), true, true); } return new int[] { ExcelParser.getSkipNum(ifstart, ifend), ExcelParser.getShiftNum(ifend, ifstart), 1 }; } }
From source file:net.sf.excelutils.tags.PageTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { int rowNum = curRow.getRowNum(); LOG.debug("#page at rownum = " + rowNum); sheet.setRowBreak(rowNum - 1);/*from www . java 2 s . c o m*/ sheet.removeRow(curRow); if (rowNum + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(rowNum + 1, sheet.getLastRowNum(), -1, true, true); } return new int[] { 0, -1, 0 }; }
From source file:net.sf.excelutils.tags.SheetTag.java
License:Apache License
/** * Parse #sheet detail in list by sheetName *//*from w w w .j a v a 2 s . c o m*/ public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { String sheetExpr = curCell.getStringCellValue(); StringTokenizer st = new StringTokenizer(sheetExpr, " "); String properties = ""; String property = ""; String sheetName = ""; // parse the collection an object int pos = 0; while (st.hasMoreTokens()) { String str = st.nextToken(); if (pos == 1) { property = str; } if (pos == 3) { properties = str; } if (pos == 5) { sheetName = str; } pos++; } // get collection Object collection = ExcelParser.parseStr(context, properties); if (null == collection) { return new int[] { 0, 0, 1 }; } // remove #sheet tag sheet.removeRow(curRow); // remove merged region in forstart & forend for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= curRow.getRowNum() && r.getLastRow() <= curRow.getRowNum()) { sheet.removeMergedRegion(i); i = i - 1; } } sheet.shiftRows(curRow.getRowNum() + 1, sheet.getLastRowNum(), -1, true, true); // get the iterator of collection Iterator iterator = ExcelParser.getIterator(collection); if (null != iterator) { // first obj, use parse method Object firstObj = null; if (iterator.hasNext()) { firstObj = iterator.next(); } // next obj, clone sheet and use parseSheet method while (iterator.hasNext()) { Object obj = iterator.next(); ExcelUtils.addValue(context, property, obj); try { int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet); // clone sheet Sheet cloneSheet = wb.cloneSheet(sheetIndex); // set cloneSheet name int cloneSheetIndex = WorkbookUtils.getSheetIndex(wb, cloneSheet); setSheetName(obj, wb, cloneSheetIndex, sheetName); // parse cloneSheet ExcelUtils.parseSheet(context, wb, cloneSheet); } catch (Exception e) { if (LOG.isErrorEnabled()) { LOG.error("parse sheet error", e); } } } if (null != firstObj) { ExcelUtils.addValue(context, property, firstObj); // set sheet name int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet); setSheetName(firstObj, wb, sheetIndex, sheetName); } } return new int[] { 0, -1, 0 }; }
From source file:net.sf.excelutils.WorkbookUtils.java
License:Apache License
/** * Set Print Area//from ww w .j a va 2s . co m * * @param wb * @param sheetIndex */ public static void setPrintArea(Workbook wb, int sheetIndex) { // sheet Sheet sheet = wb.getSheetAt(sheetIndex); if (null != sheet) { // #endRow Row endRow = sheet.getRow(sheet.getLastRowNum()); if (null != endRow) { Cell cell = WorkbookUtils.getCell(endRow, 0); String cellStr = cell.getStringCellValue(); cellStr = cellStr == null ? "" : cellStr.trim(); if (cellStr.startsWith(EndRowTag.KEY_ENDROW)) { // search #endColumn int endColumn = endRow.getLastCellNum(); for (int i = endRow.getLastCellNum(); i >= endRow.getFirstCellNum(); i--) { Cell endCell = WorkbookUtils.getCell(endRow, i); String endCellStr = endCell.getStringCellValue(); endCellStr = endCellStr == null ? "" : endCellStr.trim(); if (endCellStr.startsWith(EndRowTag.KEY_ENDCOLUMN)) { endColumn = i; break; } } wb.setPrintArea(sheetIndex, endRow.getFirstCellNum(), endColumn, sheet.getFirstRowNum(), sheet.getLastRowNum() - 1); sheet.removeRow(endRow); } } } }
From source file:net.sf.taverna.t2.activities.spreadsheet.ExcelSpreadsheetReader.java
License:Open Source License
public void read(InputStream inputStream, Range rowRange, Range columnRange, boolean ignoreBlankRows, SpreadsheetRowProcessor rowProcessor) throws SpreadsheetReadException { Workbook workbook;/*ww w. java 2 s . co m*/ try { workbook = WorkbookFactory.create(inputStream); } catch (InvalidFormatException e) { throw new SpreadsheetReadException("The file does not have a compatible spreadsheet format", e); } catch (IOException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } catch (IllegalArgumentException e) { throw new SpreadsheetReadException("The spreadsheet stream could not be read", e); } DataFormatter dataFormatter = new DataFormatter(); workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK); Sheet sheet = workbook.getSheetAt(0); if (rowRange.getEnd() < 0) { rowRange.setEnd(sheet.getLastRowNum()); logger.debug("No end of row range specified, setting to " + rowRange.getEnd()); } SortedMap<Integer, String> currentDataRow = new TreeMap<Integer, String>(); for (int rowIndex = rowRange.getStart(); rowIndex <= rowRange.getEnd(); rowIndex++) { boolean blankRow = true; if (rowRange.contains(rowIndex)) { Row row = sheet.getRow(rowIndex); for (int columnIndex = columnRange.getStart(); columnIndex <= columnRange.getEnd(); columnIndex++) { if (columnRange.contains(columnIndex)) { String value = null; if (row != null) { Cell cell = row.getCell(columnIndex); if (cell != null) { value = getCellValue(cell, dataFormatter); } } if (value != null) { blankRow = false; } currentDataRow.put(columnIndex, value); if (columnIndex == columnRange.getEnd()) { if (!ignoreBlankRows || !blankRow) { rowProcessor.processRow(rowIndex, currentDataRow); } currentDataRow = new TreeMap<Integer, String>(); } } } } } }
From source file:no.hild1.bank.KonverterMottagerregister.java
License:Apache License
private void processInnland(Sheet sheet) throws InvalidFormatException { if (sheet.getPhysicalNumberOfRows() > 0) { int lastRowNum = 0; Row row = null;/* www.j a va 2 s. c om*/ lastRowNum = sheet.getLastRowNum(); System.out.println("Innland har " + lastRowNum + " rader"); row = sheet.getRow(0); int lastCellNum = row.getLastCellNum(); System.out.println("Innland rad 0 har " + lastCellNum + " celler"); System.out.println("Kjrer tilregnelighetssjekk"); String KO = text(row, INNLAND_KONTONR); String LE = text(row, INNLAND_LEVNR); String NA = text(row, INNLAND_NAVN); String A1 = text(row, INNLAND_ADDR1); String A2 = text(row, INNLAND_ADDR2); String NR = text(row, INNLAND_POSTNUMMER); String ST = text(row, INNLAND_POSTSTED); if (KO.equals("Kontonr") && LE.equals("Lev.nr") && NA.equals("Navn") && A1.equals("Adresse 1") && A2.equals("Adresse 2") && NR.equals("Postnr.") && ST.equals("Poststed")) { System.out.println("Frste rad ser OK ut, fortsetter"); for (int j = 1; j <= lastRowNum; j++) { System.out.println("Prosesserer rad " + j + " av " + lastRowNum); row = sheet.getRow(j); this.rowToXML(row, j); } } else { throw new InvalidFormatException("Kjenner ikke igjen frste rad\n" + "Skulle vrt (1), fant (2)\n(1) 'Kontonr' 'Lev.nr' 'Navn' 'Adresse 1' 'Adresse 2' 'Postnr.' 'Poststed'\n'" + "" + KO + "' '" + LE + "' '" + NA + "' '" + A1 + "' '" + A2 + "' '" + NR + "' '" + ST + "'"); } } }
From source file:no.hild1.excelsplit.ES.java
private void processSheet(Sheet sheet) throws IOException { // Er det 2 eller flere rows (dvs minst header + 1) i arket? if (sheet.getPhysicalNumberOfRows() >= 2) { int lastRowNum = 0; Row header = null;//from w ww . j a v a2 s .c o m lastRowNum = sheet.getLastRowNum(); // hent siste row det er skrevet i System.out.println("Regnearket har " + lastRowNum + " rader"); header = sheet.getRow(0); int lastCellNum = header.getLastCellNum(); System.out.println("Header har " + lastCellNum + " kolonner"); String header1 = text(header, 0); String header2 = text(header, 1); String header3 = text(header, 2); String header4 = text(header, 3); if (header1.equals("Header 1") && header2.equals("Header 2") && header3.equals("Header 3") && header4.equals("Header 4")) { System.out.println("Frste rad ser OK ut, fortsetter"); Row row = null; Map<String, XSSFWorkbook> header2types = null; for (int j = 1; j <= lastRowNum; j++) { System.out.println("Prosesserer rad " + j + " av " + lastRowNum); row = sheet.getRow(j); handleRow(row, j, header, header2types); } for (Map.Entry<String, XSSFWorkbook> entry : header2types.entrySet()) { FileOutputStream out = new FileOutputStream("Some_name_" + entry.getKey() + ".xlss"); entry.getValue().write(out); out.close(); } } } }
From source file:no.hild1.excelsplit.ES.java
private void handleRow(Row row, int j, Row header, Map<String, XSSFWorkbook> header2types) throws IOException { int HEADER1 = 0, HEADER2 = 1, HEADER3 = 2, HEADER4 = 3; String header2forthisrow = text(row, HEADER2); XSSFWorkbook w = null;//www .j a v a 2s .com Sheet s = null; Row r = null; if (!header2types.containsKey(header2forthisrow)) { w = new XSSFWorkbook(); s = w.createSheet(); r = s.createRow(0); // insert "header" into "r" somehow header2types.put(header2forthisrow, w); } else { w = header2types.get(header2forthisrow); s = w.getSheetAt(0); } r = s.createRow(s.getLastRowNum() + 1); // insert data "row" into "r" somehow }
From source file:nu.mine.kino.projects.utils.POITest.java
License:Open Source License
@Test public void test1() throws InvalidFormatException, IOException { Sheet sheet = workbook.getSheetAt(0); // int[] rowBreaks = sheet.getRowBreaks(); Row row = sheet.getRow(25);/*from w w w .j a v a2s . c o m*/ Cell cell = row.getCell(24); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); break; default: System.out.println("cellType=" + cell.getCellType()); break; } System.out.println("value: " + cell); System.out.println(sheet.getRow(46).getCell(4)); final int rowMax = sheet.getLastRowNum(); System.out.println(rowMax); }