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

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

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

Introduction

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

Prototype

@Override
public HSSFRow getRow(int rowIndex)

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

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);/*  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.mohammad.donkiello.CustomerManager.java

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.ORANGE.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);// w ww. ja v  a2 s.  c o  m
    }
}

From source file:com.ms.commons.test.datareader.impl.ExcelReadUtil.java

License:Open Source License

protected static MemoryTable readSheet(String name, HSSFSheet sheet) {
    HSSFRow headRow = sheet.getRow(0);
    int maxRows = sheet.getPhysicalNumberOfRows();
    String[] headFields = readSheetHead(headRow);

    MemoryTable table = new MemoryTable(name.trim());
    List<MemoryRow> rowList = new ArrayList<MemoryRow>();
    for (int i = 1; i < maxRows; i++) {
        HSSFRow row = sheet.getRow(i);//from  w w  w . j a  v  a2s  . co  m
        MemoryRow memoryRow = readSheetRow(headFields, row);
        if (memoryRow == null) {
            break;
        }
        rowList.add(memoryRow);
    }
    table.setRowList(rowList);
    return table;
}

From source file:com.ms.commons.test.datareader.impl.ExcelReadUtil.java

License:Open Source License

/**
 * A column means an item in table. With horizontal style, in contrast, a row represents an item in table.
 * /*from w w w  .  j av a 2s. com*/
 * @author Qiu Shuo
 */
private static MemoryTable readSheetWithVerticalStyle(String name, HSSFSheet sheet) {
    MemoryTable table = new MemoryTable(name.trim());
    List<MemoryRow> itemList = new ArrayList<MemoryRow>();
    int maxRows = sheet.getPhysicalNumberOfRows();
    int maxItemNumPlusOne = 0;
    // get maxItemNumPlusOne
    {
        for (int i = 0; i < maxRows; i++) {
            HSSFRow row = sheet.getRow(i);
            int cur = row.getLastCellNum();
            maxItemNumPlusOne = (cur > maxItemNumPlusOne) ? cur : maxItemNumPlusOne;
        }
    }
    for (int i = 0; i < maxRows; i++) {
        HSSFRow row = sheet.getRow(i);
        HSSFCell columnNameCell = row.getCell((short) 0);
        String columnName = columnNameCell.getRichStringCellValue().getString();
        for (short j = 1; j < maxItemNumPlusOne; j++) {
            HSSFCell cell = row.getCell(j);
            MemoryField field = readCellValue(columnName, cell);
            while (itemList.size() <= j - 1) {
                itemList.add(new MemoryRow(new ArrayList<MemoryField>()));
            }
            MemoryRow item = itemList.get(j - 1);
            item.getFieldList().add(field);
        }
    }
    table.setRowList(itemList);
    return table;
}

From source file:com.mx.santander.lh.obpyme.Utilerias.ObtenerDatosExcel.java

public MarketingObjeto RecuperarDatosExcel() throws FileNotFoundException, IOException {

    MarketingObjeto objetoMarketing = new MarketingObjeto();

    FileInputStream file = new FileInputStream("DataTestMarketing.xls");
    HSSFWorkbook workBook = new HSSFWorkbook(file);
    HSSFSheet sheet = workBook.getSheetAt(0);

    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        objetoMarketing.setCasoPrueba(sheet.getRow(i).getCell(0).toString());
        objetoMarketing.setEmpresaMarketing(sheet.getRow(i).getCell(1).toString());
        objetoMarketing.setPosicionamientoMarketingTopURL(sheet.getRow(i).getCell(2).toString());
        objetoMarketing.setTopNumber(Integer.parseInt(sheet.getRow(i).getCell(3).toString()));
    }//from   w ww .  j a  va 2s.  c o  m

    return objetoMarketing;

}

From source file:com.mycompany.corevaluecontest.InsertEmployee_Excecl.java

public void uploadExcel() {
    try {/*from   ww  w .java2  s .  co m*/

        conn = Database.getConnection();

        // Use excel file insert to employee
        FileInputStream input = new FileInputStream("D:\\Excel\\Corevalue\\Employee.xls");
        //System.out.println("+++++++++++++++++");
        POIFSFileSystem fs = new POIFSFileSystem(input);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row;
        HSSFCell id, firstname, lastname, division, section, grp, position;

        int a = 0;
        int b = 2;
        int c = 3;
        int d = 5;
        int e = 6;
        int f = 7;
        int g = 8;
        int as = 0;
        String grpvalue = null;
        int x = sheet.getLastRowNum();
        //System.out.println("+++++++2+++++");
        for (int i = 1; i <= x; i++) {
            row = sheet.getRow(i);

            if (row.getCell(a).toString() != null) {
                as++;
                //System.out.println("%%%%%%%%" + as + "//////////" + x);
            }

        }
        System.out.println("++++++3++++++++");
        for (int i = 1; i <= as; i++) {
            row = sheet.getRow(i);

            id = row.getCell(a);

            String str = id.getStringCellValue().toString();
            String ans = str.substring(str.length() - 4, str.length());

            firstname = row.getCell(b);
            String firstvalue = firstname.getStringCellValue().toString();

            lastname = row.getCell(c);
            String lastvalue = lastname.getStringCellValue().toString();

            division = row.getCell(d);
            String divisionvalue = division.getStringCellValue().toString();

            section = row.getCell(e);
            String sectionvalue = section.getStringCellValue().toString();

            if (row.getCell(f) != null) {
                grp = row.getCell(f);
                grpvalue = grp.getStringCellValue();
            } else {
                grpvalue = "";
            }

            position = row.getCell(g);
            String positionvalue = position.getStringCellValue();

            String last2 = lastvalue.substring(0, 1) + "." + firstvalue;

            String sql = "INSERT INTO tblMaster_User (UserID)VALUES('" + ans + "')";

            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.execute();
            /*
             SQL_Str = "DELETE FROM PositionMaster";
             conn = Database.getConnection();
             ps = (PreparedStatement) conn.prepareStatement(SQL_Str);
             ps.execute();
                    
             String sql2 = "INSERT INTO PositionMaster (P_Name) VALUES ('" + positionvalue + "')";
                    
             ps = (PreparedStatement) conn.prepareStatement(sql2);
             ps.execute();
             */
            System.out.println("Import rows " + i);
        }

        //<p:commandButton value="Submitxx" update="@all" icon="ui-icon-check" action="#{InsertEmployee_Excecl.uploadExcel()}"/>
        conn.commit();
        ps.close();
        conn.close();
        input.close();
        System.out.println("Success import excel to mysql table");

    } catch (SQLException ex) {
        System.out.println(ex);
    } catch (IOException ioe) {
        System.out.println(ioe);
    }

}

From source file:com.netxforge.netxstudio.server.dataimport.MasterDataImporter.java

License:Open Source License

private List<EObject> processWorkSheet(int index, HSSFSheet sheet) {
    final List<EObject> result = new ArrayList<EObject>();
    setEFeatures(sheet);// w w  w . ja  v  a  2  s .c  om
    for (int i = 2; i <= sheet.getLastRowNum(); i++) {
        if (isEmptyRow(sheet.getRow(i))) {
            continue;
        }

        final EObject eObject = processRow(sheet.getRow(i));
        if (eObject != null) {
            result.add(eObject);
            objects.add(eObject);
            final RowResult rowResult = new RowResult();
            rowResult.setRow(sheet.getRow(i));
            rowResult.setEObject(eObject);
            rowResults.add(rowResult);
        }
    }
    return result;
}

From source file:com.netxforge.netxstudio.server.dataimport.MasterDataImporter.java

License:Open Source License

private void setEFeatures(HSSFSheet sheet) {
    eFeatures.clear();/*from  w  w w. j  a v a2 s. co m*/
    final Iterator<Cell> iterator = sheet.getRow(0).cellIterator();
    while (iterator.hasNext()) {
        final String name = iterator.next().getStringCellValue();
        for (final EStructuralFeature eFeature : eClassToImport.getEAllStructuralFeatures()) {
            if (name.toLowerCase().equals(eFeature.getName().toLowerCase())) {
                eFeatures.add(eFeature);
                break;
            }
        }
    }
}

From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java

@Override
public HSSFWorkbook printClaim(List<PrintClaimPojo> listPojo) throws Exception {
    HSSFWorkbook workbook = null;/*from w  w w .ja  v a2s.c om*/

    POIFSFileSystem fs = new POIFSFileSystem(
            new FileInputStream(servletContext.getRealPath("templates") + File.separator + "print_claim.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // title
    //HSSFRow row = sheet.getRow(1);
    //HSSFCell cell = row.getCell(1);
    //String title;
    //cell.setCellValue(title);
    //
    HSSFRow row;
    final int CELL_START = 1;
    final int ROW_START = sheet.getLastRowNum() - 1;
    final int CELL_END = sheet.getRow(ROW_START).getLastCellNum();

    int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent;
    //
    HSSFCellStyle[] style = new HSSFCellStyle[CELL_END];
    short styleHeight = sheet.getRow(rowCurrent).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle();
    }

    // total style
    HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END];
    short totalStyleHeight = sheet.getRow(rowTotal).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle();
    }

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
    int numQuantity = 0;
    for (PrintClaimPojo pcp : listPojo) {
        numQuantity++;

        row = sheet.createRow(rowCurrent++);
        row.setHeight(styleHeight);

        cellCurrent = CELL_START;
        createCell(row, cellCurrent++, style, numQuantity);
        createCell(row, cellCurrent++, style, pcp.getTin());
        createCell(row, cellCurrent++, style, pcp.getMfo());
        createCell(row, cellCurrent++, style, pcp.getChet());
        createCell(row, cellCurrent++, style, pcp.getPaymentNum());
        createCell(row, cellCurrent++, style, dateFormat.format(pcp.getPaymentDate()));
        createCell(row, cellCurrent++, style, pcp.getPaymentSum());
        createCell(row, cellCurrent++, style, pcp.getPaymentDetails());
    }
    List<Integer> totalCellIndexes = new ArrayList<>();
    totalCellIndexes.add(CELL_START + 6); // for payment sum columns
    createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent);

    return workbook;
}

From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java

@Override
public HSSFWorkbook printRegister(List<PrintRegisterPojo> listPojo) throws Exception {
    HSSFWorkbook workbook = null;/*from   w w w.j  a  v  a 2s  .c  o  m*/

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
            servletContext.getRealPath("templates") + File.separator + "print_register.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // title
    //HSSFRow row = sheet.getRow(1);
    //HSSFCell cell = row.getCell(1);
    //String title;
    //cell.setCellValue(title);
    //
    HSSFRow row;
    final int CELL_START = 1;
    final int ROW_START = sheet.getLastRowNum() - 1;
    final int CELL_END = sheet.getRow(ROW_START).getLastCellNum();

    int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent;
    //
    HSSFCellStyle[] style = new HSSFCellStyle[CELL_END];
    short styleHeight = sheet.getRow(rowCurrent).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle();
    }

    // total style
    HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END];
    short totalStyleHeight = sheet.getRow(rowTotal).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle();
    }

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
    int numQuantity = 0;
    for (PrintRegisterPojo prp : listPojo) {
        numQuantity++;

        row = sheet.createRow(rowCurrent++);
        row.setHeight(styleHeight);

        cellCurrent = CELL_START;
        createCell(row, cellCurrent++, style, prp.getInvoiceNum());
        createCell(row, cellCurrent++, style, prp.getPaymentNum());
        createCell(row, cellCurrent++, style, dateFormat.format(prp.getPaymentDate()));
        createCell(row, cellCurrent++, style, prp.getTin());
        createCell(row, cellCurrent++, style, prp.getName());
        createCell(row, cellCurrent++, style, prp.getPaymentSum());

    }
    List<Integer> totalCellIndexes = new ArrayList<>();
    totalCellIndexes.add(CELL_START + 5); // for payment sum columns
    createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent);

    return workbook;
}