Formatting Your Excel Sheet

In this chapter, we are going to go over two different libraries in R and various libraries in Python that can be used to format tables and data in an Excel worksheet.

There are several packages in R that we will be discussing, including the following:

• styledTables
• basictabler

We are going to create some fictitious data along with using one of R’s built-in datasets, Iris, and then we are going to apply styling to it from the aforementioned packages. Each package will have its methods to apply this styling, so it’s good to go over them and see which type of workflow you prefer.

In the Python section of this chapter, we will explore the advanced options available in pandas and openpyxl to create beautiful tables and pivot tables from Python in Excel. In particular, we will use pandas, openpyxl, and pywin32.

In this chapter, we will understand the following topics:

• Installing and using styledTables in R
...

Technical requirements

The code for this chapter can be found on GitHub at the following link: https://github.com/PacktPublishing/Extending-Excel-with-Python-and-R/tree/main/Chapter5.

As mentioned previously, we are going to be working with a few different packages in the R section. The styledTables package can only be installed from GitHub via the devtools package.

For the Python section, you will need one new package (specifically for the formatting tasks with pandas): jinja2==3.1.2.

Installing and using styledTables in R

As we have done before, we will need to install the necessary packages before we can use them. In this section, we are going to install the styledTables package. Because styledTables is not on CRAN, we cannot install it using the typical method of using the install.packages()function, so we will need to install this package from GitHub. This will require us to also install the devtools package, which has the functionality to do this very sort of thing.

Here is the full code you’ll need to install the package:

install.packages("devtools")
# Install development version from GitHub
devtools::install_github(
'R-package/styledTables',
build_vignettes = TRUE
)

After running the preceding code, we can call the library into the current session in the typical fashion by issuing library(styledtables) in the console. Now that we have loaded the library in, we can go ahead and create our first script, which will style a...

Advanced options for formatting with Python

The Python section of this chapter is organized into the following three sections:

• Cell formatting: Cell formatting is crucial for presenting data in a visually appealing and organized manner. We will demonstrate how to apply various formatting styles to cells, such as setting font properties (for example, size, color, bold, and italic), adjusting cell background colors, and aligning text within cells. You will learn how to create professional-looking tables with well-formatted cells that enhance data readability.
• Conditional formatting: Conditional formatting allows you to dynamically format cells based on specific conditions. We will walk you through the process of applying conditional formatting to highlight important data points, visualize trends, and identify outliers. You will discover how to use pandas and openpyxl to implement various conditional formatting rules, such as color scales, data bars, and icon sets, making...

Summary

In this chapter, we delved into the art of formatting Excel sheets to present data in a visually appealing and organized manner. Divided into three sections, we covered essential techniques to transform raw data into professional-looking tables that enhance data readability.

The first section focused on cell formatting, where we demonstrated how to apply various styles to cells, such as adjusting font properties, cell backgrounds, and text alignment. By mastering cell formatting, you can create well-organized and visually appealing tables.

Next, we explored conditional formatting, a powerful feature that allows you to dynamically format cells based on specific conditions. We provided practical examples of using styledTables and basictabler for R and then pandas and openpyxl for Python to implement various conditional formatting rules, such as color scales, data bars, and icon sets, making your data stand out and revealing critical insights.

Lastly, we unlocked the...