How to Read and Write in Excel in Python

In this example we will show us how to operate the data input and output in excel with Python.

Source Code

#! /usr/bin/env python3
# -*- coding: utf-8 -*-
import xlwt
import xlrd  

# create an excel file, sheetName and excel name are sheet and file name respectively 
def writeData2Excle(sheetName, excelName):
	# create Workbook Object  
	book = xlwt.Workbook(encoding='utf-8', style_compression=0)  
  
	# create sheet object  
	sheet = book.add_sheet(sheetName, cell_overwrite_ok=True)    
	  
	# add data to the sheet the first parameter indicates the location and the second is data 
	# write the data to the first column
        sheet.write(0, 0, 'George')    
	sheet.write(1, 0, 'Peter')
	sheet.write(2, 0, 'John')
	sheet.write(3, 0, 'Christina')
	sheet.write(4, 0, 'Henry')  
	
	# write the data to the second column
	sheet.write(0, 1, 98) 
	sheet.write(1, 1, 99)  
  	sheet.write(2, 1, 94) 
  	sheet.write(3, 1, 92)
  	sheet.write(4, 1, 87)

	# save the file  
	book.save(excelName) 

# load data from file
def readDataFromExcel(excelName):
	# set filename   
	xlsfile = excelName  

	# load data from file to book object 
	book = xlrd.open_workbook(xlsfile)  
  
	# get sheet object  
	sheetObject = book.sheet_by_index(0)    # get sheet object  
	sheetName = book.sheet_names()[0]   	# get sheet name  
	print('sheetName = ' + sheetName)       # print sheet name

	# get the number of rows and columns  
	nrows = sheetObject.nrows    # row number  
	ncols = sheetObject.ncols    # column number
	print('nrows = ' + str(nrows))
	print('ncols = ' + str(ncols))
  
  	# print the content of the sheet
	for index in range(0, nrows):
		print(sheetObject.cell_value(index, 0),)
		print(sheetObject.cell_value(index, 1))

	# get the data of the second column
	columnNameList = sheetObject.col_values(0)
	columnGradeList = sheetObject.col_values(1)
	gradeDict = list2dict(columnGradeList, columnNameList)
	print("The highest score： ")
	print(gradeDict.get(max(columnGradeList)),)
	print(max(columnGradeList))

	print("The lowest score： ")
	print(gradeDict.get(min(columnGradeList)),)
	print(min(columnGradeList))

# combined two lists to a dictionary
def list2dict(list1, list2):
	return dict(zip(list1, list2))

def main():
	excelName = r'studentGrades.xls'
	sheetName = r'sheet'

	# create a sheet and write the data
	writeData2Excle(sheetName, excelName)
	# load the data from the sheet and print
	readDataFromExcel(excelName)

if __name__ == '__main__':
	main()

After running the codes above, we can get these results: