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.
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.
#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")
Running the above code gives us the following result −
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.
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')
Running the above code gives us the following result −
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.
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()
Running the above code gives us the following result −