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.siva.javamultithreading.ExcelUtil.java

private static void copySheets(HSSFWorkbook newWorkbook, HSSFSheet newSheet, HSSFSheet sheet,
        boolean copyStyle) {
    int newRownumber = newSheet.getLastRowNum() + 1;
    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 + newRownumber);
        if (srcRow != null) {
            copyRow(newWorkbook, sheet, newSheet, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }//  w  w  w.j  a v  a 2  s . c o m
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
}

From source file:com.verticon.treatment.poi.handlers.PoiUtils.java

License:Open Source License

static boolean isWorkSheetMatch(String[] header, HSSFSheet workSheet) {
    int rowNum = workSheet.getFirstRowNum();
    Row row = workSheet.getRow(rowNum);/*from   w w w  . j a  va  2s .  com*/
    for (int i = 0; i < header.length; i++) {
        Cell cell = row.getCell(i);
        // Must be a String
        if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
            System.out.println(
                    "Header Column Type Error: " + cell.getCellType() + " != " + Cell.CELL_TYPE_STRING);
            return false;
        }
        if (!header[i].equalsIgnoreCase(cell.getRichStringCellValue().getString())) {
            // System.out.println("Header Column Name Error: " + header[i]
            // + " != " + cell.getRichStringCellValue().getString());
            return false;
        }

    }
    return true;
}

From source file:com.wangzhu.poi.ExcelToHtmlConverter.java

License:Apache License

protected void processSheet(HSSFSheet sheet) {
    // this.processSheetHeader(this.htmlDocumentFacade.getBody(), sheet);

    final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
    if (physicalNumberOfRows <= 0) {
        return;//w  ww  .j a v  a  2 s.com
    }

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

    Element tableBody = this.htmlDocumentFacade.createTableBody();

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

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

        if (row == null) {
            continue;
        }

        if (!this.isOutputHiddenRows() && row.getZeroHeight()) {
            continue;
        }

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

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

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

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

    this.processColumnWidths(sheet, maxSheetColumns, table);

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

    table.appendChild(tableBody);

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

    if (null != this.getExcelImageManager()) {

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

        tableBody = this.htmlDocumentFacade.createTableBody();
        List<String> urlPaths = this.getExcelImageManager()
                .getImagePath(sheet.getDrawingPatriarch().getChildren());
        if ((urlPaths != null) && (urlPaths.size() != 0)) {
            Document document = this.htmlDocumentFacade.getDocument();

            for (int i = 0, size = urlPaths.size(); i < size; i++) {
                Element tableRowElement = this.htmlDocumentFacade.createTableRow();
                String[] urlPathArr = urlPaths.get(i).split("@");
                Element result = document.createElement("img");
                result.setAttribute("src", urlPathArr[0]);
                String imageWidth = urlPathArr[1];
                String imageHeight = urlPathArr[2];
                result.setAttribute("style",
                        "width:" + imageWidth + "in;height:" + imageHeight + "in;vertical-align:text-bottom;");

                Element tableCellElement = this.htmlDocumentFacade.createTableCell();
                tableCellElement.appendChild(result);
                tableRowElement.appendChild(tableCellElement);
                tableBody.appendChild(tableRowElement);
            }
            table.appendChild(tableBody);
            this.htmlDocumentFacade.getBody().appendChild(table);
        }
    }

}

From source file:com.xpn.xwiki.plugin.lucene.textextraction.MSExcelTextExtractor.java

License:Open Source License

/**
 * It will parse the sheet with row wise and get the text from the sheet.
 *//*  www. j ava2s  .  c o  m*/
private void parseSheet(HSSFSheet sheet, StringBuffer cleanBuffer) {
    int firstRow = sheet.getFirstRowNum();
    int lastRow = sheet.getLastRowNum();
    for (int rowIdx = firstRow; rowIdx <= lastRow; rowIdx++) {
        HSSFRow row = sheet.getRow(rowIdx);

        if (row != null) {
            parseRow(row, cleanBuffer);
        }
    }
}

From source file:fft.FFT.java

License:Open Source License

public static void main(String[] args) throws IOException {

    InputStream myxls = new FileInputStream("/Users/huangge/Documents/workspace/fft/src/BxDec99.xls");
    HSSFWorkbook wb = new HSSFWorkbook(myxls);
    HSSFSheet sheet = wb.getSheetAt(0);
    int rowStart = Math.min(15, sheet.getFirstRowNum());
    int rowEnd = Math.max(1400, sheet.getLastRowNum());
    Row r_for_rowCount = sheet.getRow(0);
    int lastColumn = Math.min(r_for_rowCount.getLastCellNum(), 1000);

    double[][] res = new double[lastColumn - 1][rowEnd];
    Workbook wb_out = new HSSFWorkbook(); // or new XSSFWorkbook();
    Sheet sheet_out = wb_out.createSheet();
    int count = 0;
    for (int j = 1; j < lastColumn; j++) {//make res matrix
        count = 0;// ww w  .java  2 s .  c  o m
        for (int i = 1; i <= rowEnd; i++) {
            Row r = sheet.getRow(i);
            Cell c = r.getCell(3, Row.RETURN_BLANK_AS_NULL);
            if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {
                break;
            } else if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                res[j - 1][i - 1] = c.getNumericCellValue();
                count++;
            }
        }
    }

    int N = count;
    int nextPowTwo = 1;
    while (nextPowTwo < N) {
        nextPowTwo += nextPowTwo;
    }
    N = nextPowTwo;
    FFT fft = new FFT(N);
    double[] window = fft.getWindow();
    double[] re = new double[N];
    Arrays.fill(re, 0);
    ;
    double[] im = new double[N];

    for (int i = 0; i < re.length / 2; i++) {//initial sheet
        Row row_cre = sheet_out.createRow(i);
        for (int k = 0; k < lastColumn - 1; k++) {
            Cell cell = row_cre.createCell((short) (k));
        }
    }

    for (int j = 1; j < lastColumn; j++) {//make result sheet
        for (int i = 0; i < count; i++) {
            re[i] = res[j - 1][i];
            im[i] = 0;
        }
        beforeAfter(fft, re, im);
        for (int i = 0; i < re.length / 2; i++) {
            Row row_out = sheet_out.getRow(i);
            Cell cell = row_out.getCell((short) (j - 1));
            cell.setCellValue(Math.abs(re[i]));
        }

    }

    FileOutputStream fileOut//write file
            = new FileOutputStream("/Users/huangge/Documents/workspace/fft/src/workbook.xls");
    wb_out.write(fileOut);
    fileOut.close();

    long time = System.currentTimeMillis();
    double iter = 10;
    for (int i = 0; i < iter; i++)
        // fft.fft(re,im);
        time = System.currentTimeMillis() - time;
    System.out.println("Averaged " + (time / iter) + "ms per iteration");
}

From source file:FormatConvert.exceloperation.Excel2csv.java

public static void copySheets2CSV(HSSFSheet sheet, String csvfile) {
    int maxColumnNum = 0;

    try {/*from  w  w w  .  j  a  va2s .c  o  m*/
        FileWriter fw = new FileWriter(csvfile);

        String str = "";
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            HSSFRow srcRow = sheet.getRow(i);

            if (srcRow != null) {
                for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
                    if (j != srcRow.getLastCellNum()) {
                        str = str + srcRow.getCell(j).getStringCellValue() + ",";
                    } else {
                        str = str + srcRow.getCell(j).getStringCellValue() + "\r\n";
                    }

                }
                fw.append(str);
            }
            str = "";
        }

        fw.flush();
        fw.close();
    } catch (IOException ex) {

    }
    //Util.copyPictures(newSheet,sheet) ;
}

From source file:Import.Utils.XSSFConvert.java

/**
 * @param destination the sheet to create from the copy.
 * @param the sheet to copy./*w w w . j  a v  a2s .co m*/
 * @param copyStyle true copy the style.
 */
public static void copySheets(HSSFSheet source, XSSFSheet destination, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
    for (int i = source.getFirstRowNum(); i <= source.getLastRowNum(); i++) {
        HSSFRow srcRow = source.getRow(i);
        XSSFRow destRow = destination.createRow(i);
        if (srcRow != null) {
            copyRow(source, destination, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        destination.setColumnWidth(i, source.getColumnWidth(i));
    }
}

From source file:is.idega.idegaweb.egov.fsk.business.FSKBusinessBean.java

License:Open Source License

@Override
public Map importExcelFile(UploadFile file, Object coursePK, int column) {
    Map map = new HashMap();

    try {//ww  w  . j  a  v a 2s  .co  m
        Course course = getCourse(coursePK);
        Group group = course.getGroup();

        FileInputStream input = new FileInputStream(file.getRealPath());
        HSSFWorkbook wb = new HSSFWorkbook(input);

        HSSFSheet sheet = wb.getSheetAt(0);

        NumberFormat format = NumberFormat.getNumberInstance();
        format.setGroupingUsed(false);
        format.setMinimumIntegerDigits(10);

        Collection imported = new ArrayList();
        Collection alreadyImported = new ArrayList();
        Collection outsideCommune = new ArrayList();
        Collection outsideAgeRange = new ArrayList();
        Collection invalidPersonalID = new ArrayList();
        Collection noUserFound = new ArrayList();

        for (int a = sheet.getFirstRowNum(); a <= sheet.getLastRowNum(); a++) {
            HSSFRow row = sheet.getRow(a);
            HSSFCell cell = row.getCell((short) (column - 1));
            if (cell == null) {
                continue;
            }

            String personalID = null;
            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                personalID = cell.getStringCellValue();
            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                personalID = String.valueOf(new Double(cell.getNumericCellValue()).longValue());
            } else {
                personalID = cell.getStringCellValue();
            }

            try {
                personalID = format.format(format.parse(personalID.replaceAll("-", "")));
            } catch (ParseException e1) {
                e1.printStackTrace();
                continue;
            }

            if (SocialSecurityNumber.isValidSocialSecurityNumber(personalID, getDefaultLocale())) {
                try {
                    User user = getUserBusiness().getUser(personalID);
                    if (!group.hasRelationTo(((Integer) user.getPrimaryKey()).intValue())) {
                        IWTimestamp dateOfBirth = new IWTimestamp(user.getDateOfBirth());
                        dateOfBirth.setMonth(1);
                        dateOfBirth.setDay(1);
                        Age age = new Age(dateOfBirth.getDate());

                        if (age.getYears(course.getStartDate()) < 6
                                || age.getYears(course.getStartDate()) > 18) {
                            outsideAgeRange.add(user);
                            continue;
                        }

                        if (!getUserBusiness().isCitizenOfDefaultCommune(user)) {
                            outsideCommune.add(user);
                            continue;
                        }

                        group.addGroup(user);
                        imported.add(user);
                    } else {
                        alreadyImported.add(user);
                    }
                } catch (FinderException e) {
                    noUserFound.add(personalID);
                }
            } else {
                invalidPersonalID.add(personalID);
            }
        }

        map.put(FSKConstants.REGISTRATION_CODE_REGISTERED, imported);
        map.put(FSKConstants.REGISTRATION_CODE_ALREADY_REGISTERED, alreadyImported);
        map.put(FSKConstants.REGISTRATION_CODE_OUTSIDE_COMMUNE, outsideCommune);
        map.put(FSKConstants.REGISTRATION_CODE_OUTSIDE_AGE_RANGE, outsideAgeRange);
        map.put(FSKConstants.REGISTRATION_CODE_INVALID_PERSONAL_ID, invalidPersonalID);
        map.put(FSKConstants.REGISTRATION_CODE_NO_USER_FOUND, noUserFound);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return map;
}

From source file:it.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java

License:Apache License

public static List<String[]> getCells(HSSFSheet sheet, Short[] columnAbsolutePositions,
        boolean looseConstraint) {
    List<String[]> cells = new ArrayList<String[]>();
    for (int rowAbsolutePosition = sheet.getFirstRowNum() + 1; rowAbsolutePosition <= sheet
            .getLastRowNum(); rowAbsolutePosition++) {
        HSSFRow row = sheet.getRow(rowAbsolutePosition);

        if (row == null) {
            if (looseConstraint) {
                log.debug("empty row in the absolute position \"" + rowAbsolutePosition + "\"");
            } else {
                log.warn("row in the absolute position \"" + rowAbsolutePosition + "\" has problem");
            }//  w  ww  .j a v  a2  s  . co m
            continue;
        }

        String cellStringValues[] = new String[5];
        for (int columnRelativePosition = 0; columnRelativePosition < cellStringValues.length; columnRelativePosition++) {
            HSSFCell cell = row.getCell(columnAbsolutePositions[columnRelativePosition]);
            String cellStringValue = cell != null ? cell.getStringCellValue() : null;
            if (cellStringValue != null && cellStringValue.trim().equals("")) {
                cellStringValue = null;
            }
            cellStringValues[columnRelativePosition] = cellStringValue;
        }

        if (cellStringValues[0] == null && cellStringValues[1] == null && cellStringValues[2] == null
                && cellStringValues[3] == null && cellStringValues[4] == null) {
            if (looseConstraint) {
                log.debug("empty row in the absolute position \"" + rowAbsolutePosition + "\"");
            } else {
                log.warn("row in the absolute position \"" + rowAbsolutePosition
                        + "\" has problem, it's {null, null, null, null, null}");
            }
        } else {
            cells.add(cellStringValues);
        }
    }

    dumpCells(cells);
    validateCells(cells, looseConstraint);

    return cells;
}

From source file:it.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java

License:Apache License

private static void dumpSheet(HSSFSheet sheet) {
    if (sheet == null) {
        log.debug("The sheet to dump is null!");
        return;/*from   ww  w  .  ja va  2s  . co m*/
    }

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        log.debug("row#" + i + "=");
        HSSFRow row = sheet.getRow(i);
        if (row == null) {
            log.debug("\t| " + NULL_RAPPRESENTATION);
            continue;
        }
        for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
            HSSFCell cell = row.getCell((short) j);
            String cellValue = getCellValue(cell);
            log.debug("\t| " + cellValue);
        }
        log.debug("\n");
    }
}