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:bo.com.offercruzmail.utils.HojaExcelHelper.java

public Cell getCelda(int rowIndex, int colIndex) {
    Row fila = hoja.getRow(rowIndex);
    if (fila == null) {
        fila = hoja.createRow(rowIndex);
    }//from w w  w . ja v  a 2 s  .  c o  m

    Cell celda = fila.getCell(colIndex);
    if (celda == null) {
        celda = fila.createCell(colIndex);
    }

    return celda;

}

From source file:bouttime.fileinput.ExcelFileInputTest.java

License:Open Source License

private static void makeWrestler(Sheet s, int rownum, String fn, String ln, String c, String d, String wc,
        String aw, String tn, String l, String sn, String gn) {

    Row r = s.createRow(rownum);

    if (fn != null) {
        r.createCell(0).setCellValue(fn);
    }//from   w  w w  .j a  va2 s. c o m
    if (ln != null) {
        r.createCell(1).setCellValue(ln);
    }
    if (c != null) {
        r.createCell(2).setCellValue(c);
    }
    if (d != null) {
        r.createCell(3).setCellValue(d);
    }
    if (wc != null) {
        r.createCell(4).setCellValue(wc);
    }
    if (l != null) {
        r.createCell(5).setCellValue(l);
    }
    if (tn != null) {
        r.createCell(6).setCellValue(tn);
    }
    if (aw != null) {
        r.createCell(7).setCellValue(aw);
    }
    if (sn != null) {
        r.createCell(8).setCellValue(sn);
    }
    if (gn != null) {
        r.createCell(9).setCellValue(gn);
    }
}

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  . ja va  2  s  . c om*/
    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);//from ww w  .j  a v  a2  s.co 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.itfox.test.Excel.java

public void gerarExcel(boolean simple) {
    try {//from   w ww.j  av a 2  s. c om
        String ini = "01/01/2015";
        String fim = "22/06/2016";
        String seg = "'21','22','23'";
        String areaOper = "'47','24','23','29','4','18','5','48','10','31','43','35','36','7','33','45','3','32','9','39','13','38','16','44','30','15','2','17','12','6','42','41','34','40','1','19','14','26','22','51','46','49','27','25','8','50','52','28','11','20','37','21'";
        BusinessDelegate bd = new BusinessDelegate();
        String path = "/Users/belchiorpalma/Desktop/template/";
        String pathTemplate = "/Users/belchiorpalma/NetBeansProjects/Quest_Iveco/src/br/com/itfox/generator/";
        InputStream is = null;
        try {
            is = new FileInputStream(pathTemplate + "TemplateGic.xlsx");
        } catch (FileNotFoundException ex) {
            // Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        }
        //try(InputStream is = GeneratorObjectCollection.class.getResourceAsStream(pathTemplate+"TemplateGic.xlsx"))
        // {
        SimpleDateFormat sdf = new SimpleDateFormat("dd_M_yyyy_hh_mm_ss");
        String date = sdf.format(new Date());

        Workbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();

        ini = (Utils.dateFormat(ini));
        fim = (Utils.dateFormat(fim));

        List<Gic> gics = bd.selectGic(ini, fim, seg, areaOper);
        int i = 0;
        for (Gic g : gics) {
            Row row = sh.createRow(i);
            for (int cellnum = 0; cellnum < 153; cellnum++) {
                Cell cell = row.createCell(cellnum);
                cell.setCellValue(g.getC_nomeproprietario());
            }
            i++;
        }
        /*
        for (int rownum = 0; rownum < 1000000; rownum++) {
            Row row = sh.createRow(rownum);
            for (int cellnum = 0; cellnum < 2; cellnum++) {
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }
        }*/

        FileOutputStream out;
        try {
            out = new FileOutputStream(path + "object_collection_output.xlsx");
            wb.write(out);
            out.close();
        } catch (FileNotFoundException ex) {
            //Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        }

    } catch (IOException ex) {
        // Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
        ex.printStackTrace();
    }
}

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

License:Apache License

/**
 * mtodo cria bytes de documento Excel//ww w. ja v  a2 s  .  c  o  m
 * @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

public Workbook toWorkBook(Workbook wb, Table table) {
    List<List<TableCell>> matrix = table.getCells();
    List<List<TableCell>> matrixFull = table.toTableCellMatrix();

    replaceColorsPallete(table.getColorsReplaceMap(), wb);

    String sheetName = table.getTitle();
    Sheet sheet = sheetName == null ? wb.createSheet() : wb.createSheet(sheetName);
    int titleRows = 0;
    int r = titleRows;
    int c = 0;/* www .  j a  va2s . c o  m*/
    int maxColumns = -1;
    Map<Integer, Integer> defaultColumnWidth = new HashMap<>();

    ExporterFormatter tableExporterFormatter = table.getExporterFormatter();

    for (List<TableCell> row : matrix) {
        Row sheetRow = sheet.createRow(r);

        for (TableCell tableCell : row) {
            while (matrixFull.get(r - titleRows).get(c) == EmptyTableCell.EMPTY_CELL) {
                c++;
                if (c >= matrixFull.get(r - titleRows).size()) {
                    c = 0;
                    r++;
                }
            }

            Cell cell = sheetRow.createCell(c);
            if (c > maxColumns) {
                maxColumns = c;
            }

            if (tableCell.getRowspan() > 1 || tableCell.getColspan() > 1) {
                int rowStart = r;
                int rowEnd = rowStart + (tableCell.getRowspan() - 1);
                int colStart = c;
                int colEnd = colStart + (tableCell.getColspan() - 1);

                CellRangeAddress cellRange = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
                sheet.addMergedRegion(cellRange);

                RegionUtil.setBorderTop(BorderStyle.THIN, cellRange, sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cellRange, sheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN, cellRange, sheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cellRange, sheet);
            } else if (!table.isAutoSizeColumnSheet()) {
                Integer maxColumnWidth = defaultColumnWidth.get(c);
                if (maxColumnWidth == null) {
                    defaultColumnWidth.put(c, tableCell.getDefaultColumnWidth());
                } else {
                    int defaultWidth = tableCell.getDefaultColumnWidth();
                    if (defaultWidth > maxColumnWidth) {
                        defaultColumnWidth.put(c, defaultWidth);
                    }
                }
            }

            String format = setConvertedValue(cell, tableCell, tableExporterFormatter);
            setCellStyle(cell, tableCell, wb, format);
            c++;
        }
        r++;
        c = 0;
    }

    if (table.isAutoSizeColumnSheet()) {
        for (int i = 0; i <= maxColumns; ++i) {
            if (sheet instanceof SXSSFSheet) {
                ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
            }
            sheet.autoSizeColumn(i, true);
        }
    } else {
        for (int i = 0; i <= maxColumns; ++i) {
            if (defaultColumnWidth.get(i) == null) {
                if (sheet instanceof SXSSFSheet) {
                    ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
                }
                sheet.autoSizeColumn(i, true);
            } else {
                int width = table.getMinOrMaxOrActualCellWidth(defaultColumnWidth.get(i));
                sheet.setColumnWidth(i, width);
            }
        }
    }
    return wb;
}

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

License:LGPL

public Workbook toWorkBook(Workbook wb, Table table) {
    List<List<TableCell>> matrix = table.getCells();
    List<List<TableCell>> matrixFull = table.toTableCellMatrix();

    String sheetName = table.getTitle();
    Sheet sheet = sheetName == null ? wb.createSheet() : wb.createSheet(sheetName);
    int titleRows = 0;
    int r = titleRows;
    int c = 0;//from  ww  w .  j a  v a2s .  c o m
    int maxColumns = -1;
    Map<Integer, Integer> defaultColumnWidth = new HashMap<>();

    ExporterFormatter tableExporterFormatter = table.getExporterFormatter();

    for (List<TableCell> row : matrix) {
        Row sheetRow = sheet.createRow(r);

        for (TableCell tableCell : row) {
            while (matrixFull.get(r - titleRows).get(c) == EmptyTableCell.EMPTY_CELL) {
                c++;
                if (c >= matrixFull.get(r - titleRows).size()) {
                    c = 0;
                    r++;
                }
            }

            Cell cell = sheetRow.createCell(c);
            if (c > maxColumns) {
                maxColumns = c;
            }

            if (tableCell.getRowspan() > 1 || tableCell.getColspan() > 1) {
                int rowStart = r;
                int rowEnd = rowStart + (tableCell.getRowspan() - 1);
                int colStart = c;
                int colEnd = colStart + (tableCell.getColspan() - 1);

                CellRangeAddress cellRange = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
                sheet.addMergedRegion(cellRange);

                RegionUtil.setBorderTop(1, cellRange, sheet, wb);
                RegionUtil.setBorderRight(1, cellRange, sheet, wb);
                RegionUtil.setBorderBottom(1, cellRange, sheet, wb);
                RegionUtil.setBorderLeft(1, cellRange, sheet, wb);
            } else if (!table.isAutoSizeColumnSheet()) {
                Integer maxColumnWidth = defaultColumnWidth.get(c);
                if (maxColumnWidth == null) {
                    defaultColumnWidth.put(c, tableCell.getDefaultColumnWidth());
                } else {
                    int defaultWidth = tableCell.getDefaultColumnWidth();
                    if (defaultWidth > maxColumnWidth) {
                        defaultColumnWidth.put(c, defaultWidth);
                    }
                }
            }

            String format = setConvertedValue(cell, tableCell, tableExporterFormatter);
            setCellStyle(cell, tableCell, wb, format);
            c++;
        }
        r++;
        c = 0;
    }

    if (table.isAutoSizeColumnSheet()) {
        for (int i = 0; i <= maxColumns; ++i) {
            if (sheet instanceof SXSSFSheet) {
                ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
            } else {
                sheet.autoSizeColumn(i, true);
            }
        }
    } else {
        for (int i = 0; i <= maxColumns; ++i) {
            if (defaultColumnWidth.get(i) == null) {
                if (sheet instanceof SXSSFSheet) {
                    ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
                } else {
                    sheet.autoSizeColumn(i, true);
                }
            } else {
                sheet.setColumnWidth(i, defaultColumnWidth.get(i));
            }
        }
    }
    return wb;
}

From source file:br.sp.telesul.service.ExportServiceImpl.java

public void writeExcel(String templateHead, String[] columns, HSSFWorkbook workbook) {
    try {//  w w  w.j  a v a 2s  . c o m
        List<Funcionario> funcionarios = funcionarioService.search();

        HSSFSheet sheet = workbook.createSheet(templateHead);

        Row rowHeading = sheet.createRow(0);
        for (int i = 0; i < columns.length; i++) {
            rowHeading.createCell(i).setCellValue(columns[i]);
        }

        for (int i = 0; i < columns.length; i++) {
            CellStyle stylerowHeading = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            font.setFontName(HSSFFont.FONT_ARIAL);
            font.setFontHeightInPoints((short) 11);
            font.setColor(HSSFColor.WHITE.index);
            stylerowHeading.setFont(font);
            stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER);
            stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
            stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND);
            rowHeading.getCell(i).setCellStyle(stylerowHeading);
        }

        int r = 1;
        for (Funcionario f : funcionarios) {
            Row row = sheet.createRow(r);

            Cell Nome = row.createCell(0);
            Nome.setCellValue(f.getNome());
            Cell cargo = row.createCell(1);
            cargo.setCellValue(f.getCargo());

            Cell dtAdmissao = row.createCell(2);
            dtAdmissao.setCellValue(f.getDtAdmissao());

            CellStyle styleDate = workbook.createCellStyle();
            HSSFDataFormat dfAdmissao = workbook.createDataFormat();
            styleDate.setDataFormat(dfAdmissao.getFormat("dd/mm/yyyy"));
            dtAdmissao.setCellStyle(styleDate);

            Cell area = row.createCell(3);
            area.setCellValue(f.getArea());

            Cell gestor = row.createCell(4);
            gestor.setCellValue(f.getGestor());

            try {
                Cell email = row.createCell(5);
                email.setCellValue(f.getEmail());
            } catch (NullPointerException ne) {

            }
            try {
                Cell telefone = row.createCell(6);
                telefone.setCellValue(f.getTelefone());

            } catch (NullPointerException e) {

            }
            try {
                Cell celular = row.createCell(7);
                celular.setCellValue(f.getCelular());
            } catch (NullPointerException e) {

            }

            r++;
        }

        for (int i = 0; i < columns.length; i++) {
            sheet.autoSizeColumn(i);
        }

    } catch (Exception e) {
        logger.error("Error gerate Report: " + e);
        System.out.println("Error" + e);
    }
}

From source file:br.sp.telesul.service.ExportServiceImpl.java

public void writeExcelFormacao(String templateHead, String[] columns, HSSFWorkbook workbook) {
    try {//from   ww  w .j  av  a2 s  .c o m
        List<Funcionario> funcionarios = funcionarioService.search();

        HSSFSheet sheet = workbook.createSheet(templateHead);

        Row rowHeading = sheet.createRow(0);
        for (int i = 0; i < columns.length; i++) {
            rowHeading.createCell(i).setCellValue(columns[i]);
        }
        //Estilizar o Cabealho - Stylesheet the heading
        for (int i = 0; i < columns.length; i++) {
            CellStyle stylerowHeading = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            font.setFontName(HSSFFont.FONT_ARIAL);
            font.setFontHeightInPoints((short) 11);
            font.setColor(HSSFColor.WHITE.index);
            stylerowHeading.setFont(font);
            stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER);
            stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
            stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND);
            rowHeading.getCell(i).setCellStyle(stylerowHeading);
        }
        //Preencher linhas
        int r = 1;
        for (Funcionario f : funcionarios) {

            if (!f.getFormacoes().isEmpty()) {

                for (Formacao fmc : f.getFormacoes()) {
                    if (!fmc.getInstituicao().isEmpty() || !fmc.getCurso().isEmpty()
                            || !fmc.getNivel().isEmpty()) {
                        Row row = sheet.createRow(r);

                        try {
                            Cell Nome = row.createCell(0);
                            Nome.setCellValue(f.getNome());
                        } catch (NullPointerException e) {

                        }

                        try {
                            Cell curso = row.createCell(1);
                            curso.setCellValue(fmc.getCurso());
                        } catch (NullPointerException e) {

                        }
                        try {
                            Cell instituicao = row.createCell(2);
                            instituicao.setCellValue(fmc.getInstituicao());
                        } catch (NullPointerException e) {

                        }
                        try {
                            Cell nivel = row.createCell(3);
                            nivel.setCellValue(fmc.getNivel());
                        } catch (NullPointerException e) {

                        }
                        try {
                            Cell copia = row.createCell(4);
                            copia.setCellValue(fmc.getCopiaCertificado());
                        } catch (NullPointerException e) {

                        }

                        r++;
                    }
                }

            }

        }

        for (int i = 0; i < columns.length; i++) {
            sheet.autoSizeColumn(i);
        }

    } catch (Exception e) {
        System.out.println("Error " + e);
    }
}