List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum
int getLastRowNum();
From source file:org.cgiar.ccafs.marlo.utils.ReadExcelFile.java
License:Open Source License
public List<Row> searchForEmptyRows(Sheet sheet) { // Decide which rows to process List<Row> notEmptyRows = new ArrayList<>(); Row firstRow = sheet.getRow(10);/*from w ww.j a v a 2s . c om*/ if (firstRow != null) { int rowStart = firstRow.getRowNum(); int rowEnd = sheet.getLastRowNum(); for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) { Row r = sheet.getRow(rowNum); if (r != null) { notEmptyRows.add(r); continue; } } } return notEmptyRows; }
From source file:org.codelabor.example.crud.emp.web.controller.EmpController.java
License:Apache License
private List<EmpDto> fileToDtoList(MultipartFile file, List<String> failureMessages) throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD // by//w w w . j av a 2 s . com // "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); 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.codelabor.example.crud.emp.web.controller.EmpController.java
License:Apache License
private List<EmpDto> fileToDtoList(Part file, List<String> failureMessages) throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD // by/*from ww w . ja va 2 s.c o m*/ // "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); 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.codelabor.example.emp.web.controller.EmpController.java
License:Apache License
private List<EmpDto> fileToDtoList(MultipartFile 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);/* ww w .j ava 2 s. c o 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.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);/*from w w w .j a v a 2s. c o 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.corpus_tools.peppermodules.spreadsheet.Spreadsheet2SaltMapper.java
License:Apache License
/** * get the primary text tiers and their annotations of the given document * /*from www. ja va 2s. c o m*/ * @param workbook * @param timeline */ private void getPrimTextTiers(Workbook workbook, STimeline timeline) { // get all primary text tiers String primaryTextTier = getProps().getPrimaryText(); // seperate string of primary text tiers into list by commas List<String> primaryTextTierList = Arrays.asList(primaryTextTier.split("\\s*,\\s*")); TreeSet<String> annosWithoutPrim = new TreeSet<>(); if (workbook != null) { // get corpus sheet Sheet corpusSheet; // default ("Tabelle1"/ first sheet) if (getProps().getCorpusSheet().equals("Tabelle1")) { corpusSheet = workbook.getSheetAt(0); } else { // get corpus sheet by name corpusSheet = workbook.getSheet(getProps().getCorpusSheet()); } // end of the excel file int lastRow = corpusSheet.getLastRowNum(); mapLinenumber2STimeline(lastRow, timeline); if (corpusSheet != null) { // row with all names of the annotation tiers (first row) Row headerRow = corpusSheet.getRow(0); // List for each primary text and its annotations HashMap<Integer, Integer> annoPrimRelations = new HashMap<>(); List<Integer> primTextPos = new ArrayList<Integer>(); if (headerRow != null) { // iterate through all tiers and save tiers (column number) // that hold the primary data int currColumn = 0; List<String> emptyColumnList = new ArrayList<>(); while (currColumn < headerRow.getPhysicalNumberOfCells()) { if (headerRow.getCell(currColumn) == null || headerRow.getCell(currColumn).toString().isEmpty()) { String emptyColumn = CellReference.convertNumToColString(currColumn); emptyColumnList.add(emptyColumn); currColumn++; continue; } else { if (!emptyColumnList.isEmpty()) { for (String emptyColumn : emptyColumnList) { SpreadsheetImporter.logger.warn("Column \"" + emptyColumn + "\" in document \"" + getResourceURI().lastSegment() + "\" has no name."); } emptyColumnList = new ArrayList<>(); } boolean primWasFound = false; String tierName = headerRow.getCell(currColumn).toString(); if (primaryTextTierList.contains(tierName)) { // current tier contains primary text // save all indexes of tier containing primary // text primTextPos.add(currColumn); primWasFound = true; } else { // current tier contains (other) annotations if (tierName.matches(".+\\[.+\\]") || getProps().getAnnoPrimRel() != null || getProps().getShortAnnoPrimRel() != null) { if (tierName.matches(".+\\[.+\\]")) { // the belonging primary text was set by // the annotator String primTier = tierName.split("\\[")[1].replace("]", ""); setAnnotationPrimCouple(primTier, annoPrimRelations, currColumn, headerRow); primWasFound = true; } String primOfAnnoFromConfig = getPrimOfAnnoPrimRel(tierName.split("\\[")[0]); if (primOfAnnoFromConfig != null) { // current tier is an annotation and the // belonging primary text was set by // property setAnnotationPrimCouple(primOfAnnoFromConfig, annoPrimRelations, currColumn, headerRow); primWasFound = true; } } else if (primaryTextTierList.size() == 1 && getProps().getAnnoPrimRel() == null && getProps().getShortAnnoPrimRel() == null) { // There is only one primary text so we can // safely assume this is the one // the annotation is connected to. setAnnotationPrimCouple(primaryTextTierList.get(0), annoPrimRelations, currColumn, headerRow); primWasFound = true; } } if (!primWasFound) { annosWithoutPrim.add(tierName); } currColumn++; } } } final Map<String, SLayer> layerTierCouples = getLayerTierCouples(); Table<Integer, Integer, CellRangeAddress> mergedCells = null; if (corpusSheet.getNumMergedRegions() > 0) { mergedCells = calculateMergedCellIndex(corpusSheet.getMergedRegions()); } int progressTotalNumberOfColumns = 0; if (!primTextPos.isEmpty()) { progressTotalNumberOfColumns = setPrimText(corpusSheet, primTextPos, annoPrimRelations, headerRow, mergedCells, layerTierCouples); } else { SpreadsheetImporter.logger .warn("No primary text for the document \"" + getResourceURI().lastSegment() + "\" found. Please check the spelling of your properties."); } setAnnotations(annoPrimRelations, corpusSheet, mergedCells, layerTierCouples, progressTotalNumberOfColumns); } if (getProps().getMetaAnnotation()) { setDocMetaData(workbook); } // report if any column was not included if (!annosWithoutPrim.isEmpty()) { SpreadsheetImporter.logger.warn( "No primary text column found for columns\n- {}\nin document {}. This means these columns are not included in the conversion!", Joiner.on("\n- ").join(annosWithoutPrim), getResourceURI().toFileString()); } } }
From source file:org.corpus_tools.peppermodules.spreadsheet.Spreadsheet2SaltMapper.java
License:Apache License
/** * Set the primary text of each document * /*from w ww .j a v a 2 s . c o m*/ * @param column * @param primText * @param corpusSheet * @param primTextPos * @param annoPrimRelations * @param mergedCells * @param layerTierCouples * @return */ private int setPrimText(Sheet corpusSheet, List<Integer> primTextPos, HashMap<Integer, Integer> annoPrimRelations, Row headerRow, Table<Integer, Integer, CellRangeAddress> mergedCells, Map<String, SLayer> layerTierCouples) { // initialize with number of token we have to create int progressTotalNumberOfColumns = primTextPos.size(); // add each annotation to this number int progressProcessedNumberOfColumns = 0; // use formater to ensure that e.g. integers will not be converted into // decimals DataFormatter formatter = new DataFormatter(); // save all tokens of the current primary text List<SToken> currentTokList = new ArrayList<>(); // save all tokens of the current primary text for (int primText : primTextPos) { // initialize primaryText STextualDS primaryText = SaltFactory.createSTextualDS(); StringBuilder currentText = new StringBuilder(); if (headerRow.getCell(primText) != null) { primaryText.setName(headerRow.getCell(primText).toString()); } getDocument().getDocumentGraph().addNode(primaryText); int offset = currentText.length(); SToken lastTok = null; // start with the second row of the table, since the first row holds // the name of each tier int currRow = 1; while (currRow < corpusSheet.getPhysicalNumberOfRows()) { // iterate through all rows of the given corpus sheet Row row = corpusSheet.getRow(currRow); Cell primCell = row.getCell(primText); SToken currTok = null; int endCell = currRow; String text = null; if (primCell != null && !primCell.toString().isEmpty()) { text = formatter.formatCellValue(primCell); } else if (getProps().getIncludeEmptyPrimCells()) { text = ""; } if (text != null) { int start = offset; int end = start + text.length(); offset += text.length(); currentText.append(text); currTok = getDocument().getDocumentGraph().createToken(primaryText, start, end); if (primCell != null) { endCell = getLastCell(primCell, mergedCells); } } if (currTok != null) { if (lastTok != null && getProps().getAddOrderRelation()) { addOrderRelation(lastTok, currTok, headerRow.getCell(primText).toString()); } // add timeline relation addTimelineRelation(currTok, currRow, endCell, corpusSheet); // remember all SToken currentTokList.add(currTok); // insert space between tokens if (text != null && (currRow != corpusSheet.getLastRowNum())) { currentText.append(" "); offset++; } } if (currTok != null) { lastTok = currTok; } currRow++; } // end for each token row primaryText.setText(currentText.toString()); progressProcessedNumberOfColumns++; setProgress((double) progressProcessedNumberOfColumns / (double) progressTotalNumberOfColumns); if (getProps().getLayer() != null) { if (currentTokList != null && layerTierCouples.size() > 0) { if (layerTierCouples.get(primaryText.getName()) != null) { SLayer sLayer = layerTierCouples.get(primaryText.getName()); getDocument().getDocumentGraph().addLayer(sLayer); for (SToken t : currentTokList) { sLayer.addNode(t); } } } } } // end for each primTextPos return progressProcessedNumberOfColumns; }
From source file:org.databene.benerator.template.xmlanon.XmlAnonInputReader.java
License:Open Source License
private static AnonymizationSetup parseXls(String xlsUri) throws IOException, InvalidFormatException { Workbook workbook = WorkbookFactory.create(IOUtil.getInputStreamForURI(xlsUri)); Sheet sheet = workbook.getSheetAt(0); // parse header information int varnameColumnIndex = -1; ArrayList<String> files = new ArrayList<String>(); Row headerRow = sheet.getRow(0);// w ww . j a v a 2s. c o m Assert.notNull(headerRow, "header row"); for (int i = 0; i <= headerRow.getLastCellNum(); i++) { String header = headerRow.getCell(i).getStringCellValue(); if ("varname".equals(header)) { varnameColumnIndex = i; break; } else { if (StringUtil.isEmpty(header)) throw new ConfigurationError( "Filename missing in column header #" + i + " of Excel document " + xlsUri); files.add(header); } } if (varnameColumnIndex == -1) throw new ConfigurationError("No 'varname' header defined in Excel document " + xlsUri); if (files.size() == 0) throw new ConfigurationError("No files specified in Excel document " + xlsUri); // parse anonymization rows List<Anonymization> anonymizations = new ArrayList<Anonymization>(); for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum); if (XLSUtil.isEmpty(row)) continue; Cell varnameCell = row.getCell(varnameColumnIndex); if (varnameCell == null || StringUtil.isEmpty(varnameCell.getStringCellValue())) throw new ConfigurationError("'varname' cell empty in table row #" + (rownum + 1)); Anonymization anon = new Anonymization(varnameCell.getStringCellValue()); // parse locators for (int colnum = 0; colnum < varnameColumnIndex; colnum++) { Cell cell = row.getCell(colnum); String path = (cell != null ? cell.getStringCellValue() : null); if (!StringUtil.isEmpty(path)) { List<String> tokens = XPathTokenizer.tokenize(path); String entityPath = XPathTokenizer.merge(tokens, 0, tokens.size() - 2); String entity = normalizeXMLPath(XPathTokenizer.nodeName(tokens.get(tokens.size() - 2))); String attribute = normalizeXMLPath(tokens.get(tokens.size() - 1)); anon.addLocator(new Locator(files.get(colnum), path, entityPath, entity, attribute)); } } // parse settings for (int colnum = varnameColumnIndex + 1; colnum < row.getLastCellNum() - 1; colnum += 2) { String key = row.getCell(colnum).getStringCellValue(); String value = row.getCell(colnum + 1).getStringCellValue(); if (!StringUtil.isEmpty(key) && !StringUtil.isEmpty(value)) anon.addSetting(key, value); } anonymizations.add(anon); } return new AnonymizationSetup(files, anonymizations); }
From source file:org.datanucleus.store.excel.ExcelPersistenceHandler.java
License:Open Source License
/** * Deletes a persistent object from the database. * @param op The Object Provider of the object to be deleted. * @throws NucleusDataStoreException when an error occurs in the datastore communication * @throws NucleusOptimisticException thrown if version checking fails on an optimistic transaction for this object *//* w ww . java 2 s . c om*/ public void deleteObject(ObjectProvider op) { // Check if read-only so update not permitted assertReadOnlyForUpdateOfObject(op); ExecutionContext ec = op.getExecutionContext(); ManagedConnection mconn = storeMgr.getConnection(ec); try { AbstractClassMetaData cmd = op.getClassMetaData(); if (cmd.isVersioned()) { NucleusLogger.PERSISTENCE.warn( "This datastore doesn't support optimistic version checks since the datastore file is for a single-connection"); } Workbook wb = (Workbook) mconn.getConnection(); Table table = ec.getStoreManager().getStoreDataForClass(op.getClassMetaData().getFullClassName()) .getTable(); final Sheet sheet = ExcelUtils.getSheetForClass(op, wb, table); // Invoke any cascade deletion op.loadUnloadedFields(); op.provideFields(cmd.getAllMemberPositions(), new DeleteFieldManager(op)); // Delete this object long startTime = System.currentTimeMillis(); if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) { NucleusLogger.DATASTORE_PERSIST.debug( Localiser.msg("Excel.Delete.Start", op.getObjectAsPrintable(), op.getInternalObjectId())); } int rowId = ExcelUtils.getRowNumberForObjectInWorkbook(op, wb, false, table); if (rowId < 0) { throw new NucleusObjectNotFoundException("object not found", op.getObject()); } if (storeMgr instanceof XLSStoreManager && sheet.getLastRowNum() == rowId) { // Deleting top row which is last row so just remove all cells and leave row // otherwise Apache POI throws an ArrayIndexOutOfBoundsException Row row = sheet.getRow(rowId); Iterator<Cell> it = row.cellIterator(); while (it.hasNext()) { row.removeCell(it.next()); } } else { // Deleting top row so remove it sheet.removeRow(sheet.getRow(rowId)); if (sheet.getLastRowNum() > rowId) { sheet.shiftRows(rowId + 1, sheet.getLastRowNum(), -1); } } if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) { NucleusLogger.DATASTORE_PERSIST .debug(Localiser.msg("Excel.ExecutionTime", (System.currentTimeMillis() - startTime))); } if (ec.getStatistics() != null) { ec.getStatistics().incrementNumWrites(); ec.getStatistics().incrementDeleteCount(); } } finally { mconn.release(); } }
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 . j a va2 s.co 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; }