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:com.linus.excel.poi.AligningCells.java

License:Apache License

/**
 * Creates a cell and aligns it a certain way.
 *
 * @param wb     the workbook/*from www. j  ava  2 s . c  om*/
 * @param row    the row to create the cell in
 * @param column the column number to create the cell in
 * @param halign the horizontal alignment for the cell.
 */
private static void createCell(Workbook wb, Row row, short column, short halign, short valign) {
    CreationHelper ch = wb.getCreationHelper();
    Cell cell = row.createCell(column);
    cell.setCellValue(ch.createRichTextString("Align It"));
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);
    cell.setCellStyle(cellStyle);
}

From source file:com.lufs.java.apache.poi.example.CalendarDemo.java

License:Apache License

public static void main(String[] args) throws Exception {

    Calendar calendar = Calendar.getInstance();
    boolean xlsx = true;
    for (String arg : args) {
        if (arg.charAt(0) == '-') {
            xlsx = arg.equals("-xlsx");
        } else {//from www . j av a2 s.c o m
            calendar.set(Calendar.YEAR, Integer.parseInt(arg));
        }
    }
    int year = calendar.get(Calendar.YEAR);

    try (Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook()) {

        Map<String, CellStyle> styles = createStyles(wb);

        for (int month = 0; month < 12; month++) {
            calendar.set(Calendar.MONTH, month);
            calendar.set(Calendar.DAY_OF_MONTH, 1);
            //create a sheet for each month
            Sheet sheet = wb.createSheet(months[month]);

            //turn off gridlines
            sheet.setDisplayGridlines(false);
            sheet.setPrintGridlines(false);
            sheet.setFitToPage(true);
            sheet.setHorizontallyCenter(true);
            PrintSetup printSetup = sheet.getPrintSetup();
            printSetup.setLandscape(true);

            //the following three statements are required only for HSSF
            sheet.setAutobreaks(true);
            printSetup.setFitHeight((short) 1);
            printSetup.setFitWidth((short) 1);

            //the header row: centered text in 48pt font
            Row headerRow = sheet.createRow(0);
            headerRow.setHeightInPoints(80);
            Cell titleCell = headerRow.createCell(0);
            titleCell.setCellValue(months[month] + " " + year);
            titleCell.setCellStyle(styles.get("title"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

            //header with month titles
            Row monthRow = sheet.createRow(1);
            for (int i = 0; i < days.length; i++) {
                //set column widths, the width is measured in units of 1/256th of a character width
                sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide
                sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide
                sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
                Cell monthCell = monthRow.createCell(i * 2);
                monthCell.setCellValue(days[i]);
                monthCell.setCellStyle(styles.get("month"));
            }

            int cnt = 1, day = 1;
            int rownum = 2;
            for (int j = 0; j < 6; j++) {
                Row row = sheet.createRow(rownum++);
                row.setHeightInPoints(100);
                for (int i = 0; i < days.length; i++) {
                    Cell dayCell_1 = row.createCell(i * 2);
                    Cell dayCell_2 = row.createCell(i * 2 + 1);

                    int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                    if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                        dayCell_1.setCellValue(day);
                        calendar.set(Calendar.DAY_OF_MONTH, ++day);

                        if (i == 0 || i == days.length - 1) {
                            dayCell_1.setCellStyle(styles.get("weekend_left"));
                            dayCell_2.setCellStyle(styles.get("weekend_right"));
                        } else {
                            dayCell_1.setCellStyle(styles.get("workday_left"));
                            dayCell_2.setCellStyle(styles.get("workday_right"));
                        }
                    } else {
                        dayCell_1.setCellStyle(styles.get("grey_left"));
                        dayCell_2.setCellStyle(styles.get("grey_right"));
                    }
                    cnt++;
                }
                if (calendar.get(Calendar.MONTH) > month)
                    break;
            }
        }

        // Write the output to a file
        String file = "calendar.xls";
        if (wb instanceof XSSFWorkbook)
            file += "x";

        try (FileOutputStream out = new FileOutputStream(file)) {
            wb.write(out);
        }
    }
}

From source file:com.lushapp.common.excel.ExportExcel.java

License:Apache License

private HSSFWorkbook exportExcelInUserModel2File(String title, Class<T> pojoClass, Collection<T> dataSet) {
    // /* ww  w.j ava 2  s.c o m*/
    HSSFWorkbook workbook = null;
    try {
        // 
        workbook = new HSSFWorkbook();
        // ?
        Sheet sheet = workbook.createSheet(title);

        // 
        List<String> exportFieldTitle = new ArrayList<String>();
        List<Integer> exportFieldWidth = new ArrayList<Integer>();
        // ???get
        List<Method> methodObj = new ArrayList<Method>();
        Map<String, Method> convertMethod = new HashMap<String, Method>();
        Class superClazz = null;
        Field fileds[] = new Field[0];
        boolean flag = true;
        while (flag) {
            if (superClazz != null) {
                superClazz = superClazz.getSuperclass();
            } else {
                superClazz = pojoClass.getSuperclass();
            }
            if (superClazz.isInstance(Object.class)) {
                flag = false;
            } else {
                Field[] sf = superClazz.getDeclaredFields();
                if (sf != null && sf.length > 0) {
                    for (int m = 0; m < sf.length; m++) {
                        fileds = ArrayUtils.addAll(fileds, sf[m]);
                    }
                }
            }

        }
        // 
        Field cfileds[] = pojoClass.getDeclaredFields();
        if (cfileds != null && cfileds.length > 0) {
            for (int n = 0; n < cfileds.length; n++) {
                fileds = ArrayUtils.addAll(fileds, cfileds[n]);
            }
        }
        // ??filed
        for (int i = 0; i < fileds.length; i++) {
            Field field = fileds[i];
            Excel excel = field.getAnnotation(Excel.class);
            // annottion
            if (excel != null) {
                // 
                exportFieldTitle.add(excel.exportName());
                // 
                exportFieldWidth.add(excel.exportFieldWidth());
                // ?
                String fieldname = field.getName();
                // System.out.println(i+""+excel.exportName()+" "+excel.exportFieldWidth());
                StringBuffer getMethodName = new StringBuffer("get");
                getMethodName.append(fieldname.substring(0, 1).toUpperCase());
                getMethodName.append(fieldname.substring(1));

                Method getMethod = pojoClass.getMethod(getMethodName.toString(), new Class[] {});

                methodObj.add(getMethod);
                if (excel.exportConvert() == true) {
                    //----------------------------------------------------------------
                    //update-begin--Author:Quainty  Date:20130524 for[8]excel
                    // get/setXxxxConvert??? ?Entity?
                    StringBuffer getConvertMethodName = new StringBuffer("convertGet");
                    getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());
                    getConvertMethodName.append(fieldname.substring(1));
                    //getConvertMethodName.append("Convert");
                    //update-end--Author:Quainty  Date:20130524 for[8]excel
                    //----------------------------------------------------------------
                    // System.out.println("convert: "+getConvertMethodName.toString());
                    Method getConvertMethod = pojoClass.getMethod(getConvertMethodName.toString(),
                            new Class[] {});
                    convertMethod.put(getMethodName.toString(), getConvertMethod);
                }
            }
        }
        int index = 0;
        // 
        Row row = sheet.createRow(index);
        row.setHeight((short) 450);
        CellStyle titleStyle = getTitleStyle(workbook);
        for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {
            Cell cell = row.createCell(i);
            // cell.setCellStyle(style);
            RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i));
            cell.setCellValue(text);
            cell.setCellStyle(titleStyle);
        }

        // ?
        for (int i = 0; i < exportFieldWidth.size(); i++) {
            // 256=65280/255
            sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));
        }
        Iterator its = dataSet.iterator();
        // ??
        while (its.hasNext()) {
            // 
            index++;
            row = sheet.createRow(index);
            row.setHeight((short) 350);
            Object t = its.next();
            for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) {
                Cell cell = row.createCell(k);
                Method getMethod = methodObj.get(k);
                Object value = null;
                if (convertMethod.containsKey(getMethod.getName())) {
                    Method cm = convertMethod.get(getMethod.getName());
                    value = cm.invoke(t, new Object[] {});
                } else {
                    value = getMethod.invoke(t, new Object[] {});
                }
                cell.setCellValue(value == null ? "" : value.toString());

                if (index % 2 == 0)
                    cell.setCellStyle(getTwoStyle(workbook));
                else
                    cell.setCellStyle(getOneStyle(workbook));
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return workbook;
}

From source file:com.lw.common.utils.ExcelUtil.java

/**
 * excel/*from  w w  w .jav a2 s. co  m*/
 * @param list ?
 * @param keys listmapkey?
 * @param columnNames excel??
 * */
public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) {
    // excel
    Workbook wb = new HSSFWorkbook();
    // sheet??
    Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
    // ???n?
    for (int i = 0; i < keys.length; i++) {
        sheet.setColumnWidth((short) i, (short) (35.7 * 150));
    }

    // 
    Row row = sheet.createRow((short) 0);

    // ???
    CellStyle cs = wb.createCellStyle();
    CellStyle cs2 = wb.createCellStyle();

    // ?
    Font f = wb.createFont();
    Font f2 = wb.createFont();

    // ????
    f.setFontHeightInPoints((short) 10);
    f.setColor(IndexedColors.BLACK.getIndex());
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // ??
    f2.setFontHeightInPoints((short) 10);
    f2.setColor(IndexedColors.BLACK.getIndex());

    //        Font f3=wb.createFont();
    //        f3.setFontHeightInPoints((short) 10);
    //        f3.setColor(IndexedColors.RED.getIndex());

    // ?????
    cs.setFont(f);
    cs.setBorderLeft(CellStyle.BORDER_THIN);
    cs.setBorderRight(CellStyle.BORDER_THIN);
    cs.setBorderTop(CellStyle.BORDER_THIN);
    cs.setBorderBottom(CellStyle.BORDER_THIN);
    cs.setAlignment(CellStyle.ALIGN_CENTER);

    // ???
    cs2.setFont(f2);
    cs2.setBorderLeft(CellStyle.BORDER_THIN);
    cs2.setBorderRight(CellStyle.BORDER_THIN);
    cs2.setBorderTop(CellStyle.BORDER_THIN);
    cs2.setBorderBottom(CellStyle.BORDER_THIN);
    cs2.setAlignment(CellStyle.ALIGN_CENTER);
    //??
    for (int i = 0; i < columnNames.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(columnNames[i]);
        cell.setCellStyle(cs);
    }
    //??
    for (short i = 1; i < list.size(); i++) {
        // Row ,Cell  , Row  Cell 0
        // sheet
        Row row1 = sheet.createRow((short) i);
        // row
        for (short j = 0; j < keys.length; j++) {
            Cell cell = row1.createCell(j);
            cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
            cell.setCellStyle(cs2);
        }
    }
    return wb;
}

From source file:com.lwr.software.reporter.restservices.ReportExportService.java

License:Open Source License

public Response exportExcel(Report toExport, Set<ReportParameter> reportParams) {
    Workbook wb = new XSSFWorkbook();

    Font boldFont = wb.createFont();
    boldFont.setBold(true);//from  w  w  w . j a  va  2  s .  c  om

    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setBorderBottom(BorderStyle.THIN);
    headerStyle.setBorderTop(BorderStyle.THIN);
    headerStyle.setBorderRight(BorderStyle.THIN);
    headerStyle.setBorderLeft(BorderStyle.THIN);
    headerStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
    headerStyle.setFont(boldFont);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);

    CellStyle titleStyle = wb.createCellStyle();
    titleStyle.setBorderBottom(BorderStyle.THIN);
    titleStyle.setBorderTop(BorderStyle.THIN);
    titleStyle.setBorderRight(BorderStyle.THIN);
    titleStyle.setBorderLeft(BorderStyle.THIN);

    List<RowElement> rows = toExport.getRows();
    int sheetIndex = 0;
    for (RowElement rowElement : rows) {
        List<Element> elements = rowElement.getElements();
        for (Element element : elements) {
            try {
                element.setParams(reportParams);
                element.init();
            } catch (Exception e) {
                logger.error("Unable to init '" + element.getTitle() + "' element of report '"
                        + toExport.getTitle() + "' Error " + e.getMessage(), e);
                return Response.serverError().entity("Unable to init '" + element.getTitle()
                        + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage()).build();
            }
            String sheetName = element.getTitle().substring(0,
                    element.getTitle().length() > 30 ? 30 : element.getTitle().length()) + (sheetIndex++);
            Sheet sheet = wb.createSheet(sheetName);

            Row reportTitleRow = sheet.createRow(0);
            Cell reportTitleHeader = reportTitleRow.createCell(0);
            reportTitleHeader.setCellStyle(headerStyle);
            reportTitleHeader.setCellValue("Report Title:");

            Cell reportTitleCell = reportTitleRow.createCell(1);
            reportTitleCell.setCellStyle(titleStyle);
            reportTitleCell.setCellValue(toExport.getTitle());

            Row elementTitleRow = sheet.createRow(1);
            Cell elementTitleHeader = elementTitleRow.createCell(0);
            elementTitleHeader.setCellStyle(headerStyle);
            elementTitleHeader.setCellValue("Element Title:");

            Cell elementTitleCell = elementTitleRow.createCell(1);
            elementTitleCell.setCellStyle(titleStyle);
            elementTitleCell.setCellValue(element.getTitle());

            List<List<Object>> dataToExport = element.getData();

            int rowIndex = 3;
            Row headerRow = sheet.createRow(rowIndex++);
            List<Object> unifiedHeaderRow = element.getHeader();
            for (int i = 0; i < unifiedHeaderRow.size(); i++) {
                Cell headerCell = headerRow.createCell(i);
                String headerCellValue = unifiedHeaderRow.get(i).toString();
                headerCell.setCellValue(headerCellValue);
                headerCell.setCellStyle(headerStyle);
            }
            for (int i = 0; i < dataToExport.size(); i++) {
                Row row = sheet.createRow(rowIndex++);
                List<Object> unifiedRow = dataToExport.get(i);
                int cellIndex = 0;
                for (Object cellValue : unifiedRow) {
                    Cell cell = row.createCell(cellIndex);
                    cell.setCellStyle(cellStyle);
                    try {
                        double val = Double.parseDouble(cellValue.toString());
                        cell.setCellValue(val);
                    } catch (NumberFormatException e) {
                        cell.setCellValue(cellValue.toString());
                    }
                    cellIndex++;
                }
            }
        }
    }
    try {
        File file = new File(DashboardConstants.APPLN_TEMP_DIR + System.nanoTime());
        logger.info("Export CSV temp file path is " + file.getAbsoluteFile());
        wb.write(new FileOutputStream(file));
        wb.close();
        ResponseBuilder responseBuilder = Response.ok((Object) file);
        responseBuilder.header("Content-Type",
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        responseBuilder.header("Content-Transfer-Encoding", "binary");
        responseBuilder.header("Content-Disposition", "attachment;filename=" + file.getName());
        responseBuilder.header("Content-Length", file.length());
        Response responseToSend = responseBuilder.build();
        file.deleteOnExit();
        return responseToSend;
    } catch (Exception e1) {
        return Response.serverError()
                .entity("Unable to export " + toExport.getTitle() + " report " + e1.getMessage()).build();
    }

}

From source file:com.marcosanta.controllers.ComscoreController.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);/*  ww  w. j a va 2s.  c o m*/
    CellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
    for (Row row : sheet) {
        for (Cell cell : row) {
            cell.setCellValue(cell.getStringCellValue().toUpperCase());
            cell.setCellStyle(style);
        }
    }
}

From source file:com.mimp.controllers.reporte.java

private static void copyRowStyle(Sheet worksheet, int sourceRowNum, int destinationRowNum) {
        // Coge la fila antigua y nueva
        Row newRow = worksheet.getRow(destinationRowNum);
        Row sourceRow = worksheet.getRow(sourceRowNum);

        //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna
        if (newRow != null) {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
        } else {/*from w w  w  .j a va 2  s .c  o m*/
            newRow = worksheet.createRow(destinationRowNum);
        }

        // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            // Copia la antigua y nueva celda
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell = newRow.createCell(i);

            // Si la anterior celda es null, evalua la siguiente celda defrente
            if (oldCell == null) {
                newCell = null;
                continue;
            }

            // Usa el estilo de la celda antigua
            newCell.setCellStyle(oldCell.getCellStyle());

            // Establece el tipo de valor de la celda
            newCell.setCellType(oldCell.getCellType());

            // Establece el valor de la celda
            //            switch (oldCell.getCellType()) {
            //                case Cell.CELL_TYPE_BLANK:
            //                    break;
            //                case Cell.CELL_TYPE_BOOLEAN:
            //                    newCell.setCellValue(oldCell.getBooleanCellValue());
            //                    break;
            //                case Cell.CELL_TYPE_ERROR:
            //                    newCell.setCellErrorValue(oldCell.getErrorCellValue());
            //                    break;
            //                case Cell.CELL_TYPE_FORMULA:
            //                    newCell.setCellFormula(oldCell.getCellFormula());
            //                    break;
            //                case Cell.CELL_TYPE_NUMERIC:
            //                    newCell.setCellValue(oldCell.getNumericCellValue());
            //                    break;
            //                case Cell.CELL_TYPE_STRING:
            //                    newCell.setCellValue(oldCell.getRichStringCellValue());
            //                    break;
            //            }
        }
    }

From source file:com.mimp.controllers.reporte.java

private static void copyRow(Sheet worksheet, int sourceRowNum, int destinationRowNum) {
        // Coge la fila antigua y nueva
        Row newRow = worksheet.getRow(destinationRowNum);
        Row sourceRow = worksheet.getRow(sourceRowNum);

        //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna
        if (newRow != null) {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
        } else {/* w w  w  .  ja  va2 s .  co m*/
            newRow = worksheet.createRow(destinationRowNum);
        }

        // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            // Copia la antigua y nueva celda
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell = newRow.createCell(i);

            // Si la anterior celda es null, evalua la siguiente celda defrente
            if (oldCell == null) {
                newCell = null;
                continue;
            }

            // Usa el estilo de la celda antigua
            newCell.setCellStyle(oldCell.getCellStyle());

            // Establece el tipo de valor de la celda
            newCell.setCellType(oldCell.getCellType());

            // Establece el valor de la celda
            switch (oldCell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getRichStringCellValue());
                break;
            }
        }
    }

From source file:com.miraisolutions.xlconnect.SSCellStyle.java

License:Open Source License

public static void set(Cell c, SSCellStyle cs) {
    c.setCellStyle(cs.cellStyle);
}

From source file:com.mycompany.excelreadandwrite.WritetoExcel.java

public void writeSongsListToExcel(List<Song> songList) {

    /*/*from  w  w w  .  j a va 2s. c o  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);

    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("Lastname, Firstname");

    /*
    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);
    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.CORNFLOWER_BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

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

    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:G5"), CellRangeAddress.valueOf("A6:G6"),
            CellRangeAddress.valueOf("A7:G7"), CellRangeAddress.valueOf("A8:G8"),
            CellRangeAddress.valueOf("A13:G13"), CellRangeAddress.valueOf("A14:G14"),
            CellRangeAddress.valueOf("A15:G15"), CellRangeAddress.valueOf("A16:G16"),
            CellRangeAddress.valueOf("A23:G23"), CellRangeAddress.valueOf("A24:G24"),
            CellRangeAddress.valueOf("A25:G25"), CellRangeAddress.valueOf("A26:G26")

    };

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

    CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:G9"), CellRangeAddress.valueOf("A10:G10"),
            CellRangeAddress.valueOf("A11:G11"), CellRangeAddress.valueOf("A12:G12"),
            CellRangeAddress.valueOf("A17:G17"), CellRangeAddress.valueOf("A18:G18"),
            CellRangeAddress.valueOf("A19:G19"), CellRangeAddress.valueOf("A20:G20"),
            CellRangeAddress.valueOf("A21:G21"), CellRangeAddress.valueOf("A22:G22"),
            CellRangeAddress.valueOf("A27:G27"), CellRangeAddress.valueOf("A28:G28"),
            CellRangeAddress.valueOf("A29:G29") };

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

    /*
     Setting coloumn header values
    */
    int mainCellIndex = 0;

    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");
    rowMain.createCell(mainCellIndex++).setCellValue("Budget");

    /*
    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();
    }

}