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

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

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

Introduction

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

Prototype

@Override
public int getFirstRowNum()

Source Link

Document

Gets the first row on the sheet

Usage

From source file:com.cms.utils.ExcelReader.java

public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        HSSFRow srcRow = sheet.getRow(i);
        HSSFRow destRow = newSheet.createRow(i);
        if (srcRow != null) {
            copyRow(sheet, newSheet, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }/*from  w w  w.j ava  2  s.  c om*/
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
}

From source file:com.cn.util.ExcelImport.java

/**
* ?2003excel// w  ww  .  ja  v a 2 s.  c o  m
* @param file
* @return
*/
private static List<List<Object>> read2003Excel(InputStream inputStream) throws IOException {
    List<List<Object>> dataList = new ArrayList<>();
    HSSFWorkbook wb = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = null;
    HSSFCell cell = null;
    Object val = null;
    DecimalFormat df = new DecimalFormat("0");// ?
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// ?

    for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        List<Object> objList = new ArrayList<>();
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            cell = row.getCell(j);
            if (cell == null) {
                val = null;
                objList.add(val);
                continue;
            }
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                val = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else {
                    val = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                }
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                val = cell.getBooleanCellValue();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                val = "";
                break;
            default:
                val = cell.toString();
                break;
            }
            objList.add(val);
        }
        dataList.add(objList);
    }
    return dataList;
}

From source file:com.codecrate.shard.transfer.excel.ExcelObjectImporter.java

License:Apache License

public Collection importObjects(File file, ProgressMonitor progress) {
    Collection results = new ArrayList();

    try {/*from   w w w  . ja v a2 s.  co  m*/
        POIFSFileSystem poifs = new POIFSFileSystem(new FileInputStream(file));

        HSSFWorkbook workbook = new HSSFWorkbook(poifs);
        HSSFSheet sheet = workbook.getSheetAt(0);

        int firstRow = sheet.getFirstRowNum() + 1;
        int lastRow = sheet.getLastRowNum();

        progress.startTask("Import objects from " + file.getName(), lastRow - firstRow);
        for (int currentRow = firstRow; currentRow <= lastRow; currentRow++) {
            HSSFRow row = sheet.getRow(currentRow);

            try {
                Object result = rowHandler.handleRow(row);
                results.add(result);
            } catch (Exception e) {
                LOG.error("Error importing row: " + currentRow, e);
            }

            progress.completeUnitOfWork();
        }
    } catch (Exception e) {
        LOG.error("Error importing file: " + file, e);
    }

    progress.finish();

    return results;
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

protected void cleanupCells(HSSFSheet resultSheet) {
    for (int i = resultSheet.getFirstRowNum(); i <= resultSheet.getLastRowNum(); i++) {
        HSSFRow row = resultSheet.getRow(i);
        if (row != null) {
            for (int j = 0; j < row.getLastCellNum(); j++) {
                HSSFCell cell = row.getCell(j);
                if (cell != null) {
                    row.removeCell(cell);
                }//from  w  w w. j  a va2 s  .co  m
            }
        }
    }
}

From source file:com.huateng.bo.impl.risk.T40201BOTarget.java

License:Open Source License

public String importFile(List<File> fileList, List<String> fileNameList, Operator operator) throws Exception {
    HSSFWorkbook workbook = null;/*ww w.ja v  a  2  s.  c  om*/
    HSSFSheet sheet = null;
    HSSFRow row = null;
    // ?
    String returnMsg = "";
    // ??
    int fileNameIndex = 0;
    // ??
    String fileName = null;

    // ??
    String saCardNo = null;
    // ??
    //      String saLimitAmt = null;
    // ?
    //      String saAction = null;
    // 
    String saBrhId = operator.getOprBrhId();
    // ?
    String saOprId = operator.getOprId();
    // 
    String saInitTime = CommonFunction.getCurrentDateTime();

    TblCtlCardInf tblCtlCardInf = null;

    FileInputStream fileInputStream = null;

    for (File file : fileList) {

        fileInputStream = new FileInputStream(file);

        workbook = new HSSFWorkbook(fileInputStream);

        sheet = workbook.getSheetAt(0);

        fileName = fileNameList.get(fileNameIndex);

        for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {

            row = sheet.getRow(rowIndex);

            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
                if (row.getCell(i).getCellType() != HSSFCell.CELL_TYPE_STRING)
                    returnMsg += "[ " + fileName + " ]" + (row.getRowNum() + 1) + "" + ""
                            + (i + 1) + "???<br>";

            if (!"".equals(returnMsg))
                return returnMsg;

            saCardNo = row.getCell(0).getStringCellValue();
            // ??
            if (saCardNo.getBytes().length > 19)
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "???<br>";

            //            saLimitAmt = row.getCell(1).getStringCellValue();
            //            saLimitAmt = CommonFunction.transYuanToFen(saLimitAmt);

            // ??
            /*if(!CommonFunction.isAllDigit(saLimitAmt))
               return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "????<br>";
                    
            if(saLimitAmt.getBytes().length > 12) 
               return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/

            //            saAction = row.getCell(2).getStringCellValue();
            // ?
            /*if(!("1".equals(saAction) || "2".equals(saAction) || "3".equals(saAction) || "4".equals(saAction)))
               return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/

            // ???????
            tblCtlCardInf = new TblCtlCardInf();
            tblCtlCardInf.setId(saCardNo);
            //            tblCtlCardInf.setSaLimitAmt(saLimitAmt);
            //            tblCtlCardInf.setSaAction(saAction);
            tblCtlCardInf.setSaInitZoneNo(saBrhId);
            tblCtlCardInf.setSaInitOprId(saOprId);
            tblCtlCardInf.setSaInitTime(saInitTime);
            tblCtlCardInfDAO.saveOrUpdate(tblCtlCardInf);
        }
        fileInputStream.close();
        fileNameIndex++;
    }
    return Constants.SUCCESS_CODE;
}

From source file:com.huateng.bo.impl.risk.T40202BOTarget.java

License:Open Source License

@SuppressWarnings("unchecked")
public String importFile(List<File> fileList, List<String> fileNameList, Operator operator) throws Exception {
    HSSFWorkbook workbook = null;/*from   ww  w . ja  v  a2s  . com*/
    HSSFSheet sheet = null;
    HSSFRow row = null;
    // ?
    String returnMsg = "";
    // ??
    int fileNameIndex = 0;
    // ??
    String fileName = null;
    String sql = null;
    // 
    List<Object[]> dataList = null;

    // ??
    String saMerNo = null;
    // ??
    String saMerChName = null;
    // ??
    String saMerEnName = null;
    // ?
    String saZoneNo = null;
    // ??
    //      String saLimitAmt = null;
    // ?
    //      String saAction = null;
    // 
    String saBrhId = operator.getOprBrhId();
    // ?
    String saOprId = operator.getOprId();
    // 
    String saInitTime = CommonFunction.getCurrentDateTime();

    TblCtlMchtInf tblCtlMchtInf = null;

    FileInputStream fileInputStream = null;

    for (File file : fileList) {

        fileInputStream = new FileInputStream(file);

        workbook = new HSSFWorkbook(fileInputStream);

        sheet = workbook.getSheetAt(0);

        fileName = fileNameList.get(fileNameIndex);

        for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            row = sheet.getRow(rowIndex);

            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
                if (row.getCell(i).getCellType() != HSSFCell.CELL_TYPE_STRING)
                    returnMsg += "[ " + fileName + " ]" + (row.getRowNum() + 1) + "" + ""
                            + (i + 1) + "???<br>";

            if (!"".equals(returnMsg))
                return returnMsg;

            saMerNo = row.getCell(0).getStringCellValue();
            // ?
            if (saMerNo.getBytes().length > 15)
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "???<br>";
            sql = "select  mcht_nm,eng_name,bank_no from TBL_MCHT_BASE_INF where MCHT_NO = '" + saMerNo + "'";

            dataList = CommonFunction.getCommQueryDAO().findBySQLQuery(sql);
            if (dataList.size() == 0)
                return "[ " + fileName + " ]" + (row.getRowNum() + 1)
                        + "??<br>";
            // ??
            saMerChName = dataList.get(0)[0].toString();
            // ??
            saMerEnName = dataList.get(0)[1].toString();
            saZoneNo = dataList.get(0)[2].toString();

            //            saLimitAmt = row.getCell(1).getStringCellValue();
            //            saLimitAmt = CommonFunction.transYuanToFen(saLimitAmt);

            // ??
            /*if(!CommonFunction.isAllDigit(saLimitAmt))
               return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "????<br>";
                    
            if(saLimitAmt.getBytes().length > 12) 
               return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/

            //            saAction = row.getCell(2).getStringCellValue();
            // ?
            /*if(!("1".equals(saAction) || "2".equals(saAction) || "3".equals(saAction) || "4".equals(saAction)))
               return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/

            // ??????
            tblCtlMchtInf = new TblCtlMchtInf();
            tblCtlMchtInf.setId(saMerNo);
            tblCtlMchtInf.setSaMerChName(saMerChName);
            tblCtlMchtInf.setSaMerEnName(saMerEnName);
            tblCtlMchtInf.setSaZoneNo(saZoneNo);
            //            tblCtlMchtInf.setSaLimitAmt(saLimitAmt);
            //            tblCtlMchtInf.setSaAction(saAction);
            tblCtlMchtInf.setSaInitZoneNo(saBrhId);
            tblCtlMchtInf.setSaInitOprId(saOprId);
            tblCtlMchtInf.setSaInitTime(saInitTime);
            tblCtlMchtInfDAO.saveOrUpdate(tblCtlMchtInf);
        }
        fileInputStream.close();
        fileNameIndex++;
    }
    return Constants.SUCCESS_CODE;
}

From source file:com.ivstars.astrology.util.LocationProvider.java

License:Open Source License

public Location[] listLocations(String province) {
    HSSFSheet sheet = wb.getSheet(province);
    int begin = sheet.getFirstRowNum();
    int end = sheet.getLastRowNum();
    Location[] locs = new Location[end - begin + 1];
    for (int i = begin; i <= end; i++) {
        locs[i - begin] = createLocation(sheet.getRow(i), province);
    }//w  w  w  . j  a  v  a  2 s .c o m
    return locs;
}

From source file:com.mebigfatguy.yank.YankTask.java

License:Apache License

private List<Artifact> readArtifactList() throws IOException {
    BufferedInputStream bis = null;
    List<Artifact> artifacts = new ArrayList<Artifact>();

    try {//from   w w  w  .  j av a  2  s  . c  om
        bis = new BufferedInputStream(new FileInputStream(xlsFile));
        POIFSFileSystem poifs = new POIFSFileSystem(bis);
        HSSFWorkbook workBook = new HSSFWorkbook(poifs);

        HSSFSheet sheet = workBook.getSheetAt(0);

        Map<ColumnType, Integer> columnHeaders = getColumnInfo(sheet);
        Integer typeColumn = columnHeaders.get(ColumnType.TYPE_COLUMN);
        Integer classifierColumn = columnHeaders.get(ColumnType.CLASSIFIER_COLUMN);
        String groupId = "";
        String artifactId = "";
        String type = JAR;
        String version = "";
        String classifier = "";

        for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); ++i) {
            HSSFRow row = sheet.getRow(i);
            if (row != null) {
                HSSFCell cell = row.getCell(columnHeaders.get(ColumnType.GROUP_COLUMN));
                if (cell != null) {
                    String gId = cell.getStringCellValue().trim();
                    if (!gId.isEmpty()) {
                        groupId = gId;
                    }
                }

                cell = row.getCell(columnHeaders.get(ColumnType.ARTIFACT_COLUMN));
                if (cell != null) {
                    String aId = cell.getStringCellValue().trim();
                    if (!aId.isEmpty()) {
                        artifactId = aId;
                    }
                }

                cell = row.getCell(columnHeaders.get(ColumnType.VERSION_COLUMN));
                if (cell != null) {
                    String v;
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        v = String.valueOf(cell.getNumericCellValue());
                    } else {
                        v = cell.getStringCellValue().trim();
                    }
                    if (!v.isEmpty()) {
                        version = v;
                    }
                }

                cell = (typeColumn != null) ? row.getCell(typeColumn.intValue()) : null;
                if (cell != null) {
                    type = cell.getStringCellValue().trim();
                }

                cell = (classifierColumn != null) ? row.getCell(classifierColumn.intValue()) : null;
                if (cell != null) {
                    classifier = cell.getStringCellValue().trim();
                }

                if (groupId.isEmpty() || artifactId.isEmpty() || version.isEmpty()) {
                    if (groupId.isEmpty() || version.isEmpty()) {
                        getProject().log("Row " + row.getRowNum() + ": Invalid artifact specified: [groupId: "
                                + groupId + ", artifactId: " + artifactId + ", classifier: " + classifier
                                + ", version: " + version + "]");
                    }
                } else {
                    artifacts.add(new Artifact(groupId, artifactId, type, classifier, version));
                }
            }

            artifactId = "";
            classifier = "";
            type = JAR;
        }

        getProject().log(sheet.getLastRowNum() + " rows read from " + xlsFile, Project.MSG_VERBOSE);
    } finally {
        Closer.close(bis);
    }

    return artifacts;
}

From source file:com.mebigfatguy.yank.YankTask.java

License:Apache License

private Map<ColumnType, Integer> getColumnInfo(HSSFSheet sheet) {
    int firstRow = sheet.getFirstRowNum();
    HSSFRow row = sheet.getRow(firstRow);

    Map<ColumnType, Integer> columnHeaders = new EnumMap<ColumnType, Integer>(ColumnType.class);

    for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); ++i) {
        HSSFCell cell = row.getCell(i);/*from   w  ww .  j av  a  2s  . co  m*/

        if (cell != null) {
            String value = cell.getStringCellValue();
            if (value != null) {
                value = value.trim().toLowerCase();
                if (value.startsWith("group")) {
                    columnHeaders.put(ColumnType.GROUP_COLUMN, i);
                } else if (value.startsWith("artifact")) {
                    columnHeaders.put(ColumnType.ARTIFACT_COLUMN, i);
                } else if (value.startsWith("type")) {
                    columnHeaders.put(ColumnType.TYPE_COLUMN, i);
                } else if (value.startsWith("version")) {
                    columnHeaders.put(ColumnType.VERSION_COLUMN, i);
                } else if (value.startsWith("classifier") || value.startsWith("alternate")) {
                    columnHeaders.put(ColumnType.CLASSIFIER_COLUMN, i);
                }
                if (columnHeaders.size() == 4) {
                    return columnHeaders;
                }
            }
        }
    }

    if (columnHeaders.size() >= 3)
        return columnHeaders;

    throw new BuildException(
            "Input yank xls file (" + xlsFile + ") does not contains GroupId, ArtifactId, or Version columns");
}

From source file:com.report.excel.ExcelToHtmlConverter.java

License:Apache License

protected void processSheet(HSSFSheet sheet) {
    //processSheetHeader( htmlDocumentFacade.getBody(), sheet );
    sheet.setAutobreaks(true);//from   w ww . ja  v  a 2  s  .c  o m

    final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
    if (physicalNumberOfRows <= 0)
        return;

    Element table = htmlDocumentFacade.createTable();
    htmlDocumentFacade.addStyleClass(table, cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;");

    Element tableBody = htmlDocumentFacade.createTableBody();

    final CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.buildMergedRangesMap(sheet);

    final List<Element> emptyRowElements = new ArrayList<Element>(physicalNumberOfRows);
    int maxSheetColumns = 1;
    for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
        HSSFRow row = sheet.getRow(r);

        if (row == null)
            continue;

        if (!isOutputHiddenRows() && row.getZeroHeight())
            continue;

        Element tableRowElement = htmlDocumentFacade.createTableRow();
        htmlDocumentFacade.addStyleClass(tableRowElement, cssClassPrefixRow,
                "height:" + (row.getHeight() / 20f) + "pt;");

        int maxRowColumnNumber = processRow(mergedRanges, row, tableRowElement);

        if (maxRowColumnNumber == 0) {
            emptyRowElements.add(tableRowElement);
        } else {
            if (!emptyRowElements.isEmpty()) {
                for (Element emptyRowElement : emptyRowElements) {
                    tableBody.appendChild(emptyRowElement);
                }
                emptyRowElements.clear();
            }

            tableBody.appendChild(tableRowElement);
        }
        maxSheetColumns = Math.max(maxSheetColumns, maxRowColumnNumber);
    }

    processColumnWidths(sheet, maxSheetColumns, table);

    if (isOutputColumnHeaders()) {
        //processColumnHeaders( sheet, maxSheetColumns, table );
    }

    table.appendChild(tableBody);

    htmlDocumentFacade.getBody().appendChild(table);
}