List of usage examples for org.apache.poi.ss.usermodel Sheet getPhysicalNumberOfRows
int getPhysicalNumberOfRows();
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 .jav a 2 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, 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; }
From source file:com.projectswg.tools.SwgExcelConverter.java
License:Open Source License
public SWGFile convert(Sheet sheet) { Row header = sheet.getRow(sheet.getFirstRowNum()); if (header == null) return null; int headerNum = header.getRowNum(); // Create base datatable iff SWGFile swgFile = new SWGFile("DTII"); swgFile.addForm("0001"); // Create individual iff info int columns = createTableColumnData(swgFile, header); String[] types = createTableTypeData(swgFile, sheet.getRow(headerNum + 1), columns); if (types == null) return null; int rows = sheet.getPhysicalNumberOfRows(); List<DatatableRow> rowList = new ArrayList<>(); for (int i = headerNum + 2; i < rows; i++) { rowList.add(getDataTableRow(sheet.getRow(i), columns, types)); }/* ww w. j av a2 s . c o m*/ createTableRowData(swgFile, rowList); return swgFile; }
From source file:com.simopuve.helper.ReadPVDFromFile.java
private static List<PDVRow> getRowsFromWorkbook(Sheet sheet) { Map rowMap = getPDVRowPropertiesCoordinatesByName(); List<PDVRow> PDVRowList = new ArrayList<>(); DataFormatter formatter = new DataFormatter(); PropertyCoordinates tmpCoordinate;// w ww.ja v a2s .co m String text; sheet.getPhysicalNumberOfRows(); //TODO revisar si es siempre as int longitud = (sheet.getPhysicalNumberOfRows() - FIRST_ROW) - 2; int endRows = FIRST_ROW + longitud; int i = FIRST_ROW; String personNum = getTextFromCell(FIRST_ROW, 0, formatter, sheet, sheet.getRow(FIRST_ROW)); boolean isPersonNumberEmpty = personNum.isEmpty(); while (!isPersonNumberEmpty) { Row row = sheet.getRow(i); PDVRow PDVRow = new PDVRow(); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceBrand"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceBrand(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceModel"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceModel(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("contractType"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setContractType(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceMode"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceMode(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("additionalCharacteristics"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setAdditionalCharacteristics(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("planRating"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPlanRating(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("deviceRating"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setDeviceRating(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("portabilityChange"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPortabilityChange(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("portabilityChangeReason"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPortabilityChangeReason(text); tmpCoordinate = (PropertyCoordinates) rowMap.get("personNumber"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setPersonNumber(Integer.parseInt(text)); isPersonNumberEmpty = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, sheet.getRow(i + 1)).isEmpty(); tmpCoordinate = (PropertyCoordinates) rowMap.get("expressRefillValue"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); String tmpTxt = text.replace("$", ""); tmpTxt = tmpTxt.replaceAll("\\s+", ""); tmpTxt = tmpTxt.replaceAll(",", ""); tmpTxt = tmpTxt.replaceAll(".", ""); Logger.getLogger(ReadPVDFromFile.class.getName()).log(Level.INFO, "valor a convertir:" + tmpTxt); if (tmpTxt.isEmpty()) PDVRow.setExpressRefillValue(0); else if (tmpTxt == "" || tmpTxt == null) PDVRow.setExpressRefillValue(0); else PDVRow.setExpressRefillValue(Integer.parseInt(tmpTxt)); tmpCoordinate = (PropertyCoordinates) rowMap.get("boughtCard"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setBoughtCard(!("No".equals(text))); tmpCoordinate = (PropertyCoordinates) rowMap.get("boughtChip"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setBoughtChip(!("No".equals(text))); tmpCoordinate = (PropertyCoordinates) rowMap.get("boughtAccessory"); text = getTextFromCell(tmpCoordinate.getRowX(), tmpCoordinate.getCellY(), formatter, sheet, row); PDVRow.setBoughtAccessory(!("No".equals(text))); PDVRowList.add(PDVRow); Logger.getLogger(ReadPVDFromFile.class.getName()).log(Level.INFO, PDVRow.toString()); i++; } Logger.getLogger(ReadPVDFromFile.class.getName()).log(Level.INFO, "Tama\u00f1o de rows: {0}", PDVRowList.size()); return PDVRowList; }
From source file:com.validation.manager.core.tool.requirement.importer.RequirementImporter.java
License:Apache License
@Override public List<Requirement> importFile(boolean header) throws RequirementImportException, VMException { queue.clear();/*from w ww . j av a 2 s. co m*/ List<Integer> errors = new ArrayList<>(); HashMap<String, Object> parameters = new HashMap<>(); List<Object> result; if (toImport == null) { throw new RequirementImportException("message.requirement.import.file.null"); } else if (!toImport.exists()) { throw new RequirementImportException("message.requirement.import.file.invalid"); } else { //Excel support if (toImport.getName().endsWith(".xls") || toImport.getName().endsWith(".xlsx")) { try { Workbook wb = loadFile(); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); int r = 0; if (header) { //Skip header row r++; } for (; r < rows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } if (row.getCell(0) == null) { LOG.log(Level.WARNING, "Found an empty row on line: {0}. " + "Stopping processing", r); break; } int cells = row.getPhysicalNumberOfCells(); if (cells < 2) { LOG.log(Level.INFO, "Processing row: {0}", r); LOG.warning(ResourceBundle .getBundle("com.validation.manager.resources.VMMessages", Locale.getDefault()) .getString("message.requirement.import.missing.column") .replaceAll("%c", "" + cells)); errors.add(r); } else { Requirement requirement = new Requirement(); LOG.log(Level.FINE, "Row: {0}", r); for (int c = 0; c < cells; c++) { Cell cell = row.getCell(c); String value = ""; if (cell != null) { switch (cell.getCellTypeEnum()) { case FORMULA: value = cell.getCellFormula(); break; case NUMERIC: value = "" + cell.getNumericCellValue(); break; case STRING: value = cell.getStringCellValue(); break; default: value = ""; break; } } //Remove any extra spaces. value = value.trim(); switch (c) { case 0: //Unique ID LOG.fine("Setting id"); requirement.setUniqueId(value); break; case 1: //Description LOG.fine("Setting desc"); requirement.setDescription(value); break; case 2: //Optional Requirement type LOG.fine("Setting requirement type"); parameters.clear(); parameters.put("name", value); result = namedQuery("RequirementType.findByName", parameters); if (result.isEmpty()) { //Assume a default parameters.clear(); parameters.put("name", "SW"); result = namedQuery("RequirementType.findByName", parameters); } requirement.setRequirementTypeId((RequirementType) result.get(0)); break; case 3: //Optional notes LOG.fine("Setting notes"); requirement.setNotes(value); break; default: throw new RequirementImportException("Invalid column detected: " + c); } LOG.fine(value); } //This shouldn't be null assert rsn != null : "Requirement Spec Node is null?"; requirement.setRequirementSpecNode(rsn); parameters.clear(); parameters.put("status", "general.open"); result = namedQuery("RequirementStatus.findByStatus", parameters); requirement.setRequirementStatusId((RequirementStatus) result.get(0)); assert requirement.getUniqueId() != null && !requirement.getUniqueId().isEmpty() : "Invalid requirement detected!"; try { if (!exists(requirement) && !queue.containsKey(requirement.getUniqueId())) { queue.put(requirement.getUniqueId(), requirement); } } catch (IllegalOrphanException | NonexistentEntityException ex) { Exceptions.printStackTrace(ex); } } } } catch (InvalidFormatException | IOException ex) { LOG.log(Level.SEVERE, null, ex); } finally { try { if (inp != null) { inp.close(); } } catch (IOException ex) { LOG.log(Level.SEVERE, null, ex); } } } else if (toImport.getName().endsWith(".xml")) { throw new RequirementImportException("XML importing not supported yet."); } else if (toImport.getName().endsWith(".doc") || toImport.getName().endsWith(".docx")) { try { TableExtractor te = new TableExtractor(toImport); List<DefaultTableModel> tables = te.extractTables(); Requirement requirement = new Requirement(); LOG.log(Level.INFO, "Imported {0} tables!", tables.size()); int count = 1; for (DefaultTableModel model : tables) { int rows = model.getRowCount(); int cols = model.getColumnCount(); LOG.log(Level.INFO, "Processing table {0} with {1} " + "rows and {2} columns.", new Object[] { count, rows, cols }); for (int r = 0; r < rows; r++) { for (int c = 0; c < cols; c++) { String value = (String) model.getValueAt(rows, cols); switch (c) { case 0: //Unique ID LOG.fine("Setting id"); requirement.setUniqueId(value); break; case 1: //Description LOG.fine("Setting desc"); requirement.setDescription(value); break; case 2: //Requirement type LOG.fine("Setting requirement type"); parameters.clear(); parameters.put("name", value); result = namedQuery("RequirementType.findByName", parameters); if (result.isEmpty()) { //Assume a default parameters.clear(); parameters.put("name", "SW"); result = namedQuery("RequirementType.findByName", parameters); } requirement.setRequirementTypeId((RequirementType) result.get(0)); break; case 3: //Optional notes LOG.fine("Setting notes"); requirement.setNotes(value); break; default: throw new RuntimeException("Invalid column detected: " + c); } } } } } catch (IOException | ClassNotFoundException ex) { Exceptions.printStackTrace(ex); } } else { throw new RequirementImportException("Unsupported file format: " + toImport.getName()); } StringBuilder sb = new StringBuilder("Rows with erros:\n"); errors.stream().forEach((line) -> { sb.append(line).append('\n'); }); if (!errors.isEmpty()) { getDefault().lookup(MessageHandler.class).info(sb.toString()); } return new ArrayList(queue.values()); } }
From source file:com.validation.manager.core.tool.step.importer.StepImporter.java
License:Apache License
@Override public List<Step> importFile(boolean header) throws TestCaseImportException { steps.clear();//from www . ja va 2 s . c om if (toImport == null) { throw new TestCaseImportException("message.step.import.file.null"); } else if (!toImport.exists()) { throw new TestCaseImportException("message.step.import.file.invalid"); } else { //Excel support if (toImport.getName().endsWith(".xls") || toImport.getName().endsWith(".xlsx")) { InputStream inp = null; try { inp = new FileInputStream(toImport); org.apache.poi.ss.usermodel.Workbook wb = create(inp); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); int r = 0; if (header) { //Skip header row r++; } for (; r < rows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); if (row.getCell(0) == null) { LOG.log(Level.WARNING, "Found an empty row on line: {0}. " + "Stopping processing", r); break; } if (cells < 2) { throw new TestCaseImportException(RB.getString("message.step.import.missing.column") .replaceAll("%c", "" + cells)); } Step step = new Step(); step.setRequirementList(new ArrayList<>()); HashMap<String, Object> parameters = new HashMap<>(); List<Object> result; LOG.log(Level.FINE, "Row: {0}", r); for (int c = 0; c < cells; c++) { Cell cell = row.getCell(c); String value = null; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case Cell.CELL_TYPE_NUMERIC: value = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; default: //Do nothing. } } switch (c) { case 0: if (value != null) { //Sequence LOG.fine("Setting sequence"); Integer val = value.contains(".") ? valueOf(value.substring(0, value.indexOf("."))) : valueOf(value); if (!tc.getStepList().isEmpty()) { int max = 0; for (Step s : tc.getStepList()) { if (s.getStepSequence() > max) { max = s.getStepSequence(); } } //Make sure there isn't one on that sequence already val += max; } step.setStepSequence(val); } break; case 1: if (value != null) { //Text LOG.fine("Setting text"); step.setText(value.getBytes("UTF-8")); } break; case 2: //Optional Related requirements if (value != null && !value.trim().isEmpty()) { LOG.fine("Setting related requirements"); StringTokenizer st = new StringTokenizer(value, ","); while (st.hasMoreTokens()) { String token = st.nextToken().trim(); parameters.clear(); parameters.put("uniqueId", token); result = namedQuery("Requirement.findByUniqueId", parameters); if (!result.isEmpty()) { for (Object o : result) { step.getRequirementList().add((Requirement) o); } } } } break; case 3: if (value != null) { //Optional Expected result LOG.fine("Setting expected result"); step.setExpectedResult(value.getBytes("UTF-8")); } break; case 4: if (value != null) { //Optional notes LOG.fine("Setting notes"); step.setNotes(value); } break; default: throw new RuntimeException("Invalid column detected: " + c); } LOG.fine(value); } step.setTestCase(tc); steps.add(step); } } catch (InvalidFormatException | IOException ex) { LOG.log(Level.SEVERE, null, ex); } finally { try { if (inp != null) { inp.close(); } } catch (IOException ex) { LOG.log(Level.SEVERE, null, ex); } } } else if (toImport.getName().endsWith(".xml")) { throw new TestCaseImportException("XML importing not supported yet."); } else { throw new TestCaseImportException("Unsupported file format: " + toImport.getName()); } return steps; } }
From source file:com.virtusa.isq.vtaf.runtime.SeleniumTestBase.java
License:Apache License
/** * Adds the values from excel./* ww w .j a v a2s.c o m*/ * * @param path * the path * @param index * the index * @return the string[][] * @throws IOException * Signals that an I/O exception has occurred. * @throws InvalidFormatException * the invalid format exception */ public final String[][] addValuesFromExcel(final String path, final String index) throws IOException, InvalidFormatException { String cellStringValue = null; double cellDoubleValue = 0; Boolean cellBooleanValue; byte cellErrorValue = 0; String[][] arrExcelContent; FileInputStream file = null; Workbook workbook = null; Sheet sheet = null; try { file = new FileInputStream(new File(path)); workbook = WorkbookFactory.create(file); sheet = workbook.getSheetAt(Integer.parseInt(index)); Iterator<Row> rowIterator = sheet.iterator(); arrExcelContent = new String[sheet.getPhysicalNumberOfRows()][]; while (rowIterator.hasNext()) { Row row = rowIterator.next(); int rowNumber = row.getRowNum(); Iterator<Cell> cellIterator = row.cellIterator(); arrExcelContent[rowNumber] = new String[sheet.getRow(rowNumber).getPhysicalNumberOfCells()]; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int cellNumber = cell.getColumnIndex(); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellStringValue = cell.getStringCellValue(); arrExcelContent[rowNumber][cellNumber] = cellStringValue; } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { cellBooleanValue = cell.getBooleanCellValue(); arrExcelContent[rowNumber][cellNumber] = cellBooleanValue.toString(); } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { cellErrorValue = cell.getErrorCellValue(); arrExcelContent[rowNumber][cellNumber] = Byte.toString(cellErrorValue); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { cellStringValue = cell.getCellFormula(); arrExcelContent[rowNumber][cellNumber] = cellStringValue; } else { cellDoubleValue = cell.getNumericCellValue(); arrExcelContent[rowNumber][cellNumber] = Double.toString(cellDoubleValue); } } } } finally { if (((InputStream) workbook) != null) { ((InputStream) workbook).close(); } } return arrExcelContent; }
From source file:com.waveconn.Excel2MySQL.java
License:Apache License
void dbImport() { FileInputStream excel_file = null; try {// www . j a v a2 s .c o m excel_file = new FileInputStream(new File(excel_file_path)); } catch (FileNotFoundException e) { System.out.println("File not found: " + excel_file_path); System.exit(-3); } try { workbook = WorkbookFactory.create(excel_file); evaluator = workbook.getCreationHelper().createFormulaEvaluator(); formatter = new DataFormatter(true); Sheet sheet = null; Row row = null; int lastRowNum = 0; System.out.println("Reading excel file content from " + excel_file_path); // Discover how many sheets there are in the workbook.... int numSheets = workbook.getNumberOfSheets(); // and then iterate through them. for (int i = 0; i < numSheets; i++) { // Get a reference to a sheet and check to see if it contains any rows. sheet = workbook.getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { // Note down the index number of the bottom-most row and // then iterate through all of the rows on the sheet starting // from the very first row - number 1 - even if it is missing. // Recover a reference to the row and then call another method // which will strip the data from the cells and build lines lastRowNum = sheet.getLastRowNum(); int start = 0; if (!is_read_first_line) start = 1; for (int j = start; j <= lastRowNum; j++) { row = sheet.getRow(j); this.rowToData(row); } } } } catch (IOException e) { e.printStackTrace(); System.out.println("IOException: " + excel_file_path); System.exit(-4); } catch (InvalidFormatException e) { e.printStackTrace(); System.out.println("Invalid Format: " + excel_file_path); System.exit(-5); } finally { if (excel_file != null) { try { excel_file.close(); } catch (IOException e) { e.printStackTrace(); System.out.println("IOException: " + excel_file_path); System.exit(-6); } } } //put valid rows into DB System.out.println("Inserting valid rows into DB table " + db_url + "/" + db_table); insertDB(); System.out.println(); //save invalid rows if any int errs = errorRows.size(); if (errs > 0) { saveError(); } else { System.out.println("There is no invalid row"); } }
From source file:com.zfer.kit.excel.importor.ExcelAbstractImportor.java
License:Apache License
public void importExcel(Sheet sheet, InputStream inputStream) throws Exception { if (sheet == null) return;/* w w w . j a va 2 s.co m*/ int rows = sheet.getPhysicalNumberOfRows(); if (rows <= 0) return; initConfig();//abstract to impl Map<Integer, Map<Integer, String>> rowNumAndRowErrorMsgMap = new HashMap<Integer, Map<Integer, String>>();//?? for (int i = readExcelValueStartRowNum; i < rows; i++) {//default 2,1 is head Row row = sheet.getRow(i); if (row == null) { continue; } Map<Integer, String> colNumAndCellValMap = new HashMap<Integer, String>(); Map<Integer, String> rowError = new HashMap<Integer, String>(); for (int cellColNum = 0; cellColNum < getMaxColNum(colNumAndFieldNameMap) + 1; cellColNum++) { Cell cell = row.getCell(cellColNum); String cellVal = ExcelPoiKit.getCellVal(cell); if (trimValue) cellVal = StrKit.getStrTrim(cellVal); //validate 1 if (requiredFieldsList != null && requiredFieldsList.contains(cellColNum) && StrKit.isBlank(cellVal)) { rowError.put(cellColNum, ExcelImportSheetErrorMsg.emptyErrorMsg); } //validate 2 if (colNumAndFieldValRegexMap != null && colNumAndFieldValRegexMap.size() != 0) { String regex = colNumAndFieldValRegexMap.get(cellColNum); if (StrKit.notBlank(regex, cellVal)) { Pattern r = Pattern.compile(regex); Matcher m = r.matcher(cellVal); boolean regexrs = m.matches(); if (!regexrs) { rowError.put(cellColNum, ExcelImportSheetErrorMsg.invalidErrorMsg); } } } colNumAndCellValMap.put(cellColNum, cellVal); } // if (rowError.isEmpty()) { T entity = setEntityFieldVal(colNumAndFieldNameMap, colNumAndCellValMap); if (entity != null) excelRightDataList.add(entity); } else { rowNumAndRowErrorMsgMap.put(i, rowError); } //??? try { T entity = setEntityFieldVal(colNumAndFieldNameMap, colNumAndCellValMap); if (entity != null) excelAllDataList.add(entity); } catch (Exception e) { e.printStackTrace(); } } excelImportSheetErrorMsg = new ExcelImportSheetErrorMsg(rowNumAndRowErrorMsgMap); }
From source file:com.zfer.kit.excel.importor.ExcelTemplateImportor.java
License:Apache License
@Override public void initConfig() throws Exception { ExcelPoiKit excelKit = new ExcelPoiKit(); excelKit.handleExcel("", getTemplateExcelInputStream()); Sheet sheet = excelKit.getSheet();//ExcelPoiKit.getSheet("", getTemplateExcelInputStream()); if (sheet == null) return;/*w w w .ja va 2s . c o m*/ int rows = sheet.getPhysicalNumberOfRows(); if (rows <= 0) return; //1.get 0 row,get colNum - FieldName Map Map<Integer, String> colNumAndFieldNameMap = ExcelPoiKit.getColNumAndValMap(sheet.getRow(0)); super.setColNumAndFieldNameMap(colNumAndFieldNameMap); //2.get 1 row,get colNum - FieldDisplayName Map Map<Integer, String> colNumAndFieldDisplayNameMap = ExcelPoiKit.getColNumAndValMap(sheet.getRow(1)); super.setColNumAndFieldDisplayNameMap(colNumAndFieldDisplayNameMap); //3.get 2 row,get required value Fields List Row validateRequiredFieldRow = sheet.getRow(2); List<Integer> requiredFieldsList = new ArrayList<Integer>(); Map<Integer, String> colNumAndRequiredValMap = ExcelPoiKit.getColNumAndValMap(validateRequiredFieldRow); for (Map.Entry<Integer, String> entry : colNumAndRequiredValMap.entrySet()) { if ("required".equals(entry.getValue())) { requiredFieldsList.add(entry.getKey()); } } super.setRequiredFieldsList(requiredFieldsList); //4.get 3 row,get colNum - value regex Map<Integer, String> colNumAndFieldValRegexMap = ExcelPoiKit.getColNumAndValMap(sheet.getRow(3)); super.setColNumAndFieldValRegexMap(colNumAndFieldValRegexMap); }
From source file:com.znsx.cms.service.impl.DeviceManagerImpl.java
private List<Dvr> readDvrRows(Sheet sheet, Organ organ) { List<Dvr> list = new ArrayList<Dvr>(); List<Manufacturer> manufs = manufacturerDAO.findAll(); List<String> dvrNames = dvrDAO.listDvrName(); // dvr int rows = sheet.getPhysicalNumberOfRows(); int rowIndex = 0; // ? int notnullRowIndex = 0; // ? List<String> snList = dvrDAO.listDvrSN(); // dvrsn? List<String> dvrNumbers = new ArrayList<String>(); String[] standardNumber = batchGenerateSN("Dvr", organ.getId(), (rows - 1)); // ?Dvr? isExcelModel(sheet.getRow(0));/* w w w .ja v a 2 s . co m*/ while (notnullRowIndex < rows) { Row row = sheet.getRow(rowIndex); if (row != null) { if (rowIndex != 0) {// ?? // dvrNames.add(isNameExist(dvrNames, rowIndex, row)); // // dvr????? dvrNumbers.add(isDvrNumber(row, rowIndex, dvrNumbers)); // ?????Dvr // snList.add(isDvrStandardNumber(row, rowIndex, snList));// // sn????????? list.add(readDvrCells(row, rowIndex, organ, standardNumber[notnullRowIndex - 1], manufs, snList)); } notnullRowIndex++; } rowIndex++; } // dvr? if (list.size() <= 0) { throw new BusinessException(ErrorCode.EXCEL_DVR_IS_NULL, "excel dvr sheet is null"); } return list; }