import numpy as np, pandas as pd
from pandas import compat
import pandas.core.format as fmt

def to_excel_with_style(self, excel_writer, style_df, sheet_name='Sheet1', na_rep='',
             float_format=None, columns=None, header=True, index=True,
             index_label=None, startrow=0, startcol=0, engine='openpyxl2',
             merge_cells=True, encoding=None, inf_rep='inf'):
    """
    Write DataFrame to a excel sheet with specified cell styles

    Parameters
    ----------
    excel_writer : string or ExcelWriter object
        File path or existing ExcelWriter
    style_df : DataFrame of style dicts
        style parameters to apply to each cell
    sheet_name : string, default 'Sheet1'
        Name of sheet which will contain DataFrame
    na_rep : string, default ''
        Missing data representation
    float_format : string, default None
        Format string for floating point numbers
    columns : sequence, optional
        Columns to write
    header : boolean or list of string, default True
        Write out column names. If a list of string is given it is
        assumed to be aliases for the column names
    index : boolean, default True
        Write row names (index)
    index_label : string or sequence, default None
        Column label for index column(s) if desired. If None is given, and
        `header` and `index` are True, then the index names are used. A
        sequence should be given if the DataFrame uses MultiIndex.
    startrow :
        upper left cell row to dump data frame
    startcol :
        upper left cell column to dump data frame
    engine : string, default ``openpyxl2``
        write engine to use - currently must be ``openpyxl2``.
    merge_cells : boolean, default True
        Write MultiIndex and Hierarchical Rows as merged cells.
    encoding: string, default None
        encoding of the resulting excel file. Only necessary for xlwt,
        other writers support unicode natively.
    cols : kwarg only alias of columns [deprecated]
    inf_rep : string, default 'inf'
        Representation for infinity (there is no native representation for
        infinity in Excel)

    Notes
    -----
    The not-yet-documented style dict supports things like this:
    >>> df = pd.DataFrame({'A':[10,20,30], 'B':[40,50,60]})
    >>> red_text = {'font': {'color': '00FF0000'}}
    >>> dashed_blue = {'style': 'dashed', 'color': '000000FF'}
    >>> dashed_blue = {'border': {s: dashed_blue for s in ['left', 'right', 'top', 'bottom']}}
    >>> style_df = pd.DataFrame(index=df.index, columns=df.columns, dtype=object)
    >>> style_df.iloc[:,0] = red_text
    >>> style_df.iloc[1,:] = dashed_blue
    >>> to_excel_with_style(df, 't.xlsx', style_df)
    """
    from pandas.io.excel import ExcelWriter

    need_save = False
    if encoding == None:
        encoding = 'ascii'

    if isinstance(excel_writer, compat.string_types):
        excel_writer = ExcelWriter(excel_writer, engine=engine)
        need_save = True

    formatter = fmt.ExcelFormatter(self,
                                   na_rep=na_rep,
                                   cols=columns,
                                   header=header,
                                   float_format=float_format,
                                   index=index,
                                   index_label=index_label,
                                   merge_cells=merge_cells,
                                   inf_rep=inf_rep)
    formatted_cells = formatter.get_formatted_cells()
    
    assert engine == 'openpyxl2', 'Only openpyxl2 engine is supported'
    assert np.allclose(self.shape, style_df.shape)
    
    style_df = style_df.fillna('')
    
    # merge style information into cells
    def styled_formatted_cells(formatted_cells):
        for cell in formatted_cells:
            # cell.row, cell.col may not correspond to style_df row and columns
            # because of header and index (FIXME: many cases to consider)
            new_style = {}
            if cell.row > 0 and cell.col > 0:
                new_style = dict(style_df.iloc[cell.row-1, cell.col-1])
                    
            # merge new style on top of any existing style
            if cell.style == None:
                cell.style = {}
            cell.style.update(new_style)

            yield cell
        
    excel_writer.write_cells(styled_formatted_cells(formatted_cells), sheet_name,
                             startrow=startrow, startcol=startcol)
    if need_save:
        excel_writer.save()

        

>>> df = pd.DataFrame({'A':[10,20,30], 'B':[40,50,60]})
>>> red_text = {'font': {'color': '00FF0000'}}
>>> dashed_blue = {'style': 'dashed', 'color': '000000FF'}
>>> dashed_blue = {'border': {s: dashed_blue for s in ['left', 'right', 'top', 'bottom']}}
>>> style_df = pd.DataFrame(index=df.index, columns=df.columns, dtype=object)
>>> style_df.iloc[:,0] = red_text
>>> style_df.iloc[1,:] = dashed_blue
>>> to_excel_with_style(df, 'New folder/t.xlsx', style_df)