List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getRowNum
@Override public int getRowNum()
From source file:kupkb_experiments.ExperimentSpreadSheetParser.java
License:Open Source License
private void firstPass(HSSFRow row) { // first pass, looking for experiment ID and where the role and compound list are located Iterator i = row.cellIterator(); while (i.hasNext()) { Cell cell = (Cell) i.next();//from www .ja v a2s . c om // System.out.println(cell.getCellType()); cell.setCellType(HSSFCell.CELL_TYPE_STRING); // if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { // // } if (SpreadhseetVocabulary.isValid(cell.getStringCellValue())) { // put this in the map, and the value of the next cell if (keyValue.get(cell.getStringCellValue().toLowerCase()) == null) { keyValue.put(cell.getStringCellValue().toLowerCase(), new ArrayList<Cell>()); } keyValue.get(cell.getStringCellValue().toLowerCase()).add(cell); if (cell.getStringCellValue().toLowerCase().equals(SpreadhseetVocabulary.ROLE.getKeyName())) { roleCell.add(cell); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.GENE_SYMBOL.getKeyName())) { compoundListStart = row.getRowNum() + 1; compoundAttributeToColumn.put(SpreadhseetVocabulary.GENE_SYMBOL.getKeyName(), cell.getColumnIndex()); } // else if (cell.getStringCellValue().toLowerCase().equals(SpreadhseetVocabulary.GENE_SYMBOL.getKeyName()) && cell.getColumnIndex() != 9) { // compoundAttributeToColumn.put(SpreadhseetVocabulary.GENE_SYMBOL.getKeyName(), cell.getColumnIndex()); // } else if (cell.getStringCellValue().toLowerCase().equals(SpreadhseetVocabulary.GENE_ID.getKeyName()) && cell.getColumnIndex() != 9) { compoundAttributeToColumn.put(SpreadhseetVocabulary.GENE_ID.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.ENTREZ_GENE_ID.getKeyName()) && cell.getColumnIndex() != 9) { compoundAttributeToColumn.put(SpreadhseetVocabulary.ENTREZ_GENE_ID.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.UNIPROT_ID.getKeyName()) && cell.getColumnIndex() != 9) { compoundAttributeToColumn.put(SpreadhseetVocabulary.UNIPROT_ID.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.UNIPROT_ACC.getKeyName()) && cell.getColumnIndex() != 9) { compoundAttributeToColumn.put(SpreadhseetVocabulary.UNIPROT_ACC.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.HMDB_ID.getKeyName()) && cell.getColumnIndex() != 9) { compoundAttributeToColumn.put(SpreadhseetVocabulary.HMDB_ID.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.MICROCOSM.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.MICROCOSM.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.EXPRESSION_STRENGTH.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.EXPRESSION_STRENGTH.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.DIFFERENTIAL.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.DIFFERENTIAL.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.RATIO.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.RATIO.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.P_VALUE.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.P_VALUE.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase().equals(SpreadhseetVocabulary.FDR.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.FDR.getKeyName(), cell.getColumnIndex()); } } } }
From source file:net.intelliant.marketing.ContactListServices.java
License:Open Source License
@SuppressWarnings("unchecked") private static GenericValue insertIntoConfiguredCustomEntity(GenericDelegator delegator, Locale locale, String userLoginId, String entityName, HSSFRow excelRowData, Map<String, Object> columnMapper, Map<Integer, String> errorDetails) throws GenericEntityException, ParseException { ModelEntity modelEntity = delegator.getModelEntity(entityName); String entityPrimaryKeyField = modelEntity.getFirstPkFieldName(); String entityPrimaryKey = delegator.getNextSeqId(entityName); GenericValue rowToInsertGV = delegator.makeValue(entityName); rowToInsertGV.put(entityPrimaryKeyField, entityPrimaryKey); rowToInsertGV.put("importedOnDateTime", UtilDateTime.nowTimestamp()); rowToInsertGV.put("importedByUserLogin", userLoginId); boolean isErrorFound = false; Set<Entry<String, Object>> entries = columnMapper.entrySet(); for (Map.Entry<String, Object> entry : entries) { String columnName = entry.getKey(); ModelField modelField = modelEntity.getField(columnName); HSSFCell excelCell = null;/*w ww . j a v a 2 s. co m*/ Object cellValue = null; short columnIndex = -1; try { columnIndex = Short.parseShort(String.valueOf(entry.getValue())); excelCell = excelRowData.getCell(columnIndex); cellValue = (excelCell != null) ? excelCell.toString() : ""; } catch (NumberFormatException nfe) { cellValue = ""; } if (Debug.infoOn()) { Debug.logInfo( "[insertIntoConfiguredCustomEntity] Checking excel row No. >> " + excelRowData.getRowNum(), MODULE); Debug.logInfo("[insertIntoConfiguredCustomEntity] Checking excel columnIndex >> " + columnIndex, MODULE); if (excelCell != null) { Debug.logInfo("[insertIntoConfiguredCustomEntity] Checking excel column type >> " + excelCell.getCellType(), MODULE); } else { Debug.logInfo("[insertIntoConfiguredCustomEntity] excelCell found NULL for columnIndex >> " + columnIndex, MODULE); } Debug.logInfo("[insertIntoConfiguredCustomEntity] Checking model field >> " + modelField.getName(), MODULE); Debug.logInfo("[insertIntoConfiguredCustomEntity] Initial cellValue >> " + cellValue, MODULE); } if (modelField.getIsNotNull()) { if (!UtilValidate.isNotEmpty(cellValue)) { Map<String, Object> messageMap = UtilMisc.toMap("columnName", modelField.getDescription()); errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperIsEmpty", messageMap, locale)); isErrorFound = true; } } if (modelField.getType().equals("email")) { if (!(UtilValidate.isNotEmpty(cellValue) && UtilCommon.isValidEmailAddress(String.valueOf(cellValue)))) { Map<String, Object> messageMap = UtilMisc.toMap("columnName", cellValue); errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperNotValidEmail", messageMap, locale)); isErrorFound = true; } } else if (modelField.getType().equals("tel-number")) { if (UtilValidate.isNotEmpty(cellValue)) { Map<String, Object> messageMap = UtilMisc.toMap("columnName", cellValue); if (excelCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { if (Debug.infoOn()) { Debug.logInfo("[insertIntoConfiguredCustomEntity] Cell type is numeric", MODULE); } NumberFormat testNumberFormat = NumberFormat.getNumberInstance(); try { cellValue = (pattern.parse(testNumberFormat.format(excelCell.getNumericCellValue()))) .longValue(); } catch (ParseException e) { errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperNotValidPhoneNO", messageMap, locale)); isErrorFound = true; } } if (!UtilValidate.isInternationalPhoneNumber(String.valueOf(cellValue))) { errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperNotValidPhoneNO", messageMap, locale)); isErrorFound = true; } } } else if (modelField.getType().equals("date")) { try { cellValue = new java.sql.Date(excelCell.getDateCellValue().getTime()); } catch (Exception e) { cellValue = excelCell.toString(); Map<String, Object> messageMap = UtilMisc.toMap("columnName", cellValue); errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperNotValidDate", messageMap, locale)); isErrorFound = true; } if (!UtilValidate.isNotEmpty(cellValue)) { Map<String, Object> messageMap = UtilMisc.toMap("columnName", cellValue); errorDetails.put((int) columnIndex, UtilProperties.getMessage(resource, "ErrorImportMapperNotValidDate", messageMap, locale)); isErrorFound = true; } } if (Debug.infoOn()) { Debug.logInfo("[insertIntoConfiguredCustomEntity] Final cellValue >> " + cellValue, MODULE); } rowToInsertGV.put(columnName, cellValue); } if (isErrorFound) { throw new GenericEntityException("Errors found in spread sheet data"); } else { delegator.storeAll(UtilMisc.toList(rowToInsertGV)); } return rowToInsertGV; }
From source file:org.amanzi.splash.importer.ExcelImporter.java
License:Open Source License
/** * Creates a child spreadsheet inside parent spreadsheet * * @param sheet sheet to import/*ww w . j a v a 2 s . c o m*/ * @param sheetName name of sheet * @param monitor monitor */ @SuppressWarnings(value = { "deprecation", "unchecked" }) private void createSheet(HSSFSheet sheet, String sheetName, Transaction transaction) { spreadsheetNode = null; spreadsheetName = sheetName; createSpreadsheet(rootSpreadsheet); try { Iterator<HSSFRow> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = rows.next(); //display row number in the console. LOGGER.debug("Row No.: " + row.getRowNum()); //once get a row its time to iterate through cells. Iterator<HSSFCell> cells = row.cellIterator(); int R = row.getRowNum(); if ((R % 20) == 0) { updateTransaction(transaction); } while (cells.hasNext()) { HSSFCell cell = cells.next(); LOGGER.debug("Cell No.: " + cell.getCellNum()); int C = cell.getCellNum(); /* * Now we will get the cell type and display the values * accordingly. */ switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { // cell type numeric. LOGGER.debug("===================================================="); LOGGER.debug("Numeric value: " + cell.getNumericCellValue()); LOGGER.debug("===================================================="); String def = Double.toString(cell.getNumericCellValue()); Cell c = new Cell(R, C, def, def, new CellFormat()); //TODO: interpet!!!!!! //Cell c = model.interpret(def, R, C); saveCell(c); break; } case HSSFCell.CELL_TYPE_STRING: { // cell type string. HSSFRichTextString richTextString = cell.getRichStringCellValue(); LOGGER.debug("===================================================="); LOGGER.debug("String value: " + richTextString.getString()); LOGGER.debug("===================================================="); Cell c = new Cell(R, C, richTextString.getString(), richTextString.getString(), new CellFormat()); saveCell(c); break; } case HSSFCell.CELL_TYPE_FORMULA: // cell type string. String cellFormula = "=" + cell.getCellFormula().toLowerCase(); Cell c = new Cell(R, C, cellFormula, cellFormula, new CellFormat()); //TODO: interpet!!!!!! //Cell c = model.interpret(def, R, C); saveCell(c); LOGGER.debug("===================================================="); LOGGER.debug("Formula value: " + cellFormula); LOGGER.debug("===================================================="); break; default: { // types other than String and Numeric. LOGGER.debug("Type not supported."); break; } } } } } finally { updateTransaction(transaction); } }
From source file:org.apache.cocoon.generation.HSSFGenerator.java
License:Apache License
/** * Writes out the workbook data as XML, without formatting information *//* ww w . ja v a 2 s . c o m*/ private void writeXML(HSSFWorkbook workbook) throws SAXException { this.contentHandler.startDocument(); start("Workbook"); start("SheetNameIndex"); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { start("SheetName"); data(workbook.getSheetName(i)); end("SheetName"); } end("SheetNameIndex"); start("Sheets"); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); start("Sheet"); start("Name"); data(workbook.getSheetName(i)); end("Name"); start("MaxCol"); data(Integer.toString(getMaxCol(sheet))); end("MaxCol"); start("MaxRow"); data(Integer.toString(sheet.getLastRowNum())); end("MaxRow"); if (formatting) { writeStyles(workbook, sheet); } start("Cells"); final Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { final HSSFRow row = (HSSFRow) rows.next(); final Iterator cells = row.cellIterator(); while (cells.hasNext()) { final HSSFCell cell = (HSSFCell) cells.next(); attribute("Row", Integer.toString(row.getRowNum())); attribute("Col", Short.toString(cell.getCellNum())); attribute("ValueType", getValueType(cell.getCellType())); start("Cell"); data(getValue(cell)); end("Cell"); } } end("Cells"); end("Sheet"); } end("Sheets"); end("Workbook"); this.contentHandler.endDocument(); }
From source file:org.apache.cocoon.generation.HSSFGenerator.java
License:Apache License
/** * Writes out the workbook data as XML, with formatting information */// w ww . j ava 2 s .c o m private void writeStyles(HSSFWorkbook workbook, HSSFSheet sheet) throws SAXException { start("Styles"); HSSFRow row = null; HSSFCell cell = null; Iterator cells = null; Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (HSSFRow) rows.next(); cells = row.cellIterator(); while (cells.hasNext()) { cell = (HSSFCell) cells.next(); attribute("startRow", Integer.toString(row.getRowNum())); attribute("endRow", Integer.toString(row.getRowNum())); attribute("startCol", Short.toString(cell.getCellNum())); attribute("endCol", Short.toString(cell.getCellNum())); start("StyleRegion"); HSSFCellStyle style = cell.getCellStyle(); attribute("HAlign", Integer.toString(style.getAlignment())); attribute("VAlign", Integer.toString(style.getVerticalAlignment())); attribute("WrapText", ((style.getWrapText()) ? "1" : "0")); attribute("Orient", Integer.toString(style.getRotation())); attribute("Indent", Integer.toString(style.getIndention())); attribute("Locked", ((style.getLocked()) ? "1" : "0")); attribute("Hidden", ((style.getHidden()) ? "1" : "0")); attribute("Fore", workbook.getCustomPalette().getColor(style.getFillForegroundColor()).getHexString()); attribute("Back", workbook.getCustomPalette().getColor(style.getFillBackgroundColor()).getHexString()); attribute("PatternColor", Integer.toString(style.getFillPattern())); // TODO attribute("Format", "General"); // TODO start("Style"); HSSFFont font = workbook.getFontAt(style.getFontIndex()); attribute("Unit", Short.toString(font.getFontHeightInPoints())); attribute("Bold", Short.toString(font.getBoldweight())); attribute("Italic", ((font.getItalic()) ? "1" : "0")); attribute("Unterline", Integer.toString(font.getUnderline())); attribute("StrikeThrough", ((font.getStrikeout()) ? "1" : "0")); start("Font"); data(font.getFontName()); end("Font"); end("Style"); end("StyleRegion"); } } end("Styles"); }
From source file:org.apache.ofbiz.product.spreadsheetimport.ImportProductServices.java
License:Apache License
/** * This method is responsible to import spreadsheet data into "Product" and * "InventoryItem" entities into database. The method uses the * ImportProductHelper class to perform its operation. The method uses "Apache * POI" api for importing spreadsheet (xls files) data. * * Note : Create the spreadsheet directory in the ofbiz home folder and keep * your xls files in this folder only.// w w w .j ava2 s . c o m * * @param dctx the dispatch context * @param context the context * @return the result of the service execution * @throws IOException */ public static Map<String, Object> productImportFromSpreadsheet(DispatchContext dctx, Map<String, ? extends Object> context) throws IOException { Delegator delegator = dctx.getDelegator(); Locale locale = (Locale) context.get("locale"); // System.getProperty("user.dir") returns the path upto ofbiz home // directory String path = System.getProperty("user.dir") + "/spreadsheet"; List<File> fileItems = new LinkedList<File>(); if (UtilValidate.isNotEmpty(path)) { File importDir = new File(path); if (importDir.isDirectory() && importDir.canRead()) { File[] files = importDir.listFiles(); // loop for all the containing xls file in the spreadsheet // directory for (int i = 0; i < files.length; i++) { if (files[i].getName().toUpperCase().endsWith("XLS")) { fileItems.add(files[i]); } } } else { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportDirectoryNotFound", locale)); } } else { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportPathNotSpecified", locale)); } if (fileItems.size() < 1) { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportPathNoSpreadsheetExists", locale) + path); } for (File item : fileItems) { // read all xls file and create workbook one by one. List<Map<String, Object>> products = new LinkedList<Map<String, Object>>(); List<Map<String, Object>> inventoryItems = new LinkedList<Map<String, Object>>(); POIFSFileSystem fs = null; HSSFWorkbook wb = null; try { fs = new POIFSFileSystem(new FileInputStream(item)); wb = new HSSFWorkbook(fs); } catch (IOException e) { Debug.logError("Unable to read or create workbook from file", module); return ServiceUtil.returnError(UtilProperties.getMessage(resource, "ProductProductImportCannotCreateWorkbookFromFile", locale)); } // get first sheet HSSFSheet sheet = wb.getSheetAt(0); wb.close(); int sheetLastRowNumber = sheet.getLastRowNum(); for (int j = 1; j <= sheetLastRowNumber; j++) { HSSFRow row = sheet.getRow(j); if (row != null) { // read productId from first column "sheet column index // starts from 0" HSSFCell cell2 = row.getCell(2); cell2.setCellType(HSSFCell.CELL_TYPE_STRING); String productId = cell2.getRichStringCellValue().toString(); // read QOH from ninth column HSSFCell cell5 = row.getCell(5); BigDecimal quantityOnHand = BigDecimal.ZERO; if (cell5 != null && cell5.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) quantityOnHand = new BigDecimal(cell5.getNumericCellValue()); // check productId if null then skip creating inventory item // too. boolean productExists = ImportProductHelper.checkProductExists(productId, delegator); if (productId != null && !productId.trim().equalsIgnoreCase("") && !productExists) { products.add(ImportProductHelper.prepareProduct(productId)); if (quantityOnHand.compareTo(BigDecimal.ZERO) >= 0) inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, quantityOnHand, delegator.getNextSeqId("InventoryItem"))); else inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, BigDecimal.ZERO, delegator.getNextSeqId("InventoryItem"))); } int rowNum = row.getRowNum() + 1; if (row.toString() != null && !row.toString().trim().equalsIgnoreCase("") && productExists) { Debug.logWarning("Row number " + rowNum + " not imported from " + item.getName(), module); } } } // create and store values in "Product" and "InventoryItem" entity // in database for (int j = 0; j < products.size(); j++) { GenericValue productGV = delegator.makeValue("Product", products.get(j)); GenericValue inventoryItemGV = delegator.makeValue("InventoryItem", inventoryItems.get(j)); if (!ImportProductHelper.checkProductExists(productGV.getString("productId"), delegator)) { try { delegator.create(productGV); delegator.create(inventoryItemGV); } catch (GenericEntityException e) { Debug.logError("Cannot store product", module); return ServiceUtil.returnError(UtilProperties.getMessage(resource, "ProductProductImportCannotStoreProduct", locale)); } } } int uploadedProducts = products.size() + 1; if (products.size() > 0) Debug.logInfo("Uploaded " + uploadedProducts + " products from file " + item.getName(), module); } return ServiceUtil.returnSuccess(); }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
@Transactional public String upload() { try {//from www.ja v a 2s .com insertQuery = persistenceService.getSession().createSQLQuery(insertsql); final Bankaccount ba = (Bankaccount) persistenceService.find("from Bankaccount ba where id=?", Long.valueOf(accountId)); accNo = ba.getAccountnumber(); final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(bankStatmentInXls)); final HSSFWorkbook wb = new HSSFWorkbook(fs); wb.getNumberOfSheets(); final HSSFSheet sheet = wb.getSheetAt(0); sheet.getFirstRowNum(); // Validating selected bankaccount and BankStatements bankaccount final HSSFRow row = sheet.getRow(ACCOUNTNUMBER_ROW_INDEX); if (row == null) { bank_account_not_match_msg = bank_account_not_match_msg.replace("#name", bankStatmentInXlsFileName); throw new ValidationException( Arrays.asList(new ValidationError(bank_account_not_match_msg, bank_account_not_match_msg))); } String strValue2 = getStrValue(row.getCell(0)); strValue2 = strValue2.substring(strValue2.indexOf(':') + 1, strValue2.indexOf('-')).trim(); if (!strValue2.equals(accNo.trim())) { bank_account_not_match_msg = bank_account_not_match_msg.replace("#name", bankStatmentInXlsFileName); throw new ValidationException( Arrays.asList(new ValidationError(bank_account_not_match_msg, bank_account_not_match_msg))); } AutoReconcileBean ab = null; HSSFRow detailRow = null; String dateStr = null; rowIndex = STARTOF_DETAIL_ROW_INDEX; count = 0; do { try { ab = new AutoReconcileBean(); if (rowIndex == STARTOF_DETAIL_ROW_INDEX) { detailRow = sheet.getRow(rowIndex); if (rowIndex >= 9290) if (LOGGER.isDebugEnabled()) LOGGER.debug(String.valueOf(detailRow.getRowNum())); dateStr = getStrValue(detailRow.getCell(TXNDT_INDEX)); if (alreadyUploaded(dateStr)) { file_already_uploaded = file_already_uploaded.replace("#name", bankStatmentInXlsFileName); throw new ValidationException(Arrays .asList(new ValidationError(file_already_uploaded, file_already_uploaded))); } ab.setTxDateStr(dateStr); } ab.setTxDateStr(dateStr); ab.setInstrumentNo(getStrValue(detailRow.getCell(CHEQUENO_INDEX))); // if(strValue!=null) // ab.setInstrumentNo(strValue.replaceFirst(".0", "")); ab.setDebit(getNumericValue(detailRow.getCell(DEBIT_INDEX))); ab.setCredit(getNumericValue(detailRow.getCell(CREDIT_INDEX))); ab.setBalance(getNumericValue(detailRow.getCell(BALANCE_INDEX))); String strValue = getStrValue(detailRow.getCell(NARRATION_INDEX)); if (strValue != null) { if (strValue.length() > 125) strValue = strValue.substring(0, 125); // strValue=strValue.replaceFirst(".0", ""); ab.setNarration(strValue); } ab.setType(getStrValue(detailRow.getCell(TYPE_INDEX))); ab.setCSLno(getStrValue(detailRow.getCell(CSLNO_INDEX))); // if(ab.getType()==null) // ab.setType("CLG"); if (LOGGER.isInfoEnabled()) LOGGER.info(detailRow.getRowNum() + " " + ab.toString()); insert(ab); if (count % 20 == 0) persistenceService.getSession().flush(); } catch (ValidationException ve) { throw ve; } catch (final NumberFormatException e) { if (!isFailed) failureMessage += detailRow.getRowNum() + 1; else failureMessage += " , " + detailRow.getRowNum() + 1; isFailed = true; throw new ValidationException( Arrays.asList(new ValidationError(failureMessage, failureMessage))); } rowIndex++; count++; detailRow = sheet.getRow(rowIndex); if (detailRow != null) dateStr = getStrValue(detailRow.getCell(TXNDT_INDEX)); else dateStr = null; // ab.setTxDateStr(detailRow.getRowNum()+"-->" + dateStr); } while (dateStr != null && !dateStr.isEmpty()); if (isFailed) throw new ValidationException(Arrays.asList(new ValidationError(failureMessage, failureMessage))); else { final FileStoreMapper fileStore = fileStoreService.store(getBankStatmentInXls(), bankStatmentInXlsFileName, "application/vnd.ms-excel", FinancialConstants.MODULE_NAME_APPCONFIG, false); persistenceService.persist(fileStore); String fileStoreId = fileStore.getFileStoreId(); DocumentUpload upload = new DocumentUpload(); upload.setFileStore(fileStore); upload.setObjectId(accountId.longValue()); upload.setObjectType(FinancialConstants.BANK_STATEMET_OBJECT); upload.setUploadedDate(new Date()); documentUploadRepository.save(upload); message = successMessage.replace("#", "" + count); } } catch (final FileNotFoundException e) { throw new ValidationException( Arrays.asList(new ValidationError("File cannot be uploaded", "File cannot be uploaded"))); } catch (final IOException e) { throw new ValidationException(Arrays .asList(new ValidationError("Unable to read uploaded file", "Unable to read uploaded file"))); } catch (final ValidationException ve) { throw ve; } catch (final NullPointerException npe) { throw new ValidationException( Arrays.asList(new ValidationError(bankStatementFormat, bankStatementFormat))); } catch (final Exception e) { throw new ValidationException( Arrays.asList(new ValidationError(bankStatementFormat, bankStatementFormat))); } return "upload"; }
From source file:org.encuestame.business.search.IndexerFile.java
License:Apache License
/** * Extract spreadsheets content./* w w w . ja v a 2s . c o m*/ * @param workBook * @return * @throws Exception */ public static String extractContentSpreadsheetsDocument(final HSSFWorkbook workBook) throws Exception { StringBuilder contents = new StringBuilder(); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { HSSFSheet sheet = workBook.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); // Display the row number log.debug(row.getRowNum()); Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); // Display the cell number of the current Row switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { log.debug(String.valueOf(cell.getNumericCellValue())); contents.append(String.valueOf(cell.getNumericCellValue())).append(" "); break; } case HSSFCell.CELL_TYPE_STRING: { HSSFRichTextString richTextString = cell.getRichStringCellValue(); log.debug(richTextString.toString()); contents.append(richTextString.toString()).append(" "); break; } case HSSFCell.CELL_TYPE_BOOLEAN: { contents.append(String.valueOf(cell.getBooleanCellValue())).append(" "); break; } } } } } return contents.toString(); }
From source file:org.encuestame.business.search.SearchUtils.java
License:Apache License
/** * Create Spreadsheets Document.//from w w w . j a va 2s. c o m * @param file Spreadsheet {@link File}. * @param Long attachmentId. * @return {@link Document} * @throws FileNotFoundException */ public static Document createSpreadsheetsDocument(final File file) throws Exception { InputStream is = new FileInputStream(file); StringBuilder contents = new StringBuilder(); POIFSFileSystem fileSystem = new POIFSFileSystem(is); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { HSSFSheet sheet = workBook.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); // Display the row number log.debug(row.getRowNum()); Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); // Display the cell number of the current Row switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { log.debug(String.valueOf(cell.getNumericCellValue())); contents.append(String.valueOf(cell.getNumericCellValue())).append(" "); break; } case HSSFCell.CELL_TYPE_STRING: { HSSFRichTextString richTextString = cell.getRichStringCellValue(); log.debug(richTextString.toString()); contents.append(richTextString.toString()).append(" "); break; } case HSSFCell.CELL_TYPE_BOOLEAN: { contents.append(String.valueOf(cell.getBooleanCellValue())).append(" "); break; } } } } } Document doc = SearchUtils.addFields(file, contents.toString()); return doc; }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { // Get the source / new row HSSFRow newRow = worksheet.getRow(destinationRowNum); HSSFRow sourceRow = worksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {// w ww .j a va2s. c o m newRow = worksheet.createRow(destinationRowNum); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell HSSFCell oldCell = sourceRow.getCell(i); HSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell HSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } }