List of usage examples for org.apache.poi.hssf.usermodel HSSFRow cellIterator
@Override
public Iterator<Cell> cellIterator()
From source file:include.excel_import.XlsValidator.java
License:Open Source License
/** * ???? /*ww w. j a va2 s . co m*/ * @return true: */ private boolean validateTitle(HSSFSheet sheet, String sheetName) { HSSFRow row = sheet.getRow((short) 0); if (row == null) { // message.append(",SHEET"); return false; } Iterator cells = row.cellIterator(); int size = 0; while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); size++; } for (int j = 0; j < size - 1; j++) { HSSFCell cell = row.getCell((short) j); if (cell == null) { return false; } else { if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING) { message.append(""/*sheetName*/).append(""); message.append(j + 1).append("?<br>"); return false; } if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { message.append(""/*sheetName*/).append("").append(j + 1).append("<br>"); return false; } } } return true; }
From source file:info.vancauwenberge.filedriver.filereader.xls.XlsFileReader.java
License:Mozilla Public License
public void openFile(File f) throws ReadException { trace.trace("Reading " + f.getAbsolutePath()); //If the file is existing, open and read it try {/*from w ww .j a v a2 s . c o m*/ FileInputStream fin = new FileInputStream(f); POIFSFileSystem poifs = new POIFSFileSystem(fin); wb = new HSSFWorkbook(poifs); } catch (IOException e) { throw new ReadException("Error while trying to read file " + f.getAbsolutePath(), e); } currentSheet = wb.getSheet(sheetName); //If we do not have a sheet with the given name, throw exception. if (currentSheet == null) throw new ReadException("No sheet with name " + sheetName + " found in file " + f.getAbsolutePath(), null); nextRowNumber = currentSheet.getFirstRowNum(); //If we have a aheader row, read it to get the actual schema if (hasHeader) { if (useHeaderNames) { HSSFRow row = currentSheet.getRow(nextRowNumber); //Last cell num is zero based => +1 String[] fields = new String[row.getLastCellNum()]; trace.trace("Number of fields:" + fields.length); Iterator<Cell> iter = row.cellIterator(); while (iter.hasNext()) { HSSFCell element = (HSSFCell) iter.next(); String value = element.getStringCellValue(); fields[element.getCellNum()] = value; } //We might have some nulls in the array. Default them. for (int i = 0; i < fields.length; i++) { String string = fields[i]; if (string == null) fields[i] = "_Unknown_" + i + "_"; } schema = fields; nextRowNumber++; } } }
From source file:javaexcel.LeyendoExcel.java
private void leeFicheroExcel(String fileName) { List cellDataList = new ArrayList(); try {/*from w w w. j ava2s.com*/ // POIFSFileSystem: ciclo de vida completo del sistema de archivos. // HSSFWorkbook: primer objeto construido. // HSSFSheet: hojas de clculo. // HSSFRow: fila de una hoja de clculo. // HSSFCell: celda en una fila de la hoja de clculo. FileInputStream fileInputStream = new FileInputStream(fileName); POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream); HSSFWorkbook libro = new HSSFWorkbook(fsFileSystem); HSSFSheet hoja = libro.getSheetAt(0); Iterator rowIterator = hoja.rowIterator(); while (rowIterator.hasNext()) { HSSFRow fila = (HSSFRow) rowIterator.next(); Iterator iterator = fila.cellIterator(); List cellTempList = new ArrayList(); while (iterator.hasNext()) { HSSFCell hssfCell = (HSSFCell) iterator.next(); cellTempList.add(hssfCell); } cellDataList.add(cellTempList); } } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } }
From source file:kupkb_experiments.ExperimentSpreadSheetParser.java
License:Open Source License
private void firstPass(HSSFRow row) { // first pass, looking for experiment ID and where the role and compound list are located Iterator i = row.cellIterator(); while (i.hasNext()) { Cell cell = (Cell) i.next();/*from ww w.j av a2 s . co m*/ // System.out.println(cell.getCellType()); cell.setCellType(HSSFCell.CELL_TYPE_STRING); // if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { // // } if (SpreadhseetVocabulary.isValid(cell.getStringCellValue())) { // put this in the map, and the value of the next cell if (keyValue.get(cell.getStringCellValue().toLowerCase()) == null) { keyValue.put(cell.getStringCellValue().toLowerCase(), new ArrayList<Cell>()); } keyValue.get(cell.getStringCellValue().toLowerCase()).add(cell); if (cell.getStringCellValue().toLowerCase().equals(SpreadhseetVocabulary.ROLE.getKeyName())) { roleCell.add(cell); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.GENE_SYMBOL.getKeyName())) { compoundListStart = row.getRowNum() + 1; compoundAttributeToColumn.put(SpreadhseetVocabulary.GENE_SYMBOL.getKeyName(), cell.getColumnIndex()); } // else if (cell.getStringCellValue().toLowerCase().equals(SpreadhseetVocabulary.GENE_SYMBOL.getKeyName()) && cell.getColumnIndex() != 9) { // compoundAttributeToColumn.put(SpreadhseetVocabulary.GENE_SYMBOL.getKeyName(), cell.getColumnIndex()); // } else if (cell.getStringCellValue().toLowerCase().equals(SpreadhseetVocabulary.GENE_ID.getKeyName()) && cell.getColumnIndex() != 9) { compoundAttributeToColumn.put(SpreadhseetVocabulary.GENE_ID.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.ENTREZ_GENE_ID.getKeyName()) && cell.getColumnIndex() != 9) { compoundAttributeToColumn.put(SpreadhseetVocabulary.ENTREZ_GENE_ID.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.UNIPROT_ID.getKeyName()) && cell.getColumnIndex() != 9) { compoundAttributeToColumn.put(SpreadhseetVocabulary.UNIPROT_ID.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.UNIPROT_ACC.getKeyName()) && cell.getColumnIndex() != 9) { compoundAttributeToColumn.put(SpreadhseetVocabulary.UNIPROT_ACC.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.HMDB_ID.getKeyName()) && cell.getColumnIndex() != 9) { compoundAttributeToColumn.put(SpreadhseetVocabulary.HMDB_ID.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.MICROCOSM.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.MICROCOSM.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.EXPRESSION_STRENGTH.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.EXPRESSION_STRENGTH.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.DIFFERENTIAL.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.DIFFERENTIAL.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.RATIO.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.RATIO.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase() .equals(SpreadhseetVocabulary.P_VALUE.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.P_VALUE.getKeyName(), cell.getColumnIndex()); } else if (cell.getStringCellValue().toLowerCase().equals(SpreadhseetVocabulary.FDR.getKeyName())) { compoundAttributeToColumn.put(SpreadhseetVocabulary.FDR.getKeyName(), cell.getColumnIndex()); } } } }
From source file:LogicModel.excel_Manage.java
public static List readExcel(String path) throws IOException { //String path1 = System.getProperty("user.home")+"/ejemploExcelJava.xls"; String path1 = path;// w w w. j ava 2 s .c o m List<List> sheetData = new ArrayList(); FileInputStream FlujoDeDatos = null; try { FlujoDeDatos = new FileInputStream(path1); if (FlujoDeDatos == null) { System.out.println("No se encuentra el archivo"); // No se encuentra la plantilla - aqui puedes enviar un mensaje de log o lo que quieras return null; } // Si todo ha ido bien HSSFWorkbook workbook = new HSSFWorkbook(FlujoDeDatos); // // Get the first sheet on the workbook. // HSSFSheet sheet = workbook.getSheetAt(0); // When we have a sheet object in hand we can iterator on // each sheet's rows and on each row's cells. We store the // data read on an ArrayList so that we can printed the // content of the excel to the console. // Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); //String cel=cell.toString(); // System.out.println("Aadiendo Celda: " + cell.toString()); data.add(cell); } sheetData.add(data); } } catch (IOException e) { e.printStackTrace(); } finally { if (FlujoDeDatos != null) { FlujoDeDatos.close(); } } //showExelData(sheetData); return sheetData; }
From source file:Login.HULogin.java
private void Login() throws Exception { String fileName = "/Users/cdp/Desktop/Aksh/TestFile.xls"; System.out.println(fileName); List sheetData = new ArrayList(); try (FileInputStream fis = new FileInputStream(fileName)) { HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); data.add(cell);// w ww. j a va 2s. c om } sheetData.add(data); } } catch (IOException e) { } loginData(sheetData); }
From source file:Login.HULogin.java
private void Register() throws Exception { String fileName = "/Users/cdp/Desktop/Aksh/TestData/register.xls"; System.out.println(fileName); List sheetData = new ArrayList(); try (FileInputStream fis = new FileInputStream(fileName)) { HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); data.add(cell);//from ww w .j av a 2 s . co m } sheetData.add(data); } } catch (IOException e) { } UserData(sheetData); }
From source file:Login.ventas.fpagosvarios.java
private void readExcelFile(String fileName) { /**/* ww w.j a v a2 s. co m*/ * Create a new instance for cellDataList */ cellDataList = new ArrayList(); try { /** * Create a new instance for FileInputStream class */ FileInputStream fileInputStream = new FileInputStream(fileName); /** * Create a new instance for POIFSFileSystem class */ POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream); /* * Create a new instance for HSSFWorkBook Class */ HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem); HSSFSheet hssfSheet = workBook.getSheetAt(0); /** * Iterate the rows and cells of the spreadsheet * to get all the datas. */ Iterator rowIterator = hssfSheet.rowIterator(); rowIterator.next(); while (rowIterator.hasNext()) { HSSFRow hssfRow = (HSSFRow) rowIterator.next(); Iterator iterator = hssfRow.cellIterator(); List cellTempList = new ArrayList(); while (iterator.hasNext()) { HSSFCell hssfCell = (HSSFCell) iterator.next(); cellTempList.add(hssfCell); } cellDataList.add(cellTempList); } } catch (Exception ex) { System.out.println(ex); } }
From source file:model.bank.BankTransDAO.java
/** * Metodo que permiete importar las transacciones o movimientos de una cuenta bancaria * desde un archivo de excel emitido por el Banco Internacional * @param idBankAccount numero de id de la cuenta a la que se importaran los datos * @param fileXls <b>File</b> del archivo excel con los datos a importar * @param hacerArchivoLog/*ww w .j av a 2 s . co m*/ * @return el numero de registros agregados * @throws java.lang.ClassNotFoundException * @throws java.sql.SQLException */ public static int importExcel(int idBankAccount, File fileXls, boolean hacerArchivoLog) throws ClassNotFoundException, SQLException { int rowsImported = 0; DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Importando datos desde: " + fileXls.getAbsolutePath() + " - a idcuenta: " + idBankAccount); if (idBankAccount > 0 && fileXls != null) { int numRegImportados = 0; FileTxt archLog = null; if (fileXls.exists()) { // if (hacerArchivoLog) { // String nombreArchLog = fileXls.getAbsolutePath(); // nombreArchLog.replace(".xlsx", ".log"); // nombreArchLog.replace(".xls", ".log"); // archLog = new FileTxt(nombreArchLog); // DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Importando desde Excel. Archivo: " + archLog.getFile().getAbsolutePath() + "*******************"); // } try { // crear un stream POIFSFileSystem poiFS; poiFS = new POIFSFileSystem(new FileInputStream(fileXls)); // if (hacerArchivoLog) { // archLog.open(FileTxt.OpenMode.WRITE); // } // crear una hoja de excel HSSFWorkbook libro = new HSSFWorkbook(poiFS); HSSFSheet sheet = libro.getSheetAt(0); HSSFRow row; HSSFCell cell; Iterator itr = sheet.rowIterator(); // extraer la informacion a un arrayList int rowsCount = 0; BankTrans trans = new BankTrans(); while (itr.hasNext()) { // reviso fila por fila row = (HSSFRow) itr.next(); if (rowsCount >= 4) { // si la fila es la 4 o mayor importo los datos Iterator itc = row.cellIterator(); trans.setIdBankAccount(idBankAccount); trans.setValue(0.0); trans.setIdRegType(2); // <2> = registro importado int colCount = 0; double value = 0; while (itc.hasNext()) { // reviso celda por celda cell = (HSSFCell) itc.next(); // leo la informacion de la celda if (cell != null) { // si la celda no es nula switch (colCount) { case 0: // columna 0, nada break; case 1: // columna 1, fecha trans.setDate(DateTime.getStringToDateUtil(cell.getStringCellValue(), AppGlobal.getFormatDate())); break; case 2: // columna 2, tipo de transaccion String codTipo = cell.getStringCellValue().trim(); trans.setType(codTipo); Type btt = TypeDAO.get("banktranstypes", codTipo); if (btt != null) { trans.setIdType(btt.getId()); } else { String texto = "Tipo de transanccion bancaria no encontrada: " + codTipo + ". Creandola"; DknConsole.warning(Thread.currentThread().getStackTrace()[1].toString(), texto); VMessage.show(texto); btt = new Type(); btt.setCode(codTipo); btt.setName(codTipo); btt.setDescription(codTipo); btt.setActive(true); if (TypeDAO.update("banktranstypes", btt) > 0) { Type btt1 = TypeDAO.get("banktranstypes", codTipo); if (btt1 != null) { trans.setIdType(btt1.getId()); DknConsole.msg( Thread.currentThread().getStackTrace()[1].toString(), "Creada y utilizando el Tipo de transaccin bancaria id: " + btt1.getId() + " trans.id: " + trans.getIdType()); } } else { DknConsole.error( Thread.currentThread().getStackTrace()[1].toString(), "Tipo de transaccin bancaria No se pudo crear. Cdigo: " + codTipo); } } break; case 3: // columna 3, numero de transaccin trans.setNumber(cell.getStringCellValue().trim()); break; case 4: // columna 4, nada break; case 5: // columna 5, observaciones trans.setObservations(cell.getStringCellValue().trim()); break; case 6: // columna 6, nada break; case 7: // columna 7, valor debito if (cell.getNumericCellValue() > 0) { value = cell.getNumericCellValue(); value *= -1; trans.setValue(value); } break; case 8: // columna 8, valor credito if (cell.getNumericCellValue() > 0) { value = cell.getNumericCellValue(); trans.setValue(value); } break; default: break; } // fin del switch } // Fin celda nula colCount++; } // Fin while de celdas // System.out.println("Agregando: " + trans); String error = BankTransDAO.validate(trans); if (error == null) { if (BankTransDAO.update(trans) >= 0) { numRegImportados++; DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Agregado: " + trans.getNumber()); if (hacerArchivoLog) { archLog.writeLine("Agregado: " + trans); } } else { DknConsole.warning(Thread.currentThread().getStackTrace()[1].toString(), "NO agregado: " + trans.getNumber()); if (hacerArchivoLog) { archLog.writeLine("NO agregado: " + trans); } } } else { DknConsole.error(Thread.currentThread().getStackTrace()[1].toString(), "NO paso la validacion: " + error + " - " + trans.getNumber()); if (hacerArchivoLog) { archLog.writeLine("NO paso la validacion: " + error + " - " + trans); } } } rowsCount++; } // Fin while de filas rowsImported = numRegImportados; DknConsole.msg(Thread.currentThread().getStackTrace()[1].toString(), "Importacion completa. Total " + numRegImportados + " registros importados."); // grabar los datos en la base de datos } catch (FileNotFoundException ex) { Logger.getLogger(BankTransDAO.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException | ParseException ex) { Logger.getLogger(BankTransDAO.class.getName()).log(Level.SEVERE, null, ex); } finally { if (hacerArchivoLog) { try { archLog.close(); } catch (IOException ex) { Logger.getLogger(BankTransDAO.class.getName()).log(Level.SEVERE, null, ex); } } } // JOptionPane.showMessageDialog(this, AppConfig.IMPORTACION_TEXTO, AppConfig.IMPORTACION_TITULO, JOptionPane.INFORMATION_MESSAGE); } else { DknConsole.error(Thread.currentThread().getStackTrace()[1].toString(), "Archivo no existe."); } } else { DknConsole.error(Thread.currentThread().getStackTrace()[1].toString(), "Archivo o cuenta no indicadas."); } return rowsImported; }
From source file:net.intelliant.util.UtilImport.java
License:Open Source License
public static List<String> readExcelFirstRow(String excelFilePath, boolean isFirstRowHeader, int sheetIndex) throws FileNotFoundException, IOException { List<String> columnIndices = new ArrayList<String>(); File file = new File(excelFilePath); if (file != null && file.canRead()) { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); // HSSFSheet sheet = wb.getSheet(wb.getActiveSheetIndex()); HSSFSheet sheet = wb.getSheetAt(sheetIndex); if (sheet != null) { HSSFRow firstRow = sheet.getRow(sheet.getFirstRowNum()); if (firstRow != null) { firstRow.getPhysicalNumberOfCells(); Iterator<?> cells = firstRow.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); if (isFirstRowHeader) { if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { columnIndices.add(cell.toString()); } else { columnIndices.add("N/A - " + cell.getCellNum()); }// w w w . ja va 2s. co m } else { columnIndices.add(String.valueOf(cell.getCellNum())); } } } } } return columnIndices; }