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.bizosys.dataservice.dao.WriteToXls.java

License:Apache License

private void createRecord(Object[] cols, Sheet sheet) throws SQLException {

    String colStr = null;/*from w  ww  . j ava  2s  .c  o  m*/

    Row row = sheet.createRow(startRowIndex++);
    int colI = 0;
    for (Object colObj : cols) {
        colStr = (null == colObj) ? EMPTY_STRING : colObj.toString().trim();
        Cell cell = row.createCell(colI);
        cell.setCellValue(colStr);
        colI++;
    }
}

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataSetWriterExcel.java

License:Apache License

private Sheet initNewSheet(RecordDef recordDef) {
    Sheet sheet = workbook.createSheet();
    // Write header row
    Row row = sheet.createRow(0);
    int cellIndex = 0;
    for (FieldDef fieldDef : recordDef) {
        Cell cell = row.createCell(cellIndex++);
        String cellValue = fieldDef.getDescription();
        cell.setCellValue(cellValue);//from ww  w . j  av  a 2  s.c  o  m
    }
    return sheet;
}

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataSetWriterExcel.java

License:Apache License

private void addDataRow(Sheet sheet, RecordDef recordDef, Record record, int rowIndex) throws Exception {
    Row row = sheet.createRow(rowIndex++);
    int cellIndex = 0;
    for (FieldDef fieldDef : recordDef) {
        Cell cell = row.createCell(cellIndex++);
        switch (fieldDef.getType()) {
        case STRING:
        case DATE:
            populateStringCell(record, fieldDef, cell);
            break;
        case HYPERLINK:
            populateHyperlinkCell(record, fieldDef, cell);
            break;

        }//w w  w . j  a v  a 2 s  .  co m
    }
}

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 {//from   w  ww. ja  v  a  2s .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 {/*w  w w.jav  a2 s .  co m*/
        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 av a  2  s.  c o m
    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);//  w w w  .j  a  va2 s  .  c o m
            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);
    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);//from  w  ww.  j a v a  2  s  . c om
        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));
                        }/*  w  w w .ja va  2  s .c om*/
                    }
                } 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);

    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);//from   ww w. java 2s . c o  m
    }

    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.");
}