List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt
Sheet getSheetAt(int index);
From source file:com.simopuve.rest.SimopuveRESTServices.java
@Path("/helloworld") @GET//from w w w . j a v a2 s . co m @Produces("text/plain") public String getClichedMessage() { String filePath = new StringBuilder(System.getProperty("jboss.server.data.dir")).append("/PDV/pdv2.xlsx") .toString(); PDVSurvey survey = null; try { survey = getPDVSurveyFromFile(filePath, false); survey.getHeader(); List<PDVSurvey> surveyList = new ArrayList<>(); surveyList.add(survey); filePath = new StringBuilder(System.getProperty("jboss.server.data.dir")).append("/PDV/testFlow.xlsx") .toString(); Workbook flowWorkbook = POIHelper.getWorkbookFromLocalReource("plantilla-base-flujo.xlsx"); FillFlowBaseSheet(surveyList, flowWorkbook.getSheetAt(0)); FillDetailBaseSheet(surveyList, flowWorkbook.getSheetAt(1)); POIHelper.writeWorkbookInPath(flowWorkbook, filePath); } catch (IOException ex) { Logger.getLogger(SimopuveRESTServices.class.getName()).log(Level.SEVERE, null, ex); } return "ewwe: "; }
From source file:com.smanempat.view.ExcelReading.java
/** * @param args the command line arguments */// w ww .j a va 2 s.com public static void main(String[] args) { InputStream inp = null; try { inp = new FileInputStream("C:\\Users\\Zakaria\\Documents\\Test\\2009_15112015_1615.xlsx\\"); Workbook wb = WorkbookFactory.create(inp); for (int i = 0; i < wb.getNumberOfSheets(); i++) { System.out.println(wb.getSheetAt(i).getSheetName()); echoAsCSV(wb.getSheetAt(i)); } } catch (InvalidFormatException ex) { Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex); } finally { try { inp.close(); } catch (IOException ex) { Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:com.smegi.bonusi.model.Excel.java
public void getExcelBonuses(int month) { Calendar calendar = Calendar.getInstance(); calendar.set(2016, month - 1, Calendar.DAY_OF_MONTH); try {//from ww w .j av a 2 s . c o m FileInputStream inputStream = new FileInputStream(new File(path)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(month - 1); for (int i = 2; i < sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); String username = ""; int bonuses = 0; for (int j = 1; j < row.getLastCellNum(); j++) { //getting username Cell cell = row.getCell(j); if (j == 1) { username = cell.toString(); continue; } if (cell.toString() != "") { bonuses++; } } // updating user in userslist for (User user : users) { if (user.getName().equalsIgnoreCase(username)) { // System.out.println("Found username in base: " + username); // System.out.println(" - adding bonuses: " + bonuses); StringBuilder sb = new StringBuilder(); sb.append(calendar.get(Calendar.YEAR)).append("-").append((calendar.get(Calendar.MONTH))); user.addExcelBonusi(sb.toString(), bonuses); break; } } } } catch (FileNotFoundException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.snapdeal.scm.web.core.utils.ExcelReader.java
License:Open Source License
public static FileValuesDTO readExcelFile(MultipartFile file) throws IOException, FileUploadException { FileValuesDTO fileValue = new FileValuesDTO(); InputStream inputStream = file.getInputStream(); Workbook workbook = getWorkbook(inputStream, file.getOriginalFilename()); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); if (!iterator.hasNext()) { throw new FileUploadException("Empty File Uploded"); }//ww w . java 2s. com List<String> headers = new ArrayList<String>(); iterator.next().forEach(cell -> headers.add(cell.getStringCellValue())); fileValue.setHeader(headers); List<Map<String, String>> headerDataMap = new ArrayList<Map<String, String>>(); int size = fileValue.getHeader().size(); while (iterator.hasNext()) { Map<String, String> dataMap = new HashMap<String, String>(); Row row = iterator.next(); if (row.getPhysicalNumberOfCells() == 0) { continue; } for (int i = 0; i < size; i++) { switch (row.getCell(i).getCellType()) { case Cell.CELL_TYPE_NUMERIC: dataMap.put(fileValue.getHeader().get(i), new Double(row.getCell(i).getNumericCellValue()).toString()); break; case Cell.CELL_TYPE_STRING: dataMap.put(fileValue.getHeader().get(i), row.getCell(i).getStringCellValue()); break; } } headerDataMap.add(dataMap); } if (CollectionUtils.isEmpty(headerDataMap)) { throw new FileUploadException("No date in Uploded File"); } fileValue.setHeaderDataMap(headerDataMap); workbook.close(); inputStream.close(); return fileValue; }
From source file:com.softtek.mdm.web.institution.UserController.java
/** * /* www .j a v a2 s . co m*/ * * @param request * @param response * @return * @throws Exception */ @Log(operateType = "logs.usercontroller.member.type.import", operateContent = "logs.usercontroller.content.member.import") @RequestMapping(value = "/importusers", method = RequestMethod.POST) @ResponseBody public Map<String, Object> importvirmember(MultipartFile files, HttpServletRequest request, HttpServletResponse response, HttpSession session) throws Exception { Map<String, Object> messages = new HashMap<String, Object>(); // xlsxlsx Integer filetype = 0; if (files.getOriginalFilename().endsWith("xls")) { filetype = 03; } if (files.getOriginalFilename().endsWith("xlsx")) { filetype = 07; } // ?id OrganizationModel organization = (OrganizationModel) session .getAttribute(SessionStatus.SOFTTEK_ORGANIZATION.toString()); Integer orgid = organization.getId(); // ??? ManagerModel managerModel = (ManagerModel) session.getAttribute(SessionStatus.SOFTTEK_MANAGER.toString()); List<Integer> idlist = new ArrayList<Integer>(); // createById Integer managerId = managerModel.getId(); @SuppressWarnings("unchecked") List<StructureModel> list = (List<StructureModel>) session .getAttribute(SessionStatus.SOFTTEK_DEPARTMENT.toString()); NodeModel nodes = treeManager.bulidTreeContainUser(list, organization.getId(), managerModel.getUser()); // ?? if (managerModel.getUser() != null) { @SuppressWarnings("unchecked") List<StructureModel> list1 = (List<StructureModel>) session .getAttribute(SessionStatus.SOFTTEK_DEPARTMENT.toString()); for (StructureModel s : list1) { idlist.add(s.getId()); } } else { idlist = null; } InputStream ins = files.getInputStream(); Workbook wb = null; wb = WorkbookFactory.create(ins); ins.close(); Sheet sheet = null; // 3.Excel if (filetype == 03) { sheet = (HSSFSheet) wb.getSheetAt(0); } if (filetype == 07) { sheet = (XSSFSheet) wb.getSheetAt(0); } // int trLength = sheet.getLastRowNum(); Row rowtest = sheet.getRow(0); Cell celltest1 = rowtest.getCell(0); Cell celltest2 = rowtest.getCell(1); Cell celltest3 = rowtest.getCell(2); if (celltest1 != null && celltest2 != null && celltest3 != null) { celltest1.setCellType(Cell.CELL_TYPE_STRING); celltest2.setCellType(Cell.CELL_TYPE_STRING); celltest3.setCellType(Cell.CELL_TYPE_STRING); if (trLength > 0) { if (celltest1.getStringCellValue() .equals(messageSource.getMessage("web.institution.usercontroller.export.excel.label1", null, LocaleContextHolder.getLocale())) && celltest2.getStringCellValue() .equals(messageSource.getMessage( "web.institution.usercontroller.export.excel.label2", null, LocaleContextHolder.getLocale())) && celltest3.getStringCellValue() .equals(messageSource.getMessage( "web.institution.usercontroller.export.excel.label3", null, LocaleContextHolder.getLocale()))) { List<ExcelInsertUserModel> excelList = new ArrayList<ExcelInsertUserModel>(); List<String> usertlist = (List<String>) userService.findAllMember(orgid); String erromessages = ""; int rownumber = 0; for (int i = 1; i <= trLength; i++) { // Excel Row row = sheet.getRow(i); ExcelInsertUserModel excelInsertUserModel = new ExcelInsertUserModel(); for (int j = 0; j <= 10; j++) { // Excel? if (row != null) { Cell newcell = row.getCell(j); String cell = ""; if (newcell != null) { newcell.setCellType(Cell.CELL_TYPE_STRING); if (StringUtils.isNotBlank(StringUtils.trim(newcell.getStringCellValue()))) { // cell = // StringUtils.trim(newcell.getStringCellValue()); cell = newcell.getStringCellValue().replaceAll(" ", ""); } } if (j == 0) { // ??? if (StringUtils.isNotBlank(cell)) { String groupname[] = cell.split("/"); if (null != groupname) { /* */ int deep = 0; List<NodeModel> nodeList = null; StructureModel structure = null; NodeModel temp = nodes; do { if (temp.getTags().getParent() == null) { nodeList = temp.getNodes(); for (NodeModel n : nodeList) { if (n.getTags().getName().equals(groupname[deep])) { deep++; temp = n; break; } } if (deep == 0) { break; } } else { int deep1 = deep; for (NodeModel n : nodeList) { if (n.getTags().getName().equals(groupname[deep])) { deep++; temp = n; break; } } if (deep1 == deep) { break; } } if (deep == groupname.length) { structure = temp.getTags(); break; } } while ((nodeList = temp.getNodes()) != null); if (structure == null) { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.nullgroup", args, LocaleContextHolder.getLocale()); } else { if (idlist != null) { int idlistsize1 = idlist.size(); idlist.remove(structure.getId()); int idlistsize2 = idlist.size(); if (idlistsize1 != idlistsize2) { excelInsertUserModel.setGroup_id(structure.getId()); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.group2", args, LocaleContextHolder.getLocale()); } idlist.add(structure.getId()); } else { excelInsertUserModel.setGroup_id(structure.getId()); // excelList.add(excelInsertUserModel); } } } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.group3", args, LocaleContextHolder.getLocale()); } } // ?? if (j == 1) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 20) { boolean reuser = usertlist.remove(cell); if (reuser == true) { excelInsertUserModel.setUser_name(cell); rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.username", args, LocaleContextHolder.getLocale()); usertlist.add(cell); } else { excelInsertUserModel.setUser_name(cell); } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.username1", args, LocaleContextHolder.getLocale()); } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.username2", args, LocaleContextHolder.getLocale()); } } // ?? if (j == 2) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 20) { excelInsertUserModel.setReal_name(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.realname", args, LocaleContextHolder.getLocale()); } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.realname1", args, LocaleContextHolder.getLocale()); } } // if (j == 3) { if (StringUtils.isNotBlank(cell)) { String regex = "^\\s*\\w+(?:\\.{0,1}[\\w-]+)*@[a-zA-Z0-9]+(?:[-.][a-zA-Z0-9]+)*\\.[a-zA-Z]+\\s*$"; if (cell.matches(regex) == true) { excelInsertUserModel.setEmail(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.email", args, LocaleContextHolder.getLocale()); } } } // if (j == 4) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setMark(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.mark", args, LocaleContextHolder.getLocale()); } } } // ?? if (j == 5) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setPhone((cell)); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.phone", args, LocaleContextHolder.getLocale()); } } } // if (j == 6) { if (StringUtils.isNotBlank(cell)) { if (cell.equals( messageSource.getMessage("web.institution.usercontroller.sex.woman", null, LocaleContextHolder.getLocale()))) { excelInsertUserModel.setGender(0); } else { excelInsertUserModel.setGender(1); } } else { excelInsertUserModel.setGender(1); } } // ?? if (j == 7) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setSign(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.sign", args, LocaleContextHolder.getLocale()); } } } // ?? if (j == 8) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setAddress(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.address", args, LocaleContextHolder.getLocale()); } } } // ? if (j == 9) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setOffice_phone(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.office_phone", args, LocaleContextHolder.getLocale()); } } } // ?? if (j == 10) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setPosition(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.position", args, LocaleContextHolder.getLocale()); } } } } } excelList.add(excelInsertUserModel); } // ?? ???? List<String> yqs = new ArrayList<String>(); Set<String> testreusername = new HashSet<String>(yqs); for (int k = 0; k < excelList.size(); k++) { if (excelList.get(k).getUser_name() != null) { testreusername.add(excelList.get(k).getUser_name()); yqs.add(excelList.get(k).getUser_name()); } } if (testreusername.size() < yqs.size()) { erromessages = messageSource.getMessage( "web.institution.usercontroller.excel.erro.erromessages", null, LocaleContextHolder.getLocale()); messages.put("messages", erromessages); } else { // ? if (erromessages.length() > 0) { erromessages.substring(0, erromessages.length() - 1); messages.put("messages", erromessages); } else { Md5PasswordEncoder md5PasswordEncoder = new Md5PasswordEncoder(); String password = md5PasswordEncoder.encodePassword("123456", null); if (excelList.size() > 0) { for (int q = 0; q < excelList.size(); q++) { Integer policyId = structureService .queryPolicyIdById(excelList.get(q).getGroup_id()); excelList.get(q).setPolicy_id(policyId); excelList.get(q).setOrgid(orgid); excelList.get(q).setPassword(password); excelList.get(q).setCreate_by(managerId); } userService.importUsers(excelList); messages.put("success", "success"); } } } } else { String nullmessages = messageSource.getMessage( "web.institution.usercontroller.excel.erromodel.erromessages", null, LocaleContextHolder.getLocale()); messages.put("messages", nullmessages); } } else { String nullmessages = messageSource.getMessage( "web.institution.usercontroller.excel.erro.nullmessages", null, LocaleContextHolder.getLocale()); messages.put("messages", nullmessages); } } else { String nullmessages = messageSource.getMessage( "web.institution.usercontroller.excel.erromodel.erromessages", null, LocaleContextHolder.getLocale()); messages.put("messages", nullmessages); } return messages; }
From source file:com.sonicle.webtop.core.io.input.MemoryExcelFileReader.java
License:Open Source License
protected Sheet getSheet(Workbook wb) { if (StringUtils.isBlank(sheet)) { return wb.getSheetAt(0); } else {/*from www . ja v a 2 s . com*/ return wb.getSheet(sheet); } }
From source file:com.ssy.havefun.f3d.F3DDaoImpl.java
@Override public List<F3DEntity> doImport() throws IOException { List<F3DEntity> list = new ArrayList<>(); Workbook wb; try {// w w w .j av a2 s .com wb = WorkbookFactory.create(new File("C:/Users/jsun/Downloads/2015.xls")); Sheet sheet = wb.getSheetAt(2); for (int i = 0; i < sheet.getLastRowNum() + 1; i++) { Row row = sheet.getRow(i); F3DEntity f3d = new F3DEntity(); for (int j = 0; j < 10; j++) { Cell cell = row.getCell(j); String value = getCellValue(cell); switch (j) { case 0: f3d.setsId(Integer.parseInt(value)); break; case 1: f3d.setDate(value.replaceAll("-", "").substring(0, 8)); break; case 2: char[] values = value.toCharArray(); f3d.setFirst(Integer.parseInt(String.valueOf(values[0]))); f3d.setSecond(Integer.parseInt(String.valueOf(values[1]))); f3d.setThird(Integer.parseInt(String.valueOf(values[2]))); break; case 3: break; case 4: f3d.setSale(Integer.parseInt(value)); break; case 5: f3d.setDirect(Integer.parseInt(value)); break; case 6: break; case 7: f3d.setThirdCombination(Integer.parseInt(value)); break; case 8: break; case 9: f3d.setSixCombination(Integer.parseInt(value)); break; } } System.out.println(f3d.toString()); list.add(f3d); } } catch (InvalidFormatException ex) { Logger.getLogger(F3DDaoImpl.class.getName()).log(Level.SEVERE, null, ex); } return list; }
From source file:com.stam.excellatin.ExcelLatin.java
public static void main(String[] args) { List<String> options = new ArrayList<>(); int startIndex = 0; for (String arg : args) { if (validOptions.contains(arg)) { options.add(arg);/* w ww .ja v a 2 s .co m*/ startIndex++; } } if (args[0].equals("-h") || args.length < 3) { System.out.println("usage: ExcelLatin [options] filenameIn filenameOut columnNames..."); System.out.println("options:"); System.out.println("\t-L\tto Latin (default)"); System.out.println("\t-G\tto Greek"); System.out.println("\t-d\tdon't deaccent"); System.out.println("\t-h\thelp"); } else { boolean greekToLatin = false; boolean latinToGreek = false; Transliterator transliterator = null; if ((!options.contains("-L") && !options.contains("-G")) || options.contains("-L")) { transliterator = Transliterator.getInstance("Greek-Latin/UNGEGN"); System.out.println("\nTransliterating Greek to Latin"); greekToLatin = true; } else if (options.contains("-G")) { transliterator = Transliterator.getInstance("Latin-Greek/UNGEGN"); System.out.println("\nTransliterating Latin to Greek"); latinToGreek = true; } if (transliterator == null) { System.out.println("Not a valid option for the transliteration language"); return; } boolean deAccent = true; if (options.contains("-d")) { deAccent = false; System.out.println("Will not deaccent"); } String fileNameIn = args[startIndex]; String fileNameOut = args[startIndex + 1]; List<String> columnNames = new ArrayList<>(); System.out.println("\nColumns to transliterate\n---------------------------"); for (int i = startIndex + 2; i < args.length; i++) { columnNames.add(args[i]); System.out.println(args[i]); } System.out.println("\n"); try { File file = new File(fileNameIn); if (!file.exists()) { System.out.println("The file " + fileNameIn + " was not found"); return; } Map<String, String> mapTransformations = new HashMap<>(); Scanner sc = new Scanner(new FileReader("map.txt")); while (sc.hasNextLine()) { String greekEntry = sc.next(); String latinEntry = sc.next(); if (greekToLatin) { mapTransformations.put(greekEntry, latinEntry); } else if (latinToGreek) { mapTransformations.put(latinEntry, greekEntry); } } DataFormatter formatter = new DataFormatter(); Workbook wb = WorkbookFactory.create(file); Workbook newWb = null; if (wb instanceof HSSFWorkbook) { newWb = new HSSFWorkbook(); } else if (wb instanceof XSSFWorkbook) { newWb = new XSSFWorkbook(); } FileOutputStream fileOut = new FileOutputStream(fileNameOut); if (newWb != null) { Sheet sheetOut = newWb.createSheet(); Sheet sheet = wb.getSheetAt(0); List<Integer> idxs = new ArrayList<>(); Row row = sheet.getRow(0); for (Cell cell : row) { String cellVal = formatter.formatCellValue(cell); if (cellVal == null || cellVal.trim().equals("")) { break; } if (columnNames.contains(cell.getStringCellValue())) { idxs.add(cell.getColumnIndex()); } } for (Row rowIn : sheet) { Row rowOut = sheetOut.createRow(rowIn.getRowNum()); if (rowIn.getRowNum() == 0) { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cell.getStringCellValue()); } } else { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); String cellVal = formatter.formatCellValue(cell); String cellNewVal = cellVal; if (idxs.contains(cell.getColumnIndex()) && cellVal != null) { if (mapTransformations.containsKey(cellVal)) { cellNewVal = mapTransformations.get(cellVal); } else { if (deAccent) { cellNewVal = deAccent(transliterator.transform(cellVal)); } else { cellNewVal = transliterator.transform(cellVal); } } } Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cellNewVal); } } } System.out.println("Finished!"); newWb.write(fileOut); fileOut.close(); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(ExcelLatin.class.toString()).log(Level.SEVERE, null, ex); } } }
From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java
License:Apache License
public WorkbookParser(WorkbookParserSettings settings, Context context, Workbook workbook, String offsetId) throws DataParserException { this.settings = requireNonNull(settings); this.context = requireNonNull(context); this.workbook = requireNonNull(workbook); this.rowIterator = iterate(this.workbook); this.offset = requireNonNull(offsetId); this.evaluator = workbook.getCreationHelper().createFormulaEvaluator(); this.currentSheet = null; // default to blank. Used to figure out when sheet changes and get new field names from header row if (!rowIterator.hasNext()) { throw new DataParserException(Errors.EXCEL_PARSER_04); }/*from www . j a va2 s. c om*/ headers = new HashMap<>(); // If Headers are expected, go through and get them from each sheet if (settings.getHeader() == ExcelHeader.WITH_HEADER) { Sheet sheet; String sheetName; Row hdrRow; for (int s = 0; s < workbook.getNumberOfSheets(); s++) { sheet = workbook.getSheetAt(s); sheetName = sheet.getSheetName(); hdrRow = sheet.rowIterator().next(); List<Field> sheetHeaders = new ArrayList<>(); // if the table happens to have blank columns in front of it, loop through and artificially add those as headers // This helps in the matching of headers to data later as the indexes will line up properly. for (int columnNum = 0; columnNum < hdrRow.getFirstCellNum(); columnNum++) { sheetHeaders.add(Field.create("")); } for (int columnNum = hdrRow.getFirstCellNum(); columnNum < hdrRow.getLastCellNum(); columnNum++) { Cell cell = hdrRow.getCell(columnNum); try { sheetHeaders.add(Cells.parseCell(cell, this.evaluator)); } catch (ExcelUnsupportedCellTypeException e) { throw new DataParserException(Errors.EXCEL_PARSER_05, cell.getCellTypeEnum()); } } headers.put(sheetName, sheetHeaders); } } Offsets.parse(offsetId).ifPresent(offset -> { String startSheetName = offset.getSheetName(); int startRowNum = offset.getRowNum(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); int rowNum = row.getRowNum(); String sheetName = row.getSheet().getSheetName(); // if a sheet has blank rows at the top then the starting row number may be higher than a default offset of zero or one, thus the >= compare if (startSheetName.equals(sheetName) && rowNum >= startRowNum) { if (rowIterator.hasPrevious()) { row = rowIterator.previous(); this.currentSheet = row.getRowNum() == row.getSheet().getFirstRowNum() ? null : row.getSheet().getSheetName(); // used in comparison later to see if we've moved to new sheet } else { this.currentSheet = null; } break; } } }); }
From source file:com.svi.uzabase.logic.ExtractData.java
private List<String> extractCompanyNames() { List<String> companyList = new ArrayList<>(); try {/* www.ja v a 2s.co m*/ FileInputStream inputStream = new FileInputStream(new File(COMPANY_EXCEL_PATH)); Workbook workbook = new HSSFWorkbook(inputStream); Row row; Cell cell; Sheet sheet; sheet = workbook.getSheetAt(0); for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { row = sheet.getRow(rowIndex); for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) { cell = row.getCell(colIndex); cell.setCellType(Cell.CELL_TYPE_STRING); if (colIndex == 1) { companyList.add(cell.getStringCellValue().toUpperCase()); } } } //Closes opened documents inputStream.close(); workbook.close(); } catch (IOException ex) { Logger.getLogger(ExtractData.class.getName()).log(Level.SEVERE, null, ex); } return companyList; }