Example usage for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getLastRowNum

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum.

Prototype

@Override
public int getLastRowNum()

Source Link

Document

Gets the number last row on the sheet.

Usage

From source file:Main3.java

public static void removeRow(HSSFSheet sheet, int rowIndex) {
    int lastRowNum = sheet.getLastRowNum();
    if (rowIndex >= 0 && rowIndex < lastRowNum) {
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
    }/*  www. ja v a2 s  .com*/
    if (rowIndex == lastRowNum) {
        HSSFRow removingRow = sheet.getRow(rowIndex);
        if (removingRow != null) {
            sheet.removeRow(removingRow);
        }
    }
}

From source file:AdminServlet.java

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    adminame = request.getParameter("adminname");
    adpassword = request.getParameter("adminpassword");
    request.setAttribute("adname", adminame);

    try {//ww  w.  java 2  s. com
        FileInputStream file = new FileInputStream(new File("C:\\Users\\mc13082\\Desktop\\form\\new.xls"));
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet worksheet = workbook.getSheetAt(0);
        ArrayList Rows = new ArrayList();

        for (int j = worksheet.getFirstRowNum() + 1; j <= worksheet.getLastRowNum(); j++) {
            ArrayList row = new ArrayList();
            HSSFRow row1 = worksheet.getRow(j);

            HSSFCell cellA1 = row1.getCell(0);
            String a1Val = cellA1.getStringCellValue();
            row.add(a1Val);
            HSSFCell cellB1 = row1.getCell(1);
            String b1Val = cellB1.getStringCellValue();
            row.add(b1Val);
            HSSFCell cellC1 = row1.getCell(2);
            String c1Val = cellC1.getStringCellValue();
            row.add(c1Val);
            HSSFCell cellD1 = row1.getCell(3);
            String d1Val = cellD1.getStringCellValue();
            row.add(d1Val);
            HSSFCell cellE1 = row1.getCell(4);
            String e1Val = cellE1.getStringCellValue();
            row.add(e1Val);
            HSSFCell cellF1 = row1.getCell(5);
            String f1Val = cellF1.getStringCellValue();
            row.add(f1Val);
            HSSFCell cellG1 = row1.getCell(6);
            String g1Val = cellG1.getStringCellValue();
            row.add(g1Val);

            HSSFCell cellH1 = row1.getCell(7);
            String h1Val = cellH1.getStringCellValue();
            row.add(h1Val);

            HSSFCell cellI1 = row1.getCell(8);
            String i1Val = cellI1.getStringCellValue();
            row.add(i1Val);
            Rows.add(row);

        }

        request.setAttribute("results", Rows);
        if (adminame.equals("Admin") && adpassword.equals("admin")) {
            RequestDispatcher rd = request.getRequestDispatcher("adminHome.jsp");
            rd.forward(request, response);
        } else {
            out.println("login failed");
            RequestDispatcher rd = request.getRequestDispatcher("afailed.jsp");
            rd.include(request, response);
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:SystemPropertiesParser.java

License:Apache License

public static List<SystemProperty> parse() throws IOException {
    URL xls = SystemPropertiesParser.class.getResource("SystemProperties.xls");
    FileInputStream fis = new FileInputStream(new File(xls.getPath()));
    HSSFWorkbook workbook = new HSSFWorkbook(fis);
    HSSFSheet sheet = workbook.getSheetAt(0);
    int numRows = sheet.getLastRowNum();
    List<SystemProperty> systemProperties = new ArrayList<>(numRows);
    for (int i = 1; i < numRows; i++) {
        HSSFRow row = sheet.getRow(i);/*from   www . java  2 s.c o m*/
        SystemProperty.Builder builder = SystemProperty.newSystemProperty();
        builder.key(row.getCell(0).getStringCellValue());
        HSSFCell cell = row.getCell(1);
        if (cell != null) {
            builder.desc(cell.getStringCellValue());
        }
        List<SystemProperty.GithubInfo> github = new ArrayList<>();
        String uses = row.getCell(2).getStringCellValue();
        String[] arr = uses.split(" ");
        for (String str : arr) {
            String repo = str.split("/")[0];
            String path = str.substring(repo.length() + 1);
            SystemProperty.GithubInfo githubInfo = new SystemProperty.GithubInfo(repo, path);
            github.add(githubInfo);
        }
        builder.github(github);
        systemProperties.add(builder.build());
    }
    fis.close();
    Collections.sort(systemProperties, (o1, o2) -> o1.key.compareToIgnoreCase(o2.key));
    return systemProperties;
}

From source file:at.htlpinkafeld.beans.AlleAbwesenheitenBean.java

/**
 * xls post processing/*  w  ww.j  a v a  2 s.c om*/
 *
 * @param document xls document
 */
public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);

    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);

        sheet.autoSizeColumn(i);
    }

    HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
    bottomRow.createCell(0)
            .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

}

From source file:at.htlpinkafeld.beans.JahresuebersichtBean.java

/**
 * post processes the XLS for creating//from  ww  w.  j a  v  a  2 s . c o  m
 *
 * @param document xls-doc
 */
public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);

    sheet.shiftRows(0, sheet.getLastRowNum(), 2);

    HSSFRow topRow = sheet.createRow(0);

    topRow.createCell(0).setCellValue("Jahresbersicht - " + selectedYear.getYear());
    topRow.createCell(3).setCellValue("von " + selectedUser.getPersName());
    sheet.createRow(1).createCell(0).setCellValue(" ");

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

    HSSFRow header = sheet.getRow(2);
    HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);

        cell = footer.getCell(i);
        cell.setCellStyle(cellStyle);

        sheet.autoSizeColumn(i);
    }

    HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
    bottomRow.createCell(0)
            .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

}

From source file:at.htlpinkafeld.beans.UserDetailsBean.java

public void postProcessXLS(Object document) {
        HSSFWorkbook wb = (HSSFWorkbook) document;
        HSSFSheet sheet = wb.getSheetAt(0);

        sheet.shiftRows(0, sheet.getLastRowNum(), 2);

        HSSFRow topRow = sheet.createRow(0);

        topRow.createCell(0)/*w ww  . j a  v  a2s . c o m*/
                .setCellValue("Monatsbersicht - " + selectedDate.format(DateTimeFormatter.ofPattern("MM.yyyy")));
        topRow.createCell(7).setCellValue("von " + selectedUser);
        sheet.createRow(1).createCell(0).setCellValue(" ");

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));

        HSSFRow header = sheet.getRow(2);
        HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = header.getCell(i);
            cell.setCellStyle(cellStyle);

            cell = footer.getCell(i);
            cell.setCellStyle(cellStyle);

            sheet.autoSizeColumn(i);
        }

        HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
        bottomRow.createCell(0)
                .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

    }

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

/**
 * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>.
 * Only the sheet on the given sheetIndex is copied.
        /*from  ww  w. ja v  a2s . com*/
 * @param igrid the XMA model where to copy the data
 * @param book the POI represntation of the data
 * @param sheetIndex the index of the sheet to copy
 * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges
 */
public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) {
    GridWM grid = (GridWM) igrid;
    try {
        List errorList = new ArrayList();
        grid.setSheetName(book.getSheetName(sheetIndex));

        grid.colors.clear();
        grid.initBuildInColors();
        short ic = GridWM.HSSF_FIRST_COLOR_INDEX;
        HSSFPalette palette = book.getCustomPalette();
        for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) {
            grid.colors.add(ic, new GridColor(color.getTriplet()));
        }

        grid.fonts.clear();
        int numFonts = book.getNumberOfFonts();
        if (numFonts > 4) {
            // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt()
            numFonts += 1;
        }
        for (short i = 0; i < numFonts; i++) {
            HSSFFont font = book.getFontAt(i);
            byte fontstyle = GridFont.FONT_NORML;
            if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD)
                fontstyle |= GridFont.FONT_BOLD;
            if (font.getItalic())
                fontstyle |= GridFont.FONT_ITALIC;
            grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor()));
        }

        grid.styles.clear();
        for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) {
            HSSFCellStyle style = book.getCellStyleAt(i);
            grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor()));
        }

        grid.namedRanges.clear();
        for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) {
            HSSFName name = book.getNameAt(i);
            String rangeName = name.getNameName();
            String rangeRef = null;
            try { // ranges not defined but referenced by formulas have a name but no reference in HSSF
                rangeRef = name.getReference();
            } catch (Exception exc) {
                errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName))
                        .setCode(GridWM.CODE_inconsistentRange));
            }
            if (rangeRef != null) {
                try {
                    GridRange range = grid.getJeksDelegate().toRange(rangeRef);
                    range.setKey(rangeName);
                    grid.namedRanges.put(rangeName, range);
                } catch (Exception exc) {
                    errorList.add(new SysException(exc,
                            ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef))
                                    .setCode(GridWM.CODE_unsupportedReference));
                }
            }
        }

        grid.rows.clear();
        grid.cols.clear();
        grid.cells.clear();
        grid.delegate = new GridJeksDelegate(grid);
        HSSFSheet sheet = book.getSheetAt(sheetIndex);
        int firstColNum = Integer.MAX_VALUE;
        int lastColNum = Integer.MIN_VALUE;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null)
                continue;
            if (row.getFirstCellNum() >= 0)
                firstColNum = Math.min(firstColNum, row.getFirstCellNum());
            lastColNum = Math.max(lastColNum, row.getLastCellNum());
            if (lastColNum > 255)
                lastColNum = 255;
            for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                HSSFCell hssfcell = row.getCell(j);
                if (hssfcell == null)
                    continue;
                GridCell gridcell = grid.getOrCreateCell(i, j);
                switch (hssfcell.getCellType()) {
                case HSSFCell.CELL_TYPE_BLANK:
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    gridcell.setValue(hssfcell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    // TODO: recherche error text
                    byte errorCode = hssfcell.getErrorCellValue();
                    //                    gridcell.setValue(errorCode);
                    gridcell.setValue("#ERROR");
                    errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord",
                            grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode)))
                                    .setCode(GridWM.CODE_errorRecord));
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    String formula = null;
                    try {
                        formula = hssfcell.getCellFormula();
                        gridcell.setFormula(formula);
                    } catch (SysException e) {
                        if (formula != null)
                            gridcell.setValue("=" + formula); //set it as text without interpretation
                        errorList.add(e);
                    }
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (isDateCell(book, hssfcell)) {
                        gridcell.setValue(hssfcell.getDateCellValue());
                    } else {
                        gridcell.setValue(hssfcell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    gridcell.setValue(hssfcell.getStringCellValue());
                    break;
                default:
                    throw new SysException("unknown cell type " + hssfcell.getCellType());
                }
                gridcell.setEditable(!hssfcell.getCellStyle().getLocked());
                gridcell.setStyle(hssfcell.getCellStyle().getIndex());
            }
        }

        final int scalefactor = 256 / 7; // empirically testet
        //        int width = sheet.getDefaultColumnWidth();  // returns nonsense
        //        width = width/scalefactor;
        //        grid.setDefaultColumnWidth(width);
        for (short i = (short) firstColNum; i <= lastColNum; i++) {
            int width = sheet.getColumnWidth(i);
            width = width / scalefactor;
            grid.getOrCreateColumn(i).setWidth(width);
        }

        if (firstColNum == Integer.MAX_VALUE)
            firstColNum = 0;
        if (lastColNum == Integer.MIN_VALUE)
            lastColNum = 0;
        grid.setMaxRange(
                new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum));
        grid.setVisibleRange(grid.getMaxRange());
        return errorList;
    } finally {
        grid.handle(grid.new GridReloadEvent());
    }
}

From source file:be.vds.jtbdive.client.view.core.dive.profile.DiveProfileExcelParser.java

License:Open Source License

public DiveProfile read(File file) throws IOException {
    DiveProfile dp = new DiveProfile();
    Map<Double, Double> depthEntries = new HashMap<Double, Double>();
    Set<Double> ascentWarnings = new HashSet<Double>();
    Set<Double> decoWarnings = new HashSet<Double>();
    Set<Double> remainingBottomTimeWarnings = new HashSet<Double>();
    Set<Double> decoEntries = new HashSet<Double>();

    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
    HSSFSheet sheet = wb.getSheetAt(0);

    int rowMax = sheet.getLastRowNum();
    for (int i = 1; i <= rowMax; i++) {
        HSSFRow row = sheet.getRow(i);//w  w  w  . j  a va2  s.  co m
        double second = row.getCell(0).getNumericCellValue();
        depthEntries.put(second, row.getCell(1).getNumericCellValue());

        boolean b = row.getCell(2).getBooleanCellValue();
        if (b)
            ascentWarnings.add(second);

        b = row.getCell(3).getBooleanCellValue();
        if (b)
            remainingBottomTimeWarnings.add(second);

        b = row.getCell(4).getBooleanCellValue();
        if (b)
            decoWarnings.add(second);

        b = row.getCell(5).getBooleanCellValue();
        if (b)
            decoEntries.add(second);
    }

    dp.setDepthEntries(depthEntries);

    if (ascentWarnings.size() > 0)
        dp.setAscentWarnings(ascentWarnings);

    if (decoWarnings.size() > 0)
        dp.setDecoCeilingWarnings(decoWarnings);

    if (remainingBottomTimeWarnings.size() > 0)
        dp.setRemainingBottomTimeWarnings(remainingBottomTimeWarnings);

    if (decoEntries.size() > 0)
        dp.setDecoEntries(decoEntries);

    return dp;
}

From source file:br.solutio.licita.controlador.ControladorPregao.java

public void editandoXlsParaExportar(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet planilha = wb.getSheetAt(0);

    //Move as celulas selecionadas para baixo de acordo com o valor informado
    planilha.shiftRows(planilha.getFirstRowNum(), planilha.getLastRowNum(), 5);

    HSSFRow linha0 = planilha.createRow(0);
    linha0.createCell(0).setCellValue("Instituio Licitadora:");
    planilha.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
    linha0.createCell(2)//from   w  w  w . j  a v a  2 s. co m
            .setCellValue(" " + getEntidade().getInstituicaoLicitadora().getPessoaJuridica().getNomeFantasia());
    planilha.addMergedRegion(new CellRangeAddress(0, 0, 2, 6));

    HSSFRow linha1 = planilha.createRow(1);
    linha1.createCell(0).setCellValue("Numero do Pregao:");
    planilha.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
    linha1.createCell(2).setCellValue(" " + getEntidade().getNumeroPregao());
    planilha.addMergedRegion(new CellRangeAddress(1, 1, 2, 6));

    HSSFRow linha2 = planilha.createRow(2);
    linha2.createCell(0).setCellValue("Numero do Processo:");
    planilha.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
    linha2.createCell(2).setCellValue(" " + getEntidade().getNumeroProcesso());
    planilha.addMergedRegion(new CellRangeAddress(2, 2, 2, 6));

    HSSFRow linha3 = planilha.createRow(3);
    linha3.createCell(0).setCellValue("Empresa Licitante:");
    planilha.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));
    linha3.createCell(2).setCellValue("Preencha com o nome de sua Empresa");
    planilha.addMergedRegion(new CellRangeAddress(3, 3, 2, 6));

    HSSFRow linha4 = planilha.createRow(4);

    //Nova coluna para a empresas adicionarem seus valores
    HSSFRow linha5 = planilha.getRow(5);
    HSSFCell celula5 = linha5.createCell(5);
    celula5.setCellValue("Valor do Licitante");

    //for para ajustar automaticamente o tamnho das colunas
    for (int i = 0; i < 6; i++) {
        planilha.autoSizeColumn(i);
    }

    //Cor da linha de titulos da tabela
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    for (int i = 0; i < linha5.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = linha5.getCell(i);

        cell.setCellStyle(cellStyle);
    }

    CellStyle unlockedCellStyle = wb.createCellStyle();
    unlockedCellStyle.setLocked(false);

    HSSFCell celula2 = linha3.getCell(2);
    celula2.setCellStyle(unlockedCellStyle);

}

From source file:br.solutio.licita.servico.ServicoSessao.java

@Override
public boolean filtraPlanilha(UploadedFile uploadArquivo) {
    HSSFWorkbook wb = null;/*from  w ww  .  ja  va  2s.  co m*/
    try {
        wb = new HSSFWorkbook(uploadArquivo.getInputstream());
    } catch (IOException ex) {
        Logger.getLogger(ControladorSessao.class.getName()).log(Level.SEVERE,
                "Error InputStream ArquivoProposta", ex);
    }

    HSSFSheet planilha = wb.getSheetAt(0);
    Logger.getLogger(ControladorSessao.class.getName()).log(Level.INFO, " TAMANHO DA PLANILHA: {0}",
            planilha.getLastRowNum());

    //Indica o numero da coluna onde inicia os valores que sero retirado
    int indInicioValores = 6;
    // +1, devido a necessidade de contar mais uma coluna
    propostas = new Double[(planilha.getLastRowNum() + 1) - indInicioValores][2];

    for (int i = 6; i <= planilha.getLastRowNum(); i++) {
        HSSFRow linha = planilha.getRow(i);

        String conversao = linha.getCell(0).getStringCellValue();
        Double convertido = Double.parseDouble(conversao);
        propostas[i - indInicioValores][0] = convertido;
        propostas[i - indInicioValores][1] = linha.getCell(5).getNumericCellValue();
        numTable.add(linha.getCell(5).getNumericCellValue());
        Logger.getLogger(ControladorSessao.class.getName()).log(Level.INFO, numTable.toString());
    }
    return !numTable.isEmpty();
}