List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:utilities.RCFRosterToMapGenerator.java
private Map<String, String> getArmyPrisoners(String xlsxFileName) throws FileNotFoundException, IOException { Map<String, String> map = new TreeMap<String, String>(); File myFile = new File(xlsxFileName); FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook wb = new XSSFWorkbook(fis); Sheet sheet = wb.getSheetAt(SHEET_NO_SERVICES); Iterator<Row> rowIterator = sheet.rowIterator(); rowIterator.next(); //skip the header row; while (rowIterator.hasNext()) { Row row = rowIterator.next();//from w w w . j ava 2s . c o m Cell cellReg = row.getCell(REG_INDX_SERVICES); Cell cellBranch = row.getCell(INMATE_BRANCH_TYPE_INDX_SERVICES); Cell cellService = row.getCell(INMATE_SERVICE_TYPE_INDX_SERVICES); //officer / enlisted String reg = GlobalVar.trimString(cellReg.getStringCellValue()); //reg = reg.replaceAll(String.valueOf((char)160),""); String service = GlobalVar.trimString(cellService.getStringCellValue()); //service = service.replaceAll(String.valueOf((char)160),""); String branch = GlobalVar.trimString(cellBranch.getStringCellValue()); //branch = branch.replaceAll(String.valueOf((char)160),""); // System.out.println(reg + "+" + branch); if (!map.containsKey(reg) && branch.equalsIgnoreCase("Army")) { map.put(reg, service); } } return map; }
From source file:utilities.RCFRosterToMapGenerator.java
private Map<String, List<String>> getPersonalInfo(String xlsxFileName) throws FileNotFoundException, IOException { Map<String, List<String>> map = new TreeMap<>(); File myFile = new File(xlsxFileName); FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook wb = new XSSFWorkbook(fis); Sheet sheet = wb.getSheetAt(SHEET_NO_PERSONAL); Iterator<Row> rowIterator = sheet.rowIterator(); rowIterator.next(); //skip the header row; DataFormatter df = new DataFormatter(); while (rowIterator.hasNext()) { Row row = rowIterator.next();//from w ww . j a v a2 s . com Cell cellReg = row.getCell(REG_INDX_PERSONAL); String reg = GlobalVar.trimString(cellReg.getStringCellValue()); if (!map.containsKey(reg)) { List<String> list = new LinkedList<>(); String ssn = GlobalVar.fullSSNgenerator( GlobalVar.trimString(row.getCell(SSN_INDX_PERSONAL).getStringCellValue())); String name = GlobalVar.trimString(row.getCell(INMATE_NAME_INDX_PERSONAL).getStringCellValue()); String confinementType = GlobalVar .trimString(row.getCell(CONF_TYPE_INDX_PERSONAL).getStringCellValue()); //String arrDt = row.getCell(ARR_DT_INDX_PERSONAL).getStringCellValue(); //doesn't work since cell is numeric String arrDt = GlobalVar.trimString(df.formatCellValue(row.getCell(ARR_DT_INDX_PERSONAL))); list.add(ssn); // list.add(name); list.add(confinementType); list.add(arrDt); map.put(reg, list); } } return map; }
From source file:Valuacion.Importa.java
private void b_busca_ordenActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_b_busca_ordenActionPerformed h = new Herramientas(usr, menu); h.session(sessionPrograma);// w w w .j a va2 s .c om aviso.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); int resp = aviso.showOpenDialog(null); if (resp == aviso.APPROVE_OPTION) { File archivoXLS = aviso.getSelectedFile(); if (archivoXLS.exists() == true) { t_archivo.setText(archivoXLS.getAbsolutePath()); Session session = HibernateUtil.getSessionFactory().openSession(); try { FileInputStream archivo = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(archivo); Workbook libro1 = new HSSFWorkbook(fsFileSystem); Sheet especialidad = libro1.getSheetAt(0); Sheet catalogo = libro1.getSheetAt(1); Sheet marca = libro1.getSheetAt(2); Sheet tipo = libro1.getSheetAt(3); Sheet ejemplar = libro1.getSheetAt(4); Sheet orden = libro1.getSheetAt(5); Sheet partida = libro1.getSheetAt(6); Sheet compania = libro1.getSheetAt(7); Iterator rowIterator; int r = 0; //**********cargamos orden************************ rowIterator = orden.rowIterator(); r = 0; while (rowIterator.hasNext()) { HSSFRow renglon = (HSSFRow) rowIterator.next(); if (r > 0) { this.t_orden.setText("" + ((int) renglon.getCell(0).getNumericCellValue()));//id_orden this.t_aseguradora.setText("" + ((int) renglon.getCell(1).getNumericCellValue()));//compaia this.t_poliza.setText(renglon.getCell(2).getStringCellValue());//poliza this.t_siniestro.setText(renglon.getCell(3).getStringCellValue());//siniestro this.t_inciso.setText(renglon.getCell(4).getStringCellValue());//inciso this.t_reporte.setText(renglon.getCell(5).getStringCellValue());//no reporte this.t_fecha.setText("" + renglon.getCell(6).getDateCellValue());//fecha this.t_cliente.setText(renglon.getCell(7).getStringCellValue());//nombre this.t_tipo_cliente.setText(renglon.getCell(8).getStringCellValue());//tipo cliente this.t_marca.setText(renglon.getCell(9).getStringCellValue().toUpperCase());//marca this.t_tipo.setText(renglon.getCell(10).getStringCellValue());//tipo this.t_modelo.setText("" + ((int) renglon.getCell(11).getNumericCellValue()));//modelo } else r = 1; } //***********cargamos especialidades************************ rowIterator = especialidad.rowIterator(); r = 0; DefaultTableModel t_e = (DefaultTableModel) tabla_especialidad.getModel(); t_e.getDataVector().removeAllElements(); while (rowIterator.hasNext()) { HSSFRow renglon = (HSSFRow) rowIterator.next(); if (r > 0) { Object[] row_e = new Object[3]; row_e[0] = (int) renglon.getCell(0).getNumericCellValue(); //if(renglon.getCell(1).getStringCellValue().compareTo("")!=0) row_e[1] = renglon.getCell(1).getStringCellValue(); row_e[2] = renglon.getCell(2).getStringCellValue(); t_e.addRow(row_e); } else r = 1; } //***********cargamos catalogos************************ rowIterator = catalogo.rowIterator(); r = 0; DefaultTableModel t_c = (DefaultTableModel) tabla_catalogo.getModel(); t_c.getDataVector().removeAllElements(); while (rowIterator.hasNext()) { HSSFRow renglon = (HSSFRow) rowIterator.next(); if (r > 0) { Object[] row_c = new Object[4]; row_c[0] = (int) renglon.getCell(0).getNumericCellValue(); //if(renglon.getCell(1).getStringCellValue().compareTo("")!=0) row_c[1] = renglon.getCell(1).getStringCellValue(); row_c[2] = (int) renglon.getCell(2).getNumericCellValue(); row_c[3] = renglon.getCell(3).getStringCellValue(); t_c.addRow(row_c); } else r = 1; } //***********cargamos marcas************************ rowIterator = marca.rowIterator(); r = 0; tabla_marca.removeAll(); DefaultTableModel t_m = (DefaultTableModel) tabla_marca.getModel(); t_m.getDataVector().removeAllElements(); while (rowIterator.hasNext()) { HSSFRow renglon = (HSSFRow) rowIterator.next(); if (r > 0) { Object[] row_m = new Object[3]; row_m[0] = renglon.getCell(0).getStringCellValue(); row_m[1] = renglon.getCell(1).getStringCellValue(); row_m[2] = renglon.getCell(2).getStringCellValue(); t_m.addRow(row_m); } else r = 1; } //**********cargamos tipos************************ rowIterator = tipo.rowIterator(); r = 0; tabla_tipo.removeAll(); DefaultTableModel t_t = (DefaultTableModel) tabla_tipo.getModel(); t_t.getDataVector().removeAllElements(); while (rowIterator.hasNext()) { HSSFRow renglon = (HSSFRow) rowIterator.next(); if (r > 0) { Object[] row_t = new Object[3]; row_t[0] = renglon.getCell(0).getStringCellValue(); if (renglon.getCell(1) .getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC) row_t[1] = (int) renglon.getCell(1).getNumericCellValue(); row_t[2] = renglon.getCell(2).getStringCellValue(); t_t.addRow(row_t); } else r = 1; } //**********cargamos ejemplares************************ rowIterator = ejemplar.rowIterator(); r = 0; DefaultTableModel t_eje = (DefaultTableModel) tabla_ejemplar.getModel(); t_eje.getDataVector().removeAllElements(); while (rowIterator.hasNext()) { HSSFRow renglon = (HSSFRow) rowIterator.next(); if (r > 0) { Object[] row_eje = new Object[7]; row_eje[0] = renglon.getCell(0).getStringCellValue(); row_eje[1] = renglon.getCell(1).getStringCellValue(); row_eje[2] = renglon.getCell(2).getStringCellValue(); if (renglon.getCell(3) .getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC) row_eje[3] = (int) renglon.getCell(3).getNumericCellValue(); else row_eje[3] = renglon.getCell(3).getStringCellValue(); if (renglon.getCell(4) .getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC) row_eje[4] = (int) renglon.getCell(4).getNumericCellValue(); else row_eje[4] = renglon.getCell(4).getStringCellValue(); row_eje[5] = renglon.getCell(5).getStringCellValue(); row_eje[6] = renglon.getCell(6).getStringCellValue(); t_eje.addRow(row_eje); } else r = 1; } //**********cargamos partidas************************ rowIterator = partida.rowIterator(); r = 0; DefaultTableModel t_p = (DefaultTableModel) tabla_partidas.getModel(); t_p.getDataVector().removeAllElements(); while (rowIterator.hasNext()) { HSSFRow renglon = (HSSFRow) rowIterator.next(); if (r > 0) { Object[] row_par = new Object[50]; row_par[0] = (int) renglon.getCell(0).getNumericCellValue(); row_par[1] = (int) renglon.getCell(1).getNumericCellValue(); row_par[2] = (int) renglon.getCell(2).getNumericCellValue(); row_par[3] = renglon.getCell(3).getBooleanCellValue(); row_par[4] = renglon.getCell(4).getBooleanCellValue(); row_par[5] = renglon.getCell(5).getBooleanCellValue(); row_par[6] = renglon.getCell(6).getBooleanCellValue(); row_par[7] = renglon.getCell(7).getNumericCellValue(); row_par[8] = renglon.getCell(8).getNumericCellValue(); row_par[9] = renglon.getCell(9).getNumericCellValue(); row_par[10] = renglon.getCell(10).getNumericCellValue(); row_par[11] = renglon.getCell(11).getNumericCellValue(); row_par[12] = renglon.getCell(12).getNumericCellValue(); row_par[13] = renglon.getCell(13).getNumericCellValue(); row_par[14] = renglon.getCell(14).getStringCellValue(); if (renglon.getCell(15) .getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC) row_par[15] = (int) renglon.getCell(15).getNumericCellValue(); if (renglon.getCell(16).getStringCellValue().compareTo("") != 0) row_par[16] = renglon.getCell(16).getStringCellValue(); row_par[17] = renglon.getCell(17).getBooleanCellValue(); row_par[18] = renglon.getCell(18).getBooleanCellValue(); row_par[19] = renglon.getCell(19).getBooleanCellValue(); row_par[20] = renglon.getCell(20).getBooleanCellValue(); row_par[21] = renglon.getCell(21).getBooleanCellValue(); if (renglon.getCell(22) .getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC) row_par[22] = (int) renglon.getCell(22).getNumericCellValue(); row_par[23] = renglon.getCell(23).getNumericCellValue(); row_par[24] = renglon.getCell(24).getNumericCellValue(); row_par[25] = renglon.getCell(25).getNumericCellValue(); row_par[26] = renglon.getCell(26).getNumericCellValue(); row_par[27] = renglon.getCell(27).getNumericCellValue(); row_par[28] = renglon.getCell(28).getNumericCellValue(); row_par[29] = renglon.getCell(29).getNumericCellValue(); row_par[30] = renglon.getCell(30).getNumericCellValue(); row_par[31] = renglon.getCell(31).getStringCellValue(); row_par[32] = renglon.getCell(32).getStringCellValue(); if (renglon.getCell(33) .getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC) row_par[33] = renglon.getCell(33).getNumericCellValue(); row_par[34] = renglon.getCell(34).getBooleanCellValue(); row_par[35] = renglon.getCell(35).getNumericCellValue(); row_par[36] = renglon.getCell(36).getNumericCellValue(); row_par[37] = renglon.getCell(37).getNumericCellValue(); row_par[38] = renglon.getCell(38).getNumericCellValue(); row_par[39] = renglon.getCell(39).getNumericCellValue(); row_par[40] = renglon.getCell(40).getBooleanCellValue(); row_par[41] = renglon.getCell(41).getNumericCellValue(); row_par[42] = renglon.getCell(42).getBooleanCellValue(); row_par[43] = renglon.getCell(43).getBooleanCellValue(); row_par[44] = renglon.getCell(44).getBooleanCellValue(); if (renglon.getCell(45) .getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC) row_par[45] = (int) renglon.getCell(45).getNumericCellValue(); if (renglon.getCell(46) .getCellType() != org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING) row_par[46] = renglon.getCell(46).getDateCellValue(); row_par[47] = (int) renglon.getCell(47).getNumericCellValue(); row_par[48] = renglon.getCell(48).getNumericCellValue(); row_par[49] = renglon.getCell(49).getStringCellValue(); t_p.addRow(row_par); } else r = 1; } } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(this, "No se pudo abrir el archivo"); } if (session != null) if (session.isOpen() == true) session.close(); } } }
From source file:xqt.adapters.csv.MSExcelDataAdapterHelper.java
@Override public LinkedHashMap<String, FieldInfo> getContinerSchema(SingleContainer container, Object... params) { if (isHeaderExternal(container)) { return super.getContinerSchema(container, params); } else {//from www . jav a2 s . c o m try { LinkedHashMap<String, FieldInfo> headers = new LinkedHashMap<>(); String baseContainerPath = String.valueOf(params[0]); //String columnDelimiter = String.valueOf(params[1]); String typeDelimiter = String.valueOf(params[2]); String unitDelimiter = String.valueOf(params[3]); String fileName = getCompleteSourceName(container, baseContainerPath); HeaderBuilder hb = new HeaderBuilder(); //XSSFWorkbook workbook2 = new XSSFWorkbook(fileName); InputStream inp = new FileInputStream(fileName); Workbook workbook = WorkbookFactory.create(inp); Sheet sheet = null; String sheetName = container.getContainerName(); if (sheetName != null && !sheetName.isEmpty()) { sheet = workbook.getSheet(sheetName); } if (sheet == null) { // sheetName is not valid or is an index sheet = workbook.getSheetAt(container.getContainerIndex()); } FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); if (sheet.rowIterator().hasNext()) { Row row = sheet.getRow(0); String[] cellvalues = RowBuilder.createRowArray(row, evaluator); int indexCount = 0; for (String cell : cellvalues) { if (cell != null && !cell.isEmpty()) { FieldInfo field = hb.convert(cell, typeDelimiter, unitDelimiter); field.index = indexCount; if (field.name != null && !field.name.isEmpty() && !headers.containsKey(field.name)) { headers.put(field.name, field); indexCount++; } } } } for (FieldInfo field : headers.values()) { field.conceptualDataType = getConceptualType(field.internalDataType); } return headers; } catch (IOException | InvalidFormatException ex) { LoggerHelper.logError( MessageFormat.format("Schema generation error for adapter: \'CSV\'. {0}", ex.getMessage())); return new LinkedHashMap<>(); } } }