List of usage examples for org.apache.poi.hssf.usermodel HSSFRow cellIterator
@Override
public Iterator<Cell> cellIterator()
From source file:org.goobi.managedbeans.ProcessBean.java
License:Open Source License
public void generateResultAsPdf() { FacesContext facesContext = FacesContextHelper.getCurrentFacesContext(); if (!facesContext.getResponseComplete()) { /*/*from w w w . ja va 2 s. c o m*/ * -------------------------------- 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(); SearchResultHelper sch = new SearchResultHelper(); HSSFWorkbook wb = sch.getResult(prepareSearchColumnData(), this.filter, sortList(), this.showClosedProcesses, this.showArchivedProjects); 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.A4.getHeight(), PageSize.A4.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(rowList.get(0).size()); table.setSpacingBefore(20); for (int i = 0; i < rowList.size(); i++) { List<HSSFCell> row = rowList.get(i); table.completeRow(); for (int j = 0; j < row.size(); j++) { HSSFCell myCell = row.get(j); String stringCellValue = myCell.toString(); table.addCell(stringCellValue); } } document.add(table); } document.close(); out.flush(); facesContext.responseComplete(); } catch (Exception e) { } } }
From source file:org.jlibrary.core.search.extraction.ExcelExtractor.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * //from w w w .j av a 2 s . c o m * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring */ protected String extractTableContent(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); StringBuilder result = new StringBuilder(4096); int numberOfSheets = excelWb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { HSSFSheet sheet = excelWb.getSheetAt(i); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { if ((excelWb.getSheetName(i) != null) && !excelWb.getSheetName(i).equals("")) { // append sheet name to content if (i > 0) { result.append("\n\n"); } result.append(excelWb.getSheetName(i).trim()); result.append(":\n\n"); } Iterator rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { HSSFRow row = (HSSFRow) rowIt.next(); if (row != null) { boolean hasContent = false; Iterator it = row.cellIterator(); while (it.hasNext()) { HSSFCell cell = (HSSFCell) it.next(); String text = null; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case HSSFCell.CELL_TYPE_NUMERIC: text = Double.toString(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } } catch (Exception e) { // ignore this cell } if ((text != null) && !text.equals("")) { result.append(text.trim()); result.append(' '); hasContent = true; } } if (hasContent) { // append a newline at the end of each row that has content result.append('\n'); } } } } } return result.toString(); }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * /*from www. j a v a2 s .co m*/ * @param mainSheet -- ? * @param subSheet -- ? * @param tempRow -- ????? * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet, int tempRow) { if (mainSheet == null || subSheet == null) return null; //?? if (!isAllowOut(mainSheet)) return mainSheet; //? int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; //? copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); //?? CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; //int k = 0; //? mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext();) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); /** * ??????orgcell = mainSheet.getRow(row).getCell(column); * ?? * ??orgcell.getCellStyle()????sheet?? * This Style does not belong to the supplied Workbook. * ?descell.getCellStyle().cloneStyleFrom(sourcell.getCellStyle());???excel * HSSFCellStyle cs = mainSheet.getWorkbook().createCellStyle(); * cs.cloneStyleFrom(sourcell.getCellStyle()); * descell.setCellStyle(cs);//excel? * tempRow???? */ //???????? int row = sourcell.getRowIndex(); if (tempRow > 0 && row > tempRow) { row = tempRow; } orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { //orgcell.getCellType()???0 descell.setCellType(HSSFCell.CELL_TYPE_STRING); //??? descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * ?PageSize?/* w w w .ja v a2 s. c o m*/ * @param sheet -- ? * @param startRow -- ???PageSize * @param rows -- ? * @return */ public static HSSFSheet insertSheetRow(HSSFSheet sheet, int startRow, int rows) { if (sheet == null) return null; sheet.shiftRows(startRow, sheet.getLastRowNum(), rows, true, false); HSSFCell sourcell = null, descell = null; HSSFRow sourow = sheet.getRow(startRow - 1); for (int i = 0; i < rows; i++) { HSSFRow descrow = sheet.createRow(startRow + i); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); descell.setCellType(sourcell.getCellType()); descell.setCellStyle(sourcell.getCellStyle()); } } //?? insertSheetRegions(sheet, startRow, rows); return sheet; }
From source file:org.kitodo.production.services.data.ProcessService.java
License:Open Source License
/** * Generate result as PDF./*from w w w. j a v a 2 s . c o m*/ * * @param filter * for generating search results */ public void generateResultAsPdf(String filter) throws DocumentException, IOException { FacesContext facesContext = FacesContext.getCurrentInstance(); if (!facesContext.getResponseComplete()) { ExternalContext response = prepareHeaderInformation(facesContext, "search.pdf"); try (OutputStream out = response.getResponseOutputStream()) { SearchResultGeneration sr = new SearchResultGeneration(filter, this.showClosedProcesses, this.showInactiveProjects); 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 rectangle = new Rectangle(PageSize.A3.getHeight(), PageSize.A3.getWidth()); PdfWriter.getInstance(document, out); document.setPageSize(rectangle); document.open(); if (!rowList.isEmpty()) { Paragraph paragraph = new Paragraph(rowList.get(0).get(0).toString()); document.add(paragraph); document.add(getPdfTable(rowList)); } document.close(); out.flush(); facesContext.responseComplete(); } } }
From source file:org.mcisb.excel.ExcelReader.java
License:Open Source License
/** * //from w w w . j a v a 2 s . co m * @param sheetName * @return List */ public List<Object> getColumnNames(String sheetName) { final List<Object> list = new ArrayList<>(); final HSSFRow row = getColumns(workbook.getSheet(sheetName)); for (Iterator<?> iterator = row.cellIterator(); iterator.hasNext();) { final Object o = iterator.next(); if (o instanceof HSSFCell) { final HSSFCell cell = (HSSFCell) o; list.add(getValue(cell)); } } return list; }
From source file:org.mcisb.excel.ExcelReader.java
License:Open Source License
/** * /* w w w. j av a2 s . c o m*/ * @param sheetName */ private void read(final String sheetName) { final HSSFSheet sheet = workbook.getSheet(sheetName); ExcelCell excelCell = null; HSSFRow row = null; int currentRowNumber = 0; while ((row = sheet.getRow(currentRowNumber)) != null) { for (Iterator<?> iterator = row.cellIterator(); iterator.hasNext();) { final Object o = iterator.next(); if (o instanceof HSSFCell) { final HSSFCell cell = (HSSFCell) o; final ExcelCell newExcelCell = new ExcelCell(getValue(cell), currentRowNumber, cell.getColumnIndex()); support.firePropertyChange(EXCEL_CELL, excelCell, newExcelCell); excelCell = newExcelCell; } } currentRowNumber++; } }
From source file:org.mule.routing.outbound.SimpleXlsSplitter.java
License:Open Source License
public Map<String, HSSFCell> getNextRow() { Map<String, HSSFCell> rowMap = new HashMap<String, HSSFCell>(); if (rowIterator.hasNext()) { HSSFRow row = (HSSFRow) rowIterator.next(); for (Iterator<HSSFCell> cit = (Iterator<HSSFCell>) row.cellIterator(); cit.hasNext();) { HSSFCell cell = cit.next();//from w w w . j ava2 s . com rowMap.put(Short.toString(cell.getCellNum()), cell); } } else { return null; } return rowMap; }
From source file:org.myorg.insertar.insertarSabanaTopComponent.java
public void leerArchivoExel() throws IOException { File abre = abrirArchivo();/*from w w w. ja v a 2 s .c o m*/ 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. // // 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); procesaDatosEntrada(sheetData); }
From source file:org.nuxeo.ecm.core.convert.plugins.text.extractors.XL2TextConverter.java
License:Apache License
@Override public BlobHolder convert(BlobHolder blobHolder, Map<String, Serializable> parameters) throws ConversionException { InputStream stream = null;/*from w w w. j a v a 2 s . c o m*/ StringBuffer sb = new StringBuffer(); try { stream = blobHolder.getBlob().getStream(); POIFSFileSystem fs = new POIFSFileSystem(stream); HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); appendTextFromCell(cell, sb); sb.append(CELL_SEP); } sb.append(ROW_SEP); } } return new SimpleCachableBlobHolder(Blobs.createBlob(sb.toString())); } catch (IOException e) { throw new ConversionException("Error during XL2Text conversion", e); } finally { if (stream != null) { try { stream.close(); } catch (IOException e) { log.error("Error while closing Blob stream", e); } } } }