How to read Excel in Python

In python automation, we often encounter operations on data files, such as adding multiple employees, but directly writing employee data in the python file, not only the workload is large, if you encounter similar batch data operations again in the future, it will be written in python file?

To cope with this problem, you can write data into an excel file, and operate on the excel file, which is a perfect solution.

This article only introduces the operation of python on excel

Install xlrd library

The official address of the xlrd library: https://pypi.org/project/xlrd/

pip install xlrd

The author used pip3 install xlrd when installing

Reason: I installed python2 and python3 at the same time

If pip is used, the library will be installed in python2 by default, and it cannot be called directly in python3.

So, should you use pip or pip3 for installation?

If only Python2 is installed in the system, then only pip can be used.

If only Python3 is installed in the system, then either pip or pip3 can be used, and the two are equivalent.

If both Python2 and Python3 are installed in the system, pip will be used by Python2 by default and pip3 will be used by Python3.

Simple use of Xlrd library

Take the following excel file as an example

The file name is demo, there are two sheets, named worksheet 1 and worksheet 2.

There are the following data in worksheet 1

Simple to use

# coding=utf-8import xlrd

# open a file
data = xlrd.open_workbook('file/demo.xlsx')

# View worksheet
data.sheet_names()print("sheets："+str(data.sheet_names()))

# Get worksheet by file name,Get worksheet 1
table = data.sheet_by_name('Work schedule 1')

# Print data.sheet_names()It can be found that the returned value is a list, and worksheet 1 is obtained by indexing the list.
# table = data.sheet_by_index(0)

# Get the number of rows and columns
# Number of rows: table.nrows
# Number of columns: table.ncols
print("Total number of rows:"+str(table.nrows))print("Total number of columns:"+str(table.ncols))

# Get the value of the entire row and the value of the entire column, the returned result is an array
# Whole row value: table.row_values(start,end)
# Entire column value: table.col_values(start,end)
# The parameter start is the number from which to start printing,
# end is the end of printing to that position, the default is none
print("Whole row value:"+str(table.row_values(0)))print("Entire column value:"+str(table.col_values(1)))

# Get the value of a cell, for example, get the value of cell B3
cel_B3 = table.cell(3,2).value
print("The value of the third row and second column:"+ cel_B3)

Results after running

Used in the project

Get all the data

# coding=utf-8import xlrd

def read_xlrd(excelFile):
 data = xlrd.open_workbook(excelFile)
 table = data.sheet_by_index(0)for rowNum inrange(table.nrows):
 rowVale = table.row_values(rowNum)for colNum inrange(table.ncols):if rowNum   0 and colNum ==0:print(int(rowVale[0]))else:print(rowVale[colNum])print("---------------")

 # if judgment is to format id
 # print("Unformatted Id data:")
 # print(table.cell(1,0))
 # Result: number:1001.0if __name__ =='__main__':
 excelFile ='file/demo.xlsx'read_xlrd(excelFile=excelFile)

result

If you use it in the project, you can slightly modify the content method. After obtaining all the data, return each row of data as an array

# coding=utf-8import xlrd

def read_xlrd(excelFile):
 data = xlrd.open_workbook(excelFile)
 table = data.sheet_by_index(0)
 dataFile =[]for rowNum inrange(table.nrows):
 # if remove the header
 if rowNum   0:
 dataFile.append(table.row_values(rowNum))return dataFile

if __name__ =='__main__':
 excelFile ='file/demo.xlsx'print(read_xlrd(excelFile=excelFile))

result

The above is the detailed content of how to read Excel in Python. For more information about how to read Excel in Python, please follow other related articles on ZaLou.Cn!