Example usage for org.apache.poi.hssf.usermodel HSSFSheet 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:com.eastsoft.ui.MainUI.java

License:Open Source License

void savePrintInfoToExcel(recordInfo recordinfor, int printFormat) {
    if (printFormat != 0) {
        HSSFWorkbook wb = null;//from ww w  .j av a 2s.  co  m
        File printRecord = new File("?.xls");
        if (!printRecord.exists()) {

            wb = new HSSFWorkbook();
            HSSFSheet sheet1 = wb.createSheet("sheet1");
            HSSFRow row = sheet1.createRow(0);
            row.createCell((short) 0).setCellValue("?           ");
            row.createCell((short) 1).setCellValue("           ");
            row.createCell((short) 2).setCellValue("??           ");
            row.createCell((short) 3).setCellValue("??           ");
            row.createCell((short) 4).setCellValue("Aid     ");
            row.createCell((short) 5).setCellValue("?            ");

            HSSFRow row1 = sheet1.createRow(sheet1.getLastRowNum() + 1);
            row1.createCell((short) 0).setCellValue(recordinfor.getDate());
            row1.createCell((short) 1).setCellValue(recordinfor.getProductSerialNO());
            row1.createCell((short) 2).setCellValue(recordinfor.getDevice());
            row1.createCell((short) 3).setCellValue(recordinfor.getProduct());
            row1.createCell((short) 4).setCellValue(recordinfor.getAid());
            row1.createCell((short) 5).setCellValue(recordinfor.getPasswd());

        } else {
            FileInputStream fs = null;
            try {
                fs = new FileInputStream(printRecord);
            } catch (FileNotFoundException e2) {
                // TODO Auto-generated catch block
                e2.printStackTrace();
            }
            POIFSFileSystem ps = null;
            try {
                ps = new POIFSFileSystem(fs);
            } catch (IOException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }

            try {
                wb = new HSSFWorkbook(ps);
            } catch (IOException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }

            HSSFSheet sheet = wb.getSheetAt(0);
            // ?cell,Rows 0(Create a row and put some cells
            // in
            // it. Rows are 0 based.)
            HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
            row.createCell((short) 0).setCellValue(recordinfor.getDate());
            row.createCell((short) 1).setCellValue(recordinfor.getProductSerialNO());
            row.createCell((short) 2).setCellValue(recordinfor.getDevice());
            row.createCell((short) 3).setCellValue(recordinfor.getProduct());
            row.createCell((short) 4).setCellValue(recordinfor.getAid());
            row.createCell((short) 5).setCellValue(recordinfor.getPasswd());

        }

        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream(printRecord);
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            appendTextareaText(jTextArea_status,
                    "\n?.xls ??");
            JOptionPane.showMessageDialog(getParent(),
                    "?.xls ??",
                    "?.xls", JOptionPane.WARNING_MESSAGE);
            e.printStackTrace();
        }
        try {
            wb.write(fileOut);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            fileOut.close();

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

---

From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelImportStudyServiceImpl.java

License:Open Source License

private void validateNumberOfRows(HSSFSheet observationSheet, Workbook workbook)
        throws WorkbookParserException {
    if (workbook.getObservations() != null
            && observationSheet.getLastRowNum() != workbook.getObservations().size()) {
        throw new WorkbookParserException("error.workbook.import.observationRowCountMismatch");
    }//from w  ww .j  a va  2  s.  c  o  m
}

---

From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelImportStudyServiceImpl.java

License:Open Source License

private List<String> getAllVariates(HSSFSheet descriptionSheet) {
    List<String> variates = new ArrayList<String>();

    int startRowIndex = findRow(descriptionSheet, TEMPLATE_SECTION_VARIATE) + 1;
    int endRowIndex = descriptionSheet.getLastRowNum();

    if (startRowIndex <= endRowIndex) {
        for (int rowIndex = startRowIndex; rowIndex <= endRowIndex; rowIndex++) {
            variates.add(descriptionSheet.getRow(rowIndex).getCell(0).getStringCellValue().toUpperCase());
        }/*from ww  w .  ja v a 2s  .com*/
    }

    return variates;
}

---

From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelImportStudyServiceImpl.java

License:Open Source License

private int findRow(HSSFSheet sheet, String cellValue) {
    int result = 0;
    for (int i = 0; i < sheet.getLastRowNum(); i++) {
        HSSFRow row = sheet.getRow(i);//from  www. j  a va  2  s .  c  om
        if (row != null) {
            HSSFCell cell = row.getCell(0);
            if (cell != null && cell.getStringCellValue() != null) {
                if (cell.getStringCellValue().equals(cellValue)) {
                    return i;
                }
            }
        }
    }

    return result;
}

---

From source file:com.eryansky.core.excelTools.ExcelUtils.java

License:Apache License

public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) {
    if (src == null || dest == null)
        return;//from   w  w  w.  j a v  a  2s  .c o m

    dest.setAlternativeExpression(src.getAlternateExpression());
    dest.setAlternativeFormula(src.getAlternateFormula());
    dest.setAutobreaks(src.getAutobreaks());
    dest.setDialog(src.getDialog());
    if (src.getColumnBreaks() != null) {
        for (int col : src.getColumnBreaks()) {
            dest.setColumnBreak(col);
        }
    }
    dest.setDefaultColumnWidth(src.getDefaultColumnWidth());
    dest.setDefaultRowHeight(src.getDefaultRowHeight());
    dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints());
    dest.setDisplayGuts(src.getDisplayGuts());
    dest.setFitToPage(src.getFitToPage());
    dest.setHorizontallyCenter(src.getHorizontallyCenter());
    dest.setDisplayFormulas(src.isDisplayFormulas());
    dest.setDisplayGridlines(src.isDisplayGridlines());
    dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings());
    dest.setGridsPrinted(src.isGridsPrinted());
    dest.setPrintGridlines(src.isPrintGridlines());

    for (int i = 0; i < src.getNumMergedRegions(); i++) {
        CellRangeAddress r = src.getMergedRegion(i);
        dest.addMergedRegion(r);
    }

    if (src.getRowBreaks() != null) {
        for (int row : src.getRowBreaks()) {
            dest.setRowBreak(row);
        }
    }
    dest.setRowSumsBelow(src.getRowSumsBelow());
    dest.setRowSumsRight(src.getRowSumsRight());

    int maxcol = 0;
    for (int i = 0; i <= src.getLastRowNum(); i++) {
        HSSFRow row = src.getRow(i);
        if (row != null) {
            if (maxcol < row.getLastCellNum())
                maxcol = row.getLastCellNum();
        }
    }
    for (int col = 0; col <= maxcol; col++) {
        if (src.getColumnWidth(col) != src.getDefaultColumnWidth())
            dest.setColumnWidth(col, src.getColumnWidth(col));
        dest.setColumnHidden(col, src.isColumnHidden(col));
    }
}

---

From source file:com.eryansky.core.excelTools.ExcelUtils.java

License:Apache License

public static void copySheet(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) {
    if (src == null || dest == null)
        return;/*from  w  ww . j  a  v  a 2  s  .c o m*/

    copySheetStyle(destwb, dest, srcwb, src);

    for (int i = 0; i <= src.getLastRowNum(); i++) {
        HSSFRow row = src.getRow(i);
        copyRow(destwb, dest.createRow(i), srcwb, row);
    }
}

---

From source file:com.esd.cs.common.HExcelSheetParser.java

License:Open Source License

public List<List<Object>> getDatasInSheet(int sheetNumber) {
    List<List<Object>> result = new ArrayList<List<Object>>();
    // sheet/*ww w .  jav  a2 s.co m*/
    HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
    // sheet
    int rowCount = sheet.getLastRowNum();
    logger.info("found excel rows count:" + rowCount);
    if (rowCount < 1) {
        return result;
    }
    // ??row
    for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
        // 
        HSSFRow row = sheet.getRow(rowIndex);
        if (null != row) {
            List<Object> rowData = new ArrayList<Object>();
            // ?
            int cellCount = row.getLastCellNum();
            // ??cell
            for (int cellIndex = 0; cellIndex < cellCount; cellIndex++) {
                HSSFCell cell = row.getCell(cellIndex);
                // ??
                Object cellStr = this.getCellString(cell);

                rowData.add(cellStr);
            }
            result.add(rowData);
        }
    }

    return result;
}

---

From source file:com.etest.view.tq.itemanalysis.FileUploadWindow.java

void readContentFromExcelFile(File excelFile) {
    try {/*from  w w  w . j a  v  a  2s.  c o  m*/
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelFile));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(wb.getActiveSheetIndex());
        HSSFRow row;
        HSSFCell cell;

        boolean stop = false;
        boolean nonBlankRowFound;
        int s;
        HSSFRow lastRow = null;

        while (stop == false) {
            nonBlankRowFound = false;
            lastRow = sheet.getRow(sheet.getLastRowNum());
            for (s = lastRow.getFirstCellNum(); s <= lastRow.getLastCellNum(); s++) {
                cell = lastRow.getCell(s);
                if (cell != null && lastRow.getCell(s).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                    nonBlankRowFound = true;
                }
            }
            if (nonBlankRowFound == true) {
                stop = true;
            } else {
                sheet.removeRow(lastRow);
            }
        }

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns
        int tmp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols)
                    cols = tmp;
            }
        }

        List<ItemAnalysis> itemAnalysisList = new ArrayList<>();
        List<Character> answer;
        ItemAnalysis itemAnalysis = null;

        for (int c = 0; c < cols; c++) {
            itemAnalysis = new ItemAnalysis();
            answer = new ArrayList<>();
            for (int r = 0; r < rows; r++) {
                row = sheet.getRow(r);
                if (row == null || row.toString().isEmpty()) {
                    ShowErrorNotification.error("Remove all blank/empty rows after the last Item!");
                    return;
                } else {
                    //                   if(row != null){
                    cell = row.getCell(c);
                    if (cell == null || cell.toString().isEmpty()) {
                        ShowErrorNotification.error("Remove all blank/empty columns after the last student!");
                        return;
                    } else {
                        //                       if(cell != null){                           
                        if (c != 0) {
                            if (r == 0) {
                                itemAnalysis.setStudentNumber(cell.toString().trim());
                            } else {
                                answer.add(cell.toString().trim().charAt(0));
                            }
                        } else {
                            if (r != 0) {
                                totalItems++;
                            }
                        }
                    }
                }
            }
            if (c != 0) {
                itemAnalysis.setAnswer(answer);
                itemAnalysisList.add(itemAnalysis);
            }
        }

        if (tq.getCellItemIdByTQCoverageId(getTqCoverageId()).size() != totalItems) {
            ShowErrorNotification.error("Total Items do not MATCH!");
            totalItems = 0;
            return;
        }

        studentNoAndTotalScore = new HashMap<>();
        studentNoAndAnswer = new HashMap<>();
        totalItems = 1;
        new Thread() {

            @Override
            public void run() {
                totalData = itemAnalysisList.size();
                for (ItemAnalysis i : itemAnalysisList) {
                    try {
                        Thread.sleep(50);
                        studentNoAndTotalScore.put(i.getStudentNumber(), ItemAnalysisInterpretation
                                .getTotalScoresOfAllStudent(tqCoverageId, i.getAnswer()));
                        studentNoAndAnswer.put(i.getStudentNumber(), i.getAnswer());

                        getUI().access(new Runnable() {

                            @Override
                            public void run() {
                                if (totalItems < itemAnalysisList.size()) {
                                    analyze.setValue("Analyzing data.. "
                                            + CommonUtilities.roundOffToTwoDecimal((current / totalData) * 100)
                                            + "%");
                                    current++;
                                } else {
                                    analyze.setValue("Data analyzed... 100%");
                                    getLowerAndUpperGroupStudent(studentNoAndTotalScore);

                                    HorizontalLayout h = new HorizontalLayout();
                                    h.setWidth("100%");

                                    h.addComponent(viewTableProportion());
                                    h.addComponent(viewStudentsTotalScore());
                                    h.addComponent(approveItemAnalysis());
                                    v.addComponent(h);

                                    v.addComponent(itemAnalysisGridPanel());
                                }
                            }

                        });

                        totalItems++;
                    } catch (InterruptedException ex) {
                        Logger.getLogger(FileUploadWindow.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }

        }.start();
        UI.getCurrent().setPollInterval(500);
    } catch (IOException ex) {
        Logger.getLogger(TQItemAnalysisUI.class.getName()).log(Level.SEVERE, null, ex);
    }
}

---

From source file:com.example.selenium.util.ApachePOIXLSReader.java

public static Object[][] excelRead(String filePath) throws Exception {
    File excel = new File(filePath);
    FileInputStream fis = new FileInputStream(excel);

    HSSFWorkbook wb = new HSSFWorkbook(fis);
    HSSFSheet ws = wb.getSheet("Sheet1");

    int rowNum = ws.getLastRowNum() + 1;
    int colNum = ws.getRow(0).getLastCellNum();
    Object[][] data = new Object[(rowNum - 1)][colNum];

    int k = 0;//from w ww  . j a  va 2s. c  o  m
    for (int i = 1; i < rowNum; i++) {
        HSSFRow row = ws.getRow(i);
        for (int j = 0; j < colNum; j++) {
            HSSFCell cell = row.getCell(j);
            Object value = cellToObject(cell);
            data[k][j] = value;
        }
        k++;
    }
    return data;
}

---

From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java.//from  w w w . ja va 2 s  .c om
 * 
 * @param file
 * @return
 * @throws IOException
 */
public static List<Map<String, Object>> parseHSSFMapList(MultipartFile file) throws IOException {//
    POIFSFileSystem poiFs = new POIFSFileSystem(file.getInputStream());
    HSSFWorkbook wb = new HSSFWorkbook(poiFs);

    HSSFSheet sheet = wb.getSheetAt(0);

    int rowNum = sheet.getLastRowNum();
    HSSFRow titleRow = sheet.getRow(0);

    int colNum = titleRow.getLastCellNum();

    //?17
    List<String> titleList = new ArrayList<String>();
    for (int i = 0; i < colNum; i++) {
        String title = titleRow.getCell(i).getStringCellValue();
        titleList.add(trimTitle(title));
    }

    List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
    for (int i = 1; i <= rowNum; i++) {
        HSSFRow row = sheet.getRow(i);
        Map<String, Object> map = new LinkedHashMap<String, Object>();
        for (int j = 0; j < colNum; j++) {
            HSSFCell cell = row.getCell(j);
            if (cell != null) {
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    double d = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    //?
                    if (HSSFDateUtil.isCellDateFormatted(cell) || (style != null
                            && (style.getDataFormat() == 57 || style.getDataFormat() == 58))) {
                        map.put(titleList.get(j), HSSFDateUtil.getJavaDate(d));
                    } else {
                        map.put(titleList.get(j), d);
                    }
                    break;

                default:
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    map.put(titleList.get(j), row.getCell(j).getStringCellValue());
                    break;
                }
            } else {
                map.put(titleList.get(j), null);
            }
        }
        mapList.add(map);
    }

    return mapList;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• »