List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getFirstRowNum
@Override public int getFirstRowNum()
From source file:org.openmrs.module.kenyametadatatools.mflsync.MflSyncFromRemoteSpreadsheetTask.java
License:Open Source License
/** * Imports a MFL spreadsheet from a stream * @param stream the input stream/*from w ww.j ava 2s. co m*/ * @throws IOException if an error occurred */ protected void importXls(InputStream stream) throws IOException { POIFSFileSystem poifs = new POIFSFileSystem(stream); HSSFWorkbook wbook = new HSSFWorkbook(poifs); HSSFSheet sheet = wbook.getSheetAt(0); for (int r = sheet.getFirstRowNum() + 1; r <= sheet.getLastRowNum(); ++r) { HSSFRow row = sheet.getRow(r); String code = String.valueOf(((Double) cellValue(row.getCell(0))).intValue()); String name = (String) cellValue(row.getCell(1)); String province = (String) cellValue(row.getCell(2)); String type = (String) cellValue(row.getCell(6)); if (StringUtils.isEmpty(name)) { TaskEngine.logError("Unable to import location " + code + " with empty name"); } else if (StringUtils.isEmpty(code)) { TaskEngine.logError("Unable to import location '" + name + "' with invalid code"); } else { importLocation(code, name.trim(), province, type); } } }
From source file:org.ramadda.data.tools.ProcessXls.java
License:Apache License
/** * _more_/*from w w w . j av a 2s . co m*/ * * @param filename _more_ * * @return _more_ * * @throws Exception _more_ */ public String makeEntries(String filename) throws Exception { sb.append("<entries>"); /* sb.append(XmlUtil.tag("entry", XmlUtil.attrs(new String[]{ "type", "group", "name", "Projects", "id","project", }),"")); */ InputStream myxls = IOUtil.getInputStream(filename, ProcessXls.class); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); int skipRows = 2; for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) { if (skipRows-- > 0) { // System.err.println("skipping"); continue; } HSSFRow row = sheet.getRow(rowIdx); if ((row == null) || (rowIdx == 0)) { continue; } processRow(row); } sb.append(end); sb.append("</entries>"); return sb.toString(); }
From source file:org.ramadda.util.XlsUtil.java
License:Apache License
/** * Convert excel to csv/*from w w w . j a v a2s . co 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 w w .j a v a 2s . 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:poi.hssf.view.SVSheetTable.java
License:Apache License
public SVSheetTable(HSSFSheet sheet) { super(new SVTableModel(sheet)); this.sheet = sheet; setIntercellSpacing(new Dimension(0, 0)); setAutoResizeMode(AUTO_RESIZE_OFF);//from w w w. j ava2 s . c o m JTableHeader header = getTableHeader(); header.setDefaultRenderer(new HeaderCellRenderer()); pendingPaintings = new PendingPaintings(this); //Set the columns the correct size TableColumnModel columns = getColumnModel(); for (int i = 0; i < columns.getColumnCount(); i++) { TableColumn column = columns.getColumn(i); int width = sheet.getColumnWidth(i); //256 is because the width is in 256ths of a character column.setPreferredWidth(width / 256 * magicCharFactor); } Toolkit t = getToolkit(); int res = t.getScreenResolution(); TableModel model = getModel(); for (int i = 0; i < model.getRowCount(); i++) { Row row = sheet.getRow(i - sheet.getFirstRowNum()); if (row != null) { short h = row.getHeight(); int height = Math.round(Math.max(1, h / (res / 70 * 20) + 3)); System.out.printf("%d: %d (%d @ %d)%n", i, height, h, res); setRowHeight(i, height); } } addHierarchyListener(new HierarchyListener() { public void hierarchyChanged(HierarchyEvent e) { if ((e.getChangeFlags() & HierarchyEvent.PARENT_CHANGED) != 0) { Container changedParent = e.getChangedParent(); if (changedParent instanceof JViewport) { Container grandparent = changedParent.getParent(); if (grandparent instanceof JScrollPane) { JScrollPane jScrollPane = (JScrollPane) grandparent; setupScroll(jScrollPane); } } } } }); }
From source file:POI.Sheet.java
/** * HSSFSheetSheet//from w w w . ja va 2 s .com * * @param sheet */ public Sheet(HSSFSheet sheet) { this.sheet = sheet; this.firstRowNum = sheet.getFirstRowNum(); this.lastRowNum = sheet.getLastRowNum(); }
From source file:ro.nextreports.engine.exporter.util.XlsUtil.java
License:Apache License
/** * Copy a sheet to another sheet at a specific (row, column) position * //w w w . j av a 2 s . c o m * @param parentSheet the sheet to copy into * @param parentSheetRow the row inside parentSheet where we start to copy * @param parentSheetColumn the column inside parentSheet where we start to copy * @param sheet the sheet that is copied * @param copyStyle true to copy the style * @return column number */ public static int copyToSheet(HSSFSheet parentSheet, int parentSheetRow, int parentSheetColumn, HSSFSheet sheet, boolean copyStyle) { int maxColumnNum = 0; Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow srcRow = sheet.getRow(i); HSSFRow destRow; // subreport is not the first cell in a row if ((parentSheetColumn > 0) && (i == sheet.getFirstRowNum())) { destRow = parentSheet.getRow(parentSheetRow); } else { destRow = parentSheet.getRow(parentSheetRow + i); if (destRow == null) { destRow = parentSheet.createRow(parentSheetRow + i); } } if (srcRow != null) { XlsUtil.copyRow(sheet, parentSheet, parentSheetRow, parentSheetColumn, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { parentSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } return maxColumnNum; }
From source file:senselogic.excelbundle.ExcelImporter.java
License:Apache License
/** * Fetch all LanguageFiles in the specified language from the specified * sheet and add them to the specified LanguagePack. *///w ww.j a v a 2 s. c o m public void getFromSheet(HSSFSheet sheet, String language, LanguagePack pack) { //First, let's fetch all of the defined rows List<HSSFRow> rows = new ArrayList<HSSFRow>(); int lastRow = sheet.getLastRowNum(); for (int i = sheet.getFirstRowNum(); i <= lastRow; i++) { //Don't add empty rows HSSFRow r = sheet.getRow(i); if ((r != null) && (r.getPhysicalNumberOfCells() != 0)) { rows.add(r); } } //Now go, through them one at a time ListIterator<HSSFRow> it = rows.listIterator(); while (it.hasNext()) { HSSFRow r = it.next(); HSSFCell c = r.getCell(0); String value = getString(c); if ((value.charAt(0) != '/') && (value.charAt(0) != '\\')) continue; String bundlePath = value; //Let's see if this language is included if (!it.hasNext()) break; r = it.next(); int valueCol = getIndexOf(r, language); //Skip to next if the language isn't included in this bundle if (valueCol < 0) continue; LanguageFile langFile = new LanguageFile(bundlePath, language); //Now, search through all the keys while (it.hasNext()) { r = it.next(); String firstCell = getString(r.getCell(0)); if (firstCell == null) continue; //If this is a bundle path, rewind and break the loop to parse //the next bundle if ((firstCell.charAt(0) == '/') || (firstCell.charAt(0) == '\\')) { it.previous(); break; } langFile.setValue(firstCell, getString(r.getCell(valueCol))); } if (!langFile.getPairs().isEmpty()) pack.addLanguageFile(langFile); } }
From source file:ucar.unidata.data.DataUtil.java
License:Open Source License
/** * Convert excel to csv//from w ww. j a v a2 s. c om * * @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(); }