List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum
short getLastCellNum();
From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java
License:Mozilla Public License
/** * Method goes through rows after XML Schema and finds schemas for Excel sheets (DataDict tables). cell(0) =sheet name; * cell(1)=XML schema//from w ww.ja v a2 s . c o m * * @param schemaSheet sheet name * @return Map */ private Map<String, String> findSheetSchemas(Sheet schemaSheet) { Row schemaRow = null; Cell schemaCell = null; Cell sheetCell = null; Map<String, String> result = new LinkedHashMap<String, String>(); if (schemaSheet.getLastRowNum() < 1) { return null; } for (int i = 0; i <= schemaSheet.getLastRowNum(); i++) { schemaRow = schemaSheet.getRow(i); if (schemaRow == null) { continue; } if (schemaRow.getLastCellNum() < 1) { continue; } schemaCell = schemaRow.getCell(1); if (schemaCell == null) { continue; } String schemaValue = schemaCell.getRichStringCellValue().toString(); if (schemaValue.startsWith("http://") && schemaValue.toLowerCase().indexOf("/getschema") > 0 && Utils.isURL(schemaValue)) { sheetCell = schemaRow.getCell(0); String sheetValue = sheetCell.getRichStringCellValue().toString(); if (sheetValue == null) { continue; } if (sheetValue != null && sheetValue.length() > 31) { sheetValue = sheetValue.substring(0, 31); } Sheet sheet = getSheet(sheetValue); if (sheet != null && !result.containsKey(sheetValue)) { result.put(sheetValue, schemaValue); } } } return result; }
From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java
License:Mozilla Public License
/** * Read column header.// w w w . jav a2 s . c o m * * @param row Excel row object * @param elements List of DD table elements * @param mainTable true if the table is main table. */ private void setColumnMappings(Row row, List<DDXmlElement> elements, boolean mainTable) { if (row == null || elements == null) { return; } int firstCell = row.getFirstCellNum(); int lastCell = row.getLastCellNum(); for (int j = 0; j < elements.size(); j++) { DDXmlElement elem = elements.get(j); String elemLocalName = elem.getLocalName(); for (int k = firstCell; k < lastCell; k++) { Cell cell = row.getCell(k); String colName = cellValueToString(cell, null); colName = colName != null ? colName.trim() : ""; if (colName.equalsIgnoreCase(elemLocalName)) { elem.setColIndex(k); elem.setMainTable(mainTable); break; } } } }
From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java
License:Mozilla Public License
/** * Goes through all columns and logs missing and redundant columns into conversion log. * * @param sheetName Excel sheet name./*from w w w. j a v a 2 s . c o m*/ * @param row Excel Row object * @param metaRow Excel meta sheet row * @param elements List of XML elements */ private void logColumnMappings(String sheetName, Row row, Row metaRow, List<DDXmlElement> elements) { int nofColumns = row.getLastCellNum() - row.getFirstCellNum(); readerLogger.logNumberOfColumns(nofColumns, sheetName); if (metaRow != null) { int nofMetaColumns = row.getLastCellNum() - row.getFirstCellNum(); readerLogger.logNumberOfColumns(nofMetaColumns, sheetName + DDXMLConverter.META_SHEET_NAME); } List<String> missingColumns = new ArrayList<String>(); List<String> elemNames = new ArrayList<String>(); for (DDXmlElement element : elements) { if (element.getColIndex() < 0) { missingColumns.add(element.getLocalName()); } elemNames.add(element.getLocalName().toLowerCase()); } if (missingColumns.size() > 0) { readerLogger.logMissingColumns(StringUtils.join(missingColumns, ", "), sheetName); } List<String> extraColumns = getExtraColumns(sheetName, row, elemNames); if (extraColumns.size() > 0) { readerLogger.logExtraColumns(StringUtils.join(extraColumns, ", "), sheetName); } if (metaRow != null) { List<String> extraMetaColumns = getExtraColumns(sheetName, metaRow, elemNames); if (extraMetaColumns.size() > 0) { readerLogger.logExtraColumns(StringUtils.join(extraColumns, ", "), sheetName + DDXMLConverter.META_SHEET_NAME); } } }
From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java
License:Mozilla Public License
/** * Find redundant columns from the list of columns. * * @param sheetName Excel sheet name.//from w w w . j ava 2s. c o m * @param row Excel row. * @param elemNames DD element names. * @return List of extra columns added to sheet. */ private List<String> getExtraColumns(String sheetName, Row row, List<String> elemNames) { List<String> extraColumns = new ArrayList<String>(); List<Integer> emptyColumns = new ArrayList<Integer>(); for (int k = row.getFirstCellNum(); k < row.getLastCellNum(); k++) { Cell cell = row.getCell(k); String colName = (cell != null) ? cellValueToString(cell, null) : null; colName = colName != null ? colName.trim() : ""; if (colName.equals("")) { emptyColumns.add(k); } else if (!Utils.isNullStr(colName) && !elemNames.contains(colName.toLowerCase())) { extraColumns.add(colName); } } if (emptyColumns.size() > 0) { readerLogger.logInfo(sheetName, "Found data from column(s): " + StringUtils.join(emptyColumns, ", ") + ", but no column heading is available. The column(s) will be ignored."); } return extraColumns; }
From source file:eu.esdihumboldt.hale.app.bgis.ade.common.AbstractAnalyseTable.java
License:Open Source License
/** * Analyzes the table header.//from w w w. j a v a 2 s .co m * * @param sheet the table sheet */ protected void analyseHeader(Sheet sheet) { Row header = sheet.getRow(0); // identify columns for (int i = header.getFirstCellNum(); i < header.getLastCellNum(); i++) { Cell cell = header.getCell(i); String text = extractText(cell); headerCell(i, text); } }
From source file:eu.esdihumboldt.hale.io.xls.AnalyseXLSSchemaTable.java
License:Open Source License
/** * @see eu.esdihumboldt.hale.io.xls.AbstractAnalyseTable#analyseRow(int, * org.apache.poi.ss.usermodel.Row) *//*from w w w . j a v a 2s. com*/ @Override protected void analyseRow(int num, Row row) { List<String> rowContent = new ArrayList<String>(); for (int i = 0; i < row.getLastCellNum(); i++) { rowContent.add(extractText(row.getCell(i))); } rows.put(num, rowContent); }
From source file:examples.toHTML.ToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();//from ww w.ja va 2 s . c o m out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); out.format(" <tr>%n"); out.format(" <td class=\"%s\">%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { // String content = " "; String attrs = ""; CellStyle style = null; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); //Set the value that is rendered for the cell //also applies the format CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; content = replaceUmlaut(content); if (content.equals("")) // content = " "; } } out.format(" <td class=\"%s %s\">%s</td>%n", styleName(style), attrs, content); } out.format(" </tr>%n"); } out.format("</tbody>%n"); }
From source file:failedtiposting.AnalyzePostings.java
private void analyzeBtnActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_analyzeBtnActionPerformed // TODO add your handling code here: String casaPath = casaBrowserText.getText(); String failedPath = failedTrxnBrowserText.getText(); FileInputStream casaStream = null; FileInputStream failedStream = null; if (casaPath.equals("") || failedPath.equals("")) { outputArea.append("Please specify both files if you need me to run!"); return;//www . j a v a2s . c o m } try { casaStream = new FileInputStream(casaPath); failedStream = new FileInputStream(failedPath); POIFSFileSystem casaPoiFs = new POIFSFileSystem(casaStream); POIFSFileSystem failedPoiFs = new POIFSFileSystem(failedStream); HSSFWorkbook casaWb = new HSSFWorkbook(casaPoiFs); HSSFWorkbook failedWb = new HSSFWorkbook(failedPoiFs); HSSFSheet casaSh = casaWb.getSheetAt(0); HSSFSheet failedSh = failedWb.getSheetAt(0); for (Row row : failedSh) { int lastCol = row.getLastCellNum(); for (int col = 0; col < lastCol; col++) { if (col == NARRATION_COLUMN) { Cell cell = row.getCell(col, Row.RETURN_BLANK_AS_NULL); if (cell != null) { String narr = cell.toString(); String found = getNarrationToken(narr); outputArea.append(narr + "\n"); outputArea.append("found = " + found + "\n\n\n"); } } } } } catch (IOException ex) { outputArea.append(ex.getMessage()); } finally { if (casaStream != null) { try { casaStream.close(); } catch (IOException ex) { outputArea.append(ex.getMessage()); } } if (failedStream != null) { try { failedStream.close(); } catch (IOException ex) { outputArea.append(ex.getMessage()); } } } }
From source file:fft.FFT.java
License:Open Source License
public static void main(String[] args) throws IOException { InputStream myxls = new FileInputStream("/Users/huangge/Documents/workspace/fft/src/BxDec99.xls"); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0);/*from w ww .ja v a2s.c om*/ int rowStart = Math.min(15, sheet.getFirstRowNum()); int rowEnd = Math.max(1400, sheet.getLastRowNum()); Row r_for_rowCount = sheet.getRow(0); int lastColumn = Math.min(r_for_rowCount.getLastCellNum(), 1000); double[][] res = new double[lastColumn - 1][rowEnd]; Workbook wb_out = new HSSFWorkbook(); // or new XSSFWorkbook(); Sheet sheet_out = wb_out.createSheet(); int count = 0; for (int j = 1; j < lastColumn; j++) {//make res matrix count = 0; for (int i = 1; i <= rowEnd; i++) { Row r = sheet.getRow(i); Cell c = r.getCell(3, Row.RETURN_BLANK_AS_NULL); if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) { break; } else if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) { res[j - 1][i - 1] = c.getNumericCellValue(); count++; } } } int N = count; int nextPowTwo = 1; while (nextPowTwo < N) { nextPowTwo += nextPowTwo; } N = nextPowTwo; FFT fft = new FFT(N); double[] window = fft.getWindow(); double[] re = new double[N]; Arrays.fill(re, 0); ; double[] im = new double[N]; for (int i = 0; i < re.length / 2; i++) {//initial sheet Row row_cre = sheet_out.createRow(i); for (int k = 0; k < lastColumn - 1; k++) { Cell cell = row_cre.createCell((short) (k)); } } for (int j = 1; j < lastColumn; j++) {//make result sheet for (int i = 0; i < count; i++) { re[i] = res[j - 1][i]; im[i] = 0; } beforeAfter(fft, re, im); for (int i = 0; i < re.length / 2; i++) { Row row_out = sheet_out.getRow(i); Cell cell = row_out.getCell((short) (j - 1)); cell.setCellValue(Math.abs(re[i])); } } FileOutputStream fileOut//write file = new FileOutputStream("/Users/huangge/Documents/workspace/fft/src/workbook.xls"); wb_out.write(fileOut); fileOut.close(); long time = System.currentTimeMillis(); double iter = 10; for (int i = 0; i < iter; i++) // fft.fft(re,im); time = System.currentTimeMillis() - time; System.out.println("Averaged " + (time / iter) + "ms per iteration"); }
From source file:fi.semantum.strategia.widget.Database.java
License:Open Source License
public static Database load(Main main, String databaseId) { Database result = null;//from www . j av a 2 s . co m synchronized (Database.class) { try { Map<String, EnumerationDatatype> enumerations = new HashMap<String, EnumerationDatatype>(); try { File file = new File(Main.baseDirectory(), "database.xlsx"); FileInputStream fis = new FileInputStream(file); Workbook book = WorkbookFactory.create(fis); fis.close(); Sheet sheet = book.getSheetAt(0); for (int rowN = sheet.getFirstRowNum(); rowN <= sheet.getLastRowNum(); rowN++) { Row row = sheet.getRow(rowN); Cell cell = row.getCell(0, Row.RETURN_BLANK_AS_NULL); if (cell != null) { if ("Monivalinta".equals(cell.toString())) { Cell id = row.getCell(1, Row.RETURN_BLANK_AS_NULL); if (id == null) continue; Cell traffic = row.getCell(2, Row.RETURN_BLANK_AS_NULL); if (traffic == null) continue; int count = row.getLastCellNum() - 3; if (traffic.toString().length() != count) continue; List<String> values = new ArrayList<String>(); for (int i = 0; i < count; i++) { Cell val = row.getCell(3 + i, Row.RETURN_BLANK_AS_NULL); if (val != null) values.add(val.toString()); } enumerations.put(id.toString(), new EnumerationDatatype(result, id.toString(), values, traffic.toString())); } } } } catch (Exception e) { } File f = new File(Main.baseDirectory(), databaseId); FileInputStream fileIn = new FileInputStream(f); ObjectInputStream in = new ObjectInputStream(fileIn); result = (Database) in.readObject(); in.close(); fileIn.close(); result.databaseId = databaseId; main.setDatabase(result); migrate(main, enumerations); validate(main); result.lastModified = new Date(f.lastModified()); } catch (IOException i) { i.printStackTrace(); result = create(main, databaseId); } catch (ClassNotFoundException c) { System.out.println("Database class not found"); c.printStackTrace(); result = create(main, databaseId); } result.touchBackup(); result.updateTags(); try { if (!Lucene.indexExists(databaseId)) { Lucene.startWrite(databaseId); for (Base b : result.enumerate()) { Lucene.set(databaseId, b.uuid, b.searchText(result)); } Lucene.endWrite(); } } catch (Throwable t) { t.printStackTrace(); } } return result; }