Example usage for org.apache.poi.ss.usermodel Sheet createRow

List of usage examples for org.apache.poi.ss.usermodel Sheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. createRow

Introduction

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

Prototype

Row createRow(int rownum);

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

From source file:com.maogousoft.wuliu.controller.DriverController.java

public void exportExcel() throws IOException {
    StringBuffer from = new StringBuffer();
    from.append("from logistics_driver where status = 1 ");
    from.append(createOrder());/*from   w  w w  . j ava2  s  .  c o  m*/
    Page<Record> page = Db.paginate(getPageIndex(), 100000, "select * ", from.toString());
    List<Record> list = page.getList();
    Dict.fillDictToRecords(page.getList());

    String headers = "?|?|??|??|?|??|||?|??||?|??|?|??|?";
    String attributes = "id|phone|name|recommender|plate_number|id_card|car_type_str|car_length|car_weight|gold|regist_time|car_phone|start_province_str|start_city_str|end_province_str|end_city_str";

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet();
    Row headerRow = sheet.createRow(0);
    List<String> headerList = WuliuStringUtils.parseVertical(headers);
    for (int j = 0; j < headerList.size(); j++) {
        String attr = headerList.get(j);
        Cell cell = headerRow.createCell(j);
        cell.setCellValue(attr);
    }

    for (int i = 0; i < list.size(); i++) {
        Record record = list.get(i);
        Row row = sheet.createRow(i + 1);

        List<String> attrList = WuliuStringUtils.parseVertical(attributes);
        for (int j = 0; j < attrList.size(); j++) {
            String attr = attrList.get(j);
            Cell cell = row.createCell(j);
            Object value = getValue(record, attr);
            cell.setCellValue(value + "");
        }
    }

    HttpServletResponse resp = getResponse();
    String filename = TimeUtil.format(new Date(), "'?'yyyyMMdd_HHmmss'.xls'");
    resp.addHeader("Content-Disposition",
            "attachment;filename=" + new String(filename.getBytes("GBK"), "ISO-8859-1"));
    ServletOutputStream out = resp.getOutputStream();
    wb.write(out);
    out.close();
    renderNull();
}

From source file:com.maogousoft.wuliu.controller.DriverController.java

/**
 * //from  w ww.  ja  va  2 s  . co m
 * @description ? 
 * @author shevliu
 * @email shevliu@gmail.com
 * 201386 ?11:47:19
 * @throws IOException
 */
public void exportPendingAudit() throws IOException {
    StringBuffer from = new StringBuffer();
    from.append("from logistics_driver where status = 0 ");
    from.append(createOrder());
    Page<Record> page = Db.paginate(getPageIndex(), 100000, "select * ", from.toString());
    List<Record> list = page.getList();
    Dict.fillDictToRecords(page.getList());

    String headers = "?|?|??|??|?|??|||?|??||?|??|?|??|?";
    String attributes = "id|phone|name|recommender|plate_number|id_card|car_type_str|car_length|car_weight|gold|regist_time|car_phone|start_province_str|start_city_str|end_province_str|end_city_str";

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet();
    Row headerRow = sheet.createRow(0);
    List<String> headerList = WuliuStringUtils.parseVertical(headers);
    for (int j = 0; j < headerList.size(); j++) {
        String attr = headerList.get(j);
        Cell cell = headerRow.createCell(j);
        cell.setCellValue(attr);
    }

    for (int i = 0; i < list.size(); i++) {
        Record record = list.get(i);
        Row row = sheet.createRow(i + 1);
        List<String> attrList = WuliuStringUtils.parseVertical(attributes);
        for (int j = 0; j < attrList.size(); j++) {
            String attr = attrList.get(j);
            Cell cell = row.createCell(j);
            Object value = getValue(record, attr);
            cell.setCellValue(value + "");
        }
    }

    HttpServletResponse resp = getResponse();
    String filename = TimeUtil.format(new Date(), "'?'yyyyMMdd_HHmmss'.xls'");
    resp.addHeader("Content-Disposition",
            "attachment;filename=" + new String(filename.getBytes("GBK"), "ISO-8859-1"));
    ServletOutputStream out = resp.getOutputStream();
    wb.write(out);
    out.close();
    renderNull();
}

From source file:com.mechatronika.trackmchtr.ExportToExcel.java

private static void writeToExcel(String path) throws FileNotFoundException, IOException {
    new WorkbookFactory();
    Workbook wb = new XSSFWorkbook(); //Excell workbook
    Sheet sheet = wb.createSheet(); //WorkSheet //wb.createSheet("sheetName");
    Row row = sheet.createRow(2); //Row created at line 3
    TableModel model = table.getModel(); //Table model

    Row headerRow = sheet.createRow(0); //Create row at line 0
    for (int headings = 0; headings < model.getColumnCount(); headings++) { //For each column
        headerRow.createCell(headings).setCellValue(model.getColumnName(headings));//Write column name
    }//from  w w  w .  j a v a  2s.co  m

    for (int rows = 0; rows < model.getRowCount(); rows++) { //For each table row
        for (int cols = 0; cols < table.getColumnCount(); cols++) { //For each table column
            row.createCell(cols).setCellValue(model.getValueAt(rows, cols).toString()); //Write value
        }

        //Set the row to the next one in the sequence 
        row = sheet.createRow((rows + 3));
    }
    wb.write(new FileOutputStream(path));//Save the file  
    IJ.showMessage("Excel file created!");
}

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

@RequestMapping("/Reportes/OrganismosAcreditados")
    public void ReporteOrganismo(ModelMap map, HttpSession session, HttpServletResponse response) {
        Personal usuario = (Personal) session.getAttribute("usuario");
        Workbook wb = new XSSFWorkbook();
        try {/* w ww . j  av  a2s  . c  o m*/
            //Se llama a la plantilla localizada en la ruta

            //            InputStream inp = new FileInputStream("C:\\Plantillas\\OrgAcred.xlsx");
            InputStream inp = new FileInputStream("/opt/Plantillas/OrgAcred.xlsx");

            wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);

            //Aqu va el query que consigue los datos de la tabla
            //ArrayList<Organismo> listaorg = ServicioPersonal.ListaOrganismos();
            ArrayList<Organismo> listaorg = ServicioReporte.ReporteOrganismo2();

            int i = 1;
            for (Organismo org : listaorg) {
                Row row = sheet.createRow(i);

                Cell cell = row.createCell(0);
                cell.setCellValue(i);
                cell = row.createCell(1);
                cell.setCellValue(org.getEntidad().getNombre());
                cell = row.createCell(2);
                cell.setCellValue(org.getCompetencia());
                cell = row.createCell(3);
                cell.setCellValue(org.getEntidad().getResolAuto());
                cell = row.createCell(4);
                String fechaVenc = "";
                try {
                    fechaVenc = format.dateToString(org.getEntidad().getFechaVenc());
                } catch (Exception ex) {
                }
                cell.setCellValue(fechaVenc);
                cell = row.createCell(5);
                for (Iterator iter = org.getRepresentantes().iterator(); iter.hasNext();) {
                    Representante rep = (Representante) iter.next();
                    cell.setCellValue(rep.getNombre() + " " + rep.getApellidoP());
                }
                cell = row.createCell(6);
                cell.setCellValue(org.getEntidad().getObs());

                i++;
            }
        } catch (Exception e) {
            //e.printStackTrace();
        }

        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition",
                    "attachment; filename=Registro del nmero Organismos Acreditados.xlsx");
            OutputStream fileOut = response.getOutputStream();
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (Exception ex) {
            //ex.printStackTrace();
        }

        String mensaje_log = "El usuario: " + usuario.getNombre() + " " + usuario.getApellidoP() + " con ID: "
                + usuario.getIdpersonal() + ". Descarg el Reporte 'Organismos Acreditados' ";

        String Tipo_registro = "Personal";

        try {
            String Numero_registro = String.valueOf(usuario.getIdpersonal());

            ServicioPersonal.InsertLog(usuario, Tipo_registro, Numero_registro, mensaje_log);
        } catch (Exception ex) {
        }
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

License:Open Source License

private Cell getCell(Sheet sheet, int rowIndex, int colIndex, boolean create) {
    // Get or create row
    Row row = sheet.getRow(rowIndex);/*  w  ww.  j  ava 2  s . c om*/
    if (row == null) {
        if (create) {
            row = sheet.createRow(rowIndex);
        } else
            return null;
    }
    // Get or create cell
    Cell cell = row.getCell(colIndex);
    if (cell == null) {
        if (create) {
            cell = row.createCell(colIndex);
        } else
            return null;
    }

    return cell;
}

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]);
    }/*  w  w  w.j  av  a  2  s  .  co  m*/

    // 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) {

    /*/*from  w  w w .jav 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("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 w  w .j ava 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();
    }

}