Example usage for org.apache.poi.hssf.usermodel HSSFRow getCell

Introduction

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

Prototype

@Override
public HSSFCell getCell(int cellnum) 

Source Link

Document

Get the hssfcell representing a given column (logical cell) 0-based.

Usage

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.
 * //w  w  w  .  j  a  v  a 2  s  . c  o m
 * @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.ms.commons.test.datareader.impl.ExcelReadUtil.java

License:Open Source License

protected static MemoryRow readSheetRow(String[] headFields, HSSFRow row) {
    if (row == null) {
        return null;
    }//from ww  w.  ja va  2  s  .  co  m

    boolean isAllFieldEmpty = true;
    List<MemoryField> fieldList = new ArrayList<MemoryField>();
    for (short i = 0; i < headFields.length; i++) {
        MemoryField field = readCellValue(headFields[i], row.getCell(i));
        if (field.getType() != MemoryFieldType.Null) {
            isAllFieldEmpty = false;
        }
        fieldList.add(field);
    }
    if (isAllFieldEmpty) {
        return null;
    } else {
        return new MemoryRow(fieldList);
    }
}

---

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

License:Open Source License

protected static String[] readSheetHead(HSSFRow headRow) {
    List<String> headList = new ArrayList<String>();
    int n = headRow.getPhysicalNumberOfCells();
    for (Integer i = 0; i < n; i++) {
        HSSFCell cell = headRow.getCell(i.shortValue());
        String head = readCellValue(null, cell).getStringValue();
        if (head == null || head.trim().length() == 0) {
            break;
        }/*from   w  w  w  .ja  va 2 s .  c o m*/
        headList.add(readCellValue(null, cell).getStringValue());
    }
    return headList.toArray(new String[] {});
}

---

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

public void uploadExcel() {
    try {/*from   w  w w . ja  v a 2 s .  c  o  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 EObject processRow(HSSFRow row) {
    if (row == null) {
        return null;
    }/*from w ww. j a va 2 s  .  c om*/
    if (isEmptyRow(row)) {
        return null;
    }

    final EObject result = EcoreUtil.create(eClassToImport);
    for (int i = 0; i < eFeatures.size(); i++) {
        final EStructuralFeature eFeature = eFeatures.get(i);
        if (row.getCell(i) == null) {
            continue;
        }
        if (eFeature instanceof EReference) {
            continue;
        }
        final String value = row.getCell(i).getStringCellValue();
        final int maxLength = ExtendedMetaData.INSTANCE
                .getMaxLengthFacet(((EAttribute) eFeature).getEAttributeType());
        if (value == null || value.trim().length() == 0) {
            continue;
        }
        if (maxLength > 0 && value != null && value.length() > maxLength) {
            result.eSet(eFeature, value.substring(0, maxLength));
        } else {
            result.eSet(eFeature, value);
        }
    }
    return result;
}

---

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

License:Open Source License

private void processEReferences(EObject result, HSSFRow row) {
    for (int i = 0; i < eFeatures.size(); i++) {
        final EStructuralFeature eFeature = eFeatures.get(i);
        if (row.getCell(i) == null) {
            continue;
        }/*from   w  ww . jav a2 s  .co  m*/
        if (eFeature instanceof EAttribute) {
            continue;
        }
        final String value = row.getCell(i).getStringCellValue();
        if (value == null || value.trim().length() == 0) {
            continue;
        }
        final EReference eReference = (EReference) eFeature;
        result.eSet(eFeature, getObject(eReference, value));
    }
}

---

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

@Override
public HSSFWorkbook printPaymentManual(BankStatement bs) throws Exception {
    HSSFWorkbook workbook = null;// w  ww.  j  a  va2 s . c  om

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
            servletContext.getRealPath("templates") + File.separator + "payment_manual.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);

    HSSFRow row = sheet.getRow(0);
    row.getCell(1).setCellValue(bs.getId());

    final int CELL_START = 1;
    final int ROW_START = sheet.getLastRowNum();
    final int CELL_END = sheet.getRow(ROW_START).getLastCellNum();

    int rowCurrent = ROW_START, 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();
    }

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
    row = sheet.createRow(3);

    createCell(row, 1, style, "");
    createCell(row, 2, style, bs.getMfo());
    createCell(row, 3, style, bs.getChet());
    createCell(row, 4, style, bs.getPaymentNum());
    createCell(row, 5, style, dateFormat.format(bs.getPaymentDate()));
    createCell(row, 6, style, "");
    createCell(row, 7, style, bs.getTin());

    return workbook;
}

---

From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);/* w  w  w  .java  2  s. co  m*/
    HSSFRow header = sheet.getRow(0);
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        header.getCell(i).setCellStyle(cellStyle);
    }
}

---

From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java

private void validateAndResetTollTagAndPlateNumber(HSSFRow row, EzToll ezToll) {
    String tollNum = getCellValue(row.getCell(3)).toString();
    if (StringUtils.isEmpty(tollNum)) {
        return;/*from  w w w .  j av  a 2s  . c  om*/
    }

    String tollQuery = "select obj from VehicleTollTag obj where obj.tollTagNumber='" + tollNum + "'";
    if (ezToll.getToolcompany() != null) {
        tollQuery += " and obj.tollCompany='" + ezToll.getToolcompany().getId() + "'";
    }

    List<VehicleTollTag> vehicleTollTags = genericDAO.executeSimpleQuery(tollQuery);
    if (vehicleTollTags != null && vehicleTollTags.size() > 0) {
        String plateNum = getCellValue(row.getCell(4)).toString();
        if (StringUtils.contains(tollNum, plateNum) || StringUtils.contains(plateNum, tollNum)) {
            row.getCell(4).setCellValue(StringUtils.EMPTY);
        }
    } else {
        row.getCell(3).setCellValue(StringUtils.EMPTY);
    }
}

---

From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java

private Vehicle retrieveVehicleForPlate(String plateNum, HSSFRow row) {
    String transactiondate = StringUtils.EMPTY;
    if (validDate(getCellValue(row.getCell(6)))) {
        transactiondate = dateFormat.format(((Date) getCellValue(row.getCell(6))).getTime());
    }/*from   w w w  . ja  v a 2s .  c o  m*/

    String vehicleQuery = "select obj from Vehicle obj where obj.plate='" + plateNum + "'"
            + " and obj.validFrom <='" + transactiondate + "' and obj.validTo >= '" + transactiondate + "'";
    System.out.println("******* vehicleQuery  ======>" + vehicleQuery);

    List<Vehicle> vehicleList = genericDAO.executeSimpleQuery(vehicleQuery.toString());
    if (vehicleList == null || vehicleList.isEmpty()) {
        return null;
    } else {
        return vehicleList.get(0);
    }
}

---

• «
• 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
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »