Example usage for org.apache.poi.ss.usermodel IndexedColors LIGHT_GREEN

List of usage examples for org.apache.poi.ss.usermodel IndexedColors LIGHT_GREEN

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. IndexedColors
  6. LIGHT_GREEN

Introduction

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

Prototype

IndexedColors LIGHT_GREEN

To view the source code for org.apache.poi.ss.usermodel IndexedColors LIGHT_GREEN.

Click Source Link

Usage

From source file:bandaru_excelreadwrite.WritetoExcel.java

public void writeSongsListToExcel(List<Song> songList) {

    /*/*ww  w  .  j  a  va  2 s.  c om*/
    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.asakusafw.testdata.generator.excel.WorkbookInfo.java

License:Apache License

/**
 * Creates a new instance.// w  ww . j a  va  2s. c  o m
 * @param workbook target workbook
 * @throws IllegalArgumentException if some parameters were {@code null}
 */
public WorkbookInfo(Workbook workbook) {
    if (workbook == null) {
        throw new IllegalArgumentException("workbook must not be null"); //$NON-NLS-1$
    }
    this.workbook = workbook;
    this.version = WorkbookGenerator.getSpreadsheetVersion(workbook);

    Font font = workbook.createFont();
    // font.setFontName("...");

    commonStyle = workbook.createCellStyle();
    commonStyle.setFont(font);
    commonStyle.setBorderTop(CellStyle.BORDER_THIN);
    commonStyle.setBorderBottom(CellStyle.BORDER_THIN);
    commonStyle.setBorderLeft(CellStyle.BORDER_THIN);
    commonStyle.setBorderRight(CellStyle.BORDER_THIN);

    titleStyle = workbook.createCellStyle();
    titleStyle.cloneStyleFrom(commonStyle);
    titleStyle.setLocked(true);
    titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    titleStyle.setAlignment(CellStyle.ALIGN_CENTER);

    lockedStyle = workbook.createCellStyle();
    lockedStyle.cloneStyleFrom(commonStyle);
    lockedStyle.setLocked(true);
    lockedStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    lockedStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
    lockedStyle.setAlignment(CellStyle.ALIGN_CENTER);

    optionsStyle = workbook.createCellStyle();
    optionsStyle.cloneStyleFrom(commonStyle);
    optionsStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    optionsStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());

    CreationHelper helper = workbook.getCreationHelper();
    DataFormat df = helper.createDataFormat();

    dataStyle = workbook.createCellStyle();
    dataStyle.cloneStyleFrom(commonStyle);

    dateDataStyle = workbook.createCellStyle();
    dateDataStyle.cloneStyleFrom(commonStyle);
    dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); //$NON-NLS-1$

    timeDataStyle = workbook.createCellStyle();
    timeDataStyle.cloneStyleFrom(commonStyle);
    timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); //$NON-NLS-1$

    datetimeDataStyle = workbook.createCellStyle();
    datetimeDataStyle.cloneStyleFrom(commonStyle);
    datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); //$NON-NLS-1$
}

From source file:com.asakusafw.testdriver.excel.WorkbookInfo.java

License:Apache License

/**
 * Creates a new instance./* www .  ja  v a 2  s .  co m*/
 * @param workbook target workbook
 * @throws IllegalArgumentException if some parameters were {@code null}
 */
public WorkbookInfo(Workbook workbook) {
    if (workbook == null) {
        throw new IllegalArgumentException("workbook must not be null"); //$NON-NLS-1$
    }
    this.workbook = workbook;

    Font font = workbook.createFont();

    commonStyle = workbook.createCellStyle();
    commonStyle.setFont(font);
    commonStyle.setBorderTop(CellStyle.BORDER_THIN);
    commonStyle.setBorderBottom(CellStyle.BORDER_THIN);
    commonStyle.setBorderLeft(CellStyle.BORDER_THIN);
    commonStyle.setBorderRight(CellStyle.BORDER_THIN);

    titleStyle = workbook.createCellStyle();
    titleStyle.cloneStyleFrom(commonStyle);
    titleStyle.setLocked(true);
    titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    titleStyle.setAlignment(CellStyle.ALIGN_CENTER);

    CreationHelper helper = workbook.getCreationHelper();
    DataFormat df = helper.createDataFormat();

    dataStyle = workbook.createCellStyle();
    dataStyle.cloneStyleFrom(commonStyle);

    dateDataStyle = workbook.createCellStyle();
    dateDataStyle.cloneStyleFrom(commonStyle);
    dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); //$NON-NLS-1$

    timeDataStyle = workbook.createCellStyle();
    timeDataStyle.cloneStyleFrom(commonStyle);
    timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); //$NON-NLS-1$

    datetimeDataStyle = workbook.createCellStyle();
    datetimeDataStyle.cloneStyleFrom(commonStyle);
    datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); //$NON-NLS-1$
}

From source file:com.asakusafw.testtools.templategen.ExcelBookBuilder.java

License:Apache License

private void configureColumnStyle() {
    assert workbook != null;
    HSSFFont font = workbook.createFont();
    font.setFontName(" ");

    commonStyle = workbook.createCellStyle();
    commonStyle.setFont(font);// w  ww.j a  va  2 s .  co m
    commonStyle.setBorderTop(CellStyle.BORDER_THIN);
    commonStyle.setBorderBottom(CellStyle.BORDER_THIN);
    commonStyle.setBorderLeft(CellStyle.BORDER_THIN);
    commonStyle.setBorderRight(CellStyle.BORDER_THIN);

    titleStyle = workbook.createCellStyle();
    titleStyle.cloneStyleFrom(commonStyle);
    titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    titleStyle.setAlignment(CellStyle.ALIGN_CENTER);

    centerAlignStyle = workbook.createCellStyle();
    centerAlignStyle.cloneStyleFrom(commonStyle);
    centerAlignStyle.setAlignment(CellStyle.ALIGN_CENTER);

    fixedValueStyle = workbook.createCellStyle();
    fixedValueStyle.cloneStyleFrom(commonStyle);
    fixedValueStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    fixedValueStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());

    centerAlignFixedValueStyle = workbook.createCellStyle();
    centerAlignFixedValueStyle.cloneStyleFrom(fixedValueStyle);
    centerAlignFixedValueStyle.setAlignment(CellStyle.ALIGN_CENTER);

    CreationHelper helper = workbook.getCreationHelper();
    DataFormat df = helper.createDataFormat();

    dateTimeStyle = workbook.createCellStyle();
    dateTimeStyle.cloneStyleFrom(commonStyle);
    dateTimeStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss"));

    dateStyle = workbook.createCellStyle();
    dateStyle.cloneStyleFrom(commonStyle);
    dateStyle.setDataFormat(df.getFormat("yyyy-mm-dd"));
}

From source file:com.cms.utils.ExportExcellCelltype.java

public CellStyle cellTypeColumnStt() {
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);
    cellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cellStyle.setWrapText(false);/* w  w  w .  j ava 2  s . c  o m*/
    return cellStyle;

}

From source file:com.cms.utils.ExportExcellCelltype.java

public CellStyle cellTypeHeader(Boolean wraptext) {
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);
    cellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cellStyle.setWrapText(wraptext);/*  w ww  .j av  a2  s .  co  m*/
    return cellStyle;
}

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

public void writeSongsToExcel(List<SongsList> songList) {

    /*//  ww w  . j a va2s .  c o m
    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.plugin.excel.util.ExcelFormatterApiImpl.java

License:Apache License

/**
 * It updates {@link ExcelCell} with required formatting.
 * /* w  ww.  j  a  v a 2s .  c o  m*/
 * @param list
 * @param backgroundColor
 * @return
 */
private List<ExcelCell> updateHeaderExcelCell(List<ExcelCell> list, int fontHeight) {

    if (list != null && !list.isEmpty()) {
        for (ExcelCell cell : list) {
            IndexedColors backgroundColor = null;
            if (StringUtils.isNotBlank(cell.getDisplayText())) {
                backgroundColor = cell.getDisplayText().endsWith("*") ? IndexedColors.LIGHT_GREEN
                        : IndexedColors.LIGHT_CORNFLOWER_BLUE;
            }
            updateExcelCell(cell, backgroundColor, null, fontHeight, true, false);
        }
    }

    return list;
}

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

License:Apache License

/**
 * Use Excel conditional formatting to shade alternating rows on the worksheet
 *//*from   w  w w.  j  ava2 s. com*/
static void shadeAlt(Sheet sheet) {
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

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

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
    sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is  =MOD(ROW(),2)   (Light Green Fill)");
}

From source file:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java

License:Open Source License

private void construitLigneExportCatalogueGeneric(Workbook wb, Row row, String libelle, int colonne,
        boolean mouvementStock) {
    Cell cell = row.createCell(colonne);
    cell.setCellValue(libelle);//from   w w w. j a v a2  s .  co m
    cell.setCellStyle(createCellWithBorderAndColor(wb, BorderStyle.MEDIUM,
            mouvementStock ? IndexedColors.LIGHT_GREEN : null, false));
}