Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createSheet

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet.

Prototype

@Override
public HSSFSheet createSheet(String sheetname)

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:bean.TrialBalanceExport.java

public void export() {
    Integer columnNo;/*  w w w.  j  av  a  2 s.  c  o m*/
    HSSFWorkbook workbook;
    HSSFSheet sheet;
    HSSFRow dataRow;
    HSSFCell cell;
    HSSFCellStyle cellStyle, boldStyle;
    HSSFFont font;

    ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay();

    getExportData().createFolder(null, themeDisplay, "Trial Balance Report", "DESCRIPTION");

    if (getExportData().getFilename() == null || getExportData().getFilename().length() == 0) {
        getExportData().setFilename("Default(" + new Date() + ")");
    }
    getExportData().setFilename(getExportData().getFilename().replace(":", ""));

    try {
        getExportData().setFilename(getExportData().getFilename().concat(".xls"));
        workbook = new HSSFWorkbook();

        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

        font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        boldStyle = workbook.createCellStyle();
        boldStyle.setFont(font);

        sheet = workbook
                .createSheet("Trial Balance Report " + getFinancialReportData().getCurrentMonth() != null
                        ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " "
                                + getFinancialReportData().getCurrentYear().toString()
                        : getFinancialReportData().getCurrentYear().toString());

        dataRow = sheet.createRow((short) 0);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("TRIAL BALANCE");
        cell.setCellStyle(boldStyle);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue(getFinancialReportData().getCurrentMonth() != null
                ? getDataConvert().monthConvert(getFinancialReportData().getCurrentMonth()) + " "
                        + getFinancialReportData().getCurrentYear().toString()
                : getFinancialReportData().getCurrentYear().toString());
        cell.setCellStyle(boldStyle);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);

        dataRow = sheet.createRow(dataRow.getRowNum() + 1);
        columnNo = 0;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Account Title");
        cell.setCellStyle(boldStyle);

        columnNo = 1;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Debit");
        cell.setCellStyle(boldStyle);

        columnNo = 2;
        cell = dataRow.createCell(columnNo++);
        cell.setCellValue("Credit");
        cell.setCellStyle(boldStyle);

        if (getTrialBalanceData().getType() == 1) {
            for (int i = 0; i < getTrialBalanceData().getCombotb().size(); i++) {
                for (int ii = 0; ii < getTrialBalanceData().getCombotb().get(i).size(); ii++) {
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    columnNo = 0;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellValue(getDataConvert()
                            .accountCodeConvert((String) getTrialBalanceData().getCombotb().get(i).get(ii)[0]));
                    cell.setCellStyle(cellStyle);
                    //                
                    dataRow.createCell(columnNo++).setCellValue("");

                    columnNo = 1;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(
                            ((BigDecimal) getTrialBalanceData().getCombotb().get(i).get(ii)[1]).doubleValue());
                    cell.setCellStyle(cellStyle);

                    columnNo = 2;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(
                            ((BigDecimal) getTrialBalanceData().getCombotb().get(i).get(ii)[2]).doubleValue());
                    cell.setCellStyle(cellStyle);
                }

                if (i == getTrialBalanceData().getCombotb().size() - 1) {
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    columnNo = 0;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellValue("TOTAL:");
                    cell.setCellStyle(boldStyle);

                    dataRow.createCell(columnNo++).setCellValue("");

                    columnNo = 1;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(((getTrialBalanceData().getComboDebitTotal()).doubleValue()));
                    cell.setCellStyle(boldStyle);
                    System.out.println("debit total "
                            + getDataConvert().convertAmount(getTrialBalanceData().getComboDebitTotal()));

                    columnNo = 2;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(((getTrialBalanceData().getComboCreditTotal()).doubleValue()));
                    cell.setCellStyle(boldStyle);
                    System.out.println("credit total "
                            + getDataConvert().convertAmount(getTrialBalanceData().getComboCreditTotal()));
                }
            }

        } else if (getTrialBalanceData().getType() == 2) {
            for (int i = 0; i < getTrialBalanceData().getTrialBalance().size(); i++) {
                dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                columnNo = 0;

                cell = dataRow.createCell(columnNo++);
                cell.setCellValue((String) getTrialBalanceData().getTrialBalance().get(i).getAcctTitle());
                cell.setCellStyle(cellStyle);
                //                
                dataRow.createCell(columnNo++).setCellValue("");

                columnNo = 1;

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getDataConvert()
                        .convertAmount((BigDecimal) getTrialBalanceData().getTrialBalance().get(i).getDebit()));
                cell.setCellStyle(cellStyle);

                columnNo = 2;

                cell = dataRow.createCell(columnNo++);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(getDataConvert().convertAmount(
                        (BigDecimal) getTrialBalanceData().getTrialBalance().get(i).getCredit()));
                cell.setCellStyle(cellStyle);

                if (i == getTrialBalanceData().getTrialBalance().size() - 1) {
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    dataRow = sheet.createRow(dataRow.getRowNum() + 1);
                    columnNo = 0;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellValue("TOTAL:");
                    cell.setCellStyle(boldStyle);

                    dataRow.createCell(columnNo++).setCellValue("");

                    columnNo = 1;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(
                            getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceDebitTotal()));
                    cell.setCellStyle(boldStyle);

                    columnNo = 2;

                    cell = dataRow.createCell(columnNo++);
                    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(
                            getDataConvert().convertAmount(getTrialBalanceData().getTrialBalanceCreditTotal()));
                    cell.setCellStyle(boldStyle);
                }
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename());
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        getExportData().fileUploadByDL(getExportData().getFilename(), "Trial Balance Report", themeDisplay,
                null);

        File file = new File(getExportData().getFilename());
        if (file.exists()) {
            file.delete();
        }
    } catch (Exception e) {
        System.out.print("trialBalanceExport().export() " + e);
        FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error",
                "An error occurred while generating excel file.");
        FacesContext.getCurrentInstance().addMessage(null, message);
    }
}

From source file:binky.reportrunner.engine.renderers.exporters.XLSExporter.java

License:Open Source License

@SuppressWarnings("deprecation")
@Override//from   www  . j  a v a 2  s . c o m
public void export(ResultSet resultSet, String label, OutputStream outputStream) throws ExportException {
    try {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Report");
        ResultSetMetaData metaData;

        metaData = resultSet.getMetaData();

        short rowCount = 0;
        // logger.debug("writing header");
        HSSFRow headerRow = sheet.createRow(rowCount);
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            //TODO:fix
            HSSFCell cell = headerRow.createCell((short) (i - 1));
            HSSFRichTextString string = new HSSFRichTextString(metaData.getColumnName(i));
            string.applyFont(HSSFFont.BOLDWEIGHT_BOLD);
            cell.setCellValue(string);
        }

        while (resultSet.next()) {
            rowCount++;
            HSSFRow row = sheet.createRow(rowCount);
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                //TODO:fix
                HSSFCell cell = row.createCell((short) (i - 1));

                // TODO:make this better by using types
                HSSFRichTextString string = new HSSFRichTextString("" + resultSet.getObject(i));
                cell.setCellValue(string);

            }
        }

        // Write the output to the stream file
        wb.write(outputStream);
        outputStream.flush();

    } catch (SQLException e) {
        throw new ExportException(e.getMessage(), e);
    } catch (IOException e) {
        throw new ExportException(e.getMessage(), e);
    }
}

From source file:br.com.hslife.orcamento.controller.LancamentoContaController.java

License:Open Source License

@SuppressWarnings("resource")
public void exportarLancamentos() {
    if (listEntity == null || listEntity.isEmpty()) {
        warnMessage("Listagem vazio. Nada a exportar.");
    }/*from w w  w  .j ava  2  s.c o m*/

    try {

        HSSFWorkbook excel = new HSSFWorkbook();
        HSSFSheet planilha = excel.createSheet("lancamentoConta");

        HSSFRow linha = planilha.createRow(0);

        HSSFCell celula = linha.createCell(0);
        celula.setCellValue("Data");
        celula = linha.createCell(1);
        celula.setCellValue("Histrico");
        celula = linha.createCell(2);
        celula.setCellValue("Valor");

        int linhaIndex = 1;
        for (LancamentoConta l : listEntity) {
            linha = planilha.createRow(linhaIndex);

            celula = linha.createCell(0);
            celula.setCellValue(Util.formataDataHora(l.getDataPagamento(), Util.DATA));

            celula = linha.createCell(1);
            celula.setCellValue(l.getDescricao());

            celula = linha.createCell(2);
            celula.setCellValue(l.getValorPago());

            linhaIndex++;
        }

        HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance()
                .getExternalContext().getResponse();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=lancamentoConta.xls");
        response.setContentLength(excel.getBytes().length);
        ServletOutputStream output = response.getOutputStream();
        output.write(excel.getBytes(), 0, excel.getBytes().length);
        FacesContext.getCurrentInstance().responseComplete();

    } catch (IOException e) {
        errorMessage(e.getMessage());
    }
}

From source file:br.com.muranodesign.resources.AlunoVariavelResource.java

License:Creative Commons License

/**
 * Gerar relatrio de aluno variavel/*from www .ja  va 2 s  .c o m*/
 * @param Tutoria
 * @param Ano
 * @param Periodo
 * @param Nome
 * @param Sexo
 * @param Datanascimento
 * @param Endereco
 * @param TelefoneResidencial
 * @param TelefoneCelular
 * @param email
 * @param NomeResponsavel
 * @param ParentescoResponsavel
 * @param TelefoneResidencialResponsavel
 * @param TelefoneCelularResponsavel
 * @param TelefoneComercialResponsavel
 * @param emailResponsavel
 * @param NomeMae
 * @param EnderecoMae
 * @param TelefoneCelularMae
 * @param TelefoneResidencialMae
 * @param TelefoneComercialMae
 * @param emailMae
 * @return list
 */
@Path("Relatorio")
@POST
@Produces("application/json")
public List<String> relatorio(@FormParam("Tutoria") int Tutoria, @FormParam("Ano") int Ano,
        @FormParam("Periodo") int Periodo, @FormParam("Neces") String Neces, @FormParam("Prog") String Prog,
        @FormParam("Status") String Status,

        @FormParam("tutoria") String tut, @FormParam("Nome") String Nome, @FormParam("Sexo") String Sexo,
        @FormParam("Datanascimento") String Datanascimento, @FormParam("Endereco") String Endereco,
        @FormParam("TelefoneResidencial") String TelefoneResidencial,
        @FormParam("TelefoneCelular") String TelefoneCelular, @FormParam("email") String email,
        @FormParam("NomeResponsavel") String NomeResponsavel,
        @FormParam("ParentescoResponsavel") String ParentescoResponsavel,
        @FormParam("TelefoneResidencialResponsavel") String TelefoneResidencialResponsavel,
        @FormParam("TelefoneCelularResponsavel") String TelefoneCelularResponsavel,
        @FormParam("TelefoneComercialResponsavel") String TelefoneComercialResponsavel,
        @FormParam("emailResponsavel") String emailResponsavel, @FormParam("NomeMae") String NomeMae,
        @FormParam("EnderecoMae") String EnderecoMae,
        @FormParam("TelefoneCelularMae") String TelefoneCelularMae,
        @FormParam("TelefoneResidencialMae") String TelefoneResidencialMae,
        @FormParam("TelefoneComercialMae") String TelefoneComercialMae,
        @FormParam("emailMae") String emailMae) {

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet firstSheet = workbook.createSheet("Relatorio Aluno");

    FileOutputStream fos = null;

    String nomeArquivo;

    try {

        nomeArquivo = new StringUtil().geraNomeAleatorio("xls", 15);
        fos = new FileOutputStream(new File("/home/tomcat/webapps/files/" + nomeArquivo));
        //fos = new FileOutputStream(new File("C:/Users/murano/Desktop/Teste/" + nomeArquivo));      

        HSSFRow row = firstSheet.createRow(0);

        List<String> retorno = new ArrayList<String>();
        List<List<String>> listRetorno = new ArrayList<List<String>>();

        int h = 0;
        row = firstSheet.createRow(h);
        row.createCell((short) 0).setCellValue("Tutoria");
        row.createCell((short) 1).setCellValue("Nome");
        row.createCell((short) 2).setCellValue("Sexo");
        row.createCell((short) 3).setCellValue("Datanascimento");
        row.createCell((short) 4).setCellValue("Endereco");
        row.createCell((short) 5).setCellValue("TelefoneResidencial");
        row.createCell((short) 6).setCellValue("TelefoneCelular");
        row.createCell((short) 7).setCellValue("email");
        row.createCell((short) 8).setCellValue("NomeResponsavel");
        row.createCell((short) 9).setCellValue("ParentescoResponsavel");
        row.createCell((short) 10).setCellValue("TelefoneResidencialResponsavel");
        row.createCell((short) 11).setCellValue("TelefoneCelularResponsavel");
        row.createCell((short) 12).setCellValue("TelefoneComercialResponsavel");
        row.createCell((short) 13).setCellValue("emailResponsavel");
        row.createCell((short) 14).setCellValue("NomeMae");
        row.createCell((short) 15).setCellValue("EnderecoMae");
        row.createCell((short) 16).setCellValue("TelefoneCelularMae");
        row.createCell((short) 17).setCellValue("TelefoneResidencialMae");
        row.createCell((short) 18).setCellValue("TelefoneComercialMae");
        row.createCell((short) 19).setCellValue("emailMae");

        List<AlunoVariavel> list = new AlunoVariavelService().listarRelatorioSecretaria(Tutoria, Ano, Periodo,
                Neces, Prog, Status);
        for (AlunoVariavel alunoVariavel : list) {
            h++;
            row = firstSheet.createRow(h);
            //row.createCell((short) 0).setCellValue("Relatrio");

            if (tut != "" && tut != null) {

                String valor = alunoVariavel.getGrupo().getTutoria().getTutoria();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 0).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }
            }

            if (Nome != "" && Nome != null) {

                String valor = alunoVariavel.getAluno().getNome();

                if (valor != null && valor != "-") {
                    retorno.add(valor);
                    row.createCell((short) 1).setCellValue(valor);

                } else {
                    retorno.add("no informado");
                }

            }
            if (Sexo != "" && Sexo != null) {

                String valor = alunoVariavel.getAluno().getSexo();

                if (valor != null && valor != "-") {
                    retorno.add(valor);
                    row.createCell((short) 2).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (Datanascimento != "" && Datanascimento != null) {

                Date valor = alunoVariavel.getAluno().getDataNascimento();

                if (valor != null) {

                    DateFormat formatter = new SimpleDateFormat("dd-MM-yy");

                    String dataS = formatter.format(valor);

                    //Date data = (Date) formatter.parse(dataS);

                    retorno.add(dataS);

                    row.createCell((short) 3).setCellValue(valor);
                } else {
                    retorno.add(null);
                }

            }
            if (Endereco != "" && Endereco != null) {

                String valor = alunoVariavel.getAluno().getEndereco();

                if (valor != null && valor != "-") {
                    retorno.add(valor);
                    row.createCell((short) 4).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (TelefoneResidencial != "" && TelefoneResidencial != null) {

                String valor = alunoVariavel.getAluno().getTelefoneResidencialResponsavel();

                if (valor != null && valor != "-") {
                    retorno.add(valor);
                    row.createCell((short) 5).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (TelefoneCelular != "" && TelefoneCelular != null) {

                String valor = alunoVariavel.getAluno().getTelefoneCelularResponsavel();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 6).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (email != "" && email != null) {

                String valor = alunoVariavel.getAluno().getEmail();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 7).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (NomeResponsavel != "" && NomeResponsavel != null) {

                String valor = alunoVariavel.getAluno().getNomeResponsavel();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 8).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (ParentescoResponsavel != "" && ParentescoResponsavel != null) {

                String valor = alunoVariavel.getAluno().getParentescoResponsavel();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 9).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (TelefoneResidencialResponsavel != "" && TelefoneResidencialResponsavel != null) {

                String valor = alunoVariavel.getAluno().getTelefoneResidencialResponsavel();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 10).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (TelefoneCelularResponsavel != "" && TelefoneCelularResponsavel != null) {

                String valor = alunoVariavel.getAluno().getTelefoneCelularResponsavel();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 11).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (TelefoneComercialResponsavel != "" && TelefoneComercialResponsavel != null) {

                String valor = alunoVariavel.getAluno().getTelefoneComercialResponsavel();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 12).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (emailResponsavel != "" && emailResponsavel != null) {

                String valor = alunoVariavel.getAluno().getEmail1Responsavel();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 13).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (NomeMae != "" && NomeMae != null) {

                String valor = alunoVariavel.getAluno().getNomeMae();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 14).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (EnderecoMae != "" && EnderecoMae != null) {

                String valor = alunoVariavel.getAluno().getEnderecoMae();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 15).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (TelefoneCelularMae != "" && TelefoneCelularMae != null) {

                String valor = alunoVariavel.getAluno().getTelefoneCelularMae();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 16).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (TelefoneResidencialMae != "" && TelefoneResidencialMae != null) {

                String valor = alunoVariavel.getAluno().getTelefoneResidencialMae();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 17).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (TelefoneComercialMae != "" && TelefoneComercialMae != null) {

                String valor = alunoVariavel.getAluno().getTelefoneComercialMae();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 18).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }

            }
            if (emailMae != "" && emailMae != null) {

                String valor = alunoVariavel.getAluno().getEmail1Mae();

                if (valor != null && valor != "-") {
                    retorno.add(valor);

                    row.createCell((short) 19).setCellValue(valor);
                } else {
                    retorno.add("no informado");
                }
            }

            listRetorno.add(retorno);
        }

        if (listRetorno.isEmpty()) {
            retorno.add("no h valores");
            listRetorno.add(retorno);
        }

        workbook.write(fos);

        retorno.add("http://177.55.99.90/files/" + nomeArquivo);
        //retorno.add("http://172.16.31.178/"+nomeArquivo);
        listRetorno.add(retorno);
        return listRetorno.get(0);

    } catch (Exception e) {
        e.printStackTrace();
        logger.info("Erro ao exportar arquivo");

    } finally {
        try {
            fos.flush();
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    return null;

}

From source file:br.com.pontocontrol.controleponto.controller.impl.ExportadorXLSController.java

@Override
public boolean extrair(FolhaMensalPonto folhaMensal, String outputFileDir) {
    Calendar data = Calendar.getInstance();
    data.set(Calendar.MONTH, folhaMensal.getMes());
    data.set(Calendar.YEAR, folhaMensal.getAno());
    final String nomeArquivo = format("%s-%s_%s.xls",
            SessaoManager.getInstance().getUsuarioAutenticado().getLogin(),
            formatDate("yyyy-MMMM", data.getTime()), formatDate("yyyyMMddHHmmssSSS", new Date()));
    final String filePath = format("%s/%s", outputFileDir, nomeArquivo);
    File arquivoXLS = new File(filePath);
    FileOutputStream fos = null;//  w ww  . ja  v  a2  s  .c  o m
    try {
        arquivoXLS.createNewFile();

        fos = new FileOutputStream(arquivoXLS);

        HSSFWorkbook workbook = HSSFWorkbook.create(InternalWorkbook.createWorkbook());

        final String nomePlanilha = formatDate("MMM-yy", data.getTime());
        HSSFSheet planilha = workbook.createSheet(nomePlanilha);

        //TOTAL ROW
        HSSFRow totalRow = planilha.createRow(0);
        totalRow.createCell(CELL_INDEX.TOTAL_ROW.TOTAL_TXT).setCellValue("Total:");
        totalRow.createCell(CELL_INDEX.TOTAL_ROW.TOTAL_VALUE).setCellValue(folhaMensal.calcularTotalMensal());
        totalRow.createCell(CELL_INDEX.TOTAL_ROW.VAR_TXT).setCellValue("Variao:");
        totalRow.createCell(CELL_INDEX.TOTAL_ROW.VAR_VALUE).setCellValue(folhaMensal.calcularVariacaoMensal());

        //HEADER
        HSSFRow headerRow = planilha.createRow(1);
        headerRow.createCell(CELL_INDEX.DIA).setCellValue("Dia");
        headerRow.createCell(CELL_INDEX.ENTRADA).setCellValue("Entrada");
        headerRow.createCell(CELL_INDEX.ALMOCO).setCellValue("Almoo");
        headerRow.createCell(CELL_INDEX.RETORNO).setCellValue("Retorno");
        headerRow.createCell(CELL_INDEX.SAIDA).setCellValue("Sada");
        headerRow.createCell(CELL_INDEX.TOTAL_EXP).setCellValue("Expediente");
        headerRow.createCell(CELL_INDEX.VARIACAO).setCellValue("Variao");

        formatHeaderRow(workbook, headerRow);
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.YEAR, folhaMensal.getAno());
        cal.set(Calendar.MONTH, folhaMensal.getMes());

        for (int dia = 1; dia <= cal.getActualMaximum(Calendar.DAY_OF_MONTH); dia++) {
            int i = planilha.getPhysicalNumberOfRows();
            HSSFRow row = planilha.createRow(i);
            cal.set(Calendar.DAY_OF_MONTH, dia);
            row.createCell(CELL_INDEX.DIA).setCellValue(formatDate("MMM dd, EEE", cal.getTime()));

            RegistroDiarioPonto reg = folhaMensal.getRegistros().get(dia);
            if (reg != null) {
                row.createCell(CELL_INDEX.ENTRADA)
                        .setCellValue(reg.getEntrada() != null ? reg.getEntrada().format(TIME_FORMATTER) : "");
                row.createCell(CELL_INDEX.ALMOCO)
                        .setCellValue(reg.getAlmoco() != null ? reg.getAlmoco().format(TIME_FORMATTER) : "");
                row.createCell(CELL_INDEX.RETORNO)
                        .setCellValue(reg.getRetorno() != null ? reg.getRetorno().format(TIME_FORMATTER) : "");
                row.createCell(CELL_INDEX.SAIDA)
                        .setCellValue(reg.getSaida() != null ? reg.getSaida().format(TIME_FORMATTER) : "");
                row.createCell(CELL_INDEX.TOTAL_EXP).setCellValue(
                        reg.isRegistroDiarioCompleto() ? reg.calcularTotalExpedienteAsNumber() : 0);
                row.createCell(CELL_INDEX.VARIACAO)
                        .setCellValue(reg.isRegistroDiarioCompleto() ? reg.calcularVariacaoExpediente() : 0);
            } else {
                row.createCell(CELL_INDEX.ENTRADA).setCellValue("");
                row.createCell(CELL_INDEX.ALMOCO).setCellValue("");
                row.createCell(CELL_INDEX.RETORNO).setCellValue("");
                row.createCell(CELL_INDEX.SAIDA).setCellValue("");
                row.createCell(CELL_INDEX.TOTAL_EXP).setCellValue("");
                row.createCell(CELL_INDEX.VARIACAO).setCellValue("");
            }
            if (SessaoManager.getInstance().getUsuarioAutenticado().checarSeDiaExpediente(cal)) {
                formatRow(workbook, row);
            } else {
                formatHeaderRow(workbook, row);
            }
        }
        for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
            planilha.autoSizeColumn(i);
        }
        workbook.write(fos);
        fos.flush();
        return true;
    } catch (IOException ex) {
        LOG.log(Level.SEVERE, "Erro ao criar arquivo XLS de sada", ex);
        return false;
    } finally {
        IOUtils.closeQuietly(fos);
    }
}

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

public void writeExcel(String templateHead, String[] columns, HSSFWorkbook workbook) {
    try {// w ww . jav  a2s.c om
        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  a  v  a  2 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);
    }
}

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

public void writeExcelCertificacoes(String templateHead, String[] columns, HSSFWorkbook workbook) {
    try {//from   ww  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]);
        }
        //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.getCertificacoes().isEmpty()) {

                for (Certificacao ct : f.getCertificacoes()) {
                    Row row = sheet.createRow(r);

                    CellStyle styleDate = workbook.createCellStyle();
                    HSSFDataFormat dfExame = workbook.createDataFormat();
                    styleDate.setDataFormat(dfExame.getFormat("dd/mm/yyyy"));
                    try {
                        Cell Nome = row.createCell(0);
                        Nome.setCellValue(f.getNome());

                    } catch (NullPointerException e) {

                    }
                    try {
                        Cell cod = row.createCell(1);
                        cod.setCellValue(ct.getCodigo());
                    } catch (NullPointerException e) {

                    }
                    try {
                        Cell nome = row.createCell(3);
                        nome.setCellValue(ct.getNome());
                    } catch (NullPointerException e) {

                    }
                    try {
                        Cell empresa = row.createCell(2);
                        empresa.setCellValue(ct.getEmpresa());
                    } catch (NullPointerException e) {

                    }
                    try {
                        Cell dtExame = row.createCell(4);
                        dtExame.setCellValue(ct.getDtExame());
                        dtExame.setCellStyle(styleDate);
                    } catch (NullPointerException e) {

                    }
                    try {
                        Cell dtValidade = row.createCell(5);
                        dtValidade.setCellValue(ct.getDtValidade());
                        dtValidade.setCellStyle(styleDate);
                    } catch (NullPointerException e) {

                    }
                    try {

                        Cell copia = row.createCell(6);
                        copia.setCellValue(ct.getCopia());
                    } catch (NullPointerException e) {

                    }

                    r++;
                }

            }

        }

        for (int i = 0; i < columns.length; i++) {
            sheet.autoSizeColumn(i);
        }
        //            String file = "C:/Users/ebranco.TELESULCORP/new.xls";
        //            FileOutputStream out = new FileOutputStream(file);
        //            workbook.write(out);
        //            out.close();
        //            workbook.close();
        //            System.out.println("Excell write succesfully");
    } catch (Exception e) {
        System.out.println("Error" + e);
    }
}

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

public void writeExcelIdiomas(String templateHead, String[] columns, HSSFWorkbook workbook) {
    try {/*from  ww w  .ja  va  2  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.getIdiomas().isEmpty()) {

                for (Idioma idm : f.getIdiomas()) {
                    try {
                        if (idm.getNivel() != null || idm.getNome() != null) {
                            Row row = sheet.createRow(r);
                            Cell Nome = row.createCell(0);
                            Nome.setCellValue(f.getNome());

                            Cell language = row.createCell(1);
                            language.setCellValue(idm.getNome().toString());

                            Cell nivel = row.createCell(2);
                            nivel.setCellValue(idm.getNivel().toString());
                            r++;
                        }
                    } catch (NullPointerException ne) {
                        System.out.println("Error " + ne);
                        break;
                    }

                }

            }

        }

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

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

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

public void writeExcelSingle(Long id, HSSFWorkbook workbook) {
    String[] columns = { "Nome", "Cargo", "Data de Admissao", "?rea", "Gestor", "Email", "Telefone",
            "Celular" };
    String[] colFormacao = { "Formao", "Curso", "Instituio", "Cpia de Certificao" };
    String[] colIdioma = { "Idioma", "Nvel" };
    String[] colCertificacao = { "Certificadora", "Exame", "Cdigo", "Data de Exame", "Data de Validade",
            "Cpia de Certificado" };
    try {//from  w  w  w. j  a v  a2  s  .  c  om
        Funcionario funcionario = this.funcionarioService.searchById(id);

        HSSFSheet sheet = workbook.createSheet("Funcionrio");

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

        int r = 1;

        Row row = sheet.createRow(r);

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

        Cell dtAdmissao = row.createCell(2);
        dtAdmissao.setCellValue(funcionario.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(funcionario.getArea());

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

        try {
            Cell email = row.createCell(5);
            email.setCellValue(funcionario.getEmail());

        } catch (NullPointerException ne) {

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

        } catch (NullPointerException ne) {

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

        }

        int auxRow = 12;
        for (Formacao form : funcionario.getFormacoes()) {
            if (!form.getCurso().isEmpty() || !form.getNivel().isEmpty() || !form.getInstituicao().isEmpty()) {
                int headerFormacao = 11;
                Row rowHeadingForm = sheet.createRow(headerFormacao);
                for (int i = 0; i < colFormacao.length; i++) {
                    rowHeadingForm.createCell(i).setCellValue(colFormacao[i]);
                }
                stylizeHeader(rowHeadingForm, workbook, colFormacao);
                int rowFormacao = 12;
                Row rowFormacaoDatas = sheet.createRow(rowFormacao);
                Cell formacao = rowFormacaoDatas.createCell(0);
                Cell curso = rowFormacaoDatas.createCell(1);
                Cell instituicao = rowFormacaoDatas.createCell(2);
                Cell copy = rowFormacaoDatas.createCell(3);
                for (Formacao f : funcionario.getFormacoes()) {
                    try {
                        formacao.setCellValue(f.getNivel());
                    } catch (NullPointerException e) {

                    }
                    try {
                        curso.setCellValue(f.getCurso());
                    } catch (NullPointerException e) {

                    }
                    try {
                        instituicao.setCellValue(f.getInstituicao());
                    } catch (NullPointerException e) {

                    }
                    try {
                        copy.setCellValue(f.getCopiaCertificado());
                    } catch (NullPointerException e) {

                    }

                    rowFormacao++;
                    auxRow = rowFormacao;
                }
                autoSizeColum(sheet, colFormacao);
            }
        }
        for (Idioma i : funcionario.getIdiomas()) {
            try {
                if (!i.getNome().toString().isEmpty() && !i.getNivel().toString().isEmpty()) {
                    int headerIdiomas = auxRow + 9;
                    Row rowHeadingIdioma = sheet.createRow(headerIdiomas);
                    for (int j = 0; j < colIdioma.length; j++) {
                        rowHeadingIdioma.createCell(j).setCellValue(colIdioma[j]);
                    }
                    stylizeHeader(rowHeadingIdioma, workbook, colIdioma);
                    int rowIdioma = headerIdiomas + 1;
                    Row rowIdiomasDatas = sheet.createRow(rowIdioma);
                    Cell nivelIdm = rowIdiomasDatas.createCell(0);
                    Cell language = rowIdiomasDatas.createCell(1);
                    for (Idioma j : funcionario.getIdiomas()) {
                        nivelIdm.setCellValue(j.getNivel().toString());
                        language.setCellValue(j.getNome().toString());
                        rowIdioma++;
                        auxRow = rowIdioma;
                    }
                    autoSizeColum(sheet, colIdioma);
                }
            } catch (NullPointerException ne) {
                logger.error("Idiomas" + ne);
                break;
            }

        }

        if (funcionario.getCertificacoes().size() > 0) {
            int headerCertificacao = auxRow + 9;
            Row rowHeadingCert = sheet.createRow(headerCertificacao);
            for (int j = 0; j < colCertificacao.length; j++) {
                rowHeadingCert.createCell(j).setCellValue(colCertificacao[j]);
            }
            stylizeHeader(rowHeadingCert, workbook, colCertificacao);
            int rowCert = headerCertificacao + 1;

            for (Certificacao c : funcionario.getCertificacoes()) {
                Row rowCertDatas = sheet.createRow(rowCert);
                Cell certificadora = rowCertDatas.createCell(0);
                Cell exame = rowCertDatas.createCell(1);
                Cell codigo = rowCertDatas.createCell(2);
                Cell dtExame = rowCertDatas.createCell(3);
                dtExame.setCellStyle(styleDate);
                Cell dtValidade = rowCertDatas.createCell(4);
                dtValidade.setCellStyle(styleDate);
                Cell copia = rowCertDatas.createCell(5);
                try {
                    certificadora.setCellValue(c.getEmpresa());
                } catch (NullPointerException e) {

                }
                try {
                    exame.setCellValue(c.getNome());
                } catch (NullPointerException e) {

                }
                try {
                    codigo.setCellValue(c.getCodigo());
                } catch (NullPointerException e) {

                }
                try {
                    dtExame.setCellValue(c.getDtExame());
                } catch (NullPointerException e) {

                }
                try {
                    dtValidade.setCellValue(c.getDtValidade());
                } catch (NullPointerException e) {

                }
                try {
                    copia.setCellValue(c.getCopia());
                } catch (NullPointerException e) {

                }

                rowCert++;
            }
            autoSizeColum(sheet, colCertificacao);
        }

        //r++;
    } catch (Exception e) {
        logger.error("Error Writing Single Report: " + e);
        System.out.println("rror Writing Single Report: " + e);
    }
}