List of usage examples for org.apache.poi.ss.usermodel Sheet getRow
Row getRow(int rownum);
From source file:com.ostrichemulators.semtool.poi.main.POIReader.java
License:Open Source License
public static ImportData readNonloadingSheet(Workbook workbook) { ImportData id = new ImportData(); int sheets = workbook.getNumberOfSheets(); for (int sheetnum = 0; sheetnum < sheets; sheetnum++) { Sheet sheet = workbook.getSheetAt(sheetnum); String sheetname = workbook.getSheetName(sheetnum); // we need to shoehorn the arbitrary data from a spreadsheet into our // ImportData class, which has restrictions on the data...we're going // to do it by figuring out the row with the most columns, and then // naming all the columns with A, B, C...AA, AB... // then load everything as if it was plain data // first, figure out our max number of columns int rows = sheet.getLastRowNum(); int maxcols = Integer.MIN_VALUE; for (int r = 0; r <= rows; r++) { Row row = sheet.getRow(r); if (null != row) { int cols = (int) row.getLastCellNum(); if (cols > maxcols) { maxcols = cols;/* w w w . j a v a 2 s .c om*/ } } } // second, make "properties" for each column LoadingSheetData nlsd = new LoadingSheetData(sheetname, "A"); for (int c = 1; c < maxcols; c++) { nlsd.addProperty(Integer.toString(c)); } // lastly, fill the sheets for (int r = 0; r <= rows; r++) { Row row = sheet.getRow(r); if (null != row) { Map<String, Value> propmap = new HashMap<>(); int lastpropcol = row.getLastCellNum(); for (int c = 1; c <= lastpropcol; c++) { String val = getString(row.getCell(c)); if (!val.isEmpty()) { propmap.put(Integer.toString(c), VF.createLiteral(val)); } } nlsd.add(getString(row.getCell(0)), propmap); } } if (!nlsd.isEmpty()) { id.add(nlsd); } } return id; }
From source file:com.pactera.edg.am.metamanager.extractor.adapter.mapping.helper.AmCommonCodeDeleteDataGenerator.java
License:Open Source License
/** * @return HashMap<,LinkedHashMap<, ?>> */// w w w.j a v a 2s .c o m public Map<String, List<Map<String, String>>> generateDeleteData() { // ? Map<String, List<Map<String, String>>> deleteDatas = new HashMap<String, List<Map<String, String>>>( deleteDataBos.size()); // ?? Collections.reverse(deleteDataBos); for (DeleteDataBo deleteDataBo : deleteDataBos) { if (!INCLUDING_CLASSIFIERS.contains(deleteDataBo.getClassifierId())) { // ?????? continue; } Sheet sheet = wBook.getSheet(deleteDataBo.getSheetName()); // ? int maxRowNum = (deleteDataBo.getDEnd() >= deleteDataBo.getDStart()) ? deleteDataBo.getDEnd() - 1 : sheet.getLastRowNum(); List<Map<String, String>> deleteDataList = new ArrayList<Map<String, String>>(); for (int rowIndex = deleteDataBo.getDStart() - 1; rowIndex <= maxRowNum; rowIndex++) { try { Row row = sheet.getRow(rowIndex); if (row == null) continue; boolean rowIsBlank = rowIsBlank(row); if (rowIsBlank) // ?NULL,?CELL?NULL''?? continue; Map<String, int[]> pathPosition = deleteDataBo.getPathPosition(); Map<String, String> deleteData = new LinkedHashMap<String, String>(pathPosition.size()); // ??? boolean dataIsNormal = true; for (Iterator<String> classifierIdIter = pathPosition.keySet().iterator(); classifierIdIter .hasNext();) { String classifierId = classifierIdIter.next(); int[] positions = pathPosition.get(classifierId); // ?NULL''? String value = getCode(POIExcelUtil.getCellValue(positions, row)); if (value.equals("")) { // ?,?.. dataIsNormal = false; break; } deleteData.put(classifierId, value); } //if (dataIsNormal) { // ?,?? deleteDataList.add(deleteData); //} } catch (Exception e) { // ?? e.printStackTrace(); } } deleteDatas.put(deleteDataBo.getClassifierId(), deleteDataList); } return deleteDatas; }
From source file:com.pactera.edg.am.metamanager.extractor.adapter.mapping.helper.AmProductDeleteDataGenerator.java
License:Open Source License
/** * @return HashMap<,LinkedHashMap<, ?>> *///w w w . j a v a2 s . c o m public Map<String, List<Map<String, String>>> generateDeleteData() { // ? Map<String, List<Map<String, String>>> deleteDatas = new HashMap<String, List<Map<String, String>>>( deleteDataBos.size()); // ?? Collections.reverse(deleteDataBos); for (DeleteDataBo deleteDataBo : deleteDataBos) { if (!INCLUDING_CLASSIFIERS.contains(deleteDataBo.getClassifierId())) { // ?????? continue; } Sheet sheet = wBook.getSheet(deleteDataBo.getSheetName()); // ? int maxRowNum = (deleteDataBo.getDEnd() >= deleteDataBo.getDStart()) ? deleteDataBo.getDEnd() - 1 : sheet.getLastRowNum(); List<Map<String, String>> deleteDataList = new ArrayList<Map<String, String>>(); for (int rowIndex = deleteDataBo.getDStart() - 1; rowIndex <= maxRowNum; rowIndex++) { try { Row row = sheet.getRow(rowIndex); if (row == null) continue; boolean rowIsBlank = rowIsBlank(row); if (rowIsBlank) // ?NULL,?CELL?NULL''?? continue; Map<String, int[]> pathPosition = deleteDataBo.getPathPosition(); Map<String, String> deleteData = new LinkedHashMap<String, String>(pathPosition.size()); // ??? boolean dataIsNormal = true; for (Iterator<String> classifierIdIter = pathPosition.keySet().iterator(); classifierIdIter .hasNext();) { String classifierId = classifierIdIter.next(); int[] positions = pathPosition.get(classifierId); // ?NULL''? String value = getCode(POIExcelUtil.getCellValue(positions, row)); if (value.equals("")) { // ?,?.. dataIsNormal = false; break; } deleteData.put(classifierId, value); } //if (dataIsNormal) { // ?,?? deleteDataList.add(deleteData); //} } catch (Exception e) { // ?? e.printStackTrace(); } } if ("AmProduct".equals(deleteDataBo.getClassifierId())) { if (deleteDatas.get(deleteDataBo.getClassifierId()) == null) deleteDatas.put(deleteDataBo.getClassifierId(), deleteDataList); } else { deleteDatas.put(deleteDataBo.getClassifierId(), deleteDataList); } } return deleteDatas; }
From source file:com.pactera.edg.am.metamanager.extractor.adapter.mapping.helper.AmTemplateDeleteDataGenerator.java
License:Open Source License
/** * @return HashMap<,LinkedHashMap<, ?>> *//*from w ww . j a v a2 s . c o m*/ public Map<String, List<Map<String, String>>> generateDeleteData() { // ? Map<String, List<Map<String, String>>> deleteDatas = new HashMap<String, List<Map<String, String>>>( deleteDataBos.size()); // ?? Collections.reverse(deleteDataBos); for (DeleteDataBo deleteDataBo : deleteDataBos) { if (!INCLUDING_CLASSIFIERS.contains(deleteDataBo.getClassifierId())) { // ?????? continue; } Sheet sheet = wBook.getSheet(deleteDataBo.getSheetName()); // ? int maxRowNum = (deleteDataBo.getDEnd() >= deleteDataBo.getDStart()) ? deleteDataBo.getDEnd() - 1 : sheet.getLastRowNum(); List<Map<String, String>> deleteDataList = new ArrayList<Map<String, String>>(); for (int rowIndex = deleteDataBo.getDStart() - 1; rowIndex <= maxRowNum; rowIndex++) { try { Row row = sheet.getRow(rowIndex); if (row == null) continue; boolean rowIsBlank = rowIsBlank(row); if (rowIsBlank) // ?NULL,?CELL?NULL''?? continue; Map<String, int[]> pathPosition = deleteDataBo.getPathPosition(); Map<String, String> deleteData = new LinkedHashMap<String, String>(pathPosition.size()); // ??? boolean dataIsNormal = true; for (Iterator<String> classifierIdIter = pathPosition.keySet().iterator(); classifierIdIter .hasNext();) { String classifierId = classifierIdIter.next(); int[] positions = pathPosition.get(classifierId); // ?NULL''? String value = getCode(POIExcelUtil.getCellValue(positions, row)); if (value.equals("")) { // ?,?.. dataIsNormal = false; break; } deleteData.put(classifierId, value); } if (dataIsNormal) { // ?,?? deleteDataList.add(deleteData); } } catch (Exception e) { // ?? e.printStackTrace(); } } deleteDatas.put(deleteDataBo.getClassifierId(), deleteDataList); } return deleteDatas; }
From source file:com.pactera.edg.am.metamanager.extractor.adapter.mapping.helper.MappingDeleteDataGenerator.java
License:Open Source License
/** * @return HashMap<,LinkedHashMap<, ?>> *///w w w .j a v a2 s. c om public Map<String, List<Map<String, String>>> generateDeleteData() { // ? Map<String, List<Map<String, String>>> deleteDatas = new HashMap<String, List<Map<String, String>>>( deleteDataBos.size()); // ?? Collections.reverse(deleteDataBos); for (DeleteDataBo deleteDataBo : deleteDataBos) { if (!INCLUDING_CLASSIFIERS.contains(deleteDataBo.getClassifierId())) { // ?????? continue; } Sheet sheet = wBook.getSheet(deleteDataBo.getSheetName()); // ? int maxRowNum = (deleteDataBo.getDEnd() >= deleteDataBo.getDStart()) ? deleteDataBo.getDEnd() - 1 : sheet.getLastRowNum(); List<Map<String, String>> deleteDataList = new ArrayList<Map<String, String>>(); for (int rowIndex = deleteDataBo.getDStart() - 1; rowIndex <= maxRowNum; rowIndex++) { try { Row row = sheet.getRow(rowIndex); if (row == null) continue; boolean rowIsBlank = rowIsBlank(row); if (rowIsBlank) // ?NULL,?CELL?NULL''?? continue; Map<String, int[]> pathPosition = deleteDataBo.getPathPosition(); Map<String, String> deleteData = new LinkedHashMap<String, String>(pathPosition.size()); // ??? boolean dataIsNormal = true; for (Iterator<String> classifierIdIter = pathPosition.keySet().iterator(); classifierIdIter .hasNext();) { String classifierId = classifierIdIter.next(); int[] positions = pathPosition.get(classifierId); // ?NULL''? String value = getCode(POIExcelUtil.getCellValue(positions, row)); if (value.equals("")) { // ?,?.. dataIsNormal = false; break; } deleteData.put(classifierId, value); } //if (dataIsNormal) { // ?,?? deleteDataList.add(deleteData); //} } catch (Exception e) { // ?? e.printStackTrace(); } } deleteDatas.put(deleteDataBo.getClassifierId(), deleteDataList); } try { deleteRelObj(deleteDatas); } catch (Throwable e) { // TODO Auto-generated catch block e.printStackTrace(); } return deleteDatas; }
From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java
public FileInputStream generateAllEmployeeReport(File file) throws ExceptionWrapper { try {//from ww w .jav a 2s. c om FileInputStream inp = new FileInputStream(file); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); int iRowCounter = 1; for (EmployeeBO employeeBO : employeeBL.getAllEmployees().values()) { Row row = sheet.getRow(iRowCounter); if (row == null) row = sheet.createRow(iRowCounter); Cell cell = row.getCell(0); if (cell == null) cell = row.createCell(0); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeId()); cell = row.getCell(1); if (cell == null) cell = row.createCell(1); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeUid()); cell = row.getCell(2); if (cell == null) cell = row.createCell(2); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeName()); cell = row.getCell(3); if (cell == null) cell = row.createCell(3); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmail()); cell = row.getCell(4); if (cell == null) cell = row.createCell(4); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getManager()); cell = row.getCell(5); if (cell == null) cell = row.createCell(5); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getMobileNumber()); cell = row.getCell(6); if (cell == null) cell = row.createCell(6); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getExtensionNum()); cell = row.getCell(7); if (cell == null) cell = row.createCell(7); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getWorkspace()); iRowCounter = iRowCounter + 1; } FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); return new FileInputStream(file); } catch (Exception ex) { throw new ExceptionWrapper(ex); } }
From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java
public FileInputStream generateAllEmployeeTeamWiseReport(File file, Boolean isScrumTeam) throws ExceptionWrapper { try {// w ww . j a va 2s.c o m FileInputStream inp = new FileInputStream(file); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); int iSheetCounter = 1; for (Integer groupID : employeeBL.getAllEmployeesKeyedByGroupId().keySet()) { GroupBO groupBO = groupsBL.getAllGroups().get(groupID); if (isScrumTeam && !groupBO.getRallyGroup()) continue; if (!isScrumTeam && groupBO.getRallyGroup()) continue; Sheet sheet = wb.cloneSheet(0); wb.setSheetName(wb.getSheetIndex(sheet), groupBO.getGroupName()); //wb.setSheetName(iSheetCounter,groupBO.getGroupName()); int iRowCounter = 1; for (EmployeeBO employeeBO : employeeBL.getAllEmployeesKeyedByGroupId().get(groupID)) { Row row = sheet.getRow(iRowCounter); if (row == null) row = sheet.createRow(iRowCounter); Cell cell = row.getCell(0); if (cell == null) cell = row.createCell(0); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeId()); cell = row.getCell(1); if (cell == null) cell = row.createCell(1); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeUid()); cell = row.getCell(2); if (cell == null) cell = row.createCell(2); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeName()); cell = row.getCell(3); if (cell == null) cell = row.createCell(3); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmail()); cell = row.getCell(4); if (cell == null) cell = row.createCell(4); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getManager()); cell = row.getCell(5); if (cell == null) cell = row.createCell(5); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getMobileNumber()); cell = row.getCell(6); if (cell == null) cell = row.createCell(6); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getExtensionNum()); cell = row.getCell(7); if (cell == null) cell = row.createCell(7); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getWorkspace()); if (isScrumTeam) { cell = row.getCell(8); if (cell == null) cell = row.createCell(8); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getSpecificRoleInScrumTeam()); } iRowCounter = iRowCounter + 1; } } iSheetCounter = iSheetCounter + 1; wb.removeSheetAt(0); FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); return new FileInputStream(file); } catch (Exception ex) { throw new ExceptionWrapper(ex); } }
From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java
public FileInputStream generateLicenseReport(File file) throws ExceptionWrapper { try {/*from www .ja v a 2s .c om*/ FileInputStream inp = new FileInputStream(file); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); HashMap<String, ArrayList<String>> licenseInfoKeyedByLicenseName = licensesBL.getLicenseRelatedInfo(); //Create The Summary Sheet Sheet sheetSummary = wb.getSheetAt(1); int iSummaryRowCounter = 1; for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) { Row row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); Cell cell = row.getCell(0); if (cell == null) cell = row.createCell(0); setCellBorder(wb, cell); cell.setCellValue(licenseType); row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); cell = row.getCell(1); if (cell == null) cell = row.createCell(1); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); cell = row.getCell(2); if (cell == null) cell = row.createCell(2); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); row = sheetSummary.getRow(iSummaryRowCounter); if (row == null) row = sheetSummary.createRow(iSummaryRowCounter); cell = row.getCell(3); if (cell == null) cell = row.createCell(3); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)) - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); iSummaryRowCounter = iSummaryRowCounter + 1; } int iSheetCounter = 1; for (String licenseType : licenseInfoKeyedByLicenseName.keySet()) { Sheet sheet = wb.cloneSheet(0); wb.setSheetName(wb.getSheetIndex(sheet), licenseType); CellReference cellReference = new CellReference("B1"); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); if (cell == null) cell = row.createCell(cellReference.getCol()); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cellReference = new CellReference("B2"); row = sheet.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); if (cell == null) cell = row.createCell(cellReference.getCol()); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cellReference = new CellReference("B3"); row = sheet.getRow(cellReference.getRow()); cell = row.getCell(cellReference.getCol()); if (cell == null) cell = row.createCell(cellReference.getCol()); setCellBorder(wb, cell); cell.setCellValue(Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(0)) - Double.parseDouble(licenseInfoKeyedByLicenseName.get(licenseType).get(1))); cell.setCellType(Cell.CELL_TYPE_NUMERIC); ArrayList<EmployeeBO> allEmployees = new ArrayList<EmployeeBO>( employeeBL.getAllEmployees().values()); final String selectedLicenseTypeName = licenseType; CollectionUtils.filter(allEmployees, new Predicate() { @Override public boolean evaluate(Object o) { EmployeeBO emp = (EmployeeBO) o; if (CollectionUtils.exists(emp.getLicenses(), new Predicate() { @Override public boolean evaluate(Object o) { return ((LicenseBO) o).getLicenseTypeName() .equalsIgnoreCase(selectedLicenseTypeName); //To change body of implemented methods use File | Settings | File Templates. } })) return true; else return false; } }); int iRowCounter = 5; for (EmployeeBO employeeBO : allEmployees) { row = sheet.getRow(iRowCounter); if (row == null) row = sheet.createRow(iRowCounter); cell = row.getCell(0); if (cell == null) cell = row.createCell(0); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeId()); cell = row.getCell(1); if (cell == null) cell = row.createCell(1); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeUid()); cell = row.getCell(2); if (cell == null) cell = row.createCell(2); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmployeeName()); cell = row.getCell(3); if (cell == null) cell = row.createCell(3); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getEmail()); cell = row.getCell(4); if (cell == null) cell = row.createCell(4); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getManager()); cell = row.getCell(5); if (cell == null) cell = row.createCell(5); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getMobileNumber()); cell = row.getCell(6); if (cell == null) cell = row.createCell(6); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getExtensionNum()); cell = row.getCell(7); if (cell == null) cell = row.createCell(7); setCellBorder(wb, cell); cell.setCellValue(employeeBO.getWorkspace()); iRowCounter = iRowCounter + 1; } } iSheetCounter = iSheetCounter + 1; wb.removeSheetAt(0); FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); return new FileInputStream(file); } catch (Exception ex) { throw new ExceptionWrapper(ex); } }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override public List<LinkedList<Object>> importVendorSpecificFuelLog(InputStream is, LinkedHashMap<String, String> vendorSpecificColumns, Long vendor, HashMap<String, Object> additionalVendorData) throws Exception { List<LinkedList<Object>> data = new ArrayList<LinkedList<Object>>(); try {//from w ww .jav a2 s.c o m POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); Sheet sheet = wb.getSheetAt(0); Row titleRow = sheet.getRow(sheet.getFirstRowNum()); LinkedHashMap<String, Integer> orderedColIndexes = getOrderedColumnIndexes(titleRow, vendorSpecificColumns); Set<Entry<String, Integer>> keySet = orderedColIndexes.entrySet(); System.out.println("Physical number of rows in Excel = " + sheet.getPhysicalNumberOfRows()); System.out.println("While reading values from vendor specific Excel Sheet: "); Map criterias = new HashMap(); criterias.put("id", vendor); FuelVendor fuelVendor = genericDAO.findByCriteria(FuelVendor.class, criterias, "name", false).get(0); boolean stopParsing = false; for (int i = titleRow.getRowNum() + 1; !stopParsing && i <= sheet.getPhysicalNumberOfRows() - 1; i++) { LinkedList<Object> rowObjects = new LinkedList<Object>(); rowObjects.add(fuelVendor.getName()); rowObjects.add(fuelVendor.getCompany().getName()); Row row = sheet.getRow(i); Iterator<Entry<String, Integer>> iterator = keySet.iterator(); while (iterator.hasNext()) { Entry<String, Integer> entry = iterator.next(); // corresponding column not found in actual column list, find in additionalVendorData if (entry.getValue() == -1) { System.out.println("Additional vendor data = " + additionalVendorData); System.out.println("Column " + entry.getKey() + " not found in Vendor Excel, checking in additionalVendorData"); Object cellValueObj = additionalVendorData.get(entry.getKey()); if (cellValueObj != null) { rowObjects.add(cellValueObj); } else { rowObjects.add(StringUtils.EMPTY); } continue; } Object cellValueObj = getCellValue((HSSFCell) row.getCell(entry.getValue()), true); if (cellValueObj != null && cellValueObj.toString().equalsIgnoreCase("END_OF_DATA")) { System.out.println("Received END_OF_DATA"); stopParsing = true; rowObjects.clear(); break; } rowObjects.add(cellValueObj); } if (!stopParsing) { data.add(rowObjects); } } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return data; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override public List<LinkedList<Object>> importTollCompanySpecificTollTag(InputStream is, LinkedHashMap<String, String> tollCompanySpecificColumns, Long tollCompanyId) throws Exception { List<LinkedList<Object>> data = new ArrayList<LinkedList<Object>>(); try {/*from w w w.j a v a 2 s. co m*/ POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); Sheet sheet = wb.getSheetAt(0); Row titleRow = sheet.getRow(sheet.getFirstRowNum()); LinkedHashMap<String, Integer> orderedColIndexes = getOrderedColumnIndexes(titleRow, tollCompanySpecificColumns); Set<Entry<String, Integer>> keySet = orderedColIndexes.entrySet(); System.out.println("Physical number of rows in Excel = " + sheet.getPhysicalNumberOfRows()); System.out.println("While reading values from vendor specific Excel Sheet: "); Map criterias = new HashMap(); criterias.put("id", tollCompanyId); TollCompany tollCompany = genericDAO.findByCriteria(TollCompany.class, criterias, "name", false).get(0); //boolean stopParsing = false; for (int i = titleRow.getRowNum() + 1; i <= sheet.getPhysicalNumberOfRows() - 1; i++) { Row row = sheet.getRow(i); Object firstCellValueObj = getCellValue((HSSFCell) row.getCell(0), true); if (firstCellValueObj != null && firstCellValueObj.toString().equalsIgnoreCase("END_OF_DATA")) { System.out.println("Received END_OF_DATA"); break; } LinkedList<Object> rowObjects = new LinkedList<Object>(); rowObjects.add(tollCompany.getName()); /*// TODO: For now, need to get logic String company = StringUtils.substringAfterLast(tollCompany.getName(), " "); company = StringUtils.defaultIfEmpty(company, "LU"); rowObjects.add(company);*/ rowObjects.add(tollCompany.getCompany().getName()); Iterator<Entry<String, Integer>> iterator = keySet.iterator(); while (iterator.hasNext()) { Entry<String, Integer> entry = iterator.next(); if (entry.getValue() == -1) { // corresponding column not found rowObjects.add(StringUtils.EMPTY); continue; } Object cellValueObj = getCellValue((HSSFCell) row.getCell(entry.getValue()), true); if (cellValueObj != null) { System.out.println("Adding " + cellValueObj.toString()); } else { System.out.println("Adding NULL"); } rowObjects.add(cellValueObj); } data.add(rowObjects); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return data; }