List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:edu.ucsd.bioeng.coreplugin.tableImport.ui.PreviewTablePanel.java
License:Open Source License
/** * Load file and show preview./*w w w. ja v a 2 s . c o m*/ * * @param sourceURL * @param delimiters * @param renderer * renderer for this table. Can be null. * @param size * @param commentLineChar * TODO * @param startLine * TODO * @throws IOException */ public void setPreviewTable(URL sourceURL, List<String> delimiters, TableCellRenderer renderer, int size, final String commentLineChar, final int startLine) throws IOException { TableCellRenderer curRenderer = renderer; if ((commentLineChar != null) && (commentLineChar.trim().length() != 0)) this.commentChar = commentLineChar; else this.commentChar = null; /* * If rendrer is null, create default one. */ if (curRenderer == null) { curRenderer = new AttributePreviewTableCellRenderer(0, new ArrayList<Integer>(), AttributePreviewTableCellRenderer.PARAMETER_NOT_EXIST, AttributePreviewTableCellRenderer.PARAMETER_NOT_EXIST, null, TextFileDelimiters.PIPE.toString()); } /* * Reset current state */ for (int i = 0; i < tableTabbedPane.getTabCount(); i++) tableTabbedPane.removeTabAt(i); previewTables = new HashMap<String, JTable>(); TableModel newModel; fileTypeLabel.setVisible(true); if (sourceURL.toString().endsWith(SupportedFileType.EXCEL.getExtension()) || sourceURL.toString().endsWith(SupportedFileType.OOXML.getExtension())) { fileTypeLabel.setIcon(SPREADSHEET_ICON.getIcon()); fileTypeLabel.setText("Excel" + '\u2122' + " Workbook"); InputStream is = null; final Workbook wb; try { is = sourceURL.openStream(); wb = WorkbookFactory.create(is); } catch (InvalidFormatException e) { e.printStackTrace(); throw new IllegalArgumentException("Could not read Excel file. Maybe the file is broken?"); } finally { if (is != null) is.close(); } if (wb.getNumberOfSheets() == 0) throw new IllegalStateException("No sheet found in the workbook."); /* * Load each sheet in the workbook. */ logger.debug("# of Sheets = " + wb.getNumberOfSheets()); Sheet sheet = wb.getSheetAt(0); logger.debug("Sheet name = " + wb.getSheetName(0) + ", ROW = " + sheet.rowIterator().hasNext()); newModel = parseExcel(sourceURL, size, curRenderer, sheet, startLine); if (newModel.getRowCount() == 0) throw new IllegalStateException("No data found in the Excel sheet."); DataTypeUtil.guessTypes(newModel, wb.getSheetName(0), dataTypeMap); listDataTypeMap.put(wb.getSheetName(0), initListDataTypes(newModel)); addTableTab(newModel, wb.getSheetName(0), curRenderer); } else { if (isCytoscapeAttributeFile(sourceURL)) { fileTypeLabel.setText("Cytoscape Attribute File"); fileTypeLabel.setIcon(new ImageIcon(Cytoscape.class.getResource("images/icon48.png"))); newModel = parseText(sourceURL, size, curRenderer, null, 1); } else { fileTypeLabel.setText("Text File"); fileTypeLabel.setIcon(TEXT_FILE_ICON.getIcon()); newModel = parseText(sourceURL, size, curRenderer, delimiters, startLine); } String[] urlParts = sourceURL.toString().split("/"); final String tabName = urlParts[urlParts.length - 1]; DataTypeUtil.guessTypes(newModel, tabName, dataTypeMap); listDataTypeMap.put(tabName, initListDataTypes(newModel)); addTableTab(newModel, tabName, curRenderer); } if (getFileType() == FileTypes.GENE_ASSOCIATION_FILE) { fileTypeLabel.setText("Gene Association"); fileTypeLabel.setToolTipText("This is a fixed-format Gene Association file."); } loadFlag = true; }
From source file:examples.toHTML.ToHtml.java
License:Apache License
public void printStyles() { ensureOut();/*from www . j av a2s .c o m*/ // First, copy the base css BufferedReader in = null; try { FileInputStream f = new FileInputStream(path + File.separator + "resources" + File.separator + "html" + File.separator + "excelStyle.css"); in = new BufferedReader(new InputStreamReader(f)); /** in = new BufferedReader(new InputStreamReader( getClass().getResourceAsStream("excelStyle.css"))); */ String line; while ((line = in.readLine()) != null) { out.format("%s%n", line); } } catch (IOException e) { throw new IllegalStateException("Reading standard css", e); } finally { if (in != null) { try { in.close(); } catch (IOException e) { //noinspection ThrowFromFinallyBlock throw new IllegalStateException("Reading standard css", e); } } } // now add css for each used style Set<CellStyle> seen = new HashSet<CellStyle>(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) { CellStyle style = cell.getCellStyle(); if (!seen.contains(style)) { printStyle(style); seen.add(style); } } } } }
From source file:examples.toHTML.ToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();// w w w . jav a 2 s . c om out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); out.format(" <tr>%n"); out.format(" <td class=\"%s\">%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { // String content = " "; String attrs = ""; CellStyle style = null; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); //Set the value that is rendered for the cell //also applies the format CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; content = replaceUmlaut(content); if (content.equals("")) // content = " "; } } out.format(" <td class=\"%s %s\">%s</td>%n", styleName(style), attrs, content); } out.format(" </tr>%n"); } out.format("</tbody>%n"); }
From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java
License:EUPL
protected List<String> getDataFromColumn(Sheet sheet, int colnum) { final DataFormatter dataFormatter = new DataFormatter(); return stream(spliteratorUnknownSize(sheet.rowIterator(), ORDERED), false).map(row -> row.getCell(colnum)) .map(cell -> dataFormatter.formatCellValue(cell)).collect(toList()); }
From source file:gob.dp.sid.registro.controller.ImportarController.java
private void importar(File archivo) { List<Object[]> listaObjetos = new ArrayList<>(); try {/* w w w . ja v a 2 s . c o m*/ wb = WorkbookFactory.create(new FileInputStream(archivo)); Sheet hoja = wb.getSheetAt(0); Iterator filaIterator = hoja.rowIterator(); int indiceFila = -1; while (filaIterator.hasNext()) { indiceFila++; Row fila = (Row) filaIterator.next(); Iterator columnaIterator = fila.cellIterator(); Object[] listaColumna = new Object[7]; int indiceColumna = -1; while (columnaIterator.hasNext()) { indiceColumna++; Cell celda = (Cell) columnaIterator.next(); if (indiceFila == 0) { } else { if (celda != null && indiceColumna < 7) { switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //listaColumna[indiceColumna]= (int)Math.round(celda.getNumericCellValue()); listaColumna[indiceColumna] = celda.getDateCellValue(); break; case Cell.CELL_TYPE_STRING: listaColumna[indiceColumna] = celda.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: listaColumna[indiceColumna] = celda.getBooleanCellValue(); break; default: listaColumna[indiceColumna] = null; break; } } } } if (indiceFila != 0) { listaObjetos.add(listaColumna); } } cargarGestiones(listaObjetos); } catch (IOException | InvalidFormatException | EncryptedDocumentException e) { log.error("importar" + e); } }
From source file:gov.nih.nci.evs.app.neopl.XLStoXLSX.java
License:Open Source License
/** * @param args//from w w w . j ava 2s. c om * @throws InvalidFormatException * @throws IOException */ public static void run(String inputfile, String outputfile) throws IOException { InputStream in = new BufferedInputStream(new FileInputStream(inputfile)); try { Workbook wbIn = new HSSFWorkbook(in); File outFn = new File(outputfile); if (outFn.exists()) { outFn.delete(); } Workbook wbOut = new XSSFWorkbook(); int sheetCnt = wbIn.getNumberOfSheets(); for (int i = 0; i < sheetCnt; i++) { Sheet sIn = wbIn.getSheetAt(0); Sheet sOut = wbOut.createSheet(sIn.getSheetName()); Iterator<Row> rowIt = sIn.rowIterator(); while (rowIt.hasNext()) { Row rowIn = rowIt.next(); Row rowOut = sOut.createRow(rowIn.getRowNum()); Iterator<Cell> cellIt = rowIn.cellIterator(); while (cellIt.hasNext()) { Cell cellIn = cellIt.next(); Cell cellOut = rowOut.createCell(cellIn.getColumnIndex(), cellIn.getCellType()); switch (cellIn.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: cellOut.setCellValue(cellIn.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: cellOut.setCellValue(cellIn.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: cellOut.setCellFormula(cellIn.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: cellOut.setCellValue(cellIn.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cellOut.setCellValue(cellIn.getStringCellValue()); break; } { CellStyle styleIn = cellIn.getCellStyle(); CellStyle styleOut = cellOut.getCellStyle(); styleOut.setDataFormat(styleIn.getDataFormat()); } cellOut.setCellComment(cellIn.getCellComment()); // HSSFCellStyle cannot be cast to XSSFCellStyle // cellOut.setCellStyle(cellIn.getCellStyle()); } } } OutputStream out = new BufferedOutputStream(new FileOutputStream(outFn)); try { wbOut.write(out); } finally { out.close(); } } finally { in.close(); } }
From source file:hrytsenko.gscripts.io.XlsFiles.java
License:Apache License
/** * Loads record from file.// www . j a v a 2s . co m * * @param args * the named arguments. * * @return the list of records. * * @throws IOException * if file could not be loaded. */ public static List<Map<String, String>> loadXls(Map<String, ?> args) { Path path = NamedArgs.findPath(args); LOGGER.info("Load {}.", path.getFileName()); try (InputStream stream = Files.newInputStream(path); Workbook workbook = WorkbookFactory.create(stream);) { Sheet sheet = workbook.getSheetAt(0); List<Row> rows = ImmutableList.copyOf(sheet.rowIterator()); if (rows.isEmpty()) { return Collections.emptyList(); } List<String> columns = rows.stream().findFirst().map(XlsFiles::cellValues) .orElseThrow(() -> new IllegalStateException("Cannot read columns.")); return rows.stream().skip(1).map(toRecord(columns)).collect(Collectors.toList()); } catch (Exception exception) { throw new AppException(String.format("Could not load file %s.", path.getFileName()), exception); } }
From source file:IO.FILES.java
public void overWrite(Persona p, String texto) throws Exception { if (p == null) return;/*from w w w . j a v a 2 s. co m*/ Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta))); Sheet hoja = wb.getSheetAt(0); boolean encontrado = false; Row row = null; Iterator it = hoja.rowIterator(); while (it.hasNext()) { row = (Row) it.next(); if (row.getCell(0).getStringCellValue().equalsIgnoreCase(p.getId())) { encontrado = true; break; } } if (!encontrado) return; int aux = row.getRowNum(); hoja.removeRow(row); row = hoja.createRow(aux); CentroEducativo centro = p.getCentro(); Redaccion R = p.getRedaccion(); for (int i = 0; i < cols.length; i++) { Cell cell = row.createCell(i); switch (i) { case 0: cell.setCellValue(p.getId()); break; case 1: cell.setCellValue(p.getName()); break; case 2: cell.setCellValue(p.getLastName()); break; case 3: cell.setCellValue(p.getLastName2()); break; case 4: cell.setCellValue(p.getNivel()); break; case 5: cell.setCellValue(p.getEdad()); break; case 6: cell.setCellValue(p.isMale() ? "M" : "F"); break; case 7: cell.setCellValue(centro.getName()); break; case 8: cell.setCellValue(centro.getSiglas()); break; case 9: cell.setCellValue(centro.isPublic() ? "PUBLICO" : "PRIVADO"); break; case 10: cell.setCellValue(p.isCCA() ? "CCA" : "SCA"); break; case 11: cell.setCellValue(p.isCCA() ? p.getCCA_DETALLE() : "-"); break; case 12: cell.setCellValue(p.isAdecuacion() ? "SI" : "NO"); break; case 13: cell.setCellValue(p.isAdecuacion() ? modelo.Info.tipos[p.getTipo()] : "-"); break; case 14: cell.setCellValue(R.getUT()); break; case 15: cell.setCellValue(R.getCL()); break; case 16: cell.setCellValue(R.getPAL()); break; case 17: cell.setCellValue(R.getLPUT()); break; case 18: cell.setCellValue(R.getLPCL()); break; case 19: cell.setCellValue(R.getINSUB()); } } REDACCIONES.overWrite(wb, p, texto); save(wb); }
From source file:IO.FILES.java
public void write(Persona p, String texto) throws Exception { if (p == null) return;/*from www. j ava 2s . co m*/ Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta))); Sheet hoja = wb.getSheetAt(0); int inx = 0; Iterator it = hoja.rowIterator(); while (it.hasNext()) { inx++; it.next(); } Row row = hoja.createRow(inx); CentroEducativo centro = p.getCentro(); Redaccion R = p.getRedaccion(); for (int i = 0; i < cols.length; i++) { Cell cell = row.createCell(i); switch (i) { case 0: cell.setCellValue(p.getId()); break; case 1: cell.setCellValue(p.getName()); break; case 2: cell.setCellValue(p.getLastName()); break; case 3: cell.setCellValue(p.getLastName2()); break; case 4: cell.setCellValue(p.getNivel()); break; case 5: cell.setCellValue(p.getEdad()); break; case 6: cell.setCellValue(p.isMale() ? "M" : "F"); break; case 7: cell.setCellValue(centro.getName()); break; case 8: cell.setCellValue(centro.getSiglas()); break; case 9: cell.setCellValue(centro.isPublic() ? "PUBLICO" : "PRIVADO"); break; case 10: cell.setCellValue(p.isCCA() ? "CCA" : "SCA"); break; case 11: cell.setCellValue(p.isCCA() ? p.getCCA_DETALLE() : "-"); break; case 12: cell.setCellValue(p.isAdecuacion() ? "SI" : "NO"); break; case 13: cell.setCellValue(p.isAdecuacion() ? modelo.Info.tipos[p.getTipo()] : "-"); break; case 14: cell.setCellValue(R.getUT()); break; case 15: cell.setCellValue(R.getCL()); break; case 16: cell.setCellValue(R.getPAL()); break; case 17: cell.setCellValue(R.getLPUT()); break; case 18: cell.setCellValue(R.getLPCL()); break; case 19: cell.setCellValue(R.getINSUB()); } } REDACCIONES.write(wb, p, texto); save(wb); }
From source file:IO.FILES.java
public void remove(String id) throws Exception { try {// w ww .j a v a2s . com Workbook wb = WorkbookFactory.create(new FileInputStream(new File(ruta))); Sheet hoja = wb.getSheetAt(0); Iterator rows = hoja.rowIterator(); int i = 0; Persona p = null; while (rows.hasNext()) { Row row = (Row) rows.next(); if (row.getCell(0).getStringCellValue().equalsIgnoreCase(id)) { removeRow(hoja, row); REDACCIONES.remove(id, wb); save(wb); break; } i++; } } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) { Logger.getLogger(FILES.class.getName()).log(Level.SEVERE, null, ex); } }