Example usage for org.apache.poi.hssf.usermodel HSSFCellStyle setDataFormat

List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCellStyle
  6. setDataFormat

Introduction

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

Prototype

@Override
public void setDataFormat(short fmt)

Source Link

Document

set the data format (must be a valid format)

Usage

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();/* w  w  w  . ja v  a  2  s  .  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: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  .  j a v  a2  s .  c om

    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:com.accenture.control.GerenciaPlanilhaTS.java

public void geraNovaPlanilhaTS(String dir, List<TesteCaseTSBean> listTS)
        throws FileNotFoundException, IOException {

    FileInputStream arquivo = new FileInputStream(new File(dir));
    HSSFWorkbook workbook = new HSSFWorkbook(arquivo);
    HSSFSheet sheetTS = workbook.getSheetAt(0);

    HSSFDataFormat format = workbook.createDataFormat();
    HSSFCellStyle estilo = workbook.createCellStyle();
    String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);//w w  w. ja  va 2 s. c  o m

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);

    for (int i = 0; i < listTS.size(); i++) {

        estilo.setDataFormat(format.getFormat(formatData));
        estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

        row = sheetTS.getRow(linha);

        descriptionPlan = row.getCell(0);
        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);

        descriptionPlan.setCellValue(listTS.get(i).getTestPlan());
        prj.setCellValue(listTS.get(i).getSTIPRJ());
        fase.setCellValue(listTS.get(i).getFASE());
        testPhase.setCellValue(listTS.get(i).getTestPhase());
        testScriptName.setCellValue(listTS.get(i).getTestScriptName());
        testScriptDescription.setCellValue(listTS.get(i).getTestScriptDescription());
        stepNo.setCellValue(listTS.get(i).getSTEP_NUMERO());
        stepDescription.setCellValue(listTS.get(i).getStepDescription());
        expectedResults.setCellValue(listTS.get(i).getExpectedResults());
        product.setCellValue(listTS.get(i).getProduct());
        dataPlanejada.setCellValue(listTS.get(i).getDataPlanejada());

        dataPlanejada.setCellStyle(estilo);

        linha = linha + 2;

    }

    FileOutputStream fileOut = new FileOutputStream(new File(dir));
    workbook.write(fileOut);
    fileOut.close();
    arquivo.close();

}

From source file:com.ah.ui.actions.admin.LicenseMgrAction.java

License:Open Source License

private void setEntitlementKeyCellValue(int cellcount, HSSFCellStyle cs, HSSFCellStyle cs1, HSSFRow r,
        OrderHistoryInfo lsInfo, String keyTitle) {
    for (int i = 0; i < cellcount; i++) {
        HSSFCell c = r.createCell(i);//from w  w  w.ja va 2 s. co  m
        c.setCellStyle(cs);
        switch (i) {
        case 0:
            c.setCellValue(null == lsInfo ? keyTitle : lsInfo.getOrderKey());
            break;
        case 1:
            c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.type")
                    : lsInfo.getLicenseTypeStr());
            break;
        case 2:
            c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.ap")
                    : String.valueOf(lsInfo.getNumberOfAps()));
            if (null != lsInfo) {
                cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                c.setCellStyle(cs1);
            }
            break;
        case 3:
            if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(
                        null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.subscription.end")
                                : lsInfo.getSubEndTimeStr());
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.cvg")
                        : String.valueOf(lsInfo.getNumberOfCvgs()));
            }
            if (null != lsInfo) {
                if (!NmsUtil.isHostedHMApplication()) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                }
                if (!"N/A".equals(c.getStringCellValue())) {
                    c.setCellStyle(cs1);
                }
            }
            break;
        case 4:
            if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.cvg")
                        : String.valueOf(lsInfo.getNumberOfCvgs()));
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.vhm")
                        : String.valueOf(lsInfo.getNumberOfVhms()));
            }
            if (null != lsInfo) {
                cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                c.setCellStyle(cs1);
            }
            break;
        case 5:
            if (NmsUtil.isHMForOEM()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.time")
                        : (lsInfo.getIsPermanentLicense() ? "N/A"
                                : String.valueOf(lsInfo.getNumberOfEvalValidDays())));
                if (null != lsInfo) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                    c.setCellStyle(lsInfo.getIsPermanentLicense() ? cs : cs1);
                }
            } else if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(
                        null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.subscription.end")
                                : lsInfo.getCvgSubEndTimeStr());
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.end")
                        : lsInfo.getSupportEndTimeStr());
            }
            if (null != lsInfo && !"N/A".equals(c.getStringCellValue())) {
                c.setCellStyle(cs1);
            }
            break;
        case 6:
            if (NmsUtil.isHMForOEM()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.active")
                        : lsInfo.getActiveTimeStr());
            } else if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.end")
                        : lsInfo.getSupportEndTimeStr());
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.time")
                        : (lsInfo.getIsPermanentLicense() ? "N/A"
                                : String.valueOf(lsInfo.getNumberOfEvalValidDays())));
                if (null != lsInfo) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                    c.setCellStyle(lsInfo.getIsPermanentLicense() ? cs : cs1);
                }
            }
            if (null != lsInfo && !"N/A".equals(c.getStringCellValue())) {
                c.setCellStyle(cs1);
            }
            break;
        case 7:
            if (NmsUtil.isHostedHMApplication()) {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.time")
                        : (lsInfo.getIsPermanentLicense() ? "N/A"
                                : String.valueOf(lsInfo.getNumberOfEvalValidDays())));
                if (null != lsInfo) {
                    cs1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                    c.setCellStyle(lsInfo.getIsPermanentLicense() ? cs : cs1);
                }
            } else {
                c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.active")
                        : lsInfo.getActiveTimeStr());
            }
            if (null != lsInfo && !"N/A".equals(c.getStringCellValue())) {
                c.setCellStyle(cs1);
            }
            break;
        case 8:
            c.setCellValue(null == lsInfo ? MgrUtil.getUserMessage("admin.license.orderKey.support.active")
                    : lsInfo.getActiveTimeStr());
            if (null != lsInfo) {
                c.setCellStyle(cs1);
            }
            break;
        default:
            break;
        }
    }
}

From source file:com.brick.customer.util.CustomerInfoExcel.java

@SuppressWarnings("unchecked")
public HSSFWorkbook createReport(Map<String, Object> params, Context context) throws Exception {

    ExcelFileWriter efw = new ExcelFileWriter();
    HSSFSheet sheet = efw.createSheet(context.contextMap.get("sheetName") == null ? "summary"
            : (String) context.contextMap.get("sheetName"));
    List<HashMap<String, Object>> list = (List<HashMap<String, Object>>) params.get("cusInfo");

    sheet.setColumnWidth(0, 5000);//from   w  ww .  ja v  a2s .  c  o  m
    sheet.setColumnWidth(1, 3000);
    sheet.setColumnWidth(2, 5300);
    sheet.setColumnWidth(3, 3600);
    sheet.setColumnWidth(4, 4600);
    sheet.setColumnWidth(5, 10000);
    sheet.setColumnWidth(6, 6000);
    sheet.setColumnWidth(7, 4000);
    sheet.setColumnWidth(8, 3500);
    sheet.setColumnWidth(9, 3500);
    sheet.setColumnWidth(10, 3500);
    sheet.setColumnWidth(11, 4200);
    sheet.setColumnWidth(12, 4200);
    sheet.setColumnWidth(13, 4200);
    sheet.setColumnWidth(14, 4800);
    sheet.setColumnWidth(15, 5000);
    sheet.setColumnWidth(16, 5000);
    sheet.setColumnWidth(17, 3000);
    sheet.setColumnWidth(18, 4500);
    sheet.setColumnWidth(19, 4500);
    sheet.setColumnWidth(20, 3000);
    sheet.setColumnWidth(21, 4300);
    sheet.setColumnWidth(22, 4000);
    sheet.setColumnWidth(23, 7000);
    sheet.setColumnWidth(24, 10000);

    HSSFFont headFont0 = null;
    HSSFCellStyle headStyle0 = null;

    headFont0 = efw.getWorkbook().createFont();
    headFont0.setFontHeightInPoints((short) 13); //?
    headFont0.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //    

    headStyle0 = efw.getWorkbook().createCellStyle(); //?
    headStyle0.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?   
    headStyle0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //     
    headStyle0.setWrapText(true); // ?  
    headStyle0.setFillBackgroundColor((short) 59);
    headStyle0.setFont(headFont0);

    //??
    HSSFCellStyle cellMoney = efw.getWorkbook().createCellStyle();
    HSSFDataFormat format = efw.getWorkbook().createDataFormat();
    cellMoney.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellMoney.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    cellMoney.setDataFormat(format.getFormat("#,###,##0"));

    HSSFRow row0 = sheet.createRow(0);
    HSSFCell cell0 = row0.createCell(0);
    cell0.setCellValue("?");
    cell0.setCellStyle(headStyle0);

    HSSFCell cell1 = row0.createCell(1);
    cell1.setCellValue("??");
    cell1.setCellStyle(headStyle0);

    HSSFCell cell2 = row0.createCell(2);
    cell2.setCellValue("???");
    cell2.setCellStyle(headStyle0);

    HSSFCell cell3 = row0.createCell(3);
    cell3.setCellValue("??");
    cell3.setCellStyle(headStyle0);

    HSSFCell cell4 = row0.createCell(4);
    cell4.setCellValue("?");
    cell4.setCellStyle(headStyle0);

    HSSFCell cell5 = row0.createCell(5);
    cell5.setCellValue("??");
    cell5.setCellStyle(headStyle0);

    HSSFCell cell6 = row0.createCell(6);
    cell6.setCellValue("??");
    cell6.setCellStyle(headStyle0);

    HSSFCell cell7 = row0.createCell(7);
    cell7.setCellValue("?");
    cell7.setCellStyle(headStyle0);

    HSSFCell cell8 = row0.createCell(8);
    cell8.setCellValue("?");
    cell8.setCellStyle(headStyle0);

    HSSFCell cell9 = row0.createCell(9);
    cell9.setCellValue("");
    cell9.setCellStyle(headStyle0);

    HSSFCell cell10 = row0.createCell(10);
    cell10.setCellValue("");
    cell10.setCellStyle(headStyle0);

    HSSFCell cell11 = row0.createCell(11);
    cell11.setCellValue("??");
    cell11.setCellStyle(headStyle0);

    HSSFCell cell12 = row0.createCell(12);
    cell12.setCellValue("?");
    cell12.setCellStyle(headStyle0);

    HSSFCell cell13 = row0.createCell(13);
    cell13.setCellValue("?");
    cell13.setCellStyle(headStyle0);

    HSSFCell cell14 = row0.createCell(14);
    cell14.setCellValue("");
    cell14.setCellStyle(headStyle0);

    HSSFCell cell15 = row0.createCell(15);
    cell15.setCellValue("?");
    cell15.setCellStyle(headStyle0);

    HSSFCell cell16 = row0.createCell(16);
    cell16.setCellValue("??");
    cell16.setCellStyle(headStyle0);

    HSSFCell cell17 = row0.createCell(17);
    cell17.setCellValue("?");
    cell17.setCellStyle(headStyle0);

    HSSFCell cell18 = row0.createCell(18);
    cell18.setCellValue("??");
    cell18.setCellStyle(headStyle0);

    HSSFCell cell19 = row0.createCell(19);
    cell19.setCellValue("?");
    cell19.setCellStyle(headStyle0);

    HSSFCell cell20 = row0.createCell(20);
    cell20.setCellValue("");
    cell20.setCellStyle(headStyle0);

    HSSFCell cell21 = row0.createCell(21);
    cell21.setCellValue("???");
    cell21.setCellStyle(headStyle0);

    HSSFCell cell22 = row0.createCell(22);
    cell22.setCellValue("??");
    cell22.setCellStyle(headStyle0);

    HSSFCell cell23 = row0.createCell(23);
    cell23.setCellValue("??");
    cell23.setCellStyle(headStyle0);

    HSSFCell cell24 = row0.createCell(24);
    cell24.setCellValue("");
    cell24.setCellStyle(headStyle0);

    for (int i = 0; i < list.size(); i++) {
        HSSFRow row1 = sheet.createRow(i + 1);

        HSSFCell cellr0 = row1.createCell(0);
        cellr0.setCellValue((String) list.get(i).get("CUST_CODE"));

        HSSFCell cellr1 = row1.createCell(1);
        cellr1.setCellValue((String) list.get(i).get("NAME"));

        HSSFCell cellr2 = row1.createCell(2);
        cellr2.setCellValue((String) list.get(i).get("CUST_NAME"));

        HSSFCell cellr3 = row1.createCell(3);
        cellr3.setCellValue((String) list.get(i).get("CORP_ORAGNIZATION_CODE"));

        HSSFCell cellr4 = row1.createCell(4);
        cellr4.setCellValue((String) list.get(i).get("CUST_AREA"));

        HSSFCell cellr5 = row1.createCell(5);
        cellr5.setCellValue((String) list.get(i).get("CORP_WORK_ADDRESS"));

        HSSFCell cellr6 = row1.createCell(6);
        cellr6.setCellValue((String) list.get(i).get("VIRTUAL_CODE"));

        HSSFCell cellr7 = row1.createCell(7);
        String s = null;
        int type = Integer.parseInt(list.get(i).get("STATETYPE").toString());
        switch (type) {
        case 0:
            s = "";
            break;
        case 1:
            s = "";
            break;
        case 2:
            s = "??";
            break;
        case 3:
            s = "";
            break;
        case 4:
            s = "";
            break;
        }
        cellr7.setCellValue(s);

        HSSFCell cellr8 = row1.createCell(8);
        cellr8.setCellValue((String) list.get(i).get("CORP_SETUP_DATE"));

        HSSFCell cellr9 = row1.createCell(9);
        double n = list.get(i).get("CORP_REGISTE_CAPITAL") == null ? 0
                : (Double) list.get(i).get("CORP_REGISTE_CAPITAL");
        cellr9.setCellValue(n);
        cellr9.setCellStyle(cellMoney);

        HSSFCell cellr10 = row1.createCell(10);
        double m = list.get(i).get("CORP_PAICLUP_CAPITAL") == null ? 0
                : (Double) list.get(i).get("CORP_PAICLUP_CAPITAL");
        cellr10.setCellValue(m);
        cellr10.setCellStyle(cellMoney);

        HSSFCell cellr11 = row1.createCell(11);
        cellr11.setCellValue((String) list.get(i).get("CORP_BUSINESS_LICENSE"));

        HSSFCell cellr12 = row1.createCell(12);
        cellr12.setCellValue((String) list.get(i).get("TAX_CODE"));

        HSSFCell cellr13 = row1.createCell(13);
        cellr13.setCellValue((String) list.get(i).get("CORP_TAX_CODE"));

        HSSFCell cellr14 = row1.createCell(14);
        cellr14.setCellValue((String) list.get(i).get("CORP_PERIOD_VALIDITY"));

        HSSFCell cellr15 = row1.createCell(15);
        cellr15.setCellValue((String) list.get(i).get("CORP_WORK_ADDRESS"));

        HSSFCell cellr16 = row1.createCell(16);
        cellr16.setCellValue((String) list.get(i).get("CORP_BUSINESS_RANGE"));

        HSSFCell cellr17 = row1.createCell(17);
        cellr17.setCellValue((String) list.get(i).get("CORP_COMPANY_ZIP"));

        HSSFCell cellr18 = row1.createCell(18);
        cellr18.setCellValue((String) list.get(i).get("CORP_COMPANY_WEBSITE"));

        HSSFCell cellr19 = row1.createCell(19);
        cellr19.setCellValue((String) list.get(i).get("CORP_COMPANY_EMAIL"));

        HSSFCell cellr20 = row1.createCell(20);
        cellr20.setCellValue((String) list.get(i).get("CORP_HEAD_SIGNATURE"));

        HSSFCell cellr21 = row1.createCell(21);
        cellr21.setCellValue((String) list.get(i).get("CORP_HS_IDCARD"));

        HSSFCell cellr22 = row1.createCell(22);
        cellr22.setCellValue((String) list.get(i).get("CORP_HS_LINK_MODE"));

        HSSFCell cellr23 = row1.createCell(23);
        cellr23.setCellValue((String) list.get(i).get("CORP_HS_HOME_ADDRESS"));

        HSSFCell cellr24 = row1.createCell(24);
        cellr24.setCellValue((String) list.get(i).get("REMARK"));
    }
    return efw.getWorkbook();
}

From source file:com.commander4j.util.JExcel.java

License:Open Source License

public void exportToExcel(String filename, ResultSet rs) {
    try {/*from  w ww  .  j  a v a2 s  . c  o  m*/

        ResultSetMetaData rsmd = rs.getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        int columnType = 0;
        String columnTypeName = "";
        int recordNumber = 0;
        int passwordCol = -1;

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        HSSFCellStyle cellStyle_varchar = workbook.createCellStyle();
        cellStyle_varchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_nvarchar = workbook.createCellStyle();
        cellStyle_nvarchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_varchar2 = workbook.createCellStyle();
        cellStyle_varchar2.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_title = workbook.createCellStyle();
        cellStyle_title.setAlignment(HorizontalAlignment.CENTER);

        HSSFCellStyle cellStyle_char = workbook.createCellStyle();
        cellStyle_char.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_date = workbook.createCellStyle();
        cellStyle_date.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_date.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_timestamp = workbook.createCellStyle();
        cellStyle_timestamp.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_timestamp.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_decimal = workbook.createCellStyle();
        cellStyle_decimal.setAlignment(HorizontalAlignment.RIGHT);

        HSSFFont font_title = workbook.createFont();
        font_title.setColor((short) 0xc);
        font_title.setBold(true);
        ;
        font_title.setItalic(true);
        font_title.setUnderline(HSSFFont.U_DOUBLE);
        cellStyle_title.setFont(font_title);

        HSSFCell cell;
        HSSFRow row;

        // rs.beforeFirst();

        while (rs.next()) {
            recordNumber++;

            if (recordNumber == 1) {
                row = sheet.createRow((int) 0);
                for (int column = 1; column <= numberOfColumns; column++) {
                    cell = row.createCell((int) (column - 1));
                    String columnName = rsmd.getColumnLabel(column);
                    columnName = columnName.replace("_", " ");
                    columnName = JUtility.capitalize(columnName);
                    cell.setCellStyle(cellStyle_title);
                    cell.setCellValue(columnName);
                    if (columnName.equals("Password")) {
                        passwordCol = column;
                    }
                }
            }

            row = sheet.createRow((int) recordNumber);

            for (int column = 1; column <= numberOfColumns; column++) {

                columnType = rsmd.getColumnType(column);
                columnTypeName = rsmd.getColumnTypeName(column);

                cell = row.createCell((int) (column - 1));

                try {
                    switch (columnType) {
                    case java.sql.Types.NVARCHAR:
                        HSSFRichTextString rtf_nvarchar;
                        if (column == passwordCol) {
                            rtf_nvarchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_nvarchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_nvarchar);
                        cell.setCellValue(rtf_nvarchar);
                        break;
                    case java.sql.Types.VARCHAR:
                        HSSFRichTextString rtf_varchar;
                        if (column == passwordCol) {
                            rtf_varchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_varchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_varchar);
                        cell.setCellValue(rtf_varchar);
                        break;
                    case java.sql.Types.CHAR:
                        HSSFRichTextString rtf_char = new HSSFRichTextString(rs.getString(column));
                        cell.setCellStyle(cellStyle_char);
                        cell.setCellValue(rtf_char);
                        break;
                    case java.sql.Types.DATE:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_date);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.TIMESTAMP:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_timestamp);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.DECIMAL:
                        HSSFRichTextString rtf_decimal = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimal);
                        break;
                    case java.sql.Types.NUMERIC:
                        HSSFRichTextString rtf_decimaln = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimaln);
                        break;
                    case java.sql.Types.BIGINT:
                        HSSFRichTextString rtf_bigint = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_bigint);
                        break;
                    case java.sql.Types.INTEGER:
                        HSSFRichTextString rtf_int = new HSSFRichTextString(String.valueOf(rs.getInt(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_int);
                        break;
                    case java.sql.Types.FLOAT:
                        HSSFRichTextString rtf_float = new HSSFRichTextString(
                                String.valueOf(rs.getFloat(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_float);
                        break;
                    case java.sql.Types.DOUBLE:
                        HSSFRichTextString rtf_double = new HSSFRichTextString(
                                String.valueOf(rs.getDouble(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_double);
                        break;
                    default:
                        cell.setCellValue(new HSSFRichTextString(columnTypeName));
                        break;
                    }
                } catch (Exception ex) {
                    String errormessage = ex.getLocalizedMessage();
                    HSSFRichTextString rtf_exception = new HSSFRichTextString(errormessage);
                    cell.setCellStyle(cellStyle_varchar);
                    cell.setCellValue(rtf_exception);
                    break;
                }
            }

            if (recordNumber == 65535) {
                break;
            }
        }

        for (int column = 1; column <= numberOfColumns; column++) {
            sheet.autoSizeColumn((int) (column - 1));
        }

        if (recordNumber > 0) {
            try {
                FileOutputStream fileOut = new FileOutputStream(filename.toLowerCase());
                workbook.write(fileOut);
                fileOut.close();
            } catch (Exception ex) {
                setErrorMessage(ex.getMessage());
            }
        }

        try {
            workbook.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }
}

From source file:com.demo.common.extreme.view.XlsView.java

License:Apache License

private Map initStyles(HSSFWorkbook wb, short fontHeight) {
    Map result = new HashMap();
    HSSFCellStyle titleStyle = wb.createCellStyle();
    HSSFCellStyle textStyle = wb.createCellStyle();
    HSSFCellStyle boldStyle = wb.createCellStyle();
    HSSFCellStyle numericStyle = wb.createCellStyle();
    HSSFCellStyle numericStyleBold = wb.createCellStyle();
    HSSFCellStyle moneyStyle = wb.createCellStyle();
    HSSFCellStyle moneyStyleBold = wb.createCellStyle();
    HSSFCellStyle percentStyle = wb.createCellStyle();
    HSSFCellStyle percentStyleBold = wb.createCellStyle();

    // Add to export totals
    HSSFCellStyle moneyStyle_Totals = wb.createCellStyle();
    HSSFCellStyle naStyle_Totals = wb.createCellStyle();
    HSSFCellStyle numericStyle_Totals = wb.createCellStyle();
    HSSFCellStyle percentStyle_Totals = wb.createCellStyle();
    HSSFCellStyle textStyle_Totals = wb.createCellStyle();

    result.put("titleStyle", titleStyle);
    result.put("textStyle", textStyle);
    result.put("boldStyle", boldStyle);
    result.put("numericStyle", numericStyle);
    result.put("numericStyleBold", numericStyleBold);
    result.put("moneyStyle", moneyStyle);
    result.put("moneyStyleBold", moneyStyleBold);
    result.put("percentStyle", percentStyle);
    result.put("percentStyleBold", percentStyleBold);

    // Add to export totals
    result.put("moneyStyle_Totals", moneyStyle_Totals);
    result.put("naStyle_Totals", naStyle_Totals);
    result.put("numericStyle_Totals", numericStyle_Totals);
    result.put("percentStyle_Totals", percentStyle_Totals);
    result.put("textStyle_Totals", textStyle_Totals);

    HSSFDataFormat format = wb.createDataFormat();

    // Global fonts
    HSSFFont font = wb.createFont();/*from w w  w  .ja  v  a  2 s.  c om*/
    font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    font.setColor(HSSFColor.BLACK.index);
    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setFontHeightInPoints(fontHeight);

    HSSFFont fontBold = wb.createFont();
    fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    fontBold.setColor(HSSFColor.BLACK.index);
    fontBold.setFontName(HSSFFont.FONT_ARIAL);
    fontBold.setFontHeightInPoints(fontHeight);

    // Money Style
    moneyStyle.setFont(font);
    moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    moneyStyle.setDataFormat(format.getFormat(moneyFormat));

    // Money Style Bold
    moneyStyleBold.setFont(fontBold);
    moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    moneyStyleBold.setDataFormat(format.getFormat(moneyFormat));

    // Percent Style
    percentStyle.setFont(font);
    percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    percentStyle.setDataFormat(format.getFormat(percentFormat));

    // Percent Style Bold
    percentStyleBold.setFont(fontBold);
    percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    percentStyleBold.setDataFormat(format.getFormat(percentFormat));

    // Standard Numeric Style
    numericStyle.setFont(font);
    numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

    // Standard Numeric Style Bold
    numericStyleBold.setFont(fontBold);
    numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

    // Title Style
    titleStyle.setFont(font);
    titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    titleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    titleStyle.setRightBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    titleStyle.setTopBorderColor(HSSFColor.BLACK.index);
    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    // Standard Text Style
    textStyle.setFont(font);
    textStyle.setWrapText(true);

    // Standard Text Style
    boldStyle.setFont(fontBold);
    boldStyle.setWrapText(true);

    // Money Style Total
    moneyStyle_Totals.setFont(fontBold);
    moneyStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    moneyStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    moneyStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    moneyStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index);
    moneyStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN);
    moneyStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index);
    moneyStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    moneyStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    moneyStyle_Totals.setDataFormat(format.getFormat(moneyFormat));

    // n/a Style Total
    naStyle_Totals.setFont(fontBold);
    naStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    naStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    naStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    naStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index);
    naStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN);
    naStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index);
    naStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    naStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    // Numeric Style Total
    numericStyle_Totals.setFont(fontBold);
    numericStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    numericStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    numericStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    numericStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index);
    numericStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN);
    numericStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index);
    numericStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    numericStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    // Percent Style Total
    percentStyle_Totals.setFont(fontBold);
    percentStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    percentStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    percentStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    percentStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index);
    percentStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN);
    percentStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index);
    percentStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    percentStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    percentStyle_Totals.setDataFormat(format.getFormat(percentFormat));

    // Text Style Total
    textStyle_Totals.setFont(fontBold);
    textStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    textStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    textStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    textStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index);
    textStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN);
    textStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index);
    textStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    textStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    return result;
}

From source file:com.eryansky.core.excelTools.ExcelUtils.java

License:Apache License

public static void copyCellStyle(HSSFWorkbook destwb, HSSFCellStyle dest, HSSFWorkbook srcwb,
        HSSFCellStyle src) {/*from   w  w w  . j ava 2  s .  c o  m*/
    if (src == null || dest == null)
        return;
    dest.setAlignment(src.getAlignment());
    dest.setBorderBottom(src.getBorderBottom());
    dest.setBorderLeft(src.getBorderLeft());
    dest.setBorderRight(src.getBorderRight());
    dest.setBorderTop(src.getBorderTop());
    dest.setBottomBorderColor(findColor(src.getBottomBorderColor(), srcwb, destwb));
    dest.setDataFormat(
            destwb.createDataFormat().getFormat(srcwb.createDataFormat().getFormat(src.getDataFormat())));
    dest.setFillPattern(src.getFillPattern());
    dest.setFillForegroundColor(findColor(src.getFillForegroundColor(), srcwb, destwb));
    dest.setFillBackgroundColor(findColor(src.getFillBackgroundColor(), srcwb, destwb));
    dest.setHidden(src.getHidden());
    dest.setIndention(src.getIndention());
    dest.setLeftBorderColor(findColor(src.getLeftBorderColor(), srcwb, destwb));
    dest.setLocked(src.getLocked());
    dest.setRightBorderColor(findColor(src.getRightBorderColor(), srcwb, destwb));
    dest.setRotation(src.getRotation());
    dest.setTopBorderColor(findColor(src.getTopBorderColor(), srcwb, destwb));
    dest.setVerticalAlignment(src.getVerticalAlignment());
    dest.setWrapText(src.getWrapText());

    HSSFFont f = srcwb.getFontAt(src.getFontIndex());
    HSSFFont nf = findFont(f, srcwb, destwb);
    if (nf == null) {
        nf = destwb.createFont();
        nf.setBoldweight(f.getBoldweight());
        nf.setCharSet(f.getCharSet());
        nf.setColor(findColor(f.getColor(), srcwb, destwb));
        nf.setFontHeight(f.getFontHeight());
        nf.setFontHeightInPoints(f.getFontHeightInPoints());
        nf.setFontName(f.getFontName());
        nf.setItalic(f.getItalic());
        nf.setStrikeout(f.getStrikeout());
        nf.setTypeOffset(f.getTypeOffset());
        nf.setUnderline(f.getUnderline());
    }
    dest.setFont(nf);
}

From source file:com.github.gaborfeher.grantmaster.framework.base.ExcelExporter.java

License:Open Source License

private void setExcelCell(HSSFWorkbook workbook, Object cellValue, Cell excelCell) {
    if (cellValue instanceof BigDecimal) {
        double doubleValue = ((BigDecimal) cellValue).doubleValue();
        excelCell.setCellValue(doubleValue);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFDataFormat hssfDataFormat = workbook.createDataFormat();
        cellStyle.setDataFormat(hssfDataFormat.getFormat("#,##0.00"));
        excelCell.setCellStyle(cellStyle);
        excelCell.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (cellValue instanceof LocalDate) {
        LocalDate localDate = (LocalDate) cellValue;
        Calendar calendar = Calendar.getInstance();
        calendar.set(localDate.getYear(), localDate.getMonthValue() - 1, localDate.getDayOfMonth());
        excelCell.setCellValue(calendar);

        String excelFormatPattern = DateFormatConverter.convert(Locale.US, "yyyy-MM-DD");
        CellStyle cellStyle = workbook.createCellStyle();
        DataFormat poiFormat = workbook.createDataFormat();
        cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
        excelCell.setCellStyle(cellStyle);
    } else if (cellValue != null) {
        excelCell.setCellValue(cellValue.toString());
    }/* w  w w  . java 2 s.  c o  m*/
}

From source file:com.github.gujou.deerbelling.sonarqube.service.XlsTasksGenerator.java

License:Open Source License

public static File generateFile(Project sonarProject, FileSystem sonarFileSystem, String sonarUrl,
        String sonarLogin, String sonarPassword) {

    short formatIndex;
    HSSFDataFormat dataFormat = null;//from w ww .  j  a  v a2 s. c o  m
    FileOutputStream out = null;
    HSSFWorkbook workbook = null;

    String filePath = sonarFileSystem.workDir().getAbsolutePath() + File.separator + "tasks_report_"
            + sonarProject.getEffectiveKey().replace(':', '-') + "."
            + ReportsKeys.TASKS_REPORT_TYPE_XLS_EXTENSION;

    File resultFile = new File(filePath);

    try {
        out = new FileOutputStream(resultFile);

        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Tasks list");

        // Date format.
        dataFormat = workbook.createDataFormat();
        formatIndex = dataFormat.getFormat("yyyy-MM-ddTHH:mm:ss");
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(formatIndex);

        Issues rootIssue = IssueGateway.getOpenIssues(sonarProject.getEffectiveKey(), sonarUrl, sonarLogin,
                sonarPassword);

        if (rootIssue == null) {
            return null;
        }

        DataValidationHelper validationHelper = new HSSFDataValidationHelper(sheet);
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(
                new String[] { "OPENED", "CONFIRMED", "REOPENED", "RESOLVED", "CLOSE" });
        CellRangeAddressList addressList = new CellRangeAddressList(1, rootIssue.getIssues().size() + 1,
                STATUS_COLUMN_INDEX, STATUS_COLUMN_INDEX);
        DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
        dataValidation.setSuppressDropDownArrow(false);
        sheet.addValidationData(dataValidation);

        int rownum = 0;

        Row row = sheet.createRow(rownum++);
        row.createCell(STATUS_COLUMN_INDEX).setCellValue("Status");
        row.createCell(SEVERITY_COLUMN_INDEX).setCellValue("Severity");
        row.createCell(COMPONENT_COLUMN_INDEX).setCellValue("Component");
        row.createCell(LINE_COLUMN_INDEX).setCellValue("Line");
        row.createCell(MESSAGE_COLUMN_INDEX).setCellValue("Message");
        row.createCell(AUTHOR_COLUMN_INDEX).setCellValue("Author");
        row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue("Assigned");
        row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue("CreationDate");
        row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue("UpdateDate");
        row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue("Path");

        for (Issue issue : rootIssue.getIssues()) {
            if (issue != null) {
                row = sheet.createRow(rownum++);
                int componentIndex = 0;
                if (issue.getComponent() != null) {
                    componentIndex = issue.getComponent().lastIndexOf('/');
                }
                String component;
                String path;
                if (componentIndex > 0) {
                    component = issue.getComponent().substring(componentIndex + 1);
                    path = issue.getComponent().substring(0, componentIndex);
                } else {
                    component = issue.getComponent();
                    path = "";
                }

                // Set values.
                row.createCell(STATUS_COLUMN_INDEX).setCellValue(issue.getStatus());
                row.createCell(SEVERITY_COLUMN_INDEX).setCellValue(issue.getSeverity());
                row.createCell(COMPONENT_COLUMN_INDEX).setCellValue(component);
                row.createCell(LINE_COLUMN_INDEX).setCellValue(issue.getLine());
                row.createCell(MESSAGE_COLUMN_INDEX).setCellValue(issue.getMessage());
                row.createCell(AUTHOR_COLUMN_INDEX).setCellValue(issue.getAuthor());
                row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue(issue.getAssignee());
                row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue(issue.getCreationDate());
                row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue(issue.getUpdateDate());
                row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue(path);

                // Set date style to date column.
                row.getCell(CREATION_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
                row.getCell(UPDATE_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
            }
        }

        // Auto-size sheet columns.
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_COLUMN_INDEX);
        sheet.autoSizeColumn(LINE_COLUMN_INDEX);
        sheet.autoSizeColumn(MESSAGE_COLUMN_INDEX);
        sheet.autoSizeColumn(AUTHOR_COLUMN_INDEX);
        sheet.autoSizeColumn(ASSIGNED_COLUMN_INDEX);
        sheet.autoSizeColumn(CREATION_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(UPDATE_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_PATH_COLUMN_INDEX);

        workbook.write(out);

    } catch (FileNotFoundException e) {

        // TODO manage error.
        e.printStackTrace();
    } catch (IOException e) {

        // TODO manage error.
        e.printStackTrace();
    } finally {
        IOUtils.closeQuietly(workbook);
        IOUtils.closeQuietly(out);
    }

    return resultFile;
}