List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum
short getLastCellNum();
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ??/*from w w w.j a v a 2s. com*/ * * @param sheet * @param rangeAddress */ public static void clearCell(Sheet sheet, CellRangeAddress rangeAddress) { int fromRowIndex = rangeAddress.getFirstRow(); int fromColumnIndex = rangeAddress.getFirstColumn(); int toRowIndex = rangeAddress.getLastRow(); int toColumnIndex = rangeAddress.getLastColumn(); // ??? List<Row> removeRowList = new ArrayList<Row>(); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (fromRowIndex <= row.getRowNum() && row.getRowNum() <= toRowIndex) { Set<Cell> removeCellSet = new HashSet<Cell>(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (fromColumnIndex <= cell.getColumnIndex() && cell.getColumnIndex() <= toColumnIndex) { removeCellSet.add(cell); } } for (Cell cell : removeCellSet) { row.removeCell(cell); } } if (row.getLastCellNum() == -1) { removeRowList.add(row); } } for (Row row : removeRowList) { sheet.removeRow(row); } }
From source file:org.bbreak.excella.reports.listener.BreakAdapter.java
License:Open Source License
/** * ?????????/* w w w . ja v a 2 s .c om*/ */ protected void parseRow(Sheet sheet, SheetParser sheetParser, SheetData sheetData, Row row, int rowIndex) { int firstColNum = row.getFirstCellNum(); int lastColNum = row.getLastCellNum() - 1; for (int colIndex = firstColNum; colIndex <= lastColNum; colIndex++) { Cell cell = row.getCell(colIndex); if (cell != null) { if (cell.getCellTypeEnum() == CellType.STRING && cell.getStringCellValue().contains(BreakParamParser.DEFAULT_TAG)) { // if (isInMergedRegion(sheet, row, cell)) { setRowBreakMergedRegion(sheet, row, cell); } else { setRowBreak(sheet, row, cell); } } } } }
From source file:org.bbreak.excella.reports.listener.RemoveAdapter.java
License:Open Source License
@Override public void postParse(Sheet sheet, SheetParser sheetParser, SheetData sheetData) throws ParseException { int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); for (int rowIndex = firstRowNum; rowIndex <= lastRowNum; rowIndex++) { Row row = sheet.getRow(rowIndex); if (row != null) { int firstColNum = row.getFirstCellNum(); int lastColNum = row.getLastCellNum() - 1; boolean isRowFlag = false; for (int colIndex = firstColNum; colIndex <= lastColNum; colIndex++) { Cell cell = row.getCell(colIndex); if (cell != null) { if (cell.getCellTypeEnum() == CellType.STRING && cell.getStringCellValue().contains(RemoveParamParser.DEFAULT_TAG)) { // ?? String[] paramArray = getStrParam(sheet, rowIndex, colIndex); // ?? String removeUnit = paramArray[0]; // ?? row.removeCell(cell); // ???? if (removeUnit.equals("") || removeUnit.equals(ROW)) { removeRegion(sheet, rowIndex, -1); removeControlRow(sheet, rowIndex); isRowFlag = true; break; } else if (removeUnit.equals(CELL) || removeUnit.equals(COLUMN)) { // ??????? removeCellOrCol(paramArray, removeUnit, sheet, row, cell, rowIndex, colIndex); }//from w w w .java2s . co m lastColNum = row.getLastCellNum() - 1; colIndex--; } // ?? if (isControlRow(sheet, sheetParser, row, cell)) { removeControlRow(sheet, rowIndex); isRowFlag = true; break; } } } // ??? if (isRowFlag) { lastRowNum = sheet.getLastRowNum(); rowIndex--; } } } }
From source file:org.bbreak.excella.reports.listener.RemoveAdapter.java
License:Open Source License
/** * ??????/*ww w. j ava2s . com*/ * * @param row * @param cell * @param colIndex */ private void shiftLeft(Row row, Cell cell, int colIndex) { // int startCopyIndex = colIndex + 1; if (row == null) { return; } int finishCopyIndex = row.getLastCellNum() - 1; for (int copyColNum = startCopyIndex; copyColNum <= finishCopyIndex; copyColNum++) { // Cell fromCell = row.getCell(copyColNum); // Cell toCell = row.getCell(copyColNum - 1); if (fromCell != null) { if (toCell == null) { toCell = row.createCell(copyColNum - 1); } PoiUtil.copyCell(fromCell, toCell); row.removeCell(fromCell); } } }
From source file:org.bbreak.excella.reports.processor.ReportsWorkbookTest.java
License:Open Source License
protected List<ParsedReportInfo> parseSheet(ReportsTagParser<?> parser, Sheet sheet, ReportsParserInfo reportsParserInfo) throws ParseException { List<ParsedReportInfo> parsedList = new ArrayList<ParsedReportInfo>(); for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) { Row row = sheet.getRow(rowIndex); if (row == null) { continue; }/*from w w w. j a v a2 s.c o m*/ for (int columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) { Cell cell = row.getCell(columnIndex); if (cell == null) { continue; } if (parser.isParse(sheet, cell)) { parsedList.add(parser.parse(sheet, cell, reportsParserInfo)); } } } return parsedList; }
From source file:org.bbreak.excella.reports.ReportsTestUtil.java
License:Open Source License
/** * // w w w. ja v a2 s . co m * * @param expected * @param actual * @throws ReportsCheckException */ public static void checkRow(Row expected, Row actual) throws ReportsCheckException { List<CheckMessage> errors = new ArrayList<CheckMessage>(); // ---------------------- // ???? // ---------------------- if (expected == null && actual == null) { return; } if (expected == null) { if (actual.iterator().hasNext()) { errors.add(new CheckMessage("[" + actual.getRowNum() + "]", null, actual.toString())); throw new ReportsCheckException(errors); } else { return; } } if (actual == null) { if (expected.iterator().hasNext()) { errors.add(new CheckMessage("[" + expected.getRowNum() + "]", expected.toString(), null)); throw new ReportsCheckException(errors); } else { return; } } // ??(shiftRow??????????????????) // float adjustHight = 0f; // if ( hasHeightAdjustBorderCell( actual.getSheet().getRow( actual.getRowNum() - 1), actual, actual.getSheet().getRow( actual.getRowNum() + 1))) { // log.error( "true"); // adjustHight = 0.75f; // } // // if ( expected.getHeightInPoints() != actual.getHeightInPoints() + adjustHight) { // if ( log.isErrorEnabled()) { // log.error( "expectedROW[" + expected.getRowNum() + "]:" + expected.getHeightInPoints()); // log.error( "actualROW[" + actual.getRowNum() + "]:" + (actual.getHeightInPoints() + adjustHight)); // } // throw new Exception( "??"); // } // if (expected.getLastCellNum() != actual.getLastCellNum()) { errors.add(new CheckMessage("[" + expected.getRowNum() + "]", String.valueOf(expected.getLastCellNum()), String.valueOf(actual.getLastCellNum()))); throw new ReportsCheckException(errors); } // ??? for (int i = 0; i < expected.getLastCellNum(); i++) { try { checkCell(expected.getCell(i), actual.getCell(i)); } catch (ReportsCheckException e) { errors.addAll(e.getCheckMessages()); } } if (!errors.isEmpty()) { throw new ReportsCheckException(errors); } }
From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaExecuter.java
License:Open Source License
/** * ??????<BR>/*from www . j a va 2 s . com*/ * ???????<BR> * * @param targetSheet ? * @param targetColumnInfoList * @return * @throws ParseException */ protected List<Object> parseTargetSheet(Sheet targetSheet, SheetToJavaParseInfo sheetInfo, List<SheetToJavaSettingInfo> targetColumnInfoList) throws ParseException { // ?? List<Object> results = new ArrayList<Object>(); int logicalRowNum = sheetInfo.getLogicalNameRowNum() - 1; int valueStartRowNum = sheetInfo.getValueRowNum() - 1; int valueEndRowNum = targetSheet.getLastRowNum(); // ????index? Map<String, Integer> colLogicalNameMap = new HashMap<String, Integer>(); // colLogicalNameMap? Row row = targetSheet.getRow(logicalRowNum); if (row != null) { // ????? int firstColIdx = row.getFirstCellNum(); int lastColIdx = row.getLastCellNum(); for (int colIdx = firstColIdx; colIdx <= lastColIdx; colIdx++) { Cell cell = row.getCell(colIdx); if (cell != null) { try { // ??? String logicalCellValue = cell.getStringCellValue(); if (!logicalCellValue.startsWith(BookController.COMMENT_PREFIX)) { colLogicalNameMap.put(logicalCellValue, colIdx); } } catch (Exception e) { throw new ParseException(cell, e); } } } } // ????????????? List<Class<?>> classList = new ArrayList<Class<?>>(); // ?SettingInfo? Map<Class<?>, List<SheetToJavaSettingInfo>> settingInfoListMap = new HashMap<Class<?>, List<SheetToJavaSettingInfo>>(); // ??????? Map<Class<?>, List<String>> uniquePropertyListMap = new HashMap<Class<?>, List<String>>(); for (SheetToJavaSettingInfo settingInfo : targetColumnInfoList) { // ?? Class<?> clazz = settingInfo.getClazz(); List<SheetToJavaSettingInfo> settingInfoList = settingInfoListMap.get(clazz); if (settingInfoList == null) { // ????????? settingInfoList = new ArrayList<SheetToJavaSettingInfo>(); } List<String> uniquePropertyList = uniquePropertyListMap.get(clazz); if (uniquePropertyList == null) { // ????????? uniquePropertyList = new ArrayList<String>(); } // ?? settingInfoList.add(settingInfo); if (settingInfo.isUnique()) { uniquePropertyList.add(settingInfo.getPropertyName()); } // ??? if (!classList.contains(clazz)) { classList.add(clazz); } // ?? settingInfoListMap.put(clazz, settingInfoList); uniquePropertyListMap.put(clazz, uniquePropertyList); } // ??? for (Class<?> clazz : classList) { // ?? List<Object> objList = new ArrayList<Object>(); Object obj = null; try { // ??? for (int valueRowIdx = valueStartRowNum; valueRowIdx <= valueEndRowNum; valueRowIdx++) { Row valueRow = targetSheet.getRow(valueRowIdx); if (valueRow == null) { continue; } boolean isProcessRow = true; for (SheetToJavaListener propertyListener : sheetToJavaListeners) { if (!propertyListener.preProcessRow(valueRow)) { isProcessRow = false; } } if (!isProcessRow) { continue; } obj = Class.forName(clazz.getName()).newInstance(); // ??? List<SheetToJavaSettingInfo> settingInfoList = settingInfoListMap.get(clazz); for (SheetToJavaSettingInfo settingInfo : settingInfoList) { // ?? String propertyName = settingInfo.getPropertyName(); // Object value = settingInfo.getValue(); // ? Object settingValue = value; Cell valueCell = null; if (value instanceof String) { // ?? String settingValueStr = (String) value; if (settingValueStr.startsWith(TAG_PREFIX)) { // ?? if (settingValueStr.startsWith(TAG_LOGICAL_NAME_PREFIX)) { // ????? String logicalKey = TagUtil.getParam(settingValueStr, LNAME_TAG_PARAM_PREFIX, LNAME_TAG_PARAM_SUFFIX); Integer logicalKeyCol = colLogicalNameMap.get(logicalKey); if (logicalKeyCol == null) { Cell errorCell = null; for (SheetToJavaSettingInfo columnInfo : targetColumnInfoList) { if (columnInfo.getValue().equals(settingValueStr)) { errorCell = columnInfo.getValueCell(); } } throw new ParseException(errorCell, "????:" + logicalKey); } valueCell = valueRow.getCell(logicalKeyCol); if (valueCell != null) { Class<?> propertyClass = PropertyUtils.getPropertyType(obj, settingInfo.getPropertyName()); try { settingValue = PoiUtil.getCellValue(valueCell, propertyClass); } catch (RuntimeException e) { throw new ParseException(valueCell, "???????(" + propertyClass + ")", e); } } else { // ?null?? settingValue = null; valueCell = null; } } else { // ????? // ?? parseCustomProperty(valueCell, colLogicalNameMap, obj, valueRow, settingValueStr); // ?? continue; } } } // try { // ????? for (SheetToJavaListener propertyListener : sheetToJavaListeners) { propertyListener.preSetProperty(valueCell, obj, propertyName, settingValue); } PropertyUtils.setProperty(obj, propertyName, settingValue); // ???? for (SheetToJavaListener propertyListener : sheetToJavaListeners) { propertyListener.postSetProperty(valueCell, obj, propertyName, settingValue); } } catch (ParseException parseEx) { throw parseEx; } catch (RuntimeException e) { throw new ParseException(valueCell, "??????(" + propertyName + "=" + settingValue + "[" + settingValue.getClass().getCanonicalName() + "]" + ")", e); } } for (SheetToJavaListener propertyListener : sheetToJavaListeners) { if (!propertyListener.postProcessRow(valueRow, obj)) { isProcessRow = false; } } if (!isProcessRow) { continue; } List<String> uniquePropertyList = uniquePropertyListMap.get(clazz); if (!isDuplicateObj(obj, objList, uniquePropertyList)) { // ??????? objList.add(obj); } } // ???? results.addAll(objList); } catch (ParseException parseEx) { throw parseEx; } catch (Exception e) { throw new ParseException(e.toString()); } } return results; }
From source file:org.bbreak.excella.trans.tag.sheet2sql.SheetToSqlExecuter.java
License:Open Source License
/** * ?????Insert?Sql?<BR>/* w w w .j a va2 s . c o m*/ * ???????<BR> * * @param targetSheet ? * @param targetColumnInfoList * @return Sql * @throws ParseException */ protected List<Object> parseTargetSheet(Sheet targetSheet, SheetToSqlParseInfo sheetInfo, List<SheetToSqlSettingInfo> targetColumnInfoList) throws ParseException { // ?? List<Object> results = new ArrayList<Object>(); int logicalRowNum = sheetInfo.getLogicalNameRowNum() - 1; int valueStartRowNum = sheetInfo.getValueRowNum() - 1; int valueEndRowNum = targetSheet.getLastRowNum(); // ????index? Map<String, Integer> colLogicalNameMap = new HashMap<String, Integer>(); // colLogicalNameMap? Row row = targetSheet.getRow(logicalRowNum); if (row != null) { // ????? int firstColIdx = row.getFirstCellNum(); int lastColIdx = row.getLastCellNum(); for (int colIdx = firstColIdx; colIdx <= lastColIdx; colIdx++) { Cell cell = row.getCell(colIdx); if (cell != null) { try { // ??? String logicalCellValue = cell.getStringCellValue(); if (!logicalCellValue.startsWith(BookController.COMMENT_PREFIX)) { colLogicalNameMap.put(logicalCellValue, colIdx); } } catch (Exception e) { throw new ParseException(cell, e); } } } } // ????????????? List<String> tableNameList = new ArrayList<String>(); // ?SettingInfo? Map<String, List<SheetToSqlSettingInfo>> settingInfoListMap = new HashMap<String, List<SheetToSqlSettingInfo>>(); // ??????? Map<String, List<String>> uniqueColumnListMap = new HashMap<String, List<String>>(); for (SheetToSqlSettingInfo settingInfo : targetColumnInfoList) { // ?? String tableName = settingInfo.getTableName(); List<SheetToSqlSettingInfo> settingInfoList = settingInfoListMap.get(tableName); if (settingInfoList == null) { // ????????? settingInfoList = new ArrayList<SheetToSqlSettingInfo>(); } List<String> uniqueColumnList = uniqueColumnListMap.get(tableName); if (uniqueColumnList == null) { // ????????? uniqueColumnList = new ArrayList<String>(); } // ?? settingInfoList.add(settingInfo); if (settingInfo.isUnique()) { uniqueColumnList.add(settingInfo.getColumnName()); } // ??? if (!tableNameList.contains(tableName)) { tableNameList.add(tableName); } // ?? settingInfoListMap.put(tableName, settingInfoList); uniqueColumnListMap.put(tableName, uniqueColumnList); } // ????? for (String tableName : tableNameList) { // SQL??????? List<SheetToSqlInfo> infoList = new ArrayList<SheetToSqlInfo>(); SheetToSqlInfo info = null; // ??? for (int valueRowIdx = valueStartRowNum; valueRowIdx <= valueEndRowNum; valueRowIdx++) { Map<String, String> columnValueMap = new HashMap<String, String>(); List<String> columnNameList = new ArrayList<String>(); // SheetToSqlInfo? info = new SheetToSqlInfo(); info.setTableName(tableName); info.setColumnValueMap(columnValueMap); info.setColumnNameList(columnNameList); Row valueRow = targetSheet.getRow(valueRowIdx); if (valueRow == null) { continue; } // ??? List<SheetToSqlSettingInfo> settingInfoList = settingInfoListMap.get(tableName); for (SheetToSqlSettingInfo settingInfo : settingInfoList) { // ?? String columnName = settingInfo.getColumnName(); // Object value = settingInfo.getValue(); // String dataType = settingInfo.getDataType(); // ??value Object target = value; Cell cell = null; if (value instanceof String) { // ??? String settingValueStr = (String) value; if (settingValueStr.startsWith(TAG_LOGICAL_NAME_PREFIX)) { // ????? String logicalKey = TagUtil.getParam(settingValueStr, LNAME_TAG_PARAM_PREFIX, LNAME_TAG_PARAM_SUFFIX); Integer logicalKeyCol = colLogicalNameMap.get(logicalKey); if (logicalKeyCol == null) { throw new ParseException(settingInfo.getValueCell(), "????:" + logicalKey); } // ????????? cell = valueRow.getCell(logicalKeyCol); target = PoiUtil.getCellValue(cell); } } // ?? try { String valueStr = dataConverter.convert(target, dataType, settingInfo); // ?? columnValueMap.put(columnName, valueStr); // ???? columnNameList.add(columnName); } catch (ParseException parseEx) { // ???????????????? if (cell != null) { parseEx.setCell(cell); } throw parseEx; } } List<String> uniqueColumnList = uniqueColumnListMap.get(tableName); if (!isDuplicateObj(info, infoList, uniqueColumnList)) { // ??????? infoList.add(info); } } // SQL???????? List<String> sqlList = createInsertSqlList(infoList); results.addAll(sqlList); } return results; }
From source file:org.bdxjug.api.infrastructure.sheet.xlsx.XlsxSheet.java
License:Apache License
private static boolean isRowEmpty(Row row) { for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c);//w ww . j a v a 2 s . c o m if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) return false; } return true; }
From source file:org.cgiar.ccafs.marlo.action.center.capdev.ParticipantsAction.java
License:Open Source License
public String preLoadExcelFile() { request = ServletActionContext.getRequest(); try {//from ww w . j a va2 s .c om InputStream input = request.getInputStream(); wb = WorkbookFactory.create(input); Sheet sheet = wb.getSheetAt(0); Row firstRow = sheet.getRow(0); int totalRows = sheet.getLastRowNum(); int totalColumns = firstRow.getLastCellNum(); input.close(); } catch (IOException | EncryptedDocumentException | InvalidFormatException e) { e.printStackTrace(); } return SUCCESS; }