List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:org.apdplat.platform.generator.ModelGenerator.java
License:Open Source License
/** * ???EXCEL/*from ww w . ja v a 2 s. c o m*/ * ?EXCELJAVA * @param inputStream ??EXCEL * @return JAVA */ private static List<ModelInfo> readModelInfos(InputStream inputStream) { List<ModelInfo> models = new ArrayList<>(); try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); try { HSSFRow row = sheet.getRow(2); if (row == null) { LOG.info("???" + sheet.getSheetName()); continue; } HSSFCell cell = row.getCell(1); //?? String modelPackage = cell.getStringCellValue(); row = sheet.getRow(3); cell = row.getCell(1); //?? String modelEnglish = cell.getStringCellValue(); row = sheet.getRow(4); cell = row.getCell(1); //?? String modelChinese = cell.getStringCellValue(); ModelInfo modelInfo = new ModelInfo(); modelInfo.setModelPackage(modelPackage); modelInfo.setModelEnglish(modelEnglish); modelInfo.setModelChinese(modelChinese); int rows = sheet.getPhysicalNumberOfRows(); //8?? for (int rowNumber = 7; rowNumber < rows; rowNumber++) { HSSFRow oneRow = sheet.getRow(rowNumber); if (oneRow == null) { continue; } Attr attr = new Attr(); //?? HSSFCell oneCell = oneRow.getCell(0); if (oneCell != null) { String cellValue = oneCell.getStringCellValue(); if (cellValue != null && !"".equals(cellValue.trim()) && !"null".equals(cellValue.trim().toLowerCase())) { attr.setDes(cellValue); } else { continue; } } //?? oneCell = oneRow.getCell(1); if (oneCell != null) { String cellValue = oneCell.getStringCellValue(); if (cellValue != null && !"".equals(cellValue.trim()) && !"null".equals(cellValue.trim().toLowerCase())) { attr.setName(cellValue); } else { continue; } } // oneCell = oneRow.getCell(2); if (oneCell != null) { String cellValue = oneCell.getStringCellValue(); if (cellValue != null && !"".equals(cellValue.trim()) && !"null".equals(cellValue.trim().toLowerCase())) { attr.setType(AttrType.validType(cellValue)); } else { attr.setType(AttrType.validType("String")); } } //?string oneCell = oneRow.getCell(3); if (oneCell != null) { if (oneCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String cellValue = oneCell.getStringCellValue(); if (cellValue != null && !"".equals(cellValue.trim()) && !"null".equals(cellValue.trim().toLowerCase())) { try { int length = Integer.parseInt(cellValue); attr.setLength(length); } catch (Exception e) { LOG.error("?" + cellValue); } } } if (oneCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { double length = oneCell.getNumericCellValue(); attr.setLength((int) length); } } //?? oneCell = oneRow.getCell(4); if (oneCell != null) { boolean cellValue = oneCell.getBooleanCellValue(); attr.setSearchable(cellValue); } //?? oneCell = oneRow.getCell(5); if (oneCell != null) { boolean cellValue = oneCell.getBooleanCellValue(); attr.setRenderIgnore(cellValue); } //?? oneCell = oneRow.getCell(6); if (oneCell != null) { String cellValue = oneCell.getStringCellValue(); if (cellValue != null && !"".equals(cellValue.trim()) && !"null".equals(cellValue.trim().toLowerCase())) { attr.setDic(DicType.validType(cellValue)); } } //??? oneCell = oneRow.getCell(7); if (oneCell != null) { String cellValue = oneCell.getStringCellValue(); if (cellValue != null && !"".equals(cellValue.trim()) && !"null".equals(cellValue.trim().toLowerCase())) { attr.setDicName(cellValue); } } // oneCell = oneRow.getCell(8); if (oneCell != null) { String cellValue = oneCell.getStringCellValue(); if (cellValue != null && !"".equals(cellValue.trim()) && !"null".equals(cellValue.trim().toLowerCase())) { attr.setMap(MapType.validType(cellValue)); } } // oneCell = oneRow.getCell(9); if (oneCell != null) { String cellValue = oneCell.getStringCellValue(); if (cellValue != null && !"".equals(cellValue.trim()) && !"null".equals(cellValue.trim().toLowerCase())) { attr.setAttrRef(cellValue); } } if ("Date".equals(attr.getType()) || "Time".equals(attr.getType())) { modelInfo.setHasDateTime(true); } if ("DicItem".equals(attr.getType())) { modelInfo.setHasDicItem(true); //DicItemdicSimpleDic if (!"SimpleDic".equals(attr.getDic()) && !"TreeDic".equals(attr.getDic())) { attr.setDic("SimpleDic"); } if (attr.getDicName() == null || "".equals(attr.getDicName())) { attr.setDicName(attr.getName()); } } modelInfo.addAttr(attr); } models.add(modelInfo); } catch (Exception e) { LOG.error("?:" + sheet.getSheetName() + " ", e); } } } catch (IOException e) { LOG.error("?MODEL", e); } return models; }
From source file:org.bbreak.excella.core.handler.DebugErrorHandler.java
License:Open Source License
/** * ?/*from ww w . j a v a 2s . c om*/ * * @param workbook * @param errorCell * @param exception */ protected void markupErrorCell(Workbook workbook, ParseException exception) { Cell errorCell = exception.getCell(); if (errorCell == null) { return; } // ???? workbook.setActiveSheet(workbook.getSheetIndex(errorCell.getSheet())); errorCell.setAsActiveCell(); if (workbook instanceof XSSFWorkbook) { XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook; CellStyle errorCellStyle = xssfWorkbook.createCellStyle(); errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex()); errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); errorCell.setCellStyle(errorCellStyle); // TODO:??????????????? // XSSFComment xssfComment = ((XSSFSheet)sheet).createComment(); // xssfComment.setRow( errorCell.getRowIndex()); // xssfComment.setColumn( (short)errorCell.getColumnIndex()); // XSSFRichTextString string = new XSSFRichTextString( ex.getMessage()); // xssfComment.setString( ex.getMessage()); } else { HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook; int sheetNum = hssfWorkbook.getNumberOfSheets(); for (int cnt = 0; cnt < sheetNum; cnt++) { hssfWorkbook.getSheetAt(cnt).setSelected(false); } // ? CellStyle errorCellStyle = hssfWorkbook.createCellStyle(); errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex()); errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); errorCell.setCellStyle(errorCellStyle); // ? short commentColFrom = (short) (errorCell.getColumnIndex() + 1); short commentColTo = (short) (errorCell.getColumnIndex() + ERROR_COMENT_COL_SIZE); int commentRowFrom = errorCell.getRowIndex(); int commentRowTo = errorCell.getRowIndex() + ERROR_COMENT_ROW_SIZE; HSSFSheet hssfSheet = (HSSFSheet) errorCell.getSheet(); HSSFPatriarch patr = hssfSheet.createDrawingPatriarch(); hssfSheet.setSelected(true); HSSFComment comment = patr.createComment( new HSSFClientAnchor(0, 0, 0, 0, commentColFrom, commentRowFrom, commentColTo, commentRowTo)); comment.setVisible(true); comment.setString(new HSSFRichTextString(createCommentMessage(exception))); errorCell.setCellComment(comment); } }
From source file:org.beanfuse.struts2.action.EntityDrivenAction.java
License:GNU General Public License
/** * /*from w w w .jav a 2 s.com*/ * * @param upload * @param clazz * @return */ protected EntityImporter buildEntityImporter(String upload, Class clazz) { try { File[] files = (File[]) ActionContext.getContext().getParameters().get(upload); if (files == null || files.length < 1) { logger.error("cannot get {} file.", upload); } String fileName = get(upload + "FileName"); InputStream is = new FileInputStream(files[0]); if (fileName.endsWith(".xls")) { HSSFWorkbook wb = new HSSFWorkbook(is); if (wb.getNumberOfSheets() < 1 || wb.getSheetAt(0).getLastRowNum() == 0) { return null; } EntityImporter importer = (clazz == null) ? new DefaultEntityImporter() : new DefaultEntityImporter(clazz); importer.setReader(new ExcelItemReader(wb, 1)); put("importer", importer); return importer; } else { LineNumberReader reader = new LineNumberReader(new InputStreamReader(is)); if (null == reader.readLine()) return null; reader.reset(); EntityImporter importer = (clazz == null) ? new DefaultEntityImporter() : new DefaultEntityImporter(clazz); importer.setReader(new CSVReader(reader)); return importer; } } catch (Exception e) { logger.error("error", e); return null; } }
From source file:org.drools.informer.load.spreadsheet.WorkbookData.java
License:Apache License
/** * Open and load the workbook sheets. Note: any sheet with an "!" in the name will be ignored. * /* w w w .j av a 2s . c om*/ * @param filename * @return */ public boolean loadWorkbook(String filename) { try { logger.debug("\n\n\nPROCESSING FILE: " + filename); InputStream inp = new FileInputStream(filename); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp)); for (int i = 0; i < wb.getNumberOfSheets(); i++) { HSSFSheet sheet = wb.getSheetAt(i); SpreadsheetData sheetData = new SpreadsheetData(sheet); String sheetName = sheet.getSheetName(); if (sheetName.indexOf("!") >= 0) { logger.debug("Ignoring sheet named: " + sheetName); continue; } data.put(sheetName, sheetData); sheetList.add(sheetName); } inp.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return false; } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } return true; }
From source file:org.eclipse.birt.report.data.oda.excel.impl.util.ExcelFileReader.java
License:Open Source License
public static List<String> getSheetNamesInExcelFile(Object file) throws MalformedURLException, IOException { String extension = getExtensionName(file); InputStream fis = ResourceLocatorUtil.getURIStream(file); List<String> sheetNames = new ArrayList<String>(); try {// w ww . j a v a 2s . c o m // using uri, we may not know the extension name of the file. if (isXlsxFile(extension)) { XlsxFileReader poiRdr = new XlsxFileReader(fis); LinkedHashMap<String, String> lxlsxWorkSheetList = poiRdr.getSheetNames(); for (Map.Entry<String, String> entry : lxlsxWorkSheetList.entrySet()) { sheetNames.add(entry.getKey()); } } else if (isXlsFile(extension)) { //Only called in design env HSSFWorkbook lworkBook = new HSSFWorkbook(fis); for (int i = 0; i < lworkBook.getNumberOfSheets(); i++) { sheetNames.add(lworkBook.getSheetName(i)); } } } catch (FileNotFoundException e) { // do nothing } catch (IOException e) { // do nothing } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { fis.close(); } return sheetNames; }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
@Transactional public String upload() { try {/*from w w w . j a va 2 s . c om*/ 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.egov.egf.web.actions.budget.BudgetLoadAction.java
License:Open Source License
@ValidationErrorPage("upload") @Action(value = "/budget/budgetLoad-upload") public String upload() { try {/*from w w w .j ava2 s. c o m*/ FileInputStream fsIP = new FileInputStream(budgetInXls); final POIFSFileSystem fs = new POIFSFileSystem(fsIP); final HSSFWorkbook wb = new HSSFWorkbook(fs); wb.getNumberOfSheets(); final HSSFSheet sheet = wb.getSheetAt(0); final HSSFRow reRow = sheet.getRow(RE_YEAR_ROW_INDEX); final HSSFRow beRow = sheet.getRow(BE_YEAR_ROW_INDEX); String reFinYearRange = getStrValue(reRow.getCell(1)); String beFinYearRange = getStrValue(beRow.getCell(1)); CFinancialYear reFYear = financialYearDAO.getFinancialYearByFinYearRange(reFinYearRange); CFinancialYear beFYear = financialYearDAO.getNextFinancialYearByDate(reFYear.getStartingDate()); if (!validateFinancialYears(reFYear, beFYear, beFinYearRange)) throw new ValidationException(Arrays .asList(new ValidationError(getText("be.year.is.not.immediate.next.fy.year.of.re.year"), getText("be.year.is.not.immediate.next.fy.year.of.re.year")))); timeStamp = new Timestamp((new Date()).getTime()).toString().replace(".", "_"); if (budgetInXlsFileName.contains("_budget_original_")) { budgetOriginalFileName = budgetInXlsFileName.split("_budget_original_")[0] + "_budget_original_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } else if (budgetInXlsFileName.contains("_budget_output_")) { budgetOriginalFileName = budgetInXlsFileName.split("_budget_output_")[0] + "_budget_original_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } else { if (budgetInXlsFileName.length() > 60) { throw new ValidationException(Arrays .asList(new ValidationError(getText("file.name.should.be.less.then.60.characters"), getText("file.name.should.be.less.then.60.characters")))); } else budgetOriginalFileName = budgetInXlsFileName.split("\\.")[0] + "_budget_original_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } final FileStoreMapper originalFileStore = fileStoreService.store(budgetInXls, budgetOriginalFileName, budgetInXlsContentType, FinancialConstants.MODULE_NAME_APPCONFIG, false); persistenceService.persist(originalFileStore); originalFileStoreId = originalFileStore.getFileStoreId(); List<BudgetUpload> budgetUploadList = loadToBudgetUpload(sheet); budgetUploadList = validateMasterData(budgetUploadList); budgetUploadList = validateDuplicateData(budgetUploadList); if (errorInMasterData) { fsIP.close(); prepareOutPutFileWithErrors(budgetUploadList); addActionMessage(getText("error.while.validating.masterdata")); return "result"; } budgetUploadList = removeEmptyRows(budgetUploadList); budgetUploadList = budgetDetailService.loadBudget(budgetUploadList, reFYear, beFYear); fsIP.close(); prepareOutPutFileWithFinalStatus(budgetUploadList); addActionMessage(getText("budget.load.sucessful")); } catch (final ValidationException e) { originalFiles = (List<FileStoreMapper>) persistenceService.getSession() .createQuery("from FileStoreMapper where fileName like '%budget_original%' order by id desc ") .setMaxResults(5).list(); outPutFiles = (List<FileStoreMapper>) persistenceService.getSession() .createQuery("from FileStoreMapper where fileName like '%budget_output%' order by id desc ") .setMaxResults(5).list(); throw new ValidationException(Arrays.asList( new ValidationError(e.getErrors().get(0).getMessage(), e.getErrors().get(0).getMessage()))); } catch (final Exception e) { originalFiles = (List<FileStoreMapper>) persistenceService.getSession() .createQuery("from FileStoreMapper where fileName like '%budget_original%' order by id desc ") .setMaxResults(5).list(); outPutFiles = (List<FileStoreMapper>) persistenceService.getSession() .createQuery("from FileStoreMapper where fileName like '%budget_output%' order by id desc ") .setMaxResults(5).list(); throw new ValidationException(Arrays.asList(new ValidationError(budgetUploadError, budgetUploadError))); } return "result"; }
From source file:org.egov.egf.web.actions.budget.BudgetLoadAction.java
License:Open Source License
private void prepareOutPutFileWithErrors(List<BudgetUpload> budgetUploadList) { FileInputStream fsIP;/*from ww w. jav a 2 s . c o m*/ try { fsIP = new FileInputStream(budgetInXls); Map<String, String> errorsMap = new HashMap<String, String>(); final POIFSFileSystem fs = new POIFSFileSystem(fsIP); final HSSFWorkbook wb = new HSSFWorkbook(fs); wb.getNumberOfSheets(); final HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(3); HSSFCell cell = row.createCell(7); cell.setCellValue("Error Reason"); for (BudgetUpload budget : budgetUploadList) errorsMap.put(budget.getFundCode() + "-" + budget.getFunctionCode() + "-" + budget.getDeptCode() + "-" + budget.getBudgetHead(), budget.getErrorReason()); for (int i = DATA_STARTING_ROW_INDEX; i <= sheet.getLastRowNum(); i++) { HSSFRow errorRow = sheet.getRow(i); HSSFCell errorCell = errorRow.createCell(7); errorCell.setCellValue(errorsMap.get((getStrValue(sheet.getRow(i).getCell(FUNDCODE_CELL_INDEX)) + "-" + getStrValue(sheet.getRow(i).getCell(FUNCTIONCODE_CELL_INDEX)) + "-" + getStrValue(sheet.getRow(i).getCell(DEPARTMENTCODE_CELL_INDEX)) + "-" + getStrValue(sheet.getRow(i).getCell(GLCODE_CELL_INDEX))))); } FileOutputStream output_file = new FileOutputStream(budgetInXls); wb.write(output_file); output_file.close(); if (budgetInXlsFileName.contains("_budget_original_")) { budgetOutPutFileName = budgetInXlsFileName.split("_budget_original_")[0] + "_budget_output_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } else if (budgetInXlsFileName.contains("_budget_output_")) { budgetOutPutFileName = budgetInXlsFileName.split("_budget_output_")[0] + "_budget_output_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } else { if (budgetInXlsFileName.length() > 60) { throw new ValidationException(Arrays .asList(new ValidationError(getText("file.name.should.be.less.then.60.characters"), getText("file.name.should.be.less.then.60.characters")))); } else budgetOutPutFileName = budgetInXlsFileName.split("\\.")[0] + "_budget_output_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } final FileStoreMapper outPutFileStore = fileStoreService.store(budgetInXls, budgetOutPutFileName, budgetInXlsContentType, FinancialConstants.MODULE_NAME_APPCONFIG); persistenceService.persist(outPutFileStore); outPutFileStoreId = outPutFileStore.getFileStoreId(); } catch (FileNotFoundException e) { throw new ValidationException(Arrays.asList(new ValidationError(e.getMessage(), e.getMessage()))); } catch (IOException e) { throw new ValidationException(Arrays.asList(new ValidationError(e.getMessage(), e.getMessage()))); } }
From source file:org.egov.egf.web.actions.budget.BudgetLoadAction.java
License:Open Source License
private void prepareOutPutFileWithFinalStatus(List<BudgetUpload> budgetUploadList) { FileInputStream fsIP;/*from ww w . j a va2 s . c o m*/ try { fsIP = new FileInputStream(budgetInXls); Map<String, String> errorsMap = new HashMap<String, String>(); final POIFSFileSystem fs = new POIFSFileSystem(fsIP); final HSSFWorkbook wb = new HSSFWorkbook(fs); wb.getNumberOfSheets(); final HSSFSheet sheet = wb.getSheetAt(0); Map<String, String> finalStatusMap = new HashMap<String, String>(); HSSFRow row = sheet.getRow(3); HSSFCell cell = row.createCell(7); cell.setCellValue("Status"); for (BudgetUpload budget : budgetUploadList) finalStatusMap.put(budget.getFundCode() + "-" + budget.getFunctionCode() + "-" + budget.getDeptCode() + "-" + budget.getBudgetHead(), budget.getFinalStatus()); for (int i = DATA_STARTING_ROW_INDEX; i <= sheet.getLastRowNum(); i++) { HSSFRow finalStatusRow = sheet.getRow(i); HSSFCell finalStatusCell = finalStatusRow.createCell(7); finalStatusCell .setCellValue(finalStatusMap.get((getStrValue(sheet.getRow(i).getCell(FUNDCODE_CELL_INDEX)) + "-" + getStrValue(sheet.getRow(i).getCell(FUNCTIONCODE_CELL_INDEX)) + "-" + getStrValue(sheet.getRow(i).getCell(DEPARTMENTCODE_CELL_INDEX)) + "-" + getStrValue(sheet.getRow(i).getCell(GLCODE_CELL_INDEX))))); } FileOutputStream output_file = new FileOutputStream(budgetInXls); wb.write(output_file); output_file.close(); if (budgetInXlsFileName.contains("_budget_original_")) { budgetOutPutFileName = budgetInXlsFileName.split("_budget_original_")[0] + "_budget_output_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } else if (budgetInXlsFileName.contains("_budget_output_")) { budgetOutPutFileName = budgetInXlsFileName.split("_budget_output_")[0] + "_budget_output_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } else { if (budgetInXlsFileName.length() > 60) { throw new ValidationException(Arrays .asList(new ValidationError(getText("file.name.should.be.less.then.60.characters"), getText("file.name.should.be.less.then.60.characters")))); } else budgetOutPutFileName = budgetInXlsFileName.split("\\.")[0] + "_budget_output_" + timeStamp + "." + budgetInXlsFileName.split("\\.")[1]; } final FileStoreMapper outPutFileStore = fileStoreService.store(budgetInXls, budgetOutPutFileName, budgetInXlsContentType, FinancialConstants.MODULE_NAME_APPCONFIG); persistenceService.persist(outPutFileStore); outPutFileStoreId = outPutFileStore.getFileStoreId(); } catch (FileNotFoundException e) { throw new ValidationException(Arrays.asList(new ValidationError(e.getMessage(), e.getMessage()))); } catch (IOException e) { throw new ValidationException(Arrays.asList(new ValidationError(e.getMessage(), e.getMessage()))); } }
From source file:org.encuestame.business.search.IndexerFile.java
License:Apache License
/** * Extract spreadsheets content.//from w ww .jav a 2s . c om * @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(); }