In Python, if you need to read and write old Microsoft Excel spreadsheet files(.xls), then use these libraries: xlrd, xlwt or xlutils. However, if you only work with .xlsx files, then use openpyxl library. For this tutorial, I'm going to show how to use xlrd to read and xlwt to write old Excel files.
Installation
pip install xlrd xlwtWrite old Excel file
import datetime # Import package. import xlwt # Create a workbook. workbook = xlwt.Workbook() # Create a worksheet. worksheet = workbook.add_sheet('My Fruits Sheet') # Write 'apple' in row=0, col=0. worksheet.write(0, 0, 'apple') # Write 2.99 in row=0, col=1. worksheet.write(0, 1, 2.99) worksheet.write(0, 2, 'banana') worksheet.write(0, 3, 4.99) # Add formula at row=1, col=1 worksheet.write(1, 1, xlwt.Formula("B1+D1")) # Add datetime and format cell as 'D-MMM-YY'. dt_style = xlwt.easyxf(num_format_str='D-MMM-YY') worksheet.write(2, 0, datetime.datetime.now(), dt_style) # Save the file workbook.save("sample.xls")
Output
Read old Excel file
# Import package. import xlrd # Load Excel file. workbook = xlrd.open_workbook("sample.xls") # List all sheet names. print(workbook.sheet_names()) # Select sheet. worksheet = workbook.sheet_by_index(0) # Print value of cell. print(worksheet.cell_value(rowx=0, colx=2)) # Print sheet stats. print("{0} sheet has {1} rows and {2} columns".format(worksheet.name, worksheet.nrows, worksheet.ncols))
Output
['My Fruits Sheet'] banana My Fruits Sheet sheet has 3 rows and 4 columns
About the author
Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.