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

Introduction

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

Prototype

void setCellStyle(CellStyle style);

Source Link

Document

Set the style for the cell.

Usage

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

public static ByteArrayOutputStream createTollUploadErrorResponse(InputStream is, List<String> errors)
        throws IOException {
    POIFSFileSystem fs = new POIFSFileSystem(is);
    HSSFWorkbook wb = new HSSFWorkbook(fs);

    HSSFFont font = wb.createFont();/*from  w  w  w.j a  v a 2s .  c o  m*/
    font.setColor(Font.COLOR_RED);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);

    HSSFSheet sheet = wb.getSheetAt(0);

    Row row = sheet.getRow(0);
    int lastCell = row.getLastCellNum();
    Cell cell = createExcelCell(sheet, row, lastCell, 256 * 100);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("ERRORS");

    for (String anError : errors) {
        String lineNoStr = StringUtils.substringBefore(anError, ":");
        lineNoStr = StringUtils.substringAfter(lineNoStr, "Line ");
        Integer lineNo = new Integer(lineNoStr) - 1;

        row = sheet.getRow(lineNo);
        cell = createExcelCell(sheet, row, lastCell, 256 * 100);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(anError);
    }

    return createOutputStream(wb);
}

---

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

public static ByteArrayOutputStream createTollUploadSuccessResponse() {
    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFFont font = wb.createFont();//from   w  w w .j av a 2 s  .  c  om
    font.setColor(IndexedColors.GREEN.getIndex());
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);

    Sheet sheet = wb.createSheet();

    Row row = sheet.createRow(0);
    Cell cell = createExcelCell(sheet, row, 0, 256 * 100);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("ALL tolls uploaded successfully");

    return createOutputStream(wb);
}

---

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

public static ByteArrayOutputStream createTollUploadExceptionResponse(Exception e) {
    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFFont font = wb.createFont();/*from www  . ja v  a2s.  c om*/
    font.setColor(Font.COLOR_RED);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);

    Sheet sheet = wb.createSheet();

    Row row = sheet.createRow(0);
    Cell cell = createExcelCell(sheet, row, 0, 256 * 100);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("An error occurred while uploading!!!");

    return createOutputStream(wb);
}

---

From source file:com.qihang.winter.poi.excel.export.base.ExcelExportBase.java

License:Apache License

/**
 * Cell/*www. j a v  a 2 s.com*/
 * 
 * @param row
 * @param index
 * @param text
 * @param style
 * @param entity
 */
public void createStringCell(Row row, int index, String text, CellStyle style,
        com.qihang.winter.poi.excel.entity.params.ExcelExportEntity entity) {
    Cell cell = row.createCell(index);
    if (style != null && style.getDataFormat() > 0 && style.getDataFormat() < 12) {
        cell.setCellValue(Double.parseDouble(text));
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else {
        RichTextString Rtext;
        if (type.equals(com.qihang.winter.poi.excel.entity.enmus.ExcelType.HSSF)) {
            Rtext = new HSSFRichTextString(text);
        } else {
            Rtext = new XSSFRichTextString(text);
        }
        cell.setCellValue(Rtext);
    }
    if (style != null) {
        cell.setCellStyle(style);
    }
    addStatisticsData(index, text, entity);
}

---

From source file:com.qihang.winter.poi.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * ?(?,,?)//ww  w .ja  v  a 2  s.  co m
 *
 * @param params
 * @param object
 * @param cell
 * @param excelParams
 * @param titleString
 * @param row
 * @throws Exception
 */
private void saveFieldValue(com.qihang.winter.poi.excel.entity.ImportParams params, Object object, Cell cell,
        Map<String, com.qihang.winter.poi.excel.entity.params.ExcelImportEntity> excelParams,
        String titleString, Row row) throws Exception {
    Object value = cellValueServer.getValue(params.getDataHanlder(), object, cell, excelParams, titleString);
    if (object instanceof Map) {
        if (params.getDataHanlder() != null) {
            params.getDataHanlder().setMapValue((Map) object, titleString, value);
        } else {
            ((Map) object).put(titleString, value);
        }
    } else {
        com.qihang.winter.poi.excel.entity.result.ExcelVerifyHanlderResult verifyResult = verifyHandlerServer
                .verifyData(object, value, titleString, excelParams.get(titleString).getVerify(),
                        params.getVerifyHanlder());
        if (verifyResult.isSuccess()) {
            setValues(excelParams.get(titleString), object, value);
        } else {
            Cell errorCell = row.createCell(row.getLastCellNum());
            errorCell.setCellValue(verifyResult.getMsg());
            errorCell.setCellStyle(errorCellStyle);
            verfiyFail = true;
            throw new com.qihang.winter.poi.exception.excel.ExcelImportException(
                    com.qihang.winter.poi.exception.excel.enums.ExcelImportEnum.VERIFY_ERROR);
        }
    }
}

---

From source file:com.qihang.winter.poi.util.PoiSheetUtility.java

License:Apache License

private static void cloneCell(Cell cNew, Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());

    switch (cNew.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN: {
        cNew.setCellValue(cOld.getBooleanCellValue());
        break;/* w  ww.ja  v  a 2 s.  c  om*/
    }
    case Cell.CELL_TYPE_NUMERIC: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        cNew.setCellValue(cOld.getStringCellValue());
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        cNew.setCellValue(cOld.getErrorCellValue());
        break;
    }
    case Cell.CELL_TYPE_FORMULA: {
        cNew.setCellFormula(cOld.getCellFormula());
        break;
    }
    }

}

---

From source file:com.quanticate.opensource.datalistdownload.DataListDownloadWebScript.java

License:Open Source License

@Override
protected void populateBody(Object resource, Workbook workbook, Sheet sheet, List<QName> properties)
        throws IOException {
    NodeRef list = (NodeRef) resource;/*  w  w  w  . j  a v a 2s. c  om*/
    List<NodeRef> items = getItems(list);

    // Our various formats
    DataFormat formatter = workbook.createDataFormat();

    CellStyle styleInt = workbook.createCellStyle();
    styleInt.setDataFormat(formatter.getFormat("0"));
    CellStyle styleDate = workbook.createCellStyle();
    styleDate.setDataFormat(formatter.getFormat("yyyy-mm-dd"));
    CellStyle styleDouble = workbook.createCellStyle();
    styleDouble.setDataFormat(formatter.getFormat("General"));
    CellStyle styleNewLines = workbook.createCellStyle();
    styleNewLines.setWrapText(true);

    // Export the items
    int rowNum = 1, colNum = 0;
    for (NodeRef item : items) {
        Row r = sheet.createRow(rowNum);

        colNum = 0;
        for (QName prop : properties) {
            Cell c = r.createCell(colNum);

            Pair<Object, String> valAndLink = identifyValueAndLink(item, prop);

            if (valAndLink == null) {
                // This property isn't set
                c.setCellType(Cell.CELL_TYPE_BLANK);
            } else {
                Object val = valAndLink.getFirst();

                // Multi-line property?
                if (val instanceof String[]) {
                    String[] lines = (String[]) val;
                    StringBuffer text = new StringBuffer();

                    for (String line : lines) {
                        if (text.length() > 0) {
                            text.append('\n');
                        }
                        text.append(line);
                    }

                    String v = text.toString();
                    c.setCellValue(v);
                    if (lines.length > 1) {
                        c.setCellStyle(styleNewLines);
                        r.setHeightInPoints(lines.length * sheet.getDefaultRowHeightInPoints());
                    }
                }

                // Regular properties
                else if (val instanceof String) {
                    c.setCellValue((String) val);
                } else if (val instanceof Date) {
                    c.setCellValue((Date) val);
                    c.setCellStyle(styleDate);
                } else if (val instanceof Integer || val instanceof Long) {
                    double v = 0.0;
                    if (val instanceof Long)
                        v = (double) (Long) val;
                    if (val instanceof Integer)
                        v = (double) (Integer) val;
                    c.setCellValue(v);
                    c.setCellStyle(styleInt);
                } else if (val instanceof Float || val instanceof Double) {
                    double v = 0.0;
                    if (val instanceof Float)
                        v = (double) (Float) val;
                    if (val instanceof Double)
                        v = (double) (Double) val;
                    c.setCellValue(v);
                    c.setCellStyle(styleDouble);
                } else {
                    // TODO
                    System.err
                            .println("TODO: Handle Excel output of " + val.getClass().getName() + " - " + val);
                }
            }

            colNum++;
        }

        rowNum++;
    }

    // Sensible column widths please!
    colNum = 0;
    for (QName prop : properties) {
        sheet.autoSizeColumn(colNum);
        colNum++;
    }
}

---

From source file:com.quanticate.opensource.datalistdownload.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body.//from   w w w  . jav a 2  s.  com
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");

    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY);
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else if ("odf".equals(format) || "ods".equals(format)) {
        try {
            SpreadsheetDocument odf = SpreadsheetDocument.newSpreadsheetDocument();

            // Add the header row
            Table sheet = odf.appendSheet("Export");
            org.odftoolkit.simple.table.Row hr = sheet.appendRow();

            // TODO

            // Have the contents populated
            // TODO

            // Save it for the template
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            odf.save(baos);
            model.put(MODEL_ODF, baos.toByteArray());
        } catch (Exception e) {
            throw new WebScriptException("Error creating ODF file", e);
        }
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        sheet.createFreezePane(0, 1);

        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

---

From source file:com.qwazr.externalizor.BenchmarkTest.java

License:Apache License

private static void setCell(CellStyle style, Row row, int pos, String value) {
    Cell cell = row.createCell(pos);
    cell.setCellStyle(style);
    cell.setCellValue(value);/*w w  w.  ja  v a2 s.  c  om*/
}

---

From source file:com.qwazr.library.poi.ExcelBuilder.java

License:Apache License

/**
 * Create a new cell at the current cursor position. The horizontal position of the cursor is incremented.
 *
 * @param object the content of the cell
 * @return the created cell/*from www.j  a v  a2 s. c  o  m*/
 */
public Cell addOneCell(final Object object) {
    if (object == null) {
        incCell(1);
        return null;
    }
    Cell cell = currentRow.getCell(xpos.get());
    if (cell == null)
        cell = currentRow.createCell(xpos.get());
    if (object instanceof Calendar) {
        cell.setCellValue((Calendar) object);
        if (defaultDateCellStyle != null)
            cell.setCellStyle(defaultDateCellStyle);
    } else if (object instanceof Date) {
        cell.setCellValue((Date) object);
        if (defaultDateCellStyle != null)
            cell.setCellStyle(defaultDateCellStyle);
    } else if (object instanceof Number)
        cell.setCellValue(((Number) object).doubleValue());
    if (defaultNumberCellStyle != null)
        cell.setCellStyle(defaultNumberCellStyle);
    else if (object instanceof Boolean)
        cell.setCellValue((Boolean) object);
    else
        cell.setCellValue(object.toString());
    xpos.incrementAndGet();
    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
• »