Example usage for org.apache.poi.ss.usermodel Row getLastCellNum

List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getLastCellNum

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getLastCellNum.

Prototype

short getLastCellNum();

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

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 {/*from w  ww.  j  ava 2  s .c om*/
            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:org.wso2.carbon.dataservices.core.description.query.ExcelQuery.java

License:Open Source License

private String[] extractRowData(Row row) {
    if (row == null || row.getLastCellNum() == -1) {
        return null;
    }//from   w  ww  .  j a va2 s  .c om
    String[] data = new String[row.getLastCellNum()];
    Cell cell;
    for (int i = 0; i < data.length; i++) {
        cell = row.getCell(i);
        if (cell == null) {
            data[i] = "";
            continue;
        }
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            data[i] = cell.getRichStringCellValue().getString();
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            data[i] = "";
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            data[i] = String.valueOf(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            data[i] = "{formula}";
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            data[i] = processNumericValue(cell.getNumericCellValue());
            break;
        }
    }
    return data;
}

From source file:org.wurtele.ifttt.watchers.WorkTimesWatcher.java

License:Open Source License

private void processFile(Path input) {
    logger.info("Updating " + output);

    try (Workbook wb = new XSSFWorkbook();
            OutputStream out = Files.newOutputStream(output, StandardOpenOption.CREATE,
                    StandardOpenOption.TRUNCATE_EXISTING)) {
        Sheet sheet = wb.createSheet("Time Sheet");
        List<WorkDay> days = new ArrayList<>();
        DateFormat df = new SimpleDateFormat("MMMM dd, yyyy 'at' hh:mma");
        for (String line : Files.readAllLines(input)) {
            String[] data = line.split(";");
            LocationType type = LocationType.valueOf(data[0].toUpperCase());
            Date time = df.parse(data[1]);
            Date day = DateUtils.truncate(time, Calendar.DATE);
            WorkDay wd = new WorkDay(day);
            if (days.contains(wd))
                wd = days.get(days.indexOf(wd));
            else//from  w  ww .j a v  a  2s  .c  o  m
                days.add(wd);
            wd.getTimes().add(new WorkTime(time, type));
        }

        CreationHelper helper = wb.getCreationHelper();
        Font bold = wb.createFont();
        bold.setBoldweight(Font.BOLDWEIGHT_BOLD);

        CellStyle dateStyle = wb.createCellStyle();
        dateStyle.setDataFormat(helper.createDataFormat().getFormat("MMMM d, yyyy"));
        CellStyle timeStyle = wb.createCellStyle();
        timeStyle.setDataFormat(helper.createDataFormat().getFormat("h:mm AM/PM"));
        CellStyle headerStyle = wb.createCellStyle();
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headerStyle.setFont(bold);
        CellStyle totalStyle = wb.createCellStyle();
        totalStyle.setAlignment(CellStyle.ALIGN_RIGHT);

        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("DATE");
        header.getCell(0).setCellStyle(headerStyle);

        Collections.sort(days);
        for (int r = 0; r < days.size(); r++) {
            WorkDay day = days.get(r);
            Row row = sheet.createRow(r + 1);
            row.createCell(0).setCellValue(day.getDate());
            row.getCell(0).setCellStyle(dateStyle);
            Collections.sort(day.getTimes());
            for (int c = 0; c < day.getTimes().size(); c++) {
                WorkTime time = day.getTimes().get(c);
                if (sheet.getRow(0).getCell(c + 1) != null
                        && !sheet.getRow(0).getCell(c + 1).getStringCellValue().equals(time.getType().name())) {
                    throw new Exception("Invalid data");
                } else if (sheet.getRow(0).getCell(c + 1) == null) {
                    sheet.getRow(0).createCell(c + 1).setCellValue(time.getType().name());
                    sheet.getRow(0).getCell(c + 1).setCellStyle(headerStyle);
                }
                row.createCell(c + 1).setCellValue(time.getTime());
                row.getCell(c + 1).setCellStyle(timeStyle);
            }
        }

        int totalCol = header.getLastCellNum();
        header.createCell(totalCol).setCellValue("TOTAL");
        header.getCell(totalCol).setCellStyle(headerStyle);

        for (int r = 0; r < days.size(); r++) {
            sheet.getRow(r + 1).createCell(totalCol).setCellValue(days.get(r).getTotal());
            sheet.getRow(r + 1).getCell(totalCol).setCellStyle(totalStyle);
        }

        for (int c = 0; c <= totalCol; c++) {
            sheet.autoSizeColumn(c);
        }

        wb.write(out);
    } catch (Exception e) {
        logger.error("Failed to update " + output, e);
    }
}

From source file:Parser.ExcelParser.java

License:Open Source License

public String[] getHeaders(String selectedSheetArg)
        throws FileNotFoundException, IOException, InvalidFormatException {
    selectedSheet = selectedSheetArg;/*from w  ww  .jav  a2  s  . c  om*/
    listModel = new DefaultListModel();
    List<String> listHeaders = new ArrayList();

    Row row;
    Sheet sheet = wb.getSheet(selectedSheet);
    row = sheet.getRow(0);
    for (int j = 0; j < row.getLastCellNum(); j++) {
        if (row.getCell(j) == null || row.getCell(j).getStringCellValue().isEmpty()
                || row.getCell(j).getStringCellValue() == null) {
            break;
        }
        listHeaders.add(row.getCell(j).getStringCellValue());
        listModel.addElement(row.getCell(j).getStringCellValue());

    }
    return listHeaders.toArray(new String[listHeaders.size()]);
}

From source file:Parser.ExcelParser.java

License:Open Source License

public Integer getColumnCount() throws FileNotFoundException, IOException, InvalidFormatException {
    Sheet sheet = wb.getSheet(sheetName);
    Row row = sheet.getRow(0);
    int numColumns = row.getLastCellNum();
    return numColumns;
}

From source file:PlacementFormatter.Controller.FileController.java

/**
 *
 * @param filepath/*from  w w w.  ja  v  a2  s .  c o  m*/
 * @throws IOException
 */
public static void formatFile(PlacementFile filepath) throws IOException {

    //Creates instance  for reading xls, workbook,sheet, FileInputStream
    InputStream ExcelFileToRead = new FileInputStream(filepath.getFilepath());
    XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
    XSSFSheet sheet = wb.getSheetAt(0);

    //Creates instances for writing output to xls format.
    String sheetName = "Import";//name of sheet
    HSSFWorkbook outWorkbook = new HSSFWorkbook();
    HSSFSheet outSheet = outWorkbook.createSheet(sheetName);

    //Variables to hold the data without ' and r for the row counter
    String cellReplace;
    int r = 0;

    //Outer and Inner loop for iterating through the workbook
    for (Row row : sheet) {

        HSSFRow outRow = outSheet.createRow(r);

        for (int cn = 0; cn < row.getLastCellNum(); cn++) {
            // If the cell is missing from the file, generate a blank one
            // (Works by specifying a MissingCellPolicy)
            org.apache.poi.ss.usermodel.Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);

            //NumberToTextConverter nc = new NumberToTextConverter.toText();
            //or
            DataFormatter df = new DataFormatter();
            String dataCell = df.formatCellValue(cell);

            //Replaces the single dash located in the data

            cellReplace = dataCell.replace("'", "");

            HSSFCell outCell = outRow.createCell(cn);
            outCell.setCellValue(cellReplace);
            //System.out.println("CELL: " + cn + " --> " + cellReplace);

        } //ends inner loop
        r++;
    } //ends outer loop

    FileOutputStream fileOut = new FileOutputStream(filepath.getFilepath().replace("xlsx", "xls"));
    outWorkbook.write(fileOut);
    fileOut.flush();
    System.out.print("File Exported Correclty");

}

From source file:plugins.excel.client.util.ExcelReader.java

License:Microsoft Reference Source License

private ArrayList<String> getColumnNames(Row rowObject, boolean fromFirstLine) {
    Cell cell = null;/*from w  ww .ja  va 2 s .c  o m*/
    ArrayList<String> columnNames = new ArrayList<String>();
    int columnNameNr = 0;

    // go through all columns
    for (int i = 0; i < rowObject.getLastCellNum(); i++) {
        // grab the cell
        cell = rowObject.getCell(i);

        // if the first line contains the column headers
        if (fromFirstLine) {
            // only accept string type 
            switch (cell.getCellType()) {
            case (Cell.CELL_TYPE_STRING):
            case (Cell.CELL_TYPE_FORMULA):
                String cellVal = cell.getStringCellValue();
                // for redundant column names add an incrementing number
                if (columnNames.contains(cellVal)) {
                    columnNameNr++;
                    columnNames.add(cellVal + columnNameNr);
                } else {
                    columnNames.add(cellVal);
                }
                break;
            // if it's not a string type just create a FieldX name and print a warning
            default:
                columnNames.add("Field" + columnNameNr);
                Debugger.debug(
                        "WARNING: CellType of column #" + (columnNameNr) + " of the first line is no string! "
                                + "Only text can be used as column names. Using 'Field" + columnNameNr
                                + "' as column name");
                columnNameNr++;
                break;
            }
            // the first line contains data, just create FieldX to FieldY as name
        } else {
            columnNames.add("Field" + columnNameNr);
            columnNameNr++;
        }
    }

    return columnNames;
}

From source file:projetrdf.ExcelManager.java

public Datas extractData() throws FileNotFoundException, IOException, InvalidFormatException {
    FileInputStream fip = new FileInputStream(new File(path));
    Workbook workbook = WorkbookFactory.create(fip);
    Sheet firstSheet = workbook.getSheet(sheet);
    Iterator<Row> iterator = firstSheet.iterator();
    List<Individu> li = new ArrayList<>();
    while (iterator.hasNext()) {
        Row nextRow = iterator.next();
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        double[] tabIndividu = new double[nextRow.getLastCellNum()];
        String nomIndividu = "";
        int cpt = 0;
        while (cellIterator.hasNext()) {

            Cell cell = cellIterator.next();
            if (cpt == 0) {
                nomIndividu = cell.getStringCellValue();
            } else {
                tabIndividu[cpt - 1] = Double.parseDouble(cell.getStringCellValue());
            }/*from   w w w. j ava  2 s. c o  m*/
            cpt++;
            if (cell.getStringCellValue().equals("")) {
                break;
            }
        }
        int classe = 0;
        String[] nom = nomIndividu.split("\\.");
        classe = Integer.parseInt(nom[0]) / 100;
        li.add(new Individu(nomIndividu, tabIndividu, classe));
    }
    return new Datas(li);
}

From source file:readers.discount.XmlDiscountReader.java

private void checkSheet(Row row) {
    if (row.getLastCellNum() > 4) {
        throw new ProcessingException("Za ilo kolumn w pliku.");
    }/*from  w ww  .j a v  a  2 s . c  om*/
}

From source file:resources.ministory.MinistoryFormManager.java

private List<MinistoryFormItem> loadAllFromExcel(String filePath) {
    System.out.println("Loading MiniStory Form Excel Database");
    List<MinistoryFormItem> miniFormList = new ArrayList();
    boolean cleanSucess = true;
    try {/*from  w  w  w. j  a  v  a2 s.c o  m*/
        System.out.println("Reading Ministory DB");
        FileInputStream fileStream = checkFileExist(filePath);

        //Get the workbook instance for XLSX file 
        XSSFWorkbook workbook = new XSSFWorkbook(fileStream);
        XSSFSheet spreadsheet = workbook.getSheetAt(0);
        //XSSFRow row;

        final int MY_MINIMUM_COLUMN_COUNT = 12;
        int rowStart = 1;
        int rowEnd = Math.max(1400, spreadsheet.getLastRowNum());
        //Startfrom row 1
        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
            Row r = spreadsheet.getRow(rowNum);
            if (r == null) {
                // This whole row is empty
                continue;
            }
            MinistoryFormItem miniFormTemp = new MinistoryFormItem();
            int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);

            for (int cellNum = 0; cellNum < lastColumn; cellNum++) {
                Cell cell = r.getCell(cellNum, Row.RETURN_BLANK_AS_NULL);
                if (cell == null) {
                    // The spreadsheet is empty in this cell
                    miniFormTemp.setEmptyValue(cellNum);
                } else {
                    // Fill the cell's contents to MiniForm Obj
                    miniFormTemp.setValue(cellNum, cell);
                }
            }

            miniFormList.add(miniFormTemp);

        }

        fileStream.close();
    } catch (FileNotFoundException e) {
        cleanSucess = false;
        e.printStackTrace();
    } catch (IOException e) {
        cleanSucess = false;
        e.printStackTrace();
    } catch (Exception ex) {
        cleanSucess = false;
        Logger.getLogger(MinistoryFormItem.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (cleanSucess) {
            System.out.println("All Read Without Error");
        } else {
            System.out.println("There were some Error(s)");
        }
    }

    return miniFormList;
}