Example usage for org.apache.poi.ss.usermodel Workbook createCellStyle

List of usage examples for org.apache.poi.ss.usermodel Workbook createCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createCellStyle

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook createCellStyle.

Prototype

CellStyle createCellStyle();

Source Link

Document

Create a new Cell style and add it to the workbook's style table

Usage

From source file:PlacasClientes.java

private void EnviarExcel(ResultSet rs) throws IOException {
    String rutaArchivo = System.getProperty("user.home") + "/ejemploExcelJava.xls";
    /*Se crea el objeto de tipo File con la ruta del archivo*/
    File archivoXLS = new File(rutaArchivo);
    /*Si el archivo existe se elimina*/
    if (archivoXLS.exists())
        archivoXLS.delete();/*from   w w w .ja va2 s.  co  m*/
    /*Se crea el archivo*/
    archivoXLS.createNewFile();

    /*Se crea el libro de excel usando el objeto de tipo Workbook*/
    Workbook libro = new HSSFWorkbook();
    CreationHelper createhelper = libro.getCreationHelper();
    CellStyle cellStyle = libro.createCellStyle();
    cellStyle.setDataFormat(createhelper.createDataFormat().getFormat("dd/mm/yyyy"));
    CellStyle cellStyle2 = libro.createCellStyle();

    /*Se inicializa el flujo de datos con el archivo xls*/
    FileOutputStream archivo = new FileOutputStream(archivoXLS);

    /*Utilizamos la clase Sheet para crear una nueva hoja de trabajo dentro del libro que creamos anteriormente*/
    Sheet hoja = libro.createSheet("ClientesPlacas");

    Font fuente = libro.createFont();
    fuente.setFontHeightInPoints((short) 13);
    fuente.setFontName("Arial");
    fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    Font fuente2 = libro.createFont();
    fuente.setFontHeightInPoints((short) 13);
    fuente.setFontName("Arial");
    //fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    Row Enc = hoja.createRow(2);
    Cell celda = Enc.createCell(0);

    celda.setCellValue("PLACA DE VEHICULOS POR CLIENTES");

}

From source file:adams.core.ExcelHelper.java

License:Open Source License

/**
 * Creates a cellstyle for a date cell with the specified format.
 * /*from w w w  . ja  va 2 s  .c o  m*/
 * @param wb      the workbook to use this format for
 * @param format   the format to use
 * @return      the generated style
 */
public static CellStyle getDateCellStyle(Workbook wb, String format) {
    CellStyle result;

    result = wb.createCellStyle();
    result.setDataFormat(wb.createDataFormat().getFormat(format));

    return result;
}

From source file:apm.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?//from   w ww .  jav  a 2s  .  c o m
 * @param wb 
 * @return ?
 */
private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font titleFont = wb.createFont();
    titleFont.setFontName("Arial");
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(titleFont);
    styles.put("title", style);

    style = wb.createCellStyle();
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    Font dataFont = wb.createFont();
    dataFont.setFontName("Arial");
    dataFont.setFontHeightInPoints((short) 10);
    style.setFont(dataFont);
    styles.put("data", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_LEFT);
    styles.put("data1", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put("data2", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    styles.put("data3", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    //      style.setWrapText(true);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Font headerFont = wb.createFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    return styles;
}

From source file:ar.edu.unrc.gametictactoe.performanceandtraining.configurations.StatisticExperiment.java

License:Open Source License

/**
 *
 * @param filePath/* ww w. j a  v a  2s  . co m*/
 * @param backupFiles
 * @param resultsPerFile
 * @param resultsRandom
 * @param randomPerceptronFile <p>
 * @throws IOException
 * @throws InvalidFormatException
 */
public void exportToExcel(String filePath, List<File> backupFiles, Map<File, StatisticForCalc> resultsPerFile,
        Map<File, StatisticForCalc> resultsRandom, File randomPerceptronFile)
        throws IOException, InvalidFormatException {
    InputStream inputXLSX = this.getClass()
            .getResourceAsStream("/ar/edu/unrc/gametictactoe/resources/EstadisticasTicTacToe.xlsx");
    Workbook wb = WorkbookFactory.create(inputXLSX);

    try (FileOutputStream outputXLSX = new FileOutputStream(
            filePath + "_" + dateFormater.format(dateForFileName) + "_STATISTICS" + ".xlsx")) {
        //============= imptimimos en la hoja de % Of Games Won ===================
        Sheet sheet = wb.getSheetAt(0);
        //Estilo par los titulos de las tablas
        int rowStartTitle = 0;
        int colStartTitle = 2;
        int rowStart = 1;
        int colStart = 3;
        Row rowPlayer1;
        Row rowPlayer2;
        Row rowDraw;
        // Luego creamos el objeto que se encargar de aplicar el estilo a la celda
        Font fontCellTitle = wb.createFont();
        fontCellTitle.setFontHeightInPoints((short) 10);
        fontCellTitle.setFontName("Arial");
        fontCellTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle CellStyleTitle = wb.createCellStyle();
        CellStyleTitle.setWrapText(true);
        CellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
        CellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        CellStyleTitle.setFont(fontCellTitle);

        // Establecemos el tipo de sombreado de nuestra celda
        CellStyleTitle.setFillBackgroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        CellStyleTitle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        loadTitle(rowStartTitle, colStartTitle, sheet, backupFiles.size(), CellStyleTitle);
        //estilo titulo finalizado

        //Estilo de celdas con los valores de las estadisticas
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);
        /* We are now ready to set borders for this style */
        /* Draw a thin left border */
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        /* Add medium right border */
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        /* Add dashed top border */
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        /* Add dotted bottom border */
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        //estilo celdas finalizado
        //loadTitle(rowStartTitle, colStartTitle, sheet, backupFiles.size(), CellStyleTitle);
        rowPlayer1 = sheet.getRow(rowStart);
        rowPlayer2 = sheet.getRow(rowStart + 1);
        rowDraw = sheet.getRow(rowStart + 2);
        for (int file = 0; file < backupFiles.size(); file++) {
            Cell cellPlayer1 = rowPlayer1.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC);
            Cell cellPlayer2 = rowPlayer2.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC);
            Cell cellDraw = rowDraw.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC);
            cellPlayer1.setCellStyle(cellStyle);
            cellPlayer2.setCellStyle(cellStyle);
            cellDraw.setCellStyle(cellStyle);
            Double cellValuePlayer1 = resultsPerFile.get(backupFiles.get(file)).getWinRatePlayer1();
            Double cellValuePlayer2 = resultsPerFile.get(backupFiles.get(file)).getWinRatePlayer2();
            Double cellValueDraw = resultsPerFile.get(backupFiles.get(file)).getDrawRate();
            assert cellValuePlayer1 <= 100 && cellValuePlayer1 >= 0;
            assert cellValuePlayer2 <= 100 && cellValuePlayer2 >= 0;
            assert cellValueDraw <= 100 && cellValueDraw >= 0;
            //assert cellValueDraw + cellValuePlayer1 + cellValuePlayer2 == 100;
            cellDraw.setCellValue(cellValueDraw);
            cellPlayer1.setCellValue(cellValuePlayer1);
            cellPlayer2.setCellValue(cellValuePlayer2);
        }
        if (!resultsRandom.isEmpty()) {
            int file = 0;//hay que ir a buscar el randomfile
            Cell cellDraw = rowDraw.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC);
            Cell cellPlayer1 = rowPlayer1.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC);
            Cell cellPlayer2 = rowPlayer2.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC);
            cellDraw.setCellStyle(cellStyle);
            cellPlayer1.setCellStyle(cellStyle);
            cellPlayer2.setCellStyle(cellStyle);
            //                StatisticForCalc get = resultsRandom.get(randomPerceptronFile);
            //                Double cellValuePlayer1 = get.getWinRatePlayer1();
            Double cellValuePlayer1 = resultsRandom.get(randomPerceptronFile).getWinRatePlayer1();
            Double cellValuePlayer2 = resultsRandom.get(randomPerceptronFile).getWinRatePlayer2();
            Double cellValueDraw = resultsRandom.get(randomPerceptronFile).getDrawRate();
            //assert cellValueDraw + cellValuePlayer1 + cellValuePlayer2 == 100;

            cellPlayer1.setCellValue(cellValuePlayer1);
            cellPlayer2.setCellValue(cellValuePlayer2);
            cellDraw.setCellValue(cellValueDraw);
        }
        wb.write(outputXLSX);
    }
}

From source file:at.fh.swenga.firefighters.report.ExcelFireEngineReportView.java

@Override
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {

    List<FireEngineModel> fireEngines = (List<FireEngineModel>) model.get("fireEngines");

    // create a worksheet
    Sheet sheet = workbook.createSheet("FireEngine Report");

    // create style for header cells
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontName("Arial");
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    style.setFont(font);/*from   ww  w  .  j  a  v  a  2  s .  c  o m*/

    // create a new row in the worksheet
    Row headerRow = sheet.createRow(0);

    // create a new cell in the row
    Cell cell0 = headerRow.createCell(0);
    cell0.setCellValue("ID");
    cell0.setCellStyle(style);

    // create a new cell in the row
    Cell cell1 = headerRow.createCell(1);
    cell1.setCellValue("Modell");
    cell1.setCellStyle(style);

    // create a new cell in the row
    Cell cell2 = headerRow.createCell(2);
    cell2.setCellValue("Kennzeichen");
    cell2.setCellStyle(style);

    // create a new cell in the row
    Cell cell3 = headerRow.createCell(3);
    cell3.setCellValue("Leistung");
    cell3.setCellStyle(style);

    // create a new cell in the row
    Cell cell4 = headerRow.createCell(4);
    cell4.setCellValue("Baujahr");
    cell4.setCellStyle(style);

    // create a new cell in the row
    Cell cell5 = headerRow.createCell(5);
    cell5.setCellValue("Aktiv");
    cell5.setCellStyle(style);

    // create a new cell in the row
    Cell cell6 = headerRow.createCell(6);
    cell6.setCellValue("Funktion");
    cell6.setCellStyle(style);

    // create a new cell in the row
    Cell cell7 = headerRow.createCell(7);
    cell7.setCellValue("Feuerwehr");
    cell7.setCellStyle(style);

    // create multiple rows with fireEngines data
    int rowNum = 1;
    for (FireEngineModel fireEngine : fireEngines) {
        // create the row data
        Row row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(fireEngine.getId());
        row.createCell(1).setCellValue(fireEngine.getModel());
        row.createCell(2).setCellValue(fireEngine.getLicensePlate());
        row.createCell(3).setCellValue(fireEngine.getPerformance());
        row.createCell(4).setCellValue(fireEngine.getBuildYear());
        row.createCell(5).setCellValue(fireEngine.getActive());
        row.createCell(6).setCellValue(fireEngine.getAbbreviation().getAbbreviation());
        row.createCell(7).setCellValue(fireEngine.getFireBrigade().getName());
    }

    // adjust column width to fit the content
    sheet.autoSizeColumn((short) 0);
    sheet.autoSizeColumn((short) 1);
    sheet.autoSizeColumn((short) 2);
    sheet.autoSizeColumn((short) 3);
    sheet.autoSizeColumn((short) 4);
    sheet.autoSizeColumn((short) 5);
    sheet.autoSizeColumn((short) 6);
    sheet.autoSizeColumn((short) 7);

}

From source file:bad.robot.excel.matchers.StubCell.java

License:Apache License

static Cell createCell(int row, int column, Date date) {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    Cell cell = sheet.createRow(row).createCell(column, CELL_TYPE_NUMERIC);
    cell.setCellValue(date);/*  www.j a v a2s.c  o  m*/
    CellStyle style = workbook.createCellStyle();
    style.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("m/d/yy h:mm"));
    cell.setCellStyle(style);
    return cell;
}

From source file:bad.robot.excel.style.ClonedStyleFactory.java

License:Apache License

@Override
public CellStyle create(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.cloneStyleFrom(source.getCellStyle());
    return style;
}

From source file:bad.robot.excel.style.NewStyleFactory.java

License:Apache License

@Override
public CellStyle create(Workbook workbook) {
    return workbook.createCellStyle();
}

From source file:balony.tableWriter.java

public static void writeTable(JFrame parent, Object[][] tableData, String[] colNames) {
    String opts[] = { "Tab-delimited text (raw)", "Excel .xls", "Excel 2007+ .xlsx" };
    int i = JOptionPane.showOptionDialog(parent, "Choose output format:", "Export Table",
            JOptionPane.DEFAULT_OPTION, JOptionPane.QUESTION_MESSAGE, null, opts, opts[0]);
    if (i == JOptionPane.CLOSED_OPTION) {
        return;//from  w  w w . j av  a 2  s.c  o  m
    }

    String[] chars = { ":", "[", "]", "/", "\\", "?", "*" };
    String fname = parent.getTitle();
    for (String c : chars) {
        fname = fname.replace(c, "");
    }

    JFileChooser jfc = new JFileChooser();
    if (i == 0) {
        jfc.setFileFilter(new FileNameExtensionFilter("Tab-delimited text files", "txt"));
        jfc.setSelectedFile(new File(fname.concat(".txt")));
    }

    if (i == 1) {
        jfc.setFileFilter(new FileNameExtensionFilter("Excel .xls files", "xls"));
        jfc.setSelectedFile(new File(fname.concat(".xls")));
    }

    if (i == 2) {
        jfc.setFileFilter(new FileNameExtensionFilter("Excel .xlsx files", "xlsx"));
        jfc.setSelectedFile(new File(fname.concat(".xlsx")));
    }

    int rv = jfc.showSaveDialog(parent);
    if (rv == JFileChooser.APPROVE_OPTION) {
        File f = jfc.getSelectedFile();

        try {

            if (i == 0) {
                BufferedWriter out = new BufferedWriter(new FileWriter(f));
                for (String columnName : colNames) {
                    out.write(columnName);
                    out.write("\t");
                }
                out.newLine();
                for (Object[] tableData1 : tableData) {
                    for (int k = 0; k < colNames.length; k++) {
                        if (tableData1[k] != null) {
                            out.write(tableData1[k].toString());
                        }
                        out.write("\t");
                    }
                    out.newLine();
                }
                out.close();
            }

            if (i > 0) {

                Workbook wb;

                if (i == 1) {
                    wb = new HSSFWorkbook();
                } else {
                    wb = new XSSFWorkbook();
                }

                Sheet sheet = wb.createSheet(fname);
                Row r;
                CellStyle style;
                sheet.createFreezePane(0, 1);

                for (int j = 0; j < tableData.length; j++) {
                    r = sheet.createRow(j + 1);
                    for (int k = 0; k < colNames.length; k++) {

                        if (tableData[j][k] != null) {
                            Cell c = r.createCell(k);

                            if (tableData[j][k] instanceof Integer) {
                                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                                int v = ((Integer) tableData[j][k]);
                                c.setCellValue(v);
                            } else {
                                if (tableData[j][k] instanceof Double) {
                                    c.setCellType(Cell.CELL_TYPE_NUMERIC);
                                    double v = ((Double) tableData[j][k]);
                                    c.setCellValue(v);
                                } else {

                                    c.setCellType(Cell.CELL_TYPE_STRING);
                                    c.setCellValue(tableData[j][k].toString());

                                }
                            }
                        }
                    }
                }

                r = sheet.createRow(0);
                style = wb.createCellStyle();
                Font font = wb.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                style.setFont(font);

                for (int k = 0; k < colNames.length; k++) {
                    Cell c = r.createCell(k);
                    c.setCellType(Cell.CELL_TYPE_STRING);
                    c.setCellStyle(style);
                    c.setCellValue(colNames[k]);
                }

                FileOutputStream fos = new FileOutputStream(f);
                wb.write(fos);
                fos.close();
            }

        } catch (Exception ex) {
            Logger.getLogger(dataTable.class.getName()).log(Level.SEVERE, null, ex);
            JOptionPane.showMessageDialog(parent, "File error: ".concat(ex.getLocalizedMessage()), "File Error",
                    JOptionPane.ERROR_MESSAGE);
            return;
        }
        int n = JOptionPane.showOptionDialog(parent, "File saved. Open in default application?", "Message",
                JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE, null, null, null);
        if (n == JOptionPane.YES_OPTION) {
            try {
                Desktop d = Desktop.getDesktop();
                d.open(f);
            } catch (IOException e) {
                System.out.println(e.getLocalizedMessage());
            }
        }
    }
}

From source file:bandaru_excelreadwrite.WritetoExcel.java

public void writeSongsListToExcel(List<Song> songList) {

    /*// www.  j  av a2s  . co  m
    Use XSSF for xlsx format and for xls use HSSF
     */
    Workbook workbook = new XSSFWorkbook();

    /*
    create new sheet 
     */
    Sheet songsSheet = workbook.createSheet("Albums");

    XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle();
    /* Create XSSFFont object from the workbook */
    XSSFFont my_font = (XSSFFont) workbook.createFont();

    /*
    setting cell color
     */
    CellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

    /*
     setting Header color
     */
    CellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());
    style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style2.setAlignment(style2.ALIGN_CENTER);

    Row rowName = songsSheet.createRow(1);

    /*
    Merging the cells
     */
    songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));

    /*
    Applying style to attribute name
     */
    int nameCellIndex = 1;
    Cell namecell = rowName.createCell(nameCellIndex++);
    namecell.setCellValue("Name");
    namecell.setCellStyle(style);

    Cell cel = rowName.createCell(nameCellIndex++);
    cel.setCellValue("Bandaru, Sreekanth");

    /*
    Applying underline to Name
     */
    my_font.setUnderline(XSSFFont.U_SINGLE);
    my_style.setFont(my_font);
    /* Attaching the style to the cell */
    CellStyle combined = workbook.createCellStyle();
    combined.cloneStyleFrom(my_style);
    combined.cloneStyleFrom(style);
    combined.setAlignment(combined.ALIGN_CENTER);

    cel.setCellStyle(combined);

    /*
    Applying  colors to header 
     */
    Row rowMain = songsSheet.createRow(3);
    SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("3");

    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIME.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:F4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    /*
    setting new rule to apply alternate colors to cells having same Genre
     */
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:F5"), CellRangeAddress.valueOf("A6:F6"),
            CellRangeAddress.valueOf("A7:F7"), CellRangeAddress.valueOf("A8:F8"),
            CellRangeAddress.valueOf("A13:F13"), CellRangeAddress.valueOf("A14:F14"),
            CellRangeAddress.valueOf("A15:F15"), CellRangeAddress.valueOf("A16:F16"),
            CellRangeAddress.valueOf("A23:F23"), CellRangeAddress.valueOf("A24:F24"),
            CellRangeAddress.valueOf("A25:F25"), CellRangeAddress.valueOf("A26:F26")

    };

    /*        
    setting new rule to apply alternate colors to cells having same Fenre
     */
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill3 = rule3.createPatternFormatting();
    fill3.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:F9"), CellRangeAddress.valueOf("A10:F10"),
            CellRangeAddress.valueOf("A11:F11"), CellRangeAddress.valueOf("A12:F12"),
            CellRangeAddress.valueOf("A17:F17"), CellRangeAddress.valueOf("A18:F18"),
            CellRangeAddress.valueOf("A19:F19"), CellRangeAddress.valueOf("A20:F20"),
            CellRangeAddress.valueOf("A21:F21"), CellRangeAddress.valueOf("A22:F22"),
            CellRangeAddress.valueOf("A27:F27"), CellRangeAddress.valueOf("A28:F28"),
            CellRangeAddress.valueOf("A29:F29") };

    /*
    Applying above created rule formatting to cells
     */
    sheetCF.addConditionalFormatting(regionsAction, rule2);
    sheetCF.addConditionalFormatting(regionsAdv, rule3);

    /*
     Setting coloumn header values
     */
    int mainCellIndex = 0;
    CellStyle style4 = workbook.createCellStyle();
    XSSFFont my_font2 = (XSSFFont) workbook.createFont();
    my_font2.setBold(true);
    style4.setFont(my_font2);
    rowMain.setRowStyle(style4);
    rowMain.createCell(mainCellIndex++).setCellValue("SNO");
    rowMain.createCell(mainCellIndex++).setCellValue("Genre");
    rowMain.createCell(mainCellIndex++).setCellValue("Rating");
    rowMain.createCell(mainCellIndex++).setCellValue("Movie Name");
    rowMain.createCell(mainCellIndex++).setCellValue("Director");
    rowMain.createCell(mainCellIndex++).setCellValue("Release Date");

    /*
    populating cell values
     */
    int rowIndex = 4;
    int sno = 1;
    for (Song song : songList) {
        if (song.getSno() != 0) {

            Row row = songsSheet.createRow(rowIndex++);
            int cellIndex = 0;

            /*
            first place in row is Sno
             */
            row.createCell(cellIndex++).setCellValue(sno++);

            /*
            second place in row is  Genre
             */
            row.createCell(cellIndex++).setCellValue(song.getGenre());

            /*
            third place in row is Critic score
             */
            row.createCell(cellIndex++).setCellValue(song.getCriticscore());

            /*
            fourth place in row is Album name
             */
            row.createCell(cellIndex++).setCellValue(song.getAlbumname());

            /*
            fifth place in row is Artist
             */
            row.createCell(cellIndex++).setCellValue(song.getArtist());

            /*
            sixth place in row is marks in date
             */
            if (song.getReleasedate() != null) {

                Cell date = row.createCell(cellIndex++);

                DataFormat format = workbook.createDataFormat();
                CellStyle dateStyle = workbook.createCellStyle();

                dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy"));
                date.setCellStyle(dateStyle);

                date.setCellValue(song.getReleasedate());

                /*
                auto-resizing columns
                 */
                songsSheet.autoSizeColumn(6);
                songsSheet.autoSizeColumn(5);
                songsSheet.autoSizeColumn(4);
                songsSheet.autoSizeColumn(3);
                songsSheet.autoSizeColumn(2);
            }

        }
    }

    /*
    writing this workbook to excel file.
     */
    try {
        FileOutputStream fos = new FileOutputStream(FILE_PATH);
        workbook.write(fos);
        fos.close();

        System.out.println(FILE_PATH + " is successfully written");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}