Example usage for org.apache.poi.ss.usermodel Workbook createCellStyle

List of usage examples for org.apache.poi.ss.usermodel Workbook createCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createCellStyle

Introduction

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

Prototype

CellStyle createCellStyle();

Source Link

Document

Create a new Cell style and add it to the workbook's style table

Usage

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

License:Apache License

/**
 * Creates a cell and aligns it a certain way.
 *
 * @param wb     the workbook/*from   w ww. ja v  a2  s  .c o  m*/
 * @param row    the row to create the cell in
 * @param column the column number to create the cell in
 * @param halign the horizontal alignment for the cell.
 */
private static void createCell(Workbook wb, Row row, short column, short halign, short valign) {
    CreationHelper ch = wb.getCreationHelper();
    Cell cell = row.createCell(column);
    cell.setCellValue(ch.createRichTextString("Align It"));
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);
    cell.setCellStyle(cellStyle);
}

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

License:Apache License

/**
 * cell styles used for formatting calendar sheets
 *///from   w  ww .j av  a 2  s . c o m
private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<>();

    short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();

    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 48);
    titleFont.setColor(IndexedColors.DARK_BLUE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 12);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    monthFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(monthFont);
    styles.put("month", style);

    Font dayFont = wb.createFont();
    dayFont.setFontHeightInPoints((short) 14);
    dayFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    style.setFont(dayFont);
    styles.put("weekend_left", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("weekend_right", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setBorderLeft(BorderStyle.THIN);
    style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setLeftBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    style.setFont(dayFont);
    styles.put("workday_left", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("workday_right", style);

    style = wb.createCellStyle();
    style.setBorderLeft(BorderStyle.THIN);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("grey_left", style);

    style = wb.createCellStyle();
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("grey_right", style);

    return styles;
}

From source file:com.lw.common.utils.ExcelUtil.java

/**
 * excel// w w w.ja v  a  2s  .c  o m
 * @param list ?
 * @param keys listmapkey?
 * @param columnNames excel??
 * */
public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) {
    // excel
    Workbook wb = new HSSFWorkbook();
    // sheet??
    Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
    // ???n?
    for (int i = 0; i < keys.length; i++) {
        sheet.setColumnWidth((short) i, (short) (35.7 * 150));
    }

    // 
    Row row = sheet.createRow((short) 0);

    // ???
    CellStyle cs = wb.createCellStyle();
    CellStyle cs2 = wb.createCellStyle();

    // ?
    Font f = wb.createFont();
    Font f2 = wb.createFont();

    // ????
    f.setFontHeightInPoints((short) 10);
    f.setColor(IndexedColors.BLACK.getIndex());
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // ??
    f2.setFontHeightInPoints((short) 10);
    f2.setColor(IndexedColors.BLACK.getIndex());

    //        Font f3=wb.createFont();
    //        f3.setFontHeightInPoints((short) 10);
    //        f3.setColor(IndexedColors.RED.getIndex());

    // ?????
    cs.setFont(f);
    cs.setBorderLeft(CellStyle.BORDER_THIN);
    cs.setBorderRight(CellStyle.BORDER_THIN);
    cs.setBorderTop(CellStyle.BORDER_THIN);
    cs.setBorderBottom(CellStyle.BORDER_THIN);
    cs.setAlignment(CellStyle.ALIGN_CENTER);

    // ???
    cs2.setFont(f2);
    cs2.setBorderLeft(CellStyle.BORDER_THIN);
    cs2.setBorderRight(CellStyle.BORDER_THIN);
    cs2.setBorderTop(CellStyle.BORDER_THIN);
    cs2.setBorderBottom(CellStyle.BORDER_THIN);
    cs2.setAlignment(CellStyle.ALIGN_CENTER);
    //??
    for (int i = 0; i < columnNames.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(columnNames[i]);
        cell.setCellStyle(cs);
    }
    //??
    for (short i = 1; i < list.size(); i++) {
        // Row ,Cell  , Row  Cell 0
        // sheet
        Row row1 = sheet.createRow((short) i);
        // row
        for (short j = 0; j < keys.length; j++) {
            Cell cell = row1.createCell(j);
            cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
            cell.setCellStyle(cs2);
        }
    }
    return wb;
}

From source file:com.lwr.software.reporter.restservices.ReportExportService.java

License:Open Source License

public Response exportExcel(Report toExport, Set<ReportParameter> reportParams) {
    Workbook wb = new XSSFWorkbook();

    Font boldFont = wb.createFont();
    boldFont.setBold(true);/*from   w w  w  .  j a va 2s  .c om*/

    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setBorderBottom(BorderStyle.THIN);
    headerStyle.setBorderTop(BorderStyle.THIN);
    headerStyle.setBorderRight(BorderStyle.THIN);
    headerStyle.setBorderLeft(BorderStyle.THIN);
    headerStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
    headerStyle.setFont(boldFont);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);

    CellStyle titleStyle = wb.createCellStyle();
    titleStyle.setBorderBottom(BorderStyle.THIN);
    titleStyle.setBorderTop(BorderStyle.THIN);
    titleStyle.setBorderRight(BorderStyle.THIN);
    titleStyle.setBorderLeft(BorderStyle.THIN);

    List<RowElement> rows = toExport.getRows();
    int sheetIndex = 0;
    for (RowElement rowElement : rows) {
        List<Element> elements = rowElement.getElements();
        for (Element element : elements) {
            try {
                element.setParams(reportParams);
                element.init();
            } catch (Exception e) {
                logger.error("Unable to init '" + element.getTitle() + "' element of report '"
                        + toExport.getTitle() + "' Error " + e.getMessage(), e);
                return Response.serverError().entity("Unable to init '" + element.getTitle()
                        + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage()).build();
            }
            String sheetName = element.getTitle().substring(0,
                    element.getTitle().length() > 30 ? 30 : element.getTitle().length()) + (sheetIndex++);
            Sheet sheet = wb.createSheet(sheetName);

            Row reportTitleRow = sheet.createRow(0);
            Cell reportTitleHeader = reportTitleRow.createCell(0);
            reportTitleHeader.setCellStyle(headerStyle);
            reportTitleHeader.setCellValue("Report Title:");

            Cell reportTitleCell = reportTitleRow.createCell(1);
            reportTitleCell.setCellStyle(titleStyle);
            reportTitleCell.setCellValue(toExport.getTitle());

            Row elementTitleRow = sheet.createRow(1);
            Cell elementTitleHeader = elementTitleRow.createCell(0);
            elementTitleHeader.setCellStyle(headerStyle);
            elementTitleHeader.setCellValue("Element Title:");

            Cell elementTitleCell = elementTitleRow.createCell(1);
            elementTitleCell.setCellStyle(titleStyle);
            elementTitleCell.setCellValue(element.getTitle());

            List<List<Object>> dataToExport = element.getData();

            int rowIndex = 3;
            Row headerRow = sheet.createRow(rowIndex++);
            List<Object> unifiedHeaderRow = element.getHeader();
            for (int i = 0; i < unifiedHeaderRow.size(); i++) {
                Cell headerCell = headerRow.createCell(i);
                String headerCellValue = unifiedHeaderRow.get(i).toString();
                headerCell.setCellValue(headerCellValue);
                headerCell.setCellStyle(headerStyle);
            }
            for (int i = 0; i < dataToExport.size(); i++) {
                Row row = sheet.createRow(rowIndex++);
                List<Object> unifiedRow = dataToExport.get(i);
                int cellIndex = 0;
                for (Object cellValue : unifiedRow) {
                    Cell cell = row.createCell(cellIndex);
                    cell.setCellStyle(cellStyle);
                    try {
                        double val = Double.parseDouble(cellValue.toString());
                        cell.setCellValue(val);
                    } catch (NumberFormatException e) {
                        cell.setCellValue(cellValue.toString());
                    }
                    cellIndex++;
                }
            }
        }
    }
    try {
        File file = new File(DashboardConstants.APPLN_TEMP_DIR + System.nanoTime());
        logger.info("Export CSV temp file path is " + file.getAbsoluteFile());
        wb.write(new FileOutputStream(file));
        wb.close();
        ResponseBuilder responseBuilder = Response.ok((Object) file);
        responseBuilder.header("Content-Type",
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        responseBuilder.header("Content-Transfer-Encoding", "binary");
        responseBuilder.header("Content-Disposition", "attachment;filename=" + file.getName());
        responseBuilder.header("Content-Length", file.length());
        Response responseToSend = responseBuilder.build();
        file.deleteOnExit();
        return responseToSend;
    } catch (Exception e1) {
        return Response.serverError()
                .entity("Unable to export " + toExport.getTitle() + " report " + e1.getMessage()).build();
    }

}

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

License:Open Source License

public static void main(String[] args) throws Exception {
    String file = "/home/mstuder/test.xlsx";
    File f = new File(file);
    if (f.exists())
        f.delete();//from w  ww.  ja  va2 s .c o  m
    Workbook wb = Workbook.getWorkbook(f, true);
    wb.setStyleAction(StyleAction.DATATYPE);
    CellStyle cs = wb.createCellStyle();
    cs.setDataFormat("d/m/yy");
    wb.setCellStyleForDataType(DataType.DateTime, cs);
    DataFrame df = new DataFrame();
    boolean[] missing = new boolean[] { false, false, false, false, false };
    Date date = new Date();
    df.addColumn("A", new Column(new double[] { 1.0, 2.0, 3.0, 4.0, 5.0 }, missing, DataType.Numeric));
    df.addColumn("B", new Column(new Date[] { date, date, date, date, date }, missing, DataType.DateTime));
    wb.createSheet("data");
    wb.writeWorksheet(df, "data", true);
    wb.save();
    printDataFrame(df);

}

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

public void writeSongsListToExcel(List<Song> songList) {

    /*/*from ww  w  .  j  a  v 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("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  www.ja va2s  .com
    Use XSSF for xlsx format and for xls use HSSF
    */
    Workbook workbook = new XSSFWorkbook();

    /*
    create new sheet 
    */
    Sheet songsSheet = workbook.createSheet("Gannarapu_Output");

    XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle();
    /* Create XSSFFont object from the workbook */
    XSSFFont my_font = (XSSFFont) workbook.createFont();
    XSSFFont font = (XSSFFont) workbook.createFont();

    /*
    setting cell color
    */
    CellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

    /*
     setting Header color
    */
    CellStyle style2 = workbook.createCellStyle();
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    style2.setFont(font);
    style2.setAlignment(CellStyle.ALIGN_CENTER);
    //   style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex());
    //   style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
    //            

    Row rowName = songsSheet.createRow(1);

    /*
    Merging the cells
    */
    songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));

    /*
    Applying style to attribute name
    */
    int nameCellIndex = 1;
    Cell namecell = rowName.createCell(nameCellIndex++);
    namecell.setCellValue("Name");
    namecell.setCellStyle(style);

    Cell cel = rowName.createCell(nameCellIndex++);
    cel.setCellValue("Gannarapu, Anirudh");

    /*
    Applying underline to Name
    */
    my_font.setUnderline(XSSFFont.U_DOUBLE);
    my_style.setFont(my_font);
    /* Attaching the style to the cell */
    CellStyle combined = workbook.createCellStyle();
    combined.cloneStyleFrom(my_style);
    combined.cloneStyleFrom(style);
    cel.setCellStyle(combined);

    /*
    Applying  colors to header 
    */

    Row rowMain = songsSheet.createRow(3);
    SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("5");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.RED.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:F4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    /*
    setting new rule to apply alternate colors to cells having same Genre
    */
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:F5"), CellRangeAddress.valueOf("A6:F6"),
            CellRangeAddress.valueOf("A7:F7"), CellRangeAddress.valueOf("A8:F8"),
            CellRangeAddress.valueOf("A13:F13"), CellRangeAddress.valueOf("A14:F14"),
            CellRangeAddress.valueOf("A15:F15"), CellRangeAddress.valueOf("A16:F16"),
            CellRangeAddress.valueOf("A23:F23"), CellRangeAddress.valueOf("A24:F24"),
            CellRangeAddress.valueOf("A25:F25"), CellRangeAddress.valueOf("A26:F26")

    };

    /*        
    setting new rule to apply alternate colors to cells having same Genre
     */
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4");
    PatternFormatting fill3 = rule3.createPatternFormatting();
    fill3.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:F9"), CellRangeAddress.valueOf("A10:F10"),
            CellRangeAddress.valueOf("A11:F11"), CellRangeAddress.valueOf("A12:F12"),
            CellRangeAddress.valueOf("A17:F17"), CellRangeAddress.valueOf("A18:F18"),
            CellRangeAddress.valueOf("A19:F19"), CellRangeAddress.valueOf("A20:F20"),
            CellRangeAddress.valueOf("A21:F21"), CellRangeAddress.valueOf("A22:F22"),
            CellRangeAddress.valueOf("A27:F27"), CellRangeAddress.valueOf("A28:F28"),
            CellRangeAddress.valueOf("A29:F29") };

    /*
    Applying above created rule formatting to cells
    */
    sheetCF.addConditionalFormatting(regionsAction, rule2);
    sheetCF.addConditionalFormatting(regionsAdv, rule3);

    /*
     Setting coloumn header values
    */
    int mainCellIndex = 0;

    Cell SNO = rowMain.createCell(mainCellIndex++);
    SNO.setCellValue("SNO");
    SNO.setCellStyle(style2);
    Cell gen = rowMain.createCell(mainCellIndex++);
    gen.setCellValue("Genre");
    gen.setCellStyle(style2);
    Cell credit = rowMain.createCell(mainCellIndex++);
    credit.setCellValue("Credit Score");
    credit.setCellStyle(style2);
    Cell name = rowMain.createCell(mainCellIndex++);
    name.setCellValue("Album Name");
    name.setCellStyle(style2);
    Cell art = rowMain.createCell(mainCellIndex++);
    art.setCellValue("Artist");
    art.setCellStyle(style2);
    Cell release = rowMain.createCell(mainCellIndex++);
    release.setCellValue("Release Date");
    release.setCellStyle(style2);

    /*
    populating cell values
    */
    int rowIndex = 4;
    int sno = 1;
    for (SongsList song : songList) {
        if (song.getSno() != 0) {

            Row row = songsSheet.createRow(rowIndex++);
            int cellIndex = 0;

            /*
            first place in row is Sno
            */
            row.createCell(cellIndex++).setCellValue(sno++);

            /*
            second place in row is  Genre
            */
            row.createCell(cellIndex++).setCellValue(song.getGenre());

            /*
            third place in row is Critic score
            */
            row.createCell(cellIndex++).setCellValue(song.getCriticscore());

            /*
            fourth place in row is Album name
            */
            row.createCell(cellIndex++).setCellValue(song.getAlbumname());

            /*
            fifth place in row is Artist
            */
            row.createCell(cellIndex++).setCellValue(song.getArtist());

            /*
            sixth place in row is marks in date
            */
            if (song.getReleasedate() != null) {

                Cell date = row.createCell(cellIndex++);

                DataFormat format = workbook.createDataFormat();
                CellStyle dateStyle = workbook.createCellStyle();
                dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy"));
                date.setCellStyle(dateStyle);

                date.setCellValue(song.getReleasedate());

                /*
                auto-resizing columns
                */
                songsSheet.autoSizeColumn(6);
                songsSheet.autoSizeColumn(5);
                songsSheet.autoSizeColumn(4);
                songsSheet.autoSizeColumn(3);
                songsSheet.autoSizeColumn(2);
            }

        }
    }

    /*
    writing this workbook to excel file.
    */
    try {
        FileOutputStream fos = new FileOutputStream(FILE_PATH);
        workbook.write(fos);
        fos.close();

        System.out.println(FILE_PATH + " is successfully written");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

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

public void writeSongsListToExcel(List<Song> songList) {

    /*/*  w  w w . j  a v  a2  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);

    CellStyle style1 = workbook.createCellStyle();//Create style
    Font font = workbook.createFont();//Create font
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);//Make font bold
    style1.setFont(font);//set it to bold
    style1.setAlignment(CellStyle.ALIGN_CENTER);

    CellStyle style13 = workbook.createCellStyle();//Create style
    Font font13 = workbook.createFont();//Create font
    style13.setAlignment(CellStyle.ALIGN_LEFT);

    CellStyle style14 = workbook.createCellStyle();//Create style
    Font font14 = workbook.createFont();//Create font
    style14.setAlignment(CellStyle.ALIGN_RIGHT);
    /*
     setting Header color
    */
    CellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
    style2.setFillBackgroundColor(IndexedColors.LIGHT_ORANGE.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++);

    /*
    Applying underline to Name
    */
    Font underlineFont = workbook.createFont();
    underlineFont.setUnderline(HSSFFont.U_SINGLE);

    /* Attaching the style to the cell */
    CellStyle combined = workbook.createCellStyle();
    combined.setFont(underlineFont);
    cel.setCellStyle(combined);
    cel.setCellValue("Gayam, Prathibha");

    /*
    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.LIGHT_ORANGE.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;

    Cell cell0 = rowMain.createCell(mainCellIndex++);
    cell0.setCellValue("SNO");
    cell0.setCellStyle(style1);
    Cell cell1 = rowMain.createCell(mainCellIndex++);
    cell1.setCellValue("Genre");
    cell1.setCellStyle(style1);
    Cell cell2 = rowMain.createCell(mainCellIndex++);
    cell2.setCellValue("Credit Score");
    cell2.setCellStyle(style1);
    Cell cell3 = rowMain.createCell(mainCellIndex++);
    cell3.setCellValue("Album Name");
    cell3.setCellStyle(style1);
    Cell cell4 = rowMain.createCell(mainCellIndex++);
    cell4.setCellValue("Artist");
    cell4.setCellStyle(style1);
    Cell cell5 = rowMain.createCell(mainCellIndex++);
    cell5.setCellValue("Release Date");
    cell5.setCellStyle(style1);

    /*
    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
            */

            Cell cell20 = row.createCell(cellIndex++);
            cell20.setCellValue(sno++);
            cell20.setCellStyle(style14);

            /*
            second place in row is  Genre
            */
            Cell cell21 = row.createCell(cellIndex++);
            cell21.setCellValue(song.getGenre());
            cell21.setCellStyle(style13);

            /*
            third place in row is Critic score
            */
            Cell cell22 = row.createCell(cellIndex++);
            cell22.setCellValue(song.getCriticscore());
            cell22.setCellStyle(style14);

            /*
            fourth place in row is Album name
            */
            Cell cell23 = row.createCell(cellIndex++);
            cell23.setCellValue(song.getAlbumname());
            cell23.setCellStyle(style13);

            /*
            fifth place in row is Artist
            */
            Cell cell24 = row.createCell(cellIndex++);
            cell24.setCellValue(song.getArtist());
            cell24.setCellStyle(style13);

            /*
            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.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>/*from  w w w .j a va2  s.  c  om*/
 * 1.  : POI UTIL
 * 2.  : POI WORKBOOK ? / String , List<Map<String, Object>> ? , List<Map<String, Object>> 
 * </pre>
 *
 * @method Name : createWorkBook
 * @param workbook, sheetName, list, colNames
 * @return Workbook
 * @throws Exception
 * 
 */
@SuppressWarnings("deprecation")
public static Workbook createWorkBook(Workbook workbook, String sheetName, List<Map<String, Object>> list,
        List<Map<String, Object>> colNames) throws Exception {
    Row row;
    Cell cell;

    /*  ? ? START */
    /* Workbook workbook = new Workbook(); */

    CellStyle titleStyle = workbook.createCellStyle();
    CellStyle cellStyle = workbook.createCellStyle();
    CellStyle contentStyle = workbook.createCellStyle();
    CellStyle contentStyle_2 = workbook.createCellStyle();

    /* ?  */
    /* ? ? */
    Font titleFont = workbook.createFont();

    titleFont.setFontHeightInPoints((short) 13);
    titleFont.setFontName("? ");

    /*  ? */
    Font colNameFont = workbook.createFont();

    colNameFont.setFontHeightInPoints((short) 10);
    colNameFont.setFontName("? ");

    /*  ? */
    Font contentFont = workbook.createFont();

    /* ? ? ?  */
    titleStyle.setFont(titleFont);

    /*   ? / ? ?  */
    cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); /*  ? */
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//? 
    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cellStyle.setFont(colNameFont);

    /*   ? / ?  */
    contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ?  */
    contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    contentStyle.setFont(contentFont);

    /*   ? / ?   */
    contentStyle_2.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ?  */
    contentStyle_2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    contentStyle_2.setBorderTop(HSSFCellStyle.BORDER_THIN);
    contentStyle_2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    contentStyle_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    contentStyle_2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    contentStyle_2.setFont(contentFont);

    /*  ? */
    Sheet sheet = workbook.createSheet(sheetName);

    /*  ?? */
    int sheet1_row = 0;

    List<Map<String, Object>> colList = colNames;

    /* ? START */
    row = sheet.createRow(sheet1_row);
    for (int i = 0; i < colList.size(); i++) {

        cell = row.createCell(i);
        cell.setCellValue(String.valueOf(colList.get(i).get("item" + i)));
        cell.setCellStyle(cellStyle);
    }

    sheet1_row++;

    /* ? END */
    for (Map<String, Object> map : list) {

        row = sheet.createRow(sheet1_row);

        for (int j = 0; j < colList.size(); j++) {
            cell = row.createCell(j);
            cell.setCellStyle(contentStyle);

            if (null != map.get(colList.get(j).get("item" + j))) {
                cell.setCellValue((double) map.get(String.valueOf(colList.get(j).get("item" + j))));
            } else {
                cell.setCellValue("");
            }

            /*  ? ??  */
            sheet.autoSizeColumn((short) j);
            sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 312);
        }
        sheet1_row++;
    }
    return workbook;
}

From source file:com.ncc.excel.test.ExcelUtil.java

License:Apache License

/** 
 * Excel? /*from  w ww.j  av a  2s. c  o m*/
 *  
 * @Title: WriteExcel 
 * @Date : 2014-9-11 ?01:33:59 
 * @param wb 
 * @param rowList 
 * @param xlsPath 
 */
private void writeExcel(Workbook wb, List<Row> rowList, String xlsPath) {

    if (wb == null) {
        out("???");
        return;
    }

    Sheet sheet = wb.getSheetAt(0);// sheet  

    // ???????  
    int lastRowNum = isOverWrite ? startReadPos : sheet.getLastRowNum() + 1;
    int t = 0;//  
    out("???" + rowList.size());
    for (Row row : rowList) {
        if (row == null)
            continue;
        // ???  
        int pos = findInExcel(sheet, row);

        Row r = null;// ??????  
        if (pos >= 0) {
            sheet.removeRow(sheet.getRow(pos));
            r = sheet.createRow(pos);
        } else {
            r = sheet.createRow(lastRowNum + t++);
        }

        //??  
        CellStyle newstyle = wb.createCellStyle();

        //?  
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = r.createCell(i);// ??  
            cell.setCellValue(getCellValue(row.getCell(i)));// ???  
            // cell.setCellStyle(row.getCell(i).getCellStyle());//  
            if (row.getCell(i) == null)
                continue;
            copyCellStyle(row.getCell(i).getCellStyle(), newstyle); // ????  
            cell.setCellStyle(newstyle);// ?  
            // sheet.autoSizeColumn(i);//  
        }
    }
    out("???:" + (rowList.size() - t) + " ?" + t);

    // ??  
    setMergedRegion(sheet);

    try {
        // ??Excel  
        FileOutputStream outputStream = new FileOutputStream(xlsPath);
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
    } catch (Exception e) {
        out("Excel?? ");
        e.printStackTrace();
    }
}