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

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

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

Introduction

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

Prototype

Cell createCell(int column);

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void exportCells(DataTable table, Sheet sheet, int rowIndex) {
    int sheetRowIndex = sheet.getLastRowNum() + 1;
    Row row = sheet.createRow(sheetRowIndex);

    for (UIColumn col : table.getColumns()) {
        if (!col.isRendered()) {
            continue;
        }/* ww  w  . j  a va 2s . c o m*/

        if (col instanceof DynamicColumn) {
            ((DynamicColumn) col).applyModel();
        }

        if (col.isExportable()) {
            //Adding RowIndex for custom Export
            UIComponent component = (UIComponent) col;
            if (component.getId().equalsIgnoreCase("subject")) {

                Cell cell = row.createCell(0);
                String value = rowIndex + "";
                cell.setCellValue(new XSSFRichTextString(value));
            }
            addColumnValue(row, col.getChildren(), "content");
        }
    }
    FacesContext context = null;
    if (table.getRowIndex() == 0) {
        for (UIComponent component : table.getChildren()) {
            if (component instanceof RowExpansion) {
                RowExpansion rowExpansion = (RowExpansion) component;
                if (rowExpansion.getChildren() != null) {
                    if (rowExpansion.getChildren().get(0) instanceof DataTable) {
                        DataTable childTable = (DataTable) rowExpansion.getChildren().get(0);
                        childTable.setRowIndex(-1);
                    }
                    if (rowExpansion.getChildren().get(0) instanceof DataList) {
                        DataList childList = (DataList) rowExpansion.getChildren().get(0);
                        childList.setRowIndex(-1);
                    }
                }

            }
        }
    }
    table.setRowIndex(table.getRowIndex() + 1);
    for (UIComponent component : table.getChildren()) {
        if (component instanceof RowExpansion) {
            RowExpansion rowExpansion = (RowExpansion) component;
            if (rowExpansion.getChildren() != null) {
                if (rowExpansion.getChildren().get(0) instanceof DataList) {
                    DataList list = (DataList) rowExpansion.getChildren().get(0);
                    if (list.getHeader() != null) {
                        tableFacet(context, sheet, list, "header");
                    }
                    exportAll(context, list, sheet);
                }
                if (rowExpansion.getChildren().get(0) instanceof DataTable) {
                    DataTable childTable = (DataTable) rowExpansion.getChildren().get(0);
                    int columnsCount = getColumnsCount(childTable);

                    if (childTable.getHeader() != null) {
                        tableFacet(context, sheet, childTable, columnsCount, "header");

                    }
                    tableColumnGroup(sheet, childTable, "header");

                    addColumnFacets(childTable, sheet, ColumnType.HEADER);

                    exportAll(context, childTable, sheet, false);

                    if (childTable.hasFooterColumn()) {
                        addColumnFacets(childTable, sheet, ColumnType.FOOTER);
                    }
                    tableColumnGroup(sheet, childTable, "footer");
                    childTable.setRowIndex(-1);
                }

            }
        }
    }
}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void exportCells(DataList list, Sheet sheet) {
    int sheetRowIndex = sheet.getLastRowNum() + 1;
    Row row = sheet.createRow(sheetRowIndex);

    for (UIComponent component : list.getChildren()) {
        if (component instanceof Column) {
            UIColumn column = (UIColumn) component;
            for (UIComponent childComponent : column.getChildren()) {
                int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
                Cell cell = row.createCell(cellIndex);
                if (component.isRendered()) {
                    String value = component == null ? ""
                            : exportValue(FacesContext.getCurrentInstance(), childComponent);
                    cell.setCellValue(new XSSFRichTextString(value));
                    cell.setCellStyle(cellStyle);
                }/*from ww  w .j  a  va2  s.  c  o  m*/
            }

        } else {
            int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
            Cell cell = row.createCell(cellIndex);
            if (component.isRendered()) {
                String value = component == null ? ""
                        : exportValue(FacesContext.getCurrentInstance(), component);
                cell.setCellValue(new XSSFRichTextString(value));
                cell.setCellStyle(cellStyle);
            }
        }
    }

}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void addColumnFacets(DataTable table, Sheet sheet, ColumnType columnType) {

    int sheetRowIndex = sheet.getLastRowNum() + 1;
    Row rowHeader = sheet.createRow(sheetRowIndex);

    for (UIColumn col : table.getColumns()) {
        if (!col.isRendered()) {
            continue;
        }//from  ww w.j  a va2 s .c o m

        if (col instanceof DynamicColumn) {
            ((DynamicColumn) col).applyModel();
        }

        if (col.isExportable()) {
            //Adding RowIndex for custom Export
            UIComponent component = (UIComponent) col;
            if (component.getId().equalsIgnoreCase("subject")) {

                Cell cell = rowHeader.createCell(0);
                String value = "Index";
                cell.setCellValue(new XSSFRichTextString(value));
            }
            //Adding RowIndex for custom Export
            addColumnValue(rowHeader, col.getFacet(columnType.facet()), "facet");
        }
    }

}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void addColumnValue(Row row, UIComponent component, String type) {
    int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
    Cell cell = row.createCell(cellIndex);
    String value = component == null ? "" : exportValue(FacesContext.getCurrentInstance(), component);
    cell.setCellValue(new XSSFRichTextString(value));
    if (type.equalsIgnoreCase("facet")) {
        // addColumnAlignments(component,facetStyle);
        cell.setCellStyle(facetStyle);/*from   w  ww.j a  va  2s . c om*/
    } else {
        CellStyle cellStyle = this.cellStyle;
        cellStyle = addColumnAlignments(component, cellStyle);
        cell.setCellStyle(cellStyle);
    }

}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void addColumnValue(Row row, List<UIComponent> components, String type) {
    int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
    Cell cell = row.createCell(cellIndex);
    StringBuilder builder = new StringBuilder();
    FacesContext context = FacesContext.getCurrentInstance();

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

            if (value != null) {
                builder.append(value);/*from   www  .  java  2s .  c  o m*/
            }
        }
    }

    cell.setCellValue(new XSSFRichTextString(builder.toString()));

    if (type.equalsIgnoreCase("facet")) {
        //addColumnAlignments(components,facetStyle);
        cell.setCellStyle(facetStyle);
    } else {
        CellStyle cellStyle = this.cellStyle;
        for (UIComponent component : components) {
            cellStyle = addColumnAlignments(component, cellStyle);
            cell.setCellStyle(cellStyle);
        }
    }

}

From source file:com.crunchify.jsp.servlet.HSSFCreate.java

/**
 * Processes requests for both HTTP GET and POST methods.
 *
 * @param request servlet request//from  w w w .j a v a  2s.  com
 * @param response servlet response
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    DepartamentoDAO d = new DepartamentoDAO();

    response.setContentType("application/vnd.ms-excel");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");

    Map<String, Object[]> data = new HashMap<String, Object[]>();

    data.put("1", new Object[] { "Emp No.", "Name" });
    for (int i = 0; i < d.findAll().size(); i++) {
        data.put("2", new Object[] { d.findAll().get(i).getNom_departamento(),
                d.findAll().get(i).getNom_departamento() });
    }

    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset) {
        Row row = sheet.createRow(rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof Date) {
                cell.setCellValue((Date) obj);
            } else if (obj instanceof Boolean) {
                cell.setCellValue((Boolean) obj);
            } else if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Double) {
                cell.setCellValue((Double) obj);
            }
        }
    }

    // Write the output 
    OutputStream out = response.getOutputStream();
    wb.write(out);
    out.close();
}

From source file:com.daphne.es.showcase.excel.service.ExcelDataService.java

License:Apache License

/**
 * workbook//ww w .  j  av  a  2 s.  c  o m
 * 1?vbs ?
 * 2?c#??
 * ? ????office 2007 ?
 * @param user
 * @param contextRootPath
 * @param searchable
 */
@Async
public void exportExcel2003WithOneSheetPerWorkBook(final User user, final String contextRootPath,
        final Searchable searchable) {
    int workbookCount = 0;
    List<String> workbookFileNames = new ArrayList<String>();
    int perSheetRows = 60000; //?sheet 6w?
    int totalRows = 0;
    String extension = "xls";

    int pageSize = 1000;
    Long maxId = 0L;

    BufferedOutputStream out = null;
    try {
        long beginTime = System.currentTimeMillis();

        while (true) {
            workbookCount++;
            String fileName = generateFilename(user, contextRootPath, workbookCount, extension);
            workbookFileNames.add(fileName);
            File file = new File(fileName);

            HSSFWorkbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet();
            Row headerRow = sheet.createRow(0);
            Cell idHeaderCell = headerRow.createCell(0);
            idHeaderCell.setCellValue("?");
            Cell contentHeaderCell = headerRow.createCell(1);
            contentHeaderCell.setCellValue("");

            totalRows = 1;

            Page<ExcelData> page = null;

            do {
                searchable.setPage(0, pageSize);
                //
                if (!searchable.containsSearchKey("id_in")) {
                    searchable.addSearchFilter("id", SearchOperator.gt, maxId);
                }
                page = findAll(searchable);

                for (ExcelData data : page.getContent()) {
                    Row row = sheet.createRow(totalRows);
                    Cell idCell = row.createCell(0);
                    idCell.setCellValue(data.getId());
                    Cell contentCell = row.createCell(1);
                    contentCell.setCellValue(data.getContent());
                    maxId = Math.max(maxId, data.getId());
                    totalRows++;
                }
                //clear entity manager
                RepositoryHelper.clear();
            } while (page.hasNext() && totalRows <= perSheetRows);

            out = new BufferedOutputStream(new FileOutputStream(file));
            wb.write(out);

            IOUtils.closeQuietly(out);

            if (!page.hasNext()) {
                break;
            }
        }

        String fileName = workbookFileNames.get(0);
        if (workbookCount > 1 || needCompress(new File(fileName))) {
            fileName = fileName.substring(0, fileName.lastIndexOf("_")) + ".zip";
            //
            compressAndDeleteOriginal(fileName, workbookFileNames.toArray(new String[0]));
        } else {
            String newFileName = fileName.substring(0, fileName.lastIndexOf("_")) + "." + extension;
            FileUtils.moveFile(new File(fileName), new File(newFileName));
            fileName = newFileName;
        }

        long endTime = System.currentTimeMillis();

        Map<String, Object> context = Maps.newHashMap();
        context.put("seconds", (endTime - beginTime) / 1000);
        context.put("url", fileName.replace(contextRootPath, ""));
        notificationApi.notify(user.getId(), "excelExportSuccess", context);
    } catch (Exception e) {
        e.printStackTrace();
        //
        IOUtils.closeQuietly(out);
        log.error("excel export error", e);
        Map<String, Object> context = Maps.newHashMap();
        context.put("error", e.getMessage());
        notificationApi.notify(user.getId(), "excelExportError", context);
    }
}

From source file:com.daphne.es.showcase.excel.service.ExcelDataService.java

License:Apache License

/**
 * excel 2003/*from w  ww  . j  a va 2s.  co  m*/
 * ????
 * ?sheet65536(usermodel? ?flush ????)
 * @param user
 * @param contextRootPath
 * @param searchable
 */
@Async
public void exportExcel2003WithUsermodel(final User user, final String contextRootPath,
        final Searchable searchable) {
    int perSheetRows = 60000; //?sheet 6w?
    int totalRows = 0;
    Long maxId = 0L;

    String fileName = generateFilename(user, contextRootPath, "xls");
    File file = new File(fileName);
    BufferedOutputStream out = null;
    try {
        long beginTime = System.currentTimeMillis();

        HSSFWorkbook wb = new HSSFWorkbook();
        while (true) {
            Sheet sheet = wb.createSheet();
            Row headerRow = sheet.createRow(0);
            Cell idHeaderCell = headerRow.createCell(0);
            idHeaderCell.setCellValue("?");
            Cell contentHeaderCell = headerRow.createCell(1);
            contentHeaderCell.setCellValue("");

            totalRows = 1;
            Page<ExcelData> page = null;
            do {
                searchable.setPage(0, pageSize);
                //
                if (!searchable.containsSearchKey("id_in")) {
                    searchable.addSearchFilter("id", SearchOperator.gt, maxId);
                }
                page = findAll(searchable);

                for (ExcelData data : page.getContent()) {
                    Row row = sheet.createRow(totalRows);
                    Cell idCell = row.createCell(0);
                    idCell.setCellValue(data.getId());
                    Cell contentCell = row.createCell(1);
                    contentCell.setCellValue(data.getContent());
                    maxId = Math.max(maxId, data.getId());
                    totalRows++;
                }
                //clear entity manager
                RepositoryHelper.clear();
            } while (page.hasNext() && totalRows <= perSheetRows);

            if (!page.hasNext()) {
                break;
            }
        }

        out = new BufferedOutputStream(new FileOutputStream(file));
        wb.write(out);

        IOUtils.closeQuietly(out);

        if (needCompress(file)) {
            fileName = compressAndDeleteOriginal(fileName);
        }

        long endTime = System.currentTimeMillis();

        Map<String, Object> context = Maps.newHashMap();
        context.put("seconds", (endTime - beginTime) / 1000);
        context.put("url", fileName.replace(contextRootPath, ""));
        notificationApi.notify(user.getId(), "excelExportSuccess", context);
    } catch (Exception e) {
        IOUtils.closeQuietly(out);
        log.error("excel export error", e);
        Map<String, Object> context = Maps.newHashMap();
        context.put("error", e.getMessage());
        notificationApi.notify(user.getId(), "excelExportError", context);
    }
}

From source file:com.daphne.es.showcase.excel.service.ExcelDataService.java

License:Apache License

/**
 * ???//from   ww  w  .ja  v  a 2s.c  om
 * excel 2007 ?sheet1048576
 * @param user
 * @param contextRootPath
 * @param searchable
 */
@Async
public void exportExcel2007(final User user, final String contextRootPath, final Searchable searchable) {

    int rowAccessWindowSize = 1000; //???
    int perSheetRows = 100000; //?sheet 10w?
    int totalRows = 0; //
    Long maxId = 0L;//??id 

    String fileName = generateFilename(user, contextRootPath, "xlsx");
    File file = new File(fileName);
    BufferedOutputStream out = null;
    SXSSFWorkbook wb = null;
    try {
        long beginTime = System.currentTimeMillis();

        wb = new SXSSFWorkbook(rowAccessWindowSize);
        wb.setCompressTempFiles(true);//?gzip

        while (true) {

            Sheet sheet = wb.createSheet();
            Row headerRow = sheet.createRow(0);
            Cell idHeaderCell = headerRow.createCell(0);
            idHeaderCell.setCellValue("?");
            Cell contentHeaderCell = headerRow.createCell(1);
            contentHeaderCell.setCellValue("");

            totalRows = 1;

            Page<ExcelData> page = null;

            do {
                searchable.setPage(0, pageSize);
                //
                if (!searchable.containsSearchKey("id_in")) {
                    searchable.addSearchFilter("id", SearchOperator.gt, maxId);
                }
                page = findAll(searchable);

                for (ExcelData data : page.getContent()) {
                    Row row = sheet.createRow(totalRows);
                    Cell idCell = row.createCell(0);
                    idCell.setCellValue(data.getId());
                    Cell contentCell = row.createCell(1);
                    contentCell.setCellValue(data.getContent());
                    maxId = Math.max(maxId, data.getId());
                    totalRows++;
                }
                //clear entity manager
                RepositoryHelper.clear();
            } while (page.hasNext() && totalRows <= perSheetRows);

            if (!page.hasNext()) {
                break;
            }
        }
        out = new BufferedOutputStream(new FileOutputStream(file));
        wb.write(out);

        IOUtils.closeQuietly(out);

        if (needCompress(file)) {
            fileName = compressAndDeleteOriginal(fileName);
        }

        long endTime = System.currentTimeMillis();

        Map<String, Object> context = Maps.newHashMap();
        context.put("seconds", (endTime - beginTime) / 1000);
        context.put("url", fileName.replace(contextRootPath, ""));
        notificationApi.notify(user.getId(), "excelExportSuccess", context);
    } catch (Exception e) {
        IOUtils.closeQuietly(out);
        log.error("excel export error", e);
        Map<String, Object> context = Maps.newHashMap();
        context.put("error", e.getMessage());
        notificationApi.notify(user.getId(), "excelExportError", context);
    } finally {
        // ?
        wb.dispose();
    }
}

From source file:com.dataart.spreadsheetanalytics.engine.DataModelConverters.java

License:Apache License

/** Convertes plain {@link IDataModel} to new {@link XSSFWorkbook} with formatting provided. */
static Workbook toWorkbook(final IDataModel dataModel, final Workbook formatting) {
    Workbook result = formatting == null ? ConverterUtils.newWorkbook()
            : ConverterUtils.clearContent(formatting);

    Sheet wbSheet = result.getSheet(dataModel.getName());
    if (wbSheet == null) {
        wbSheet = result.createSheet(dataModel.getName());
    }//from ww  w  .  jav  a2  s  .  c  o  m

    dataModel.getNamedAddresses().forEach((k, v) -> {
        Name name = result.createName();
        name.setNameName(k);

        name.setRefersToFormula(createPoiNameRef(v.address(), dataModel.getName()));
    });

    dataModel.getNamedValues().forEach((k, v) -> {
        Name name = result.createName();
        name.setNameName(k);

        String refString = v.get() == null ? "" : v.get().toString();
        if (refString.startsWith(FORMULA_PREFIX)) {
            refString = refString.substring(1);
        }

        name.setRefersToFormula(refString);
    });

    for (int rowIdx = dataModel.getFirstRowIndex(); rowIdx <= dataModel.getLastRowIndex(); rowIdx++) {
        IDmRow dmRow = dataModel.getRow(rowIdx);
        if (dmRow == null) {
            continue;
        }
        Row wbRow = wbSheet.getRow(rowIdx);
        if (wbRow == null) {
            wbRow = wbSheet.createRow(rowIdx);
        }

        for (int cellIdx = dmRow.getFirstColumnIndex(); cellIdx <= dmRow.getLastColumnIndex(); cellIdx++) {
            IDmCell dmCell = dmRow.getCell(cellIdx);
            if (dmCell == null) {
                continue;
            }

            Cell wbCell = wbRow.getCell(cellIdx);
            if (wbCell == null) {
                wbCell = wbRow.createCell(cellIdx);
            }

            ConverterUtils.populateCellValue(wbCell, dmCell.getContent());
        }
    }

    return result;
}