2014_10_24a_pandas_openpyxl_styling_exploration.ipynb

{"nbformat": 3, "worksheets": [{"cells": [{"cell_type": "code", "language": "python", "outputs": [], "collapsed": true, "prompt_number": 1, "input": "import numpy as np, pandas as pd\nfrom pandas import compat\nimport pandas.core.format as fmt\n\ndef to_excel_with_style(self, excel_writer, style_df, sheet_name='Sheet1', na_rep='',\n             float_format=None, columns=None, header=True, index=True,\n             index_label=None, startrow=0, startcol=0, engine='openpyxl2',\n             merge_cells=True, encoding=None, inf_rep='inf'):\n    \"\"\"\n    Write DataFrame to a excel sheet with specified cell styles\n\n    Parameters\n    ----------\n    excel_writer : string or ExcelWriter object\n        File path or existing ExcelWriter\n    style_df : DataFrame of style dicts\n        style parameters to apply to each cell\n    sheet_name : string, default 'Sheet1'\n        Name of sheet which will contain DataFrame\n    na_rep : string, default ''\n        Missing data representation\n    float_format : string, default None\n        Format string for floating point numbers\n    columns : sequence, optional\n        Columns to write\n    header : boolean or list of string, default True\n        Write out column names. If a list of string is given it is\n        assumed to be aliases for the column names\n    index : boolean, default True\n        Write row names (index)\n    index_label : string or sequence, default None\n        Column label for index column(s) if desired. If None is given, and\n        `header` and `index` are True, then the index names are used. A\n        sequence should be given if the DataFrame uses MultiIndex.\n    startrow :\n        upper left cell row to dump data frame\n    startcol :\n        upper left cell column to dump data frame\n    engine : string, default ``openpyxl2``\n        write engine to use - currently must be ``openpyxl2``.\n    merge_cells : boolean, default True\n        Write MultiIndex and Hierarchical Rows as merged cells.\n    encoding: string, default None\n        encoding of the resulting excel file. Only necessary for xlwt,\n        other writers support unicode natively.\n    cols : kwarg only alias of columns [deprecated]\n    inf_rep : string, default 'inf'\n        Representation for infinity (there is no native representation for\n        infinity in Excel)\n\n    Notes\n    -----\n    The not-yet-documented style dict supports things like this:\n    >>> df = pd.DataFrame({'A':[10,20,30], 'B':[40,50,60]})\n    >>> red_text = {'font': {'color': '00FF0000'}}\n    >>> dashed_blue = {'style': 'dashed', 'color': '000000FF'}\n    >>> dashed_blue = {'border': {s: dashed_blue for s in ['left', 'right', 'top', 'bottom']}}\n    >>> style_df = pd.DataFrame(index=df.index, columns=df.columns, dtype=object)\n    >>> style_df.iloc[:,0] = red_text\n    >>> style_df.iloc[1,:] = dashed_blue\n    >>> to_excel_with_style(df, 't.xlsx', style_df)\n    \"\"\"\n    from pandas.io.excel import ExcelWriter\n\n    need_save = False\n    if encoding == None:\n        encoding = 'ascii'\n\n    if isinstance(excel_writer, compat.string_types):\n        excel_writer = ExcelWriter(excel_writer, engine=engine)\n        need_save = True\n\n    formatter = fmt.ExcelFormatter(self,\n                                   na_rep=na_rep,\n                                   cols=columns,\n                                   header=header,\n                                   float_format=float_format,\n                                   index=index,\n                                   index_label=index_label,\n                                   merge_cells=merge_cells,\n                                   inf_rep=inf_rep)\n    formatted_cells = formatter.get_formatted_cells()\n    \n    assert engine == 'openpyxl2', 'Only openpyxl2 engine is supported'\n    assert np.allclose(self.shape, style_df.shape)\n    \n    style_df = style_df.fillna('')\n    \n    # merge style information into cells\n    def styled_formatted_cells(formatted_cells):\n        for cell in formatted_cells:\n            # cell.row, cell.col may not correspond to style_df row and columns\n            # because of header and index (FIXME: many cases to consider)\n            new_style = {}\n            if cell.row > 0 and cell.col > 0:\n                new_style = dict(style_df.iloc[cell.row-1, cell.col-1])\n                    \n            # merge new style on top of any existing style\n            if cell.style == None:\n                cell.style = {}\n            cell.style.update(new_style)\n\n            yield cell\n        \n    excel_writer.write_cells(styled_formatted_cells(formatted_cells), sheet_name,\n                             startrow=startrow, startcol=startcol)\n    if need_save:\n        excel_writer.save()\n\n        ", "metadata": {"trusted": true}}, {"cell_type": "code", "language": "python", "outputs": [], "collapsed": false, "prompt_number": 2, "input": ">>> df = pd.DataFrame({'A':[10,20,30], 'B':[40,50,60]})\n>>> red_text = {'font': {'color': '00FF0000'}}\n>>> dashed_blue = {'style': 'dashed', 'color': '000000FF'}\n>>> dashed_blue = {'border': {s: dashed_blue for s in ['left', 'right', 'top', 'bottom']}}\n>>> style_df = pd.DataFrame(index=df.index, columns=df.columns, dtype=object)\n>>> style_df.iloc[:,0] = red_text\n>>> style_df.iloc[1,:] = dashed_blue\n>>> to_excel_with_style(df, 'New folder/t.xlsx', style_df)", "metadata": {"trusted": true}}], "metadata": {}}], "metadata": {"name": "", "signature": "sha256:39d0119d8f159d771b804aab366fea4adcad4a334c2caf1b924bf70ec7036426"}, "nbformat_minor": 0}