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 Openpyxl 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.
Installation
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.
Python3
# import modulesfrom openpyxl import Workbookimport datetimewb = Workbook()ws = wb.active# assign sheetws['A1'] = 75# add dataws.append([1, 2, 3])ws.append([4, 5, 6])ws.append([8, 10, 12])ws['A2'] = datetime.datetime.now()# save spreadsheetwb.save("test.xlsx") |
Output:
Explanation:
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
Python3
# import modulesfrom openpyxl import load_workbookfrom openpyxl import Workbook# load spreadsheetwb = load_workbook(filename = 'test.xlsx') |
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
Python3
# import modulesfrom openpyxl import Workbookfrom openpyxl import Workbookfrom openpyxl.drawing.image import ImageSpreadsheet = Workbook()worksheet = Spreadsheet.activewb = Workbook()ws = wb.active# assign titlews['A1'] = 'Two logos demonstrated'# create an imageimg = Image('gfg.png')img2=Image('gfg2.png')# add imagews.add_image(img, 'A1')ws.add_image(img2, 'H1')wb.save('logo.xlsx') |
Output:
Explanation:
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.
Installation
Install the python package by the following command
pip3 install xlwt
OR
pip install xlwt
Creating a Workbook
Given below is a code which gives explore the formatting of value, something not explored in
Python3
# import moduleimport xlwtfrom datetime import datetime# assign attributesstyle0 = xlwt.easyxf('font: name Verdana, color-index green, bold on',)style1 = xlwt.easyxf(num_format_str='D-MMM-YY')style2 = xlwt.easyxf( 'font: name Times New Roman, color-index orange, bold on',)wb = xlwt.Workbook()ws = wb.add_sheet('A Test Sheet')# add dataws.write(0, 0, 156, style0)ws.write(1, 0, datetime.now(), style1)ws.write(2, 0, 1, style2)ws.write(2, 1, 1, style2)ws.write(2, 2, xlwt.Formula("A3+B3"))# printing resultswb.save('example.xls') |
Output:
Explanation:
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.
Installation
Install the below library from the commands
pip install XlsxWriter
OR
pip3 install XlsxWriter
Example
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.
Python3
# import moduleimport xlsxwriter# Create an new Excel file and add a # worksheet.workbook = xlsxwriter.Workbook('demo.xlsx')ws = workbook.add_worksheet()# Widen the first column to make the # text clearer.ws.set_column('A:A', 20)# Adding a bold format to use to highlight # cells.style = workbook.add_format({'bold': True})# add dataws.write('A1', 'Geeks')ws.write('A2', 'for Geeks', style)ws.insert_image('B5', 'logo.png')workbook.close() |
Explanation:
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.
Output:
