XLSX Examples

This page provides a list of examples that demonstrate rendering xlsx output from the given dataframe. Each example is self-contained inside a class. We have some helper functions to provide the data we need for this examples

All examples listed in this section use OpenPyxlStyleHelper and OpenPyxlCompositor. To use the xlsxwriter library replace these with XlsxWriterStyleHelper and XlsxWriterCompositor respectively.

If the callback funcs do not use the *StyleHelpers and return their own Style objects then the objects returned should be compatible with the value engine provided to the engine attribute.

Examples of relevant style objects can be found respective documentations for the engine being used.

Helper Functions (Data loading routines)

 1import tempfile
 2import webbrowser
 3import zipfile
 4
 5import pandas as pd
 6import requests
 7
 8import table_compositor.table_compositor as tc
 9import table_compositor.xlsx_styles as xlsstyle
10import table_compositor.xlsx_writer as xlsxw
11

 1# code snippet adapted from http://function-pipe.readthedocs.io/en/latest/usage_df.html
 2# source url
 3URL_NAMES = "https://www.ssa.gov/oact/babynames/names.zip"
 4ZIP_NAME = "names.zip"
 5
 6
 7def load_names_data():
 8    fp = os.path.join(tempfile.gettempdir(), ZIP_NAME)
 9    if not os.path.exists(fp):
10        r = requests.get(URL_NAMES)
11        with open(fp, "wb") as f:
12            f.write(r.content)
13
14    post = collections.OrderedDict()
15    with zipfile.ZipFile(fp) as zf:
16        # get ZipInfo instances
17        for zi in sorted(zf.infolist(), key=lambda zi: zi.filename):
18            fn = zi.filename
19            if fn.startswith("yob"):
20                year = int(fn[3:7])
21                df = pd.read_csv(
22                    zf.open(zi), header=None, names=("name", "gender", "count")
23                )
24                df["year"] = year
25                post[year] = df
26
27        df = pd.concat(post.values())
28        df.set_index("name", inplace=True, drop=True)
29        return df
30
31
32def sample_names_data():
33    df = load_names_data()
34    df = df[(df["year"] == 2015) & (df["count"] > 1000)]
35    return df.sample(100, random_state=0).sort_values("count")
36
37
38def top_names_for_year(year=2015, gender="F", top_n=5):
39    df = load_names_data()
40    df = df[(df["year"] == year) & (df["gender"] == gender)]
41    df = df.sort_values("count")[:top_n]
42    return df
43
44

Example 1 - DataFrame with default styles

Demonstrates converting dataframe into html format with default styles.

 1class XLSXExample1:
 2    """
 3    Demonstrates rendering a simple dataframe to a xlsx file
 4    using the default styles
 5    """
 6
 7    @classmethod
 8    def render_xlsx(cls):
 9        """
10        Render the df to a xlsx file.
11        """
12
13        # load data
14        df = sample_names_data()
15        # build presentation model
16        pm = tc.build_presentation_model(df=df, output_format="xlsx")
17
18        # render to xlsx
19        tempdir = tempfile.gettempdir()
20        fp = os.path.join(tempdir, "example1.xlsx")
21        layout = [pm]
22        print("Writing to " + fp)
23        xlsxw.OpenPyxlCompositor.to_xlsx(layout=layout, output_fp=fp)
24
25
_images/xlsx_example1.png

Example 2 - DataFrame with custom styles

In this example, we format the different components of dataframe with various styling attributes

 1class XLSXExample2:
 2    """
 3    Demonstrates using call-backs that help set the display and style
 4    properties of each cell in the xlsx sheet.
 5    """
 6
 7    @staticmethod
 8    def data_value_func(df):
 9        def _inner(idx, col):
10            if col == "gender":
11                if df.loc[idx, col] == "F":
12                    return "Female"
13                return "Male"
14            return df.loc[idx, col]
15
16        return _inner
17
18    @staticmethod
19    def data_style_func(df):
20        def _inner(idx, col):
21            bg_color = None
22            number_format = "General"
23            if col == "count":
24                number_format = "#,##0"
25            if df.loc[idx, "gender"] == "F":
26                bg_color = "bbdef8"
27            else:
28                bg_color = "e3f2fd"
29            return xlsstyle.OpenPyxlStyleHelper.get_style(
30                bg_color=bg_color, number_format=number_format
31            )
32
33        return _inner
34
35    @staticmethod
36    def index_name_value_func(value):
37        return value.capitalize()
38
39    @staticmethod
40    def index_name_style_func(value):
41        return xlsstyle.OpenPyxlStyleHelper.default_header_style()
42
43    @staticmethod
44    def header_value_func(node):
45        return node.value.capitalize()
46
47    @staticmethod
48    def header_style_func(node):
49        return xlsstyle.OpenPyxlStyleHelper.default_header_style()
50
51    @staticmethod
52    def index_value_func(node):
53        return node.value.capitalize()
54
55    @staticmethod
56    def index_style_func(df):
57        def _inner(node):
58            bg_color = None
59            if df.loc[node.value, "gender"] == "F":
60                bg_color = "bbdef8"
61            else:
62                bg_color = "e3f2fd"
63            return xlsstyle.OpenPyxlStyleHelper.get_style(bg_color=bg_color)
64
65        return _inner
66
67    @classmethod
68    def render_xlsx(cls):
69        # load data
70        df = sample_names_data()
71        # build presentation model
72        klass_ = XLSXExample2
73        pm = tc.build_presentation_model(
74            df=df,
75            output_format="xlsx",
76            data_value_func=klass_.data_value_func(df),
77            data_style_func=klass_.data_style_func(df),
78            header_value_func=klass_.header_value_func,
79            header_style_func=klass_.header_style_func,
80            index_style_func=klass_.index_style_func(df),
81            index_value_func=klass_.index_value_func,
82            index_name_style_func=klass_.index_name_style_func,
83            index_name_func=klass_.index_name_value_func,
84        )
85
86        # render to xlsx
87        tempdir = tempfile.gettempdir()
88        fp = os.path.join(tempdir, "example2.xlsx")
89        layout = [pm]
90        print("Writing to " + fp)
91        xlsxw.OpenPyxlCompositor.to_xlsx(layout=layout, output_fp=fp)
92
93
_images/xlsx_example2.png

Example 3 - Simple DataFrame with Layouts

Demonstrates rendering multi-dataframes in one worksheet along with common functions for styling

  1class XLSXExample3:
  2    """
  3    Demonstrates using call-backs and also rendering multiple tables to single
  4    worksheet.
  5    """
  6
  7    @staticmethod
  8    def data_value_func(df):
  9        def _inner(idx, col):
 10            if col == "gender":
 11                if df.loc[idx, col] == "F":
 12                    return "Female"
 13                return "Male"
 14            return df.loc[idx, col]
 15
 16        return _inner
 17
 18    @staticmethod
 19    def data_style_func(df):
 20        def _inner(idx, col):
 21            bg_color = None
 22            number_format = "General"
 23            if col == "count":
 24                number_format = "#,##0"
 25            if df.loc[idx, "gender"] == "F":
 26                bg_color = "bbdef8"
 27            else:
 28                bg_color = "e3f2fd"
 29            return xlsstyle.OpenPyxlStyleHelper.get_style(
 30                bg_color=bg_color, number_format=number_format
 31            )
 32
 33        return _inner
 34
 35    @staticmethod
 36    def index_name_value_func(value):
 37        return value.capitalize()
 38
 39    @staticmethod
 40    def index_name_style_func(value):
 41        return xlsstyle.OpenPyxlStyleHelper.default_header_style()
 42
 43    @staticmethod
 44    def header_value_func(node):
 45        return node.value.capitalize()
 46
 47    @staticmethod
 48    def header_style_func(node):
 49        return xlsstyle.OpenPyxlStyleHelper.default_header_style()
 50
 51    @staticmethod
 52    def index_value_func(node):
 53        return node.value.capitalize()
 54
 55    @staticmethod
 56    def index_style_func(df):
 57        def _inner(node):
 58            bg_color = None
 59            if df.loc[node.value, "gender"] == "F":
 60                bg_color = "bbdef8"
 61            else:
 62                bg_color = "e3f2fd"
 63            return xlsstyle.OpenPyxlStyleHelper.get_style(bg_color=bg_color)
 64
 65        return _inner
 66
 67    @classmethod
 68    def render_xlsx(cls):
 69        # Prepare first data frame (same as in render_xlsx)
 70        df = sample_names_data()
 71        # build presentation model
 72        klass_ = XLSXExample3
 73        pm_all = tc.build_presentation_model(
 74            df=df,
 75            output_format="xlsx",
 76            data_value_func=klass_.data_value_func(df),
 77            data_style_func=klass_.data_style_func(df),
 78            header_value_func=klass_.header_value_func,
 79            header_style_func=klass_.header_style_func,
 80            index_style_func=klass_.index_style_func(df),
 81            index_value_func=klass_.index_value_func,
 82            index_name_style_func=klass_.index_name_style_func,
 83            index_name_func=klass_.index_name_value_func,
 84        )
 85
 86        male_df = top_names_for_year(gender="M")
 87        pm_top_male = tc.build_presentation_model(
 88            df=male_df,
 89            output_format="xlsx",
 90            data_value_func=klass_.data_value_func(male_df),
 91            data_style_func=klass_.data_style_func(male_df),
 92            header_value_func=klass_.header_value_func,
 93            header_style_func=klass_.header_style_func,
 94            index_style_func=klass_.index_style_func(male_df),
 95            index_value_func=klass_.index_value_func,
 96            index_name_style_func=klass_.index_name_style_func,
 97            index_name_func=klass_.index_name_value_func,
 98        )
 99
100        female_df = top_names_for_year(gender="F")
101        pm_top_female = tc.build_presentation_model(
102            df=female_df,
103            output_format="xlsx",
104            data_value_func=klass_.data_value_func(female_df),
105            data_style_func=klass_.data_style_func(female_df),
106            header_value_func=klass_.header_value_func,
107            header_style_func=klass_.header_style_func,
108            index_style_func=klass_.index_style_func(female_df),
109            index_value_func=klass_.index_value_func,
110            index_name_style_func=klass_.index_name_style_func,
111            index_name_func=klass_.index_name_value_func,
112        )
113
114        layout = [pm_all, [pm_top_female, pm_top_male]]
115        # render to xlsx
116        tempdir = tempfile.gettempdir()
117        fp = os.path.join(tempdir, "example3.xlsx")
118        print("Writing to " + fp)
119        xlsxw.OpenPyxlCompositor.to_xlsx(
120            layout=layout, output_fp=fp, orientation="horizontal"
121        )
122
123
_images/xlsx_example3.png

Example 4 - DataFrames with Multi-hierarchical columns and indices

Demonstrates rendering dataframes with multi-hierarchical indices and mult-hierarchical columns

 1class XLSXExample4:
 2    """
 3    Demonstrate styling and rendering of multi-hierarchical indexed dataframe
 4    into a xlsx file.
 5    """
 6
 7    @staticmethod
 8    def data_style_func(df):
 9        def _inner(idx, col):
10            bg_color = None
11            number_format = "General"
12            if col == "count":
13                number_format = "#,##0"
14            if idx[1] == "F":
15                bg_color = "bbdef8"
16            else:
17                bg_color = "e3f2fd"
18            return xlsstyle.OpenPyxlStyleHelper.get_style(
19                bg_color=bg_color, number_format=number_format
20            )
21
22        return _inner
23
24    @staticmethod
25    def index_name_value_func(value):
26        return "Max By Year"
27
28    @staticmethod
29    def index_name_style_func(value):
30        return xlsstyle.OpenPyxlStyleHelper.default_header_style()
31
32    @staticmethod
33    def header_value_func(node):
34        return node.value.capitalize()
35
36    @staticmethod
37    def header_style_func(node):
38        return xlsstyle.OpenPyxlStyleHelper.default_header_style()
39
40    @staticmethod
41    def index_value_func(node):
42        if isinstance(node.value, str):
43            return node.value.capitalize()
44        return node.value
45
46    @staticmethod
47    def index_style_func(df):
48        def _inner(node):
49            bg_color = None
50            if len(node.key) == 1:
51                bg_color = "4f81bd"
52            elif node.key[1] == "F":
53                bg_color = "bbdef8"
54            else:
55                bg_color = "e3f2fd"
56            return xlsstyle.OpenPyxlStyleHelper.get_style(bg_color=bg_color)
57
58        return _inner
59
60    @classmethod
61    def render_xlsx(cls):
62
63        # Prepare first data frame (same as in render_xlsx)
64        data_df = load_names_data()
65        data_df = data_df[data_df["year"] >= 2000]
66        g = data_df.groupby(("year", "gender"))
67        df = g.max()
68
69        klass_ = cls
70        pm = tc.build_presentation_model(
71            df=df,
72            output_format="xlsx",
73            # data_value_func=None,   # use default
74            data_style_func=klass_.data_style_func(df),
75            header_value_func=klass_.header_value_func,
76            header_style_func=klass_.header_style_func,
77            index_style_func=klass_.index_style_func(df),
78            index_value_func=klass_.index_value_func,
79            index_name_style_func=klass_.index_name_style_func,
80            index_name_func=klass_.index_name_value_func,
81        )
82
83        layout = [pm]
84        # render to xlsx
85        tempdir = tempfile.gettempdir()
86        fp = os.path.join(tempdir, "example4.xlsx")
87        print("Writing to " + fp)
88        xlsxw.OpenPyxlCompositor.to_xlsx(
89            layout=layout, output_fp=fp, orientation="horizontal"
90        )
91
92
_images/xlsx_example4.png