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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. 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.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

protected Cell set(Sheet sheet, int row, int col, Object text, Style style, int rowspan, int colspan) {
    Row r = sheet.getRow(row);
    if (r == null)
        r = sheet.createRow(row);/*from  w  ww .j  a  va 2s. co  m*/
    Cell c = r.getCell(col);
    if (c == null)
        c = r.createCell(col);
    c.setCellStyle(styles.get(style));
    if (text == null) {
        if (c.getCellStyle().getDataFormatString().startsWith("0")) {
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue("");
        } else {
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue("");
        }
    } else if (text instanceof String) {
        try {
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(Integer.parseInt((String) text));
        } catch (Exception e) {
            try {
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(Double.parseDouble((String) text));
            } catch (Exception e2) {
                c.setCellType(Cell.CELL_TYPE_STRING);
                c.setCellValue((String) text);
            }
        }
    } else if (text instanceof Double) {
        c.setCellValue((Double) text);
        c.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (text instanceof Integer) {
        c.setCellValue((Integer) text);
        c.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (text instanceof Date) {
        c.setCellValue((Date) text);
    }
    if (rowspan > 1 || colspan > 1) {
        sheet.addMergedRegion(new CellRangeAddress(row, row + rowspan - 1, col, col + colspan - 1));
        for (int i = 0; i < rowspan; i++) {
            for (int j = 0; j < colspan; j++) {
                if (i > 0 || j > 0)
                    set(sheet, row + i, col + j, "", style);
            }
        }
    }
    return c;
}

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

License:Open Source License

protected Cell get(Sheet sheet, int row, int col) {
    Row r = sheet.getRow(row);
    if (r == null)
        r = sheet.createRow(row);//from   w  w w. j a va  2s  .  c  o  m
    Cell c = r.getCell(col);
    return c;
}

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

License:Open Source License

protected Cell setFormula(Sheet sheet, int row, int col, String text, Style style) {
    Row r = sheet.getRow(row);
    if (r == null)
        r = sheet.createRow(row);/*from   ww w .j a va  2  s . c o m*/
    Cell c = r.getCell(col);
    if (c == null)
        c = r.createCell(col);
    c.setCellStyle(styles.get(style));
    c.setCellType(Cell.CELL_TYPE_STRING);
    try {
        c.setCellFormula(text);
    } catch (Exception e) {
        e.printStackTrace();
        c.setCellValue("Err. " + e.getMessage());
    }
    return c;
}

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

License:Open Source License

protected void drawLineUnder(Sheet sheet, int row, int colMin, int colMax, short thickness) {
    Row r = sheet.getRow(row);
    if (r == null)
        r = sheet.createRow(row);/*ww w. j  a va  2s.c  o  m*/
    for (int col = colMin; col <= colMax; col++) {
        Cell c = r.getCell(col);
        if (c == null)
            c = r.createCell(col);
        CellStyle style = styleWithBordersUnder.get((c.getCellStyle().getIndex() << 4) + thickness);
        if (style == null) {
            style = sheet.getWorkbook().createCellStyle();
            style.cloneStyleFrom(c.getCellStyle());
            style.setBorderBottom(thickness);
            styleWithBordersUnder.put((c.getCellStyle().getIndex() << 4) + thickness, style);
        }
        c.setCellStyle(style);

    }
}

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

License:Open Source License

protected void drawLineAbove(Sheet sheet, int row, int colMin, int colMax, short thickness) {
    Row r = sheet.getRow(row);
    if (r == null)
        r = sheet.createRow(row);/*from ww w  . jav a  2 s . com*/
    for (int col = colMin; col <= colMax; col++) {
        Cell c = r.getCell(col);
        if (c == null)
            c = r.createCell(col);
        CellStyle style = styleWithBordersAbove.get(c.getCellStyle().getIndex() << 4 + thickness);
        if (style == null) {
            style = sheet.getWorkbook().createCellStyle();
            style.cloneStyleFrom(c.getCellStyle());
            style.setBorderTop(thickness);
            styleWithBordersAbove.put(c.getCellStyle().getIndex() << 4 + thickness, style);
        }
        c.setCellStyle(style);
    }
}

From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java

License:Open Source License

public static void autoSizeColumns(Sheet sheet, int maxColWidth, boolean resizeHeight) {
    ListHashMap<Integer, Integer> col2lens = new ListHashMap<>();
    for (int row = sheet.getFirstRowNum(); row <= sheet.getLastRowNum(); row++) {
        Row r = sheet.getRow(row);
        if (r == null || r.getFirstCellNum() < 0)
            continue;
        short maxH = 0;

        for (int col = r.getFirstCellNum(); col <= r.getLastCellNum(); col++) {
            Cell c = r.getCell(col);
            if (c == null
                    || (c.getCellType() != Cell.CELL_TYPE_STRING && c.getCellType() != Cell.CELL_TYPE_NUMERIC))
                continue;

            Font font = sheet.getWorkbook().getFontAt(c.getCellStyle().getFontIndex());
            String s = c.getCellType() == Cell.CELL_TYPE_STRING ? c.getStringCellValue()
                    : "" + c.getNumericCellValue();
            String[] lines = MiscUtils.split(s, "\n");
            int maxLen = 1;
            for (int i = 0; i < lines.length; i++) {
                maxLen = Math.max(lines[i].length(), maxLen);
            }//from   ww w . j  av  a  2 s  .co m
            if (font.getFontHeightInPoints() < 12) {
                col2lens.add(col, 700
                        + maxLen * (font.getFontHeightInPoints() + (font.getBoldweight() > 500 ? 1 : 0)) * 20);
            }
            maxH = (short) Math.max(maxH, 50 + lines.length * (font.getFontHeight() * 1.2));
        }
        if (resizeHeight)
            r.setHeight(maxH);
    }

    for (int col : col2lens.keySet()) {
        List<Integer> lens = col2lens.get(col);
        Collections.sort(lens);
        int len = lens.get(lens.size() - 1);
        if (lens.size() > 10 && lens.get(lens.size() - 1) > 2 * lens.get(lens.size() - 2)) {
            len = lens.get(lens.size() - 2);
        }
        sheet.setColumnWidth(col,
                Math.max(Math.min((int) (len * 1.25), maxColWidth > 0 ? maxColWidth : 300000), 1500));
    }
}

From source file:com.admin.poi.ExcelUtils.java

License:Apache License

/**
 * //from  www. j av  a2  s. co  m
 *
 * @param excelSheet sheet ?
 * @param sheet      sheet
 */
private static void writeHead(ExcelSheet excelSheet, Sheet sheet) {
    Row row = Optional.ofNullable(sheet.getRow(excelSheet.getHeadRowIndex()))
            .orElse(sheet.createRow(excelSheet.getHeadRowIndex()));

    for (Map.Entry<String[], ExcelFieldMapping.ExcelFieldMappingAttribute> entry : excelSheet.getFieldMapping()
            .export().entrySet()) {
        ExcelFieldMapping.ExcelFieldMappingAttribute attribute = entry.getValue();
        Integer colIndex = attribute.getIndex();
        Cell cell = row.getCell(colIndex);
        if (cell == null) {
            cell = row.createCell(colIndex);
        }

        cell.setCellValue(attribute.getHead());
    }

}

From source file:com.admin.poi.ExcelUtils.java

License:Apache License

/**
 * ?//  w w w.  j  a va2s  .  c o m
 *
 * @param excelSheet sheet ?
 * @param row        
 * @param rowData    ?
 */
private static void writeRow(ExcelSheet excelSheet, Row row, Object rowData)
        throws InvocationTargetException, IllegalAccessException {

    for (Map.Entry<String[], ExcelFieldMapping.ExcelFieldMappingAttribute> entry : excelSheet.getFieldMapping()
            .export().entrySet()) {
        String[] fieldName = entry.getKey();
        ExcelFieldMapping.ExcelFieldMappingAttribute attribute = entry.getValue();

        Integer colIndex = attribute.getIndex();
        Object val = null;
        if (rowData != null) {
            val = getFieldValue(rowData, fieldName);
        }
        // proc cell
        Cell cell = Optional.ofNullable(row.getCell(colIndex)).orElse(row.createCell(colIndex));

        ExcelCellProcessor processor = attribute.getCellProcessor();
        if (processor == null) {
            writeCell(cell, val);
            continue;
        }
        processor.process(val, cell);
    }
}

From source file:com.adobe.acs.commons.mcp.impl.TestGenericReportExcelServlet.java

License:Apache License

@Test
public void testReport() throws Exception {
    int numRows = 10;
    String reportPath = "/var/acs-commons/mcp/instances/junit/jcr:content/report";
    ResourceBuilder rb = slingContext.build()
            .resource(reportPath, "columns", new String[] { "ColumnA", "ColumnB" }, "name", "report",
                    "sling:resourceType",
                    "acs-commons/components/utilities/process-instance/process-generic-report")
            .resource("rows");
    rb.siblingsMode();//from  w w w .  j a v a 2  s .c o m
    for (int i = 1; i <= numRows; i++) {
        rb.resource("row-" + i, "ColumnA", "abcdef-" + i, "ColumnB", "qwerty-" + i);
    }
    MockSlingHttpServletRequest request = slingContext.request();
    request.setResource(slingContext.resourceResolver().getResource(reportPath));
    MockSlingHttpServletResponse response = slingContext.response();

    slingContext.addModelsForClasses(GenericReport.class);

    GenericReportExcelServlet servlet = new GenericReportExcelServlet();

    servlet.doGet(request, response);

    assertEquals("application/vnd.ms-excel", response.getContentType());

    Workbook wb = WorkbookFactory.create(new ByteArrayInputStream(response.getOutput()));
    Sheet sh = wb.getSheetAt(0);
    assertEquals(numRows, sh.getLastRowNum());
    Row header = sh.getRow(0);
    assertEquals("Column A", header.getCell(0).getStringCellValue());
    assertEquals("Column B", header.getCell(1).getStringCellValue());
    for (int i = 1; i <= numRows; i++) {
        Row row = sh.getRow(i);
        assertEquals("abcdef-" + i, row.getCell(0).getStringCellValue());
        assertEquals("qwerty-" + i, row.getCell(1).getStringCellValue());
    }

}

From source file:com.AllenBarr.CallSheetGenerator.CallSheetGeneratorControllerController.java

License:Open Source License

private void populateContributorList() {
    if (excelSheet.exists()) {
        try {/* w  w  w .j a va 2s.co m*/
            wb = WorkbookFactory.create(excelSheet);
            wbSheet = wb.getSheetAt(0);
            Row wbRow = wbSheet.getRow(0);
            Integer vanIDColumnIndex = 0;
            Integer fNameColumnIndex = 0;
            Integer lNameColumnIndex = 0;
            for (Cell cell : wbRow) {
                if (null != cell.getStringCellValue()) {
                    switch (cell.getStringCellValue()) {
                    case "VANID":
                        vanIDColumnIndex = cell.getColumnIndex();
                        break;
                    case "LastName":
                        lNameColumnIndex = cell.getColumnIndex();
                        break;
                    case "FirstName":
                        fNameColumnIndex = cell.getColumnIndex();
                        break;
                    }
                }
            }
            final ObservableList<String> names = FXCollections.observableArrayList();
            for (Row row : wbSheet) {
                switch (row.getCell(vanIDColumnIndex).getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    names.add(row.getCell(vanIDColumnIndex).getStringCellValue() + " "
                            + row.getCell(fNameColumnIndex).getStringCellValue() + " "
                            + row.getCell(lNameColumnIndex).getStringCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    names.add((int) row.getCell(vanIDColumnIndex).getNumericCellValue() + " "
                            + row.getCell(fNameColumnIndex).getStringCellValue() + " "
                            + row.getCell(lNameColumnIndex).getStringCellValue());
                    break;
                }
            }
            contributorSelector.setItems(names);
            contributorSelector.getSelectionModel().select(0);
        } catch (IOException | InvalidFormatException ex) {
            Logger.getLogger(CallSheetGeneratorControllerController.class.getName()).log(Level.SEVERE, null,
                    ex);
        }
    }
}