Read and write Excel using Python

  • 2020-05-27 06:28:37
  • OfStack

In the process of learning Python, we will encounter reading and writing problems of Excel. At this point, we can use the xlwt module to write the data to the Excel table, and the xlrd module to read the data from Excel. Let's look at how to implement a read-write operation on Excel using Python.

Python version: 3.5.2

Install xlwt and xlrd through pip, if not:

pip install xlwt

pip install xlrd

1. Write to Excel file:

# -*- conding:utf-8 -*-
__author__ = 'mayi'
#How to write to an Excel using xlwt module
import xlwt
# create 1 a Wordbook Object, which is essentially created 1 a Excel file 
book = xlwt.Workbook(encoding = "utf-8", style_compression = 0)
# create 1 a sheet Object, 1 a sheet Object corresponding to the Excel In the file 1 form 
sheet = book.add_sheet("sheet1", cell_overwrite_ok = True)
# To the table sheet1 Add data in 
sheet.write(0, 0, "EnglishName") # Among them, "0, 0" Specify the cells in the table, "EnglishName" Is what is written to the cell 
sheet.write(1, 0, "MaYi")
sheet.write(0, 1, " Chinese name ")
sheet.write(1, 1, " The ants ")
# Finally, save the above operation to the specified Excel In the file"name.xls")

2. Read the Excel file:

# -*- conding:utf-8 -*-
__author__ = 'mayi'
# How to read from an Excel using xlrd module
import xlrd
#  Opens in the specified path xls File, get book object 
xls_file = "name.xls"
# Open the specified file 
book = xlrd.open_workbook(xls_file)
#  through sheet The index for sheet object 
sheet1 = book.sheet_by_index(0)
# #  Gets the specified index sheet The name 
# sheet1_name = book.sheet_names()[0]
# print(sheet1_name)
# #  through sheet The name for sheet object 
# sheet1 = book.sheet_by_name(sheet1_name)
#  Get the number of rows and columns 
#  Total number of rows 
nrows = sheet1.nrows
# The total number of columns 
ncols = sheet1.ncols
#  Walk through the contents of the printed table 
for i in range(nrows):
  for j in range(ncols):
    cell_value = sheet1.cell_value(i, j)
    print(cell_value, end = "\t")

Related articles: