List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getFirstCellNum
@Override public short getFirstCellNum()
From source file:org.modeshape.sequencer.msoffice.excel.ExcelMetadataReader.java
License:Apache License
public static ExcelMetadata instance(InputStream stream) throws IOException { ExcelMetadata metadata = new ExcelMetadata(); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(stream)); List<ExcelSheetMetadata> sheets = new ArrayList<ExcelSheetMetadata>(); for (int sheetInd = 0; sheetInd < wb.getNumberOfSheets(); sheetInd++) { ExcelSheetMetadata meta = new ExcelSheetMetadata(); meta.setName(wb.getSheetName(sheetInd)); sheets.add(meta);//from w w w . j a va2 s .co m HSSFSheet worksheet = wb.getSheetAt(sheetInd); int lastRowNum = worksheet.getLastRowNum(); StringBuilder buff = new StringBuilder(); for (int rowNum = worksheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { HSSFRow row = worksheet.getRow(rowNum); // Empty rows are returned as null if (row == null) { continue; } int lastCellNum = row.getLastCellNum(); for (int cellNum = row.getFirstCellNum(); cellNum < lastCellNum; cellNum++) { HSSFCell cell = row.getCell(cellNum); // Undefined cells are returned as null if (cell == null) { continue; } /* * Builds a string of body content from all string, numeric, * and formula values in the body of each worksheet. * * This code currently duplicates the POI 3.1 ExcelExtractor behavior of * combining the body text from all worksheets into a single string. */ switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: buff.append(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_NUMERIC: buff.append(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: buff.append(cell.getCellFormula()); break; } HSSFComment comment = cell.getCellComment(); if (comment != null) { // Filter out row delimiter characters from comment String commentText = comment.getString().getString().replace(ROW_DELIMITER_CHAR, ' '); buff.append(" ["); buff.append(commentText); buff.append(" by "); buff.append(comment.getAuthor()); buff.append(']'); } if (cellNum < lastCellNum - 1) { buff.append(CELL_DELIMITER_CHAR); } else { buff.append(ROW_DELIMITER_CHAR); } } } meta.setText(buff.toString()); } metadata.setSheets(sheets); metadata.setMetadata(wb.getSummaryInformation()); return metadata; }
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 {// ww w . ja v a2s. c om 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.ramadda.util.XlsUtil.java
License:Apache License
/** * Convert excel to csv/*from w w w.j a v a 2s.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.yccheok.jstock.file.Statements.java
License:Open Source License
/** * Construct Statements based on given Excel File. * * @param file Given Excel File/*w ww . ja v a2 s .co m*/ * @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:POI.Sheet.java
/** * ??/*from w ww . ja v a 2s .c o m*/ * * @return * @see HSSFSheet */ public int getColumnSize() { HSSFRow row = sheet.getRow(this.getFirstRowNum()); return row.getLastCellNum() - row.getFirstCellNum(); }
From source file:POI.Sheet.java
/** * ??/*from w w w . j a v a 2 s .c o m*/ * * @param index ? * @return ? * @see HSSFRow * @see HSSFCell */ public ArrayList<String> getRowAt(int index) { HSSFRow row = sheet.getRow(index); ArrayList<String> cells = new ArrayList<String>(); int i = row.getFirstCellNum(); while (i < this.getColumnSize()) { HSSFCell cell = row.getCell(i++); if (cell == null) { cells.add(""); } else { Object val = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: val = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: val = cell.getCellFormula(); break; case Cell.CELL_TYPE_NUMERIC: val = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); default: val = cell.getRichStringCellValue(); } cells.add(String.valueOf(val)); } } return cells; }
From source file:ro.nextreports.engine.exporter.util.XlsUtil.java
License:Apache License
/** * Copy a row from a sheet to another sheet * // ww w. j a v a 2 s. co m * * @param srcSheet the sheet to copy * @param destSheet the sheet to copy into * @param parentSheetRow the row inside destSheet where we start to copy * @param parentSheetColumn the column inside destSheet where we start to copy * @param srcRow the row to copy * @param destRow the row to create * @param styleMap style map * */ public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, int parentSheetRow, int parentSheetColumn, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { // manage a list of merged zone in order to not insert two times a // merged zone Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>(); destRow.setHeight(srcRow.getHeight()); // pour chaque row for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); // ancienne cell if (oldCell != null) { HSSFCell newCell = destRow.createCell(parentSheetColumn + j); copyCell(oldCell, newCell, styleMap); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { CellRangeAddress newMergedRegion = new CellRangeAddress( parentSheetRow + mergedRegion.getFirstRow(), parentSheetRow + mergedRegion.getLastRow(), parentSheetColumn + mergedRegion.getFirstColumn(), parentSheetColumn + mergedRegion.getLastColumn()); CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion); if (isNewMergedRegion(wrapper, mergedRegions)) { mergedRegions.add(wrapper); destSheet.addMergedRegion(wrapper.range); } } } } }
From source file:test.ExcelUtil.java
License:Apache License
/** * //w w w . java 2s . c o m * , excel * @param row * @return */ public static boolean isEmptyRow(HSSFRow row) { boolean result = true; if (row == null) { result = true; } else { for (short i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { HSSFCell cell = row.getCell(i); result &= isEmptyCell(cell); if (!result) { break; } } } return result; }
From source file:ucar.unidata.data.DataUtil.java
License:Open Source License
/** * Convert excel to csv//from w ww. j a v a2s.c o m * * @param filename excel file * @param skipToFirstNumeric if true, skip to first numeric * @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, boolean skipToFirstNumeric, SimpleDateFormat sdf) throws Exception { StringBuffer sb = new StringBuffer(); InputStream myxls = IOUtil.getInputStream(filename, DataUtil.class); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); // first sheet boolean seenNumber = false; for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) { HSSFRow row = sheet.getRow(rowIdx); if (row == null) { sb.append("\n"); continue; } boolean rowOk = true; for (short colIdx = row.getFirstCellNum(); colIdx < row.getPhysicalNumberOfCells(); colIdx++) { HSSFCell cell = row.getCell(colIdx); if (cell == null) { continue; } if (skipToFirstNumeric && !seenNumber) { if (cell.getCellType() != HSSFCell.CELL_TYPE_NUMERIC) { rowOk = false; break; } seenNumber = true; } String cellValue = null; if ((sdf != null) && (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)) { if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); cellValue = sdf.format(date); } } if (cellValue == null) { cellValue = cell.toString(); } if (colIdx > 0) { sb.append(","); } sb.append(cellValue); /* if(false && comment!=null) { String author = comment.getAuthor(); String str = comment.getString().getString(); str = StringUtil.replace(str, author+":",""); str = StringUtil.replace(str, "\n",""); sb.append("("+str+")"); }*/ } if (rowOk) { sb.append("\n"); } } return sb.toString(); }
From source file:ugh.fileformats.excel.Excelfile.java
License:Open Source License
/*************************************************************************** * @param inStruct/*from ww w .j ava 2 s . c o m*/ * @param reverse * @param rowCounter * @return **************************************************************************/ private boolean UpdateAllMetadata(DocStruct inStruct, boolean reverse, int rowCounter) { // First, check in which row we have to store information use // information from allDocStruct and allStructRow which had been filled // while reading the excel spredsheet. String currentsheet = null; int currentrow = 0; // We have to put it into comment, because allDocStruct.size is always // different from allStructRow.size, because maindocstruct and anchor // are also in the allDocStruct. for (int j = 0; j < this.allDocStruct.size(); j++) { DocStruct singledoc = this.allDocStruct.get(j); if (singledoc.equals(inStruct)) { // It is the correct docstruct, so get row and sheet. currentrow = Integer.parseInt(this.allStructRow.get(j)); currentsheet = this.allStructSheets.get(j); break; } } if (currentrow == 0) { // We haven't found the inStruct in the structure maybe inStruct was // added after reading the excel sheet. return false; } if (currentsheet == null) { System.err.println("DEBUG: unknown sheet in Excel file"); return false; } if (currentsheet.equals("Bibliographie")) { System.out.println("updating in Sheet \"Bibliographie\"!"); } if (currentsheet.equals("Gliederung")) { List<Metadata> allMD = inStruct.getAllMetadata(); // Count the row in the excel spreadsheet. rowCounter++; for (int i = 0; i < allMD.size(); i++) { Metadata md = allMD.get(i); MetadataType mdt = md.getType(); if (mdt == null) { return false; } if (md.getType().getName().startsWith("_")) { // It's internal metadata, so we have to get out of loop, we // do not have to store internal metadata in excel sheet. continue; } if (md.wasUpdated()) { // Metadata field was updated; we have to update the // spreadsheet cell. Object mdnatobj = md.getNativeObject(); if (mdnatobj == null) { // No object is available. if (md.getValue() != null) { // We have no cell, but a metadata value; so we have // to find the cell (column) and add it to the cell. HSSFSheet inSheet = this.excelworkbook.getSheet(currentsheet); org.apache.poi.hssf.usermodel.HSSFRow secondRow = inSheet.getRow(1); int currentcolumn = 0; int from = secondRow.getFirstCellNum(); int to = secondRow.getLastCellNum(); for (int k = from; k < to + 1; k++) { HSSFCell currentCell = secondRow.getCell((short) (k)); String currentValue = null; if ((currentCell != null) && (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING)) { currentValue = currentCell.getStringCellValue(); } if (currentValue != null) { currentValue.trim(); MetadataType columnmdt = getMDTypeByName(currentValue, "excelGliederung"); if ((columnmdt != null) && (columnmdt.getName().equals(mdt.getName()))) { // We found a column which has a // metadatatype. currentcolumn = k; // Get out of loop, we found the column // for metadata. break; } } } if (currentcolumn == 0) { // Metadata column wasn't found. System.err.println("DEBUG: column couldn' be found"); return false; } // currentrow and currentcolumn contains the cell // coordinates. org.apache.poi.hssf.usermodel.HSSFRow cellRow = inSheet.getRow(currentrow); HSSFCell currentcell = cellRow.getCell((short) (currentcolumn)); if (currentcell == null) { // Cell doesn't exists, so we create a new cell. currentcell = cellRow.createCell((short) (currentcolumn)); System.err.println( "excel cell at " + currentrow + "/" + currentcolumn + " (r/c) is null"); } // Update the value. currentcell.setCellValue(md.getValue()); continue; } // No metadata value and no object. continue; } if (mdnatobj.getClass().getName().equals("HSSFCell")) { HSSFCell mdcell = (HSSFCell) mdnatobj; if (md.getValue() == null) { mdcell.setCellValue(""); } else { mdcell.setCellValue(md.getValue()); } } else { // Wrong native object; not an excel spreadsheet we // should throw an exception here. return false; } } } } if (reverse) { // All children. List<DocStruct> allChildren = inStruct.getAllChildren(); if (allChildren == null) { // No children, so we can get out. return true; } for (int i = 0; i < allChildren.size(); i++) { DocStruct child = allChildren.get(i); if (!UpdateAllMetadata(child, true, rowCounter)) { return false; } } } return true; }