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

List of usage examples for org.apache.poi.ss.usermodel Cell setCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. 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.blackducksoftware.tools.commonframework.standard.datatable.writer.DataSetWriterExcel.java

License:Apache License

private void populateHyperlinkCell(Record record, FieldDef fieldDef, Cell cell) throws Exception {
    String cellValue = record.getHyperlinkFieldValue(fieldDef.getName()).getDisplayText();
    cell.setCellValue(cellValue);

    // cell style for hyperlinks
    // by default hyperlinks are blue and underlined
    CellStyle hlink_style = workbook.createCellStyle();
    Font hlink_font = workbook.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);/*www  . j a va 2  s.  co m*/

    // Make it a hyperlink
    CreationHelper createHelper = workbook.getCreationHelper();
    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
    link.setAddress(record.getHyperlinkFieldValue(fieldDef.getName()).getHyperlinkText());
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
}

From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateWriter.java

License:Apache License

/**
 * Write pojo values to row for the provided sheet.
 * /*w w w .  jav  a 2 s  .  co m*/
 * @param activeSheet
 *            the provided sheet for pojo values write out to the rows
 * @param activeRow
 *            the active row
 * @param pojo
 *            the pojo
 * @param columnMap
 *            the column map
 * @param cloneStyle
 *            - if true, apply the styles to the new cells
 */
private void writePojoValuesToRow(Sheet activeSheet, Row activeRow, TemplatePojo pojo,
        Map<String, TemplateColumn> columnMap, boolean cloneStyle) {

    Iterator<String> it = columnMap.keySet().iterator();
    while (it.hasNext()) {
        String key = it.next();
        TemplateColumn column = columnMap.get(key);
        Integer position = column.getColumnPos();
        CellStyle styleFromTemplate = column.getCellStyle();

        Cell activeCell;
        int cellType = column.getCellType();
        if (cellType == Cell.CELL_TYPE_FORMULA) {
            activeCell = activeRow.createCell(position, Cell.CELL_TYPE_FORMULA);
            log.debug("Active Cell is PartOfArrayFormulaGroup: " + activeCell.isPartOfArrayFormulaGroup());
        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
            activeCell = activeRow.createCell(position, Cell.CELL_TYPE_NUMERIC);
        } else {
            activeCell = activeRow.createCell(position, Cell.CELL_TYPE_STRING);
        }

        // Set the value
        String pojoValue = getValueFromPojo(pojo, column.getLookupMappingName());
        activeCell.setCellValue(pojoValue);

        // Set the cell style
        // TODO: This catches the XML Disconnected exception, but the styles come out all wrong on subsequent
        // sheets.
        // Appears to only happen in the unit tests.
        if (cloneStyle) {
            try {
                CellStyle newcs = book.createCellStyle();
                newcs.cloneStyleFrom(styleFromTemplate);
                activeCell.setCellStyle(newcs);
            } catch (Exception e) {
                log.warn("Unable to copy cell styles!" + e.getMessage());
            }
        }

        if (cellType == Cell.CELL_TYPE_FORMULA) {
            copyFormula(activeSheet, activeCell, activeRow, column);
        }
    }
}

From source file:com.bonsoft.test.Report.java

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
    ArrayList<ReportLine> lines = new ArrayList<>();
    try {/*w  w  w. j  av  a2 s  .  c  o m*/
        Orgs org = (Orgs) jComboBox1.getSelectedItem();
        Stores store = (Stores) jComboBox2.getSelectedItem();
        Calendar period = Calendar.getInstance();
        try {
            period.setTime((new SimpleDateFormat("dd.MM.yyyy")).parse(jFormattedTextField1.getText()));
        } catch (ParseException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }

        Connection connection = null;
        Statement statement = null;
        ResultSet result = null;
        try {
            connection = DriverManager.getConnection("jdbc:postgresql://88.201.248.46:5432/personal", "vitaly",
                    "m127rqu4");
        } catch (SQLException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            statement = connection.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }
        String sql = "select operations.descr, date_part('hour', mhr_period) as hr, sum(mhr_qty) as cnt from mhr, operations "
                + "where mhr.operation_id = operations.id and mhR_qty > 0 and mhr.org_id = " + org.getId()
                + " and store_id = " + store.getId() + " and " + "date_part('day', mhr_period) = "
                + period.get(Calendar.DAY_OF_MONTH) + " and date_part('month', mhr_period) = "
                + (period.get(Calendar.MONTH) + 1) + " and date_part('year', mhr_period) = "
                + period.get(Calendar.YEAR)
                + " and mhr.operation_id in (select id from operations) group by operations.descr, hr having count(mhr_qty) > 0 order by "
                + "operations.descr, hr";
        try {
            result = statement.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("?: " + org.getName() + ", id = " + org.getId());
        System.out.println(": " + store.getDescr() + ", id = " + store.getId());
        System.out.println(": " + period.get(Calendar.DAY_OF_MONTH));
        System.out.println("??: " + (period.get(Calendar.MONTH) + 1));
        System.out.println(": " + period.get(Calendar.YEAR));
        String oldDescr = "";
        ReportLine line = null;
        while (result.next()) {
            String descr = result.getString("descr");
            double hr = result.getDouble("hr");
            double cnt = result.getDouble("cnt");
            cnt = Math.ceil(cnt);
            if (oldDescr.equals(descr)) {
                line.add(hr, cnt);
            } else {
                oldDescr = descr;
                line = new ReportLine();
                line.setName(descr);
                line.add(hr, cnt);
                lines.add(line);
            }
        }
        result.close();
        statement.close();
        connection.close();
    } catch (SQLException ex) {
        Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
    }
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("");
    Row title = sheet.createRow(0);
    Cell cell = title.createCell(0);
    cell.setCellValue("?");
    Row row = null;
    int x = 0, y = 0;
    for (ReportLine line : lines) {
        row = sheet.createRow(++y);
        cell = row.createCell(0);
        cell.setCellValue(line.getName());
        for (int i = 0; i < line.getLen(); i++) {
            x = line.getHours().get(i) - ReportLine.getMinH() + 1;
            cell = title.createCell(x);
            cell.setCellValue(line.getHours().get(i) + ":00");
            cell = row.createCell(x);
            cell.setCellValue(line.getCounts().get(i));
        }
    }
    sheet.autoSizeColumn(0);
    try (FileOutputStream fileExcel = new FileOutputStream("Report.xls")) {
        workbook.write(fileExcel);
    } catch (IOException ex) {
        Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.catdog.common.util.ExcelReadAndWrite.java

License:Apache License

public void testWorkBook() {
    try {//from w  ww  . j a  v a 2 s  . com
        long curr_time = System.currentTimeMillis();

        int rowaccess = 100;//
        /*keep 100 rowsin memory,exceeding rows will be flushed to disk*/
        SXSSFWorkbook wb = new SXSSFWorkbook(rowaccess);

        int sheet_num = 3;//?3SHEET
        for (int i = 0; i < sheet_num; i++) {
            Sheet sh = wb.createSheet();
            //?SHEET60000ROW
            for (int rownum = 0; rownum < 60000; rownum++) {
                Row row = sh.createRow(rownum);
                //?10CELL
                for (int cellnum = 0; cellnum < 10; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    String address = new CellReference(cell).formatAsString();
                    cell.setCellValue(address);
                }

                //??,?
                if (rownum % rowaccess == 0) {
                    ((SXSSFSheet) sh).flushRows();
                }
            }
        }

        /*?*/
        FileOutputStream os = new FileOutputStream("d:/biggrid.xlsx");
        wb.write(os);
        os.close();

        /**/
        System.out.println(":" + (System.currentTimeMillis() - curr_time) / 1000);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.catexpress.util.FormatosPOI.java

public void formatoSolicitud(Solicitud solicitud, Set<Proveedor> proveedores)
        throws FileNotFoundException, IOException {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet;//  w  w w . j a  va  2  s .  com
    int cont = 0;
    for (Proveedor proveedor : proveedores) {
        sheet = wb.createSheet(proveedor.getNombre());
        Row rTitulo = sheet.createRow(0);
        CellRangeAddress craTitulo = new CellRangeAddress(0, //first row (0-based)
                0, //last row  (0-based)
                0, //first column (0-based)
                6 //last column  (0-based)
        );
        sheet.addMergedRegion(craTitulo);
        Cell titulo = rTitulo.createCell(0);
        titulo.setCellValue("SOLICITUD DE MERCANC?A");
        titulo.setCellStyle(estiloHeader(wb, TITULO));
        rTitulo.setHeightInPoints(20);

        Row rUsuario = sheet.createRow(1);
        CellRangeAddress craUsuario = new CellRangeAddress(1, 1, 0, 6);
        sheet.addMergedRegion(craUsuario);
        Cell usuario = rUsuario.createCell(0);
        usuario.setCellValue((solicitud.getUsuario().getNombre() + " " + solicitud.getUsuario().getApPaterno()
                + " " + solicitud.getUsuario().getApMaterno()).toUpperCase());
        usuario.setCellStyle(estiloHeader(wb, USUARIO));
        rUsuario.setHeightInPoints(25);

        Row rSucursal = sheet.createRow(2);
        CellRangeAddress craSucursal = new CellRangeAddress(2, 2, 0, 6);
        sheet.addMergedRegion(craSucursal);
        Cell sucursal = rSucursal.createCell(0);
        sucursal.setCellValue("Sucursal: " + solicitud.getSucursal().getNombre());
        sucursal.setCellStyle(estiloHeader(wb, SUCURSAL));
        RegionUtil.setBorderTop(sucursal.getCellStyle().getBorderTop(), craSucursal, sheet, wb);
        RegionUtil.setBorderLeft(sucursal.getCellStyle().getBorderLeft(), craSucursal, sheet, wb);
        RegionUtil.setBorderRight(sucursal.getCellStyle().getBorderRight(), craSucursal, sheet, wb);
        RegionUtil.setBorderBottom(sucursal.getCellStyle().getBorderBottom(), craSucursal, sheet, wb);
        rSucursal.setHeightInPoints(20);

        Row rBlank = sheet.createRow(3);
        Cell blank;
        for (int i = 0; i <= 6; i++) {
            blank = rBlank.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rFecha = sheet.createRow(4);
        Cell labelFecha = rFecha.createCell(0);
        labelFecha.setCellValue("FECHA:");
        labelFecha.setCellStyle(estiloHeader(wb, LABEL));
        CellRangeAddress craFecha = new CellRangeAddress(4, 4, 1, 3);
        sheet.addMergedRegion(craFecha);
        Cell fecha = rFecha.createCell(1);
        fecha.setCellValue(solicitud.getFechaSolicitud());
        fecha.setCellStyle(estiloHeader(wb, FECHA));
        for (int i = 4; i <= 6; i++) {
            blank = rFecha.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rVigencia = sheet.createRow(5);
        Cell labelVigencia = rVigencia.createCell(0);
        labelVigencia.setCellValue("VIGENCIA:");
        labelVigencia.setCellStyle(estiloHeader(wb, LABEL));
        CellRangeAddress craVigencia = new CellRangeAddress(5, 5, 1, 3);
        sheet.addMergedRegion(craVigencia);
        Cell vigencia = rVigencia.createCell(1);
        Calendar clndr = Calendar.getInstance();
        clndr.setTime(solicitud.getFechaSolicitud());
        clndr.add(Calendar.DAY_OF_MONTH, 3);
        vigencia.setCellValue(clndr.getTime());
        vigencia.setCellStyle(estiloHeader(wb, FECHA));
        blank = rVigencia.createCell(4);
        blank.setCellStyle(estiloVacio(wb));
        Cell labelNoPedido = rVigencia.createCell(5);
        labelNoPedido.setCellValue("PEDIDO No:");
        labelNoPedido.setCellStyle(estiloCuadro(wb, AMARILLO));
        Cell noPedido = rVigencia.createCell(6);
        noPedido.setCellValue(solicitud.getId());
        noPedido.setCellStyle(estiloCuadro(wb, AMARILLO));

        Row rHoja = sheet.createRow(6);
        for (int i = 0; i <= 4; i++) {
            blank = rHoja.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }
        Cell labelHoja = rHoja.createCell(5);
        labelHoja.setCellValue("HOJA:");
        labelHoja.setCellStyle(estiloCuadro(wb, LABEL));
        Cell hoja = rHoja.createCell(6);
        hoja.setCellValue(++cont + "/" + proveedores.size());
        hoja.setCellStyle(estiloCuadro(wb, LABEL));

        Row rProveedor = sheet.createRow(7);
        CellRangeAddress craProveedor = new CellRangeAddress(7, 8, 0, 2);
        sheet.addMergedRegion(craProveedor);
        Cell prov = rProveedor.createCell(0);
        prov.setCellValue(proveedor.getNombre());
        prov.setCellStyle(estiloProveedor(wb));
        for (int i = 3; i <= 6; i++) {
            blank = rProveedor.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rProveedor2 = sheet.createRow(8);
        for (int i = 3; i <= 6; i++) {
            blank = rProveedor2.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }

        Row rTotales = sheet.createRow(9);
        for (int i = 0; i <= 1; i++) {
            blank = rTotales.createCell(i);
            blank.setCellStyle(estiloVacio(wb));
        }
        Cell labelTotales = rTotales.createCell(2);
        labelTotales.setCellValue("TOTALES: ");
        labelTotales.setCellStyle(estiloTotales(wb));
        blank = rTotales.createCell(3);
        blank.setCellStyle(estiloTotales(wb));
        Cell totalSolicitado = rTotales.createCell(4);
        totalSolicitado.setCellStyle(estiloTotales(wb));
        totalSolicitado.setCellType(CellType.FORMULA);
        totalSolicitado.setCellFormula("SUM(E12:E" + (11 + solicitud.getDetalles().size()) + ")");
        Cell totalSurtido = rTotales.createCell(5);
        totalSurtido.setCellStyle(estiloTotales(wb));
        totalSurtido.setCellType(CellType.FORMULA);
        totalSurtido.setCellFormula("SUM(F12:F" + (11 + solicitud.getDetalles().size()) + ")");
        Cell totalNegado = rTotales.createCell(6);
        totalNegado.setCellStyle(estiloTotales(wb));
        totalNegado.setCellType(CellType.FORMULA);
        totalNegado.setCellFormula("SUM(G12:G" + (11 + solicitud.getDetalles().size()) + ")");

        Row rColumnas = sheet.createRow(10);
        Cell labelCodigo = rColumnas.createCell(0);
        labelCodigo.setCellValue("CODIGO");
        labelCodigo.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelOpciones = rColumnas.createCell(1);
        labelOpciones.setCellValue("OPCIONES");
        labelOpciones.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelModelo = rColumnas.createCell(2);
        labelModelo.setCellValue("MODELO / MATERIAL / COLOR");
        labelModelo.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelTalla = rColumnas.createCell(3);
        labelTalla.setCellValue("TALLA");
        labelTalla.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelSolicitado = rColumnas.createCell(4);
        labelSolicitado.setCellValue("SOLICITADO");
        labelSolicitado.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelSurtido = rColumnas.createCell(5);
        labelSurtido.setCellValue("SURTIDO");
        labelSurtido.setCellStyle(estiloColumnas(wb, COLUMNA));
        Cell labelNegado = rColumnas.createCell(6);
        labelNegado.setCellValue("NEGADO");
        labelNegado.setCellStyle(estiloColumnas(wb, COLUMNA));

        Row rValues = sheet.createRow(11);
        Cell codigo;
        Cell opciones;
        Cell modelo;
        Cell talla;
        Cell solicitado;
        Cell surtido;
        Cell negado;
        for (Dsolicitud detalle : solicitud.getDetalles()) {
            if (detalle.getProducto().getProvedor().equals(proveedor)) {
                codigo = rValues.createCell(0);
                codigo.setCellValue(detalle.getProducto().getCBarras());
                codigo.setCellStyle(estiloColumnas(wb, 0));
                opciones = rValues.createCell(1);
                opciones.setCellValue(" - ");
                opciones.setCellStyle(estiloColumnas(wb, 0));
                modelo = rValues.createCell(2);
                modelo.setCellValue(detalle.getProducto().getModelo().getNombre() + " / "
                        + detalle.getProducto().getColor().getNombre());
                modelo.setCellStyle(estiloColumnas(wb, 0));
                talla = rValues.createCell(3);
                talla.setCellValue(detalle.getProducto().getTalla().getNombre());
                talla.setCellStyle(estiloColumnas(wb, 0));
                solicitado = rValues.createCell(4);
                solicitado.setCellValue(detalle.getCantidad());
                solicitado.setCellStyle(estiloColumnas(wb, 0));
                surtido = rValues.createCell(5);
                surtido.setCellStyle(estiloColumnas(wb, SURTIDO));
                negado = rValues.createCell(6);
                negado.setCellStyle(estiloColumnas(wb, 0));
            }
        }

        for (int i = 0; i <= 6; i++) {
            sheet.autoSizeColumn(i, true);
        }
    }
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("Solicitud" + solicitud.getId() + ".xls");
    wb.write(fileOut);
    fileOut.close();
}

From source file:com.centurylink.mdw.common.service.JsonExport.java

License:Apache License

public Workbook exportXlsx(String name) throws JSONException {

    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet(name == null ? jsonable.getClass().getSimpleName() : name);

    if (jsonable instanceof JsonArray) {
        JSONArray jsonArray = ((JsonArray) jsonable).getArray();
        for (int i = 0; i < jsonArray.length(); i++) {
            JSONObject jsonObj = jsonArray.getJSONObject(i);
            addNames(jsonObj);/*from   w  w w .  ja va2 s. com*/
            setRowValues(sheet, i + 1, jsonObj);
        }
        setColumnLabels(sheet);
    } else if (jsonable instanceof InstanceList) {
        InstanceList<?> instanceList = (InstanceList<?>) jsonable;
        List<? extends Jsonable> items = instanceList.getItems();
        for (int i = 0; i < items.size(); i++) {
            JSONObject jsonObj = items.get(i).getJson();
            addNames(jsonObj);
            setRowValues(sheet, i + 1, jsonObj);
        }
        setColumnLabels(sheet);
    } else if (jsonable instanceof JsonListMap) {
        JsonListMap<?> listMap = (JsonListMap<?>) jsonable;
        int row = 1;
        List<String> keys = new ArrayList<String>();
        keys.addAll(listMap.getJsonables().keySet());
        Collections.sort(keys);
        for (String key : keys) {
            List<? extends Jsonable> jsonableList = listMap.getJsonables().get(key);
            for (Jsonable jsonable : jsonableList) {
                addNames(jsonable.getJson());
            }
            if (!"".equals(names.get(0))) {
                names.add(0, ""); // key column
            }
            for (Jsonable jsonable : jsonableList) {
                Row valuesRow = setRowValues(sheet, row, jsonable.getJson(), 1);
                Cell cell = valuesRow.createCell(0);
                cell.setCellValue(key);
                row++;
            }
        }
        setColumnLabels(sheet);
    } else {
        throw new UnsupportedOperationException("Unsupported JSON type: " + jsonable);
    }

    return workbook;
}

From source file:com.centurylink.mdw.common.service.JsonExport.java

License:Apache License

private void setColumnLabels(Sheet sheet) {
    Row headerRow = sheet.createRow(0);/*ww w . j  ava  2 s . c  o m*/
    for (int i = 0; i < names.size(); i++) {
        Cell cell = headerRow.createCell(i);
        String label = names.get(i);
        if (labels != null && labels.containsKey(label))
            label = labels.get(label);
        cell.setCellValue(label);
        if (!"message".equals(label))
            sheet.autoSizeColumn(i);
    }
}

From source file:com.centurylink.mdw.common.service.JsonExport.java

License:Apache License

private Row setRowValues(Sheet sheet, int row, JSONObject json, int startCol) throws JSONException {
    Row valueRow = sheet.createRow(row);
    for (int i = startCol; i < names.size(); i++) {
        Cell cell = valueRow.createCell(i);
        String name = names.get(i);
        if (json.has(name)) {
            Object jsonValue = json.get(name);
            if (jsonValue instanceof Long || jsonValue instanceof Integer) {
                cell.setCellValue(new Double(jsonValue.toString()));
            } else if (jsonValue instanceof Boolean) {
                cell.setCellValue((Boolean) jsonValue);
            } else if (jsonValue instanceof Date) {
                cell.setCellValue((Date) jsonValue);
                cell.setCellStyle(getDateCellStyle(sheet));
            } else {
                String stringVal = jsonValue.toString();
                if (stringVal != null && (name.endsWith("Date") || "date".equals(name))) {
                    // try to parse as Query date
                    try {
                        cell.setCellValue(Query.getDate(stringVal));
                        cell.setCellStyle(getDateCellStyle(sheet));
                    } catch (ParseException ex) {
                        // try StringHelper date
                        Date d = StringHelper.stringToDate(stringVal);
                        if (d == null) {
                            cell.setCellValue(stringVal);
                        } else {
                            cell.setCellValue(d);
                            cell.setCellStyle(getDateCellStyle(sheet));
                        }//from  w  ww . ja v a2 s  .c o m
                    }
                } else {
                    cell.setCellValue(stringVal);
                }
            }
        }
    }
    return valueRow;
}

From source file:com.cisco.ca.cstg.pdi.utils.Util.java

public static void json2Excel(OutputStream output, String json, String[] properties, String[] columnsNames)
        throws IOException {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    Row header = sheet.createRow(0);//w w w  .ja  v a  2s . com

    for (int i = 0; i < columnsNames.length; i++) {
        String string = columnsNames[i];
        Cell cell = header.createCell(i);
        RichTextString text = new XSSFRichTextString(string);
        cell.setCellValue(text);
    }

    LOGGER.info("Writing on workbook.");
    try {
        ObjectMapper mapper = new ObjectMapper();
        JsonNode jsonNode = mapper.readTree(json);

        int i = 0;
        for (JsonNode jsonNode2 : jsonNode) {
            Row row = sheet.createRow(++i);

            for (int j = 0; j < properties.length; j++) {
                String string = properties[j];
                Cell cell = row.createCell(j);
                RichTextString text = new XSSFRichTextString(jsonNode2.get(string).getTextValue());
                cell.setCellValue(text);
            }
        }
    } catch (JsonProcessingException e) {
        LOGGER.error(e.getMessage(), e);
    }

    workbook.write(output);
    workbook.close();
    LOGGER.info("Written on workbook.");
}

From source file:com.clican.pluto.dataprocess.engine.processes.ExcelProcessor.java

License:LGPL

@SuppressWarnings("unchecked")
public void writeExcel(ProcessorContext context, ExcelExecBean execBean) throws Exception {
    Workbook book = null;//from  w  w w . j a v a 2 s. c  o  m
    InputStream is = null;
    try {
        is = execBean.getInputStream();
    } catch (FileNotFoundException e) {

    }
    if (is != null) {
        book = WorkbookFactory.create(is);
        is.close();
    } else {
        book = new HSSFWorkbook();
    }
    CreationHelper createHelper = book.getCreationHelper();
    CellStyle dateStyle = book.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));

    CellStyle numStyle = book.createCellStyle();
    numStyle.setDataFormat(createHelper.createDataFormat().getFormat("0.00000000"));

    CellStyle intNumStyle = book.createCellStyle();
    intNumStyle.setDataFormat(createHelper.createDataFormat().getFormat("0"));

    List<Object> result = context.getAttribute(execBean.getParamName());
    String[] columns = execBean.getColumns();
    if (execBean.getColumns() != null) {
        columns = execBean.getColumns();
    } else {
        columns = ((List<String>) context.getAttribute(execBean.getColumnsVarName())).toArray(new String[] {});
    }
    String sheetName;
    if (StringUtils.isNotEmpty(execBean.getSheetName())) {
        sheetName = execBean.getSheetName();
    } else {
        sheetName = context.getAttribute(execBean.getSheetVarName()).toString();
    }
    // int number = book.getNumberOfSheets();
    Sheet sheet = book.createSheet(sheetName);
    int rowNum = 0;
    Row firstRow = sheet.createRow(rowNum++);
    for (int i = 0; i < columns.length; i++) {
        Cell cell = firstRow.createCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(columns[i]);
    }

    for (int i = 0; i < result.size(); i++) {
        Object row = result.get(i);
        Row dataRow = sheet.createRow(rowNum++);

        for (int j = 0; j < columns.length; j++) {
            Object obj = PropertyUtils.getNestedProperty(row, columns[j]);
            Cell cell = dataRow.createCell(j);
            if (obj == null) {
                cell.setCellType(Cell.CELL_TYPE_BLANK);
            } else {
                if (obj instanceof String) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(obj.toString());
                } else if (obj instanceof Date) {
                    cell.setCellValue((Date) obj);
                    cell.setCellStyle(dateStyle);
                } else if (obj instanceof Integer || obj instanceof Long) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(intNumStyle);
                    cell.setCellValue(new Double(obj.toString()));
                } else if (obj instanceof Number) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(numStyle);
                    cell.setCellValue(new Double(obj.toString()));
                } else {
                    throw new DataProcessException("??Excel?");
                }
            }
        }
    }

    OutputStream os = null;
    try {
        os = execBean.getOutputStream();
        book.write(os);
    } finally {
        if (os != null) {
            os.close();
        }
    }
}