Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getLastRowNum

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java

private List<Exploration> populateExplorationData(Sheet sheet, Tab tab) {
    logger.info("Class - ReadExcelFile - populateExplorationData()");
    int columnNo = 0;
    List<Exploration> explorationDataList = new ArrayList<Exploration>();
    int rowCount = sheet.getLastRowNum();

    int totalRecords = 0;
    StringBuffer recordsList = new StringBuffer(BLANK);
    //      StringBuffer descriptionList=new StringBuffer(BLANK);   
    Set<String> columnNamesSet = new HashSet<String>();
    int linebreak = 1;

    for (int i = 1; i <= rowCount; i++) {
        Row row = sheet.getRow(i);/*from   ww  w.  j  av  a2s . c o m*/
        //         System.out.println(row.getRowNum());
        Exploration e = new Exploration();
        try {
            columnNo = 0;
            e.setBlockNo(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(0))) ? BLANK
                            : df.formatCellValue(row.getCell(0)));
            columnNo = 1;
            e.setRegion(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(1))) ? BLANK
                            : df.formatCellValue(row.getCell(1)));
            columnNo = 2;
            e.setCountry(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(2))) ? BLANK
                            : df.formatCellValue(row.getCell(2)));
            columnNo = 3;
            e.setOnShoreOrOffShore(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(3))) ? BLANK
                            : df.formatCellValue(row.getCell(3)));
            columnNo = 4;
            e.setBasin(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(4))) ? BLANK
                            : df.formatCellValue(row.getCell(4)));
            columnNo = 5;
            e.setStatus(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? BLANK
                            : df.formatCellValue(row.getCell(5)));

            //         System.out.println("Exploration first");

            columnNo = 6;
            e.setStartDate(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(6))) ? null
                            : new Date(df.formatCellValue(row.getCell(6))));
            columnNo = 7;
            e.setOperator(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(7))) ? BLANK
                            : df.formatCellValue(row.getCell(7)));
            columnNo = 8;
            e.setEquityPartners(null == row.getCell(8) || null == df.formatCellValue(row.getCell(8))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(8))) ? BLANK
                            : df.formatCellValue(row.getCell(8)));
            columnNo = 9;
            e.setEquityStakes(null == row.getCell(9) || null == df.formatCellValue(row.getCell(9))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(9))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(9))));
            columnNo = 10;
            e.setSourceEquity(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? BLANK
                            : df.formatCellValue(row.getCell(10)));
            columnNo = 11;
            e.setArea(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(11))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(11))));
            columnNo = 12;
            e.setLicenseEnddate(null == row.getCell(12) || null == df.formatCellValue(row.getCell(12))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(12))) ? null
                            : new Date(df.formatCellValue(row.getCell(12))));

            //         System.out.println("Exploration second");

            columnNo = 13;
            e.setWellsDrilled(null == row.getCell(13) || null == df.formatCellValue(row.getCell(13))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(13))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(13))));
            columnNo = 14;
            e.setTwoDSeismicCompleted(null == row.getCell(14) || null == df.formatCellValue(row.getCell(14))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(14))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(14))));
            columnNo = 15;
            e.setThreeDSeismic(null == row.getCell(15) || null == df.formatCellValue(row.getCell(15))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(15))) ? 0
                            : Double.valueOf(df.formatCellValue(row.getCell(15))));
            columnNo = 16;
            e.setMoreInfo(null == row.getCell(16) || null == df.formatCellValue(row.getCell(16))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(16))) ? BLANK
                            : df.formatCellValue(row.getCell(16)));
            columnNo = 17;
            e.setNotes(null == row.getCell(17) || null == df.formatCellValue(row.getCell(17))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(17))) ? BLANK
                            : df.formatCellValue(row.getCell(17)));
            columnNo = 18;
            e.setSource(null == row.getCell(18) || null == df.formatCellValue(row.getCell(18))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(18))) ? BLANK
                            : df.formatCellValue(row.getCell(18)));
            //         System.out.println(row.getCell(18));
            //         System.out.println(row.getCell(18).getStringCellValue());
            columnNo = 19;
            e.setLicenseNo(null == row.getCell(19) || null == df.formatCellValue(row.getCell(19))
                    || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(19))) ? BLANK
                            : df.formatCellValue(row.getCell(19)));
            //               columnNo=19;e.setRecordId(null==row.getCell(19)?0:Double.valueOf(row.getCell(19).getNumericCellValue()).intValue());

            explorationDataList.add(e);
        } catch (Exception ex) {
            logger.error("Exception in ReadExcelFile - populateExplorationData():" + ex);
            totalRecords++;
            String columnName = getHeaderValues(sheet, columnNo);
            recordsList.append((row.getRowNum() + 1) + COMMA);
            if (totalRecords == (12 * linebreak)) {
                recordsList.append(SPACE);// Adding space for rendering in front end;
                linebreak++;
            }
            columnNamesSet.add(columnName);
        }

    }
    if (totalRecords > 0) {
        createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet);
        logger.info("Exploration totalRecords:" + totalRecords + "recordsList:" + recordsList);
    }

    logger.info("Total objects set for Exploration:" + explorationDataList.size());
    return explorationDataList;

}

From source file:com.lulu.ofarm.test.ImportControllerTest.java

@Test
public void getBeanFromExcel() throws FileNotFoundException, IOException {
    //1.Excel  //from   ww  w. j a  v  a2s  . c  o  m
    //      POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/test.xls"));  

    //2.Excel  
    Workbook wb = new HSSFWorkbook(new FileInputStream("F:/reposibility_new.xls"));
    //3.Excel  
    Sheet sheet = wb.getSheetAt(0);
    //  
    int trLength = sheet.getLastRowNum();
    //4.Excel  
    Row row = sheet.getRow(0);
    //  
    int tdLength = row.getLastCellNum();
    //5.Excel?  
    Cell cell = row.getCell((short) 1);
    //6.??  
    //CellStyle cellStyle = cell.getCellStyle();  
    for (int i = 2; i < trLength; i++) {
        //Excel  
        Row row1 = sheet.getRow(i);

        for (int j = 0; j < tdLength; j++) {
            Cell cell1 = row1.getCell(j);
            /** 
             * ?ExcelCannot get a text value from a numeric cell 
             * ?String? 
             */
            if (cell1 != null) {
                cell1.setCellType(Cell.CELL_TYPE_STRING);
            }
            System.out.println(cell1.getStringCellValue());
        }

        //Excel?  

        //?  
        //      OutfallPolluateResourceBean resource = new OutfallPolluateResourceBean();
        //      resource.setRivername(row1.getCell(1).getStringCellValue());
        //      resource.setArea(row1.getCell(2).getStringCellValue());
        //      resource.setLeftorrightbank(row1.getCell(3).getStringCellValue());
        //      resource.setOutfalltype(row1.getCell(4).getStringCellValue());
        //      resource.setOutfallcode(row1.getCell(5).getStringCellValue());
        //      resource.setSecondaryunit(row1.getCell(6).getStringCellValue());
        //      resource.setStreetname(row1.getCell(7).getStringCellValue());
        //      resource.setStreetmanager(row1.getCell(8).getStringCellValue());
        //      resource.setVillage(row1.getCell(9).getStringCellValue());
        //      resource.setVillagemanager(row1.getCell(10).getStringCellValue());
        //      resource.setPosition(row1.getCell(11).getStringCellValue());
        //      resource.setCoordinate(row1.getCell(12).getStringCellValue());
        //      resource.setOutfallsize(row1.getCell(13).getStringCellValue());
        //      resource.setOutfallshape(row1.getCell(14).getStringCellValue());
        //      resource.setPolldescription(row1.getCell(15).getStringCellValue());
        //      
        //      resource.setRectificationmeasures(row1.getCell(16).getStringCellValue());
        //      resource.setDrainageTo(row1.getCell(17).getStringCellValue());
        //      resource.setTherectificationresponsibilityunit(row1.getCell(18).getStringCellValue());
        //      resource.setTimeofcompletion(row1.getCell(19).getStringCellValue());
        //      resource.setRemark(row1.getCell(20).getStringCellValue());
        //      System.err.println(resource);
        //      service.save(resource);

        //       PollutantSourceBean source = new PollutantSourceBean();
        //      source.setRivername(row1.getCell(1).getStringCellValue());
        //      source.setArea(row1.getCell(2).getStringCellValue());
        //      source.setPollsourcename(row1.getCell(3).getStringCellValue());
        //      source.setStreetname(row1.getCell(4).getStringCellValue());
        //      source.setStreetmanager(row1.getCell(5).getStringCellValue());
        //      source.setVillage(row1.getCell(6).getStringCellValue());
        //      source.setVillagemanager(row1.getCell(7).getStringCellValue());
        //      source.setPollsourcetype(row1.getCell(8).getStringCellValue());
        //      source.setOutfalltype(row1.getCell(9).getStringCellValue());
        //      source.setOutfallcode(row1.getCell(10).getStringCellValue());
        //      source.setPosition(row1.getCell(11).getStringCellValue());
        //      source.setCoordinate(row1.getCell(12).getStringCellValue());
        //      source.setPolldescription(row1.getCell(13).getStringCellValue());
        //      source.setDrainageto(row1.getCell(14).getStringCellValue());
        //      source.setPolldischarginglicense(row1.getCell(15).getStringCellValue());
        //      source.setDrainaglicense(row1.getCell(16).getStringCellValue());
        //      source.setHasmeasures(row1.getCell(17).getStringCellValue());
        //      source.setRectificationmeasures(row1.getCell(18).getStringCellValue());
        //      source.setTherectificationresponsibilityunit(row1.getCell(19).getStringCellValue());
        //      source.setTimeofcompletion(row1.getCell(20).getStringCellValue());
        //      source.setRemark(row1.getCell(21).getStringCellValue());
        //      System.out.println(source);

        //      service.save(source);

    }
}

From source file:com.mimp.controllers.reporte.java

private static void copyRowStyle(Sheet worksheet, int sourceRowNum, int destinationRowNum) {
        // Coge la fila antigua y nueva
        Row newRow = worksheet.getRow(destinationRowNum);
        Row sourceRow = worksheet.getRow(sourceRowNum);

        //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna
        if (newRow != null) {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
        } else {/*from www  .j  a va2  s .  c o m*/
            newRow = worksheet.createRow(destinationRowNum);
        }

        // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            // Copia la antigua y nueva celda
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell = newRow.createCell(i);

            // Si la anterior celda es null, evalua la siguiente celda defrente
            if (oldCell == null) {
                newCell = null;
                continue;
            }

            // Usa el estilo de la celda antigua
            newCell.setCellStyle(oldCell.getCellStyle());

            // Establece el tipo de valor de la celda
            newCell.setCellType(oldCell.getCellType());

            // Establece el valor de la celda
            //            switch (oldCell.getCellType()) {
            //                case Cell.CELL_TYPE_BLANK:
            //                    break;
            //                case Cell.CELL_TYPE_BOOLEAN:
            //                    newCell.setCellValue(oldCell.getBooleanCellValue());
            //                    break;
            //                case Cell.CELL_TYPE_ERROR:
            //                    newCell.setCellErrorValue(oldCell.getErrorCellValue());
            //                    break;
            //                case Cell.CELL_TYPE_FORMULA:
            //                    newCell.setCellFormula(oldCell.getCellFormula());
            //                    break;
            //                case Cell.CELL_TYPE_NUMERIC:
            //                    newCell.setCellValue(oldCell.getNumericCellValue());
            //                    break;
            //                case Cell.CELL_TYPE_STRING:
            //                    newCell.setCellValue(oldCell.getRichStringCellValue());
            //                    break;
            //            }
        }
    }

From source file:com.mimp.controllers.reporte.java

private static void copyRow(Sheet worksheet, int sourceRowNum, int destinationRowNum) {
        // Coge la fila antigua y nueva
        Row newRow = worksheet.getRow(destinationRowNum);
        Row sourceRow = worksheet.getRow(sourceRowNum);

        //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna
        if (newRow != null) {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
        } else {/* w  ww .  j av a2 s . co m*/
            newRow = worksheet.createRow(destinationRowNum);
        }

        // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            // Copia la antigua y nueva celda
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell = newRow.createCell(i);

            // Si la anterior celda es null, evalua la siguiente celda defrente
            if (oldCell == null) {
                newCell = null;
                continue;
            }

            // Usa el estilo de la celda antigua
            newCell.setCellStyle(oldCell.getCellStyle());

            // Establece el tipo de valor de la celda
            newCell.setCellType(oldCell.getCellType());

            // Establece el valor de la celda
            switch (oldCell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getRichStringCellValue());
                break;
            }
        }
    }

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public int getLastColumn(Sheet sheet) {
    int lastRow = sheet.getLastRowNum();
    int lastColumn = 1;
    for (int i = 0; i < lastRow; ++i) {
        Row row = sheet.getRow(i);//from w  ww  .  ja v a 2s .  c  om
        if (row != null) {
            int col = row.getLastCellNum();
            if (col > lastColumn) {
                lastColumn = col;
            }
        }
    }
    return lastColumn - 1;
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public void clearSheet(int sheetIndex) {
    Sheet sheet = getSheet(sheetIndex);
    int firstRow = sheet.getFirstRowNum();
    int lastRow = sheet.getLastRowNum();
    for (int i = lastRow; i >= firstRow; i--) {
        Row r = sheet.getRow(i);//from   w w w  .j  a  v  a  2s .  co m
        if (r != null)
            sheet.removeRow(r);
    }
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public int[] getBoundingBox(int sheetIndex, int startRow, int startCol, int endRow, int endCol,
        boolean autofitRow, boolean autofitCol) {
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    final int mark = Integer.MAX_VALUE - 1;

    if (startRow < 0) {
        startRow = sheet.getFirstRowNum();
        if (sheet.getRow(startRow) == null) {
            // There is no row in this sheet
            startRow = -1;//www . j  av a 2 s. c  o  m
        }
    }

    if (endRow < 0) {
        endRow = sheet.getLastRowNum();
        if (sheet.getRow(endRow) == null) {
            // There is no row in this sheet
            endRow = -1;
        }
    }

    int minRow = startRow;
    int maxRow = endRow;
    int minCol = startCol;
    int maxCol = endCol < 0 ? mark : endCol;

    startCol = startCol < 0 ? mark : startCol;
    endCol = endCol < 0 ? -1 : endCol;
    Cell topLeft = null, bottomRight = null;
    boolean anyCell = false;
    for (int i = minRow; i > -1 && i <= maxRow; i++) {
        Row r = sheet.getRow(i);
        if (r != null) {
            // Determine column boundaries
            int start = Math.max(minCol, r.getFirstCellNum());
            int end = Math.min(maxCol + 1, r.getLastCellNum()); // NOTE: getLastCellNum is 1-based!
            boolean anyNonBlank = false;
            for (int j = start; j > -1 && j < end; j++) {
                Cell c = r.getCell(j);
                if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) {
                    anyCell = true;
                    anyNonBlank = true;
                    if ((autofitCol || minCol < 0) && (topLeft == null || j < startCol)) {
                        startCol = j;
                        topLeft = c;
                    }
                    if ((autofitCol || maxCol == mark) && (bottomRight == null || j > endCol)) {
                        endCol = j;
                        bottomRight = c;
                    }
                }
            }
            if (autofitRow && anyNonBlank) {
                endRow = i;
                if (sheet.getRow(startRow) == null) {
                    startRow = i;
                }
            }
        }
    }

    if ((autofitRow || startRow < 0) && !anyCell) {
        startRow = endRow = -1;
    }
    if ((autofitCol || startCol == mark) && !anyCell) {
        startCol = endCol = -1;
    }

    return new int[] { startRow, startCol, endRow, endCol };
}

From source file:com.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>//from  w w w.ja v  a 2  s.com
 * 1.  : POI Util
 * 2.  : Excel ? ?(*.xls, *.xlsx ? )
 * </pre>
 *
 * @method Name : readExcel
 * @param strFullFilePath, serviceType
 * @return List<Map<String, Object>>
 * @throws Exception
 * 
 */
@SuppressWarnings("deprecation")
public static List<Map<String, Object>> readExcel(String strFullFilePath, String serviceType) throws Exception {
    String tmpFile = strFullFilePath;
    File wbfile = new File(tmpFile);

    Workbook wb = null;
    FileInputStream file = null;

    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    Map<String, Object> map = null;

    try {
        file = new FileInputStream(wbfile);

        wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); Version change */

        /* Sheet  ? ,  ?  ??  */
        /* for (int sheetIdx=0; sheetIdx<wb.getNumberOfSheets(); sheetIdx++) { */
        for (int sheetIdx = 0; sheetIdx < 1; sheetIdx++) { /* 1  ? */

            Sheet sheet = wb.getSheetAt(sheetIdx);

            /*   ?? ?    ?,   */

            /* row  ?  */
            int cellCount = 0;
            for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) {

                Row row = sheet.getRow(rowIdx);
                cellCount = row.getLastCellNum();
                map = new HashMap<String, Object>();

                if (row != null) {
                    // cell  ? 
                    for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) {

                        Cell cell = row.getCell(cellIdx);
                        if (cell != null) {

                            int cellType = cell.getCellType();
                            String value = "";

                            //  WBS  ? ? ??
                            if (serviceType.equals("order")) {
                                switch (cellType) {
                                case HSSFCell.CELL_TYPE_FORMULA: //??
                                    value = cell.getStringCellValue();//cell.getCellFormula();
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC://?
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                                        value = formatter.format(cell.getDateCellValue());
                                    } else {
                                        cell.setCellType(Cell.CELL_TYPE_STRING);
                                        value = cell.getStringCellValue();
                                    }
                                    break;

                                case HSSFCell.CELL_TYPE_STRING: //?
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_BLANK: //
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR: //BYTE
                                    value = cell.getErrorCellValue() + "";
                                    break;

                                default:
                                    ;
                                }
                            } else {
                                switch (cellType) {
                                case HSSFCell.CELL_TYPE_FORMULA: //??
                                    value = cell.getStringCellValue();//cell.getCellFormula();
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC://?
                                    value = cell.getNumericCellValue() + "";
                                    break;

                                case HSSFCell.CELL_TYPE_STRING: //?
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_BLANK: //
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR: //BYTE
                                    value = cell.getErrorCellValue() + "";
                                    break;

                                default:
                                }
                            }
                            map.put("colName" + cellIdx, value);
                        } else {
                            map.put("colName" + cellIdx, "");
                        }
                    }

                    list.add(map);
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();

        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= Excel File Reading ... Error : [{}] =", e);
            log.debug(
                    "==========================================================================================");
        }

        throw new NCException("ExcelUtil > readExcel ?");
    } finally {

        /* ? ??  */
        file.close();
        wb.close();
    }

    return list;
}

From source file:com.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>//from ww w .  ja  v a2 s . co  m
 * 1.  : POI Util
 * 2.  : Excel ? ?(*.xls, *.xlsx ? ), sheet  ?
 * </pre>
 *
 * @method Name : readExcelMulti
 * @param strFullFilePath, serviceType, sheetNo
 * @return 
 * @throws
 * 
 */
@SuppressWarnings("deprecation")
public static List<LinkedHashMap<String, Object>> readExcelMulti(String strFullFilePath, String serviceType,
        int sheetNo) throws Exception {
    String tmpFile = strFullFilePath;
    File wbfile = new File(tmpFile);

    Workbook wb = null;
    FileInputStream file = null;

    List<LinkedHashMap<String, Object>> list = new ArrayList<LinkedHashMap<String, Object>>();
    LinkedHashMap<String, Object> map = null;

    try {
        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= file path : {} =", strFullFilePath);
            log.debug("= tmp file  : {} =", tmpFile);
            log.debug(
                    "==========================================================================================");
        }

        file = new FileInputStream(wbfile);

        wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); */

        Sheet sheet = wb.getSheetAt(sheetNo);

        /* row  ?  */
        int cellCount = 0;
        for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) {

            Row row = sheet.getRow(rowIdx);
            cellCount = row.getLastCellNum();
            map = new LinkedHashMap<String, Object>();

            if (rowIdx == 0) {
                if (log.isDebugEnabled()) {
                    log.debug(
                            "==================================================================================");
                    log.debug("= sheet no  : {} =", sheetNo);
                    log.debug("= row count : {} =", sheet.getLastRowNum());
                    log.debug("= col count : {} =", cellCount);
                    log.debug(
                            "==================================================================================");
                }
            }

            if (row != null) {
                // cell  ? 
                for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) {

                    Cell cell = row.getCell(cellIdx);
                    if (cell != null) {

                        int cellType = cell.getCellType();
                        String value = "";

                        switch (cellType) {
                        case HSSFCell.CELL_TYPE_FORMULA: //??
                            value = cell.getStringCellValue();//cell.getCellFormula();
                            break;

                        case HSSFCell.CELL_TYPE_NUMERIC://?
                            value = cell.getNumericCellValue() + "";
                            break;

                        case HSSFCell.CELL_TYPE_STRING: //?
                            value = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_BLANK: //
                            value = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_ERROR: //BYTE
                            value = cell.getErrorCellValue() + "";
                            break;

                        default:
                        }

                        map.put("item" + String.valueOf(cellIdx), value);
                    } else {
                        map.put("item" + String.valueOf(cellIdx), "");
                    }
                }

                if (log.isDebugEnabled()) {
                    log.debug(
                            "==================================================================================");
                    log.debug("= map : {} =", map);
                    log.debug(
                            "==================================================================================");
                }

                list.add(map);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();

        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= Excel File Reading ... Error : [{}] =", e);
            log.debug(
                    "==========================================================================================");
        }

        throw new NCException("ExcelUtil > readExcel ?");
    } finally {

        /* ? ??  */
        file.close();
        wb.close();
    }

    return list;
}

From source file:com.ncc.excel.test.ExcelUtil.java

License:Apache License

/** 
 * ?Excel //ww  w . ja  v  a  2s . c  om
 *  
 * @Title: readExcel 
 * @Date : 2014-9-11 ?11:26:53 
 * @param wb 
 * @return 
 */
private List<Row> readExcel(Workbook wb) {
    List<Row> rowList = new ArrayList<Row>();

    int sheetCount = 1;//??sheet?  

    Sheet sheet = null;
    if (onlyReadOneSheet) { //??sheet  
        // ??sheet(?????)  
        sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
    } else { //?sheet  
        sheetCount = wb.getNumberOfSheets();//????  
    }

    // ?sheet  
    for (int t = startSheetIdx; t < sheetCount + endSheetIdx; t++) {
        // ??sheet  
        if (!onlyReadOneSheet) {
            sheet = wb.getSheetAt(t);
        }

        //???  
        int lastRowNum = sheet.getLastRowNum();

        if (lastRowNum > 0) { //>0?  
            out("\n????" + sheet.getSheetName() + "");
        }

        Row row = null;
        // ?  
        for (int i = startReadPos; i <= lastRowNum + endReadPos; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                rowList.add(row);
                out("" + (i + 1) + "", false);
                // ???  
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    String value = getCellValue(row.getCell(j));
                    if (!value.equals("")) {
                        out(value + " | ", false);
                    }
                }
                out("");
            }
        }
    }
    return rowList;
}