List of usage examples for org.apache.poi.ss.usermodel Row getFirstCellNum
short getFirstCellNum();
From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java
License:MIT License
/** * Gets the sheet configuration./*from w w w . ja va 2s. c o m*/ * * @param sheet * the sheet * @param formName * the form name * @param sheetRightCol * the sheet right col * @return the sheet configuration */ private SheetConfiguration getSheetConfiguration(final Sheet sheet, final String formName, final int sheetRightCol) { SheetConfiguration sheetConfig = new SheetConfiguration(); sheetConfig.setFormName(formName); sheetConfig.setSheetName(sheet.getSheetName()); int leftCol = sheet.getLeftCol(); int lastRow = sheet.getLastRowNum(); int firstRow = sheet.getFirstRowNum(); int rightCol = 0; int maxRow = 0; for (Row row : sheet) { if (row.getRowNum() > TieConstants.TIE_WEB_SHEET_MAX_ROWS) { break; } maxRow = row.getRowNum(); int firstCellNum = row.getFirstCellNum(); if (firstCellNum >= 0 && firstCellNum < leftCol) { leftCol = firstCellNum; } if ((row.getLastCellNum() - 1) > rightCol) { int verifiedcol = verifyLastCell(row, rightCol, sheetRightCol); if (verifiedcol > rightCol) { rightCol = verifiedcol; } } } if (maxRow < lastRow) { lastRow = maxRow; } // header range row set to 0 while column set to first column to // max // column (FF) e.g. $A$0 : $FF$0 String tempStr = TieConstants.CELL_ADDR_PRE_FIX + WebSheetUtility.getExcelColumnName(leftCol) + TieConstants.CELL_ADDR_PRE_FIX + "0 : " + TieConstants.CELL_ADDR_PRE_FIX + WebSheetUtility.getExcelColumnName(rightCol) + TieConstants.CELL_ADDR_PRE_FIX + "0"; sheetConfig.setFormHeaderRange(tempStr); sheetConfig.setHeaderCellRange(new CellRange(tempStr)); // body range row set to first row to last row while column set // to // first column to max column (FF) e.g. $A$1 : $FF$1000 tempStr = TieConstants.CELL_ADDR_PRE_FIX + WebSheetUtility.getExcelColumnName(leftCol) + TieConstants.CELL_ADDR_PRE_FIX + (firstRow + 1) + " : " + TieConstants.CELL_ADDR_PRE_FIX + WebSheetUtility.getExcelColumnName(rightCol) + TieConstants.CELL_ADDR_PRE_FIX + (lastRow + 1); sheetConfig.setFormBodyRange(tempStr); sheetConfig.setBodyCellRange(new CellRange(tempStr)); sheetConfig.setFormBodyType(org.tiefaces.common.TieConstants.FORM_TYPE_FREE); sheetConfig.setCellFormAttributes(new HashMap<String, List<CellFormAttributes>>()); // check it's a hidden sheet int sheetIndex = parent.getWb().getSheetIndex(sheet); if (parent.getWb().isSheetHidden(sheetIndex) || parent.getWb().isSheetVeryHidden(sheetIndex)) { sheetConfig.setHidden(true); } return sheetConfig; }
From source file:org.wandora.application.tools.extractors.excel.ExcelTopicTreeExtractor.java
License:Open Source License
public void processRow(Row row, TopicMap tm) { int firstColumn = row.getFirstCellNum(); int lastColumn = row.getLastCellNum(); for (int j = firstColumn; j <= lastColumn && !forceStop(); j++) { try {// w w w . j av a 2s . co m Cell cell = row.getCell(j); if (getCellValueAsString(cell) != null) { Topic t = getCellTopic(cell, tm); if (t != null) { for (int k = j - 1; k >= 0; k--) { Topic ct = hierarchy[k]; if (ct != null) { try { if (MAKE_SUPER_SUB_CLASS_RELATION) { Association a = tm .createAssociation(tm.getTopic(XTMPSI.SUPERCLASS_SUBCLASS)); if (a != null) { Topic superClassRole = tm.getTopic(XTMPSI.SUPERCLASS); Topic subClassRole = tm.getTopic(XTMPSI.SUBCLASS); if (superClassRole != null && subClassRole != null) { a.addPlayer(ct, superClassRole); a.addPlayer(t, subClassRole); } } } if (MAKE_CLASS_INSTANCE_RELATION) { t.addType(ct); } if (MAKE_EXCEL_RELATION) { Association a = tm.createAssociation(getDefaultAssociationTypeTopic(tm)); if (a != null) { Topic upperRole = getDefaultUpperRoleTopic(tm); Topic lowerRole = getDefaultLowerRoleTopic(tm); if (upperRole != null && lowerRole != null) { a.addPlayer(ct, upperRole); a.addPlayer(t, lowerRole); } } } if (MAKE_CUSTOM_RELATION) { if (customAssociationTypeSI == null || customUpperRoleSI == null || customLowerRoleSI == null) { requestCustomTypeAndRoles(tm); } if (customAssociationTypeSI != null && customUpperRoleSI != null && customLowerRoleSI != null) { Association a = tm .createAssociation(tm.getTopic(customAssociationTypeSI)); if (a != null) { Topic upperRole = tm.getTopic(customUpperRoleSI); Topic lowerRole = tm.getTopic(customLowerRoleSI); if (upperRole != null && lowerRole != null) { a.addPlayer(ct, upperRole); a.addPlayer(t, lowerRole); } } } } break; } catch (Exception e) { } } } hierarchy[j] = t; for (int k = j + 1; k < 1000; k++) { hierarchy[k] = null; } } } } catch (TopicMapException ex) { log(ex); } catch (Exception ex) { log(ex); } } }
From source file:regression.data.GenerateData.java
/** * Read data from excel//w ww . j a v a 2s. c om * * @param file * @throws FileNotFoundException * @throws IOException * @throws InvalidFormatException */ public void readExcelDataSet(File file) throws FileNotFoundException, IOException, InvalidFormatException { FileInputStream excelFile = new FileInputStream(file); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet firstSheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = firstSheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Cell firstCell = row.getCell(row.getFirstCellNum()); Cell secondCell = row.getCell(row.getFirstCellNum() + 1); if (firstCell.getCellType() == Cell.CELL_TYPE_NUMERIC && secondCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { try { this.points.add(new Point(firstCell.getNumericCellValue(), secondCell.getNumericCellValue())); } catch (Exception e) { System.err.println("Cannot convert data in row: " + row.getRowNum()); } } } this.numberOfInstances = points.size(); }
From source file:regression.data.GenerateData.java
public void createWekaFile(File f) throws FileNotFoundException, IOException, InvalidFormatException { FileInputStream excelFile = new FileInputStream(f); File wekaFile = new File("weka.arff"); XSSFWorkbook workbook = new XSSFWorkbook(f); XSSFSheet firstSheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = firstSheet.iterator(); PrintWriter writer = new PrintWriter(wekaFile); writer.println("@RELATION logistic"); writer.println("@ATTRIBUTE x NUMERIC"); writer.println("@ATTRIBUTE class {1,0}"); writer.println("@DATA"); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Cell firstCell = row.getCell(row.getFirstCellNum()); Cell secondCell = row.getCell(row.getFirstCellNum() + 1); if (firstCell.getCellType() == Cell.CELL_TYPE_NUMERIC && secondCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { try { writer.println(firstCell.getNumericCellValue() + "," + (int) secondCell.getNumericCellValue()); } catch (Exception e) { System.err.println("Cannot convert data in row: " + row.getRowNum()); }// www . j ava 2 s . c om } } writer.close(); this.numberOfInstances = points.size(); }
From source file:regression.data.GenerateLinearData.java
/** * Read data from excel// ww w . jav a 2 s. c om * * @param file * @throws FileNotFoundException * @throws IOException * @throws InvalidFormatException */ public void readExcelDataSet(File file) throws FileNotFoundException, IOException, InvalidFormatException { FileInputStream excelFile = new FileInputStream(file); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet firstSheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = firstSheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Cell firstCell = row.getCell(row.getFirstCellNum()); Cell secondCell = row.getCell(row.getFirstCellNum() + 1); System.out.println(firstCell.getCellType() + " " + secondCell.getCellType()); if (firstCell.getCellType() == Cell.CELL_TYPE_NUMERIC && secondCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { try { this.points.add(new Point(firstCell.getNumericCellValue(), secondCell.getNumericCellValue())); } catch (Exception e) { System.err.println("Cannot convert data in row: " + row.getRowNum()); } } } }
From source file:regression.data.GenerateLinearData.java
public Function testDataFromFile(File file, JTextArea output) throws FileNotFoundException, IOException, InvalidFormatException { FileInputStream excelFile = new FileInputStream(file); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet firstSheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = firstSheet.iterator(); List<Point> points = new ArrayList<>(); Function function = getLastSavedFunction(); output.append("Dla Funkcji:y=x" + function.getFactor().get(0) + "+" + function.getFreeFactor() + " Wartoci testowe przyjmuj" + "\n"); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Cell firstCell = row.getCell(row.getFirstCellNum()); if (firstCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { try { Double score = countFunction(function, firstCell.getNumericCellValue()); this.points.add(new Point(firstCell.getNumericCellValue(), score)); output.append("Dla x=" + firstCell.getNumericCellValue() + " y=" + score + "\n"); } catch (Exception e) { System.err.println("Cannot convert data in row: " + row.getRowNum()); }//w w w. j a v a 2s .co m } } return function; }
From source file:regression.data.GenerateLinearData.java
/** * Read data from excel//from ww w . ja v a2s . c o m * * @param file * @throws FileNotFoundException * @throws IOException * @throws InvalidFormatException */ public List<Point> getExcelDataSet(File file) throws FileNotFoundException, IOException, InvalidFormatException { FileInputStream excelFile = new FileInputStream(file); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet firstSheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = firstSheet.iterator(); List<Point> points = new ArrayList<>(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Cell firstCell = row.getCell(row.getFirstCellNum()); Cell secondCell = row.getCell(row.getFirstCellNum() + 1); // if (secondCell != null) { if ((firstCell.getCellType() == Cell.CELL_TYPE_NUMERIC && secondCell.getCellType() == Cell.CELL_TYPE_NUMERIC)) { try { points.add(new Point(firstCell.getNumericCellValue(), secondCell.getNumericCellValue())); } catch (Exception e) { System.err.println("Cannot convert data in row: " + row.getRowNum()); } } } //else { // if (firstCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // try { // points.add(new Point(firstCell.getNumericCellValue(), 0.0)); // } catch (Exception e) { // System.err.println("Cannot convert data in row: " + row.getRowNum()); // } // } // } // } return points; }
From source file:regression.gui.MainWindow.java
void createWekaFile(String pathToNewWekaFile, File fileExcel) { try {/*from w ww. jav a 2 s . com*/ File wekaFile = new File(pathToNewWekaFile); XSSFWorkbook workbook = new XSSFWorkbook(fileExcel); XSSFSheet firstSheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = firstSheet.iterator(); PrintWriter writer = new PrintWriter(wekaFile); writer.println("@RELATION logistic"); writer.println("@ATTRIBUTE x NUMERIC"); writer.println("@ATTRIBUTE class {1,0}"); writer.println("@DATA"); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Cell firstCell = row.getCell(row.getFirstCellNum()); Cell secondCell = row.getCell(row.getFirstCellNum() + 1); if (firstCell.getCellType() == Cell.CELL_TYPE_NUMERIC && secondCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { try { writer.println( firstCell.getNumericCellValue() + "," + (int) secondCell.getNumericCellValue()); } catch (Exception e) { System.err.println("Cannot convert data in row: " + row.getRowNum()); } } } writer.close(); } catch (Exception ex) { Logger.getLogger(MainWindow.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:ro.dabuno.office.integration.Data.java
private void readExcelFile(File excelFile) throws EncryptedDocumentException, InvalidFormatException, IOException { try (Workbook wb = WorkbookFactory.create(excelFile, null, true)) { Sheet sheet = wb.getSheetAt(0);//from ww w .jav a 2 s . co m if (sheet == null) { throw new IllegalArgumentException( "Provided Microsoft Excel file " + excelFile + " does not have any sheet"); } final int start; final int end; { // read headers Row row = sheet.getRow(0); if (row == null) { throw new IllegalArgumentException("Provided Microsoft Excel file " + excelFile + " does not have data in the first row in the first sheet, " + "but we expect the header data to be located there"); } start = row.getFirstCellNum(); end = row.getLastCellNum(); for (int cellnum = start; cellnum <= end; cellnum++) { Cell cell = row.getCell(cellnum); if (cell == null) { // add null to the headers if there are columns without title in the sheet headers.add(null); log.info("Had empty header for column " + CellReference.convertNumToColString(cellnum)); } else { String value = cell.toString(); headers.add(value); log.info("Had header '" + value + "' for column " + CellReference.convertNumToColString(cellnum)); } } } for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum); if (row == null) { // ignore missing rows continue; } List<String> data = new ArrayList<>(); for (int colnum = start; colnum <= end; colnum++) { Cell cell = row.getCell(colnum); if (cell == null) { // store null-data for empty/missing cells data.add(null); } else { final String value; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // ensure that numeric are formatted the same way as in the Excel file. value = CellFormat.getInstance(cell.getCellStyle().getDataFormatString()) .apply(cell).text; break; default: // all others can use the default value from toString() for now. value = cell.toString(); } data.add(value); } } values.add(data); } } }
From source file:ru.spb.nicetu.tableviewer.server.XlsToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();/*from w w w . j a v a2s .co m*/ out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); int lastNum = -1; while (rows.hasNext()) { Row row = rows.next(); int curNum = row.getRowNum(); if (curNum - lastNum > 1) { for (int i = lastNum + 2; i <= curNum; i++) { out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, i); out.format(" <td colspan=%d style=\"%s\"> </td>%n", (endColumn - firstColumn + 1), styleSimpleContents(null, false)); out.format(" </tr>%n"); } } lastNum = curNum; 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; boolean isNumeric = false; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (content != null && !content.equals("") && (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_FORMULA && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC)) { if (DateUtil.isCellDateFormatted(cell)) { // Date if ("mmm-yy".equals(style.getDataFormatString())) { SimpleDateFormat sdfRus = new SimpleDateFormat("MMM.yy"); content = sdfRus.format(cell.getDateCellValue()); } else if ("h:mm".equals(style.getDataFormatString())) { SimpleDateFormat sdfRus = new SimpleDateFormat("HH:mm"); content = sdfRus.format(cell.getDateCellValue()); } else if (style.getDataFormatString() != null && style.getDataFormatString().contains("mm")) { SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss"); content = sdfRus.format(cell.getDateCellValue()); } else { SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy"); content = sdfRus.format(cell.getDateCellValue()); } } else { // Number if ("- 0".equals(content.trim())) content = " "; else content = "<nobr>" + content.replace(",", " ").replace(".", ",") + "</nobr>"; isNumeric = true; } } if (content == null || content.equals("")) content = " "; } } boolean isInRangeNotFirst = false; for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress rangeAddress = sheet.getMergedRegion(j); if (row.getRowNum() == rangeAddress.getFirstRow() && i == rangeAddress.getFirstColumn()) { if (rangeAddress.getLastRow() - row.getRowNum() > 0) attrs += " rowspan=" + (rangeAddress.getLastRow() - row.getRowNum() + 1); if (rangeAddress.getLastColumn() - i > 0) attrs += " colspan=" + (rangeAddress.getLastColumn() - i + 1); break; } else if (row.getRowNum() >= rangeAddress.getFirstRow() && row.getRowNum() <= rangeAddress.getLastRow() && i >= rangeAddress.getFirstColumn() && i <= rangeAddress.getLastColumn()) { isInRangeNotFirst = true; break; } } if (!isInRangeNotFirst) { out.format(" <td style=\"%s\" %s>%s</td>%n", styleSimpleContents(style, isNumeric), attrs, content); } } // columns out.format(" </tr>%n"); } // rows out.format("</tbody>%n"); }