Python for Spreadsheet Users
Excel is one of the popular spreadsheets for data management. While it is tedious to write and update the data in a long Excel sheet, Python helps in minimizing this task and helps easy creation, reading, writing of Excel sheet. This can be done by various Python libraries,3 of which will be discussed in this article.
Using Openxyl Library
Openpyxl is a Python library for reading and writing Excel (with extension xlsx/xlsm/xltx/xltm) files. The openpyxl module allows Python program to read and modify Excel files.
Run the following command in the terminal to install this module.
pip install openpyxl
Creating or Writing a Workbook
We write the code in our code editor and save the code.
We first import the library and the Spreadsheet is given a workbook instance. Afterward, we assign random values corresponding to columns A. Here we have appended in A1 and A2. We can also override the original values as 1 in A1 and 4 in A2 has been overwritten as respective dates and time. Lastly, we save the sheet, where it’s saved as “test”.
Reading a workbook
We can also read a workbook from the following program
This opens the same file which we had opened earlier.
Inserting Images in the Workbook
We can also insert a few images in our Excel sheet as demonstrated in the below code
We have to download the pillow library for dependency and import the sub-libraries as shown. We now download two images we like and just give them the designated row, and we have our image inserted!
Using XLWT Library
This is a library for developers to use to generate spreadsheet files compatible with Microsoft Excel versions 95 to 2003. The package itself is pure Python with no dependencies on modules or packages outside the standard Python distribution.
We can make values bold, colored, italic, etc. Also, the font size can be controlled.
Install the python package by the following command
pip3 install xlwt
pip install xlwt
Creating a Workbook
Given below is a code which gives explore the formatting of value, something not explored in
We import the library and then declare 3 different styles to apply to the values. Style 1 explores one family and color green, while the third one is Times New Roman, and the color is orange with styling as bold. Stye 1 is the format of dates if mentioned, and it’s D-MM-YY
We write the values this time in respective rows and columns both starting with 0 indexes. Hence, ws.write(row, column, value, style). Style is optional here.
Using Xlsx writer
XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format.XlsxWriter can be used to write text, numbers, formulas, and hyperlinks to multiple worksheets, and it supports features Here is a simple example: Conditional formatting., Worksheet PNG/JPEG/BMP/WMF/EMF images, Rich multi-format strings, Cell comments., Integration with Pandas., Textboxes., Support for adding Macros.
Install the below library from the commands
pip install XlsxWriter
pip3 install XlsxWriter
This is another way to create an Excel sheet and with a wide range of options as well as images of all extensions supported. Let’s take a code example below.
We first import the said library and create a workbook (demo.xlsx) and start by setting a wide column and giving the size as 20. We then declare a style name bold where we set style as true. Next, we write the word “Geeks” with no style but the next word “for Geeks” is applied to the style we have given. Notice as compared to the earlier example we use true instead of on and easyxf function to declare a style. This makes xlsxWriter the most readable and user-free syntax compared to the other two.
Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.
To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course