List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getFirstRowNum
@Override public int getFirstRowNum()
From source file:org.gaixie.micrite.car.service.impl.DealWithCar.java
@Override public int doJob(File src, Map<String, String> res) throws Exception { getPartner1();//from w w w. ja va2s . c o m getPartner2(); getPartner3(); getPartner4(); getPartner5(); getPartner6(); getPartner7(); // TODO Auto-generated method stub if (log.isInfoEnabled()) { if (src == null) log.info("upload file is null."); } HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(src)); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(sheet.getFirstRowNum()); int colSize = row.getLastCellNum(); int rowSize = sheet.getLastRowNum(); if (log.isInfoEnabled()) log.info("this is colSize=" + rowSize); try { if (colSize == COLS_1_2) {// for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); save1_2(row); } } else if (colSize == COLS_3) {// paiSeTmp = DictionaryUtil.getDictionaryWhenNotGiveLast("", paiSe); for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); save3(row); } } else if (colSize > COLS_3) {// if (sheet.getRow(sheet.getFirstRowNum()) != null && sheet.getRow(sheet.getFirstRowNum()).getCell(PAISE_COL_1_2) != null && sheet.getRow(sheet.getFirstRowNum()).getCell(PAISE_COL_1_2).getStringCellValue() != null && PAISE_TITLE.equals(sheet.getRow(sheet.getFirstRowNum()).getCell(PAISE_COL_1_2) .getStringCellValue().trim())) { for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); save1_2(row); } } else { for (int i = sheet.getFirstRowNum() + 1; i <= rowSize; i++) { row = sheet.getRow(i); if (row.getCell(PAISE_COL_3) != null && row.getCell(PAISE_COL_3).getStringCellValue() != null) { paiSeTmp = DictionaryUtil.getDictionaryWhenNotGiveLast( row.getCell(PAISE_COL_3).getStringCellValue(), paiSe); } else { paiSeTmp = DictionaryUtil.getDictionaryWhenNotGiveLast("", paiSe); } save3(row); } } } else { log.info("does not deal with colum size=" + colSize); } } catch (Exception e) { // TODO: handle exception e.printStackTrace(); log.info(e); } return IDealWith.OK; }
From source file:org.gaixie.micrite.enterprise.service.impl.DealWithEnterprise.java
@Override public int doJob(File src, Map<String, String> res) throws Exception { List<Dictionary> qualifications = dictionaryService.findALLDictionary(Enterprise.QUALIFICATION_TYPE); List<Dictionary> kinds = dictionaryService.findALLDictionary(Enterprise.KIND_TYPE); List<Dictionary> workTypes = dictionaryService.findALLDictionary(Enterprise.WORKTYPE_TYPE); // List<Dictionary> ranges=dictionaryService.findALLDictionary(11); // TODO Auto-generated method stub HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(src)); Enterprise enterprise = new Enterprise(); HSSFSheet sheet = wb.getSheetAt(0); String temp = null;// w w w . j av a2s . c o m for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); HSSFCell cell = row.getCell(4); try { temp = cell.getStringCellValue(); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } if (enterpriseService.existEnterprise(null, temp)) continue; cell = row.getCell(1); enterprise.setUnitName(cell.getStringCellValue()); cell = row.getCell(2); enterprise.setLegalPerson(cell.getStringCellValue()); cell = row.getCell(3); enterprise.setTelephone1(cell.getStringCellValue()); cell = row.getCell(4); enterprise.setLicense(cell.getStringCellValue()); cell = row.getCell(5); enterprise.setQualification(DictionaryUtil.getDictionary(cell.getStringCellValue(), qualifications)); cell = row.getCell(6); enterprise.setHandleMan(cell.getStringCellValue()); cell = row.getCell(7); enterprise.setTelephone2(cell.getStringCellValue()); cell = row.getCell(8); enterprise.setTelephone3(cell.getStringCellValue()); cell = row.getCell(9); enterprise.setCommission(cell.getStringCellValue()); cell = row.getCell(10); enterprise.setTelephone4(cell.getStringCellValue()); cell = row.getCell(11); enterprise.setKind(DictionaryUtil.getDictionary(cell.getStringCellValue(), kinds)); cell = row.getCell(12); if (cell.getStringCellValue() != null) { try { enterprise.setLicenseDate(df.parse(cell.getStringCellValue())); } catch (Exception e) { } } cell = row.getCell(13); if (cell.getStringCellValue() != null) { try { enterprise.setDateBegin(df.parse(cell.getStringCellValue()));// HH:mm:ss.S } catch (Exception e) { } } cell = row.getCell(14); if (cell.getStringCellValue() != null) { try { enterprise.setDateEnd(df.parse(cell.getStringCellValue())); } catch (Exception e) { } } cell = row.getCell(15); enterprise.setAddress(cell.getStringCellValue()); cell = row.getCell(16); if (cell.getStringCellValue() != null) { try { enterprise.setEditDate(df.parse(cell.getStringCellValue())); } catch (Exception e) { } } cell = row.getCell(17); enterprise.setWorkArea(cell.getStringCellValue()); cell = row.getCell(18); enterprise.setWorkRemark(cell.getStringCellValue()); cell = row.getCell(19); enterprise.setWorkType(DictionaryUtil.getDictionary(cell.getStringCellValue(), workTypes)); //?cell20 ?? //--------------------------------- //? enterprise.setStation(DictionaryUtil.getDefaultDictionary()); enterprise.setStatus(IEnterpriseService.STATUS_NORMAL); enterpriseService.add(enterprise); } return IDealWith.OK; }
From source file:org.lamsfoundation.lams.admin.service.ImportService.java
License:Open Source License
public boolean isUserSpreadsheet(FormFile fileItem) throws IOException { HSSFSheet sheet = getSheet(fileItem); HSSFRow row = sheet.getRow(sheet.getFirstRowNum()); String string = parseStringCell(row.getCell(PASSWORD)); return (StringUtils.equals(string, "* password")) ? true : false; }
From source file:org.lamsfoundation.lams.admin.service.ImportService.java
License:Open Source License
public boolean isRolesSpreadsheet(FormFile fileItem) throws IOException { HSSFSheet sheet = getSheet(fileItem); HSSFRow row = sheet.getRow(sheet.getFirstRowNum()); String string = parseStringCell(row.getCell(ORGANISATION)); return (StringUtils.equals(string, "* organisation")) ? true : false; }
From source file:org.lamsfoundation.lams.admin.service.ImportService.java
License:Open Source License
public List parseGroupSpreadsheet(FormFile fileItem) throws IOException { results = new ArrayList<ArrayList>(); parentOrg = service.getRootOrganisation(); HSSFSheet sheet = getSheet(fileItem); int startRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); log.debug("Parsing spreadsheet rows " + startRow + " through " + endRow); HSSFRow row;//w w w .ja va2 s .co m Organisation org = null; int successful = 0; for (int i = startRow + 1; i < endRow + 1; i++) { emptyRow = true; hasError = false; rowResult = new ArrayList<String>(); row = sheet.getRow(i); if (row != null) { org = parseGroup(row, i); } // an empty row signifies a new group if (emptyRow) { log.debug("Row " + i + " is empty."); parentOrg = service.getRootOrganisation(); continue; } if (hasError) { log.debug("Row " + i + " has an error which has been sent to the browser."); results.add(rowResult); continue; } else { org = service.saveOrganisation(org, getCurrentUserId()); successful++; rowResult.add(org.getOrganisationId().toString()); rowResult.add(org.getName()); rowResult.add(org.getParentOrganisation().getOrganisationId().toString()); rowResult.add(org.getOrganisationType().getOrganisationTypeId().toString()); writeOrgAuditLog(org); // if we just added a group, then the rows under it become it's subgroups if (parentOrg.getOrganisationType().getOrganisationTypeId().equals(OrganisationType.ROOT_TYPE)) { parentOrg = org; } results.add(rowResult); } } log.debug("Found " + results.size() + " orgs in spreadsheet."); writeSuccessAuditLog(successful, null, "audit.successful.organisation.import"); return results; }
From source file:org.lamsfoundation.lams.admin.service.ImportService.java
License:Open Source License
public int getNumRows(FormFile fileItem) throws IOException { HSSFSheet sheet = getSheet(fileItem); int startRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); return endRow - startRow; }
From source file:org.lamsfoundation.lams.admin.service.ImportService.java
License:Open Source License
public List parseUserSpreadsheet(FormFile fileItem, String sessionId) throws IOException { results = new ArrayList<ArrayList>(); HSSFSheet sheet = getSheet(fileItem); int startRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); setupImportStatus(sessionId, endRow - startRow); UserDTO userDTO = (UserDTO) SessionManager.getSession(sessionId).getAttribute(AttributeNames.USER); log.debug("Parsing spreadsheet rows " + startRow + " through " + endRow); HSSFRow row;/* w w w.ja va 2s . c o m*/ User user = null; int successful = 0; for (int i = startRow + 1; i < endRow + 1; i++) { emptyRow = true; hasError = false; rowResult = new ArrayList<String>(); row = sheet.getRow(i); user = parseUser(row, i); if (emptyRow) { log.debug("Row " + i + " is empty."); break; } if (hasError) { log.debug("Row " + i + " has an error which has been sent to the browser."); results.add(rowResult); writeErrorsAuditLog(i + 1, rowResult, userDTO); updateImportStatus(sessionId, results.size()); continue; } else { try { service.save(user); successful++; writeAuditLog(user, userDTO); log.debug("Row " + i + " saved user: " + user.getLogin()); } catch (Exception e) { log.debug(e); rowResult.add(messageService.getMessage("error.fail.add")); } if (rowResult.size() > 0) { if (log.isDebugEnabled()) log.debug("Row " + i + " has " + rowResult.size() + " messages."); writeErrorsAuditLog(i + 1, rowResult, userDTO); } results.add(rowResult); updateImportStatus(sessionId, results.size()); } } log.debug("Found " + results.size() + " users in spreadsheet."); writeSuccessAuditLog(successful, userDTO, "audit.successful.user.import"); return results; }
From source file:org.lamsfoundation.lams.admin.service.ImportService.java
License:Open Source License
public List parseRolesSpreadsheet(FormFile fileItem, String sessionId) throws IOException { results = new ArrayList<ArrayList>(); HSSFSheet sheet = getSheet(fileItem); int startRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); log.debug("Parsing spreadsheet rows " + startRow + " through " + endRow); setupImportStatus(sessionId, endRow - startRow); UserDTO userDTO = (UserDTO) SessionManager.getSession(sessionId).getAttribute(AttributeNames.USER); HSSFRow row;/* w w w . j av a 2 s. c o m*/ List<String> roles; int successful = 0; for (int i = startRow + 1; i < endRow + 1; i++) { emptyRow = true; hasError = false; rowResult = new ArrayList<String>(); row = sheet.getRow(i); String login = parseStringCell(row.getCell(LOGIN)); String orgId = parseStringCell(row.getCell(ORGANISATION)); roles = parseRolesCell(row.getCell(ROLES)); if (emptyRow) { log.debug("Row " + i + " is empty."); break; } if (hasError) { log.debug("Row " + i + " has an error which has been sent to the browser."); results.add(rowResult); writeErrorsAuditLog(i + 1, rowResult, userDTO); updateImportStatus(sessionId, results.size()); continue; } else { try { saveUserRoles(isSysadmin(sessionId), login, orgId, roles, row); successful++; } catch (Exception e) { log.error("Unable to assign roles to user: " + login, e); rowResult.add(messageService.getMessage("error.fail.add")); } if (rowResult.size() > 0) { if (log.isDebugEnabled()) log.debug("Row " + i + " has " + rowResult.size() + " messages."); writeErrorsAuditLog(i + 1, rowResult, userDTO); } results.add(rowResult); updateImportStatus(sessionId, results.size()); } } log.debug("Found " + results.size() + " users in spreadsheet."); writeSuccessAuditLog(successful, userDTO, "audit.successful.role.import"); return results; }
From source file:org.modeshape.sequencer.msoffice.excel.ExcelMetadataReader.java
License:Apache License
public static ExcelMetadata instance(InputStream stream) throws IOException { ExcelMetadata metadata = new ExcelMetadata(); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(stream)); List<ExcelSheetMetadata> sheets = new ArrayList<ExcelSheetMetadata>(); for (int sheetInd = 0; sheetInd < wb.getNumberOfSheets(); sheetInd++) { ExcelSheetMetadata meta = new ExcelSheetMetadata(); meta.setName(wb.getSheetName(sheetInd)); sheets.add(meta);/*from w w w . j a v a 2 s . co m*/ HSSFSheet worksheet = wb.getSheetAt(sheetInd); int lastRowNum = worksheet.getLastRowNum(); StringBuilder buff = new StringBuilder(); for (int rowNum = worksheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { HSSFRow row = worksheet.getRow(rowNum); // Empty rows are returned as null if (row == null) { continue; } int lastCellNum = row.getLastCellNum(); for (int cellNum = row.getFirstCellNum(); cellNum < lastCellNum; cellNum++) { HSSFCell cell = row.getCell(cellNum); // Undefined cells are returned as null if (cell == null) { continue; } /* * Builds a string of body content from all string, numeric, * and formula values in the body of each worksheet. * * This code currently duplicates the POI 3.1 ExcelExtractor behavior of * combining the body text from all worksheets into a single string. */ switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: buff.append(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_NUMERIC: buff.append(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: buff.append(cell.getCellFormula()); break; } HSSFComment comment = cell.getCellComment(); if (comment != null) { // Filter out row delimiter characters from comment String commentText = comment.getString().getString().replace(ROW_DELIMITER_CHAR, ' '); buff.append(" ["); buff.append(commentText); buff.append(" by "); buff.append(comment.getAuthor()); buff.append(']'); } if (cellNum < lastCellNum - 1) { buff.append(CELL_DELIMITER_CHAR); } else { buff.append(ROW_DELIMITER_CHAR); } } } meta.setText(buff.toString()); } metadata.setSheets(sheets); metadata.setMetadata(wb.getSummaryInformation()); return metadata; }
From source file:org.olat.search.service.document.file.ExcelDocument.java
License:Apache License
@Override protected String readContent(final VFSLeaf leaf) throws IOException, DocumentException { BufferedInputStream bis = null; int cellNullCounter = 0; int rowNullCounter = 0; int sheetNullCounter = 0; try {/*from w ww.j a v a 2s . co m*/ bis = new BufferedInputStream(leaf.getInputStream()); final StringBuilder content = new StringBuilder(bis.available()); final POIFSFileSystem fs = new POIFSFileSystem(bis); final HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int sheetNumber = 0; sheetNumber < workbook.getNumberOfSheets(); sheetNumber++) { final HSSFSheet sheet = workbook.getSheetAt(sheetNumber); if (sheet != null) { for (int rowNumber = sheet.getFirstRowNum(); rowNumber <= sheet.getLastRowNum(); rowNumber++) { final HSSFRow row = sheet.getRow(rowNumber); if (row != null) { for (int cellNumber = row.getFirstCellNum(); cellNumber <= row .getLastCellNum(); cellNumber++) { final HSSFCell cell = row.getCell(cellNumber); if (cell != null) { // if (cell.getCellStyle().equals(HSSFCell.CELL_TYPE_NUMERIC)) if (cell.getCellType() == Cell.CELL_TYPE_STRING) { content.append(cell.getStringCellValue()).append(' '); } } else { // throw new DocumentException(); cellNullCounter++; } } } else { rowNullCounter++; } } } else { sheetNullCounter++; } } if (log.isDebug()) { if ((cellNullCounter > 0) || (rowNullCounter > 0) || (sheetNullCounter > 0)) { log.debug("Read Excel content cell=null #:" + cellNullCounter + ", row=null #:" + rowNullCounter + ", sheet=null #:" + sheetNullCounter); } } return content.toString(); } catch (final Exception ex) { throw new DocumentException("Can not read XLS Content. File=" + leaf.getName()); } finally { if (bis != null) { bis.close(); } } }