List of usage examples for org.apache.poi.ss.usermodel Sheet getFirstRowNum
int getFirstRowNum();
From source file:org.codelabor.example.emp.web.controller.EmpController.java
License:Apache License
private List<EmpDto> fileToDtoList(Part file, List<String> failureMessages) throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD by "SHIN Sang-jae" Workbook wb = WorkbookFactory.create(file.getInputStream()); int numberOfSheets = wb.getNumberOfSheets(); logger.debug("numberOfSheets: {}", numberOfSheets); // prepare model List<EmpDto> empDtoList = new ArrayList<EmpDto>(); // set effective position int effectiveFirstSheetIndex = 0; int effectiveLastSheetIndex = numberOfSheets - 1; // traverse sheet StringBuilder sb = new StringBuilder(); for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) { Sheet sheet = wb.getSheetAt(i); String sheetName = sheet.getSheetName(); logger.debug("sheetName: {}", sheetName); int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); logger.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum); // set effective position int effectiveFirstRowIndex = 1; // header row: 0 int effectiveLastRowIndex = lastRowNum; // traverse row for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) { // prepare model EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae" Row row = sheet.getRow(j);// www . j av a 2 s .co m int rowNum = row.getRowNum(); int firstCellNum = row.getFirstCellNum(); int lastCellNum = row.getLastCellNum(); logger.debug("rowNum: {}, firstCellNum: {}, lastCellNum: {}", rowNum, firstCellNum, lastCellNum); // set effective position int effectiveFirstCellIndex = firstCellNum; int effectiveLastCellIndex = lastCellNum - 1; // traverse cell for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) { Cell cell = row.getCell(k); if (cell != null) { int rowIndex = cell.getRowIndex(); int columnIndex = cell.getColumnIndex(); CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae" logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex); // populate dto switch (k) { case 0: // EMPNO empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue()); break; case 1: // ENAME empDto.setEname(cell.getRichStringCellValue().toString()); break; case 2: // JOB empDto.setJob(cell.getRichStringCellValue().toString()); break; case 3: // MGR empDto.setMgr(((Double) cell.getNumericCellValue()).intValue()); break; case 4: // HIREDATE empDto.setHireDate(cell.getDateCellValue()); break; case 5: // SAL // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 6: // COMM // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 7: // DEPTNO empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue()); break; default: break; } } } logger.debug("empDto: {}", empDto); // validate Validator validator = Validation.buildDefaultValidatorFactory().getValidator(); Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto); if (violations.isEmpty()) { // do all or nothing empDtoList.add(empDto); } else { // add failure message sb.setLength(0); // init StringBuilder for reuse for (ConstraintViolation<EmpDto> violation : violations) { String propertyPath = violation.getPropertyPath().toString(); String message = violation.getMessage(); sb.append(message); sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')'); failureMessages.add(sb.toString()); logger.error(sb.toString()); sb.setLength(0); } } } } return empDtoList; }
From source file:org.dash.valid.freq.HLAFrequenciesLoader.java
License:Open Source License
private List<DisequilibriumElement> loadNMDPLinkageReferenceData(String filename, Locus[] locusPositions) throws IOException, InvalidFormatException { List<DisequilibriumElement> disequilibriumElements = new ArrayList<DisequilibriumElement>(); // Finds the workbook instance for XLSX file InputStream inStream = HLAFrequenciesLoader.class.getClassLoader().getResourceAsStream(filename); if (inStream == null) { throw new FileNotFoundException(); }//from ww w. j a v a 2 s. com Workbook workbook = WorkbookFactory.create(inStream); // Return first sheet from the XLSX workbook Sheet mySheet = workbook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); int firstRow = mySheet.getFirstRowNum(); List<String> raceHeaders = null; // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row.getRowNum() == firstRow) { raceHeaders = readHeaderElementsByRace(row); } else { disequilibriumElements.add(readDiseqilibriumElementsByRace(row, raceHeaders, locusPositions)); } } workbook.close(); return disequilibriumElements; }
From source file:org.dash.valid.freq.HLAFrequenciesLoader.java
License:Open Source License
private void loadIndividualLocusFrequency(Frequencies freq, Locus locus) throws IOException, InvalidFormatException { List<String> singleLocusFrequencies = new ArrayList<String>(); String extension = freq.equals(Frequencies.NMDP) ? ".xlsx" : ".xls"; InputStream inputStream = HLAFrequenciesLoader.class.getClassLoader().getResourceAsStream( "frequencies/" + freq.getShortName() + "/" + locus.getFrequencyName() + extension); if (inputStream == null) return;/*from w w w . j a va 2 s .c om*/ Workbook workbook = WorkbookFactory.create(inputStream); // Return first sheet from the XLSX workbook Sheet mySheet = workbook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); int firstRow = mySheet.getFirstRowNum(); String cellValue = null; // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row.getRowNum() == firstRow) { continue; } else { cellValue = row.getCell(0).getStringCellValue(); if (!cellValue.contains(GLStringConstants.ASTERISK)) { cellValue = locus.getShortName() + GLStringConstants.ASTERISK + cellValue.substring(0, 2) + GLStringUtilities.COLON + cellValue.substring(2); } singleLocusFrequencies.add(GLStringConstants.HLA_DASH + cellValue); } } individualLocusFrequencies.put(locus, singleLocusFrequencies); workbook.close(); }
From source file:org.databene.formats.xls.XLSUtil.java
License:Open Source License
public static void autoSizeColumns(Workbook workbook) { int sheetCount = workbook.getNumberOfSheets(); for (int i = 0; i < sheetCount; i++) { Sheet sheet = workbook.getSheetAt(i); int firstRowNum = sheet.getFirstRowNum(); if (firstRowNum >= 0) { Row firstRow = sheet.getRow(firstRowNum); for (int cellnum = firstRow.getFirstCellNum(); cellnum < firstRow.getLastCellNum(); cellnum++) sheet.autoSizeColumn(cellnum); }// www . j ava 2 s. com } }
From source file:org.datanucleus.store.excel.ExcelUtils.java
License:Open Source License
/** * Convenience method to find the row number of an object in the provided workbook. * For application-identity does a search for a row with the specified PK field values. * For datastore-identity does a search for the row with the datastore column having the specified value * @param op ObjectProvider for the object * @param wb Workbook/*from w ww.ja v a 2s. c o m*/ * @param originalValue Use the original value of the identifiying fields if available (for when we are updating and using nondurable identity). * @param table The table representing this worksheet * @return The row number (or -1 if not found) */ public static int getRowNumberForObjectInWorkbook(ObjectProvider op, Workbook wb, boolean originalValue, Table table) { final AbstractClassMetaData cmd = op.getClassMetaData(); if (cmd.getIdentityType() == IdentityType.APPLICATION) { ExecutionContext ec = op.getExecutionContext(); ClassLoaderResolver clr = ec.getClassLoaderResolver(); int[] pkFieldNumbers = cmd.getPKMemberPositions(); List<Integer> pkFieldColList = new ArrayList(pkFieldNumbers.length); List pkFieldValList = new ArrayList(pkFieldNumbers.length); List<Class> pkFieldTypeList = new ArrayList(pkFieldNumbers.length); for (int i = 0; i < pkFieldNumbers.length; i++) { Object fieldValue = op.provideField(pkFieldNumbers[i]); AbstractMemberMetaData mmd = cmd.getMetaDataForManagedMemberAtAbsolutePosition(pkFieldNumbers[i]); RelationType relationType = mmd.getRelationType(clr); if (RelationType.isRelationSingleValued(relationType) && mmd.isEmbedded()) { // Embedded PC is part of PK (e.g JPA EmbeddedId) ObjectProvider embOP = ec.findObjectProvider(fieldValue); if (embOP == null) { embOP = ec.getNucleusContext().getObjectProviderFactory().newForEmbedded(ec, fieldValue, false, op, pkFieldNumbers[i]); } AbstractClassMetaData embCmd = op.getExecutionContext().getMetaDataManager() .getMetaDataForClass(mmd.getType(), clr); for (int j = 0; j < embCmd.getNoOfManagedMembers(); j++) { // TODO Support nested embedded AbstractMemberMetaData embMmd = embCmd.getMetaDataForManagedMemberAtAbsolutePosition(j); List<AbstractMemberMetaData> embMmds = new ArrayList(); embMmds.add(mmd); embMmds.add(embMmd); pkFieldColList.add( table.getMemberColumnMappingForEmbeddedMember(embMmds).getColumn(0).getPosition()); pkFieldValList.add(embOP.provideField(j)); pkFieldTypeList.add(embMmd.getType()); } } else { pkFieldColList.add(table.getMemberColumnMappingForMember(mmd).getColumn(0).getPosition()); pkFieldValList.add(fieldValue); pkFieldTypeList.add(mmd.getType()); } } String sheetName = table.getName(); final Sheet sheet = wb.getSheet(sheetName); if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) { for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) { Row row = sheet.getRow(i); if (row != null) { boolean matches = true; for (int j = 0; j < pkFieldColList.size(); j++) { int colNumber = pkFieldColList.get(j); Object fieldValue = pkFieldValList.get(j); Class fieldType = pkFieldTypeList.get(j); Cell cell = row.getCell(colNumber); if (!cellMatches(cell, fieldType, fieldValue)) { matches = false; break; } } if (matches) { // Found the object with the correct PK values so return return row.getRowNum(); } } } } } else if (cmd.getIdentityType() == IdentityType.DATASTORE) { String sheetName = table.getName(); final Sheet sheet = wb.getSheet(sheetName); int datastoreIdColNo = table.getDatastoreIdColumn().getPosition(); Object key = IdentityUtils.getTargetKeyForDatastoreIdentity(op.getInternalObjectId()); if (sheet != null) { for (int i = 0; i < sheet.getLastRowNum() + 1; i++) { Row row = sheet.getRow(i); if (row != null) { Cell cell = row.getCell(datastoreIdColNo); if (cell != null && cellMatches(cell, key.getClass(), key)) { return row.getRowNum(); } } } } } else { // Nondurable, so compare all applicable fields ExecutionContext ec = op.getExecutionContext(); ClassLoaderResolver clr = ec.getClassLoaderResolver(); int[] fieldNumbers = cmd.getAllMemberPositions(); List<Integer> fieldColList = new ArrayList(fieldNumbers.length); List<Class> fieldTypeList = new ArrayList(fieldNumbers.length); List fieldValList = new ArrayList(fieldNumbers.length); for (int i = 0; i < fieldNumbers.length; i++) { AbstractMemberMetaData mmd = cmd.getMetaDataForManagedMemberAtAbsolutePosition(fieldNumbers[i]); RelationType relationType = mmd.getRelationType(clr); Object fieldValue = null; if (originalValue) { Object oldValue = op .getAssociatedValue(ObjectProvider.ORIGINAL_FIELD_VALUE_KEY_PREFIX + fieldNumbers[i]); if (oldValue != null) { fieldValue = oldValue; } else { fieldValue = op.provideField(fieldNumbers[i]); } } else { fieldValue = op.provideField(fieldNumbers[i]); } if (RelationType.isRelationSingleValued(relationType) && mmd.isEmbedded()) { // Embedded PC is part of PK (e.g JPA EmbeddedId) ObjectProvider embOP = ec.findObjectProvider(fieldValue); if (embOP == null) { embOP = ec.getNucleusContext().getObjectProviderFactory().newForEmbedded(ec, fieldValue, false, op, fieldNumbers[i]); } AbstractClassMetaData embCmd = op.getExecutionContext().getMetaDataManager() .getMetaDataForClass(mmd.getType(), clr); for (int j = 0; j < embCmd.getNoOfManagedMembers(); j++) { // TODO Support nested embedded AbstractMemberMetaData embMmd = embCmd.getMetaDataForManagedMemberAtAbsolutePosition(j); List<AbstractMemberMetaData> embMmds = new ArrayList(); embMmds.add(mmd); embMmds.add(embMmd); fieldColList.add( table.getMemberColumnMappingForEmbeddedMember(embMmds).getColumn(0).getPosition()); fieldTypeList.add(embMmd.getType()); fieldValList.add(embOP.provideField(j)); } } else if (relationType == RelationType.NONE) { fieldColList.add(table.getMemberColumnMappingForMember(mmd).getColumn(0).getPosition()); fieldTypeList.add(mmd.getType()); fieldValList.add(fieldValue); } } String sheetName = table.getName(); final Sheet sheet = wb.getSheet(sheetName); if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) { for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) { Row row = sheet.getRow(i); if (row != null) { boolean matches = true; for (int j = 0; j < fieldColList.size(); j++) { int colNumber = fieldColList.get(j); Class fieldType = fieldTypeList.get(j); Object fieldValue = fieldValList.get(j); Cell cell = row.getCell(colNumber); if (!cellMatches(cell, fieldType, fieldValue)) { matches = false; break; } } if (matches) { // Found the object with the correct PK values so return return row.getRowNum(); } } } } } return -1; }
From source file:org.datanucleus.store.excel.ExcelUtils.java
License:Open Source License
/** * Convenience method to find the number of rows in a workbook. * This takes into account the fact that it seems to be impossible (with Apache POI 3.0.2) * to delete rows from a sheet. Consequently what we do is leave the row but delete * all cells. When returning the number of rows this ignores rows that have no cells. * @param op ObjectProvider for the object * @param wb Workbook//from w w w .j a va2s .c o m * @return Number of (active) rows (or 0 if no active rows) */ public static int getNumberOfRowsInSheetOfWorkbook(ObjectProvider op, Workbook wb) { int numRows = 0; final AbstractClassMetaData cmd = op.getClassMetaData(); Table table = op.getExecutionContext().getStoreManager() .getStoreDataForClass(op.getClassMetaData().getFullClassName()).getTable(); String sheetName = table.getName(); final Sheet sheet = wb.getSheet(sheetName); if (cmd.getIdentityType() == IdentityType.APPLICATION) { int[] pkFieldNumbers = cmd.getPKMemberPositions(); Object[] pkFieldValues = new Object[pkFieldNumbers.length]; for (int i = 0; i < pkFieldNumbers.length; i++) { pkFieldValues[i] = op.provideField(pkFieldNumbers[i]); } if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) { for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) { Row row = sheet.getRow(i); if (row != null) { boolean validRow = true; for (int j = 0; j < pkFieldNumbers.length; j++) { AbstractMemberMetaData pkMmd = cmd .getMetaDataForManagedMemberAtAbsolutePosition(pkFieldNumbers[j]); int colNumber = table.getMemberColumnMappingForMember(pkMmd).getColumn(0).getPosition(); Cell cell = row.getCell(colNumber); if (cell == null) { // Valid row. Apache POI would return cell as null if not active validRow = false; } } if (validRow) { numRows++; } } } } } else if (cmd.getIdentityType() == IdentityType.DATASTORE) { if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) { int datastoreIdColNumber = table.getDatastoreIdColumn().getPosition(); for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) { Row rrow = sheet.getRow(i); Cell cell = rrow.getCell(datastoreIdColNumber); if (cell != null) { // Valid row. Apache POI would return cell as null if not active numRows++; } } } } else { if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) { for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) { Row rrow = sheet.getRow(i); Cell cell = rrow.getCell(0); // Use first cell since no identity as such if (cell != null) { // Valid row. Apache POI would return cell as null if not active numRows++; } } } } return numRows; }
From source file:org.datanucleus.store.excel.query.ExcelCandidateList.java
License:Open Source License
/** * Constructor for the lazy loaded Excel candidate list. * @param cls The candidate class/*from www .j a va 2 s . c o m*/ * @param subclasses Whether to include subclasses * @param ec execution context * @param cacheType Type of caching * @param mconn Connection to the datastore * @param ignoreCache Whether to ignore the cache on object retrieval */ public ExcelCandidateList(Class cls, boolean subclasses, ExecutionContext ec, String cacheType, ManagedConnection mconn, boolean ignoreCache) { super(cls, subclasses, ec, cacheType); this.mconn = mconn; this.ignoreCache = ignoreCache; // Count the instances per class by scanning the associated worksheets numberInstancesPerClass = new ArrayList<Integer>(); ExcelStoreManager storeMgr = (ExcelStoreManager) ec.getStoreManager(); Iterator<AbstractClassMetaData> cmdIter = cmds.iterator(); Workbook workbook = (Workbook) mconn.getConnection(); while (cmdIter.hasNext()) { AbstractClassMetaData cmd = cmdIter.next(); if (!storeMgr.managesClass(cmd.getFullClassName())) { // Make sure schema exists, using this connection storeMgr.manageClasses(new String[] { cmd.getFullClassName() }, ec.getClassLoaderResolver(), workbook); } Table table = ec.getStoreManager().getStoreDataForClass(cmd.getFullClassName()).getTable(); String sheetName = table.getName(); Sheet sheet = workbook.getSheet(sheetName); int size = 0; if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) { // Take the next row in this worksheet int idColIndex = -1; if (cmd.getIdentityType() == IdentityType.APPLICATION) { int[] pkFieldNums = cmd.getPKMemberPositions(); // TODO Check all pk cols? AbstractMemberMetaData pkMmd = cmd .getMetaDataForManagedMemberAtAbsolutePosition(pkFieldNums[0]); idColIndex = table.getMemberColumnMappingForMember(pkMmd).getColumn(0).getPosition(); } else if (cmd.getIdentityType() == IdentityType.DATASTORE) { idColIndex = table.getDatastoreIdColumn().getPosition(); } else { idColIndex = 0; // No id column with nondurable, so just take the first } for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if (row.getCell(idColIndex) != null) // Omit inactive rows { size++; } } } numberInstancesPerClass.add(size); } }
From source file:org.datanucleus.store.excel.query.ExcelCandidateList.java
License:Open Source License
protected Object retrieveObjectForIndex(int index) { if (index < 0 || index >= getSize()) { throw new NoSuchElementException(); }//from w w w . j a v a 2s .c om Iterator<AbstractClassMetaData> cmdIter = cmds.iterator(); Iterator<Integer> numIter = numberInstancesPerClass.iterator(); int first = 0; int last = -1; while (cmdIter.hasNext()) { final AbstractClassMetaData cmd = cmdIter.next(); int number = numIter.next(); last = first + number; if (index >= first && index < last) { // Object is of this candidate type, so find the object Table table = ec.getStoreManager().getStoreDataForClass(cmd.getFullClassName()).getTable(); String sheetName = table.getName(); Workbook workbook = (Workbook) mconn.getConnection(); final Sheet worksheet = workbook.getSheet(sheetName); if (worksheet != null) { int idColIndex = -1; if (cmd.getIdentityType() == IdentityType.APPLICATION) { int[] pkFieldNums = cmd.getPKMemberPositions(); // TODO Check all pk cols? AbstractMemberMetaData pkMmd = cmd .getMetaDataForManagedMemberAtAbsolutePosition(pkFieldNums[0]); idColIndex = table.getMemberColumnMappingForMember(pkMmd).getColumn(0).getPosition(); } else if (cmd.getIdentityType() == IdentityType.DATASTORE) { idColIndex = table.getDatastoreIdColumn().getPosition(); } else { idColIndex = 0; // No id column with nondurable, so just take the first } int current = first; for (int i = worksheet.getFirstRowNum(); i <= worksheet.getLastRowNum(); i++) { final Row row = worksheet.getRow(i); if (row.getCell(idColIndex) != null) // Omit inactive rows { if (current == index) { // This row equates to the required index final int rowNumber = i; if (cmd.getIdentityType() == IdentityType.APPLICATION) { final FetchFieldManager fm = new FetchFieldManager(ec, cmd, worksheet, rowNumber, table); Object id = IdentityUtils.getApplicationIdentityForResultSetRow(ec, cmd, null, false, fm); return ec.findObject(id, new FieldValues() { // ObjectProvider calls the fetchFields method public void fetchFields(ObjectProvider op) { op.replaceFields(cmd.getAllMemberPositions(), fm); } public void fetchNonLoadedFields(ObjectProvider sm) { sm.replaceNonLoadedFields(cmd.getAllMemberPositions(), fm); } public FetchPlan getFetchPlanForLoading() { return null; } }, null, ignoreCache, false); } else if (cmd.getIdentityType() == IdentityType.DATASTORE) { final FetchFieldManager fm = new FetchFieldManager(ec, cmd, worksheet, rowNumber, table); Object id = null; Cell idCell = row.getCell(idColIndex); int type = idCell.getCellType(); if (type == Cell.CELL_TYPE_STRING) { String key = idCell.getRichStringCellValue().getString(); id = ec.getNucleusContext().getIdentityManager() .getDatastoreId(cmd.getFullClassName(), key); } else if (type == Cell.CELL_TYPE_NUMERIC) { long key = (long) idCell.getNumericCellValue(); id = ec.getNucleusContext().getIdentityManager() .getDatastoreId(cmd.getFullClassName(), key); } return ec.findObject(id, new FieldValues() { // ObjectProvider calls the fetchFields method public void fetchFields(ObjectProvider op) { op.replaceFields(cmd.getAllMemberPositions(), fm); } public void fetchNonLoadedFields(ObjectProvider op) { op.replaceNonLoadedFields(cmd.getAllMemberPositions(), fm); } public FetchPlan getFetchPlanForLoading() { return null; } }, null, ignoreCache, false); } else { // Nondurable identity final FetchFieldManager fm = new FetchFieldManager(ec, cmd, worksheet, rowNumber, table); Object id = new SCOID(cmd.getFullClassName()); return ec.findObject(id, new FieldValues() { // ObjectProvider calls the fetchFields method public void fetchFields(ObjectProvider op) { op.replaceFields(cmd.getAllMemberPositions(), fm); } public void fetchNonLoadedFields(ObjectProvider sm) { sm.replaceNonLoadedFields(cmd.getAllMemberPositions(), fm); } public FetchPlan getFetchPlanForLoading() { return null; } }, null, ignoreCache, false); } } current++; } } } } else { first += number; } } return null; }
From source file:org.datanucleus.store.excel.valuegenerator.IncrementGenerator.java
License:Open Source License
protected ValueGenerationBlock<Long> reserveBlock(long size) { if (size < 1) { return null; }/* w w w .j av a 2 s .c o m*/ // Allocate value(s) ManagedConnection mconn = connectionProvider.retrieveConnection(); List<Long> oids = new ArrayList<Long>(); try { // Create the worksheet if not existing Workbook spreadsheetDoc = (Workbook) mconn.getConnection(); Sheet sheet = spreadsheetDoc.getSheet(worksheetName); Row row = null; Cell valueCell = null; if (sheet == null) { if (!storeMgr.getSchemaHandler().isAutoCreateTables()) { throw new NucleusUserException(Localiser.msg("040011", worksheetName)); } sheet = spreadsheetDoc.createSheet(worksheetName); row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue(key); valueCell = row.createCell(1); valueCell.setCellValue(Double.valueOf(0)); } else { for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) { Row tblRow = sheet.getRow(i); if (tblRow != null) { Cell tblCell = tblRow.getCell(0); if (tblCell.getStringCellValue().equals(key)) { row = tblRow; valueCell = row.getCell(1); break; } } } if (row == null) { row = sheet.createRow(sheet.getLastRowNum() + 1); Cell cell1 = row.createCell(0); cell1.setCellValue(key); valueCell = row.createCell(1); valueCell.setCellValue(Double.valueOf(0)); } } // Update the row if (valueCell != null) { NucleusLogger.VALUEGENERATION .debug("Allowing " + size + " values for increment generator for " + key); long currentVal = (long) valueCell.getNumericCellValue(); valueCell.setCellValue(Double.valueOf(currentVal + size)); for (int i = 0; i < size; i++) { oids.add(currentVal + 1); currentVal++; } } } finally { connectionProvider.releaseConnection(); } return new ValueGenerationBlock<Long>(oids); }
From source file:org.eclipse.jubula.client.core.businessprocess.importfilter.ExcelImportFilter.java
License:Open Source License
/** * parses a file and returns the data as DataTable structure * //from ww w . j av a2 s. co m * @param dataDir * directory for data files * @param file * data source File * @return * filled TestDataManager with new data * @throws IOException * error occurred while reading data source */ public DataTable parse(File dataDir, String file) throws IOException, DataReadException { DataTable filledDataTable; final FileInputStream inStream = findDataFile(dataDir, file); try { Workbook wb; if (file.endsWith(".xls")) { //$NON-NLS-1$ POIFSFileSystem fs = new POIFSFileSystem(inStream); wb = new HSSFWorkbook(fs); } else { wb = new XSSFWorkbook(inStream); } // Open the first sheet Sheet sheet = wb.getSheetAt(0); final int lastRowNum = sheet.getLastRowNum(); final int firstRowNum = sheet.getFirstRowNum(); // iterate over rows if (sheet.getRow(firstRowNum) == null) { return new DataTable(0, 0); } final int height = lastRowNum - firstRowNum + 1; final int width = sheet.getRow(firstRowNum).getLastCellNum() - sheet.getRow(firstRowNum).getFirstCellNum(); filledDataTable = new DataTable(height, width); for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) { Row row = sheet.getRow(rowNum); final short lastCellNum = row.getLastCellNum(); final short firstCellNum = row.getFirstCellNum(); for (int cellNr = firstCellNum; cellNr < lastCellNum; cellNr++) { Cell cell = row.getCell(cellNr); String cellString = getExcelCellString(cell); filledDataTable.updateDataEntry(rowNum, cellNr, cellString); } } } catch (IOException e) { throw e; // just pass on, don't fall through to Throwable } catch (Throwable t) { throw new DataReadException(t); } finally { inStream.close(); } /* fix issues with documents saved via open office * if the document has been saved via open office it contains one ore many * "null" columns at the end of the data table; these columns are truncated */ while ((filledDataTable.getColumnCount() > 0) && (StringUtils.isBlank(filledDataTable.getData(0, filledDataTable.getColumnCount() - 1)))) { int newHeight = filledDataTable.getRowCount(); int newWidth = filledDataTable.getColumnCount() - 1; DataTable cleanedFilledDataTable = new DataTable(newHeight, newWidth); for (int i = 0; i < newHeight; i++) { for (int j = 0; j < newWidth; j++) { cleanedFilledDataTable.updateDataEntry(i, j, filledDataTable.getData(i, j)); } } filledDataTable = cleanedFilledDataTable; } return filledDataTable; }