Example usage for org.apache.poi.ss.usermodel Row getCell

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

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

Usage

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

License:Apache License

public List<Row> readExcel(Workbook wb) {
    Sheet sheet = null;/*from  w w w . jav a2 s. c o  m*/

    if (onlyReadOneSheet) {//??sheet

        System.out.println("selectedSheetName:" + selectedSheetName);
        // ??sheet(?????)  
        sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
        System.out.println(sheet.getSheetName());

    } else {
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {//??Sheet

            sheet = wb.getSheetAt(i);
            logger.info(sheet.getSheetName());

            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//??
                Row row = sheet.getRow(j);
                for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//???
                    System.out.print(row.getCell(k) + "\t");
                }
                System.out.println("---Sheet" + i + "?---");
            }
        }
    }
    return null;
}

---

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

License:Apache License

/** 
 * ?Excel /*from w w  w .  ja  v a2s. com*/
 *  
 * @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;
}

---

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

License:Apache License

/** 
 * Excel? //from w w  w . ja v a2s. c  o  m
 *  
 * @Title: WriteExcel 
 * @Date : 2014-9-11 ?01:33:59 
 * @param wb 
 * @param rowList 
 * @param xlsPath 
 */
private void writeExcel(Workbook wb, List<Row> rowList, String xlsPath) {

    if (wb == null) {
        out("???");
        return;
    }

    Sheet sheet = wb.getSheetAt(0);// sheet  

    // ???????  
    int lastRowNum = isOverWrite ? startReadPos : sheet.getLastRowNum() + 1;
    int t = 0;//  
    out("???" + rowList.size());
    for (Row row : rowList) {
        if (row == null)
            continue;
        // ???  
        int pos = findInExcel(sheet, row);

        Row r = null;// ??????  
        if (pos >= 0) {
            sheet.removeRow(sheet.getRow(pos));
            r = sheet.createRow(pos);
        } else {
            r = sheet.createRow(lastRowNum + t++);
        }

        //??  
        CellStyle newstyle = wb.createCellStyle();

        //?  
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = r.createCell(i);// ??  
            cell.setCellValue(getCellValue(row.getCell(i)));// ???  
            // cell.setCellStyle(row.getCell(i).getCellStyle());//  
            if (row.getCell(i) == null)
                continue;
            copyCellStyle(row.getCell(i).getCellStyle(), newstyle); // ????  
            cell.setCellStyle(newstyle);// ?  
            // sheet.autoSizeColumn(i);//  
        }
    }
    out("???:" + (rowList.size() - t) + " ?" + t);

    // ??  
    setMergedRegion(sheet);

    try {
        // ??Excel  
        FileOutputStream outputStream = new FileOutputStream(xlsPath);
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
    } catch (Exception e) {
        out("Excel?? ");
        e.printStackTrace();
    }
}

---

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

License:Apache License

/** 
 * ???Excel //from ww  w .  j av a  2 s .c  om
 *  
 * @Title: findInExcel 
 * @Date : 2014-9-11 ?02:23:12 
 * @param sheet 
 * @param row 
 * @return 
 */
private int findInExcel(Sheet sheet, Row row) {
    int pos = -1;

    try {
        // ??  
        if (isOverWrite || !isNeedCompare) {
            return pos;
        }
        for (int i = startReadPos; i <= sheet.getLastRowNum() + endReadPos; i++) {
            Row r = sheet.getRow(i);
            if (r != null && row != null) {
                String v1 = getCellValue(r.getCell(comparePos));
                String v2 = getCellValue(row.getCell(comparePos));
                if (v1.equals(v2)) {
                    pos = i;
                    break;
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return pos;
}

---

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

@Override
public void parseAndSavePaymentManual(File file, Long issuerSerialNumber, String issuerIp) throws Exception {
    FileInputStream fis = new FileInputStream(file);
    POIFSFileSystem fs = new POIFSFileSystem(fis);
    HSSFWorkbook workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);

    String bankStatementId = null;
    List<Payment> paymentList = new LinkedList<Payment>();
    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");

    int rowCurrent = 0, cellCurrent = 0;
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        rowCurrent++;//w w w . j av  a2 s.c om
        cellCurrent = 0;

        Row row = rowIterator.next();

        if (rowCurrent == 1) { // bank statement id
            bankStatementId = row.getCell(1).getStringCellValue();

        } else if (rowCurrent > 3) {
            Payment payment = new Payment();
            payment.setTin(row.getCell(1).getStringCellValue());
            payment.setPaymentNum(row.getCell(4).getStringCellValue());
            payment.setPaymentDate(dateFormat.parse(row.getCell(5).getStringCellValue()));
            payment.setPaymentSum(new BigDecimal(row.getCell(6).getNumericCellValue()));
            payment.setTinDebtor(row.getCell(7).getStringCellValue());
            payment.setSourceCode((short) 1);
            payment.setState((short) 1);
            payment.setClaim((short) 0);

            paymentList.add(payment);
        }
    }
    fis.close();

    paymentDao.savePaymentManual(bankStatementId, paymentList, issuerSerialNumber, issuerIp);

}

---

From source file:com.ocs.dynamo.importer.impl.BaseXlsImporter.java

License:Apache License

/**
 * Checks if any cell in the row contains a certain (String) value
 * //from w  ww.  j  av  a2  s  .com
 * @param row
 * @param value
 * @return
 */
protected boolean containsStringValue(Row row, String value) {
    if (row == null || !row.iterator().hasNext()) {
        return false;
    }

    boolean found = false;
    for (int i = row.getFirstCellNum(); !found && i < row.getLastCellNum(); i++) {
        if (row.getCell(i) != null) {
            try {
                found = value.equalsIgnoreCase(row.getCell(i).getStringCellValue());
            } catch (Exception ex) {
                // do nothing
            }
        }
    }
    return found;
}

---

From source file:com.ocs.dynamo.importer.impl.BaseXlsImporter.java

License:Apache License

@Override
protected Cell getUnit(Row row, XlsField field) {
    return row.getCell(row.getFirstCellNum() + field.index());
}

---

From source file:com.oneis.jsinterface.generate.KGenerateXLS.java

License:Mozilla Public License

@Override
protected void writeRow(int rowNumber, ArrayList<Object> row, ArrayList<Object> rowOptions, boolean isHeaderRow,
        boolean pageBreakBefore) {
    Row r = this.sheet.createRow(rowNumber);

    if (pageBreakBefore && rowNumber > 0) {
        this.sheet.setRowBreak(rowNumber - 1);
    }/*  w  w  w.j a va  2 s . com*/

    int rowSize = row.size();
    for (int i = 0; i < rowSize; ++i) {
        Object value = row.get(i); // ConsString is checked
        if (value != null) {
            Cell c = r.createCell(i);
            if (value instanceof Number) {
                c.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof CharSequence) {
                c.setCellValue(((CharSequence) value).toString());
            } else if (value instanceof Date) {
                c.setCellValue((Date) value);
                // Check to see if option is for dates only
                boolean dateAndTimeStyle = true;
                String options = (String) getOptionsFromArrayList(rowOptions, i, String.class); // ConsString is checked by getOptionsFromArrayList()
                if (options != null && options.equals("date")) {
                    dateAndTimeStyle = false;
                }
                if (dateCellStyle == null) {
                    // Only create one each of the date cell styles per workbook to save space.
                    dateCellStyle = workbook.createCellStyle();
                    dateCellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm"));
                    dateOnlyCellStyle = workbook.createCellStyle();
                    dateOnlyCellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd"));
                }
                c.setCellStyle(dateAndTimeStyle ? dateCellStyle : dateOnlyCellStyle);
                // Set column width so the dates don't come out as ########## on causal viewing
                setMinimumWidth(i, dateAndTimeStyle ? DATE_AND_TIME_COLUMN_WIDTH : DATE_COLUMN_WIDTH);
            }
        }
    }

    if (isHeaderRow) {
        // Make sure the row is always on screen
        this.sheet.createFreezePane(0, 1, 0, 1);
        // Style the row
        CellStyle style = this.workbook.createCellStyle();
        style.setBorderBottom(CellStyle.BORDER_THIN);
        Font font = this.workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(font);
        r.setRowStyle(style);
        // Style the cells
        for (int s = 0; s < rowSize; ++s) {
            Cell c = r.getCell(s);
            if (c == null) {
                c = r.createCell(s);
            }
            c.setCellStyle(style);
        }
    }
}

---

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

/**
 * See http://thinktibits.blogspot.co.uk/2012/12/Java-POI-XLS-XLSX-Change-Cell-Font-Color-Example.html
 * Currently only for xlsx/*from   w  w  w  .  j av a2 s.  c  om*/
 * @param wb
 * @param sheet
 */
private static void styleHeader(Workbook wb, Sheet sheet) {
    if (XSSFWorkbook.class.isInstance(wb) && XSSFSheet.class.isInstance(sheet)) {
        XSSFWorkbook my_workbook = (XSSFWorkbook) wb;
        XSSFCellStyle my_style = my_workbook.createCellStyle();
        XSSFFont my_font = my_workbook.createFont();
        my_font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        my_style.setFont(my_font);

        Row row = sheet.getRow(0);
        if (row != null && row.getFirstCellNum() >= 0) {
            for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    cell.setCellStyle(my_style);
                }
            }
        }
    }
}

---

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

private static boolean isEmptyCell(Row row, int col) {
    String value = getFormulaSafeTextValue(row.getCell(col));
    boolean isEmpty = Strings.isEmpty(value);
    return isEmpty;
}

---

• «
• 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
• »