List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getLastCellNum
@Override public short getLastCellNum()
From source file:org.olat.search.service.document.file.ExcelDocument.java
License:Apache License
@Override protected String readContent(final VFSLeaf leaf) throws IOException, DocumentException { BufferedInputStream bis = null; int cellNullCounter = 0; int rowNullCounter = 0; int sheetNullCounter = 0; try {//from ww w. jav a 2 s. c o m bis = new BufferedInputStream(leaf.getInputStream()); final StringBuilder content = new StringBuilder(bis.available()); final POIFSFileSystem fs = new POIFSFileSystem(bis); final HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int sheetNumber = 0; sheetNumber < workbook.getNumberOfSheets(); sheetNumber++) { final HSSFSheet sheet = workbook.getSheetAt(sheetNumber); if (sheet != null) { for (int rowNumber = sheet.getFirstRowNum(); rowNumber <= sheet.getLastRowNum(); rowNumber++) { final HSSFRow row = sheet.getRow(rowNumber); if (row != null) { for (int cellNumber = row.getFirstCellNum(); cellNumber <= row .getLastCellNum(); cellNumber++) { final HSSFCell cell = row.getCell(cellNumber); if (cell != null) { // if (cell.getCellStyle().equals(HSSFCell.CELL_TYPE_NUMERIC)) if (cell.getCellType() == Cell.CELL_TYPE_STRING) { content.append(cell.getStringCellValue()).append(' '); } } else { // throw new DocumentException(); cellNullCounter++; } } } else { rowNullCounter++; } } } else { sheetNullCounter++; } } if (log.isDebug()) { if ((cellNullCounter > 0) || (rowNullCounter > 0) || (sheetNullCounter > 0)) { log.debug("Read Excel content cell=null #:" + cellNullCounter + ", row=null #:" + rowNullCounter + ", sheet=null #:" + sheetNullCounter); } } return content.toString(); } catch (final Exception ex) { throw new DocumentException("Can not read XLS Content. File=" + leaf.getName()); } finally { if (bis != null) { bis.close(); } } }
From source file:org.openmicroscopy.shoola.util.file.SheetInfo.java
License:Open Source License
/** * Returns the last cell column written to in row rowIndex. * //w w w . j a v a2 s.c om * @param rowIndex The index of the row. * @return See above. */ int getMaxColumn(int rowIndex) { HSSFRow row = sheet.getRow(rowIndex); if (row == null) return 0; return row.getLastCellNum(); }
From source file:org.orbeon.oxf.processor.serializer.legacy.XLSSerializer.java
License:Open Source License
protected void readInput(final PipelineContext pipelineContext, ProcessorInput input, Config config, OutputStream outputStream) { try {/* w w w . j av a2 s . c om*/ Document dataDocument = readInputAsDOM4J(pipelineContext, INPUT_DATA); final DocumentWrapper wrapper = new DocumentWrapper(dataDocument, null, XPathCache.getGlobalConfiguration()); Document configDocument = readInputAsDOM4J(pipelineContext, INPUT_CONFIG); // Read template sheet String templateName = configDocument.getRootElement().attributeValue("template"); //String fileName = configDocument.getRootElement().attributeValue("filename"); InputStream templateInputStream = URLFactory.createURL(templateName).openStream(); final HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(templateInputStream)); final HSSFDataFormat dataFormat = workbook.createDataFormat(); templateInputStream.close(); int sheetIndex = 0; PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper, "/workbook/sheet", getLocationData()); List<Object> nodes = expr.evaluateToJavaReturnToPool(); for (Iterator i = nodes.iterator(); i.hasNext();) { final Element sheetElement = (Element) i.next(); HSSFSheet sheet = workbook.cloneSheet(0); workbook.setSheetName(sheetIndex + 1, sheetElement.attributeValue("name")); // Duplicate rows if we find a "repeat-row" in the config for (Iterator j = configDocument.selectNodes("/config/repeat-row").iterator(); j.hasNext();) { // Get info about row to repeat Element repeatRowElement = (Element) j.next(); final int rowNum = Integer.parseInt(repeatRowElement.attributeValue("row-num")); final String forEach = repeatRowElement.attributeValue("for-each"); HSSFRow templateRow = sheet.getRow(rowNum); int repeatCount = ((Double) sheetElement.selectObject("count(" + forEach + ")")).intValue(); // Move existing rows lower int lastRowNum = sheet.getLastRowNum(); for (int k = lastRowNum; k > rowNum; k--) { HSSFRow sourceRow = sheet.getRow(k); HSSFRow newRow = sheet.createRow(k + repeatCount - 1); XLSUtils.copyRow(workbook, newRow, sourceRow); } // Create rows, copying the template row for (int k = rowNum + 1; k < rowNum + repeatCount; k++) { HSSFRow newRow = sheet.createRow(k); XLSUtils.copyRow(workbook, newRow, templateRow); } // Modify the XPath expression on each row for (int k = rowNum; k < rowNum + repeatCount; k++) { HSSFRow newRow = sheet.getRow(k); for (short m = 0; m <= newRow.getLastCellNum(); m++) { HSSFCell cell = newRow.getCell(m); if (cell != null) { String currentFormat = dataFormat.getFormat(cell.getCellStyle().getDataFormat()); final Matcher matcher = FORMAT_XPATH.matcher(currentFormat); if (matcher.find()) { String newFormat = matcher.group(1) + "\"" + forEach + "[" + (k - rowNum + 1) + "]/" + matcher.group(2) + "\""; cell.getCellStyle().setDataFormat(dataFormat.getFormat(newFormat)); } } } } } // Set values in cells with an XPath expression XLSUtils.walk(dataFormat, sheet, new XLSUtils.Handler() { public void cell(HSSFCell cell, String sourceXPath, String targetXPath) { if (sourceXPath.charAt(0) == '/') sourceXPath = sourceXPath.substring(1); // Set cell value PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper.wrap(sheetElement), "string(" + sourceXPath + ")", getLocationData()); String newValue = (String) expr.evaluateSingleToJavaReturnToPoolOrNull(); if (newValue == null) { throw new OXFException("Nothing matches the XPath expression '" + sourceXPath + "' in the input document"); } try { cell.setCellValue(Double.parseDouble(newValue)); } catch (NumberFormatException e) { cell.setCellValue(newValue); } // Set cell format Object element = sheetElement.selectObject(sourceXPath); if (element instanceof Element) { // NOTE: We might want to support other properties here String bold = ((Element) element).attributeValue("bold"); if (bold != null) { HSSFFont originalFont = workbook.getFontAt(cell.getCellStyle().getFontIndex()); HSSFFont newFont = workbook.createFont(); XLSUtils.copyFont(newFont, originalFont); if ("true".equals(bold)) newFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cell.getCellStyle().setFont(newFont); } } } }); sheetIndex++; } workbook.removeSheetAt(0); // Write out the workbook workbook.write(outputStream); } catch (IOException e) { throw new OXFException(e); } }
From source file:org.orbeon.oxf.util.XLSUtils.java
License:Open Source License
private static void walk(boolean[][] merged, HSSFDataFormat dataFormat, HSSFRow row, Handler handler) { if (row != null) { for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) { HSSFCell cell = row.getCell((short) cellNum); if (cell != null && !merged[row.getRowNum()][cellNum]) { short dataFormatId = cell.getCellStyle().getDataFormat(); if (dataFormatId > 0) { String format = dataFormat.getFormat(dataFormatId); final Matcher matcher = FORMAT_XPATH.matcher(format); if (matcher.find()) { // Found XPath expression String xpath = matcher.group(1); int separtorPosition = xpath.indexOf('|'); String sourceXPath = separtorPosition == -1 ? xpath : xpath.substring(0, separtorPosition); String targetXPath = separtorPosition == -1 ? null : xpath.substring(separtorPosition + 1); handler.cell(cell, sourceXPath, targetXPath); }//from w w w. j ava 2 s.co m } } } } }
From source file:org.orbeon.oxf.util.XLSUtils.java
License:Open Source License
/** * Compute max number of columns/*from www . j av a2 s . c om*/ */ public static short getMaxCellNum(HSSFSheet sheet) { int lastRowNum = sheet.getLastRowNum(); short maxCellNum = 0; for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) { HSSFRow hssfRow = sheet.getRow(rowNum); if (hssfRow != null) { short lastCellNum = hssfRow.getLastCellNum(); if (lastCellNum > maxCellNum) maxCellNum = lastCellNum; } } return maxCellNum; }
From source file:org.orbeon.oxf.util.XLSUtils.java
License:Open Source License
public static void copyRow(HSSFWorkbook workbook, HSSFRow destination, HSSFRow source) { for (short i = 0; i <= source.getLastCellNum(); i++) { HSSFCell templateCell = source.getCell(i); if (templateCell != null) { HSSFCell newCell = destination.createCell(i); XLSUtils.copyCell(workbook, newCell, templateCell); }/*from ww w . ja va2s . c o m*/ } }
From source file:org.ramadda.util.XlsUtil.java
License:Apache License
/** * Convert excel to csv/* w w w . j a v a 2 s. c o m*/ * * @param filename excel file * @param skipToFirstNumeric _more_ * @param sdf If non null then use this to format any date cells * * @return csv * * @throws Exception On badness */ public static String xlsToCsv(String filename) { try { StringBuffer sb = new StringBuffer(); InputStream myxls = IOUtil.getInputStream(filename, XlsUtil.class); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); boolean seenNumber = false; for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) { HSSFRow row = sheet.getRow(rowIdx); if (row == null) { continue; } short firstCol = row.getFirstCellNum(); for (short col = firstCol; col < row.getLastCellNum(); col++) { HSSFCell cell = row.getCell(col); if (cell == null) { break; } String value = cell.toString(); if (col > firstCol) { sb.append(","); } sb.append(clean(value)); } sb.append("\n"); } return sb.toString(); } catch (Exception exc) { throw new RuntimeException(exc); } }
From source file:org.sakaiproject.tool.gradebook.ui.SpreadsheetUploadBean.java
License:Educational Community License
private String fromHSSFRowtoCSV(HSSFRow row) { StringBuffer csvRow = new StringBuffer(); int l = row.getLastCellNum(); for (int i = 0; i < l; i++) { HSSFCell cell = row.getCell((short) i); String cellValue = ""; if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { cellValue = ""; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { cellValue = "\"" + cell.getStringCellValue() + "\""; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { double value = cell.getNumericCellValue(); cellValue = getNumberFormat().format(value); cellValue = "\"" + cellValue + "\""; }//from w w w .j a v a 2 s .c om csvRow.append(cellValue); if (i < l) { csvRow.append(getCsvDelimiter().toCharArray()[0]); } } return csvRow.toString(); }
From source file:org.yccheok.jstock.file.Statements.java
License:Open Source License
/** * Construct Statements based on given Excel File. * * @param file Given Excel File//from w ww. j av a 2 s . com * @return the List of constructed Statements. Empty list if fail. */ public static List<Statements> newInstanceFromExcelFile(File file) { FileInputStream fileInputStream = null; final List<Statements> statementsList = new ArrayList<Statements>(); try { fileInputStream = new FileInputStream(file); final POIFSFileSystem fs = new POIFSFileSystem(fileInputStream); final HSSFWorkbook wb = new HSSFWorkbook(fs); final int numberOfSheets = wb.getNumberOfSheets(); for (int k = 0; k < numberOfSheets; k++) { final HSSFSheet sheet = wb.getSheetAt(k); final int startRow = sheet.getFirstRowNum(); final int endRow = sheet.getLastRowNum(); // If there are 3 rows, endRow will be 2. // We must have at least 2 rows. (endRow = 1) if (startRow != 0 || endRow <= startRow) { continue; } final HSSFRow row = sheet.getRow(startRow); if (row == null) { continue; } final int startCell = row.getFirstCellNum(); final int endCell = row.getLastCellNum(); // If there are 2 cols, endCell will be 2. // We must have at least 1 col. (endCell = 1) if (startCell != 0 || endCell <= startCell) { continue; } final List<String> types = new ArrayList<String>(); for (int i = startCell; i < endCell; i++) { final HSSFCell cell = row.getCell(i); if (cell == null) { continue; } // Exception may be thrown here, as cell may be numerical value. final String type = cell.getRichStringCellValue().getString(); if (type != null) { types.add(type); } } if (types.isEmpty()) { continue; } if (types.size() != (endCell - startCell)) { continue; } final Statement.What what = Statement.what(types); Statements s = new Statements(what.type, what.guiBundleWrapper); for (int i = startRow + 1; i <= endRow; i++) { final HSSFRow r = sheet.getRow(i); if (r == null) { continue; } final List<Atom> atoms = new ArrayList<Atom>(); for (int j = startCell; j < endCell; j++) { final HSSFCell cell = r.getCell(j); if (cell == null) { continue; } Object value = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { final HSSFRichTextString richString = cell.getRichStringCellValue(); if (richString != null) { value = richString.getString(); } else { value = ""; } } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { try { value = new Double(cell.getNumericCellValue()); } catch (NumberFormatException ex) { log.error(null, ex); value = new Double(0.0); } } else { } if (null == value) { continue; } atoms.add(new Atom(value, types.get(j - startCell))); } final Statement statement = new Statement(atoms); if (s.getType() != statement.getType()) { // Give up. s = null; break; } s.statements.add(statement); } // for (int i = startRow + 1; i <= endRow; i++) if (s != null) { statementsList.add(s); } } /* for(int k = 0; k < numberOfSheets; k++) */ } catch (Exception ex) { log.error(null, ex); } finally { org.yccheok.jstock.gui.Utils.close(fileInputStream); } return statementsList; }
From source file:payroll.PaymentFileFormatChecker.java
public void readFileFormat(File inFile) { this.inputFile = inFile; try {/* w ww . j a v a2s . c o m*/ // Create new file input stream from input file FileInputStream fis = new FileInputStream(this.inputFile); // Create new workbook from input stream HSSFWorkbook workbook = new HSSFWorkbook(fis); // Get the first sheet in the workbook (zero indexed) HSSFSheet firstSheet = workbook.getSheetAt(0); // Get the first row, which contains the titles for each field HSSFRow firstRow = firstSheet.getRow(0); // Find length of list by checking last row number int arrayLength = firstRow.getLastCellNum(); ArrayList<String> stringCellList = new ArrayList<>(); Cell cell; String cellValue; // Traverse through the row for (int i = 0; i < arrayLength; i++) { // Store each cell value in the list cell = firstRow.getCell(i); cellValue = cell.getStringCellValue(); stringCellList.add(cellValue); } // Run method to set all index locations setIndexLocations(stringCellList); } catch (IOException e) { System.out.println(e.getMessage()); } }