List of usage examples for org.apache.poi.hssf.usermodel HSSFRow cellIterator
@Override
public Iterator<Cell> cellIterator()
From source file:ch.astina.hesperid.util.jasper.JasperExcelStreamResponse.java
License:Apache License
@Override public void exportReportToStream(JasperPrint jasperPrint, OutputStream outputStream) throws Exception { JRXlsExporter exporter = new JRXlsExporter(); ByteArrayOutputStream baos = new ByteArrayOutputStream(); exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint); exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, baos); exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, this.removeEmptySpaceBetweenRows); exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, this.removeEmptySpaceBetweenColumns); exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, this.printWhitePageBackground); exporter.setParameter(JRXlsExporterParameter.IS_FONT_SIZE_FIX_ENABLED, this.fixFontSize); exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_GRAPHICS, this.ignoreGraphics); exporter.exportReport();// w w w . ja va 2 s. c om HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(baos.toByteArray())); workbook.getSheetAt(0).setAutobreaks(true); workbook.getSheetAt(0).getPrintSetup().setFitHeight((short) jasperPrint.getPages().size()); workbook.getSheetAt(0).getPrintSetup().setFitWidth((short) 1); SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy"); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); for (Integer x = 0; x < workbook.getSheetAt(0).getPhysicalNumberOfRows(); x++) { HSSFRow row = workbook.getSheetAt(0).getRow(x); Iterator<Cell> ci = row.cellIterator(); Cell c = null; Date d = null; while (ci.hasNext()) { c = ci.next(); try { d = sdf.parse(c.getStringCellValue().trim()); c.setCellValue(d); c.setCellStyle(cellStyle); } catch (Exception e) { } } } workbook.write(outputStream); }
From source file:com.afrisoftech.lib.ExportData.java
public static Vector read(String fileName) { Vector cellVectorHolder = new Vector(); try {/* w w w. java 2 s . co m*/ 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(); Vector cellStoreVector = new Vector(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); //Object obj=myCell.getStringCellValue(); System.out.print(myCell.getCellType() + " -"); if (myCell.getCellType() == 0) { cellStoreVector.addElement(myCell.getNumericCellValue()); } else if (myCell.getCellType() == 1) { cellStoreVector.addElement(myCell.getStringCellValue()); } //cellStoreVector.addElement(myCell.getStringCellValue()); } System.out.println(); cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { e.printStackTrace(); } return cellVectorHolder; }
From source file:com.alkacon.opencms.excelimport.CmsExcelContent.java
License:Open Source License
/** * Creates mapping between excel column names set by user and excel column names from excel internal.<p> * //from w ww. ja v a 2 s.com * @param in the document input stream * * @throws IOException if something goes wring */ private void readExcelColumnMappings(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); HSSFSheet sheet = excelWb.getSheetAt(0); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { HSSFRow firstRow = sheet.getRow(0); // loop over all columns in first excel row Iterator rowIter = firstRow.cellIterator(); while (rowIter.hasNext()) { // get cell HSSFCell cell = (HSSFCell) rowIter.next(); if (cell != null) { // get user column name String userColName = cell.getStringCellValue(); // get excel column name int excelColName = cell.getCellNum(); CmsExcelColumn excelCol = new CmsExcelColumn(userColName, excelColName); m_colNames.put(userColName, new Integer(excelColName)); m_colContents.put(new Integer(excelColName), excelCol); } } } }
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * Returns {@code true} iff the specified row does not exist or has only blank cells. * @param row the target row//from w w w .ja va2 s . c o m * @return {@code true} if is empty */ private boolean isEmpty(HSSFRow row) { if (row == null) { return true; } for (Iterator<Cell> iter = row.cellIterator(); iter.hasNext();) { if (iter.next().getCellType() != Cell.CELL_TYPE_BLANK) { return false; } } return true; }
From source file:com.bayareasoftware.chartengine.ds.ExcelDataSource.java
License:Apache License
/** * work around bug with named cells/*from w w w .j a v a2 s . co m*/ * */ private static void rewriteFormulas(final HSSFWorkbook workbook) { //if (true) return; // build up a cache of names // this is just an easy way of fetching the HSSFName based on the string // representation of the name final Map<String, HSSFName> nameCache = new HashMap<String, HSSFName>(workbook.getNumberOfNames()); for (int i = 0; i < workbook.getNumberOfNames(); i++) { final HSSFName name = workbook.getNameAt(i); nameCache.put(name.getNameName(), name); } // remove all the sheet names from the name references, having the sheet // names around messes up the formulas for (int i = 0; i < workbook.getNumberOfSheets(); i++) { nameCache.remove(workbook.getSheetName(i)); } //p("Names: " + nameCache.keySet()); // loop over all the cells and rewrite the formula ones for (int sheetCount = 0; sheetCount < workbook.getNumberOfSheets(); sheetCount++) { final HSSFSheet sheet = workbook.getSheetAt(sheetCount); for (final Iterator rowIterator = sheet.rowIterator(); rowIterator.hasNext();) { final HSSFRow row = (HSSFRow) rowIterator.next(); for (final Iterator cellIterator = row.cellIterator(); cellIterator.hasNext();) { final HSSFCell cell = (HSSFCell) cellIterator.next(); if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { String formula = cell.getCellFormula(); for (final String name : nameCache.keySet()) { final Pattern pattern = Pattern.compile("(\\W|^)" + name + "(\\W|$)", Pattern.CASE_INSENSITIVE); final HSSFName hssfName = nameCache.get(name); formula = pattern.matcher(formula) .replaceAll("$1" + hssfName.getReference().replace("$", "\\$") + "$2"); } //p("Resetting Cell (" + cell.toString() // + ") Formula:" + formula); cell.setCellFormula(formula); } // end if } // end for } // end for } // end for }
From source file:com.bayareasoftware.chartengine.ds.ExcelDataStream.java
License:Apache License
List<String[]> getRawData(int maxRows) { List<String[]> ret = new ArrayList<String[]>(); int last = sheet.getLastRowNum(); int first = sheet.getFirstRowNum(); for (int i = first; i < last && i < maxRows; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { ret.add(new String[0]); continue; }/*from w ww. j a va 2 s . c om*/ Iterator<Cell> iter = (Iterator<Cell>) row.cellIterator(); int count = row.getLastCellNum() - row.getFirstCellNum(); String[] s = new String[count]; int j = 0; while (iter.hasNext() && j < count) { s[j] = getCellString(iter.next()); j++; } ret.add(s); /* positionRowIterator(i); String[] s = new String[rowData.length]; for (int j = 0; j < s.length; j++) { s[j] = this.getString(i + 1); } re t.add(s); */ } return ret; }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java
License:Apache License
private void fillColumnNames(HSSFRow titleRow) { Iterator tcs = titleRow.cellIterator(); for (int i = 1; i <= metadata.getColumnCount(); i++) { HSSFCell tc = null;//from ww w.j a va 2s .c om if (tcs.hasNext()) { tc = (HSSFCell) tcs.next(); } if (metadata.getColumnName(i) == null) { String name = getCellString(tc); if (name == null) { name = "col" + i; } name = StringUtil.trim(name); if (name == null) { name = "Column_" + i; } metadata.setColumnName(i, name); } } }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java
License:Apache License
private HSSFRow inferHeaderRow(int dataRowNum) { if (dataRowNum < 1) return null; HSSFRow titleRow = sheet.getRow(dataRowNum - 1); if (titleRow == null) return null; HSSFRow dataRow = sheet.getRow(dataRowNum); short titleCells = titleRow.getLastCellNum(); short dataCells = dataRow.getLastCellNum(); if (titleCells != dataCells) { /* expect columns in title row same # columns in first row of data */ //p("titleLastCell=" + titleCells + "/dataLastCell=" + dataCells); return null; }// w w w . ja v a 2s. c om if (true) { return titleRow; } //p("getTitleRow() iterating over " + titleCells + " cells"); Iterator tcs = titleRow.cellIterator(); Iterator dcs = dataRow.cellIterator(); while (tcs.hasNext()) { HSSFCell tc = (HSSFCell) tcs.next(); if (tc.getCellType() != HSSFCell.CELL_TYPE_STRING) return null; HSSFCell dc = (HSSFCell) dcs.next(); if (tc.getCellNum() != dc.getCellNum()) return null; } return titleRow; }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java
License:Apache License
private void loadStrings() { int last = sheet.getLastRowNum(); int first = sheet.getFirstRowNum(); DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); for (int i = 0/*first*/; i < last && i < maxrows; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { rawStrings.add(new String[0]); continue; }//from www . ja v a2 s .c o m int count;// = row.getLastCellNum() - row.getFirstCellNum(); count = row.getLastCellNum(); eval.setCurrentRow(row); if (count < 0) { rawStrings.add(new String[0]); continue; } Iterator<Cell> iter = row.cellIterator(); String[] s = new String[count]; while (iter.hasNext()) { Cell cell = iter.next(); //int col = cell.getCellNum(); int col = cell.getRowIndex(); if (col >= 0 && col < count) { s[col] = getCellString(cell, eval, fmt); } else { String msg = "cell at row=" + rawStrings.size() + " column=" + col + " is out of bounds."; throw new RuntimeException(msg); } } rawStrings.add(s); } }
From source file:com.codesnippets4all.jthunder.extension.plugins.input.excel.ExcelReaderPlugin.java
License:Apache License
@SuppressWarnings("rawtypes") private void readOLEbasedExcel(BufferedInputStream bufferedStream, List<Sheet> sheets) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(bufferedStream); int sheetCount = workbook.getNumberOfSheets(); for (int index = 0; index < sheetCount; index++) { HSSFSheet sheet = workbook.getSheetAt(index); Sheet s = new Sheet(); sheets.add(s);//from ww w. ja va 2 s . c o m int lastRowNumber = sheet.getLastRowNum(); for (int rowIndex = 0; rowIndex < lastRowNumber; rowIndex++) { HSSFRow row = sheet.getRow(rowIndex); Record record = new Record(); s.addRecord(record); Iterator it = row.cellIterator(); while (it.hasNext()) { record.addCellValue(it.next()); } } } }