List of usage examples for org.apache.poi.ss.usermodel Row getPhysicalNumberOfCells
int getPhysicalNumberOfCells();
From source file:com.opengamma.integration.copier.sheet.reader.SimpleXlsSheetReader.java
License:Open Source License
private String[] getColumnNames(Row rawRow) { String[] columns = new String[rawRow.getPhysicalNumberOfCells()]; for (int i = 0; i < rawRow.getPhysicalNumberOfCells(); i++) { columns[i] = getCell(rawRow, i).trim().toLowerCase(); }/*from w ww. j a v a 2 s.c o m*/ return columns; }
From source file:com.projectswg.tools.SwgExcelConverter.java
License:Open Source License
private int createTableColumnData(SWGFile swgFile, Row row) { // Perform spreadsheet checks int count = row.getPhysicalNumberOfCells(); if (count <= 0) System.err.println("Excel sheet has no columns!"); // Setup size for the buffer int size = 4; String[] columns = new String[count]; for (int i = 0; i < count; i++) { String column = row.getCell(i).getStringCellValue(); columns[i] = column;/* ww w . ja v a 2 s . co m*/ size += column.length() + 1; } // Throw in the data IffNode cols = swgFile.addChunk("COLS"); ByteBuffer data = ByteBuffer.allocate(size).order(ByteOrder.LITTLE_ENDIAN); data.putInt(count); for (String column : columns) { data.put(column.getBytes(Charset.forName("US-ASCII"))); data.put((byte) 0); } cols.setChunkData(data); return count; }
From source file:com.projectswg.tools.SwgExcelConverter.java
License:Open Source License
private String[] createTableTypeData(SWGFile swgFile, Row row, int expectedColumns) { // Perform spreadsheet checks if (row == null || expectedColumns == 0) return null; int count = row.getPhysicalNumberOfCells(); if (count != expectedColumns) { System.err.println("2nd row only had " + count + " rows, expected " + expectedColumns); return null; }/*from w w w .j a va 2s .c o m*/ // Setup size for the buffer int size = 0; String[] types = new String[count]; for (int i = 0; i < count; i++) { String type = row.getCell(i).getStringCellValue(); types[i] = type.substring(0, 1).toLowerCase() + type.substring(1); size += type.length() + 1; } // Throw in the data IffNode type = swgFile.addChunk("TYPE"); ByteBuffer data = ByteBuffer.allocate(size).order(ByteOrder.LITTLE_ENDIAN); for (String sType : types) { data.put(sType.getBytes(Charset.forName("US-ASCII"))); data.put((byte) 0); } type.setChunkData(data); return types; }
From source file:com.projectswg.tools.SwgExcelConverter.java
License:Open Source License
private DatatableRow getDataTableRow(Row row, int expectedColumns, String[] types) { if (row == null || expectedColumns == 0) return null; int count = row.getPhysicalNumberOfCells(); // Use > because empty cells are not considered a "physical cell" if (count > expectedColumns) { System.err.println("Row " + row.getRowNum() + " has " + count + " cells, expected " + expectedColumns); return null; }/*from www . j a v a 2 s . c om*/ DatatableRow dataRow = new DatatableRow(expectedColumns); for (int i = 0; i < expectedColumns; i++) { Cell cell = row.getCell(i); if (cell == null) { // empty cell parseDataEmptyCell(types, dataRow, i); continue; } switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: parseDataCell(types, dataRow, i, cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: parseDataCell(types, dataRow, i, cell.getNumericCellValue()); break; default: System.out.println("UNK CELL TYPE: " + cell.getCellType()); } } return dataRow; }
From source file:com.snapdeal.scm.web.core.utils.ExcelReader.java
License:Open Source License
public static FileValuesDTO readExcelFile(MultipartFile file) throws IOException, FileUploadException { FileValuesDTO fileValue = new FileValuesDTO(); InputStream inputStream = file.getInputStream(); Workbook workbook = getWorkbook(inputStream, file.getOriginalFilename()); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); if (!iterator.hasNext()) { throw new FileUploadException("Empty File Uploded"); }//from w ww . j a v a 2 s . c o m List<String> headers = new ArrayList<String>(); iterator.next().forEach(cell -> headers.add(cell.getStringCellValue())); fileValue.setHeader(headers); List<Map<String, String>> headerDataMap = new ArrayList<Map<String, String>>(); int size = fileValue.getHeader().size(); while (iterator.hasNext()) { Map<String, String> dataMap = new HashMap<String, String>(); Row row = iterator.next(); if (row.getPhysicalNumberOfCells() == 0) { continue; } for (int i = 0; i < size; i++) { switch (row.getCell(i).getCellType()) { case Cell.CELL_TYPE_NUMERIC: dataMap.put(fileValue.getHeader().get(i), new Double(row.getCell(i).getNumericCellValue()).toString()); break; case Cell.CELL_TYPE_STRING: dataMap.put(fileValue.getHeader().get(i), row.getCell(i).getStringCellValue()); break; } } headerDataMap.add(dataMap); } if (CollectionUtils.isEmpty(headerDataMap)) { throw new FileUploadException("No date in Uploded File"); } fileValue.setHeaderDataMap(headerDataMap); workbook.close(); inputStream.close(); return fileValue; }
From source file:com.validation.manager.core.tool.requirement.importer.RequirementImporter.java
License:Apache License
@Override public List<Requirement> importFile(boolean header) throws RequirementImportException, VMException { queue.clear();//from ww w . j a v a 2 s . co m List<Integer> errors = new ArrayList<>(); HashMap<String, Object> parameters = new HashMap<>(); List<Object> result; if (toImport == null) { throw new RequirementImportException("message.requirement.import.file.null"); } else if (!toImport.exists()) { throw new RequirementImportException("message.requirement.import.file.invalid"); } else { //Excel support if (toImport.getName().endsWith(".xls") || toImport.getName().endsWith(".xlsx")) { try { Workbook wb = loadFile(); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); int r = 0; if (header) { //Skip header row r++; } for (; r < rows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } if (row.getCell(0) == null) { LOG.log(Level.WARNING, "Found an empty row on line: {0}. " + "Stopping processing", r); break; } int cells = row.getPhysicalNumberOfCells(); if (cells < 2) { LOG.log(Level.INFO, "Processing row: {0}", r); LOG.warning(ResourceBundle .getBundle("com.validation.manager.resources.VMMessages", Locale.getDefault()) .getString("message.requirement.import.missing.column") .replaceAll("%c", "" + cells)); errors.add(r); } else { Requirement requirement = new Requirement(); LOG.log(Level.FINE, "Row: {0}", r); for (int c = 0; c < cells; c++) { Cell cell = row.getCell(c); String value = ""; if (cell != null) { switch (cell.getCellTypeEnum()) { case FORMULA: value = cell.getCellFormula(); break; case NUMERIC: value = "" + cell.getNumericCellValue(); break; case STRING: value = cell.getStringCellValue(); break; default: value = ""; break; } } //Remove any extra spaces. value = value.trim(); switch (c) { case 0: //Unique ID LOG.fine("Setting id"); requirement.setUniqueId(value); break; case 1: //Description LOG.fine("Setting desc"); requirement.setDescription(value); break; case 2: //Optional Requirement type LOG.fine("Setting requirement type"); parameters.clear(); parameters.put("name", value); result = namedQuery("RequirementType.findByName", parameters); if (result.isEmpty()) { //Assume a default parameters.clear(); parameters.put("name", "SW"); result = namedQuery("RequirementType.findByName", parameters); } requirement.setRequirementTypeId((RequirementType) result.get(0)); break; case 3: //Optional notes LOG.fine("Setting notes"); requirement.setNotes(value); break; default: throw new RequirementImportException("Invalid column detected: " + c); } LOG.fine(value); } //This shouldn't be null assert rsn != null : "Requirement Spec Node is null?"; requirement.setRequirementSpecNode(rsn); parameters.clear(); parameters.put("status", "general.open"); result = namedQuery("RequirementStatus.findByStatus", parameters); requirement.setRequirementStatusId((RequirementStatus) result.get(0)); assert requirement.getUniqueId() != null && !requirement.getUniqueId().isEmpty() : "Invalid requirement detected!"; try { if (!exists(requirement) && !queue.containsKey(requirement.getUniqueId())) { queue.put(requirement.getUniqueId(), requirement); } } catch (IllegalOrphanException | NonexistentEntityException ex) { Exceptions.printStackTrace(ex); } } } } catch (InvalidFormatException | IOException ex) { LOG.log(Level.SEVERE, null, ex); } finally { try { if (inp != null) { inp.close(); } } catch (IOException ex) { LOG.log(Level.SEVERE, null, ex); } } } else if (toImport.getName().endsWith(".xml")) { throw new RequirementImportException("XML importing not supported yet."); } else if (toImport.getName().endsWith(".doc") || toImport.getName().endsWith(".docx")) { try { TableExtractor te = new TableExtractor(toImport); List<DefaultTableModel> tables = te.extractTables(); Requirement requirement = new Requirement(); LOG.log(Level.INFO, "Imported {0} tables!", tables.size()); int count = 1; for (DefaultTableModel model : tables) { int rows = model.getRowCount(); int cols = model.getColumnCount(); LOG.log(Level.INFO, "Processing table {0} with {1} " + "rows and {2} columns.", new Object[] { count, rows, cols }); for (int r = 0; r < rows; r++) { for (int c = 0; c < cols; c++) { String value = (String) model.getValueAt(rows, cols); switch (c) { case 0: //Unique ID LOG.fine("Setting id"); requirement.setUniqueId(value); break; case 1: //Description LOG.fine("Setting desc"); requirement.setDescription(value); break; case 2: //Requirement type LOG.fine("Setting requirement type"); parameters.clear(); parameters.put("name", value); result = namedQuery("RequirementType.findByName", parameters); if (result.isEmpty()) { //Assume a default parameters.clear(); parameters.put("name", "SW"); result = namedQuery("RequirementType.findByName", parameters); } requirement.setRequirementTypeId((RequirementType) result.get(0)); break; case 3: //Optional notes LOG.fine("Setting notes"); requirement.setNotes(value); break; default: throw new RuntimeException("Invalid column detected: " + c); } } } } } catch (IOException | ClassNotFoundException ex) { Exceptions.printStackTrace(ex); } } else { throw new RequirementImportException("Unsupported file format: " + toImport.getName()); } StringBuilder sb = new StringBuilder("Rows with erros:\n"); errors.stream().forEach((line) -> { sb.append(line).append('\n'); }); if (!errors.isEmpty()) { getDefault().lookup(MessageHandler.class).info(sb.toString()); } return new ArrayList(queue.values()); } }
From source file:com.validation.manager.core.tool.step.importer.StepImporter.java
License:Apache License
@Override public List<Step> importFile(boolean header) throws TestCaseImportException { steps.clear();//www . j a va 2s . c om if (toImport == null) { throw new TestCaseImportException("message.step.import.file.null"); } else if (!toImport.exists()) { throw new TestCaseImportException("message.step.import.file.invalid"); } else { //Excel support if (toImport.getName().endsWith(".xls") || toImport.getName().endsWith(".xlsx")) { InputStream inp = null; try { inp = new FileInputStream(toImport); org.apache.poi.ss.usermodel.Workbook wb = create(inp); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); int r = 0; if (header) { //Skip header row r++; } for (; r < rows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); if (row.getCell(0) == null) { LOG.log(Level.WARNING, "Found an empty row on line: {0}. " + "Stopping processing", r); break; } if (cells < 2) { throw new TestCaseImportException(RB.getString("message.step.import.missing.column") .replaceAll("%c", "" + cells)); } Step step = new Step(); step.setRequirementList(new ArrayList<>()); HashMap<String, Object> parameters = new HashMap<>(); List<Object> result; LOG.log(Level.FINE, "Row: {0}", r); for (int c = 0; c < cells; c++) { Cell cell = row.getCell(c); String value = null; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case Cell.CELL_TYPE_NUMERIC: value = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; default: //Do nothing. } } switch (c) { case 0: if (value != null) { //Sequence LOG.fine("Setting sequence"); Integer val = value.contains(".") ? valueOf(value.substring(0, value.indexOf("."))) : valueOf(value); if (!tc.getStepList().isEmpty()) { int max = 0; for (Step s : tc.getStepList()) { if (s.getStepSequence() > max) { max = s.getStepSequence(); } } //Make sure there isn't one on that sequence already val += max; } step.setStepSequence(val); } break; case 1: if (value != null) { //Text LOG.fine("Setting text"); step.setText(value.getBytes("UTF-8")); } break; case 2: //Optional Related requirements if (value != null && !value.trim().isEmpty()) { LOG.fine("Setting related requirements"); StringTokenizer st = new StringTokenizer(value, ","); while (st.hasMoreTokens()) { String token = st.nextToken().trim(); parameters.clear(); parameters.put("uniqueId", token); result = namedQuery("Requirement.findByUniqueId", parameters); if (!result.isEmpty()) { for (Object o : result) { step.getRequirementList().add((Requirement) o); } } } } break; case 3: if (value != null) { //Optional Expected result LOG.fine("Setting expected result"); step.setExpectedResult(value.getBytes("UTF-8")); } break; case 4: if (value != null) { //Optional notes LOG.fine("Setting notes"); step.setNotes(value); } break; default: throw new RuntimeException("Invalid column detected: " + c); } LOG.fine(value); } step.setTestCase(tc); steps.add(step); } } catch (InvalidFormatException | IOException ex) { LOG.log(Level.SEVERE, null, ex); } finally { try { if (inp != null) { inp.close(); } } catch (IOException ex) { LOG.log(Level.SEVERE, null, ex); } } } else if (toImport.getName().endsWith(".xml")) { throw new TestCaseImportException("XML importing not supported yet."); } else { throw new TestCaseImportException("Unsupported file format: " + toImport.getName()); } return steps; } }
From source file:com.xn.interfacetest.service.impl.TestCaseServiceImpl.java
License:Open Source License
private StringBuffer readExcel(String path) throws Exception { // ?Excel/*from w w w . j a va 2s.com*/ InputStream excelFile = new FileInputStream(path); //????? StringBuffer failCaseNumbers = new StringBuffer(""); try { Workbook wb = WorkbookFactory.create(new File(path)); Sheet sheet = wb.getSheetAt(0); // int rowNum = sheet.getLastRowNum() + 1; logger.info("rowNum" + rowNum); //?1 Row row = sheet.getRow(0); int colNum = row.getPhysicalNumberOfCells(); //? logger.info("colNum" + colNum); // 2, for (int i = 1; i < rowNum; i++) { TestCaseDto caseDto = new TestCaseDto(); logger.info("??" + i); row = sheet.getRow(i); //??? //?---? String number = getCellFormatValue(row.getCell(0)) + ""; //? if (!checkCaseNumberUnique(number)) { failCaseNumbers.append("?").append(number) .append("??"); continue; } caseDto.setNumber(number); //?---?? caseDto.setName(getCellFormatValue(row.getCell(1)) + ""); //?---?? caseDto.setDescription(getCellFormatValue(row.getCell(2)) + ""); //?---?id,?id?id? if (StringUtils.isBlank(getCellFormatValue(row.getCell(3)) + "") || !checkInterfaceIdExist(Long.parseLong(getCellFormatValue(row.getCell(3)) + ""))) { failCaseNumbers.append("?").append(number) .append("???id") .append(row.getCell(3)).append(""); continue; } caseDto.setInterfaceId(Long.parseLong(getCellFormatValue(row.getCell(3)) + "")); //5?---? caseDto.setCustomParams(getCellFormatValue(row.getCell(4)) + ""); caseDto.setCustomParamsType(ParamsGroupTypeEnum.CUSTOM.getId()); //6?---? caseDto.setCustomParamsType(AppendParamEnum.getIdByName(getCellFormatValue(row.getCell(5)) + "")); //10- if ("SINGLE".equals(getCellFormatValue(row.getCell(9))) || "MUTIPLE".equals(getCellFormatValue(row.getCell(9)))) { caseDto.setType(getCellFormatValue(row.getCell(9)) + ""); } else { failCaseNumbers.append("?").append(number) .append("???\"MUTIPLE\"\"SINGLE\""); continue; } caseDto = this.save(caseDto); logger.info("?" + caseDto.toString()); //7?---? String assertJson = getCellFormatValue(row.getCell(6)) + ""; if (StringUtils.isNotBlank(assertJson)) { try { //?? saveParamsAsserts(assertJson, caseDto); } catch (Exception e) { logger.error("?", e); failCaseNumbers.append("?").append(number).append( "???,??"); } } //8-? String prepareStr = getCellFormatValue(row.getCell(7)) + ""; if (StringUtils.isNotBlank(prepareStr)) { try { saveDataOperate(prepareStr, caseDto.getId(), OperationTypeEnum.PREPARE.getId(), failCaseNumbers); caseDto.setDataPrepare(1); // update(caseDto); } catch (Exception e) { logger.error("?sql", e); failCaseNumbers.append("?").append(number).append( "????,??" + e.getMessage() + ""); } } //9-? String clearStr = getCellFormatValue(row.getCell(8)) + ""; if (StringUtils.isNotBlank(clearStr)) { try { saveDataOperate(clearStr, caseDto.getId(), OperationTypeEnum.CLEAR.getId(), failCaseNumbers); caseDto.setDataClear(1); // update(caseDto); } catch (Exception e) { logger.error("?sql", e); failCaseNumbers.append("?").append(number).append( "????,??" + e.getMessage() + ""); } } else { continue; } } } catch (FileNotFoundException e) { logger.error("excel", e); throw e; } catch (IOException e) { logger.error("?excel", e); throw e; } finally { return failCaseNumbers; } }
From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.LandscapeDataWorkbook.java
License:Open Source License
/** * Reads the specified {@code excelInputStream} and returns the landscape data * as {@link LandscapeData} object, containing the building blocks, relations and * attributes. If the import failes, or no data could be found, this method * returns an empty landscape data object. * /*from w ww . j a v a 2 s. c o m*/ * @param excelInputStream the Excel file input stream * @return the {@link LandscapeData} object containing building blocks, relations and attributes */ public LandscapeData doImport(InputStream excelInputStream) { loadWorkbookFromInputStream(excelInputStream); final LandscapeData landscapeData = new LandscapeData(); if (!readInConfigSheet()) { getProcessingLog().error("Could not read in configuration sheet. Aborting."); landscapeData.setLocale(getLocale()); return landscapeData; } // we can only initialize the sheet importers *after* the locale was read in from the config sheet initSheetImporters(); calculateAllFormulas(); for (int i = 0; i < getWb().getNumberOfSheets(); i++) { Sheet sheet = getWb().getSheetAt(i); String sheetName = sheet.getSheetName(); getProcessingLog().insertDummyRow(""); getProcessingLog().debug("Current Sheet: " + (sheetName == null ? "null" : sheetName)); if (isSheetSupported(sheetName)) { final String idKey = MessageAccess.getStringOrNull("global.id", getLocale()); int contentPosition = findSheetContentPosition(sheet, idKey, 0); if (contentPosition != -1) { importSheet(sheet, contentPosition, landscapeData); } else { getProcessingLog().warn("Invalid structure of Sheet '" + sheetName + "', skipping"); } int contentPositionSubscribed = findSheetContentPosition(sheet, MessageAccess.getStringOrNull(Constants.SUBSCRIBED_USERS, getLocale()), 1); Set<User> users = Sets.newHashSet(); if (contentPositionSubscribed != -1) { Row row = sheet.getRow(contentPositionSubscribed - 1); if (row.getPhysicalNumberOfCells() > 1) { String subUsers = row.getCell(2, Row.CREATE_NULL_AS_BLANK).getStringCellValue(); if (StringUtils.isNotEmpty(subUsers)) { Set<String> logins = Sets.newHashSet(ExcelImportUtilities.getSplittedArray(subUsers, ExcelSheet.IN_LINE_SEPARATOR.trim())); for (String login : logins) { User user = new User(); user.setLoginName(login); users.add(user); } } } } TypeOfBuildingBlock type = sheetToTypeMapping.get(sheetName); landscapeData.getUsers().put(type, users); } else if (!DEFAULT_SHEET_KEY.equals(sheetName)) { getProcessingLog().warn("Unknown Sheet Name '" + sheetName + "'(different Locale?). Skipping."); } } landscapeData.setLocale(getLocale()); return landscapeData; }
From source file:de.tum.in.socket.server.ReadExcel.java
License:Apache License
/** * Returns the excel column names//w w w . j a v a 2s . com */ private static String[] getColumnNames(final Row column) { final String columns[] = new String[column.getPhysicalNumberOfCells()]; final Iterator<Cell> cellIterator = column.cellIterator(); int i = 0; while (cellIterator.hasNext()) { final Cell cell = cellIterator.next(); columns[i++] = cell.getStringCellValue(); } return columns; }