Python for Spreadsheet Users
Last Updated :
01 Nov, 2022
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
from openpyxl import Workbook
import datetime
wb = Workbook()
ws = wb.active
ws[ 'A1' ] = 75
ws.append([ 1 , 2 , 3 ])
ws.append([ 4 , 5 , 6 ])
ws.append([ 8 , 10 , 12 ])
ws[ 'A2' ] = datetime.datetime.now()
wb.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
from openpyxl import load_workbook
from openpyxl import Workbook
wb = 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
from openpyxl import Workbook
from openpyxl import Workbook
from openpyxl.drawing.image import Image
Spreadsheet = Workbook()
worksheet = Spreadsheet.active
wb = Workbook()
ws = wb.active
ws[ 'A1' ] = 'Two logos demonstrated'
img = Image( 'gfg.png' )
img2 = Image( 'gfg2.png' )
ws.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 xlwt
from datetime import datetime
style0 = 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' )
ws.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" ))
wb.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 xlsxwriter
workbook = xlsxwriter.Workbook( 'demo.xlsx' )
ws = workbook.add_worksheet()
ws.set_column( 'A:A' , 20 )
style = workbook.add_format({ 'bold' : True })
ws.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:
Share your thoughts in the comments
Please Login to comment...