Description
Feature Type
-
Adding new functionality to pandas
-
Changing existing functionality in pandas
-
Removing existing functionality in pandas
Problem Description
I would like to assign a basic custom style for DataFrame body when it is exported to Excel with the method pandas.DataFrame.to_excel(). I know that there are a vast number of alternatives using stylers but those use large libraries such as matplotlib to produce a simple css style, for example, if the user just needs to add borders or bold text or alignment.
Currently, the method pandas.DataFrame.to_excel() uses by default for any DataFrame a style with borders and bold for headers and indexes, and no style for the rest of the body.
Feature Description
There are many alternatives. For me the shortest and easiest one with the less number of changes in the current code will be:
According to the source code of the current dev-version, the method pandas.DataFrame.to_excel() uses an instance of a class ExcelFormatter to format cells and then writes them to a file. This class is defined in file pandas/io/formats/excel.py and its method .write() calls the method .get_formatted_cells() and it in its turn calls the private method ._format_body() and after some more calls the method ._generate_body() is reached. This method formats series by series and by default uses a style=None which is hard coded.
# Final part of current `ExcelFormatter._generate_body()`
yield CssExcelCell(
row=self.rowcounter + i,
col=colidx + coloffset,
val=val,
style=None,
css_styles=getattr(self.styler, "ctx", None),
css_row=i,
css_col=colidx,
css_converter=self.style_converter,
)I would like to change that None default value to a new ExcelFormatter property similar to the current header_style used to format headers and indexes. That needs to add a new property and change the call to CssExcelCell in line ~ 882 in file pandas/io/formats/excel.py as I show in this shortcode
# ExcelFormatter properties
@property
def header_style(self) -> dict[str, dict[str, str | bool]]:
return {
"font": {"bold": True},
"borders": {
"top": "thin",
"right": "thin",
"bottom": "thin",
"left": "thin",
},
"alignment": {"horizontal": "center", "vertical": "top"},
}
# -- > NEW PROPERTY < ---
@property
def body_style(self) -> dict[str, dict[str, str | bool]]:
return {
"font": {"bold": False},
"borders": {
"top": "thin",
"right": "thin",
"bottom": "thin",
"left": "thin",
},
"alignment": {"horizontal": "right", "vertical": "top"},
}
# -- > END NEW PROPERTY < ---
# Call in `ExcelFormatter._generate_body()` to ``CssExcelCell`
```python
yield CssExcelCell(
# ...
style=self.body_style
# ...
)I don't know if there is a better way to set those properties in pandas, but for my purposes, I can build a new style as a dict in my code and assign my new styles to the properties or set to None before calling the constructor of ExcelFormatter in the method.
pd.io.formats.excel.ExcelFormatter.header_style = style_header
pd.io.formats.excel.ExcelFormatter.body_style = style_body
df.to_excel(writer, sheet_name=sheet_name)A more sophisticated alternative will included new arguments with custom styles for the method ``pandas.DataFrame.to_excel()` and changes in the way those styles are parsed by ExcelFormatter instances, but in the end maybe look similar to the shortest alternative that I describe above.
Alternative Solutions
As an alternative users can use Stylers in DataFrame. This alternative requires matplotlib, jinja2.
Additional Context
I already tested these small changes in my code in an environment with pandas (version 1.4.2) and xlsxwriter I would like to push those changes for a more recent version. But, in virtue that I don't know which tests are usually made by the developers' team related to complex DataFrames or performance, I decided to launch this request.