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.joeffice.spreadsheet.sheet.SheetTableModel.java

License:Apache License

public void insertColumn(int columnIndex) {
    for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        if (row != null) {
            for (int j = row.getLastCellNum(); j > columnIndex; j--) {
                CellUtils.copyCellToColumn(row, row.getCell(j), j + 1);
            }//from  ww  w  . j  a  va 2 s .c  o  m
            row.createCell(columnIndex);
        }
    }
    fireTableStructureChanged();
}

From source file:org.jplus.compare.excel.service.CompareService.java

public static List<OptionBean> getOptionBeansFromExcel(String excelPath, String sheetName) {
    Map<String, OptionBean> optionMap = getOptionMap();
    List<OptionBean> optionBeans = new ArrayList<OptionBean>();
    InputStream inputStream = null;
    try {/*from   w ww  .  j av  a2 s  . c om*/
        File file = new File(excelPath);
        inputStream = new FileInputStream(file);
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheet(sheetName);
        Row row = BaseExcelService.getRow(sheet, 0);
        short lastCellNum = row.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            Cell cell = row.getCell(i);
            if (cell != null) {
                String string = BaseExcelService.getString(cell);
                if (!ObjectHelper.isNullOrEmptyString(string)) {
                    OptionBean optionBean = new OptionBean();
                    optionBean.setItemName(string);
                    OptionBean get = optionMap.get(string);
                    if (ObjectHelper.isNotEmpty(get)) {
                        optionBean.setCompare(get.getCompare());
                        optionBean.setThresholdValue(get.getThresholdValue());
                    }
                    optionBeans.add(optionBean);
                }
            }
        }

    } catch (IOException ex) {
        Logger.getLogger(CompareService.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (inputStream != null) {
            try {
                inputStream.close();
            } catch (IOException ex) {
                Logger.getLogger(CompareService.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    return optionBeans;
}

From source file:org.jplus.compare.excel.service.CompareService.java

public static String[] getExcelHeader(Sheet sheet) {
    List<String> header = new ArrayList<String>();
    Row row = BaseExcelService.getRow(sheet, 0);
    short lastCellNum = row.getLastCellNum();
    for (int i = 0; i < lastCellNum; i++) {
        Cell cell = row.getCell(i);//from   w  ww  .  j av  a  2 s . c o m
        if (cell != null) {
            String string = BaseExcelService.getString(cell);
            header.add(string);
        }
    }
    return header.toArray(new String[] {});

}

From source file:org.lisapark.octopus.util.json.ExcelSardineUtils.java

License:Open Source License

public static void main(String[] args) {

    Map<String, Integer> prodCellIndexMap = Maps.newHashMap();
    prodCellIndexMap.put(SHOP, 0);/*from   w w  w .ja v a 2  s . c om*/
    prodCellIndexMap.put(SHIFT, 0);
    prodCellIndexMap.put(MACHINE, 0);
    prodCellIndexMap.put(PRODUCT, 0);
    prodCellIndexMap.put(PRODUCT_TYPE, 0);
    prodCellIndexMap.put(MATERIAL_TYPE, 0);
    prodCellIndexMap.put(RAW_MATERIAL, 4);
    prodCellIndexMap.put(TOTAL_MATERIALS, 5);
    prodCellIndexMap.put(TOTAL_PRODUCTS, 6);

    Map<String, Integer> wrhCellIndexMap = Maps.newHashMap();
    wrhCellIndexMap.put(WAREHOUSE, 1);
    wrhCellIndexMap.put(ITEM, 1);
    wrhCellIndexMap.put(ITEM_TYPE, 1);
    wrhCellIndexMap.put(BEGINING, 0);
    wrhCellIndexMap.put(INCOMING, 1);
    wrhCellIndexMap.put(OUTGOING, 2);
    wrhCellIndexMap.put(ENDING, 3);

    try {
        String excelFile = "http://173.72.110.131:8080/WebDavServer/iPlast/Warehouse/";

        // Get all xml files
        Sardine sardine = SardineFactory.begin("", "");
        List<DavResource> resources = sardine.getResources(excelFile);

        for (DavResource res : resources) {
            String url = res.getPath();
            //getAbsoluteUrl();
            if (res.isDirectory()) {
                continue;
            } else {
                Map<String, String> props = res.getCustomProps();
                if (props.get(PROCESSED) == null) {
                    InputStream isData = sardine.get(url);
                    HSSFWorkbook book = new HSSFWorkbook(isData);

                    int index = 0;
                    int increament = 1;
                    if (book.getNumberOfSheets() > index) {
                        if (increament == 0) {
                            //                                increament = PROD_OUTLINE_INCREAMENT;
                            increament = WRH_OUTLINE_INCREAMENT;
                        }
                        Sheet sheet = book.getSheetAt(index);
                        if (sheet == null) {
                            continue;
                        }

                        // Iterate through the rows.
                        int splitRowNumber = 0;

                        if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) {
                            splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition();
                        }

                        Map<String, Object> rowMap = Maps.newHashMap();

                        int start = 2;
                        Row dateRow = sheet.getRow(8);
                        int end = dateRow.getLastCellNum();

                        for (int dateShift = start; dateShift < end - 4; dateShift = dateShift + 4) {

                            rowMap.put(DATE, formatDate(dateRow.getCell(dateShift).getStringCellValue()));
                            System.out.println(dateRow.getCell(dateShift).getStringCellValue());

                            Sheet _sheet = book.getSheetAt(index);

                            for (Iterator<Row> rowsIt = _sheet.rowIterator(); rowsIt.hasNext();) {
                                Row row = rowsIt.next();
                                if (row.getPhysicalNumberOfCells() <= 0 || row.getRowNum() < splitRowNumber) {
                                    continue;
                                }

                                Cell cell = row.getCell(1);
                                int indent = cell.getCellStyle().getIndention();
                                int absIndent = indent / increament;
                                //                                
                                if (processRowWrhSs(rowMap, row, wrhCellIndexMap, absIndent, dateShift)) {
                                    System.out.println(rowMap);
                                }
                            }
                        }
                    }
                    props.put(PROCESSED, TRUE);
                    sardine.setCustomProps(url, props, null);
                } else {
                    System.out.println("Property PROCESSED: " + props.get(PROCESSED));
                    List<String> removeProps = new ArrayList<String>(1);
                    removeProps.add(PROCESSED);

                    sardine.setCustomProps(url, null, removeProps);
                }
                break;
            }
        }
    } catch (FileNotFoundException ex) {
        Exceptions.printStackTrace(ex);
    } catch (IOException ex) {
        Exceptions.printStackTrace(ex);
    }
}

From source file:org.mifos.dmt.excel.columnValidator.ColumnStructure.java

License:Open Source License

public Workbook processSheetStructure() throws DMTException {
    ArrayList<String> sheetsToBeProcessed = getSheetsToBeProcessed();
    Iterator<String> itr = sheetsToBeProcessed.iterator();
    while (itr.hasNext()) {
        sheetPass = true;/*from   ww  w. j a  v a  2 s.  c  o m*/
        String sheetName = (String) itr.next();
        Sheet baseComparisonSheet = baseworkbook.getSheet(sheetName);
        Row baseComparisonRow = baseComparisonSheet.getRow(0);
        Sheet compareToSheet = workbook.getSheet(sheetName);
        Row comparisonRow = compareToSheet.getRow(0);
        short lastCell = comparisonRow.getLastCellNum();
        int j = 0;
        for (short i = 0; i <= lastCell - 1; i++, j++) {
            if (!baseComparisonRow.getCell(j).toString().equals(comparisonRow.getCell(j).toString())) {
                sheetPass = false;
                logger.error("Following Column value for the sheet " + comparisonRow.getSheet().getSheetName()
                        + " does not match with base template " + comparisonRow.getCell(j).toString());
            }
        }

        if (!sheetPass) {
            logger.error("Failed column validations for the sheet");
            throw new DMTException("Failed column validations for the sheet");
        }
    }
    logger.info("Excel sheet passed structural validations");
    return workbook;

}

From source file:org.ojbc.adapters.analyticsstaging.custody.service.DescriptionCodeLookupFromExcelService.java

License:RPL License

private void loadMapOfCodeMaps(String codeTableExcelFilePath) throws FileNotFoundException, IOException {
    log.info("Recache code table maps.");

    mapOfCodeMaps = new HashMap<String, Map<String, Integer>>();

    FileInputStream inputStream = new FileInputStream(new File(codeTableExcelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);

        Map<String, Integer> codePkMap = new HashMap<String, Integer>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);

            if (row.getCell(row.getLastCellNum() - 1).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                row.getCell(row.getLastCellNum() - 1).setCellType(Cell.CELL_TYPE_STRING);
            }//w ww.j a  va  2s  . co  m

            String codeOrDescription = StringUtils
                    .upperCase(row.getCell(row.getLastCellNum() - 1).getStringCellValue());
            Integer pkId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue();
            codePkMap.put(codeOrDescription, pkId);
        }

        mapOfCodeMaps.put(sheet.getSheetName(), codePkMap);

    }

    workbook.close();
    inputStream.close();
}

From source file:org.ojbc.adapters.analyticsstaging.custody.service.SimpleExcelReaderExample.java

License:RPL License

public static void main(String[] args) throws IOException {
    Map<String, Map<String, Integer>> mapOfCodeMaps = new HashMap<String, Map<String, Integer>>();

    String excelFilePath = "src/test/resources/codeSpreadSheets/PimaCountyAnalyticsCodeTables.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        System.out.println("Sheet Name: " + sheet.getSheetName());

        Map<String, Integer> codePkMap = new HashMap<String, Integer>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);

            if (row.getCell(row.getLastCellNum() - 1).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                row.getCell(row.getLastCellNum() - 1).setCellType(Cell.CELL_TYPE_STRING);
            }/* w w w .  j av a 2s .  c o  m*/

            String codeOrDescription = row.getCell(row.getLastCellNum() - 1).getStringCellValue();

            Integer pkId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue();
            codePkMap.put(codeOrDescription, pkId);
        }

        mapOfCodeMaps.put(sheet.getSheetName(), codePkMap);

    }

    workbook.close();
    inputStream.close();
}

From source file:org.openelis.bean.QcChartReport1Bean.java

License:Open Source License

private void finishSheet(HSSFSheet sheet, HSSFWorkbook wb, String qcName, String qcType, String sheetName) {
    int i, columnIndex;
    ArrayList<DictionaryDO> tempQcColumns;
    DictionaryDO dict;//from  ww w .  ja  v  a 2  s. co m
    HashSet<Integer> emptyColumns;
    Name rangeName;
    Row row;
    String rangeFormula;

    if (qcColumns != null && !qcColumns.isEmpty())
        row = sheet.getRow(32);
    else
        row = sheet.getRow(3);
    emptyColumns = new HashSet<Integer>();
    for (i = 0; i < row.getLastCellNum(); i++) {
        if (i >= maxChars.size() || maxChars.get(i) == 0)
            emptyColumns.add(i);
    }

    setHeaderCells(sheet, qcName, qcType, sheetName);

    if (qcColumns != null && !qcColumns.isEmpty()) {
        tempQcColumns = new ArrayList<DictionaryDO>();
        tempQcColumns.addAll(qcColumns);
        for (i = tempQcColumns.size() - 1; i > -1; i--) {
            if (emptyColumns.contains(i + 5)) {
                tempQcColumns.remove(i);
                removeColumn(sheet, i + 5);
                maxChars.remove(i + 5);
            }
        }

        rangeName = getName(wb, sheet, "RowNumber");
        if (rangeName == null) {
            rangeName = wb.createName();
            rangeName.setSheetIndex(wb.getSheetIndex(sheet));
            rangeName.setNameName("RowNumber");
        }
        rangeFormula = sheet.getSheetName() + "!$" + CellReference.convertNumToColString(0) + "$33:" + "$"
                + CellReference.convertNumToColString(0) + "$" + (sheet.getLastRowNum() + 1);
        rangeName.setRefersToFormula(rangeFormula);

        /*
         * Create named ranges for the graph to be able to locate the appropriate
         * data
         */
        columnIndex = 5;
        for (i = 0; i < tempQcColumns.size(); i++) {
            dict = tempQcColumns.get(i);
            if (!DataBaseUtil.isEmpty(dict.getCode())) {
                rangeName = getName(wb, sheet, dict.getCode());
                if (rangeName == null) {
                    rangeName = wb.createName();
                    rangeName.setSheetIndex(wb.getSheetIndex(sheet));
                    rangeName.setNameName(dict.getCode());
                }
                rangeFormula = rangeName.getRefersToFormula();
                if (rangeFormula != null && rangeFormula.length() > 0
                        && !"$A$2".equals(rangeFormula.substring(rangeFormula.indexOf("!") + 1)))
                    rangeFormula += ",";
                else
                    rangeFormula = "";
                rangeFormula += sheet.getSheetName() + "!$" + CellReference.convertNumToColString(columnIndex)
                        + "$33:" + "$" + CellReference.convertNumToColString(columnIndex) + "$"
                        + (sheet.getLastRowNum() + 1);
                rangeName.setRefersToFormula(rangeFormula);
            }
            columnIndex++;
        }
        /*
         * make each column wide enough to show the longest string in it; the
         * width for each column is set as the maximum number of characters in
         * that column multiplied by 256; this is because the default width of
         * one character is 1/256 units in Excel
         */
        for (i = 5; i < maxChars.size(); i++)
            sheet.setColumnWidth(i, maxChars.get(i) * 256);
    } else if (worksheetHeaders != null && worksheetHeaders.size() > 0) {
        /*
         * make each column wide enough to show the longest string in it; the
         * width for each column is set as the maximum number of characters in
         * that column multiplied by 256; this is because the default width of
         * one character is 1/256 units in Excel
         */
        for (i = 0; i < maxChars.size(); i++)
            sheet.setColumnWidth(i, maxChars.get(i) * 256);
    }

    wb.setSheetName(wb.getSheetIndex(sheet), sheetName);
    sheet.setForceFormulaRecalculation(true);
    maxChars.clear();
}

From source file:org.openelis.bean.QcChartReport1Bean.java

License:Open Source License

private void removeColumn(HSSFSheet sheet, Integer columnIndex) {
    int i, j;//from  w ww.java  2s .  c o m
    Cell cell;
    Row row;

    for (i = 31; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        cell = row.getCell(columnIndex);
        if (cell != null)
            row.removeCell(row.getCell(columnIndex));
        for (j = columnIndex + 1; j < row.getLastCellNum(); j++) {
            cell = row.getCell(j);
            if (cell != null)
                ((HSSFRow) row).moveCell((HSSFCell) cell, (short) (j - 1));
        }
    }
}

From source file:org.openelis.bean.WorksheetExcelHelperBean.java

License:Open Source License

@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
private int createResultCellsForFormat(HSSFSheet sheet, Row row, Row tRow, String nameIndexPrefix,
        HashMap<String, String> cellNames, WorksheetViewDO wVDO, WorksheetAnalysisViewDO waVDO,
        ArrayList<WorksheetResultViewDO> wrList, boolean isEditable,
        HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>> apMap) {
    int c, i, r;/*  ww  w .ja  v  a  2 s  .  c  o m*/
    String cellNameIndex, name;
    ArrayList<AnalyteParameterViewDO> anaParams, apList;
    DecimalFormat df;
    HashMap<Integer, ArrayList<AnalyteParameterViewDO>> pMap;
    Cell cell, tCell;
    Name cellName;
    AnalyteParameterViewDO apVDO;

    df = new DecimalFormat();
    df.setGroupingUsed(false);
    df.setMaximumFractionDigits(10);

    i = 0;
    r = row.getRowNum();
    for (WorksheetResultViewDO wrVDO : wrList) {
        if (i != 0) {
            row = sheet.createRow(r);
            for (c = 0; c < 7; c++) {
                cell = row.createCell(c);
                cell.setCellStyle(styles.get("row_no_edit"));
            }
        }

        cellNameIndex = nameIndexPrefix + "." + i;

        // analyte
        cell = row.createCell(7);
        cell.setCellStyle(styles.get("row_no_edit"));
        cell.setCellValue(wrVDO.getAnalyteName());

        // reportable
        cell = row.createCell(8);
        cell.setCellStyle(styles.get("row_no_edit"));
        cell.setCellValue(wrVDO.getIsReportable());
        cellName = sheet.getWorkbook().createName();
        cellName.setNameName("analyte_reportable." + cellNameIndex);
        cellName.setRefersToFormula(
                "Worksheet!$" + CellReference.convertNumToColString(8) + "$" + (row.getRowNum() + 1));

        apVDO = null;
        for (c = 9; c < tRow.getLastCellNum() && c < 39; c++) {
            tCell = tRow.getCell(c);

            cell = row.createCell(c);
            if (isEditable)
                cell.setCellStyle(tCell.getCellStyle());
            else
                cell.setCellStyle(styles.get("row_no_edit"));
            name = cellNames.get(
                    sheet.getSheetName() + "!$" + CellReference.convertNumToColString(tCell.getColumnIndex())
                            + "$" + (tCell.getRowIndex() + 1));
            if (name != null) {
                cellName = row.getSheet().getWorkbook().createName();
                cellName.setNameName(name + "." + cellNameIndex);
                cellName.setRefersToFormula(
                        sheet.getSheetName() + "!$" + CellReference.convertNumToColString(cell.getColumnIndex())
                                + "$" + (row.getRowNum() + 1));
            }
            if (tCell.getCellType() == Cell.CELL_TYPE_FORMULA && tCell.getCellFormula() != null) {
                cell.setCellFormula(tCell.getCellFormula());
            } else {
                setCellValue(cell, wrVDO.getValueAt(c - 9));
            }
            if ("p1".equals(name) || "p2".equals(name) || "p3".equals(name) || "p_1".equals(name)
                    || "p_2".equals(name) || "p_3".equals(name)) {
                if (wrVDO.getValueAt(c - 9) == null) {
                    pMap = apMap.get("T" + waVDO.getTestId());
                    if (pMap == null) {
                        pMap = new HashMap<Integer, ArrayList<AnalyteParameterViewDO>>();
                        apMap.put("T" + waVDO.getTestId(), pMap);
                        try {
                            anaParams = analyteParameter.fetchByActiveDate(waVDO.getTestId(),
                                    Constants.table().TEST, wVDO.getCreatedDate().getDate());
                            for (AnalyteParameterViewDO anaParam : anaParams) {
                                apList = pMap.get(anaParam.getAnalyteId());
                                if (apList == null) {
                                    apList = new ArrayList<AnalyteParameterViewDO>();
                                    pMap.put(anaParam.getAnalyteId(), apList);
                                }
                                apList.add(anaParam);
                            }
                        } catch (NotFoundException nfE) {
                            continue;
                        } catch (Exception anyE) {
                            log.log(Level.SEVERE,
                                    "Error retrieving analyte parameters for an analysis on worksheet.", anyE);
                            continue;
                        }
                    }

                    apList = pMap.get(wrVDO.getAnalyteId());
                    apVDO = null;
                    if (apList != null && apList.size() > 0) {
                        for (AnalyteParameterViewDO ap : apList) {
                            if (ap.getUnitOfMeasureId() == null
                                    || ap.getUnitOfMeasureId().equals(waVDO.getUnitOfMeasureId())) {
                                if (ap.getUnitOfMeasureId() != null) {
                                    apVDO = ap;
                                    break;
                                } else if (apVDO == null) {
                                    apVDO = ap;
                                }
                            }
                        }
                    }
                    if (apVDO != null) {
                        if (("p1".equals(name) || "p_1".equals(name)) && apVDO.getP1() != null) {
                            setCellValue(cell, df.format(apVDO.getP1()));
                        } else if (("p2".equals(name) || "p_2".equals(name)) && apVDO.getP2() != null) {
                            setCellValue(cell, df.format(apVDO.getP2()));
                        } else if (("p3".equals(name) || "p_3".equals(name)) && apVDO.getP3() != null) {
                            setCellValue(cell, df.format(apVDO.getP3()));
                        }
                    }
                }
            }
        }

        i++;
        r++;
    }

    return r;
}