List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet rowIterator
@Override
public Iterator<Row> rowIterator()
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//from w w w . jav 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.marketing.ContactListServices.java
License:Open Source License
@SuppressWarnings("unchecked") private static Map<String, Object> createRecords(GenericDelegator delegator, Locale locale, GenericValue mailerImportMapper, String userLoginId, String contactListId, String excelFilePath) throws GenericEntityException, FileNotFoundException, IOException { boolean transaction = false; int rowIndex = 0, totalCount = 0, failureCount = 0; String ofbizEntityName = mailerImportMapper.getString("ofbizEntityName"); String importMapperId = mailerImportMapper.getString("importMapperId"); String isFirstRowHeader = mailerImportMapper.getString("isFirstRowHeader"); Map<String, Map<Integer, String>> failureReport = new LinkedHashMap<String, Map<Integer, String>>(); Map<Integer, String> failureReportDetails = new LinkedHashMap<Integer, String>(); Map<String, Object> columnMappings = UtilImport.getActiveColumnMappings(delegator, importMapperId); HSSFWorkbook excelDocument = new HSSFWorkbook(new FileInputStream(excelFilePath)); HSSFSheet excelSheet = excelDocument.getSheetAt(0); Iterator<HSSFRow> excelRowIterator = excelSheet.rowIterator(); if (isFirstRowHeader.equalsIgnoreCase("Y")) { if (excelRowIterator.hasNext()) { excelRowIterator.next();//w w w . ja va 2 s . co m rowIndex++; } } while (excelRowIterator.hasNext()) { try { transaction = TransactionUtil.begin(); rowIndex++; totalCount++; failureReportDetails = new HashMap<Integer, String>(); GenericValue customEntityObj = insertIntoConfiguredCustomEntity(delegator, locale, userLoginId, ofbizEntityName, excelRowIterator.next(), columnMappings, failureReportDetails); String recipientId = customEntityObj.getString("recipientId"); createCLRecipientRelation(delegator, contactListId, recipientId); createCampaignLines(delegator, contactListId, recipientId, customEntityObj.getDate(dateOfOperationColumnName)); } catch (GenericEntityException gee) { Debug.logError(gee, MODULE); if (transaction) { TransactionUtil.rollback(); } failureReport.put(String.valueOf(rowIndex - 1), failureReportDetails); failureCount++; } catch (Exception e) { Debug.logError(e, MODULE); if (transaction) { TransactionUtil.rollback(); } failureReport.put(String.valueOf(rowIndex - 1), failureReportDetails); failureCount++; } finally { if (transaction) { TransactionUtil.commit(); } } } Map<String, Object> results = ServiceUtil.returnSuccess(); results.put("totalCount", totalCount); results.put("failureCount", failureCount); results.put("failureReport", failureReport); return results; }
From source file:net.intelliant.tests.ContactListImportTest.java
License:Open Source License
@SuppressWarnings("unchecked") private void imortedDataTest(String importMapperId, String contactListId, String excelPath) throws GenericEntityException, FileNotFoundException, IOException { GenericValue importMapper = delegator.findByPrimaryKey("MailerImportMapper", UtilMisc.toMap("importMapperId", importMapperId)); List<GenericValue> importMapperColumns = delegator.findByAnd("MailerImportColumnMapper", UtilMisc.toMap("importMapperId", importMapperId)); List<GenericValue> mailerRecipients = delegator.findByAnd("MailerRecipientAndContactListView", UtilMisc.toMap("contactListId", contactListId), UtilMisc.toList("recipientListId")); HSSFWorkbook excelWorkbook = new HSSFWorkbook(new FileInputStream(excelPath)); HSSFSheet excelSheet = excelWorkbook.getSheetAt(0); Iterator<?> rows = excelSheet.rowIterator(); HSSFRow row = null;/*from w w w . j a v a2s .c o m*/ if (importMapper.get("isFirstRowHeader").equals("Y")) { rows.next(); } int counter = 0; Timestamp testTimeStamp = null; Timestamp getTimestamp = null; String testString = null; String getString = null; int testNumber = 0; int getNumber = 0; ModelReader reader = delegator.getModelReader(); ModelEntity modelEntity = reader.getModelEntity("MailerRecipient"); ModelField modelField = null; while (rows.hasNext()) { row = (HSSFRow) rows.next(); if (validateRow(row, importMapperColumns, modelEntity)) { GenericValue mailerRecipient = mailerRecipients.get(counter++); Debug.log("##### [Row] : " + toStringHssfRow(row)); Debug.log("##### [Entity] : " + mailerRecipient); for (GenericValue importMapperColumn : importMapperColumns) { short columnIndex = Short.valueOf(String.valueOf(importMapperColumn.get("importFileColIdx"))); String columnName = (String) importMapperColumn.get("entityColName"); modelField = modelEntity.getField(columnName); String columnType = modelField.getType(); HSSFCell cell = row.getCell(columnIndex); if (columnType.equals("date") || columnType.equals("date-time")) { testTimeStamp = new Timestamp(mailerRecipient.getDate(columnName).getTime()); getTimestamp = new Timestamp(cell.getDateCellValue().getTime()); assertEquals(testTimeStamp, getTimestamp); } else if (columnName.equals("numeric")) { testNumber = mailerRecipient.getInteger(columnName); getNumber = (int) cell.getNumericCellValue(); assertEquals(testNumber, getNumber); } else { testString = mailerRecipient.getString(columnName); getString = getStringData(cell); Debug.log(testString + " # " + getString); assertEquals(testString, getString); } } } } }
From source file:no.trank.openpipe.parse.ms.ExcelParser.java
License:Apache License
private String getText(final HSSFWorkbook doc) { StringBuilder text = new StringBuilder(); for (int sheetNumber = 0; sheetNumber < doc.getNumberOfSheets(); ++sheetNumber) { if (sheetNumber > 0) { text.append('\n'); }//from w w w. j a v a 2 s .c o m HSSFSheet sheet = doc.getSheetAt(sheetNumber); for (Iterator<?> rowIterator = sheet.rowIterator(); rowIterator.hasNext();) { HSSFRow row = (HSSFRow) rowIterator.next(); boolean firstCell = true; for (Iterator<?> cellIterator = row.cellIterator(); cellIterator.hasNext();) { HSSFCell cell = (HSSFCell) cellIterator.next(); String cellText = getCellText(cell); if (cellText != null) { if (!firstCell) { text.append(' '); } firstCell = false; text.append(cellText); } } if (!firstCell) { text.append('\n'); } } } return text.toString(); }
From source file:org.ActSrnv03.core.ResidencialTopComponent.java
public void leerArchivoExel() throws IOException { JFileChooser jfc = new JFileChooser(); jfc.showOpenDialog(this); File abre = jfc.getSelectedFile(); JOptionPane.showMessageDialog(null, "\nEl nombre del archivo es:" + abre, "AVISO", JOptionPane.WARNING_MESSAGE); this.nombre = abre; // An excel file name. You can create a file name with a full // path information. ////ww w.j a va2s . c om // String filename = "test.xls"; String filename = this.nombre.getAbsolutePath(); // // Create an ArrayList to store the data read from excel sheet. // List sheetData = new ArrayList(); FileInputStream fis = null; try { // // Create a FileInputStream that will be use to read the // excel file. // fis = new FileInputStream(filename); // // Create an excel workbook from the file system. // HSSFWorkbook workbook = new HSSFWorkbook(fis); // // 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(); // System.out.println("Aadiendo Celda: " + cell.toString()); data.add(cell); } sheetData.add(data); } } catch (IOException e) { e.printStackTrace(); } finally { if (fis != null) { fis.close(); } } // showExelData(sheetData); procesaCertificaciones(sheetData); }
From source file:org.amanzi.splash.importer.ExcelImporter.java
License:Open Source License
/** * Creates a child spreadsheet inside parent spreadsheet * * @param sheet sheet to import/* w w w . ja v a 2 s . com*/ * @param sheetName name of sheet * @param monitor monitor */ @SuppressWarnings(value = { "deprecation", "unchecked" }) private void createSheet(HSSFSheet sheet, String sheetName, Transaction transaction) { spreadsheetNode = null; spreadsheetName = sheetName; createSpreadsheet(rootSpreadsheet); try { Iterator<HSSFRow> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = rows.next(); //display row number in the console. LOGGER.debug("Row No.: " + row.getRowNum()); //once get a row its time to iterate through cells. Iterator<HSSFCell> cells = row.cellIterator(); int R = row.getRowNum(); if ((R % 20) == 0) { updateTransaction(transaction); } while (cells.hasNext()) { HSSFCell cell = cells.next(); LOGGER.debug("Cell No.: " + cell.getCellNum()); int C = cell.getCellNum(); /* * Now we will get the cell type and display the values * accordingly. */ switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { // cell type numeric. LOGGER.debug("===================================================="); LOGGER.debug("Numeric value: " + cell.getNumericCellValue()); LOGGER.debug("===================================================="); String def = Double.toString(cell.getNumericCellValue()); Cell c = new Cell(R, C, def, def, new CellFormat()); //TODO: interpet!!!!!! //Cell c = model.interpret(def, R, C); saveCell(c); break; } case HSSFCell.CELL_TYPE_STRING: { // cell type string. HSSFRichTextString richTextString = cell.getRichStringCellValue(); LOGGER.debug("===================================================="); LOGGER.debug("String value: " + richTextString.getString()); LOGGER.debug("===================================================="); Cell c = new Cell(R, C, richTextString.getString(), richTextString.getString(), new CellFormat()); saveCell(c); break; } case HSSFCell.CELL_TYPE_FORMULA: // cell type string. String cellFormula = "=" + cell.getCellFormula().toLowerCase(); Cell c = new Cell(R, C, cellFormula, cellFormula, new CellFormat()); //TODO: interpet!!!!!! //Cell c = model.interpret(def, R, C); saveCell(c); LOGGER.debug("===================================================="); LOGGER.debug("Formula value: " + cellFormula); LOGGER.debug("===================================================="); break; default: { // types other than String and Numeric. LOGGER.debug("Type not supported."); break; } } } } } finally { updateTransaction(transaction); } }
From source file:org.apache.cocoon.generation.HSSFGenerator.java
License:Apache License
/** * Writes out the workbook data as XML, without formatting information *///w w w . j a va2s. c om private void writeXML(HSSFWorkbook workbook) throws SAXException { this.contentHandler.startDocument(); start("Workbook"); start("SheetNameIndex"); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { start("SheetName"); data(workbook.getSheetName(i)); end("SheetName"); } end("SheetNameIndex"); start("Sheets"); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); start("Sheet"); start("Name"); data(workbook.getSheetName(i)); end("Name"); start("MaxCol"); data(Integer.toString(getMaxCol(sheet))); end("MaxCol"); start("MaxRow"); data(Integer.toString(sheet.getLastRowNum())); end("MaxRow"); if (formatting) { writeStyles(workbook, sheet); } start("Cells"); final Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { final HSSFRow row = (HSSFRow) rows.next(); final Iterator cells = row.cellIterator(); while (cells.hasNext()) { final HSSFCell cell = (HSSFCell) cells.next(); attribute("Row", Integer.toString(row.getRowNum())); attribute("Col", Short.toString(cell.getCellNum())); attribute("ValueType", getValueType(cell.getCellType())); start("Cell"); data(getValue(cell)); end("Cell"); } } end("Cells"); end("Sheet"); } end("Sheets"); end("Workbook"); this.contentHandler.endDocument(); }
From source file:org.apache.cocoon.generation.HSSFGenerator.java
License:Apache License
/** * Returns the max column index of the given sheet * @param sheet//from w w w .ja v a 2 s. com * @return the max column index */ private int getMaxCol(HSSFSheet sheet) { int max = -1; HSSFRow row = null; Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (HSSFRow) rows.next(); int lastNum = row.getLastCellNum(); if (lastNum > max) { max = lastNum; } } return max; }
From source file:org.apache.cocoon.generation.HSSFGenerator.java
License:Apache License
/** * Writes out the workbook data as XML, with formatting information */// w w w . j av a 2 s. c o m private void writeStyles(HSSFWorkbook workbook, HSSFSheet sheet) throws SAXException { start("Styles"); HSSFRow row = null; HSSFCell cell = null; Iterator cells = null; Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (HSSFRow) rows.next(); cells = row.cellIterator(); while (cells.hasNext()) { cell = (HSSFCell) cells.next(); attribute("startRow", Integer.toString(row.getRowNum())); attribute("endRow", Integer.toString(row.getRowNum())); attribute("startCol", Short.toString(cell.getCellNum())); attribute("endCol", Short.toString(cell.getCellNum())); start("StyleRegion"); HSSFCellStyle style = cell.getCellStyle(); attribute("HAlign", Integer.toString(style.getAlignment())); attribute("VAlign", Integer.toString(style.getVerticalAlignment())); attribute("WrapText", ((style.getWrapText()) ? "1" : "0")); attribute("Orient", Integer.toString(style.getRotation())); attribute("Indent", Integer.toString(style.getIndention())); attribute("Locked", ((style.getLocked()) ? "1" : "0")); attribute("Hidden", ((style.getHidden()) ? "1" : "0")); attribute("Fore", workbook.getCustomPalette().getColor(style.getFillForegroundColor()).getHexString()); attribute("Back", workbook.getCustomPalette().getColor(style.getFillBackgroundColor()).getHexString()); attribute("PatternColor", Integer.toString(style.getFillPattern())); // TODO attribute("Format", "General"); // TODO start("Style"); HSSFFont font = workbook.getFontAt(style.getFontIndex()); attribute("Unit", Short.toString(font.getFontHeightInPoints())); attribute("Bold", Short.toString(font.getBoldweight())); attribute("Italic", ((font.getItalic()) ? "1" : "0")); attribute("Unterline", Integer.toString(font.getUnderline())); attribute("StrikeThrough", ((font.getStrikeout()) ? "1" : "0")); start("Font"); data(font.getFontName()); end("Font"); end("Style"); end("StyleRegion"); } } end("Styles"); }
From source file:org.apache.jackrabbit.extractor.MsExcelTextExtractor.java
License:Apache License
/** * {@inheritDoc}/* w w w. jav a2s .co m*/ */ public Reader extractText(InputStream stream, String type, String encoding) throws IOException { CharArrayWriter writer = new CharArrayWriter(); try { POIFSFileSystem fs = new POIFSFileSystem(stream); HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: String num = Double.toString(cell.getNumericCellValue()).trim(); if (num.length() > 0) { writer.write(num + " "); } break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) { writer.write(text + " "); } break; } } } } return new CharArrayReader(writer.toCharArray()); } catch (RuntimeException e) { logger.warn("Failed to extract Excel text content", e); return new StringReader(""); } finally { stream.close(); } }