List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum
int getLastRowNum();
From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaParser.java
License:Open Source License
/** * ?//from w ww.j a v a2s.c o m * * @param sheet * @param tagCell ??? * @param data TransProcessor?processBook, processSheet?<BR> * ????TagParser.parse??????<BR> * @return ? * @throws ParseException */ @Override public List<SheetToJavaParseInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // ? int tagRowIdx = tagCell.getRowIndex(); int tagColIdx = tagCell.getColumnIndex(); int valueRowFromIdx; int valueRowToIdx = sheet.getLastRowNum(); // ???? String settingTagName = getTag() + DEFAULT_SETTING_SUFFIX; try { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM, DEFAULT_DATA_ROW_FROM_ADJUST); if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM); } // ? valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx); if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ??? if (valueRowFromIdx > valueRowToIdx) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO); } // ???? if (paramDef.containsKey(PARAM_SETTING_TAG_NAME)) { settingTagName = paramDef.get(PARAM_SETTING_TAG_NAME); } // ? if (paramDef.containsKey(PARAM_RESULT_KEY)) { // ??????? throw new ParseException(tagCell, PARAM_RESULT_KEY + "????????"); } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } List<SheetToJavaParseInfo> sheetInfoList = new ArrayList<SheetToJavaParseInfo>(); // ?? int sheetNameColIdx = tagColIdx++; // ???No int logicalRowColIdx = tagColIdx++; // No int dataRowColIdx = tagColIdx; // ????? Workbook workbook = sheet.getWorkbook(); // ??? for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { // ?? Cell sheetNameCell = row.getCell(sheetNameColIdx); Cell logicalRowNumCell = row.getCell(logicalRowColIdx); Cell valueRowNumCell = row.getCell(dataRowColIdx); // ? if ((sheetNameCell == null) && (logicalRowNumCell == null) && (valueRowNumCell == null)) { // ????null?? continue; } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null) || ("".equals(sheetNameCell.getStringCellValue()))) { // ????????? continue; } else { // ???? Cell requiredErrorCell = null; if (logicalRowNumCell == null) { requiredErrorCell = row.createCell(logicalRowColIdx); } else if (valueRowNumCell == null) { requiredErrorCell = row.createCell(dataRowColIdx); } // ?????? if (requiredErrorCell != null) { throw new ParseException(requiredErrorCell, "?null??"); } } // ???No? int logicalRowNum; try { logicalRowNum = (Integer) PoiUtil.getCellValue(logicalRowNumCell, Integer.class); } catch (Exception e) { throw new ParseException(logicalRowNumCell, e); } // No? int valueRowNum; try { valueRowNum = (Integer) PoiUtil.getCellValue(valueRowNumCell, Integer.class); } catch (Exception e) { throw new ParseException(valueRowNumCell, e); } // ??? String sheetName = sheetNameCell.getStringCellValue(); if (workbook.getSheet(sheetName) == null) { throw new ParseException(sheetNameCell, "[" + sheetName + "]????"); } // SheetToJavaParseInfo sheetInfo = new SheetToJavaParseInfo(); sheetInfo.setSettingTagName(settingTagName); sheetInfo.setSettingTagNameCell(tagCell); sheetInfo.setSheetName(sheetName); sheetInfo.setSheetNameCell(sheetNameCell); sheetInfo.setLogicalNameRowNum(logicalRowNum); sheetInfo.setLogicalNameRowNumCell(logicalRowNumCell); sheetInfo.setValueRowNum(valueRowNum); sheetInfo.setValueRowNumCell(valueRowNumCell); sheetInfoList.add(sheetInfo); } } return sheetInfoList; }
From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaSettingParser.java
License:Open Source License
/** * ?/*from w w w.j av a 2s. c o m*/ * * @param sheet * @param tagCell ??? * @param data TransProcessor?processBook, processSheet?<BR> * ????TagParser.parse??????<BR> * @return ? * @throws ParseException */ @Override public List<SheetToJavaSettingInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // ? int tagRowIdx = tagCell.getRowIndex(); int tagColIdx = tagCell.getColumnIndex(); // int valueRowFromIdx; int valueRowToIdx = sheet.getLastRowNum(); try { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM, DEFAULT_DATA_ROW_FROM_ADJUST); if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM); } // ? valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx); if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ??? if (valueRowFromIdx > valueRowToIdx) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO); } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } List<SheetToJavaSettingInfo> sheetSettingInfoList = new ArrayList<SheetToJavaSettingInfo>(); // ?? int sheetNameColIdx = tagColIdx++; // int valueColIdx = tagColIdx++; // int classColIdx = tagColIdx++; // int propertyNameColIdx = tagColIdx++; // ? int uniqueColIdx = tagColIdx++; // ????? Workbook workbook = sheet.getWorkbook(); // ??? for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { // ?? Cell sheetNameCell = row.getCell(sheetNameColIdx); Cell valueCell = row.getCell(valueColIdx); Cell classCell = row.getCell(classColIdx); Cell propertyNameCell = row.getCell(propertyNameColIdx); Cell uniqueCell = row.getCell(uniqueColIdx); // ? if ((sheetNameCell == null) && (valueCell == null) && (classCell == null) && (propertyNameCell == null) && (uniqueCell == null)) { // ????null?? continue; } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null) || ("".equals(sheetNameCell.getStringCellValue()))) { // ????????? continue; } else { // ???? Cell requiredErrorCell = null; if (classCell == null) { // ?null?? requiredErrorCell = row.createCell(classColIdx); } // ?? if (requiredErrorCell != null) { throw new ParseException(requiredErrorCell, "?null??"); } } // ?? SheetToJavaSettingInfo settingInfo = new SheetToJavaSettingInfo(); // ???? String sheetName = sheetNameCell.getStringCellValue(); if (workbook.getSheet(sheetName) == null) { throw new ParseException(sheetNameCell, "[" + sheetName + "]????"); } // ?? settingInfo.setSheetName(sheetName); settingInfo.setSheetNameCell(sheetNameCell); // ? try { settingInfo.setClazz(Class.forName(classCell.getStringCellValue())); settingInfo.setClazzCell(classCell); } catch (ClassNotFoundException e) { throw new ParseException(classCell, e); } // Object value = PoiUtil.getCellValue(valueCell); settingInfo.setValueCell(valueCell); // ????? boolean isValueTag = false; // ???????? boolean isValueLogicalNameTag = false; if (value instanceof String) { // ?? String valueStr = (String) value; if ((valueStr).startsWith(SheetToJavaExecuter.TAG_PREFIX)) { // ?? isValueTag = true; if ((valueStr).startsWith(SheetToJavaExecuter.TAG_LOGICAL_NAME_PREFIX)) { // ????? isValueLogicalNameTag = true; } } } if (!isValueTag || isValueLogicalNameTag) { // ????????? // ?? Cell requiredErrorCell = null; if (propertyNameCell == null) { requiredErrorCell = row.createCell(propertyNameColIdx); } if (requiredErrorCell != null) { throw new ParseException(requiredErrorCell, "?null??"); } // ? settingInfo.setPropertyName(propertyNameCell.getStringCellValue()); settingInfo.setPropertyNameCell(propertyNameCell); // ? Class<?> propertyClass = null; try { Object obj = settingInfo.getClazz().newInstance(); propertyClass = PropertyUtils.getPropertyType(obj, settingInfo.getPropertyName()); } catch (Exception e) { throw new ParseException(propertyNameCell, e); } if (propertyClass == null) { throw new ParseException(propertyNameCell, "?:" + settingInfo.getPropertyName()); } // ???? if (uniqueCell != null) { if (uniqueCell.getStringCellValue() != null && uniqueCell.getStringCellValue().equals(UNIQUE_PROPERTY_MARK)) { settingInfo.setUnique(true); settingInfo.setUniqueCell(uniqueCell); } } } else { // ?? // ????? try { TagUtil.getParams((String) value); } catch (Exception e) { throw new ParseException(valueCell, e); } } // ?????? boolean checkTypeFlag = false; if (value instanceof String) { if (!isValueTag) { // ?? checkTypeFlag = true; } } else { // ?? if (value != null) { // null?? checkTypeFlag = true; } } // ? if (checkTypeFlag) { // ???? Object obj; try { obj = settingInfo.getClazz().newInstance(); Class<?> propertyClass = PropertyUtils.getPropertyType(obj, settingInfo.getPropertyName()); value = PoiUtil.getCellValue(valueCell, propertyClass); } catch (Exception e) { throw new ParseException(valueCell, e); } } settingInfo.setValue(value); // ???? sheetSettingInfoList.add(settingInfo); } } return sheetSettingInfoList; }
From source file:org.bbreak.excella.trans.tag.sheet2sql.SheetToSqlExecuter.java
License:Open Source License
/** * ?????Insert?Sql?<BR>/*from w w w . java2 s .c om*/ * ???????<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.bbreak.excella.trans.tag.sheet2sql.SheetToSqlParser.java
License:Open Source License
/** * ?/*from www.java 2 s. c om*/ * * @param sheet * @param tagCell ??? * @param data TransProcessor?processBook, processSheet?<BR> * ????TagParser.parse??????<BR> * @return ? * @throws ParseException */ @Override public List<SheetToSqlParseInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // ? int tagRowIdx = tagCell.getRowIndex(); int tagColIdx = tagCell.getColumnIndex(); int valueRowFromIdx; int valueRowToIdx = sheet.getLastRowNum(); // ???? String settingTagName = getTag() + DEFAULT_SETTING_SUFFIX; try { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM, DEFAULT_DATA_ROW_FROM_ADJUST); if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM); } // ? valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx); if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ??? if (valueRowFromIdx > valueRowToIdx) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO); } // ???? if (paramDef.containsKey(PARAM_SETTING_TAG_NAME)) { settingTagName = paramDef.get(PARAM_SETTING_TAG_NAME); } // ? if (paramDef.containsKey(PARAM_RESULT_KEY)) { // ??????? throw new ParseException(tagCell, PARAM_RESULT_KEY + "????????"); } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } List<SheetToSqlParseInfo> sheetInfoList = new ArrayList<SheetToSqlParseInfo>(); // ?? int sheetNameColIdx = tagColIdx++; // ???No int logicalRowColIdx = tagColIdx++; // No int dataRowColIdx = tagColIdx; // ????? Workbook workbook = sheet.getWorkbook(); // ??? for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { // ?? Cell sheetNameCell = row.getCell(sheetNameColIdx); Cell logicalRowNumCell = row.getCell(logicalRowColIdx); Cell valueRowNumCell = row.getCell(dataRowColIdx); // ? if ((sheetNameCell == null) && (logicalRowNumCell == null) && (valueRowNumCell == null)) { // ????null?? continue; } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null) || ("".equals(sheetNameCell.getStringCellValue()))) { // ????????? continue; } else { // ???? Cell requiredErrorCell = null; if (logicalRowNumCell == null) { requiredErrorCell = row.createCell(logicalRowColIdx); } else if (valueRowNumCell == null) { requiredErrorCell = row.createCell(dataRowColIdx); } // ?????? if (requiredErrorCell != null) { throw new ParseException(requiredErrorCell, "?null??"); } } // ???No? int logicalRowNum; try { logicalRowNum = (Integer) PoiUtil.getCellValue(logicalRowNumCell, Integer.class); } catch (Exception e) { throw new ParseException(logicalRowNumCell, e); } // No? int valueRowNum; try { valueRowNum = (Integer) PoiUtil.getCellValue(valueRowNumCell, Integer.class); } catch (Exception e) { throw new ParseException(valueRowNumCell, e); } // ??? String sheetName = sheetNameCell.getStringCellValue(); if (workbook.getSheet(sheetName) == null) { throw new ParseException(sheetNameCell, "[" + sheetName + "]????"); } // SheetToSqlParseInfo sheetInfo = new SheetToSqlParseInfo(); sheetInfo.setSettingTagName(settingTagName); sheetInfo.setSheetName(sheetName); sheetInfo.setLogicalNameRowNum(logicalRowNum); sheetInfo.setValueRowNum(valueRowNum); sheetInfoList.add(sheetInfo); } } return sheetInfoList; }
From source file:org.bbreak.excella.trans.tag.sheet2sql.SheetToSqlSettingParser.java
License:Open Source License
/** * ?//from w w w. j av a2 s .co m * * @param sheet * @param tagCell ??? * @param data TransProcessor?processBook, processSheet?<BR> * ????TagParser.parse??????<BR> * @return ? * @throws ParseException */ @Override public List<SheetToSqlSettingInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // ? int tagRowIdx = tagCell.getRowIndex(); int tagColIdx = tagCell.getColumnIndex(); // int valueRowFromIdx; int valueRowToIdx = sheet.getLastRowNum(); try { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM, DEFAULT_DATA_ROW_FROM_ADJUST); if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ? valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx); if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ??? if (valueRowFromIdx > valueRowToIdx) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO); } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } List<SheetToSqlSettingInfo> sheetSettingInfoList = new ArrayList<SheetToSqlSettingInfo>(); // ?? int sheetNameColIdx = tagColIdx++; // int valueColIdx = tagColIdx++; // int tableColIdx = tagColIdx++; // int columnNameColIdx = tagColIdx++; // ??? int uniqueColIdx = tagColIdx++; // int dataTypeColIdx = tagColIdx++; // ????? Workbook workbook = sheet.getWorkbook(); // ??? for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { // ?? Cell sheetNameCell = row.getCell(sheetNameColIdx); Cell valueCell = row.getCell(valueColIdx); Cell tableNameCell = row.getCell(tableColIdx); Cell columnNameCell = row.getCell(columnNameColIdx); Cell uniqueCell = row.getCell(uniqueColIdx); Cell dataTypeCell = row.getCell(dataTypeColIdx); // ? if ((sheetNameCell == null) && (valueCell == null) && (tableNameCell == null) && (columnNameCell == null) && (uniqueCell == null) && (dataTypeCell == null)) { // ????null?? continue; } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null) || ("".equals(sheetNameCell.getStringCellValue()))) { // ????????? continue; } else { // ???? Cell requiredErrorCell = null; if (tableNameCell == null) { // ?null?? requiredErrorCell = row.createCell(tableColIdx); } else if (columnNameCell == null) { // ?null?? requiredErrorCell = row.createCell(columnNameColIdx); } if (requiredErrorCell != null) { // ?? throw new ParseException(requiredErrorCell, "?null??"); } } // ?? SheetToSqlSettingInfo settingInfo = new SheetToSqlSettingInfo(); // ???? String sheetName = sheetNameCell.getStringCellValue(); if (workbook.getSheet(sheetName) == null) { throw new ParseException(sheetNameCell, "[" + sheetName + "]????"); } // ?? settingInfo.setSheetName(sheetName); settingInfo.setSheetNameCell(sheetNameCell); // ?? settingInfo.setTableName(tableNameCell.getStringCellValue()); settingInfo.setTableNameCell(tableNameCell); // settingInfo.setColumnName(columnNameCell.getStringCellValue()); settingInfo.setColumnNameCell(columnNameCell); // if (valueCell != null) { Object value = PoiUtil.getCellValue(valueCell); settingInfo.setValue(value); settingInfo.setValueCell(valueCell); } // ??? if (uniqueCell != null) { if (uniqueCell.getStringCellValue() != null && uniqueCell.getStringCellValue().equals(UNIQUE_PROPERTY_MARK)) { settingInfo.setUnique(true); settingInfo.setUniqueCell(uniqueCell); } } // if (dataTypeCell != null) { settingInfo.setDataType(dataTypeCell.getStringCellValue()); settingInfo.setDataTypeCell(dataTypeCell); } // ???? sheetSettingInfoList.add(settingInfo); } } return sheetSettingInfoList; }
From source file:org.betaconceptframework.astroboa.commons.excelbuilder.ExcelSheetBuilder.java
License:Open Source License
public void createSheet(HSSFWorkbook workbook) { Sheet sheet = instantiateSheet(workbook); addRowWithHeaders(sheet);/*w w w. ja v a 2 s.c o m*/ for (HelperRow helperRow : rows) { Row contentObjectRow = sheet.createRow(sheet.getLastRowNum() + 1); int cellIndex = 1; for (String propertyPath : propertyPaths) { Cell contentObjectPropertyCell = contentObjectRow.createCell(cellIndex++); helperRow.addValueToCellForPath(propertyPath, contentObjectPropertyCell); } } autoSizeColumns(sheet); }
From source file:org.cgiar.ccafs.marlo.action.center.capdev.ParticipantsAction.java
License:Open Source License
public String preLoadExcelFile() { request = ServletActionContext.getRequest(); try {//w w w . j av a2 s .c o m 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; }
From source file:org.cgiar.ccafs.marlo.action.center.capdev.test.java
License:Open Source License
public void readFile() throws FileNotFoundException { final File file = new File("C:\\Users\\logonzalez\\Downloads\\participants.xlsx"); FileInputStream fileInput;//w w w. j a v a2 s. c o m try { fileInput = new FileInputStream(file); final XSSFWorkbook wb = new XSSFWorkbook(fileInput); final Sheet sheet = wb.getSheetAt(0); final List<Row> notEmptyRows = this.searchForEmptyRows(sheet); // System.out.println(sheet.getLastRowNum()); final Row firstRow = sheet.getRow(9); final int totalRows = sheet.getLastRowNum() - firstRow.getRowNum(); // System.out.println("firstRow " + firstRow.getRowNum()); // System.out.println("totalRows " + totalRows); final int totalColumns = firstRow.getLastCellNum(); System.out.println("notEmptyRows.size " + notEmptyRows.size()); for (int fila = 0; fila < notEmptyRows.size(); fila++) { final Row row = notEmptyRows.get(fila); for (int col = 0; col < row.getLastCellNum(); col++) { final Cell cell = row.getCell(col); System.out.println(this.getCellData(cell)); } System.out.println("-----------"); } } catch (final IOException e) { e.printStackTrace(); } }
From source file:org.cgiar.ccafs.marlo.action.center.capdev.test.java
License:Open Source License
public List<Row> searchForEmptyRows(Sheet sheet) { // Decide which rows to process final List<Row> notEmptyRows = new ArrayList<>(); final Row firstRow = sheet.getRow(10); final int rowStart = firstRow.getRowNum(); final int rowEnd = sheet.getLastRowNum(); for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) { final Row r = sheet.getRow(rowNum); if (r != null) { // System.out.println("empty row " + r.getRowNum()); // sheet.removeRow(r); // This whole row is empty // Handle it as needed notEmptyRows.add(r);// w w w . j a v a 2 s. c o m continue; } } return notEmptyRows; }
From source file:org.cgiar.ccafs.marlo.utils.ReadExcelFile.java
License:Open Source License
public List<Map<String, Object>> getDataExcelFile(Workbook wb) { final List<Map<String, Object>> fullData = new ArrayList<>(); try {//from w w w . ja v a 2s .c o m Sheet sheet = wb.getSheetAt(0); this.searchForEmptyRows(sheet); Row firstRow = sheet.getRow(9); // fila donde esta el encabezado del template totalRows = (sheet.getLastRowNum() - firstRow.getRowNum()) + 1; totalColumns = firstRow.getLastCellNum(); List<Row> rows = this.searchForEmptyRows(sheet); for (int fila = 0; fila < rows.size(); fila++) { Row row = rows.get(fila); Map<String, Object> data = new HashMap<>(); for (int col = 0; col < firstRow.getLastCellNum(); col++) { Cell cell = row.getCell(col); Cell headerCell = firstRow.getCell(col); data.put(headerCell.getStringCellValue(), this.getCellData(cell)); } fullData.add(data); } } catch (EncryptedDocumentException e) { e.printStackTrace(); } return fullData; }