List of usage examples for org.apache.poi.ss.usermodel Sheet getFirstRowNum
int getFirstRowNum();
From source file:eu.esdihumboldt.hale.io.xls.test.writer.XLSInstanceWriterTest.java
License:Open Source License
private void checkFirstDataRow(Sheet sheet, List<String> firstDataRow) { Row datarow = sheet.getRow(sheet.getFirstRowNum() + 1); assertEquals("There are not enough data cells.", firstDataRow.size(), datarow.getPhysicalNumberOfCells()); for (Cell cell : datarow) { assertTrue("Not expecting data value.", firstDataRow.contains(cell.getStringCellValue())); }/*from w ww . j a v a 2 s.co m*/ }
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 w w w . j a va2 s . com*/ 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; }
From source file:Import.SheetFrameController.java
@Override public void handle(Event event) { if (event.getSource() == listSheet) { if (/*listSheet.getSelectionModel().getSelectedIndex() > -1*/ listSheet.getSelectionModel() .getSelectedItem() != null) { // clear de la liste des columns listColumn.getSelectionModel().clearSelection(); // update de la liste des colonnes // rcupration du nom du sheet slectionn String sheetName = (String) listSheet.getSelectionModel().getSelectedItem(); // rcupration du sheet Sheet sheet = book.getSheet(sheetName); // rcupration des colonnes du sheet int top = sheet.getFirstRowNum(); Row row = sheet.getRow(top); // rcupration du nombre de cellule dans la row short first = row.getFirstCellNum(); short last = row.getLastCellNum(); // on parse la premiure row entre le first et le last // cration du arraylist ArrayList al = new ArrayList(); al.clear();/*w w w . ja v a 2 s. c om*/ // boolean exeption boolean catchException = false; for (int i = first; i < last; i++) { Cell cell = row.getCell(i); // on rcupre le nom de la cellule try { if (cell.getCellType() == CellType.STRING.getCode()) { String value = cell.getStringCellValue(); // on ajoute la valeur dans le arraylist al.add(value); } } catch (java.lang.NullPointerException nle) { catchException = true; } } if (catchException) this.alertException( "Un probleme est survenu dans la lecture d'une ou plusieurs cellules du fichier"); // on transverse le arraylist dans le observable list ObservableList<String> ol = FXCollections.observableArrayList(al); // on attache le ol dans le listColumn listColumn.setItems(ol); } } if (event.getSource() == listColumn) { if (listColumn.getSelectionModel().getSelectedIndex() > -1) { // un item est slectionn dans la liste, on enable le bouton suivant bSuiv.setDisable(false); } else bSuiv.setDisable(true); } }
From source file:io.konig.spreadsheet.WorkbookProcessorImpl.java
License:Apache License
private void visitSheet(WorkbookSheet bookSheet) throws SpreadsheetException { String sheetName = bookSheet.getSheet().getSheetName(); if (settings.getIgnoreSheets().contains(sheetName)) { logger.debug("Ignoring Sheet ... {}", sheetName); return;//from w w w.j av a 2 s. c om } logger.debug("visitSheet({})", sheetName); List<SheetColumn> undeclaredColumns = new ArrayList<>(); assignColumnIndexes(bookSheet, undeclaredColumns); SheetProcessor processor = bookSheet.getProcessor(); Sheet sheet = bookSheet.getSheet(); int rowSize = sheet.getLastRowNum() + 1; SheetColumn projectColumn = filterByProject(bookSheet); // Skip the first row since it is the column header row for (int i = sheet.getFirstRowNum() + 1; i < rowSize; i++) { Row row = sheet.getRow(i); if (row != null) { SheetRow sheetRow = new SheetRow(bookSheet, row); sheetRow.setUndeclaredColumns(undeclaredColumns); try { if (accept(sheetRow, processor, projectColumn)) { processor.visit(sheetRow); } } catch (SpreadsheetException e) { handle(e); } } } }
From source file:io.konig.spreadsheet.WorkbookProcessorImpl.java
License:Apache License
private void assignColumnIndexes(WorkbookSheet s, List<SheetColumn> undeclaredColumns) { logger.debug("assignColumnIndexes({})", s.getSheet().getSheetName()); undeclaredColumns.clear();/*from w w w . j ava 2 s . c o m*/ Sheet sheet = s.getSheet(); SheetProcessor p = s.getProcessor(); for (SheetColumn c : p.getColumns()) { c.setIndex(-1); } int firstRow = sheet.getFirstRowNum(); Row row = sheet.getRow(firstRow); int colSize = row.getLastCellNum() + 1; for (int i = row.getFirstCellNum(); i < colSize; i++) { Cell cell = row.getCell(i); if (cell != null) { String columnName = cellStringValue(cell); if (columnName != null) { SheetColumn column = p.findColumnByName(columnName); if (column != null) { column.setIndex(i); logger.debug("assignColumnIndexes - {} index = {}", column, i); } else { SheetColumn c = new SheetColumn(columnName); c.setIndex(i); undeclaredColumns.add(c); } } } } }
From source file:io.konig.spreadsheet.WorkbookProcessorImpl.java
License:Apache License
private int rank(Sheet sheet, SheetProcessor p) throws SpreadsheetException { int count = 0; int firstRow = sheet.getFirstRowNum(); Row row = sheet.getRow(firstRow);/* www . jav a 2 s . co m*/ int colSize = row.getLastCellNum() + 1; for (int i = row.getFirstCellNum(); i < colSize; i++) { Cell cell = row.getCell(i); if (cell != null) { String text = cellStringValue(cell); if (text != null) { SheetColumn column = p.findColumnByName(text); if (column != null) { count++; } } } } return count; }
From source file:jacobi.test.util.JacobiDataSource.java
License:Open Source License
/** * Get all matrices defined in the specified worksheet. * @param name Name of the worksheet/* w ww.j ava2 s . co m*/ * @return Matrices defined */ public Map<Integer, Matrix> get(String name) { Throw.when().isNull(() -> this.workbook.getSheet(name), () -> "Worksheet " + name + " not found."); Map<Integer, Matrix> data = new TreeMap<>(); Sheet sheet = this.workbook.getSheet(name); int k = sheet.getFirstRowNum(); while (k <= sheet.getLastRowNum()) { Integer id = this.getIdFromAnchor(sheet.getRow(k++)); if (id == null) { continue; } Matrix matrix = this.createMatrix(sheet.getRow(k++)); k = this.readMatrixElements(sheet, k, matrix); data.put(id, matrix); } return data; }
From source file:jp.co.orangeright.crossheadofficesample2.jsf.ItemFileInterfaceContoroller.java
public String createTodenHomeItemExcel() { int count = 0; try {//from w ww. j a va2 s . c o m File excelFile = this.getFile("todenhome" + this.dataFile.getSubmittedFileName()); Workbook workbook = WorkbookFactory.create(excelFile); Sheet sheet = workbook.getSheet("Sheet1"); for (int rowNumber = sheet.getFirstRowNum() + 1; rowNumber <= sheet.getLastRowNum(); rowNumber++) { String itemCd = this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(0)); ItemSearchCondition itemCondition = new ItemSearchCondition(); itemCondition.setItemcd(itemCd); List<Item> itemList = this.itemEjb.findAll(itemCondition); if (itemList.size() > 0) { //??????? } else { this.itemController.prepareCreate(); this.itemController.getSelected().setItemcd(itemCd); this.itemController.getSelected().setCustomerid(this.customerEjb.find(31925)); this.itemController.getSelected().setUserid(this.userEjb.find("mitanto")); StringBuilder detail = new StringBuilder(); detail.append("/****** ????? ******/"); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("?: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(0))); detail.append(System.lineSeparator()); detail.append("?: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(1))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("?: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(13))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("?: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(14))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append(": "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(15))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("?: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(16))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("??: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(17))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("?_: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(18))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("?_: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(19))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append(": "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(20))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("WiFi: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(21))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("IoTNo: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(57))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("ID: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(58))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append(": "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(59))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append(": "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(60))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("Notion: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(61))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("Notion: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(62))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append(": "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(79))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("???_: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(80))); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(81))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("???_: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(82))); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(83))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append(": "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(84))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append(": "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(85))); detail.append("/"); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(86))); detail.append("/"); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(87))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("_?: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(107))); detail.append(System.lineSeparator()); detail.append("_?: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(108))); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(109))); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(110))); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(111))); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(112))); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(113))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("_: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(114))); detail.append("-"); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(115))); detail.append("-"); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(116))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append("__??: "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(117))); detail.append("-"); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(118))); detail.append("-"); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(119))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); detail.append(": "); detail.append(this.getTodenExcelCellValue(sheet.getRow(rowNumber).getCell(120))); detail.append(System.lineSeparator()); detail.append(System.lineSeparator()); this.itemController.getSelected().setDetail(detail.toString()); this.itemController.getSelected().setMemo(""); this.itemController.create(); count++; } } excelFile.delete(); JsfUtil.addSuccessMessage(count + "????"); } catch (Exception e) { return null; } return null; }
From source file:massiveanalyserxryv2.ServiceTaskSearch.java
@Override protected Task createTask() { Task task;/*w ww .ja va 2 s. c o m*/ task = new Task() { @Override protected Object call() throws Exception { // rcupration de la liste des mots cls // si il s'agit d'une base de donne slectionn dans la liste String path; if (modelDataSearch.getAbosoluthPathDb() == null) { path = System.getProperty("user.dir"); path = path + "/db/"; path = path + modelDataSearch.getNameDb(); } else { // sinon on cre le path avec le chemin absolu (fichier import) path = modelDataSearch.getAbosoluthPathDb(); } ArrayList<String> keyWords = new ArrayList<String>(); keyWords.clear(); // lecture for (String line : Files.readAllLines(Paths.get(path))) { keyWords.add(line); } // rcupration de la liste des contents du tableau excel Workbook book = WorkbookFactory.create(modelDataSearch.getFile()); // rcupration du sheet Sheet sheet = book.getSheet(modelDataSearch.getNameSheet()); // rcupration de la colonne int top = sheet.getFirstRowNum(); int down = sheet.getLastRowNum(); Row row = sheet.getRow(top); // on parse les column jusqu'a ce que le nom soit le meme que celui dans le modele short start = row.getFirstCellNum(); short end = row.getLastCellNum(); int indiceColumn = -1; for (short i = start; i <= end; i++) { if (row.getCell(i).getStringCellValue().equals(modelDataSearch.getNameColumn())) { // on connait l'indice de column indiceColumn = i; break; } } // cration de la liste des contents ArrayList<DataContent> listContent = new ArrayList<DataContent>(); listContent.clear(); for (int j = top; j <= down; j++) { if (sheet.getRow(j) != null) { if ((sheet.getRow(j).getCell(indiceColumn).getCellType() == CellType.STRING.getCode())) { DataContent data = new DataContent(j, sheet.getRow(j).getCell(indiceColumn).getStringCellValue()); listContent.add(data); } } } // recherches for (DataContent content : listContent) { for (String key : keyWords) { if (key.isEmpty()) continue; int res = content.getContent().toLowerCase().indexOf(key.toLowerCase()); if (res != -1) { DataResultat data = new DataResultat(content.getNumRow() + 1, content.getContent(), key); // +1 car dans le fichie excel les row commence 1 et pas 0 ob.add(data); } } } // Fermeture du workbook if (book != null) book.close(); //tableauResultat.setItems(ob); return (Object) ob; } }; return task; }
From source file:net.sf.excelutils.ExcelParser.java
License:Apache License
/** * parse the cell// w w w. j a va 2s. c o m * * @param context data object * @param cell excel cell */ public static void parseCell(Object context, Sheet sheet, Row row, Cell cell) { String str = cell.getStringCellValue(); if (null == str || "".equals(str)) { return; } if (str.indexOf(VALUED_DELIM) < 0) return; boolean bJustExpr = str.length() == (str.length() - str.lastIndexOf(VALUED_DELIM)); boolean bMerge = "!".equals(str.substring(str.indexOf(VALUED_DELIM) + VALUED_DELIM.length(), str.indexOf(VALUED_DELIM) + VALUED_DELIM.length() + 1)); if (str.indexOf(VALUED_DELIM) < 0) return; Object value = parseStr(context, str); // replace the cell if (null != value) { if (bJustExpr && "java.lang.Integer".equals(value.getClass().getName())) { cell.setCellValue(Double.parseDouble(value.toString())); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (bJustExpr && "java.lang.Double".equals(value.getClass().getName())) { cell.setCellValue(((Double) value).doubleValue()); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (bJustExpr && "java.util.Date".equals(value.getClass().getName())) { cell.setCellValue((Date) value); } else if (bJustExpr && "java.lang.Boolean".equals(value.getClass().getName())) { cell.setCellValue(((Boolean) value).booleanValue()); cell.setCellType(Cell.CELL_TYPE_BOOLEAN); } else if (bJustExpr && Number.class.isAssignableFrom(value.getClass())) { cell.setCellValue(((Number) (value)).doubleValue()); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else { // cell.setEncoding(Workbook.ENCODING_UTF_16); POI3.2? cell.setCellValue(value.toString()); } } else { cell.setCellValue(""); } // merge the cell that has a "!" character at the expression if (row.getRowNum() - 1 >= sheet.getFirstRowNum() && bMerge) { Row lastRow = WorkbookUtils.getRow(row.getRowNum() - 1, sheet); Cell lastCell = WorkbookUtils.getCell(lastRow, cell.getColumnIndex()); boolean canMerge = false; if (lastCell.getCellType() == cell.getCellType()) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: canMerge = lastCell.getStringCellValue().equals(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: canMerge = lastCell.getBooleanCellValue() == cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: canMerge = lastCell.getNumericCellValue() == cell.getNumericCellValue(); break; } } if (canMerge) { CellRangeAddress region = new CellRangeAddress(lastRow.getRowNum(), row.getRowNum(), lastCell.getColumnIndex(), cell.getColumnIndex()); sheet.addMergedRegion(region); } } }