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

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

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

Introduction

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

Prototype

void autoSizeColumn(int column);

Source Link

Document

Adjusts the column width to fit the contents.

Usage

From source file:com.github.luischavez.lat.excel.Excel.java

License:Open Source License

public File generate(String groupName, LocalDateTime dateTime) {
    String datetime = dateTime.format(DateTimeFormatter.ISO_LOCAL_DATE_TIME).replaceAll("-", "_")
            .replaceAll(":", "_").replaceAll("\\.", "_");
    String path = System.getProperty("user.dir").concat("/excel/").concat(datetime).concat("/");

    File file = new File(path);
    if (!file.exists()) {
        file.mkdirs();//from w  ww.  jav a 2s.c o  m
    }

    file = new File(path.concat(groupName).concat(".xls"));

    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet(groupName);
    this.setContent(workbook, sheet, groupName);
    for (int i = 0; i < 11; i++) {
        sheet.autoSizeColumn(i);
    }
    try (FileOutputStream outputStream = new FileOutputStream(file)) {
        workbook.write(outputStream);
    } catch (IOException ex) {
        throw new RuntimeException(ex);
    }

    return file;
}

From source file:com.griffinslogistics.excel.BookLabelGenerator.java

public static void generateLabel(OutputStream outputStream, BookLabelModel bookLabelModel) {

    try {/* w  w w. j ava  2  s  . c o m*/
        XSSFWorkbook workbook = new XSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(workbook);
        String title = bookLabelModel.getTitle().replace("/", "-");
        bookLabelModel.setTitle(title);
        Sheet sheet = workbook.createSheet(bookLabelModel.getBookNumber() + " " + bookLabelModel.getTitle());

        for (int i = 0; i < 20; i++) {
            Row row = sheet.createRow(i);
            if (i != 0 && i != 10) {
                row.setHeightInPoints(25);
            } else {
                row.setHeightInPoints(12);
            }
        }

        //column widths
        sheet.setColumnWidth(0, 5000);
        sheet.setColumnWidth(1, 10000);

        sheet.setColumnWidth(3, 5000);
        sheet.setColumnWidth(4, 10000);

        generateHeaders(sheet, styles);
        generateAddress(sheet, styles, bookLabelModel);
        generateClient(sheet, styles, bookLabelModel);
        generateTransportation(sheet, styles, bookLabelModel);
        generateTitle(sheet, styles, bookLabelModel);
        generateCountPerBox(sheet, styles, bookLabelModel);
        generateCountPerAddress(sheet, styles, bookLabelModel);

        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(4);

        workbook.write(outputStream);
    } catch (IOException ex) {
        Logger.getLogger(BookLabelGenerator.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.guardias.excel.CalendarToExcel.java

License:Apache License

public static void GenerateExcel(String RutaFile, Calendar calendar, String JSONContenidos,
        Medico AdministratorUser) throws IOException {

    Guardias[] lGuardias;/*from  ww  w .j  ava  2s . co m*/

    Gson gson = new GsonBuilder().create();

    lGuardias = gson.fromJson(JSONContenidos, Guardias[].class);

    boolean xlsx = true;
    int year = calendar.get(Calendar.YEAR);
    int month = calendar.get(Calendar.MONTH);

    DateFormat _format = new SimpleDateFormat("yyyy-MM-dd");

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

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

    calendar.set(Calendar.MONTH, month);
    calendar.set(Calendar.DAY_OF_MONTH, 1);

    calendar.setFirstDayOfWeek(Calendar.MONDAY);
    //create a sheet for each month
    Sheet sheet = wb.createSheet(_format.format(calendar.getTime()));

    CellStyle styleBORDER = wb.createCellStyle();
    styleBORDER.setBorderRight(CellStyle.BORDER_THICK);
    styleBORDER.setBorderBottom(CellStyle.BORDER_THICK);
    styleBORDER.setBorderTop(CellStyle.BORDER_THICK);
    styleBORDER.setBorderLeft(CellStyle.BORDER_THICK);
    styleBORDER.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    //

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

    //header with month titles
    Row monthRow = sheet.createRow(1);
    Font fontH = wb.createFont();
    CellStyle CStyleH = wb.createCellStyle();
    CStyleH.setBorderRight(CellStyle.BORDER_THICK);
    CStyleH.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    fontH.setBold(true);
    CStyleH.setFont(fontH);
    for (int i = 0; i < days.length; i++) {

        Cell monthCell = monthRow.createCell(i);

        monthCell.setCellStyle(CStyleH);
        monthCell.setCellValue(days[i]);
        sheet.autoSizeColumn(i);

    }

    int cnt = 1, day = 1;
    int rownum = 2;
    for (int j = 0; j < 6; j++) {
        Row row = sheet.createRow(rownum++);
        Row rowGuardias;
        boolean bRowsCreated = false;

        // row.setHeightInPoints(100);
        for (int i = 0; i < days.length; i++) {
            Cell dayCell_1 = row.createCell(i);
            //  Cell dayCell_2 = row.createCell(i*2 + 1);

            int currentDayOfWeek = (calendar.get(Calendar.DAY_OF_WEEK) + 7 - calendar.getFirstDayOfWeek()) % 7;
            //int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);  
            if (cnt > currentDayOfWeek && calendar.get(Calendar.MONTH) == month) {

                Font font = wb.createFont();
                CellStyle CStyle = wb.createCellStyle();
                short colorI = HSSFColor.AQUA.index; // presencia
                //font.set(colorI);
                CStyle.setFillForegroundColor(colorI);
                CStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //CStyle.setBorderBottom( colorBorder);
                CStyle.setBorderRight(CellStyle.BORDER_THICK);
                CStyle.setBorderBottom(CellStyle.BORDER_THICK);
                CStyle.setBorderTop(CellStyle.BORDER_THICK);
                CStyle.setBorderLeft(CellStyle.BORDER_THICK);
                CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                //CStyle.setFont(font);

                dayCell_1.setCellValue(day);
                dayCell_1.setCellStyle(CStyle);

                sheet.autoSizeColumn(i);

                String _Dia = _format.format(calendar.getTime());

                int DataRowCont = 1; // esto sirve para coger la fila de los datos de cada dia
                for (int d = 0; d < lGuardias.length; d++) {

                    Guardias oGuardias = lGuardias[d];
                    if (oGuardias.getDiaGuardia().equals(_Dia)) {

                        if (!bRowsCreated)
                            rowGuardias = sheet.createRow(rownum++);
                        else
                            rowGuardias = sheet.getRow(row.getRowNum() + DataRowCont);

                        Cell dayCell_1_GUARDIAS = rowGuardias.createCell(i);
                        //    Cell dayCell_2_GUARDIAS = rowGuardias.createCell(i*2 + 1);

                        List<Medico> _lMedico = MedicoDBImpl.getMedicos(oGuardias.getIdMedico(),
                                AdministratorUser.getServicioId());

                        Medico _oMedico = _lMedico.get(0);

                        font = wb.createFont();
                        CStyle = wb.createCellStyle();
                        // PRESENCIA 
                        // LOCALIZADA
                        //XSSFRichTextString richString = new HSSFRichTextString(_oMedico.getApellidos() + " " + _oMedico.getNombre());
                        colorI = HSSFColor.LIGHT_ORANGE.index; // presencia                        
                        if (oGuardias.getTipo().equals(Util.eTipoGuardia.LOCALIZADA.toString().toLowerCase()))
                            colorI = HSSFColor.GREEN.index;
                        else if (oGuardias.getTipo()
                                .equals(Util.eTipoGuardia.REFUERZO.toString().toLowerCase()))
                            colorI = HSSFColor.BLUE.index;
                        else if (oGuardias.getTipo().equals("")) // residente
                            colorI = HSSFColor.RED.index;

                        font.setColor(colorI);

                        CStyle.setFont(font);
                        //CStyle.setBorderBottom( colorBorder);
                        CStyle.setBorderRight(CellStyle.BORDER_THICK);
                        CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());

                        dayCell_1_GUARDIAS.setCellValue(_oMedico.getApellidos() + " " + _oMedico.getNombre()
                                + "[" + _oMedico.getIDMEDICO() + "]");
                        dayCell_1_GUARDIAS.setCellStyle(CStyle);

                        DataRowCont++;

                    }

                }
                bRowsCreated = true;

                //      dayCell_1_GUARDIAS.setCellValue(TextoGuardias.toString());

                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 = RutaFile;
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    wb.close();

}

From source file:com.hp.idc.resm.util.ExcelUtil.java

License:Open Source License

/**
 * Excel// w  w w .  j  av  a  2 s.c o  m
 * 
 * @param id
 *            ID
 * @return Excel
 */
public String getModelExcel(String id) {
    List<ModelAttribute> list = ServiceManager.getModelService().getModelAttributesByModelId(id);

    // 
    String[] IGNORATTR = new String[] { "id", "create_time", "contract_start", "searchcode", "last_update_time",
            "contract_end", "task_link", "order_id", "customer_id", "status", "last_update_by" };
    List<String> l = new ArrayList<String>(Arrays.asList(IGNORATTR));

    Workbook wb = new HSSFWorkbook();
    // Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet(id);
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setColor(HSSFColor.RED.index);
    style.setFont(font);
    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 0);
    int i = 0;
    Cell cell = null;
    HSSFRichTextString textString;
    for (ModelAttribute ma : list) {
        if (l.contains(ma.getAttrId()))
            continue;
        cell = row.createCell(i);
        textString = new HSSFRichTextString(ma.getName() + "/" + ma.getAttrId());
        cell.setCellValue(textString);
        if (!ma.isNullable())
            cell.setCellStyle(style);
        sheet.autoSizeColumn(i);
        i++;
    }
    for (int k = 0; k < list.size(); k++) {
        sheet.autoSizeColumn(k);
    }
    // Write the output to a file
    FileOutputStream fileOut;
    String file;
    try {
        file = System.getProperty("user.dir") + "/../temp/" + id + new Random().nextLong() + ".xls";
        fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        return "";
    } catch (IOException e) {
        e.printStackTrace();
        return "";
    }
    return file;
}

From source file:com.hp.idc.resm.util.ExcelUtil.java

License:Open Source License

/**
 * , Excel//from ww w. j  a v  a2  s . c  o  m
 * 
 * @param modelId
 *            Id
 * @return Excel
 */
public String getResouceDataToExcel(String modelId) {
    Model m = ServiceManager.getModelService().getModelById(modelId);

    List<ResourceObject> l = ServiceManager.getResourceService().getResourcesByModelId(modelId, 1);

    List<ModelAttribute> mas = m.getAttributes();
    Workbook wb = new HSSFWorkbook();
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    font.setFontName("");
    style.setFont(font);
    Sheet sheet = wb.createSheet(modelId);
    Row row = sheet.createRow(0);
    int i = 0;
    HSSFRichTextString textString;
    for (ModelAttribute ma : mas) {
        Cell cell = row.createCell(i);
        textString = new HSSFRichTextString(ma.getDefine().getName());
        cell.setCellStyle(style);
        cell.setCellValue(textString);
        i++;
    }
    i = 1;
    for (ResourceObject ro : l) {
        row = sheet.createRow(i);
        int j = 0;
        for (ModelAttribute ma : mas) {
            textString = new HSSFRichTextString(ro.getAttributeValue(ma.getAttrId()));
            row.createCell(j).setCellValue(textString);
            j++;
        }
        i++;
    }

    for (int k = 0; k < mas.size(); k++) {
        sheet.autoSizeColumn(k);
    }

    // Write the output to a file
    FileOutputStream fileOut;
    String file;
    try {
        file = System.getProperty("user.dir") + "/../temp/" + modelId + new Random().nextLong() + "_data.xls";
        fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        return "";
    } catch (IOException e) {
        e.printStackTrace();
        return "";
    }
    return file;
}

From source file:com.jwm123.loggly.reporter.ReportGenerator.java

License:Apache License

public byte[] build(List<Map<String, String>> row) throws IOException {
    List<String> colLabels = new ArrayList<String>();
    Sheet reportSheet = workbook.getSheet("Report");
    if (reportSheet == null) {
        reportSheet = workbook.createSheet("Report");
    }//from  ww w.j a v  a  2  s  . c o  m
    Row firstRow = reportSheet.getRow(0);
    if (firstRow == null) {
        firstRow = reportSheet.createRow(0);
        int cellNum = 0;
        for (Map<String, String> col : row) {
            for (String key : col.keySet()) {
                Cell cell = firstRow.createCell(cellNum++);
                setCellValue(cell, key);
            }
        }
    }
    for (int i = 0; i < firstRow.getLastCellNum(); i++) {
        Cell cell = firstRow.getCell(i);
        if (cell != null) {
            colLabels.add(cell.getStringCellValue());
        }
    }
    Row newRow = reportSheet.createRow(reportSheet.getLastRowNum() + 1);
    int lastIndex = -1;
    for (Map<String, String> col : row) {
        for (String key : col.keySet()) {
            int colNum = -1;
            Cell cell = null;
            if (colLabels.contains(key)) {
                colNum = colLabels.indexOf(key);
                lastIndex = colNum;
            }
            if (colNum == -1) {
                lastIndex++;
                colNum = lastIndex;
                shiftColumns(reportSheet, colNum, key);
                colLabels.add(colNum, key);
            }
            cell = newRow.getCell(colNum);
            if (cell == null) {
                cell = newRow.createCell(colNum);
            }
            setCellValue(cell, col.get(key));
        }
    }
    for (int i = 0; i < firstRow.getLastCellNum(); i++) {
        reportSheet.autoSizeColumn(i);
    }
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    workbook.write(baos);
    return baos.toByteArray();
}

From source file:com.kovtun.WorkTimeMap.UI.TimeReport.java

private Workbook generateData() {
    Workbook book = new HSSFWorkbook();
    Sheet sheet = book.createSheet();
    int rowCount = 1;
    //? //w  w w.  ja va  2  s  .com
    Row projRow = sheet.createRow(0);
    Cell prjName = projRow.createCell(0);
    prjName.setCellValue(project.getName());

    for (ReportUser user : users) {
        //?
        Row row = sheet.createRow(rowCount);

        Cell nullCell = row.createCell(0);
        nullCell.setCellValue(" ");

        Row nameRow = sheet.createRow(rowCount);

        Cell nameCell = nameRow.createCell(0);
        nameCell.setCellValue(user.getFio());

        Cell userAllTimeCell = nameRow.createCell(1);
        userAllTimeCell.setCellValue((user.getAllTime() / (1000 * 60)) / 60 + " . "
                + (user.getAllTime() / (1000 * 60) % 60 + " ."));

        //
        rowCount++;
        Row aRow = sheet.createRow(rowCount);

        Cell aNumber = aRow.createCell(0);
        aNumber.setCellValue("");

        Cell aName = aRow.createCell(1);
        aName.setCellValue("?");

        Cell aDescription = aRow.createCell(2);
        aDescription.setCellValue("?");

        Cell aComment = aRow.createCell(3);
        aComment.setCellValue("");

        Cell aDate = aRow.createCell(4);
        aDate.setCellValue(" ");

        Cell aTime = aRow.createCell(5);
        aTime.setCellValue("? ?");

        //   
        List<Action> list = user.getActions();
        int k = 1;
        for (Action action : list) {
            rowCount++;
            Row actionRow = sheet.createRow(rowCount);

            Cell actionNumber = actionRow.createCell(0);
            actionNumber.setCellValue(k);
            k++;

            Cell actionName = actionRow.createCell(1);
            actionName.setCellValue(action.getName());

            Cell actionDescription = actionRow.createCell(2);
            actionDescription.setCellValue(action.getDescription());

            Cell actionComment = actionRow.createCell(3);
            actionComment.setCellValue(action.getComment());

            Cell actionDate = actionRow.createCell(4);
            actionDate.setCellValue(action.getStringDate());

            Cell actionTime = actionRow.createCell(5);
            actionTime.setCellValue((action.getStopTime() == null ? "? !"
                    : ((action.getStopTime().getTime() - action.getStartTime().getTime()) / (1000 * 60)) / 60
                            + " . " + ((action.getStopTime().getTime() - action.getStartTime().getTime())
                                    / (1000 * 60) % 60 + " .")));

        }
        rowCount++;

    }

    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    sheet.autoSizeColumn(2);
    sheet.autoSizeColumn(3);
    sheet.autoSizeColumn(4);
    sheet.autoSizeColumn(5);

    return book;
}

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

License:Open Source License

public void setColumnWidth(int sheetIndex, int columnIndex, int width) {
    Sheet sheet = getSheet(sheetIndex);
    if (width >= 0)
        sheet.setColumnWidth(columnIndex, width);
    else if (width == -1)
        sheet.autoSizeColumn(columnIndex);
    else//from   w  w w  . j  av a  2s. com
        sheet.setColumnWidth(columnIndex, sheet.getDefaultColumnWidth() * 256);
}

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

public void writeSongsListToExcel(List<Song> songList) {

    /*/*ww w.ja  va  2s.  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 .  co 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();
    }

}