List of usage examples for org.apache.poi.ss.usermodel Row getRowNum
int getRowNum();
From source file:misuExcel.excelAdd.java
License:Open Source License
private void examExcel02() { Log.info("excamExcel02"); if (excel != null) { if (names != null && names.size() > 0) { Sheet sheet_add = excel.getSheetAt(sheetNum); Sheet sheet = target.getSheetAt(sheetNum_target); Row row_add = sheet_add.getRow(cellNum); Row row = sheet.getRow(cellNum_target); initList(names.size());/*from w w w. ja v a 2 s . c o m*/ nones = new ArrayList<Integer>(); Boolean isAdd = false; if (row != null) { for (int i = addJpanel.ignore_Cell; i < row.getLastCellNum(); i++) { Cell cell = row.getCell(i); if (cell != null) { String str = getCellString(cell); Log.info(" " + str); for (int k = addJpanel.ignore_Celltar; k < row_add.getLastCellNum(); k++) { Cell cell2 = row_add.getCell(k); if (cell2 != null && str.equals(getCellString(cell2))) { isAdd = true; addList.get(0).add(k); break; } } } } //end names for if (!isAdd) { nones.add(row.getRowNum()); } isAdd = false; } // }//end for Log.info("examExcel is already"); } else { Log.warm("target is none"); } } else { Log.warm("excel is not exit"); } }
From source file:misuExcel.excelSplit.java
License:Open Source License
private void examExcel() { if (excel != null) { if (names != null && names.size() > 0) { Sheet sheet = excel.getSheetAt(sheetNum); initList(names.size());// www . j a va 2s.c o m nones = new ArrayList<Integer>(); Boolean isAdd = false; for (int j = splitJpanel.ignore_Row; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); if (row != null) { Cell cell = row.getCell(cellNum); String str = getCellString(cell); Log.info(" " + str); for (int i = 0; i < names.size(); i++) { if (str != null && str.equals(names.get(i))) { isAdd = true; splitList.get(i).add(row.getRowNum()); } } //end names for if (!isAdd) { nones.add(row.getRowNum()); } isAdd = false; } } //end for Log.info("examExcel is already"); } else { Log.warm("target is none"); } } else { Log.warm("excel is not exit"); } }
From source file:Model.Spreadsheet.java
public int getRowOfCurrentDate() { Date cellDate = new Date(); this.selectedCellDate = new GregorianCalendar(); this.currentDate = new GregorianCalendar(); for (Row row : worksheet) { if (row.getRowNum() > 3 && row.getRowNum() < 18) { cellDate = row.getCell(0).getDateCellValue(); selectedCellDate.setTime(cellDate); if ((selectedCellDate.get(MONTH) == currentDate.get(MONTH)) && selectedCellDate.get(DATE) == currentDate.get(DATE)) { return row.getRowNum(); }//ww w . java 2 s. c om } } return 0; }
From source file:mongodbutils.Filehandler.java
public boolean processFile(String filePath, MongodbConnection mc, String strdbName, String strCollName) throws IOException { this.mc = mc; FileInputStream fileIn = null; try {/*from w w w. ja va2s . com*/ fileIn = new FileInputStream(filePath); POIFSFileSystem fs = new POIFSFileSystem(fileIn); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Object objReturn = null; //Read in first row as field names Row rowH = sheet.getRow(sheet.getFirstRowNum()); String fields[] = new String[sheet.getRow(0).getLastCellNum()]; for (Cell cell : rowH) { objReturn = null; objReturn = getCellValue(cell); fields[cell.getColumnIndex()] = objReturn.toString(); } //loop thru all cells with values int rowcount = 0; for (Row row : sheet) { if (row.getRowNum() == 0) { continue; //skip first row } JSONObject obj = new JSONObject(); for (Cell cell : row) { if (fields.length < cell.getColumnIndex()) { continue; //only export column if we have header set } objReturn = null; objReturn = getCellValue(cell); if (!objReturn.toString().equals("")) { if (objReturn instanceof Double) { obj.put(fields[cell.getColumnIndex()], objReturn); } else if (objReturn instanceof String) { if (objReturn.toString().contains("$date")) { JSONParser parser = new JSONParser(); try { obj.put(fields[cell.getColumnIndex()], parser.parse(objReturn.toString())); } catch (ParseException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } } else { obj.put(fields[cell.getColumnIndex()], objReturn); } } } } rowcount += 1; mc.insertJSON(strdbName, strCollName, obj.toJSONString()); } return true; } catch (FileNotFoundException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, ex); } catch (Exception e) { Logger.getLogger(Filehandler.class.getName()).log(Level.SEVERE, null, e); } finally { if (fileIn != null) { fileIn.close(); } } return false; }
From source file:net.mcnewfamily.rmcnew.shared.Util.java
License:Open Source License
public static void copyXSSFSheet(XSSFSheet srcSheet, XSSFSheet destSheet) { if (srcSheet != null && destSheet != null) { for (Row srcRow : srcSheet) { Row destRow = destSheet.createRow(srcRow.getRowNum()); copyXSSFRow((XSSFRow) srcRow, (XSSFRow) destRow); }/*from w ww. ja va 2 s . c o m*/ //copySheetDrawings(srcSheet, destSheet); } else { throw new IllegalArgumentException("Cannot copy from / to null XSSFSheet!"); } }
From source file:net.sf.excelutils.ExcelParser.java
License:Apache License
/** * parse the cell/*from ww w. j av a2 s. co m*/ * * @param context data object * @param cell excel cell */ public static void parseCell(Object context, Sheet sheet, Row row, Cell cell) { String str = cell.getStringCellValue(); if (null == str || "".equals(str)) { return; } if (str.indexOf(VALUED_DELIM) < 0) return; boolean bJustExpr = str.length() == (str.length() - str.lastIndexOf(VALUED_DELIM)); boolean bMerge = "!".equals(str.substring(str.indexOf(VALUED_DELIM) + VALUED_DELIM.length(), str.indexOf(VALUED_DELIM) + VALUED_DELIM.length() + 1)); if (str.indexOf(VALUED_DELIM) < 0) return; Object value = parseStr(context, str); // replace the cell if (null != value) { if (bJustExpr && "java.lang.Integer".equals(value.getClass().getName())) { cell.setCellValue(Double.parseDouble(value.toString())); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (bJustExpr && "java.lang.Double".equals(value.getClass().getName())) { cell.setCellValue(((Double) value).doubleValue()); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (bJustExpr && "java.util.Date".equals(value.getClass().getName())) { cell.setCellValue((Date) value); } else if (bJustExpr && "java.lang.Boolean".equals(value.getClass().getName())) { cell.setCellValue(((Boolean) value).booleanValue()); cell.setCellType(Cell.CELL_TYPE_BOOLEAN); } else if (bJustExpr && Number.class.isAssignableFrom(value.getClass())) { cell.setCellValue(((Number) (value)).doubleValue()); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else { // cell.setEncoding(Workbook.ENCODING_UTF_16); POI3.2? cell.setCellValue(value.toString()); } } else { cell.setCellValue(""); } // merge the cell that has a "!" character at the expression if (row.getRowNum() - 1 >= sheet.getFirstRowNum() && bMerge) { Row lastRow = WorkbookUtils.getRow(row.getRowNum() - 1, sheet); Cell lastCell = WorkbookUtils.getCell(lastRow, cell.getColumnIndex()); boolean canMerge = false; if (lastCell.getCellType() == cell.getCellType()) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: canMerge = lastCell.getStringCellValue().equals(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: canMerge = lastCell.getBooleanCellValue() == cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: canMerge = lastCell.getNumericCellValue() == cell.getNumericCellValue(); break; } } if (canMerge) { CellRangeAddress region = new CellRangeAddress(lastRow.getRowNum(), row.getRowNum(), lastCell.getColumnIndex(), cell.getColumnIndex()); sheet.addMergedRegion(region); } } }
From source file:net.sf.excelutils.tags.EachTag.java
License:Apache License
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell) throws ExcelException { String expr = ""; String each = curCell.getStringCellValue(); LOG.debug("EachTag:" + each); StringTokenizer st = new StringTokenizer(each, " "); String widthstr = ""; String onstr = ""; int pos = 0;/* www . j a v a 2 s . c o m*/ while (st.hasMoreTokens()) { String str = st.nextToken(); if (pos == 1) { expr = str; } if (pos == 2 && !"on".equals(str)) { widthstr = str; } if (pos == 3 && !"on".equals(str)) { onstr = str; } if (pos == 4) { onstr = str; } pos++; } int[] widths = new int[0]; if (null != widthstr && !"".equals(widthstr)) { Object o = ExcelParser.parseStr(context, widthstr); if (null != o) { String[] s = o.toString().split(","); widths = new int[s.length]; for (int i = 0; i < widths.length; i++) { widths[i] = Integer.parseInt(s[i]); } } } Object obj = ExcelParser.parseExpr(context, expr); if (null == obj) return new int[] { 0, 0, 0 }; // by onstr get the property if (!"".equals(onstr)) { obj = ExcelParser.parseExpr(context, onstr); if (null == obj) return new int[] { 0, 0, 0 }; } // iterator properties Iterator it = ExcelParser.getIterator(obj); if (null == it) { if (obj instanceof DynaBean) { it = ExcelParser.getIterator(ExcelParser.getBeanProperties(((DynaBean) obj).getDynaClass())); } else { it = ExcelParser.getIterator(ExcelParser.getBeanProperties(obj.getClass())); } } if (null == it) { return new int[] { 0, 0, 0 }; } int index = 0; int arrayIndex = 0; int eachPos = curCell.getColumnIndex(); String modelName = expr.substring(ExcelParser.VALUED_DELIM.length(), expr.length() - ExcelParser.VALUED_DELIM2.length()); // restore the obj obj = ExcelParser.parseExpr(context, expr); while (it.hasNext()) { Object o = it.next(); String property = ""; if (o instanceof Field) { property = ((Field) o).getName(); } else if (o instanceof Map.Entry) { property = ((Map.Entry) o).getKey().toString(); } else if (o instanceof DynaProperty) { property = ((DynaProperty) o).getName(); } else if (null != o) { property = o.toString(); } // test the object is array/list or other if (obj.getClass().isArray() || obj instanceof Collection) { property = modelName + "[" + arrayIndex++ + "]"; } else { property = modelName + "." + property; } Object value = ExcelParser.getValue(context, property); if (null == value) value = ""; if (ExcelUtils.isCanShowType(value)) { // get cell merge count int width = 1; if (index < widths.length) { width = widths[index]; } else if (1 == widths.length) { width = widths[0]; } // get row merged of the curCell int rowMerged = 1; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress r = sheet.getMergedRegion(i); if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == curCell.getColumnIndex() && r.getLastColumn() == curCell.getColumnIndex()) { rowMerged = r.getLastRow() - r.getFirstRow() + 1; break; } } Cell cell = WorkbookUtils.getCell(curRow, eachPos); // shift the after cell if (index > 0) { WorkbookUtils.shiftCell(sheet, curRow, cell, 1, rowMerged); } if (width > 1) { Cell nextCell = WorkbookUtils.getCell(curRow, eachPos + 1); WorkbookUtils.shiftCell(sheet, curRow, nextCell, width - 1, rowMerged); } // copy the style of curCell for (int rownum = curRow.getRowNum(); rownum < curRow.getRowNum() + rowMerged; rownum++) { for (int i = 0; i < width; i++) { Row r = WorkbookUtils.getRow(rownum, sheet); Cell c = WorkbookUtils.getCell(r, eachPos + i); Cell cc = WorkbookUtils.getCell(r, curCell.getColumnIndex()); c.setCellStyle(cc.getCellStyle()); } } // merge cells if (width > 1 || rowMerged > 1) { sheet.addMergedRegion( new CellRangeAddress(curRow.getRowNum(), curRow.getRowNum() + rowMerged - 1, cell.getColumnIndex(), cell.getColumnIndex() + width - 1)); } cell.setCellValue("${" + property + "}"); ExcelParser.parseCell(context, sheet, curRow, cell); eachPos += width; index++; } } return new int[] { 0, 0, 0 }; }
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);//from w w w . j av a 2 s . c o m 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);//w w w . j a v a 2 s.c o m 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);//w w w. j av a2 s.c om sheet.removeRow(curRow); if (rowNum + 1 <= sheet.getLastRowNum()) { sheet.shiftRows(rowNum + 1, sheet.getLastRowNum(), -1, true, true); } return new int[] { 0, -1, 0 }; }