Example usage for org.apache.poi.ss.usermodel Cell setCellValue

Introduction

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

Prototype

void setCellValue(boolean value);

Source Link

Document

Set a boolean value for the cell

Usage

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

/**
 * Processes requests for both HTTP GET and POST methods.
 *
 * @param request servlet request/*from  www . j  a v a  2  s. co m*/
 * @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/*from  ww  w.ja v  a2s .  co 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/* w ww. j  a v  a  2  s .  c  om*/
 * ????
 * ?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

/**
 * ???/*  w  w  w  . ja  v  a 2  s .  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.ConverterUtils.java

License:Apache License

/** Inserts a value to a Cell based on a value type (class). */
static void populateCellValue(final Cell cell, final ICellValue value) {
    if (cell == null) {
        return;/*w w  w . ja  v  a  2s  .  c  o m*/
    }

    int cellType = resolveCellType(value);
    cell.setCellType(cellType);
    switch (cellType) {
    case CELL_TYPE_BLANK: {
        break;
    }
    case CELL_TYPE_BOOLEAN: {
        cell.setCellValue((Boolean) value.get());
        break;
    }
    case CELL_TYPE_NUMERIC: {
        cell.setCellValue((Double) value.get());
        break;
    }
    case CELL_TYPE_FORMULA: {
        try {
            cell.setCellFormula(((String) value.get()).substring(1));
            break;
        } catch (FormulaParseNameException e) {
            log.error("Formula parsing error while trying to set formula field in cell " + e.getMessage());
            cell.setCellFormula(ErrorEval.NAME_INVALID.getErrorString());
            break;
        } catch (FormulaParseNAException e) {
            log.error("Formula parsing error while trying to set formula field in cell " + e.getMessage());
            cell.setCellFormula(ErrorEval.NA.getErrorString());
            break;
        }
    }
    case CELL_TYPE_ERROR: {
        cell.setCellErrorValue(FormulaError.forString((String) value.get()).getCode());
        break;
    }
    case CELL_TYPE_STRING: {
        cell.setCellValue((String) value.get());
        break;
    }

    default: {
        throw new CalculationEngineException(String.format("Type of value %s is not supported: %s", value,
                value.getClass().getSimpleName()));
    }
    }
}

---

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

private Cell addTableCell(Row row, int col, String label) {
    Cell cell = row.createCell(col);
    cell.setCellValue(label);
    CellUtil.setAlignment(cell, row.getSheet().getWorkbook(), CellStyle.ALIGN_LEFT);

    return cell;//from   ww  w.j  a va  2s  .  c om
}

---

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

@SuppressWarnings("unused")
private Cell addTableCell(Row row, int col, Boolean value) {
    Cell cell = row.createCell(col);
    cell.setCellValue(String.valueOf(value));
    CellUtil.setAlignment(cell, row.getSheet().getWorkbook(), CellStyle.ALIGN_CENTER);

    return cell;//from   w w  w  . jav a 2s.co m
}

---

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

private Cell addTableCell(Row row, int col, int value) {
    Cell cell = row.createCell(col);
    cell.setCellValue(String.valueOf(value));
    cell.setCellStyle(intStyle);/*w  w w. j a  v  a2  s  . com*/
    CellUtil.setAlignment(cell, row.getSheet().getWorkbook(), CellStyle.ALIGN_RIGHT);

    return cell;
}

---

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

private Cell addTableCell(Row row, int col, double value, HSSFCellStyle style) {
    Cell cell = row.createCell(col);
    cell.setCellValue(value);
    cell.setCellStyle(style);/*w  w  w . j av  a2 s .  c o m*/
    CellUtil.setAlignment(cell, row.getSheet().getWorkbook(), CellStyle.ALIGN_RIGHT);

    return cell;
}

---

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

private Cell createTableHeader(HSSFSheet sheet, int col, String label) {

    int n = sheet.getPhysicalNumberOfRows();
    Row row = null;/* w  w w . j av a2s .  co  m*/

    if (n < 1)
        row = sheet.createRow(0);
    else
        row = sheet.getRow(0);

    Cell cell = row.createCell(col);
    cell.setCellValue(label);
    cell.setCellStyle(boldStyle);
    CellUtil.setAlignment(cell, sheet.getWorkbook(), CellStyle.ALIGN_CENTER);

    return cell;

}

---

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