Example usage for org.apache.poi.ss.usermodel Cell setCellStyle

List of usage examples for org.apache.poi.ss.usermodel Cell setCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellStyle

Introduction

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

Prototype

void setCellStyle(CellStyle style);

Source Link

Document

Set the style for the cell.

Usage

From source file:at.mukprojects.exclycore.model.ExclyDate.java

License:Open Source License

@Override
public void setCell(Cell cell, CellStyle cellStyle) {
    cell.setCellValue(data);
    cell.setCellStyle(cellStyle);
}

From source file:at.mukprojects.exclycore.model.ExclyDateBlank.java

License:Open Source License

@Override
public void setCell(Cell cell, CellStyle cellStyle) {
    cell.setCellStyle(cellStyle);
}

From source file:at.mukprojects.exclycore.model.ExclyDateError.java

License:Open Source License

@Override
public void setCell(Cell cell, CellStyle cellStyle) {
    cell.setCellValue(ERRORCODE);/*from w w w . ja v  a  2s .c o  m*/
    CellStyle cellStyleError = cell.getSheet().getWorkbook().createCellStyle();
    cellStyleError.cloneStyleFrom(cellStyle);
    cellStyleError.setFillForegroundColor(IndexedColors.RED.getIndex());
    cellStyleError.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    cell.setCellStyle(cellStyleError);
}

From source file:b01.officeLink.excel.ExcelRefiller.java

License:Apache License

public void fillGroupContent(String groupStr, FocObject object) {
    ExcelGroupDefinition grpDef = getGroupDefinition(groupStr);
    Sheet srcSheet = getSourceSheet();/*  ww  w .ja va 2 s.  co m*/
    Sheet tarSheet = getTargetSheet();
    if (grpDef != null) {
        for (int i = 0; i < grpDef.getRowCount(); i++) {
            int rowIdx = grpDef.getRowAt(i);
            Row sRow = srcSheet.getRow(rowIdx);
            if (sRow != null) {
                Row tRow = tarSheet.getRow(currentRow);
                if (tRow == null) {
                    tRow = tarSheet.createRow(currentRow);
                }
                if (tRow != null) {
                    tRow.setHeight(sRow.getHeight());
                    for (int c = 0; c < 20; c++) {
                        Cell sCell = sRow.getCell(c + 1);
                        if (sCell != null) {
                            Cell tCell = tRow.getCell(c);
                            if (tCell == null) {
                                tCell = tRow.createCell(c);
                            }
                            if (tCell != null) {
                                tCell.setCellStyle(sCell.getCellStyle());

                                String str = "";
                                if (sCell.getCellType() == Cell.CELL_TYPE_STRING) {
                                    RichTextString rts = sCell.getRichStringCellValue();
                                    str = rts.getString();
                                    str = analyseContent(str, object);
                                } else if (sCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    str = String.valueOf(sCell.getNumericCellValue());
                                }

                                if (str != null && !str.isEmpty()) {
                                    int iVal = convertString2Integer(str);
                                    double dVal = convertString2Double(str);
                                    if (iVal != Integer.MAX_VALUE) {
                                        tCell.setCellValue(iVal);
                                    } else if (!Double.isNaN(dVal)) {
                                        tCell.setCellValue(dVal);
                                    } else {
                                        if (getFocExcelDocument() != null
                                                && getFocExcelDocument().getWorkbook() != null) {
                                            tCell.setCellValue(getFocExcelDocument().getWorkbook()
                                                    .getCreationHelper().createRichTextString(str));
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                currentRow++;
            }
        }
    }
}

From source file:bad.robot.excel.cell.DataFormat.java

License:Apache License

private void updateDataFormat(Cell cell, Workbook workbook) {
    CellStyle style = newStyleBasedOn(cell).create(workbook);
    style.setDataFormat(workbook.createDataFormat().getFormat(value()));
    cell.setCellStyle(style);
}

From source file:bad.robot.excel.matchers.StubCell.java

License:Apache License

static Cell createCell(int row, int column, Date date) {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    Cell cell = sheet.createRow(row).createCell(column, CELL_TYPE_NUMERIC);
    cell.setCellValue(date);/*  ww  w  . j a  va 2s. co  m*/
    CellStyle style = workbook.createCellStyle();
    style.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("m/d/yy h:mm"));
    cell.setCellStyle(style);
    return cell;
}

From source file:bad.robot.excel.row.CopyRow.java

License:Apache License

private static void copyCellStyle(Workbook workbook, Cell oldCell, Cell newCell) {
    newCell.setCellStyle(newStyleBasedOn(oldCell).create(workbook));
}

From source file:bad.robot.excel.style.ReplaceExistingStyle.java

License:Apache License

@Override
public void applyTo(org.apache.poi.ss.usermodel.Cell cell, Workbook workbook) {
    CellStyle style = newStyleBasedOn(cell).create(workbook);
    applyBorderTo(style);/*from w  w  w  . ja v a 2  s  . c  o  m*/
    applyFillTo(style);
    applyAlignmentTo(style);
    applyDataFormatTo(style, workbook);
    applyFontTo(style, workbook);
    cell.setCellStyle(style);
}

From source file:bandaru_excelreadwrite.WritetoExcel.java

public void writeSongsListToExcel(List<Song> songList) {

    /*//from w w w  .  ja v a 2 s .  c  om
    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);
    style2.setAlignment(style2.ALIGN_CENTER);

    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("Bandaru, Sreekanth");

    /*
    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);
    combined.setAlignment(combined.ALIGN_CENTER);

    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.LIME.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 Fenre
     */
    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;
    CellStyle style4 = workbook.createCellStyle();
    XSSFFont my_font2 = (XSSFFont) workbook.createFont();
    my_font2.setBold(true);
    style4.setFont(my_font2);
    rowMain.setRowStyle(style4);
    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");

    /*
    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:beans.ReviewsBeans.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);/*from w  w w.  j  a va  2  s.  com*/
    CellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());

    for (Row row : sheet) {
        for (Cell cell : row) {
            cell.setCellValue(cell.getStringCellValue().toUpperCase());
            cell.setCellStyle(style);
        }
    }
}