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

List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getLastCellNum

Introduction

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

Prototype

short getLastCellNum();

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

private void parseTemplate(Sheet sheet, Map<String, Object> map, boolean colForeach) throws Exception {
    deleteCell(sheet, map);//from w w  w. java 2s  . c  o  m
    mergedRegionHelper = new MergedRegionHelper(sheet);
    templateSumHanlder = new TemplateSumHanlder(sheet);
    if (colForeach) {
        colForeach(sheet, map);
    }
    Row row = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            if (row.getCell(i) != null
                    && !tempCreateCellSet.contains(row.getRowNum() + "_" + row.getCell(i).getColumnIndex())) {
                setValueForCellByMap(row.getCell(i), map);
            }
        }
    }

    //??
    hanlderSumCell(sheet);
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ,??//from ww w  . j  a  va 2s. c o  m
 * @param sheet
 * @param map
 */
private void colForeach(Sheet sheet, Map<String, Object> map) throws Exception {
    Row row = null;
    Cell cell = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String text = cell.getStringCellValue();
                if (text.contains(FOREACH_COL) || text.contains(FOREACH_COL_VALUE)) {
                    foreachCol(cell, map, text);
                }
            }
        }
    }
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ,??/*from  w ww.j ava 2  s  .c o  m*/
 * @param sheet
 * @param map
 * @throws Exception 
 */
private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
    Row row = null;
    Cell cell = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String text = cell.getStringCellValue();
                if (text.contains(IF_DELETE)) {
                    if (Boolean.valueOf(
                            eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map)
                                    .toString())) {
                        PoiSheetUtility.deleteColumn(sheet, i);
                    }
                    cell.setCellValue("");
                }
            }
        }
    }
}

From source file:cn.bzvs.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * ??/* w w w  . ja va 2  s.co  m*/
 * @param object
 * @param row
 * @param params
 * @param pojoClass
 * @return
 */
private boolean verifyingDataValidity(Object object, Row row, ImportParams params, Class<?> pojoClass) {
    boolean isAdd = true;
    Cell cell = null;
    if (params.isNeedVerfiy()) {
        String errorMsg = PoiValidationUtil.validation(object);
        if (StringUtils.isNotEmpty(errorMsg)) {
            cell = row.createCell(row.getLastCellNum());
            cell.setCellValue(errorMsg);
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(errorMsg);
            } else {
                isAdd = false;
            }
            verfiyFail = true;
        }
    }
    if (params.getVerifyHanlder() != null) {
        ExcelVerifyHanlderResult result = params.getVerifyHanlder().verifyHandler(object);
        if (!result.isSuccess()) {
            if (cell == null)
                cell = row.createCell(row.getLastCellNum());
            cell.setCellValue(
                    (StringUtils.isNoneBlank(cell.getStringCellValue()) ? cell.getStringCellValue() + "," : "")
                            + result.getMsg());
            if (object instanceof IExcelModel) {
                IExcelModel model = (IExcelModel) object;
                model.setErrorMsg(
                        (StringUtils.isNoneBlank(model.getErrorMsg()) ? model.getErrorMsg() + "," : "")
                                + result.getMsg());
            } else {
                isAdd = false;
            }
            verfiyFail = true;
        }
    }
    if (cell != null)
        cell.setCellStyle(errorCellStyle);
    return isAdd;
}

From source file:cn.edu.zucc.chenxg.preview.ToHtml.java

License:Apache License

private void ensureColumnBounds(Sheet sheet) {
    if (gotBounds)
        return;// ww  w  . j a v a 2 s .  c  o  m

    Iterator<Row> iter = sheet.rowIterator();
    firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
    endColumn = 0;
    while (iter.hasNext()) {
        Row row = iter.next();
        short firstCell = row.getFirstCellNum();
        if (firstCell >= 0) {
            firstColumn = Math.min(firstColumn, firstCell);
            endColumn = Math.max(endColumn, row.getLastCellNum());
        }
    }
    gotBounds = true;
}

From source file:cn.edu.zucc.chenxg.preview.ToHtml.java

License:Apache License

private void printSheetContent(Sheet sheet) {
    printColumnHeads();//from   ww w.ja va 2  s  .  c  o  m

    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();

        out.format("  <tr>%n");
        out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "&nbsp;";
            String attrs = "";
            CellStyle style = null;
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    //Set the value that is rendered for the cell
                    //also applies the format
                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;
                    if (content.equals(""))
                        content = "&nbsp;";
                }
            }
            out.format("    <td class=%s %s>%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

From source file:cn.poi.api.example.ExcelExample.java

License:Open Source License

public static void ReadExcel(String excel, String brandcode)
        throws EncryptedDocumentException, InvalidFormatException, IOException {
    List<BrandConfigCommand> list = new ArrayList<>();
    InputStream inp = resourceLoader.getResource(excel).getInputStream();
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(4);//from   ww w.  j av a 2s.c  o m
    Row tempRow = null;
    for (int i = 1; i < sheet.getLastRowNum(); i++) {
        BrandConfigCommand brandConfigCommand = new BrandConfigCommand();
        String[] array = new String[5];
        Row row = sheet.getRow(i);
        if (StringUtils.isEmpty(row.getCell(4).toString())) {
            continue;
        }
        for (int j = 0; j < row.getLastCellNum(); j++) {
            if (j == 0 && StringUtils.isEmpty(row.getCell(j).toString())) {
                System.out.print(tempRow.getCell(j).getStringCellValue() + "    ");
                array[j] = tempRow.getCell(0).getStringCellValue();
                continue;
            } else if (j == 0) {
                tempRow = row;
            }
            if (j == 1 && StringUtils.isEmpty(row.getCell(j).toString())) {
                System.out.print(tempRow.getCell(j).getStringCellValue() + "    ");
                array[j] = tempRow.getCell(j).getStringCellValue();
                continue;
            }
            array[j] = row.getCell(j).getStringCellValue();
            System.out.print(row.getCell(j).toString() + "    ");

        }

        brandConfigCommand.setStoreCode(array[0]);
        brandConfigCommand.setStoreDate(array[1]);
        brandConfigCommand.setProvice(array[2]);
        brandConfigCommand.setCity(array[3]);
        brandConfigCommand.setArea(array[4]);
        brandConfigCommand.setArea_name(array[2] + array[3] + array[4]);
        list.add(brandConfigCommand);
        System.out.println();
    }

    List<String> listStr = new ArrayList<>();
    for (BrandConfigCommand brandConfigCommand : list) {
        String str = sqlStart + brandConfigCommand.getArea_name() + sqlEnd + provice
                + brandConfigCommand.getProvice() + city + brandConfigCommand.getCity() + area
                + brandConfigCommand.getArea() + code + brandConfigCommand.getStoreCode() + brand_code
                + brandcode + "'";
        listStr.add(str);
    }
    System.out.println(JSON.toJSONString(listStr));

}

From source file:com.a9ski.jsf.exporter.DataTableExcelExporter.java

License:Apache License

protected Cell addColumnValue(final Row row, final ExportValue value, final UIColumn tableCol)
        throws ExportException {
    final int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
    final Cell cell = row.createCell(cellIndex);

    addColumnValue(cell, value, tableCol);

    return cell;// ww  w  . j  a  va 2s  . c o  m
}

From source file:com.a9ski.jsf.exporter.DataTableExcelExporter.java

License:Apache License

protected void addColumnValue(final Row row, final List<UIComponent> components, final UIColumn tableCol,
        final DataTable table) throws ExportException {
    final int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
    final Cell cell = row.createCell(cellIndex);
    final StringBuilder builder = new StringBuilder();
    final FacesContext context = FacesContext.getCurrentInstance();

    for (final UIComponent component : components) {
        if (component.isRendered()) {
            final String value = exportValue(context, component).getStringValue();

            if (value != null) {
                builder.append(value);/*  w  ww  .  ja va  2 s.co m*/
            }
        }
    }

    final String textValue = builder.toString();
    addColumnValue(cell, new ExportValue(textValue, textValue, table), tableCol);
}

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

public void populateReport(Study study) throws Exception {
    assert study != null;

    this.study = study;

    initWorkbook();/*from  w  ww.  j  av  a  2 s  .  c  o  m*/

    //Create the workbook
    populateWorkBook();

    //Post processing
    //Add Table borders (between different styles of cells)
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);

        for (int r = 4; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null)
                continue;

            Row rowUp = sheet.getRow(r - 1);
            Row rowDown = sheet.getRow(r + 1);
            for (int c = 0; c <= row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                Cell cellLeft = c == 0 ? null : row.getCell(c - 1);
                boolean borderLeftAbove = cellLeft != null && cellLeft.getCellStyle().getBorderTop() == 1;
                boolean borderLeftUnder = cellLeft != null && cellLeft.getCellStyle().getBorderBottom() == 1;

                if ((cell != null
                        && cell.getCellStyle().getBorderLeft() + cell.getCellStyle().getBorderRight() > 0)
                        || (cell == null && c + 1 <= row.getLastCellNum() && row.getCell(c + 1) != null)) {
                    if (borderLeftAbove)
                        drawLineAbove(sheet, r, c, c, (short) 1);
                    if (borderLeftUnder)
                        drawLineUnder(sheet, r, c, c, (short) 1);
                }

                if (cell != null) {
                    Font font = wb.getFontAt(cell.getCellStyle().getFontIndex());
                    if (font.getFontHeightInPoints() >= 12)
                        continue;

                    Cell cellUp = rowUp != null && c < rowUp.getLastCellNum() ? rowUp.getCell(c) : null;
                    Cell cellDown = rowDown != null && c < rowDown.getLastCellNum() ? rowDown.getCell(c) : null;

                    if (cellUp == null /*|| (cell.getCellType()!=0 && cellUp.getCellType()!=0 && cellUp.getCellType()!=cell.getCellType())*/ ) {
                        //Border above
                        drawLineAbove(sheet, r, c, c, (short) 1);
                    }
                    if (cellDown == null /*|| (cell.getCellType()!=0 && cellDown.getCellType()!=0 && cellDown.getCellType()!=cell.getCellType())*/) {
                        //Border under
                        drawLineUnder(sheet, r, c, c, (short) 1);
                    }
                }
            }
        }
    }

}