Python for Spreadsheet Users
Excel is the most famous spreadsheet and almost every computer user is comfortable with the idea of managing the data through spreadsheets. Eventually some python program has to interact with excel. Many python libraries are available to create, read and write into excel files. We will see the examples of few such important libraries below.
Using openpyxl
This library can read/write Excel 2010 xlsx/xlsm/xltx/xltm files. In the below example we create a excel worksheet, assign data to its cells and finally save the file to a desired location. The module has many in-built methods which can be used for this. We see those methods used below.
Example
#openpyxl
from openpyxl import Workbook
Spreadsheet = Workbook()
# grab the active worksheet
worksheet = Spreadsheet.active
# Data can be assigned directly to cells
worksheet['A1'] = 50
# Rows can also be appended
worksheet.append([5, 15, 25, 35, 45, 55])
worksheet.append([9, 19, 29, 39, 49, 59])
# Python types will automatically be converted
import datetime
t1 = datetime.datetime.now()
worksheet['A1'] = t1.year
worksheet['A2'] = t1
# Save the file
Spreadsheet.save("E:\\openpyxl.xlsx")Output
Running the above code gives us the following result −
Using xlwt
This is a library for developers to use to generate spreadsheet files compatible with Microsoft Excel versions 95 to 2003. We can not only write the values into the cells, we can also format the values. The values can be made bold, coloured, italic etc. Also the font size can be controlled. In the below example we format the numbers written and make them blue coloured.
Example
import xlwt
from datetime import datetime
Spreadsheet = xlwt.Workbook()
worksheet = Spreadsheet.add_sheet('Newsheet1')
format1 = xlwt.easyxf('font: name Times New Roman, color-index blue, bold on', num_format_str='#,##0.00')
format2 = xlwt.easyxf(num_format_str='D-MMM-YY')
worksheet.write(0, 0, datetime.now(), format2)
worksheet.write(0, 1, datetime.now().strftime("%B"),format2)
worksheet.write(0, 2, datetime.now().strftime("%A"),format2)
worksheet.write(1, 0, 5369.2, format1)
worksheet.write(1, 1, 1926.5,format1)
worksheet.write(1, 2, 4896.2,format1)
worksheet.write(2, 0, 5)
worksheet.write(2, 1, 10)
worksheet.write(2, 2, xlwt.Formula("A3+B3"))
Spreadsheet.save('E:\\xlwt_spreadsheet.xls')Output
Running the above code gives us the following result −
Using xlsxwriter
This module can create excel files of version 2007. It has much wider number of features than the above to excel file creating modules. It can write text, numbers, formulas and hyperlinks to multiple worksheets. In the below example, not only we do the formatting of the text in the sheet, but also we add an image to the sheet.
Example
import xlsxwriter
Spreadsheet = xlsxwriter.Workbook('E:\\xlsxw_spreadsheet.xlsx')
sheet = Spreadsheet.add_worksheet()
sheet.set_column('A:A', 18)
# Add a bold format to use to highlight cells.
bold = Spreadsheet.add_format({'bold': True})
#simple text.
sheet.write('A1','Freelancer',bold)
sheet.write('A2', 'Work')
sheet.write('A3', 100)
sheet.write('A4', 1000)
sheet.write('B1', 'DECIMAL', bold)
sheet.write('B2', 17.1)
sheet.write('B3', 29.6)
sheet.write('B4', 45.9)
sheet.write('C1', 'POSITIVE',bold)
sheet.write('C2', 69)
sheet.write('C3', 53)
sheet.write('C4', 36)
sheet.write('D1','NEGATIVE',bold)
sheet.write('D2', -89)
sheet.write('D3', -26)
sheet.write('D4', -15)
# Insert an image.
sheet.insert_image('E5', 'E:\\firefox.JPG')
Spreadsheet.close()Output
Running the above code gives us the following result −
Published on 04-Feb-2020 10:41:00
