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:br.sp.telesul.service.ExportServiceImpl.java

public void writeExcel(String templateHead, String[] columns, HSSFWorkbook workbook) {
    try {//from w  w  w  .j a  va 2s .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 writeExcelCertificacoes(String templateHead, String[] columns, HSSFWorkbook workbook) {
    try {/*from w ww . j av 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 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 {/*w ww.j  a  v a  2s  . c  o m*/
        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);
    }
}

From source file:br.ufpa.psi.comportamente.labgame.mbeans.RelatoriosMB.java

License:Open Source License

public StreamedContent geraRelatorioJogadasExperimento()
        throws ParsePropertyException, IOException, InvalidFormatException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Relatrio das Jogadas");

    sheet.setColumnWidth(sheet.getFirstRowNum(), (14 * 256) + 200);
    sheet.setColumnWidth(1, (14 * 256) + 200);
    sheet.setColumnWidth(4, (17 * 256) + 200);
    sheet.setColumnWidth(5, (16 * 256) + 200);

    HSSFCellStyle cs1 = workbook.createCellStyle();
    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("d-mmm-yy"));

    HSSFCellStyle cs2 = workbook.createCellStyle();
    cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));

    JogadaDAO jogadaDAO = new JogadaDAO();
    jogadaDAO.beginTransaction();/*from   ww w . j a va  2s .co  m*/
    List<Jogada> jogadas = jogadaDAO.encontrarPorExperimento(experimentoSelecionado.getId());

    int countRow = 0;
    Row row1 = sheet.createRow(countRow++);
    Cell cell = row1.createCell(0);
    cell.setCellValue("Experimento: " + experimentoSelecionado.getNome());

    Row row = sheet.createRow(countRow++);

    row.createCell(0).setCellValue("Data da Jogada");
    row.createCell(1).setCellValue("Hora da Jogada");
    row.createCell(2).setCellValue("Coluna");
    row.createCell(3).setCellValue("Linha");
    row.createCell(4).setCellValue("Pontuacao Individual");
    row.createCell(5).setCellValue("Pontuacao Coletiva");
    row.createCell(6).setCellValue("Participante");
    row.createCell(7).setCellValue("Condio");

    for (Jogada jogada : jogadas) {

        Row nrow = sheet.createRow(countRow++);

        //Data
        Cell ncell0 = nrow.createCell(0);
        ncell0.setCellValue(jogada.getMomento());
        ncell0.setCellStyle(cs1);

        //Hora
        Cell ncell1 = nrow.createCell(1);
        ncell1.setCellValue(jogada.getMomento());
        ncell1.setCellStyle(cs2);

        //Coluna
        Cell ncell2 = nrow.createCell(2);
        ncell2.setCellValue(jogada.getColunaSelecionada());

        //Linha
        Cell ncell3 = nrow.createCell(3);
        ncell3.setCellValue(jogada.getLinhaSelecionada());

        //Pontuao Individual
        Cell ncell4 = nrow.createCell(4);
        ncell4.setCellValue(jogada.getPontuacaoIndividual());

        //Pontuao Coletiva
        Cell ncell5 = nrow.createCell(5);
        ncell5.setCellValue(jogada.getPontuacaoCultural());

        //Jogador
        Cell ncell6 = nrow.createCell(6);
        ncell6.setCellValue(jogada.getJogador().getNome());

        //Id da Condio
        Cell ncell7 = nrow.createCell(7);
        ncell7.setCellValue(jogada.getIdCondicao());

    }

    jogadaDAO.stopOperation(false);

    byte[] bytes;
    try (ByteArrayOutputStream out = new ByteArrayOutputStream()) {
        workbook.write(out);
        bytes = out.toByteArray();
    }

    InputStream ioStream = new ByteArrayInputStream(bytes);
    file = new DefaultStreamedContent(ioStream, "application/vnd.ms-excel", "Relatrio_Jogadas.xls");
    return file;
}

From source file:Business.ExcelReportCreator.java

public static int create(EcoSystem system) {

    //          Steps:-
    //          Create a Workbook.
    //          Create a Sheet.
    //          Repeat the following steps until all data is processed:
    //          Create a Row.
    //          Create Cells in a Row. Apply formatting using CellStyle.
    //          Write to an OutputStream.
    //          Close the output stream.

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Customer Details");

    //Custom font style for header
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    Font font = sheet.getWorkbook().createFont();
    font.setBold(true);/*from w  ww. j ava 2s  .  c o m*/
    cellStyle.setFont(font);

    int rowCount = 0;
    int columnCount1 = 0;

    //Creating header row

    String s[] = { "CUSTOMER ID", "CUSTOMER NAME", "CONTACT NO.", "EMAIL ID", "USAGE(Gallons)", "BILLING DATE",
            "TOTAL BILL($)" };
    Row row1 = sheet.createRow(++rowCount);
    for (String s1 : s) {
        Cell header = row1.createCell(++columnCount1);
        header.setCellValue(s1);
        header.setCellStyle(cellStyle);
    }

    for (Network network : system.getNetworkList()) {
        for (Enterprise enterprise : network.getEnterpriseDirectory().getEnterpriseList()) {
            if (enterprise instanceof WaterEnterprise) {
                for (Organization organization : enterprise.getOrganizationDirectory().getOrganizationList()) {
                    if (organization instanceof CustomerOrganization) {
                        for (Employee employee : organization.getEmployeeDirectory().getEmployeeList()) {
                            Customer customer = (Customer) employee;
                            Row row = sheet.createRow(++rowCount);
                            int columnCount = 0;
                            for (int i = 0; i < 7; i++) {
                                Cell cell = row.createCell(++columnCount);
                                if (i == 0) {
                                    cell.setCellValue(customer.getId());
                                } else if (i == 1) {
                                    cell.setCellValue(customer.getName());
                                } else if (i == 2) {
                                    cell.setCellValue(customer.getContactNo());
                                } else if (i == 3) {
                                    cell.setCellValue(customer.getEmailId());
                                } else if (i == 4) {
                                    cell.setCellValue(customer.getTotalUsageVolume());
                                } else if (i == 5) {
                                    if (customer.getBillingDate() != null)
                                        cell.setCellValue(String.valueOf(customer.getBillingDate()));
                                    else
                                        cell.setCellValue("Bill Not yet available");
                                } else if (i == 6) {
                                    if (customer.getTotalBill() != 0)
                                        cell.setCellValue(customer.getTotalBill());
                                    else
                                        cell.setCellValue("Bill Not yet available");
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    try (FileOutputStream outputStream = new FileOutputStream("Customer_details.xlsx")) {
        workbook.write(outputStream);
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "File not found");
        return 0;
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null, "IOException");
        return 0;
    }
    return 1;
}

From source file:campmanager.CampUI.java

public void exportToExcel() {
    JFrame parentFrame = new JFrame();
    File fileToSave = null;/*  w w  w . ja v  a 2  s  . co m*/
    ;
    JFileChooser fileChooser = new JFileChooser();
    fileChooser.setDialogTitle("Export to Excel");

    int userSelection = fileChooser.showSaveDialog(parentFrame);

    if (userSelection == JFileChooser.APPROVE_OPTION) {
        fileToSave = fileChooser.getSelectedFile();
        // System.out.println("Save as file: " + fileToSave.getAbsolutePath());
    }
    if (fileToSave != null) {

        String fileName = fileToSave.getAbsolutePath();
        if (!fileName.endsWith(".xls"))
            fileName += ".xls";
        try {
            DefaultTableModel dtm = (DefaultTableModel) jTable_records.getModel();
            Workbook wb = new HSSFWorkbook();
            CreationHelper createhelper = wb.getCreationHelper();
            Sheet sheet = wb.createSheet("new sheet");
            Row row = null;
            Cell cell = null;
            row = sheet.createRow(0);

            HSSFFont font = (HSSFFont) wb.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();

            style.setFont(font);

            for (int t = 0; t < dtm.getColumnCount() - 1; t++) {
                cell = row.createCell(t);
                cell.setCellStyle(style);
                cell.setCellValue(dtm.getColumnName(t));
            }

            HSSFCellStyle style_gray = (HSSFCellStyle) wb.createCellStyle();
            style_gray.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
            // style_gray.setFillPattern(CellStyle.ALT_BARS);
            for (int i = 1; i <= dtm.getRowCount(); i++) {
                row = sheet.createRow(i);

                for (int j = 0; j < dtm.getColumnCount() - 1; j++) {
                    cell = row.createCell(j);
                    if (i % 2 == 0)
                        cell.setCellStyle(style_gray);
                    cell.setCellValue(dtm.getValueAt(i - 1, j).toString());

                }
            }

            FileOutputStream out = new FileOutputStream(fileName);
            wb.write(out);
            out.close();
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

}

From source file:ch.astina.hesperid.util.jasper.JasperExcelStreamResponse.java

License:Apache License

@Override
public void exportReportToStream(JasperPrint jasperPrint, OutputStream outputStream) throws Exception {
    JRXlsExporter exporter = new JRXlsExporter();
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
    exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, baos);
    exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS,
            this.removeEmptySpaceBetweenRows);
    exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS,
            this.removeEmptySpaceBetweenColumns);
    exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, this.printWhitePageBackground);
    exporter.setParameter(JRXlsExporterParameter.IS_FONT_SIZE_FIX_ENABLED, this.fixFontSize);
    exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_GRAPHICS, this.ignoreGraphics);
    exporter.exportReport();//from   ww w  . ja  va 2  s .  c o m

    HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(baos.toByteArray()));
    workbook.getSheetAt(0).setAutobreaks(true);
    workbook.getSheetAt(0).getPrintSetup().setFitHeight((short) jasperPrint.getPages().size());
    workbook.getSheetAt(0).getPrintSetup().setFitWidth((short) 1);

    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");

    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

    for (Integer x = 0; x < workbook.getSheetAt(0).getPhysicalNumberOfRows(); x++) {
        HSSFRow row = workbook.getSheetAt(0).getRow(x);

        Iterator<Cell> ci = row.cellIterator();

        Cell c = null;
        Date d = null;

        while (ci.hasNext()) {
            c = ci.next();
            try {
                d = sdf.parse(c.getStringCellValue().trim());
                c.setCellValue(d);
                c.setCellStyle(cellStyle);
            } catch (Exception e) {
            }
        }
    }

    workbook.write(outputStream);
}

From source file:ch.bfh.lca._15h.library.export.ExportToExcel.java

/***
 * Generate an Excel file based on a list of results.
 * Use the name of the fields described in the GenericResultRow to label the columns.
 * @param language Language of the label in the Excel file
 * @param sheetTitle Title of the sheet in the Excel file
 * @param tableTitle Title of the table in the Excel file
 * @param rows Arrays of rows to include in the listing
 * @param excelFilePath Path of the outputed file
 * @throws FileNotFoundException/*from   w  ww. ja v a 2s  . co  m*/
 * @throws IOException 
 */
public static void exportToExcel(Translation.TRANSLATION_LANGUAGE language, String sheetTitle,
        String tableTitle, GenericResultRow[] rows, String excelFilePath)
        throws FileNotFoundException, IOException {
    //Workbook wb = new HSSFWorkbook(); //xls
    Workbook wb = new SXSSFWorkbook(); //xlsx
    //create new sheet
    Sheet sheet1 = wb.createSheet(sheetTitle);
    Row row;
    Cell cell;
    String translation;

    int rowIndex = 0;

    //add title
    row = sheet1.createRow((short) rowIndex);
    cell = row.createCell(0);
    cell.setCellValue(tableTitle);
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 24);
    //font.setFontName("Courier New");
    style.setFont(font);
    cell.setCellStyle(style);

    //add rows
    for (int i = 0; i < rows.length; i++) {
        //if first line, write col names
        if (i == 0) {
            row = sheet1.createRow((short) rowIndex + 2);

            for (int j = 0; j < rows[i].getColNames().length; j++) {
                cell = row.createCell(j);

                //look for translation
                translation = Translation.getForKey(language, rows[i].getColNames()[j]);
                if (translation == null) {
                    translation = rows[i].getColNames()[j]; //if doesn't found a translation for the column take name of col
                }
                cell.setCellValue(translation);
            }
        }

        row = sheet1.createRow((short) (rowIndex + i + 3));

        for (int j = 0; j < rows[i].getColNames().length; j++) {
            cell = row.createCell(j);
            cell.setCellValue(rows[i].getValueAt(j).toString());
        }
    }

    //write to the file
    FileOutputStream fileOut = new FileOutputStream(excelFilePath);
    wb.write(fileOut);
    fileOut.close();

    wb.close();
}

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

private String getCellValueString(Cell cell) {
    if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {
        cell.setCellStyle(timestyle);
    }/*from w ww. j  a va  2  s .co m*/
    return formatter.formatCellValue(cell);
}

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

public void generateRequests(MacroscopicTopology macroscopicTopology, int requestsPerHour, int durationMinutes,
        int offset, String from, String to, Periodicity p) {
    String colLayoutString = getPropertyValue(tpaProps.REQUESTS_COL_LAYOUT);

    List<ColumnIdentifier> cols = new ArrayList<ColumnIdentifier>(requestsLayout.values().length);
    for (requestsLayout l : requestsLayout.values()) {
        cols.add(l);/*from w  w w .  j  ava 2 s  .  co  m*/
    }
    Map<ColumnIdentifier, Integer> colLayoutMapping = getColLayoutMapping(colLayoutString, cols);

    int arrtimeColIndex = colLayoutMapping.get(requestsLayout.ARRTIME);
    int fromColIndex = colLayoutMapping.get(requestsLayout.FROM);
    int toColIndex = colLayoutMapping.get(requestsLayout.TO);
    int deptimeColIndex = colLayoutMapping.get(requestsLayout.DEPTIME);
    int idColIndex = colLayoutMapping.get(requestsLayout.ID);
    String requestedMarker = getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER);

    List<String> linkNames = macroscopicTopology.getLinkNames();
    Sheet sheet = wb.getSheet(getPropertyValue(tpaProps.REQUESTS_WS_NAME));

    // header
    Row headerRow = sheet.createRow(0);
    for (ColumnIdentifier col : colLayoutMapping.keySet()) {
        int i = colLayoutMapping.get(col);
        Cell cell = headerRow.getCell(i, Row.CREATE_NULL_AS_BLANK);
        cell.setCellValue(col.name());
    }
    // train path slots hourly
    int k = 1;
    for (int i = 0; i < 24; i++) {
        for (int j = 0; j < requestsPerHour; j++) {
            Row row = sheet.createRow(sheet.getLastRowNum() + 1);
            int hour = i;
            int minutes = (offset + j * (60 / requestsPerHour)) % 60;
            double deptime = DateUtil
                    .convertTime(String.format("%02d", hour) + ":" + String.format("%02d", minutes));
            double arrtime = (deptime * 24 * 60 + durationMinutes) / (24 * 60);

            Cell cell = row.getCell(deptimeColIndex, Row.CREATE_NULL_AS_BLANK);
            cell.setCellStyle(timestyle);
            cell.setCellValue(deptime);
            cell = row.getCell(arrtimeColIndex, Row.CREATE_NULL_AS_BLANK);
            cell.setCellStyle(timestyle);
            cell.setCellValue(arrtime);

            row.getCell(fromColIndex, Row.CREATE_NULL_AS_BLANK).setCellValue(from);
            row.getCell(toColIndex, Row.CREATE_NULL_AS_BLANK).setCellValue(to);

            row.getCell(idColIndex, Row.CREATE_NULL_AS_BLANK)
                    .setCellValue(from + "_" + to + "_" + String.format("%03d", k));
            k++;

            // peridiocity
            for (Integer integer : p.getWeekDays()) {
                int weekdayColIndex = colLayoutMapping.get(requestsLayout.getWeekDayTrainPathLayout(integer));
                row.getCell(weekdayColIndex, Row.CREATE_NULL_AS_BLANK).setCellValue(requestedMarker);
            }
        }
    }
}