List of usage examples for org.apache.poi.hssf.usermodel HSSFRow cellIterator
@Override
public Iterator<Cell> cellIterator()
From source file:Controller.importarController.java
/** * funo para ler um arquivo em Excel//from www . j av a 2s.co m * * @param fileName * @param myInput * @return */ @SuppressWarnings("unchecked") public List ReadFile(String fileName, FileInputStream myInput) { List cellVectorHolder = new ArrayList(); try { //FileInputStream myInput = new FileInputStream(fileName); POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); HSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator rowIter = mySheet.rowIterator(); while (rowIter.hasNext()) { HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); List cellStoreVector = new ArrayList(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); cellStoreVector.add(myCell); } cellVectorHolder.add(cellStoreVector); } } catch (Exception e) { } return cellVectorHolder; }
From source file:database_lab1.ExcelToMySQL.java
public void ExcelFileReader(String filename) throws IOException, SQLException { FileInputStream fis = null;//from w ww . ja v a 2 s . c o m Scanner sc = new Scanner(System.in); try { fis = new FileInputStream(filename); String queryValue = (new File(filename).getName().replaceAll("(.xls)", "")); System.out.print("Please input the schema name:"); String schemaName = sc.next(); System.out.print("Please input the database username:"); String userName = sc.next(); System.out.print("Please input the database password:"); String password = sc.next(); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rowIter = sheet.rowIterator(); //variable that will use to find the first row int firstrow = 0, maxColumn = 0; //iterator for the row values while (rowIter.hasNext()) { HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); Vector<String> cellStoreVector = new Vector<String>(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); String cellvalue; //determines if cell value is string or numaric if (myCell.getCellType() == 1) cellvalue = myCell.getStringCellValue(); else cellvalue = (int) myCell.getNumericCellValue() + ""; //adds the value in the vector cellStoreVector.addElement(cellvalue); //prints out the cell valuse System.out.print(cellvalue + " "); } System.out.println(); //if the row is not the first row then, //insert the data in database if (firstrow != 0) { dbConnection db = new dbConnection("jdbc:mysql://localhost:3306/" + schemaName, userName, password); //sql comment String insert = "INSERT INTO " + queryValue + " VALUES (?,?,?,?,?,?);"; PreparedStatement ps = db.getConnection().prepareStatement(insert);//createStatement().executeUpdate(insert); for (int i = 1; i <= cellStoreVector.size(); i++) { ps.setString(i, cellStoreVector.get(i - 1)); } //executing the sql command ps.execute(); } firstrow++; } } catch (IOException e) { e.printStackTrace(); } finally { if (fis != null) { fis.close(); } } }
From source file:database_lab1.studentExcel.java
public void studentExcelFile(String filename) throws IOException, SQLException { FileInputStream fis = null;// w w w. jav a 2 s.c o m try { fis = new FileInputStream(filename); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rowIter = sheet.rowIterator(); int firstrow = 0; while (rowIter.hasNext()) { HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); Vector<String> cellStoreVector = new Vector<String>(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); String cellvalue; if (myCell.getCellType() == 1) cellvalue = myCell.getStringCellValue(); else cellvalue = (int) myCell.getNumericCellValue() + ""; cellStoreVector.addElement(cellvalue); System.out.print(cellvalue + " "); } System.out.println(); if (firstrow != 0) { dbConnection db = new dbConnection("jdbc:mysql://localhost:3306/database_lab1", "root", "password"); String insert = "INSERT INTO student VALUES (?,?,?,?,?,?);"; PreparedStatement ps = db.getConnection().prepareStatement(insert);//createStatement().executeUpdate(insert); ps.setString(1, cellStoreVector.get(0)); ps.setString(2, cellStoreVector.get(1)); ps.setString(3, cellStoreVector.get(2)); ps.setString(4, cellStoreVector.get(3)); ps.setString(5, cellStoreVector.get(4)); ps.setString(6, cellStoreVector.get(5)); Boolean rs = ps.execute(); } firstrow++; } } catch (IOException e) { e.printStackTrace(); } finally { if (fis != null) { fis.close(); } } }
From source file:de.sub.goobi.forms.ProzessverwaltungForm.java
License:Open Source License
/** * Generate result as PDF./*from ww w .j a v a 2 s. co m*/ */ public void generateResultAsPdf() { FacesContext facesContext = FacesContext.getCurrentInstance(); if (!facesContext.getResponseComplete()) { /* * Vorbereiten der Header-Informationen */ HttpServletResponse response = (HttpServletResponse) facesContext.getExternalContext().getResponse(); try { ServletContext servletContext = (ServletContext) facesContext.getExternalContext().getContext(); String contentType = servletContext.getMimeType("search.pdf"); response.setContentType(contentType); response.setHeader("Content-Disposition", "attachment;filename=\"search.pdf\""); ServletOutputStream out = response.getOutputStream(); SearchResultGeneration sr = new SearchResultGeneration(this.filter, this.showClosedProcesses, this.showArchivedProjects); HSSFWorkbook wb = sr.getResult(); List<List<HSSFCell>> rowList = new ArrayList<>(); HSSFSheet mySheet = wb.getSheetAt(0); Iterator<Row> rowIter = mySheet.rowIterator(); while (rowIter.hasNext()) { HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator<Cell> cellIter = myRow.cellIterator(); List<HSSFCell> row = new ArrayList<>(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); row.add(myCell); } rowList.add(row); } Document document = new Document(); Rectangle a4quer = new Rectangle(PageSize.A3.getHeight(), PageSize.A3.getWidth()); PdfWriter.getInstance(document, out); document.setPageSize(a4quer); document.open(); if (rowList.size() > 0) { Paragraph p = new Paragraph(rowList.get(0).get(0).toString()); document.add(p); PdfPTable table = new PdfPTable(9); table.setSpacingBefore(20); for (List<HSSFCell> row : rowList) { for (HSSFCell hssfCell : row) { // TODO aufhbschen und nicht toString() nutzen String stringCellValue = hssfCell.toString(); table.addCell(stringCellValue); } } document.add(table); } document.close(); out.flush(); facesContext.responseComplete(); } catch (Exception e) { logger.error(e); } } }
From source file:domain.Excel.java
public static void leerArchivoSesion(String archivoSesion) { List sheetData = new ArrayList(); FileInputStream fis = null; try {//from w w w . j av a 2 s. com fis = new FileInputStream(archivoSesion); 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); } sheetData.add(data); } } catch (FileNotFoundException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } finally { if (fis != null) { try { fis.close(); } catch (IOException ex) { Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex); } } } showExelData(sheetData); }
From source file:edu.ku.brc.specify.utilapps.BuildSampleDatabase.java
License:Open Source License
/** * @param treeDef/*from w w w .ja va2s. c o m*/ * @return */ public Geography convertGeographyFromXLS(final GeographyTreeDef treeDef) { frame.setDesc("Building Geography Tree..."); Hashtable<String, Geography> geoHash = new Hashtable<String, Geography>(); geoHash.clear(); String fileName = "Geography.xls"; File file = XMLHelper.getConfigDir("../demo_files/" + fileName); if (!file.exists()) { log.error("Couldn't file[" + file.getAbsolutePath() + "] checking the config dir"); file = XMLHelper.getConfigDir(fileName); if (!file.exists()) { file = new File("Specify/demo_files/" + fileName); } } if (file == null || !file.exists()) { log.error("Couldn't file[" + file.getAbsolutePath() + "]"); return null; } // setup the root Geography record (planet Earth) Geography earth = new Geography(); earth.initialize(); earth.setName(getResourceString("Earth")); earth.setFullName(earth.getName()); earth.setNodeNumber(1); earth.setHighestChildNodeNumber(1); earth.setRankId(0); earth.setDefinition(treeDef); GeographyTreeDefItem defItem = treeDef.getDefItemByRank(0); earth.setDefinitionItem(defItem); int counter = 0; try { startTx(); persist(earth); String[] cells = new String[4]; InputStream input = new FileInputStream(file); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); Iterator<?> rows = sheet.rowIterator(); int lastRowNum = sheet.getLastRowNum(); if (frame != null) { final int mx = lastRowNum; SwingUtilities.invokeLater(new Runnable() { public void run() { frame.setProcess(0, mx); } }); } while (rows.hasNext()) { if (counter == 0) { counter = 1; rows.next(); continue; } if (counter % 100 == 0) { if (frame != null) frame.setProcess(counter); log.info("Converted " + counter + " Geography records"); } HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cellsIter = row.cellIterator(); int i = 0; while (cellsIter.hasNext() && i < 4) { HSSFCell cell = (HSSFCell) cellsIter.next(); if (cell != null) { cells[i] = StringUtils.trim(cell.getRichStringCellValue().getString()); i++; } } // Sets nulls to unused cells for (int j = i; j < 4; j++) { cells[j] = null; } //System.out.println(); @SuppressWarnings("unused") Geography newGeo = convertGeographyRecord(cells[0], cells[1], cells[2], cells[3], earth); counter++; } } catch (Exception ex) { ex.printStackTrace(); } if (frame != null) frame.setProcess(counter); log.info("Converted " + counter + " Geography records"); frame.setDesc("Saving Geography Tree..."); frame.getProcessProgress().setIndeterminate(true); TreeHelper.fixFullnameForNodeAndDescendants(earth); earth.setNodeNumber(1); fixNodeNumbersFromRoot(earth); commitTx(); /*startTx(); TreeHelper.fixFullnameForNodeAndDescendants(earth); earth.setNodeNumber(1); fixNodeNumbersFromRoot(earth); printTree(earth, 0); saveTree(earth); commitTx();*/ log.info("Converted " + counter + " Stratigraphy records"); // set up Geography foreign key mapping for locality geoHash.clear(); return earth; }
From source file:edu.ku.brc.specify.utilapps.BuildSampleDatabase.java
License:Open Source License
/** * @param fileName/*from ww w . jav a2s. c om*/ * @return */ public HashSet<String> getColumnNamesFromXLS(final String fileName, final boolean doUserProvidedFile) { File file = TaxonLoadSetupPanel.getFileForTaxon(fileName, doUserProvidedFile); if (file == null) { return null; } HashSet<String> nameHash = new HashSet<String>(); try { String[] cells = new String[35]; InputStream input = new FileInputStream(file); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); Iterator<?> rows = sheet.rowIterator(); rows = sheet.rowIterator(); if (rows.hasNext()) { for (int i = 0; i < cells.length; i++) { cells[i] = null; } HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cellsIter = row.cellIterator(); while (cellsIter.hasNext()) { HSSFCell cell = (HSSFCell) cellsIter.next(); if (cell != null) { nameHash.add(StringUtils.trim(cell.getRichStringCellValue().getString())); } } } } catch (Exception ex) { ex.printStackTrace(); } return nameHash; }
From source file:edu.ku.brc.specify.utilapps.BuildSampleDatabase.java
License:Open Source License
/** * @param treeDef// w w w .j a v a 2 s . c o m * @param fileName * @param doUserProvidedFile * @return */ public Taxon convertTaxonFromXLS(final TaxonTreeDef treeDef, final String fileName, final boolean doUserProvidedFile) { Hashtable<String, Taxon> taxonHash = new Hashtable<String, Taxon>(); taxonHash.clear(); File file = TaxonLoadSetupPanel.getFileForTaxon(fileName, doUserProvidedFile); if (file == null) { return null; } Vector<TaxonTreeDefItem> rankedItems = new Vector<TaxonTreeDefItem>(); Hashtable<String, Boolean> colNames = new Hashtable<String, Boolean>(); for (TaxonTreeDefItem item : treeDef.getTreeDefItems()) { colNames.put(item.getName().toLowerCase(), true); rankedItems.add(item); } Collections.sort(rankedItems, new Comparator<TaxonTreeDefItem>() { @Override public int compare(TaxonTreeDefItem o1, TaxonTreeDefItem o2) { return o1.getRankId().compareTo(o2.getRankId()); } }); Connection conn = null; Statement stmt = null; TaxonTreeDefItem rootTreeDefItem = rankedItems.get(0); Set<Taxon> rootKids = rootTreeDefItem.getTreeEntries(); Taxon root = rootKids.iterator().next(); Vector<Pair<String, Integer>> nodeList = new Vector<Pair<String, Integer>>(); Pair<String, Integer> rootNode = new Pair<String, Integer>(root.getName(), root.getId()); nodeList.add(rootNode); int counter = 0; int numDataCols = 0; try { startTx(); for (TaxonTreeDefItem item : treeDef.getTreeDefItems()) { persist(item); } persist(root); commitTx(); String[] cells = new String[35]; String[] header = new String[35]; InputStream input = new FileInputStream(file); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); Iterator<?> rows = sheet.rowIterator(); int lastRowNum = sheet.getLastRowNum(); if (frame != null) { final int mx = lastRowNum; SwingUtilities.invokeLater(new Runnable() { public void run() { frame.setProcess(0, mx); } }); } conn = DBConnection.getInstance().createConnection(); //conn.setAutoCommit(false); stmt = conn.createStatement(); rows = sheet.rowIterator(); while (rows.hasNext()) { for (int i = 0; i < cells.length; i++) { cells[i] = null; } if (counter == 0) { HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cellsIter = row.cellIterator(); int i = 0; while (cellsIter.hasNext()) { HSSFCell cell = (HSSFCell) cellsIter.next(); if (cell != null) { cells[i] = getXLSCellValueAsStr(cell); header[i] = cells[i]; i++; } } for (i = 0; i < cells.length; i++) { if (cells[i] == null) break; if (colNames.get(cells[i].toLowerCase()) != null) { numDataCols = i + 1; } else { for (String key : colNames.keySet()) { System.err.println("key[" + key + "]"); } System.err.println("Not Found: [" + cells[i].toLowerCase() + "]"); break; } } loadIndexes(cells); counter = 1; for (String hdr : header) { if (hdr == null) break; int inx = 0; for (TaxonTreeDefItem item : rankedItems) { if (hdr.equalsIgnoreCase(item.getName())) { log.debug("Header: " + hdr + " -> " + inx); taxonIndexes.put(hdr, inx); item.setIsInFullName(item.getRankId() >= TaxonTreeDef.GENUS); } else { log.debug("Header: " + hdr + " -> skipped."); } inx++; } } continue; } if (counter % 100 == 0) { if (frame != null) frame.setProcess(counter); //log.info("Converted " + counter + " of "+lastRowNum+" Taxon records"); } HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cellsIter = row.cellIterator(); int i = 0; while (cellsIter.hasNext() && i < cells.length) { HSSFCell cell = (HSSFCell) cellsIter.next(); if (cell != null) { cells[i] = getXLSCellValueAsStr(cell); i++; } } convertTaxonNodes(conn, stmt, header, cells, numDataCols, rootNode, nodeList, rankedItems, root.getDefinition().getId()); counter++; } stmt.executeUpdate( "UPDATE taxon SET IsAccepted = true WHERE IsAccepted IS NULL and AcceptedID IS NULL AND TaxonTReeDefID = " + treeDef.getId()); // Clear all GUIDs in Taxon. stmt.executeUpdate("UPDATE taxon SET GUID = NULL WHERE TaxonTreeDefID = " + treeDef.getId()); conn.close(); input.close(); if (frame != null) frame.setProcess(lastRowNum); root = (Taxon) session.createQuery("FROM Taxon WHERE id = " + root.getId()).list().get(0); } catch (Exception ex) { ex.printStackTrace(); edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BackupServiceFactory.class, ex); } finally { try { if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } catch (Exception ex) { ex.printStackTrace(); } } if (frame != null) { frame.setDesc("Saving Taxon Tree..."); frame.getProcessProgress().setIndeterminate(true); } NodeNumberer<Taxon, TaxonTreeDef, TaxonTreeDefItem> nodeNumberer = new NodeNumberer<Taxon, TaxonTreeDef, TaxonTreeDefItem>( root.getDefinition()); nodeNumberer.doInBackground(); //startTx(); //TreeHelper.fixFullnameForNodeAndDescendants(root); //root.setNodeNumber(1); //fixNodeNumbersFromRoot(root); //commitTx(); log.info("Converted " + counter + " Taxon records"); // set up Taxon foreign key mapping for locality taxonHash.clear(); return root; }
From source file:edu.ku.brc.specify.utilapps.BuildSampleDatabase.java
License:Open Source License
/** * @param treeDef//w ww . j a v a 2 s .c om * @return */ public GeologicTimePeriod convertChronoStratFromXLS(final GeologicTimePeriodTreeDef treeDef, final Agent userAgent) { startTx(); GeologicTimePeriodTreeDefItem root = createGeologicTimePeriodTreeDefItem(null, treeDef, "Root", 0); GeologicTimePeriodTreeDefItem era = createGeologicTimePeriodTreeDefItem(root, treeDef, "Erathem/Era", 100); GeologicTimePeriodTreeDefItem period = createGeologicTimePeriodTreeDefItem(era, treeDef, "System/Period", 200); GeologicTimePeriodTreeDefItem series = createGeologicTimePeriodTreeDefItem(period, treeDef, "Series/Epoch", 300); @SuppressWarnings("unused") GeologicTimePeriodTreeDefItem member = createGeologicTimePeriodTreeDefItem(series, treeDef, "Stage/Age", 400); persist(root); commitTx(); series.setIsInFullName(true); frame.setDesc("Building ChronoStratigraphy Tree..."); Hashtable<String, GeologicTimePeriod> chronoHash = new Hashtable<String, GeologicTimePeriod>(); chronoHash.clear(); String fileName = "chronostrat_tree.xls"; File file = XMLHelper.getConfigDir("../demo_files/" + fileName); if (!file.exists()) { log.error("Couldn't file[" + file.getAbsolutePath() + "] checking the config dir"); file = XMLHelper.getConfigDir(fileName); if (!file.exists()) { file = new File("Specify/demo_files/" + fileName); } } if (file == null || !file.exists()) { log.error("Couldn't file[" + file.getAbsolutePath() + "]"); return null; } // setup the root ChronoStrat record (planet Earth) GeologicTimePeriod rootNode = new GeologicTimePeriod(); rootNode.initialize(); rootNode.setName(getResourceString("Root")); rootNode.setFullName(rootNode.getName()); rootNode.setRankId(0); rootNode.setDefinition(treeDef); rootNode.setDefinitionItem(root); rootNode.setCreatedByAgent(userAgent); int counter = 0; try { startTx(); persist(rootNode); String[] cells = new String[4]; InputStream input = new FileInputStream(file); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); Iterator<?> rows = sheet.rowIterator(); int lastRowNum = sheet.getLastRowNum(); if (frame != null) { final int mx = lastRowNum; SwingUtilities.invokeLater(new Runnable() { public void run() { frame.setProcess(0, mx); } }); } while (rows.hasNext()) { if (counter == 0) { counter = 1; continue; } if (counter % 100 == 0) { if (frame != null) frame.setProcess(counter); log.info("Converted " + counter + " ChronoStrat records"); } HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cellsIter = row.cellIterator(); int i = 0; while (cellsIter.hasNext() && i < 4) { HSSFCell cell = (HSSFCell) cellsIter.next(); if (cell != null) { cells[i] = StringUtils.trim(cell.getRichStringCellValue().getString()); i++; } } for (int j = i; j < 4; j++) { cells[j] = null; } //System.out.println(); @SuppressWarnings("unused") GeologicTimePeriod newGeo = convertChronoStratRecord(cells[0], cells[1], cells[2], cells[3], rootNode, userAgent); counter++; } input.close(); } catch (Exception ex) { ex.printStackTrace(); } if (frame != null) frame.setProcess(counter); log.info("Converted " + counter + " ChronoStrat records"); TreeHelper.fixFullnameForNodeAndDescendants(rootNode); rootNode.setNodeNumber(1); fixNodeNumbersFromRoot(rootNode); commitTx(); log.info("Converted " + counter + " Stratigraphy records"); // set up ChronoStrat foreign key mapping for locality chronoHash.clear(); return rootNode; }
From source file:Excel.LeerExcel.java
public void leerExcel1(String fileName) throws SQLException { tra = new ConeccionLocal(); List cellDataList = new ArrayList(); try {/*from w ww . jav a 2 s . c o m*/ /** * 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(); 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 e) { e.printStackTrace(); } /** * Call the printToConsole method to print the cell data in the * console. */ printToConsole(cellDataList); }