Example usage for org.apache.poi.ss.usermodel Sheet createRow

List of usage examples for org.apache.poi.ss.usermodel Sheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. createRow

Introduction

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

Prototype

Row createRow(int rownum);

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

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

License:Apache License

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

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

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

        if (col.isExportable()) {
            addColumnValue(row, col.getChildren(), "content");
        }
    }
}

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.  jav  a 2s .  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 w w  w .  ja v  a  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 = 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 addColumnFacets(SubTable 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 w w  w .j  a va  2 s  . com*/

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

        if (col.isExportable()) {
            addColumnValue(rowHeader, col.getFacet(columnType.facet()), "facet");
        }
    }
}

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

License:Apache License

/**
 * workbook//  w  w w  . java 2s . 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/*ww w . j  a v a2s .  com*/
 * ????
 * ?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   w ww .j a  va2s.com
 * 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   w w  w. ja v  a2s .c  om

    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;
}

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

License:Apache License

/**
 * Converts {@link IDataSet} to {@link Workbook}.
 * The result {@link Workbook} is created from @param formatting.
 *///from   w  w  w. j  a  v  a2 s.  c o m
static Workbook toWorkbook(final IDataSet dataSet, final Workbook formatting) {
    Workbook result = formatting == null ? ConverterUtils.newWorkbook()
            : ConverterUtils.clearContent(formatting);

    Sheet sheet = result.createSheet(dataSet.getName());
    for (IDsRow row : dataSet) {
        Row wbRow = sheet.createRow(row.index() - 1);
        for (IDsCell cell : row) {
            Cell wbCell = wbRow.createCell(cell.index() - 1);
            ConverterUtils.populateCellValue(wbCell, cell.getValue());
        }
    }

    return result;
}

From source file:com.dataart.spreadsheetanalytics.functions.poi.data.FuncexecFunction.java

License:Apache License

@Override
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {

    log.debug("In evaluate() of FUNCEXEC function. Args = {}", Arrays.toString(args));

    if (!(args[0] instanceof StringEval) && !(args[0] instanceof RefEval)) {
        log.warn(//  w  ww.  j av a 2 s  .  c o  m
                "The first argument of FUNCEXEC function must be a string (or a reference to a cell) - name of DEFINE function.");
        return ErrorEval.VALUE_INVALID;
    }

    String defineFunctionName;
    try {
        defineFunctionName = (String) coerceValueTo(
                getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex()));
    } catch (EvaluationException e) {
        log.error(String.format("Cannot get the value of DEFINE functuion name: %s", args[0]), e);
        return ErrorEval.VALUE_INVALID;
    }
    defineFunctionName = defineFunctionName.toUpperCase(Locale.getDefault());

    MetaFunctionAccessor defines = (MetaFunctionAccessor) ec.getCustomEvaluationContext()
            .get(MetaFunctionAccessor.class);
    if (defines == null) {
        defines = this.external.getMetaFunctionAccessor();
    }

    if (defines.get(defineFunctionName) == null) {
        log.warn("No DEFINE function with name {} is found.", defineFunctionName);
        return ErrorEval.NAME_INVALID;
    }

    List<ValueEval> inputValues = new LinkedList<>();
    List<ValueEval> inArgs = new ArrayList(Arrays.asList(args));
    inArgs.remove(0); //remove define function name

    try {
        for (ValueEval v : ICustomFunction.prepareQueryArgs(inArgs)) {
            inputValues.add(getSingleValue(v, ec.getRowIndex(), ec.getColumnIndex()));
        }
    } catch (EvaluationException e) {
        log.error("Cannot resolve input values for FUNCEXEC function", e);
        return ErrorEval.VALUE_INVALID;
    }

    final DefineFunctionMeta meta = (DefineFunctionMeta) defines.get(defineFunctionName);
    log.info("Found DEFINE function to invoke. Name = {}.", defineFunctionName);

    if (meta.getInputs().size() != inputValues.size()) {
        log.warn("Wrong number of input arguments for FUNCEXEC+DEFINE. Expected: {}, Actual: {}.",
                meta.getInputs().size(), args.length - 1);
        return ErrorEval.VALUE_INVALID;
    }

    List<IA1Address> inputAddresses = meta.getInputs();
    log.debug("Input Addresses for DEFINE: {}, Input Values for DEFINE: {}.", inputAddresses, inputValues);

    if (inputAddresses.size() != inputValues.size()) {
        log.warn("Wrong number of input arguments for {} function.", defineFunctionName);
        return ErrorEval.VALUE_INVALID;
    }

    DataModelAccessor dataModels = (DataModelAccessor) ec.getCustomEvaluationContext()
            .get(DataModelAccessor.class);
    if (dataModels == null) {
        dataModels = this.external.getDataModelAccessor();
    }

    IDataModel dmWithDefine = dataModels.get(meta.getDataModelId());

    Workbook book = toWorkbook(dmWithDefine);
    EvaluationWorkbook defineBook = toEvaluationWorkbook(book);

    Sheet s = book.getSheetAt(0); //TODO one sheet support
    for (int i = 0; i < inputAddresses.size(); i++) {

        Row defineRow = s.getRow(inputAddresses.get(i).row());
        if (defineRow == null) {
            defineRow = s.createRow(inputAddresses.get(i).row());
        }
        Cell defineCell = defineRow.getCell(inputAddresses.get(i).column());
        if (defineCell == null) {
            defineCell = defineRow.createCell(inputAddresses.get(i).column());
        }

        populateCellValue(defineCell, inputValues.get(i));
        updateCell(defineBook, defineCell);
    }

    WorkbookEvaluator defineEvaluator = new WorkbookEvaluator(defineBook,
            IStabilityClassifier.TOTALLY_IMMUTABLE, null);
    List<ValueEval> outputValues = meta.getOutputs().stream().map(
            a -> defineEvaluator.evaluate(getEvaluationCell(defineBook, a), ec.getCustomEvaluationContext()))
            .collect(Collectors.<ValueEval>toList());

    log.debug("Output Values of DEFINE execution: {}.", outputValues);

    return outputValues.size() == 1 ? outputValues.get(0) : toArrayEval(outputValues);
}