Example usage for org.apache.poi.ss.usermodel SheetConditionalFormatting addConditionalFormatting

List of usage examples for org.apache.poi.ss.usermodel SheetConditionalFormatting addConditionalFormatting

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. SheetConditionalFormatting
  6. addConditionalFormatting

Introduction

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

Prototype

int addConditionalFormatting(CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules);

Source Link

Document

Add a new Conditional Formatting set to the sheet.

Usage

From source file:bandaru_excelreadwrite.WritetoExcel.java

public void writeSongsListToExcel(List<Song> songList) {

    /*/*from   w  w  w  .j  a  v  a2  s.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();
    }

}

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

public void writeSongsListToExcel(List<Song> songList) {

    /*/* w  w  w .j  a  v a 2 s  .com*/
    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();
    }

}

From source file:com.mycompany.gannaraputakehomeexam.WritingToExcel.java

public void writeSongsToExcel(List<SongsList> songList) {

    /*/*from  ww  w  . ja va2s .  c  om*/
    Use XSSF for xlsx format and for xls use HSSF
    */
    Workbook workbook = new XSSFWorkbook();

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

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

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

    /*
     setting Header color
    */
    CellStyle style2 = workbook.createCellStyle();
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    style2.setFont(font);
    style2.setAlignment(CellStyle.ALIGN_CENTER);
    //   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("Gannarapu, Anirudh");

    /*
    Applying underline to Name
    */
    my_font.setUnderline(XSSFFont.U_DOUBLE);
    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("5");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.RED.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 Genre
     */
    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;

    Cell SNO = rowMain.createCell(mainCellIndex++);
    SNO.setCellValue("SNO");
    SNO.setCellStyle(style2);
    Cell gen = rowMain.createCell(mainCellIndex++);
    gen.setCellValue("Genre");
    gen.setCellStyle(style2);
    Cell credit = rowMain.createCell(mainCellIndex++);
    credit.setCellValue("Credit Score");
    credit.setCellStyle(style2);
    Cell name = rowMain.createCell(mainCellIndex++);
    name.setCellValue("Album Name");
    name.setCellStyle(style2);
    Cell art = rowMain.createCell(mainCellIndex++);
    art.setCellValue("Artist");
    art.setCellStyle(style2);
    Cell release = rowMain.createCell(mainCellIndex++);
    release.setCellValue("Release Date");
    release.setCellStyle(style2);

    /*
    populating cell values
    */
    int rowIndex = 4;
    int sno = 1;
    for (SongsList 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();
    }

}

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

public void writeSongsListToExcel(List<Song> songList) {

    /*//from   www  .  ja v  a2s .com
    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);

    CellStyle style1 = workbook.createCellStyle();//Create style
    Font font = workbook.createFont();//Create font
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);//Make font bold
    style1.setFont(font);//set it to bold
    style1.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle style13 = workbook.createCellStyle();//Create style
    Font font13 = workbook.createFont();//Create font
    style13.setAlignment(CellStyle.ALIGN_LEFT);

    CellStyle style14 = workbook.createCellStyle();//Create style
    Font font14 = workbook.createFont();//Create font
    style14.setAlignment(CellStyle.ALIGN_RIGHT);
    /*
     setting Header color
    */
    CellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
    style2.setFillBackgroundColor(IndexedColors.LIGHT_ORANGE.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++);

    /*
    Applying underline to Name
    */
    Font underlineFont = workbook.createFont();
    underlineFont.setUnderline(HSSFFont.U_SINGLE);

    /* Attaching the style to the cell */
    CellStyle combined = workbook.createCellStyle();
    combined.setFont(underlineFont);
    cel.setCellStyle(combined);
    cel.setCellValue("Gayam, Prathibha");

    /*
    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.LIGHT_ORANGE.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;

    Cell cell0 = rowMain.createCell(mainCellIndex++);
    cell0.setCellValue("SNO");
    cell0.setCellStyle(style1);
    Cell cell1 = rowMain.createCell(mainCellIndex++);
    cell1.setCellValue("Genre");
    cell1.setCellStyle(style1);
    Cell cell2 = rowMain.createCell(mainCellIndex++);
    cell2.setCellValue("Credit Score");
    cell2.setCellStyle(style1);
    Cell cell3 = rowMain.createCell(mainCellIndex++);
    cell3.setCellValue("Album Name");
    cell3.setCellStyle(style1);
    Cell cell4 = rowMain.createCell(mainCellIndex++);
    cell4.setCellValue("Artist");
    cell4.setCellStyle(style1);
    Cell cell5 = rowMain.createCell(mainCellIndex++);
    cell5.setCellValue("Release Date");
    cell5.setCellStyle(style1);

    /*
    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
            */

            Cell cell20 = row.createCell(cellIndex++);
            cell20.setCellValue(sno++);
            cell20.setCellStyle(style14);

            /*
            second place in row is  Genre
            */
            Cell cell21 = row.createCell(cellIndex++);
            cell21.setCellValue(song.getGenre());
            cell21.setCellStyle(style13);

            /*
            third place in row is Critic score
            */
            Cell cell22 = row.createCell(cellIndex++);
            cell22.setCellValue(song.getCriticscore());
            cell22.setCellStyle(style14);

            /*
            fourth place in row is Album name
            */
            Cell cell23 = row.createCell(cellIndex++);
            cell23.setCellValue(song.getAlbumname());
            cell23.setCellStyle(style13);

            /*
            fifth place in row is Artist
            */
            Cell cell24 = row.createCell(cellIndex++);
            cell24.setCellValue(song.getArtist());
            cell24.setCellStyle(style13);

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

}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * Highlight multiple cells based on a formula
 *//*from   ww  w.  ja  v  a  2 s.c  om*/
static void multiCell(Sheet sheet) {
    // header row
    Row row0 = sheet.createRow(0);
    row0.createCell(0).setCellValue("Units");
    row0.createCell(1).setCellValue("Cost");
    row0.createCell(2).setCellValue("Total");

    Row row1 = sheet.createRow(1);
    row1.createCell(0).setCellValue(71);
    row1.createCell(1).setCellValue(29);
    row1.createCell(2).setCellValue(2059);

    Row row2 = sheet.createRow(2);
    row2.createCell(0).setCellValue(85);
    row2.createCell(1).setCellValue(29);
    row2.createCell(2).setCellValue(2059);

    Row row3 = sheet.createRow(3);
    row3.createCell(0).setCellValue(71);
    row3.createCell(1).setCellValue(29);
    row3.createCell(2).setCellValue(2059);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =$B2>75   (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$A2>75");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:C4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(4).setCellValue("<== Condition 1: Formula Is =$B2>75   (Blue Fill)");
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 *  Use Excel conditional formatting to check for errors,
 *  and change the font colour to match the cell colour.
 *  In this example, if formula result is  #DIV/0! then it will have white font colour.
 *///w  ww .j av  a  2s .  c  o  m
static void errors(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(0);
    sheet.createRow(2).createCell(0).setCellFormula("ROUND(A1/A2,0)");
    sheet.createRow(3).createCell(0).setCellValue(0);
    sheet.createRow(4).createCell(0).setCellFormula("ROUND(A6/A4,0)");
    sheet.createRow(5).createCell(0).setCellValue(41);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =ISERROR(C2)   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISERROR(A1)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(1).setCellValue(
            "<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
    sheet.getRow(4).createCell(1).setCellValue(
            "<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to hide the duplicate values,
 * and make the list easier to read. In this example, when the table is sorted by Region,
 * the second (and subsequent) occurences of each region name will have white font colour.
 *//*from   w ww  . j a va 2 s . c o  m*/
static void hideDupplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("City");
    sheet.createRow(1).createCell(0).setCellValue("Boston");
    sheet.createRow(2).createCell(0).setCellValue("Boston");
    sheet.createRow(3).createCell(0).setCellValue("Chicago");
    sheet.createRow(4).createCell(0).setCellValue("Chicago");
    sheet.createRow(5).createCell(0).setCellValue("New York");

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("A2=A1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A6") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(1).createCell(1)
            .setCellValue("<== the second (and subsequent) "
                    + "occurences of each region name will have white font colour.  "
                    + "Condition: Formula Is   =A2=A1   (White Font)");
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to highlight duplicate entries in a column.
 *//*from   w ww. j  a v  a  2s .c  o  m*/
static void formatDuplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Code");
    sheet.createRow(1).createCell(0).setCellValue(4);
    sheet.createRow(2).createCell(0).setCellValue(3);
    sheet.createRow(3).createCell(0).setCellValue(6);
    sheet.createRow(4).createCell(0).setCellValue(3);
    sheet.createRow(5).createCell(0).setCellValue(5);
    sheet.createRow(6).createCell(0).setCellValue(8);
    sheet.createRow(7).createCell(0).setCellValue(0);
    sheet.createRow(8).createCell(0).setCellValue(2);
    sheet.createRow(9).createCell(0).setCellValue(8);
    sheet.createRow(10).createCell(0).setCellValue(6);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A11") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  "
            + "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1   (Blue Font)");
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to highlight items that are in a list on the worksheet.
 *///w  w w  .  j  a  va  2s. c o  m
static void inList(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Codes");
    sheet.createRow(1).createCell(0).setCellValue("AA");
    sheet.createRow(2).createCell(0).setCellValue("BB");
    sheet.createRow(3).createCell(0).setCellValue("GG");
    sheet.createRow(4).createCell(0).setCellValue("AA");
    sheet.createRow(5).createCell(0).setCellValue("FF");
    sheet.createRow(6).createCell(0).setCellValue("XX");
    sheet.createRow(7).createCell(0).setCellValue("CC");

    sheet.getRow(0).createCell(2).setCellValue("Valid");
    sheet.getRow(1).createCell(2).setCellValue("AA");
    sheet.getRow(2).createCell(2).setCellValue("BB");
    sheet.getRow(3).createCell(2).setCellValue("CC");

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A8") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(3).setCellValue(
            "<== Use Excel conditional formatting to highlight items that are in a list on the worksheet");
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 *  Use Excel conditional formatting to highlight payments that are due in the next thirty days.
 *  In this example, Due dates are entered in cells A2:A4.
 *//*  w w w  .j  a va  2  s .c  om*/
static void expiry(Sheet sheet) {
    CellStyle style = sheet.getWorkbook().createCellStyle();
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm"));

    sheet.createRow(0).createCell(0).setCellValue("Date");
    sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
    sheet.createRow(2).createCell(0).setCellFormula("A2+1");
    sheet.createRow(3).createCell(0).setCellFormula("A3+1");

    for (int rownum = 1; rownum <= 3; rownum++)
        sheet.getRow(rownum).getCell(0).setCellStyle(style);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF
            .createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);

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

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}