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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. 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:biz.webgate.dominoext.poi.component.kernel.simpleviewexport.WorkbooklExportProcessor.java

License:Apache License

private void setCellValue(Object objValue, Cell cellCurrent, ExportColumn expCol,
        HashMap<String, CellStyle> hsStyle) {
    if (objValue instanceof Double) {
        cellCurrent.setCellValue((Double) objValue);
        return;// w ww  .j  a v  a 2 s.  c o  m
    }
    if (objValue instanceof Integer) {
        cellCurrent.setCellValue((Integer) objValue);
        return;
    }
    if (objValue instanceof Date) {
        cellCurrent.setCellValue((Date) objValue);
        switch (expCol.getTimeDateFormat()) {
        case ViewColumn.FMT_DATE:
            cellCurrent.setCellStyle(hsStyle.get("DATE"));
            break;
        case ViewColumn.FMT_TIME:
            cellCurrent.setCellStyle(hsStyle.get("TIME"));
            break;
        default:
            cellCurrent.setCellStyle(hsStyle.get("DATETIME"));
            break;
        }
        return;
    }
    if (objValue instanceof DateTime) {
        try {
            cellCurrent.setCellValue(((DateTime) objValue).toJavaDate());
            switch (expCol.getTimeDateFormat()) {
            case ViewColumn.FMT_DATE:
                cellCurrent.setCellStyle(hsStyle.get("DATE"));
                break;
            case ViewColumn.FMT_TIME:
                cellCurrent.setCellStyle(hsStyle.get("TIME"));
                break;
            default:
                cellCurrent.setCellStyle(hsStyle.get("DATETIME"));
                break;
            }
        } catch (Exception e) {
            cellCurrent.setCellValue("" + objValue);
            e.printStackTrace();
        }
        return;
    }
    if (objValue instanceof Vector<?>) {
        Vector<?> vec = (Vector<?>) objValue;
        StringBuilder sb = new StringBuilder();
        for (Iterator<?> it = vec.iterator(); it.hasNext();) {
            sb.append("" + it.next());
            if (it.hasNext()) {
                sb.append(";");
            }
        }
        cellCurrent.setCellValue(sb.toString());
        return;
    }

    cellCurrent.setCellValue("" + objValue);

}

From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java

License:Apache License

public void setCellValue(Sheet shProcess, int nRow, int nCol, Object objValue, boolean isFormula,
        PoiCellStyle pCellStyle) {// w  ww.  j av  a  2  s.  c  o m
    // Logger logCurrent =
    // LoggerFactory.getLogger(WorkbookProcessor.class.getCanonicalName());

    try {
        Row rw = shProcess.getRow(nRow);
        if (rw == null) {
            // logCurrent.finest("Create Row");
            rw = shProcess.createRow(nRow);
        }
        Cell c = rw.getCell(nCol);
        if (c == null) {
            // logCurrent.finest("Create Cell");
            c = rw.createCell(nCol);
        }
        if (isFormula) {
            c.setCellFormula((String) objValue);
        } else {
            if (objValue instanceof Double) {
                c.setCellValue((Double) objValue);
            } else if (objValue instanceof Integer) {
                c.setCellValue((Integer) objValue);
            } else {
                if (objValue instanceof Date) {
                    c.setCellValue((Date) objValue);
                } else {
                    c.setCellValue("" + objValue);
                }
            }
        }
        // *** STYLE CONFIG Since V 1.1.7 ***

        if (pCellStyle != null) {
            checkStyleConstantValues();
            if (pCellStyle.getCellStyle() != null) {
                c.setCellStyle(pCellStyle.getCellStyle());
            } else {
                CellStyle style = shProcess.getWorkbook().createCellStyle();

                if (pCellStyle.getAlignment() != null)
                    style.setAlignment(m_StyleConstantValues.get(pCellStyle.getAlignment()));

                if (pCellStyle.getBorderBottom() != null)
                    style.setBorderBottom(m_StyleConstantValues.get(pCellStyle.getBorderBottom()));

                if (pCellStyle.getBorderLeft() != null)
                    style.setBorderLeft(m_StyleConstantValues.get(pCellStyle.getBorderLeft()));

                if (pCellStyle.getBorderRight() != null)
                    style.setBorderRight(m_StyleConstantValues.get(pCellStyle.getBorderRight()));

                if (pCellStyle.getBorderTop() != null)
                    style.setBorderTop(m_StyleConstantValues.get(pCellStyle.getBorderTop()));

                if (pCellStyle.getBottomBorderColor() != null)
                    style.setBottomBorderColor(
                            IndexedColors.valueOf(pCellStyle.getBottomBorderColor()).getIndex());

                if (pCellStyle.getDataFormat() != null) {
                    DataFormat format = shProcess.getWorkbook().createDataFormat();
                    style.setDataFormat(format.getFormat(pCellStyle.getDataFormat()));
                }

                if (pCellStyle.getFillBackgroundColor() != null)
                    style.setFillBackgroundColor(
                            IndexedColors.valueOf(pCellStyle.getFillBackgroundColor()).getIndex());

                if (pCellStyle.getFillForegroundColor() != null)
                    style.setFillForegroundColor(
                            IndexedColors.valueOf(pCellStyle.getFillForegroundColor()).getIndex());

                if (pCellStyle.getFillPattern() != null)
                    style.setFillPattern(m_StyleConstantValues.get(pCellStyle.getFillPattern()));

                // Create a new font and alter it.
                Font font = shProcess.getWorkbook().createFont();

                if (pCellStyle.getFontBoldweight() != null)
                    font.setBoldweight(m_StyleConstantValues.get(pCellStyle.getFontBoldweight()));

                if (pCellStyle.getFontColor() != null)
                    font.setColor(IndexedColors.valueOf(pCellStyle.getFontColor()).getIndex());

                if (pCellStyle.getFontHeightInPoints() != 0)
                    font.setFontHeightInPoints(pCellStyle.getFontHeightInPoints());

                if (pCellStyle.getFontName() != null)
                    font.setFontName(pCellStyle.getFontName());

                if (pCellStyle.isFontItalic())
                    font.setItalic(pCellStyle.isFontItalic());

                if (pCellStyle.isFontStrikeout())
                    font.setStrikeout(pCellStyle.isFontStrikeout());

                if (pCellStyle.getFontUnderline() != null)
                    font.setUnderline(m_StyleByteConstantValues.get(pCellStyle.getFontUnderline()));

                if (pCellStyle.getFontTypeOffset() != null)
                    font.setTypeOffset(m_StyleConstantValues.get(pCellStyle.getFontTypeOffset()));

                // Set Font
                style.setFont(font);

                if (pCellStyle.isHidden())
                    style.setHidden(pCellStyle.isHidden());

                if (pCellStyle.getIndention() != null)
                    style.setIndention(m_StyleConstantValues.get(pCellStyle.getIndention()));

                if (pCellStyle.getLeftBorderColor() != null)
                    style.setLeftBorderColor(IndexedColors.valueOf(pCellStyle.getLeftBorderColor()).getIndex());

                if (pCellStyle.isLocked())
                    style.setLocked(pCellStyle.isLocked());

                if (pCellStyle.getRightBorderColor() != null)
                    style.setRightBorderColor(
                            IndexedColors.valueOf(pCellStyle.getRightBorderColor()).getIndex());

                if (pCellStyle.getRotation() != 0)
                    style.setRotation(pCellStyle.getRotation());

                if (pCellStyle.getTopBorderColor() != null)
                    style.setTopBorderColor(IndexedColors.valueOf(pCellStyle.getTopBorderColor()).getIndex());

                if (pCellStyle.getVerticalAlignment() != null)
                    style.setVerticalAlignment(m_StyleConstantValues.get(pCellStyle.getVerticalAlignment()));

                if (pCellStyle.isWrapText())
                    style.setWrapText(pCellStyle.isWrapText());

                c.setCellStyle(style);
                pCellStyle.setCellStyle(style);
            }

        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:bo.com.offercruzmail.utils.HojaExcelHelper.java

public void setValorCelda(int rowIndex, int colIndex, Date valor) {
    Cell celda = getCelda(rowIndex, colIndex);
    if (valor != null) {
        celda.setCellValue(valor);//from   www .j  av  a 2s  . c o  m
        CreationHelper createHelper = hoja.getWorkbook().getCreationHelper();
        CellStyle estiloFecha = hoja.getWorkbook().createCellStyle();
        estiloFecha.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy"));
        celda.setCellStyle(estiloFecha);
    }
}

From source file:br.com.algoritmo.compilacao.CompilaXlsx.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//w w w  .  j  a  v  a2  s  .c  o  m
    Map<Integer, Object[]> data = new TreeMap<Integer, Object[]>();
    data.put(0, new Object[] { 0, "Luiz Carlos Miyadaira Ribeiro Junior", "Base", "0468265522433921",
            "SOFTWARE", null, null, null });
    data.put(1, new Object[] { 1, "Sergio Antnio Andrade de Freitas", "Destino 1", "0395549254894676",
            "SOFTWARE", null, null, null });
    data.put(2, new Object[] { 2, "Andre Luiz Aquere de Cerqueira e Souza", "Destino 2", "8424412648258970",
            "CIVIL", null, null, null });
    data.put(3, new Object[] { 3, "Edson Mintsu Hung Destino", "Destino 3", "6753551743147880", "ELETRNICA",
            null, null, null });
    data.put(4, new Object[] { 4, "Edgard Costa Oliveira", "Destino 4", "1196380808351110", "SOFTWARE", null,
            null, null });
    data.put(5, new Object[] { 5, "Edson Alves da Costa Jnior", "Destino 5", "2105379147123450", "SOFTWARE",
            null, null, null });
    data.put(6, new Object[] { 6, "Andr Barros de Sales", "Destino 6", "7610669796869660", "SOFTWARE", null,
            null, null });
    data.put(7, new Object[] { 7, "Giovanni Almeida dos Santos", "Destino 7", "0580891429319047", "SOFTWARE",
            null, null, null });
    data.put(8, new Object[] { 8, "Cristiane Soares Ramos", "Destino 8", "9950213660160160", "SOFTWARE", null,
            null, null });
    data.put(9, new Object[] { 9, "Fabricio Ataides Braz", "Destino 9", "1700216932505000", "SOFTWARE", null,
            null, null });
    data.put(10, new Object[] { 10, "Alexandre Srgio de Arajo Bezerra", "Destino 10", "0255998976169051",
            "MEDICINA", null, null, null });
    data.put(11, new Object[] { 11, "Eduardo Stockler Tognetti", "Destino 11", "2443108673822680", "ELTRICA",
            null, null, null });
    data.put(12, new Object[] { 12, "Jan Mendona Correa", "Destino 12", "7844006017790570",
            "CINCIA DA COMPUTAO", null, null, null });
    data.put(13, new Object[] { 13, "Rejane Maria da Costa Figueiredo", "Destino 13", "2187680174312042",
            "SOFTWARE", null, null, null });
    data.put(14, new Object[] { 14, "Augusto Csar de Mendona Brasil", "Destino 14", "0571960641751286",
            "ENERGIA", null, null, null });
    data.put(15, new Object[] { 15, "Fbio Macdo Mendes", "Destino 15", "8075435338067780", "F?SICA", null,
            null, null });

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet aba1 = wb.createSheet("Percentual de similaridade 1");
    PrintSetup printSetup = aba1.getPrintSetup();
    printSetup.setLandscape(true);
    aba1.setFitToPage(true);
    aba1.setHorizontallyCenter(true);

    Sheet aba2 = wb.createSheet("Percentual de similaridade 2");
    PrintSetup printSetup2 = aba2.getPrintSetup();
    printSetup2.setLandscape(true);
    aba1.setFitToPage(true);
    aba1.setHorizontallyCenter(true);

    //title row
    Row titleRow = aba1.createRow(0);
    titleRow.setHeightInPoints(15);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(
            "Resultado da aplicao do algoritmo de clculo do percentual de similaridade entre os indivduos");
    titleCell.setCellStyle(styles.get("title"));
    aba1.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1"));

    //header row
    Row headerRow = aba1.createRow(1);
    headerRow.setHeightInPoints(15);
    Cell headerCell;
    for (int i = 1; i <= titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i - 1]);
        headerCell.setCellStyle(styles.get("header"));
    }

    Row headerBase = aba1.createRow(2);
    headerBase.setHeightInPoints(15);
    Cell headerCellBase;
    for (int i = 1; i <= base.length; i++) {
        headerCellBase = headerBase.createCell(i);
        headerCellBase.setCellValue(base[i - 1]);
        headerCellBase.setCellStyle(styles.get("header1"));
    }

    Row headerDestino = aba1.createRow(4);
    headerDestino.setHeightInPoints(15);
    Cell headerCellDestino;
    for (int i = 1; i <= destino.length; i++) {
        headerCellDestino = headerDestino.createCell(i);
        headerCellDestino.setCellValue(destino[i - 1]);
        headerCellDestino.setCellStyle(styles.get("header1"));
    }

    /*int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
     Cell cell = row.createCell(j);
     if(j == 9){
         //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
         String ref = "C" +rownum+ ":I" + rownum;
         cell.setCellFormula("SUM("+ref+")");
         cell.setCellStyle(styles.get("formula"));
     } else if (j == 11){
         cell.setCellFormula("J" +rownum+ "-K" + rownum);
         cell.setCellStyle(styles.get("formula"));
     } else {
         cell.setCellStyle(styles.get("cell"));
     }
        }
    }
            
    rownum = 3;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles1.length; j++) {
     Cell cell = row.createCell(j);
     if(j == 9){
         //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
         String ref = "C" +rownum+ ":I" + rownum;
         cell.setCellFormula("SUM("+ref+")");
         cell.setCellStyle(styles.get("formula"));
     } else if (j == 11){
         cell.setCellFormula("J" +rownum+ "-K" + rownum);
         cell.setCellStyle(styles.get("formula"));
     } else {
         cell.setCellStyle(styles.get("cell"));
     }
        }
    }
    */
    //set sample data
    //Iterate over data and write to sheet
    Set<Integer> keyset = data.keySet();
    int rownum = 0;
    for (Integer key : keyset) {
        Row row = aba1.createRow(3 + rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
        }
        if (row.getRowNum() == 3) {
            rownum++;
        }
    }
    //finally set column widths, the width is measured in units of 1/256th of a character width
    aba1.setColumnWidth(0, 2 * 256); //2 characters wide
    aba1.setColumnWidth(1, 26 * 256); //26 characters wide
    aba1.setColumnWidth(2, 20 * 256); //20 characters wide
    aba1.setColumnWidth(3, 18 * 256); //18 characters wide
    aba1.setColumnWidth(4, 20 * 256); //20 characters wide
    for (int i = 5; i < 9; i++) {
        aba1.setColumnWidth(i, 15 * 256); //6 characters wide
    }

    // Write the output to a file
    String file = "Sada/Percentual de similaridade.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java

public Workbook bindXml(Document document, Workbook workbook) throws XPathExpressionException {

    XPath xPath = XPathFactory.newInstance().newXPath();
    NodeList cellValueList = (NodeList) xPath.evaluate("//cellValue", document, XPathConstants.NODESET);
    NodeList rowNodeList = (NodeList) xPath.evaluate("//row", document, XPathConstants.NODESET);
    Node rowsNode = (Node) xPath.evaluate("//rows", document, XPathConstants.NODE);

    Sheet sheet = workbook.getSheetAt(0);

    for (int i = 0; i < cellValueList.getLength(); i++) {
        Node cellValue = cellValueList.item(i);
        String cellName = cellValue.getAttributes().getNamedItem("ref").getTextContent();
        String type = cellValue.getAttributes().getNamedItem("type").getTextContent();
        String value = cellValue.getTextContent();
        CellReference cellRef = new CellReference(cellName);
        Row row = sheet.getRow(cellRef.getRow());
        Cell cell = row.getCell(cellRef.getCol());

        if ("number".equals(type)) {
            double doubleValue = Double.valueOf(value);
            cell.setCellValue(doubleValue);
        } else if ("date".equals(type)) {
            Date dateValue = new Date(Long.valueOf(value));
            cell.setCellValue(dateValue);
        } else if ("bool".equals(type)) {
            boolean boolValue = Boolean.valueOf(value);
            cell.setCellValue(boolValue);
        } else if ("formula".equals(type)) {
            cell.setCellFormula(value);//w w w .  j  av  a2  s . c o m
        } else {
            cell.setCellValue(value);
        }
    }

    if (rowsNode != null && rowNodeList != null && rowNodeList.getLength() > 0) {
        CellReference startCellRef = new CellReference(
                rowsNode.getAttributes().getNamedItem("startRef").getTextContent());
        CellReference endCellRef = new CellReference(
                rowsNode.getAttributes().getNamedItem("endRef").getTextContent());
        int startRowIndex = startCellRef.getRow();
        int startColIndex = startCellRef.getCol();
        int endColIndex = endCellRef.getCol();
        CellStyle[] cellStyles = new CellStyle[endColIndex + 1];
        Row firstRow = sheet.getRow(startRowIndex);

        for (int i = startColIndex; i <= endColIndex; i++) {
            cellStyles[i] = firstRow.getCell(i).getCellStyle();
        }

        for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
            Row templeteRow = sheet.getRow(i);

            if (templeteRow != null) {
                sheet.removeRow(templeteRow);
            }
        }

        int rowNodeIndex = 0;

        for (int i = startRowIndex; i < startRowIndex + rowNodeList.getLength(); i++) {

            Row row = sheet.createRow(i);
            int cellNodeIndex = 0;
            Node rowNode = rowNodeList.item(rowNodeIndex);
            NodeList rowValueNodeList = rowNode.getChildNodes();
            ArrayList<Node> nodes = new ArrayList<Node>();

            for (int idx = 0; idx < rowValueNodeList.getLength(); idx++) {
                Node currentNode = rowValueNodeList.item(idx);
                if (currentNode.getNodeType() == Node.ELEMENT_NODE) {
                    nodes.add(currentNode);
                }
            }

            for (int j = startColIndex; j <= endColIndex; j++) {
                Cell cell = row.createCell(j);
                Node cellNode = nodes.get(cellNodeIndex);
                String type = cellNode.getAttributes().getNamedItem("type").getTextContent();
                String value = cellNode.getTextContent();
                CellStyle cellStyle = cellStyles[j];

                cell.setCellStyle(cellStyle);

                if ("number".equals(type)) {
                    double doubleValue = Double.valueOf(value);
                    cell.setCellValue(doubleValue);
                } else if ("date".equals(type)) {
                    Date dateValue = new Date(Long.valueOf(value));
                    cell.setCellValue(dateValue);
                } else if ("bool".equals(type)) {
                    boolean boolValue = Boolean.valueOf(value);
                    cell.setCellValue(boolValue);
                } else if ("formula".equals(type)) {
                    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                    cell.setCellFormula(value);
                } else if ("string".equals(type)) {
                    if (value != null && value.length() > 0) {
                        cell.setCellValue(value);
                    } else {
                        cell.setCellValue("");
                    }
                } else {
                    cell.setCellValue("");
                }

                cellNodeIndex++;
            }
            rowNodeIndex++;
        }
    }

    return workbook;
}

From source file:br.com.objectos.xls.WorksheetRowWriter.java

License:Apache License

void writeThis() {
    Cell cell = nextCell();
    if (cell == null) {
        return;//from w ww  .  j a  va  2  s .c  o m
    }

    if (blank) {
        writeBlank(cell);
    } else {
        writeCell(cell);
    }

    Optional<CellStyle> maybeStyle = row.getCellStyle(canStyleList);
    if (maybeStyle.isPresent()) {
        CellStyle cellStyle = maybeStyle.get();
        cell.setCellStyle(cellStyle);
    }
}

From source file:br.com.techne.gluonsoft.eowexport.builder.ExcelBuilder.java

License:Apache License

/**
 * mtodo cria bytes de documento Excel// w  w  w. ja v  a2  s. c om
 * @param titles
 * @param columnIndex
 * @param dataRows
 * @param locale
 * @return
 * @throws Exception
 */
public static byte[] createExcelBytes(String[] titles, String[] columnIndex,
        List<HashMap<String, Object>> dataRows, Locale locale) throws Exception {

    //Workbook wb = new HSSFWorkbook();
    XSSFWorkbook wb = new XSSFWorkbook();
    byte[] outBytes;

    try {
        HashMap<String, CellStyle> styles = createStyles(wb);
        Sheet sheet = wb.createSheet("Tab 1");

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);

        //the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short) 1);
        printSetup.setFitWidth((short) 1);

        //the header row: centered text in 48pt font
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(12.75f);

        for (int indexColumn = 0; indexColumn < titles.length; indexColumn++) {
            Cell cell = headerRow.createCell(indexColumn);
            cell.setCellValue(titles[indexColumn]);

            if ((titles.length - 1) < indexColumn) {
                cell.setCellValue("");
            } else
                cell.setCellValue(titles[indexColumn]);

            cell.setCellStyle(styles.get("header"));
        }

        //freeze the first row
        sheet.createFreezePane(0, 1);

        Row row;
        Cell cell;
        int rownum = 1;//devido constar titulo, comea do indice 1

        ValueCellUtil vcutil = new ValueCellUtil(locale);

        for (int indexRow = 0; indexRow < dataRows.size(); indexRow++, rownum++) {

            row = sheet.createRow(rownum);
            HashMap<String, Object> dataRow = dataRows.get(indexRow);

            if (dataRow == null)
                continue;

            List<String> keysAttribs = null;

            if (columnIndex.length == 0) {
                keysAttribs = Arrays.asList(dataRow.keySet().toArray(new String[0]));
                Collections.reverse(keysAttribs);
            } else {
                keysAttribs = Arrays.asList(columnIndex);
            }

            int colCt = 0;

            for (String keyAttrib : keysAttribs) {

                cell = row.createCell(colCt);
                String styleName;
                cell.setCellValue(vcutil.parseValue(dataRow.get(keyAttrib)).toString());

                //zebrando tabela
                if (indexRow % 2 == 0) {
                    // even row
                    styleName = "cell_normal_even";
                } else {
                    // odd row
                    styleName = "cell_normal_odd";
                }

                if (indexRow == 0) {
                    //setando auto ajuste
                    sheet.autoSizeColumn(colCt);
                }

                cell.setCellStyle(styles.get(styleName));
                colCt++;
            }
        }

        sheet.setZoom(75); //75% scale

        // Write the output to a file
        // write for return byte[]
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            wb.write(out);
            outBytes = out.toByteArray();
        } finally {
            out.close();
        }
    } finally {
        wb.close();
    }

    return outBytes;
}

From source file:br.com.tecsinapse.dataio.util.WorkbookUtil.java

License:LGPL

private void setCellStyle(Cell cell, TableCell tableCell, Workbook wb, String cellFormat) {
    TableCellStyle tableCellStyle = tableCell.getTableCellStyle();
    if (tableCellStyle == null) {
        return;/*from  w  ww . j  a v  a  2 s .  c o m*/
    }
    tableCellStyle = tableCellStyle.duplicate();
    tableCellStyle.setCellFormat(cellFormat);
    cell.setCellStyle(getOrNewCellStyle(tableCellStyle, wb));
}

From source file:br.com.tecsinapse.exporter.util.WorkbookUtil.java

License:LGPL

private void setCellStyle(Cell cell, TableCell tableCell, Workbook wb, String cellFormat) {
    TableCellStyle tableCellStyle = tableCell.getTableCellStyle().clone();
    tableCellStyle.setCellFormat(cellFormat);
    cell.setCellStyle(getOrNewCellStyle(tableCellStyle, wb));
}

From source file:br.edu.tglima.model.result.PlanilhaXLS.java

License:Open Source License

/**
 * Mtodo responsvel por gerar a planilha XLS.
 * /*  w w  w.j a v  a2s.  c  om*/
 * 
 * @param arquivo Referente ao local e nome do arquivo.
 * @return Retorno do tipo Boolean, indicando se o arquivo foi gerado
 * com sucesso, ou no.
 */
public boolean gerarPlanilha(File arquivo) {

    try {

        /*Verificamos se j existe um arquivo com esse "nome".
        Caso ele exista, ele ser removido e o novo arquivo ser gerado.*/
        if (workbook.getNumberOfSheets() > 0) {
            workbook.removeSheetAt(0);
        }

        HSSFSheet sheet = workbook.createSheet("Valores Exportados");

        /*         Criando as linhas. --------------------------------------------- */

        HSSFRow header1 = sheet.createRow((short) 0);
        HSSFRow linha02 = sheet.createRow((short) 1);
        HSSFRow linha03 = sheet.createRow((short) 2);
        HSSFRow linha04 = sheet.createRow((short) 3);
        HSSFRow linha05 = sheet.createRow((short) 4);

        HSSFRow header2 = sheet.createRow((short) 7);
        HSSFRow linha09 = sheet.createRow((short) 8);
        HSSFRow linha10 = sheet.createRow((short) 9);
        HSSFRow linha11 = sheet.createRow((short) 10);
        HSSFRow linha12 = sheet.createRow((short) 11);
        HSSFRow linha13 = sheet.createRow((short) 12);
        HSSFRow linha14 = sheet.createRow((short) 13);
        HSSFRow linha15 = sheet.createRow((short) 14);
        HSSFRow linha17 = sheet.createRow((short) 16);

        HSSFRow header3 = sheet.createRow((short) 19);
        HSSFRow linha21 = sheet.createRow((short) 20);
        HSSFRow linha22 = sheet.createRow((short) 21);
        HSSFRow linha23 = sheet.createRow((short) 22);
        HSSFRow linha25 = sheet.createRow((short) 24);

        //  ------------------------------------------------------------------------ //

        /*         Mesclando as clulas. ------------------------------------------ */

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
        sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 2));
        sheet.addMergedRegion(new CellRangeAddress(19, 19, 0, 2));

        //  ------------------------------------------------------------------------ //

        /*         Definindo a largura das colunas. ------------------------------- */

        sheet.setColumnWidth(0, 12000);
        sheet.setColumnWidth(1, 5200);
        sheet.setColumnWidth(2, 6500);

        //  ------------------------------------------------------------------------ //

        /*         Definindo os estilos das clulas. ------------------------------ */

        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 20);
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);

        CellStyle cellCentered = workbook.createCellStyle();
        cellCentered.setAlignment(HorizontalAlignment.CENTER);

        CellStyle cellFontBold = workbook.createCellStyle();
        Font fontBold = workbook.createFont();
        fontBold.setBold(true);
        cellFontBold.setFont(fontBold);

        CellStyle cellResulted = workbook.createCellStyle();
        Font resultFont = workbook.createFont();
        resultFont.setBold(true);
        cellResulted.setFont(resultFont);
        cellResulted.setAlignment(HorizontalAlignment.CENTER);

        Cell cell;

        //  ------------------------------------------------------------------------ //

        /*         Criando as Colunas da Tabela ----------------------------------- */

        //Colunas da linha 1
        header1.createCell(0).setCellValue("Dados Fornecidos");

        //Colunas da linha 2
        linha02.createCell(0).setCellValue("Data de Entrada");
        linha02.createCell(2).setCellValue(this.rst.getDataEntrada());

        //Colunas da Linha 3
        linha03.createCell(0).setCellValue("Data de Sada");
        linha03.createCell(2).setCellValue(this.rst.getDataSaida());

        //Colunas da Linha 4
        linha04.createCell(0).setCellValue("Salrio Informado");
        linha04.createCell(2).setCellValue(this.rst.getSalario());

        //Colunas da Linha 5
        linha05.createCell(0).setCellValue("Motivo da Sada");
        linha05.createCell(2).setCellValue(this.rst.getMotivoRes());

        //Colunas da linha 8
        header2.createCell(0).setCellValue("Resciso");

        //Colunas da linha 9
        linha09.createCell(0).setCellValue("Item");
        linha09.createCell(1).setCellValue("Referncia");
        linha09.createCell(2).setCellValue("Valor");

        //Colunas da Linha 10
        linha10.createCell(0).setCellValue("Saldo Salrio");
        linha10.createCell(1).setCellValue(this.rst.getTotDiasTrabUltMes());
        linha10.createCell(2).setCellValue(this.rst.getUltSalario());

        //Colunas da Linha 11
        linha11.createCell(0).setCellValue("13 Proporcional");
        linha11.createCell(1).setCellValue(this.rst.getTotMesesTrabUltAno());
        linha11.createCell(2).setCellValue(this.rst.getVlrDecimo());

        //Colunas da Linha 12
        linha12.createCell(0).setCellValue("Frias Proporcional");
        linha12.createCell(1).setCellValue(this.rst.getTotMesesAqFerias());
        linha12.createCell(2).setCellValue(this.rst.getVlrFerias());

        //Colunas da Linha 13
        linha13.createCell(0).setCellValue("1/3 Frias Proporcional");
        linha13.createCell(1).setCellValue("-");
        linha13.createCell(2).setCellValue(this.rst.getVlrTercoFerias());

        //Colunas da Linha 14
        linha14.createCell(0).setCellValue("Frias Vencidas");
        linha14.createCell(1).setCellValue(this.rst.getTotFeriasVenc());
        linha14.createCell(2).setCellValue(this.rst.getVlrFeriasVenc());

        //Colunas da linha 15
        linha15.createCell(0).setCellValue("Aviso Prvio");
        linha15.createCell(1).setCellValue(this.rst.getTotDiasAviso());
        linha15.createCell(2).setCellValue(this.rst.getVlrAvisoP());

        //Colunas da linha 17
        linha17.createCell(0).setCellValue("Valor Total");
        linha17.createCell(1).setCellValue("-");
        linha17.createCell(2).setCellValue(this.rst.getVlrTotVenc());

        //Colunas da Linha 20
        header3.createCell(0).setCellValue("FGTS");

        //Colunas da Linha 21
        linha21.createCell(0).setCellValue("Valores do FGTS estaro disponveis para saque?");
        linha21.createCell(2).setCellValue(this.rst.getReceberFgts());

        //Colunas da Linha 22
        linha22.createCell(0).setCellValue("Saldo FGTS");
        linha22.createCell(2).setCellValue(this.rst.getSaldoFgts());

        //Colunas da Linha 23
        linha23.createCell(0).setCellValue("Multa de 40%");
        linha23.createCell(2).setCellValue(this.rst.getVlrMulta());

        //Colunas da Linha 25
        linha25.createCell(0).setCellValue("Valor total");
        linha25.createCell(2).setCellValue(this.rst.getVlrTotFgts());

        //  ------------------------------------------------------------------------ //  

        /*         Aplicando os estilos nas clulas ------------------------------- */

        cell = header1.getCell(0);
        cell.setCellStyle(headerStyle);
        header1.setRowStyle(headerStyle);
        header1.setHeightInPoints(30);

        cell = header2.getCell(0);
        cell.setCellStyle(headerStyle);
        header2.setRowStyle(headerStyle);
        header2.setHeightInPoints(30);

        cell = header3.getCell(0);
        cell.setCellStyle(headerStyle);
        header3.setRowStyle(headerStyle);
        header3.setHeightInPoints(30);

        linha02.getCell(2).setCellStyle(cellCentered);

        linha03.getCell(2).setCellStyle(cellCentered);

        linha04.getCell(2).setCellStyle(cellCentered);

        linha05.getCell(2).setCellStyle(cellCentered);

        linha09.getCell(1).setCellStyle(cellCentered);
        linha09.getCell(2).setCellStyle(cellCentered);

        linha10.getCell(1).setCellStyle(cellCentered);
        linha10.getCell(2).setCellStyle(cellCentered);

        linha11.getCell(1).setCellStyle(cellCentered);
        linha11.getCell(2).setCellStyle(cellCentered);

        linha12.getCell(1).setCellStyle(cellCentered);
        linha12.getCell(2).setCellStyle(cellCentered);

        linha13.getCell(1).setCellStyle(cellCentered);
        linha13.getCell(2).setCellStyle(cellCentered);

        linha14.getCell(1).setCellStyle(cellCentered);
        linha14.getCell(2).setCellStyle(cellCentered);

        linha15.getCell(1).setCellStyle(cellCentered);
        linha15.getCell(2).setCellStyle(cellCentered);

        linha17.getCell(0).setCellStyle(cellFontBold);
        linha17.getCell(1).setCellStyle(cellCentered);
        linha17.getCell(2).setCellStyle(cellResulted);

        linha21.getCell(2).setCellStyle(cellCentered);

        linha22.getCell(2).setCellStyle(cellCentered);

        linha23.getCell(2).setCellStyle(cellCentered);

        linha25.getCell(0).setCellStyle(cellFontBold);
        linha25.getCell(2).setCellStyle(cellResulted);

        //  ------------------------------------------------------------------------ //           

        /*         Escrever, salvar e fechar o arquivo ---------------------------- */

        workbook.write(arquivo); // Escrevendo no arquivo.

        workbook.close(); // Salvando e fechando o arquivo.

        return true;

    } catch (Exception e) {

        System.err.println("No foi possvel gerar seu arquivo!" + " \n" + e.getLocalizedMessage());

        return false;

    }

}