Example usage for org.apache.poi.ss.usermodel Row createCell

List of usage examples for org.apache.poi.ss.usermodel Row createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. createCell

Introduction

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

Prototype

Cell createCell(int column);

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:com.movielabs.availslib.AvailXML.java

License:Open Source License

protected int createT(int j, Row row, AvailSeriesMetadataType seriesMetadata) {
    Cell cell;/*from   w  w w. j ava2 s  . co  m*/

    // CompanyDisplayCredit (T)
    List<CompanyCreditsType> credits = seriesMetadata.getCompanyDisplayCredit();
    if (credits.size() == 1) {
        List<CompanyCreditsType.DisplayString> displayStrings = credits.get(0).getDisplayString();
        if (credits.size() == 1) {
            cell = row.createCell(j++);
            cell.setCellValue(displayStrings.get(0).getValue());
        }
    } else { // TODO warnings
        j++;
    }

    return j;
}

From source file:com.movielabs.availslib.AvailXML.java

License:Open Source License

protected int createTransactions1(int j, Row row, AvailTransType trans) {
    Cell cell;/*from  w  w  w  . j  av a2s  .  c o m*/

    // LicenseType
    cell = row.createCell(j++);
    cell.setCellValue(trans.getLicenseType());

    // LicenseRightsDescription
    cell = row.createCell(j++);
    cell.setCellValue(trans.getLicenseRightsDescription());

    // FormatProfile
    cell = row.createCell(j++);
    cell.setCellValue(trans.getFormatProfile());

    // Start
    cell = row.createCell(j++);
    XMLGregorianCalendar gc = trans.getStart();
    cell.setCellValue(String.format("%04d-%02d-%02d", gc.getYear(), gc.getMonth(), gc.getDay()));

    // End
    cell = row.createCell(j++);
    gc = trans.getEnd();
    if (gc != null) {
        cell.setCellValue(String.format("%04d-%02d-%02d", gc.getYear(), gc.getMonth(), gc.getDay()));
    } else {
        cell.setCellValue(trans.getEndCondition());
    }

    return j;
}

From source file:com.movielabs.availslib.AvailXML.java

License:Open Source License

protected int createTransactions2(int j, Row row, AvailTransType trans, Hack h) {
    Cell cell;//  w w  w .  j a va 2 s .c o  m

    List<AvailTransType.Term> terms = trans.getTerm();

    // PriceType & PriceValue
    // TODO need sanity check
    String SRP = "";
    h.suppressionLiftDate = "";
    h.rentalDuration = null;
    h.watchDuration = null;
    h.fixedEndDate = "";
    h.anyTerm = "";
    h.holdbackLanguage = "";
    h.holdbackExclusionLanguage = "";
    int count[] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
    for (AvailTransType.Term t : terms) { // this is n^2 but there aren't many terms so big deal
        String name = t.getTermName();
        switch (name.toLowerCase()) {
        case "tier":
            if (count[0]++ == 0) {
                cell = row.createCell(j++);
                cell.setCellValue(name);
                cell = row.createCell(j++);
                double d = Double.parseDouble(t.getText());
                cell.setCellValue(String.format("%.0f", d));
            } else {
                logger.warn("duplicate 'tier' term");
            }
            break;
        case "category":
            if (count[1]++ == 0) {
                cell = row.createCell(j++);
                cell.setCellValue(name);
                cell = row.createCell(j++);
                cell.setCellValue(t.getText());
            } else {
                logger.warn("duplicate 'category' term");
            }
            break;
        case "wsp":
            if (count[2]++ == 0) {
                cell = row.createCell(j++);
                cell.setCellValue(name);
                cell = row.createCell(j++);
                cell.setCellValue(String.format("%.2f", t.getMoney().getValue().doubleValue()));
            } else {
                logger.warn("duplicate 'WSP' term");
            }
            break;
        case "srp":
            if (count[3]++ == 0) {
                SRP = String.format("%.2f", t.getMoney().getValue().doubleValue()); // Extract and Save
            } else {
                logger.warn("duplicate 'SRP' term");
            }
            break;
        case "announcedate":
            if (count[4]++ == 0) {
                h.suppressionLiftDate = t.getEvent();
            } else {
                logger.warn("duplicate 'AnnounceDate' term");
            }
            break;
        case "rentalduration":
            if (count[5]++ == 0) {
                h.rentalDuration = t.getDuration();
            } else {
                logger.warn("duplicate 'RentalDuration' term");
            }
            break;
        case "watchduration":
            if (count[6]++ == 0) {
                h.watchDuration = t.getDuration();
            } else {
                logger.warn("duplicate 'WatchDuration' term");
            }
            break;
        case "fixedenddate":
            if (count[7]++ == 0) {
                h.fixedEndDate = t.getEvent();
            } else {
                logger.warn("duplicate 'FixedEndDate' term");
            }
            break;
        case "any":
            if (count[8]++ == 0) {
                h.anyTerm = t.getText();
            } else {
                logger.warn("duplicate 'Any' term");
            }
            break;
        case "holdbacklanguage":
            if (count[9]++ == 0) {
                h.holdbackLanguage = t.getLanguage();
            } else {
                logger.warn("duplicate 'HoldbackLanguage' term");
            }
            break;
        case "holdbackexclusionlanguage":
            if (count[10]++ == 0) {
                h.holdbackExclusionLanguage = t.getLanguage();
            } else {
                logger.warn("duplicate 'HoldbackExclusionLanguage' term");
            }
            break;
        default:
            logger.warn("unknown term: ", t.getTermName());
        }
    }

    // SRP
    cell = row.createCell(j++);
    cell.setCellValue(SRP);

    // Description
    cell = row.createCell(j++);
    if (trans.getDescription().equals("---missing---"))
        cell.setCellValue("");
    else
        cell.setCellValue(trans.getDescription());

    // Other Terms
    // TODO not right
    cell = row.createCell(j++);
    cell.setCellValue("");

    // OtherInstructions
    cell = row.createCell(j++);
    cell.setCellValue(trans.getOtherInstructions());

    return j;
}

From source file:com.movielabs.availslib.AvailXML.java

License:Open Source License

protected int createMetadataAndTransactions(int j, Row row, AvailType avail, AvailTransType trans,
        AvailMetadataType metadata, Hack h) {
    Cell cell;//from w ww.  j  av  a2  s .  c om

    // ReleaseYear
    cell = row.createCell(j++);
    cell.setCellValue(metadata.getReleaseDate());

    // ReleaseHistoryOriginal
    String releaseHistoryPhysicalHV = "";
    String releaseHistoryOriginal = "";
    cell = row.createCell(j++);
    List<ReleaseHistoryType> relHistory = metadata.getReleaseHistory();
    for (ReleaseHistoryType rh : relHistory) {
        String rhType = rh.getReleaseType().getValue();
        switch (rhType) {
        case "DVD":
            releaseHistoryPhysicalHV = rh.getDate().getValue();
            break;
        case "original":
            releaseHistoryOriginal = rh.getDate().getValue();
            break;
        default:
            logger.warn("unknown release history: " + rhType);
            break;
        }
    }
    cell.setCellValue(releaseHistoryOriginal);

    // ReleaseHistoryPhysicalHV
    cell = row.createCell(j++);
    cell.setCellValue(releaseHistoryPhysicalHV);

    // ExceptionFlag
    cell = row.createCell(j++);
    if (avail.isExceptionFlag() != null && avail.isExceptionFlag().booleanValue())
        cell.setCellValue("YES");
    else
        cell.setCellValue("");

    ContentRatingType ratings = metadata.getRatings();
    if (ratings == null) {
        j += 3;
    } else {
        List<ContentRatingDetailType> rlist = ratings.getRating();
        if (rlist.size() != 1)
            logger.warn("more than one rating specified");
        // RatingSystem
        cell = row.createCell(j++);
        cell.setCellValue(rlist.get(0).getSystem());

        // RatingValue
        cell = row.createCell(j++);
        cell.setCellValue(rlist.get(0).getValue());

        // RatingReason
        cell = row.createCell(j++);
        List<String> reasons = rlist.get(0).getReason();
        int i = 0;
        String s = "";
        int tmp = reasons.size();
        for (String r : reasons) {
            if (i++ > 0)
                s += ",";
            s += r;
        }
        cell.setCellValue(s);
    }

    // RentalDuration
    cell = row.createCell(j++);
    if (h.rentalDuration != null)
        cell.setCellValue(h.rentalDuration.getHours());

    // WatchDuration
    cell = row.createCell(j++);
    if (h.watchDuration != null)
        cell.setCellValue(h.watchDuration.getHours());

    // FixedEndDate
    cell = row.createCell(j++);
    if (h.fixedEndDate != null)
        cell.setCellValue(h.fixedEndDate);

    return j;
}

From source file:com.movielabs.availslib.AvailXML.java

License:Open Source License

protected int createLastEight(int j, Row row, AvailType avail, AvailTransType trans, AvailMetadataType metadata,
        Hack h) {//from ww w.  j a  v a  2  s.  c  o  m
    Cell cell;

    // CaptionIncluded & CaptionExemption
    BigInteger exemption = metadata.getUSACaptionsExemptionReason();
    if (exemption != null) {
        cell = row.createCell(j++);
        cell.setCellValue("No"); // CaptionIncluded
        cell = row.createCell(j++);
        cell.setCellValue(exemption.intValue()); // CaptionExemption
    } else {
        cell = row.createCell(j++);
        cell.setCellValue("Yes"); // CaptionIncluded
        j++; // CaptionExemption
    }

    // Any
    cell = row.createCell(j++);
    cell.setCellValue(h.anyTerm);

    // ContractID
    cell = row.createCell(j++);
    cell.setCellValue(trans.getContractID());

    // ServiceProvider
    cell = row.createCell(j++);
    if (avail.getServiceProvider() != null)
        cell.setCellValue(avail.getServiceProvider().getDisplayName());

    // TotalRunTime
    cell = row.createCell(j++);
    Duration runLength = metadata.getRunLength();
    if (runLength.getHours() != 0 || runLength.getMinutes() != 0 || runLength.getSeconds() != 0) {
        cell.setCellValue(String.format("%d:%02d:%02d", runLength.getHours(), runLength.getMinutes(),
                runLength.getSeconds()));
    }

    // HoldbackLanguage
    cell = row.createCell(j++);
    cell.setCellValue(h.holdbackLanguage);

    // HoldbackExclusionLanguage
    cell = row.createCell(j++);
    cell.setCellValue(h.holdbackExclusionLanguage);

    return j;
}

From source file:com.movielabs.availslib.AvailXML.java

License:Open Source License

protected void addMovieRow(AvailType avail) {
    Row row = movieSheet.createRow(currentMovieRow++);
    int j = 0;/*ww w .  j  av  a2  s . c  o m*/
    Cell cell;

    AvailAssetType asset = avail.getAsset().get(0);

    List<AvailTransType> transactions = avail.getTransaction();
    if (transactions.size() != 1)
        logger.warn("more than one transaction");
    AvailTransType trans = transactions.get(0);

    j = createAtoE(j, row, avail, asset, trans);

    // TitleInternalAlias
    AvailMetadataType metadata = asset.getMetadata();
    cell = row.createCell(j++);
    cell.setCellValue(metadata.getTitleInternalAlias());

    // TitleDisplayUnlimited
    cell = row.createCell(j++);
    cell.setCellValue(metadata.getTitleDisplayUnlimited().getValue());

    // LocalizationType
    cell = row.createCell(j++);
    cell.setCellValue(metadata.getLocalizationOffering());

    // I-M common
    j = createTransactions1(j, row, trans);

    Hack h = new Hack();

    // N-S common
    j = createTransactions2(j, row, trans, h);

    // ContentID
    cell = row.createCell(j++);
    cell.setCellValue(asset.getContentID());

    // ProductID
    cell = row.createCell(j++);
    cell.setCellValue(metadata.getEditEIDRS());

    // EncodeID
    cell = row.createCell(j++);
    cell.setCellValue(metadata.getEncodeID());

    // AvailID
    cell = row.createCell(j++);
    if (avail.getALID().equals("---missing---"))
        cell.setCellValue("");
    else
        cell.setCellValue(avail.getALID());

    // Metadata
    cell = row.createCell(j++);

    // AltID
    cell = row.createCell(j++);
    List<AvailMetadataType.AltIdentifier> altId = metadata.getAltIdentifier();
    if (altId != null && altId.size() == 1)
        cell.setCellValue(altId.get(0).getIdentifier());
    else
        logger.warn("more than one or null alternate identifier");

    // SuppressionLiftDate
    cell = row.createCell(j++);
    cell.setCellValue(h.suppressionLiftDate);

    // SpecialPreOrderFulfillDate
    // TODO not right
    j++;

    // ReleaseYear, ReleaseHistoryOriginal, ReleaseHistoryPhysicalHV, ExceptionFlag, RatingSystem,
    // RatingValue, RatingReason, RentalDuration, & WatchDuration
    j = createMetadataAndTransactions(j, row, avail, trans, metadata, h);

    // CaptionIncluded, CaptionExemption, Any, ContractID, ServiceProvider, TotalRunTime,
    // HoldbackLanguage, & HoldbackExclusionLanguage
    j = createLastEight(j, row, avail, trans, metadata, h);
}

From source file:com.movielabs.availslib.AvailXML.java

License:Open Source License

protected void addEpisodeRow(AvailType avail) {
    Row row = episodeSheet.createRow(currentEpisodeRow++);
    int j = 0;/*from  ww  w. j a  v  a2  s . com*/
    Cell cell;

    AvailAssetType asset = avail.getAsset().get(0);

    List<AvailTransType> transactions = avail.getTransaction();
    if (transactions.size() != 1)
        logger.warn("more than one transaction");
    AvailTransType trans = transactions.get(0);

    j = createAtoE(j, row, avail, asset, trans);

    AvailEpisodeMetadataType episodeMetadata = asset.getEpisodeMetadata();
    AvailSeasonMetadataType seasonMetadata = episodeMetadata.getSeasonMetadata();
    AvailSeriesMetadataType seriesMetadata = seasonMetadata.getSeriesMetadata();

    j = createFtoH(j, row, seriesMetadata, seasonMetadata);

    // EpisodeNumber (I)
    cell = row.createCell(j++);
    cell.setCellValue(episodeMetadata.getEpisodeNumber().getNumber());

    // LocalizationType (J)
    cell = row.createCell(j++);
    cell.setCellValue(episodeMetadata.getLocalizationOffering());

    // EpisodeTitleInternalAlias (K)
    cell = row.createCell(j++);
    cell.setCellValue(episodeMetadata.getTitleInternalAlias());

    // EpisodeTitleDisplayUnlimited (L)
    AvailMetadataType.TitleDisplayUnlimited tDU = episodeMetadata.getTitleDisplayUnlimited();
    if (tDU != null) {
        cell = row.createCell(j++);
        cell.setCellValue(tDU.getValue());
    } else {
        j++;
    }

    // M-R common
    j = createMtoR(j, row, seriesMetadata, seasonMetadata);

    // EpisodeAltID (S)
    List<AvailMetadataType.AltIdentifier> episodeAltId = episodeMetadata.getAltIdentifier();
    if (episodeAltId != null && episodeAltId.size() == 1) {
        cell = row.createCell(j++);
        cell.setCellValue(episodeAltId.get(0).getIdentifier());
    } else {
        j++;
    }

    // CompanyDisplayCredit (T)
    j = createT(j, row, seriesMetadata);

    // U-Y common
    j = createTransactions1(j, row, trans);

    // SpecialPreOrderFulfillDate (Z)
    // TODO not right
    j++;

    // AA-AF common
    Hack h = new Hack();
    j = createTransactions2(j, row, trans, h);

    // SeriesContentID (AG)
    cell = row.createCell(j++);
    cell.setCellValue(seriesMetadata.getSeriesContentID());

    // SeasonContentID (AH)
    cell = row.createCell(j++);
    cell.setCellValue(seasonMetadata.getSeasonContentID());

    // EpisodeContentID (AI)
    cell = row.createCell(j++);
    cell.setCellValue(asset.getContentID());

    // EpisodeProductID (AJ)
    cell = row.createCell(j++);
    cell.setCellValue(episodeMetadata.getEditEIDRS());

    // EncodeID (AK)
    cell = row.createCell(j++);
    cell.setCellValue(episodeMetadata.getEncodeID());

    // AvailID (AL)
    cell = row.createCell(j++);
    cell.setCellValue(avail.getALID());

    // Metadata (AM)
    // TODO what is this?
    j++;

    // SuppressionLiftDate (AN)
    cell = row.createCell(j++);
    cell.setCellValue(h.suppressionLiftDate);

    // ReleaseYear (AO), ReleaseHistoryOriginal (AP), ReleaseHistoryPhysicalHV (AQ),
    // ExceptionFlag (AR), RatingSystem (AS), RatingValue (AT), RatingReason (AU),
    // RentalDuration (AV), WatchDuration (AW), & FixedEndDate (AX)
    j = createMetadataAndTransactions(j, row, avail, trans, episodeMetadata, h);

    // CaptionIncluded, CaptionExemption, Any, ContractID, ServiceProvider, TotalRunTime,
    // HoldbackLanguage, & HoldbackExclusionLanguage
    j = createLastEight(j, row, avail, trans, episodeMetadata, h);

}

From source file:com.movielabs.availslib.AvailXML.java

License:Open Source License

protected void addSeasonRow(AvailType avail) {
    Row row = episodeSheet.createRow(currentEpisodeRow++);
    int j = 0;/*from  w w w  .j  av  a2 s .com*/
    Cell cell;

    AvailAssetType asset = avail.getAsset().get(0);

    List<AvailTransType> transactions = avail.getTransaction();
    if (transactions.size() != 1)
        logger.warn("more than one transaction");
    AvailTransType trans = transactions.get(0);

    j = createAtoE(j, row, avail, asset, trans);

    AvailSeasonMetadataType seasonMetadata = asset.getSeasonMetadata();
    AvailSeriesMetadataType seriesMetadata = seasonMetadata.getSeriesMetadata();

    j = createFtoH(j, row, seriesMetadata, seasonMetadata);

    // no EpisodeNumber, LocalizationType, EpisodeTitleInternalAlias, or EpisodeTitleDisplayUnlimited (I-L)
    j += 4;

    // M-R common
    j = createMtoR(j, row, seriesMetadata, seasonMetadata);

    // no EpisodeAltID (S)
    j++;

    // CompanyDisplayCredit (T)
    j = createT(j, row, seriesMetadata);

    // U-Y common
    j = createTransactions1(j, row, trans);

    // SpecialPreOrderFulfillDate (Z)
    // TODO not right
    j++;

    // AA-AF common
    Hack h = new Hack();
    j = createTransactions2(j, row, trans, h);

    // SeriesContentID (AG)
    cell = row.createCell(j++);
    cell.setCellValue(seriesMetadata.getSeriesContentID());

    // SeasonContentID (AH)
    cell = row.createCell(j++);
    cell.setCellValue(seasonMetadata.getSeasonContentID());

    // no EpisodeContentID, EpisodeProductID, or EncodeID (AI-AK)
    j += 3;

    // AvailID (AL)
    cell = row.createCell(j++);
    cell.setCellValue(avail.getALID());

    // Metadata (AM)
    // TODO what is this?
    j++;

    // SuppressionLiftDate (AN)
    cell = row.createCell(j++);
    cell.setCellValue(h.suppressionLiftDate);

    // ReleaseYear (AO)
    cell = row.createCell(j++);
    cell.setCellValue(seasonMetadata.getReleaseDate());

    // ReleaseHistoryOriginal (AP), ReleaseHistoryPhysicalHV (AQ)
    j += 2;

    // ExceptionFlag (AR)
    cell = row.createCell(j++);
    if (avail.isExceptionFlag() != null && avail.isExceptionFlag().booleanValue())
        cell.setCellValue("YES");
    else
        cell.setCellValue("");

    // RatingSystem (AS), RatingValue (AT), RatingReason (AU), RentalDuration (AV),
    // WatchDuration (AW), FixedEndDate (AX), CaptionIncluded (AY), CaptionExemption (AZ)
    j += 8;

    // Any (BA)
    cell = row.createCell(j++);
    cell.setCellValue(h.anyTerm);
}

From source file:com.msopentech.odatajclient.engine.performance.PerfTestReporter.java

License:Open Source License

public static void main(final String[] args) throws Exception {
    // 1. check input directory
    final File reportdir = new File(args[0] + File.separator + "target" + File.separator + "surefire-reports");
    if (!reportdir.isDirectory()) {
        throw new IllegalArgumentException("Expected directory, got " + args[0]);
    }//from   ww w  .  j  a v  a 2  s  .com

    // 2. read test data from surefire output
    final File[] xmlReports = reportdir.listFiles(new FilenameFilter() {

        @Override
        public boolean accept(final File dir, final String name) {
            return name.endsWith("-output.txt");
        }
    });

    final Map<String, Map<String, Double>> testData = new TreeMap<String, Map<String, Double>>();

    for (File xmlReport : xmlReports) {
        final BufferedReader reportReader = new BufferedReader(new FileReader(xmlReport));
        try {
            while (reportReader.ready()) {
                String line = reportReader.readLine();
                final String[] parts = line.substring(0, line.indexOf(':')).split("\\.");

                final String testClass = parts[0];
                if (!testData.containsKey(testClass)) {
                    testData.put(testClass, new TreeMap<String, Double>());
                }

                line = reportReader.readLine();

                testData.get(testClass).put(parts[1],
                        Double.valueOf(line.substring(line.indexOf(':') + 2, line.indexOf('['))));
            }
        } finally {
            IOUtils.closeQuietly(reportReader);
        }
    }

    // 3. build XSLX output (from template)
    final HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(args[0] + File.separator + "src"
            + File.separator + "test" + File.separator + "resources" + File.separator + XLS));

    for (Map.Entry<String, Map<String, Double>> entry : testData.entrySet()) {
        final Sheet sheet = workbook.getSheet(entry.getKey());

        int rows = 0;

        for (Map.Entry<String, Double> subentry : entry.getValue().entrySet()) {
            final Row row = sheet.createRow(rows++);

            Cell cell = row.createCell(0);
            cell.setCellValue(subentry.getKey());

            cell = row.createCell(1);
            cell.setCellValue(subentry.getValue());
        }
    }

    final FileOutputStream out = new FileOutputStream(
            args[0] + File.separator + "target" + File.separator + XLS);
    try {
        workbook.write(out);
    } finally {
        IOUtils.closeQuietly(out);
    }
}

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

public void writeSongsListToExcel(List<Song> songList) {

    /*/*w ww .  j  a v a2s. 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();
    }

}