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.codecrate.shard.transfer.excel.AbstractExcelRowHandler.java

License:Apache License

/**
 *
 * @param row/*www  .  j a  v a  2  s. c o m*/
 * @param column zero indexed column number
 * @return
 */
protected String getStringFromRow(HSSFRow row, int column) {
    HSSFCell nameCell = row.getCell((short) column);
    if (null == nameCell) {
        LOG.info("No string value found for [row, column]: " + row.getRowNum() + ", " + column);
        return null;
    }
    return nameCell.getStringCellValue();
}

---

From source file:com.comcast.cats.config.ui.monitoring.reboot.UpTimeAndRebootStatusBean.java

License:Open Source License

public void postProcessXls(Object document) {
    logger.trace("postProcessXls start document " + document);
    if (document != null) {
        HSSFWorkbook workBook = (HSSFWorkbook) document;
        HSSFSheet sheet = workBook.getSheetAt(0);

        HSSFRow headerRow = sheet.getRow(0);

        for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
            sheet.setColumnWidth(i, 30 * 265); // width for 40 characters
        }//w  w w .j a v  a  2 s  . c  om

        sheet.shiftRows(0, sheet.getLastRowNum(), 5); // shift rows 0 to n
                                                      // by 1 to get space
                                                      // for header
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F3"));

        HSSFFont headerFont = workBook.createFont();
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        HSSFCellStyle headerCellStyle = workBook.createCellStyle();
        headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
        headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFCell headerCell = headerRow.createCell(0);
        headerCell.setCellStyle(headerCellStyle);
        headerCell.setCellValue("CATS Uptime and Reboot Status : " + (new Date()));

        HSSFCellStyle metaDataCellStyle = workBook.createCellStyle();
        metaDataCellStyle.setFont(headerFont);

        HSSFRow metaDataRow = sheet.getRow(3);
        if (metaDataRow == null) {
            metaDataRow = sheet.createRow(3);
        }
        HSSFCell metaDataKey = metaDataRow.createCell(0);
        metaDataKey.setCellStyle(metaDataCellStyle);
        metaDataKey.setCellValue("CATS Instance");

        HSSFCell metaDataValue = metaDataRow.createCell(1);
        metaDataValue.setCellStyle(metaDataCellStyle);
        metaDataValue.setCellValue(AuthController.getHostAddress());

        HSSFCellStyle datatTableHeaderCellStyle = workBook.createCellStyle();
        datatTableHeaderCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        datatTableHeaderCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        datatTableHeaderCellStyle.setFont(headerFont);

        HSSFRow actualDataTableHeaderRow = sheet.getRow(5);
        for (int i = 0; i < actualDataTableHeaderRow.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = actualDataTableHeaderRow.getCell(i);
            if (cell != null) {
                String cellValue = cell.getStringCellValue();
                cellValue = cellValue.replace("<br/> ", ""); // replace
                                                             // any line
                                                             // breaks
                cell.setCellValue(cellValue);
                cell.setCellStyle(datatTableHeaderCellStyle);
            }
        }

    }
    logger.trace("postProcessXls end");
}

---

From source file:com.concursive.connect.web.modules.plans.utils.AssignmentExcelImporter.java

License:Open Source License

/**
 * Description of the Method/*ww  w . j  a  v a  2 s .  c  o  m*/
 *
 * @param requirement Description of the Parameter
 * @param db          Description of the Parameter
 * @param buffer      Description of the Parameter
 * @return Description of the Return Value
 * @throws java.sql.SQLException Description of the Exception
 */
public static boolean parse(byte[] buffer, Requirement requirement, Connection db) throws SQLException {
    if (System.getProperty("DEBUG") != null) {
        System.out.println("AssignmentExcelImporter-> parseExcel");
    }
    try {
        db.setAutoCommit(false);
        // stream the Excel Spreadsheet from the uploaded byte array
        POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(buffer));
        HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
        // get the first sheet
        HSSFSheet sheet = hssfworkbook.getSheetAt(0);
        // define objects for housing spreadsheet data
        HSSFRow currentRow = sheet.getRow(0);
        // parse each row, create and insert into a new requirement with a tree
        int rows = sheet.getPhysicalNumberOfRows();
        if (System.getProperty("DEBUG") != null) {
            System.out.println("AssignmentExcelImporter-> Number of rows: " + rows);
        }
        // Columns
        int columnHeader = -1;
        int columnMax = -1;
        boolean columnItemComplete = false;
        short itemColumn = -1;
        short priorityColumn = -1;
        short assignedToColumn = -1;
        short effortColumn = -1;
        short startColumn = -1;
        short endColumn = -1;

        // parse
        for (int r = 0; r < rows; r++) {
            currentRow = sheet.getRow(r);

            if (currentRow != null) {
                // Search for header
                if (columnHeader == -1) {
                    int cells = currentRow.getPhysicalNumberOfCells();
                    for (short c = 0; c < cells; c++) {
                        HSSFCell cell = currentRow.getCell(c);
                        if (cell != null) {
                            if ("Item".equals(getValue(cell))) {
                                columnHeader = r;
                                itemColumn = c;
                                columnMax = c;
                            } else if (itemColumn > -1 && !columnItemComplete && c > itemColumn) {
                                if ("".equals(getValue(cell))) {
                                    columnMax = c;
                                } else if (!"".equals(getValue(cell))) {
                                    columnItemComplete = true;
                                }
                            }
                            if ("Priority".equals(getValue(cell))) {
                                columnHeader = r;
                                priorityColumn = c;
                            } else if ("Assigned To".equals(getValue(cell))) {
                                columnHeader = r;
                                assignedToColumn = c;
                            } else if ("Lead".equals(getValue(cell))) {
                                columnHeader = r;
                                assignedToColumn = c;
                            } else if ("Effort".equals(getValue(cell))) {
                                columnHeader = r;
                                effortColumn = c;
                            } else if ("Start".equals(getValue(cell))) {
                                columnHeader = r;
                                startColumn = c;
                            } else if ("End".equals(getValue(cell))) {
                                columnHeader = r;
                                endColumn = c;
                            }
                        }
                    }
                }
                // Process each column
                if (columnHeader > -1 && r > columnHeader) {
                    boolean gotOne = false;
                    Assignment assignment = new Assignment();
                    assignment.setProjectId(requirement.getProjectId());
                    assignment.setRequirementId(requirement.getId());
                    // Activities and folders
                    if (itemColumn > -1) {
                        // Get the first indent level that has data
                        for (short c = itemColumn; c <= columnMax; c++) {
                            HSSFCell cell = currentRow.getCell(c);
                            if (cell != null && !"".equals(getValue(cell))) {
                                assignment.setRole(getValue(cell));
                                assignment.setIndent(c);
                                gotOne = true;
                                break;
                            }
                        }
                    }
                    if (gotOne) {
                        // Priority
                        if (priorityColumn > -1) {
                            HSSFCell cell = currentRow.getCell(priorityColumn);
                            if (cell != null) {
                                assignment.setPriorityId(getValue(cell));
                            }
                        }
                        // Effort
                        if (effortColumn > -1) {
                            HSSFCell cell = currentRow.getCell(effortColumn);
                            if (cell != null) {
                                assignment.setEstimatedLoe(getValue(cell));
                                if (assignment.getEstimatedLoeTypeId() == -1) {
                                    assignment.setEstimatedLoeTypeId(2);
                                }
                            }
                        }
                        // Assigned To
                        if (assignedToColumn > -1) {
                            HSSFCell cell = currentRow.getCell(assignedToColumn);
                            if (cell != null) {
                                assignment.addUsers(getValue(cell));
                            }
                        }
                        // Start Date
                        if (startColumn > -1) {
                            HSSFCell cell = currentRow.getCell(startColumn);
                            if (cell != null) {
                                assignment.setEstStartDate(getDateValue(cell));
                            }
                        }
                        // Due Date
                        if (endColumn > -1) {
                            HSSFCell cell = currentRow.getCell(endColumn);
                            if (cell != null) {
                                assignment.setDueDate(getDateValue(cell));
                            }
                        }
                        assignment.setEnteredBy(requirement.getEnteredBy());
                        assignment.setModifiedBy(requirement.getModifiedBy());
                        assignment.setStatusId(1);
                        // Make sure a valid priority is set
                        if (assignment.getPriorityId() < 1 || assignment.getPriorityId() > 3) {
                            assignment.setPriorityId(2);
                        }
                        // Make sure user is on team, before adding, else unset the field
                        if (!assignment.hasValidTeam(db)) {
                            assignment.getAssignedUserList().clear();
                        }
                        // Insert the assignment
                        assignment.insert(db);
                        if (System.getProperty("DEBUG") != null) {
                            System.out.println(
                                    "AssignmentExcelImporter-> Assignment Inserted: " + assignment.getId());
                        }
                    }
                }
            }
        }
        db.commit();
    } catch (Exception e) {
        db.rollback();
        e.printStackTrace(System.out);
        return false;
    } finally {
        db.setAutoCommit(true);
    }
    return true;
}

---

From source file:com.conecta.sat.utils.BuildXls.java

@Override
protected void buildExcelDocument(Map<String, Object> map, org.apache.poi.hssf.usermodel.HSSFWorkbook workbook,
        HttpServletRequest hsr, HttpServletResponse hsr1) throws Exception {
    hsr1.setContentType("application/vnd.ms-excel");
    DateFormat name = new SimpleDateFormat("ddMMyyyyhhmmss");
    hsr1.setHeader("Content-disposition", "attachment; filename=Reporte" + name.format(new Date()) + ".xls");
    HSSFSheet sheet = workbook.createSheet("Inventario");
    sheet.setDefaultColumnWidth(30);//  ww  w  .  ja va  2s.co  m
    CellStyle style = workbook.createCellStyle();
    List<PdfDTO> list = (List<PdfDTO>) map.get("list");

    HSSFRow header = sheet.createRow(0);

    for (int i = 0; i < sColumnas.length; i++) {
        header.createCell(i).setCellValue(sColumnas[i]);
        header.getCell(i).setCellStyle(style);
    }

    int rowCount = 1;
    HSSFCellStyle my_style_0 = workbook.createCellStyle();
    for (PdfDTO pdfDto : list) {
        HSSFRow aRow = sheet.createRow(rowCount++);
        aRow.createCell(0).setCellValue(pdfDto.getSerial());
        aRow.createCell(1).setCellValue(pdfDto.getEntrega());
        aRow.createCell(2).setCellValue(pdfDto.getEntrega());
        aRow.createCell(3).setCellValue(pdfDto.getActivacion());
        aRow.createCell(4).setCellValue(pdfDto.getCentro());
        aRow.createCell(5).setCellValue(pdfDto.getTipo());
        aRow.createCell(6).setCellValue(pdfDto.getCliente());
        aRow.createCell(7).setCellValue(pdfDto.getFolioPivotal());
        //            aRow.createCell(8).setCellValue( pdfDto.getIdUnico() );
        aRow.createCell(8).setCellValue(pdfDto.getLastUpdate());
    }
}

---

From source file:com.creativity.controller.PesquisaFichasBean.java

public void posProcessarXls(Object documento) {
    HSSFWorkbook planilha = (HSSFWorkbook) documento;
    HSSFSheet folha = planilha.getSheetAt(0);
    HSSFRow cabecalho = folha.getRow(0);
    HSSFCellStyle estiloCelula = planilha.createCellStyle();
    HSSFFont fonteCabecalho = planilha.createFont();

    fonteCabecalho.setColor(IndexedColors.BLACK.getIndex());
    fonteCabecalho.setBold(true);/*from   w ww . j av  a  2s. c o m*/
    fonteCabecalho.setFontHeightInPoints((short) 10);

    estiloCelula.setFont(fonteCabecalho);
    estiloCelula.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    estiloCelula.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < cabecalho.getPhysicalNumberOfCells(); i++) {
        cabecalho.getCell(i).setCellStyle(estiloCelula);
    }
}

---

From source file:com.demo.bean.bandejaBean.java

public void postProcessXLS(Object document) {
    System.out.println("ENRO A FORMAR ARCHIVO :" + nombreArch);
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);/*from w w  w. jav  a  2s  .com*/
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);
    }

    System.out.println("termino de formar el archivo");
}

---

From source file:com.demo.bean.ExportarGestionesBean.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);/*  w  w  w  .  ja v a  2 s  .c  o m*/
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);
    }
}

---

From source file:com.demo.bean.ReportePagosBean.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);// w  w w  .j  a va2  s .c o  m
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);
    }

    LogSistema
            .guardarlog(this.getClass().getName() + " Method: postProcessXLS, se exporto un reporte de pagos ");
}

---

From source file:com.demo.bean.VisitaDatosBean.java

public void postProcessXLS(Object document) {

    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);//ww  w  .ja va 2 s  . c  om
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);

        cell.setCellStyle(cellStyle);
    }

    LogSistema.guardarlog(this.getClass().getName()
            + " Method: postProcessXLS, se exporto un reporte de visitas domiciliarias");
}

---

From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java

License:Apache License

private String getCellValue(HSSFSheet sheet, int row, int column) {
    if (row > sheet.getLastRowNum())
        return "";
    HSSFRow theRow = sheet.getRow(row);
    if (theRow == null)
        return "";
    HSSFCell cell = theRow.getCell(column);
    if (cell == null)
        return "";
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return "";
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? "true" : "false";
    case HSSFCell.CELL_TYPE_NUMERIC: {
        Double v = cell.getNumericCellValue();
        if (v.doubleValue() == (v.longValue())) {
            return Long.toString(v.longValue());
        }//from   w w w.  ja  va  2 s.  com
        return Double.toString(v);
    }
    case HSSFCell.CELL_TYPE_STRING:
        String v = cell.getRichStringCellValue().getString().trim();
        return v;

    default:
        return "";
    }
}

---

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