Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

Source Link

Document

Creates new cell range.

Usage

From source file:com.l3.info.magenda.emplois_du_temps.Jour.java

void writeInSheet(Workbook workbook, Sheet sheet, int x_sem, int y_sem) {
    Row[] row = new Row[1 + this.getNbrLigne()];
    row[0] = sheet.createRow(y_sem);//from w w  w .j ava  2 s .c  o  m
    for (int i = 1; i < 1 + this.getNbrLigne(); i++) {
        row[i] = sheet.createRow(y_sem + i);
        row[i].setHeight(Workbook.PixelsToTwips(64));
    }

    int x = x_sem, y = 0;
    Cell cell = row[y].createCell(x);
    cell.setCellValue(this.nom_jour);
    cell.setCellStyle(workbook.getCellStyle("nom_du_jour"));
    x++;
    for (int i = START_HOUR_OF_DAY; i <= END_HOUR_OF_DAY; i++) {
        cell = row[y].createCell(x);
        cell.setCellValue(i + "h");
        cell.setCellStyle(workbook.getCellStyle("case_gauche_jour"));
        cell = row[y].createCell(x + 1);
        cell.setCellStyle(workbook.getCellStyle("case_droite_jour"));
        x += 2;
    }

    for (int i = 1; i <= this.getNbrLigne(); i++) {
        x = x_sem + 1;
        for (int j = (END_HOUR_OF_DAY - START_HOUR_OF_DAY + 1); j > 0; j--) {
            cell = row[i].createCell(x);
            cell.setCellStyle(workbook.getCellStyle("case_gauche_jour"));
            cell = row[i].createCell(x + 1);
            cell.setCellStyle(workbook.getCellStyle("case_droite_jour"));
            x += 2;
        }
    }

    x = x_sem;
    y = y_sem;

    for (int i = 1; i <= this.getNbrLigne(); i++) {
        cell = row[i].createCell(x);
        cell.setCellStyle(workbook.getCellStyle("nom_du_jour"));
    }
    sheet.addMergedRegion(new CellRangeAddress(y + 1, y + getNbrLigne(), x, x));

    x = x_sem + 1;
    y = y_sem + 1;
    for (Horaire h : horaire_du_jour) {
        cell = row[1 + h.getLigne()].createCell((x + h.getColonne()));
        cell.setCellValue(h.toString());
        cell.setCellStyle(workbook.getCellStyle("Style_" + h.getExamen().getDiplome()));
        for (int i = 1; i < h.getPlage_horraire(); i++) {
            cell = row[1 + h.getLigne()].createCell((x + h.getColonne() + i));
            cell.setCellStyle(workbook.getCellStyle("Style_" + h.getExamen().getDiplome()));
        }

        if (h.getPlage_horraire() > 1) {
            sheet.addMergedRegion(new CellRangeAddress(y + h.getLigne(), (y + h.getLigne()),
                    (x + h.getColonne()), (int) (x + h.getColonne() + h.getPlage_horraire() - 1)));
        }
    }

}

From source file:com.l3.info.magenda.emplois_du_temps.Semaine.java

void writeInSheet(Workbook workbook, int week_of_year) {

    XSSFWorkbook xssfWorkbook = workbook.getWorkbook();
    Sheet sheet = xssfWorkbook.createSheet("Sem. " + week_of_year);
    Row row = sheet.createRow((short) 0);

    row.setHeight(Workbook.PixelsToTwips(64));
    Cell cell = row.createCell((short) 0);

    // first row (0-based) - last row  (0-based) - first column (0-based) -last column  (0-based)
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (20 - 7) * 2 + 2));

    // Cree une nouvelle police
    Font font = xssfWorkbook.createFont();
    font.setFontHeightInPoints((short) 18);
    font.setFontName("Arial");

    // Fonts are set into a style so create a new one to use.
    XSSFCellStyle style = xssfWorkbook.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setFont(font);//from   ww  w . jav a 2 s.  com

    // Create a cell and put a value in it.
    cell.setCellValue("Semaine " + this.num_semaine);
    cell.setCellStyle(style);

    sheet.setDefaultRowHeight((short) 500);

    int x = 0, y = 2;
    for (Jour day : days_of_week) {
        day.writeInSheet(workbook, sheet, x, y);
        y += 2 + day.getNbrLigne();
    }
}

From source file:com.lapis.jsfexporter.excel.ExcelExportType.java

License:Apache License

@Override
public Row exportRow(IExportRow row) {
    Row xlsRow = sheet.createRow(rowCount++);
    int cellIndex = 0;
    for (IExportCell cell : row.getCells()) {
        boolean cellIsUsed;
        do {/*from w  w  w  .j a  v a  2 s.  co m*/
            cellIsUsed = false;
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress region = sheet.getMergedRegion(i);
                if (region.isInRange(xlsRow.getRowNum(), cellIndex)) {
                    cellIsUsed = true;
                    cellIndex += region.getLastColumn() - region.getFirstColumn() + 1;
                }
            }
        } while (cellIsUsed);

        Cell xlsCell = xlsRow.createCell(cellIndex++);
        xlsCell.setCellValue(cell.getValue());

        if (cell.getColumnSpanCount() > 1 || cell.getRowSpanCount() > 1) {
            sheet.addMergedRegion(new CellRangeAddress(xlsCell.getRowIndex(),
                    xlsCell.getRowIndex() + cell.getRowSpanCount() - 1, xlsCell.getColumnIndex(),
                    xlsCell.getColumnIndex() + cell.getColumnSpanCount() - 1));
            cellIndex += cell.getColumnSpanCount() - 1;
        }
    }
    return xlsRow;
}

From source file:com.learn.core.utils.HSSFReadWrite.java

License:Apache License

/**
 * given a filename this outputs a sample sheet with just a set of
 * rows/cells.//from w w  w  .j a v  a  2 s  . c  om
 */
private static void testCreateSampleSheet(String outputFilename) throws IOException {
    try (HSSFWorkbook wb = new HSSFWorkbook()) {
        HSSFSheet s = wb.createSheet();
        HSSFCellStyle cs = wb.createCellStyle();
        HSSFCellStyle cs2 = wb.createCellStyle();
        HSSFCellStyle cs3 = wb.createCellStyle();
        HSSFFont f = wb.createFont();
        HSSFFont f2 = wb.createFont();

        f.setFontHeightInPoints((short) 12);
        f.setColor((short) 0xA);
        f.setBold(true);
        f2.setFontHeightInPoints((short) 10);
        f2.setColor((short) 0xf);
        f2.setBold(true);
        cs.setFont(f);
        cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
        cs2.setBorderBottom(BorderStyle.THIN);
        cs2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs2.setFillForegroundColor((short) 0xA);
        cs2.setFont(f2);
        wb.setSheetName(0, "HSSF Test");
        int rownum;
        for (rownum = 0; rownum < 300; rownum++) {
            HSSFRow r = s.createRow(rownum);
            if ((rownum % 2) == 0) {
                r.setHeight((short) 0x249);
            }

            for (int cellnum = 0; cellnum < 50; cellnum += 2) {
                HSSFCell c = r.createCell(cellnum);
                c.setCellValue(
                        rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
                if ((rownum % 2) == 0) {
                    c.setCellStyle(cs);
                }
                c = r.createCell(cellnum + 1);
                c.setCellValue(new HSSFRichTextString("TEST"));
                // 50 characters divided by 1/20th of a point
                s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05));
                if ((rownum % 2) == 0) {
                    c.setCellStyle(cs2);
                }
            }
        }

        // draw a thick black border on the row at the bottom using BLANKS
        rownum++;
        rownum++;
        HSSFRow r = s.createRow(rownum);
        cs3.setBorderBottom(BorderStyle.THICK);
        for (int cellnum = 0; cellnum < 50; cellnum++) {
            HSSFCell c = r.createCell(cellnum);
            c.setCellStyle(cs3);
        }
        s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3));
        s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110));

        // end draw thick black border
        // create a sheet, set its title then delete it
        wb.createSheet();
        wb.setSheetName(1, "DeletedSheet");
        wb.removeSheetAt(1);

        // end deleted sheet
        try (FileOutputStream out = new FileOutputStream(outputFilename)) {
            wb.write(out);
        }
    }
}

From source file:com.linus.excel.poi.MergingCells.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");

    Row row = sheet.createRow((short) 1);
    Cell cell = row.createCell((short) 1);
    cell.setCellValue(new XSSFRichTextString("This is a test of merging"));

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("excel/merging_cells.xlsx");
    wb.write(fileOut);//from www . j a  v  a 2  s  .c om
    fileOut.close();
}

From source file:com.lition.service.impl.OwnedServiceImpl.java

/**
 * POI??//from  ww  w. j av  a2s. c  om
 */
@Override
public InputStream getOutExcelDate() {
    //1.?
    String headTitle[] = { "id", "?", "??", "", "??" };

    //2.Dao??
    List<OwnedVehicle> list = dao.queryAll();

    //3.?HSSFWorkbook
    HSSFWorkbook wb = new HSSFWorkbook();

    //4.?sheet
    HSSFSheet sheet = wb.createSheet("?");

    //5.?
    HSSFCellStyle style = wb.createCellStyle();
    // ??
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    //6.
    HSSFRow row0 = sheet.createRow(0);
    //7.??
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

    HSSFCell row0cell0 = row0.createCell(0);
    row0cell0.setCellValue("?");
    row0cell0.setCellStyle(style);

    HSSFRow row1 = sheet.createRow(1);

    for (int i = 0; i < headTitle.length; i++) {
        HSSFCell row0cell = row1.createCell(i);
        row0cell.setCellValue(headTitle[i]);
        row0cell.setCellStyle(style);
    }

    for (int i = 0; i < list.size(); i++) {
        HSSFRow row = sheet.createRow(i + 2);

        //ID
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellValue(list.get(i).getId());
        cell0.setCellStyle(style);

        //?
        HSSFCell cell1 = row.createCell(1);
        cell1.setCellValue(list.get(i).getVehicleId());
        cell1.setCellStyle(style);

        //??
        HSSFCell cell2 = row.createCell(2);
        cell2.setCellValue(list.get(i).getDepid());
        cell2.setCellStyle(style);

        //
        HSSFCell cell3 = row.createCell(3);
        cell3.setCellValue(list.get(i).getModel());
        cell3.setCellStyle(style);

        //??
        HSSFCell cell4 = row.createCell(4);
        cell4.setCellValue(list.get(i).getVehicleUsageId());
        cell4.setCellStyle(style);
    }

    //?inputstream;
    try {
        OutputStream out = new FileOutputStream("abc.xls");
        wb.write(out);
        out.close();
        InputStream in = new FileInputStream("abc.xls");
        return in;
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return null;
}

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

License:Apache License

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

License:Open Source License

public void appendNamedRegion(DataFrame data, String name, boolean header) {
    Sheet sheet = workbook.getSheet(getName(name).getSheetName());
    // top, left, bottom, right
    int[] coord = getReferenceCoordinates(name);
    writeData(data, sheet, coord[2] + 1, coord[1], header);
    int bottom = coord[2] + data.rows();
    int right = Math.max(coord[1] + data.columns() - 1, coord[3]);
    CellRangeAddress cra = new CellRangeAddress(coord[0], bottom, coord[1], right);
    String formula = cra.formatAsString(sheet.getSheetName(), true);
    createName(name, formula, true);//from w w w  .ja  v a 2 s.c o m
}

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

public void writeSongsListToExcel(List<Song> songList) {

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

}

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

public void writeSongsToExcel(List<SongsList> songList) {

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

}