List of usage examples for org.apache.poi.ss.usermodel Row getFirstCellNum
short getFirstCellNum();
From source file:net.bafeimao.umbrella.support.data.entity.ExcelEntityParser.java
License:Apache License
private int getColumnIndex(Sheet sheet, String colName) { Map<String, Integer> columnIndexesMap = sheetColumnIndexesMap.get(sheet.getSheetName()); if (columnIndexesMap == null) { columnIndexesMap = new HashMap<String, Integer>(); Row titleRow = sheet.getRow(1); int colNum = titleRow.getLastCellNum(); for (int i = titleRow.getFirstCellNum(); i < colNum; i++) { if (titleRow.getCell(i) != null) { columnIndexesMap.put(titleRow.getCell(i).getStringCellValue(), i); }/*from w w w . j a va2 s .co m*/ } sheetColumnIndexesMap.put(sheet.getSheetName(), columnIndexesMap); } Integer index = columnIndexesMap.get(colName); return index == null ? -1 : index; }
From source file:net.sf.ahtutils.report.util.DataUtil.java
public static void debugRow(Sheet sheet, Integer rowIndex) { // Using a StringBuffer to create one line with all column titles StringBuffer sb = new StringBuffer(); sb.append("Debugging Row " + rowIndex + " ... "); // Selecting first row since this should be the place where the column titles should be placed Row firstRow = sheet.getRow(rowIndex); // Iterating through all cells in first row for (short i = firstRow.getFirstCellNum(); i < firstRow.getLastCellNum(); i++) { Cell cell = firstRow.getCell(i); // Get the Cell Value as Object Object object = DataUtil.getCellValue(cell); // Get a String representation of the value String cellValue = getStringValue(object); // Add the content of the cell to StringBuffer sb.append("Column " + i + ": '" + cellValue + "' "); }//from w w w. j a v a 2 s .co m // Show the StringBuffer content in logging logger.info(sb.toString()); }
From source file:net.sf.excelutils.ExcelParser.java
License:Apache License
/** * parse the Excel template/*from w ww . j a va 2 s . co m*/ * * @param context data object * @param sheet Excel sheet * @param fromRow the start * @param toRow the end * @return int skip number */ public static int parse(Object context, Workbook wb, Sheet sheet, int fromRow, int toRow) throws ExcelException { int[] shift = new int[] { 0, 0, 0 }; // {SkipNum, ShiftNum, break flag} int shiftCount = 0; int rownum = fromRow; while (rownum <= toRow) { // shift rownum += shift[1] + shift[0]; toRow += shift[1]; if (rownum > toRow) break; shift[0] = 0; shift[1] = 0; shift[2] = 0; Row row = sheet.getRow(rownum); // set current row number ExcelUtils.addValue(context, "currentRowNo", new Integer(rownum + 1)); if (null == row) { rownum++; 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) { continue; } // if the cell is null then continue String cellstr = cell.getStringCellValue(); if (null == cellstr || "".equals(cellstr)) { continue; } ITag tag = getTagClass(cellstr); if (null != tag) { shift = tag.parseTag(context, wb, sheet, row, cell); } else { parseCell(context, sheet, row, cell); } shiftCount += shift[1]; if (shift[2] == 1) break; } rownum++; } return shiftCount; }
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); 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; }//from w w w . j a v a 2 s. com 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); 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; }// w w w .ja v a 2 s. c om 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.WorkbookUtils.java
License:Apache License
/** * Set Print Area//from ww w. j ava2 s . c o 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.excelutils.WorkbookUtils.java
License:Apache License
/** * copy row//from w ww . j ava 2 s.c o m * * @param sheet * @param from begin of the row * @param to destination fo the row * @param count count of copy */ public static void copyRow(Sheet sheet, int from, int to, int count) { for (int rownum = from; rownum < from + count; rownum++) { Row fromRow = sheet.getRow(rownum); Row toRow = getRow(to + rownum - from, sheet); if (null == fromRow) return; toRow.setHeight(fromRow.getHeight()); toRow.setHeightInPoints(fromRow.getHeightInPoints()); int lastCellNum = fromRow.getLastCellNum(); lastCellNum = lastCellNum > 255 ? 255 : lastCellNum; for (int i = fromRow.getFirstCellNum(); i <= lastCellNum && i >= 0; i++) { Cell fromCell = getCell(fromRow, i); Cell toCell = getCell(toRow, i); // toCell.setEncoding(fromCell.getEncoding()); toCell.setCellStyle(fromCell.getCellStyle()); toCell.setCellType(fromCell.getCellType()); switch (fromCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: toCell.setCellFormula(fromCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: toCell.setCellValue(fromCell.getStringCellValue()); break; default: } } } // copy merged region List shiftedRegions = new ArrayList(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() >= from && r.getLastRow() < from + count) { CellRangeAddress n_r = new CellRangeAddress(r.getFirstRow() + to - from, r.getLastRow() + to - from, r.getFirstColumn(), r.getLastColumn()); shiftedRegions.add(n_r); } } // readd so it doesn't get shifted again Iterator iterator = shiftedRegions.iterator(); while (iterator.hasNext()) { CellRangeAddress region = (CellRangeAddress) iterator.next(); sheet.addMergedRegion(region); } }
From source file:net.unit8.axebomber.parser.Sheet.java
License:Apache License
public Cell findCell(Pattern p, boolean scanAll) { int initialRowNum = (scanAll || tableHeader == null) ? sheet.getFirstRowNum() : tableHeader.getBodyRowIndex(); for (int i = initialRowNum; i <= sheet.getLastRowNum(); i++) { org.apache.poi.ss.usermodel.Row row = sheet.getRow(i); if (row == null) continue; for (short j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) { org.apache.poi.ss.usermodel.Cell cell = row.getCell(j); if (cell == null) continue; Matcher m = p.matcher(cell.getStringCellValue()); if (m.find()) { return new CellImpl(cell); }//from w ww . ja v a 2 s. c o m } } throw new CellNotFoundException(p.pattern() + " is not found"); }
From source file:net.unit8.axebomber.parser.Sheet.java
License:Apache License
public Cell findCell(String value, boolean scanAll) { int initialRowNum = (scanAll || tableHeader == null) ? sheet.getFirstRowNum() : tableHeader.getBodyRowIndex(); for (int i = initialRowNum; i <= sheet.getLastRowNum(); i++) { org.apache.poi.ss.usermodel.Row row = sheet.getRow(i); if (row == null) continue; for (short j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) { org.apache.poi.ss.usermodel.Cell cell = row.getCell(j); if (cell == null) continue; Cell cellImpl = new CellImpl(cell); if (value.equals(cellImpl.toString())) { return cellImpl; }/*from w w w. ja v a 2 s.c om*/ } } throw new CellNotFoundException(value + " is not found"); }
From source file:net.unit8.axebomber.parser.TableHeader.java
License:Apache License
private void scanColumnLabel(Cell beginCell) { int rowIndex = beginCell.getRowIndex(); org.apache.poi.ss.usermodel.Row row = sheet.getRow(rowIndex); String currentValue = null;//from w w w . j a v a 2 s . com for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { Cell cell = getCell(i, rowIndex); if (cell == null) continue; String label = StringUtils.remove(StringUtils.trim(cell.toString()), "\n"); if (!cell.toString().equals("")) { labelColumns.put(label, i); currentValue = label; } if (currentValue != null) columnLabels.put(i, currentValue); } }