[Various modules of python] (2) Use xlrd and xlwt to operate Excel in python

Table of Contents

1 What are xlrd and xlwt

2 Import xlwt

3 xlwt related syntax

3.1 Create a new workbook

3.2 Create a new sheet

3.3 Save workbook

4 Form of table in python

4.1 Matrix

4.2 The data form of EXCEL == is completely equal to the numerical structure of the matrix

4.3 Matrix in python

5 specific codes related to xlwt

5.1 Code

5.2 Results

5.3 Issues to note

5.3.1 It cannot be an existing table, and an error will be reported: permission denied.

5.3.2 Save format

5.3.3 Path method

6 Import xlrd

6.1 Possible problems

6.2 After installing xlrd, you can check the installed version number

7 Related syntax of xlrd

7.1 Get the existing EXCEL file workbook

7.2 Get information about sheets under wb

7.2.1 Obtain the lower-level object array of wb and the sheets object array step by step

7.2.2 Directly obtain some properties of the lower-level sheets object of wb, using specific methods

7.2.3 Get a specific sheet

7.2.4 Get the number of rows and columns in a specific sheet

7.2.5 Get the cells in a specific sheet. What about the range content?

8 specific codes related to xlrd

8.1 A piece of experimental code

1 What are xlrd and xlwt

  • Generally speaking
  • xlrd is a module used to read excel
  • xlwt is a module used to write to excel

2 import xlwt

import xlwt

3.1 Create a new workbook

wb = xlwt.Workbook(encoding=’utf-8′)

3.2 Create a new sheet

sh = wb.add_sheet(“test”)

3.3 Save workbook

You must save it before you can see the modified results.

savePath = r’C:\Users\Administrator\Desktop\1002.xls’
wb.save(savePath)

4 The form of the table in python

4.1 matrix

First recall the matrix Ai*j in mathematics

4.2 The data form of EXCEL == is completely equal to the numerical structure of the matrix

row = row of matrix

column = column of matrix

4.3 python matrix

The matrix is saved in the form like this

List: Generally refers to 1 row in the matrix,

Therefore the length of the list: generally refers to the number of rows of the matrix

The length of an element of the list: generally refers to the number of columns of the matrix

head = [‘Column 1’, ‘Column 2’, ‘Column 3’]
data = [[‘a11′,’a12′,’a13’],[‘a22′,’a22′,’a23’]]

5 xlwt related specific code

5.1 code

import xlwt
wb = xlwt.Workbook(encoding='utf-8')
sh = wb.add_sheet("test")
head = ['Column 1', 'Column 2', 'Column 3']
data = [['a11','a12','a13'],['a22','a22','a23']]
for i in head:
sh.write(0,head.index(i),i)
for i in range(len(data)):
    for j in range(len(data[i])):
        sh.write(i + 1,j,data[i][j])
savePath = r'C:\Users\Administrator\Desktop\1002.xls'
wb.save(savePath)

5.2 results

5.3 Issues to note

5.3.1 cannot be an existing table, an error will be reported: permission denied

5.3.2 Saved format

  • It can only be xls
  • Cannot be xlsx
  • Cannot be xlsm
  • The latter two types cannot be opened after being saved.

5.3.3 Path method

  • savePath = r’C:\Users\Administrator\Desktop\1002.xls’
  • savePath = ‘C:\Users\Administrator\Desktop\1002.xls’

6 Import xlrd

  • import xlrd

6.1 Possible Problems

  • There may be problems here. For example, the anaconda I use only has xlwt installed by default, but xlrd is not installed.
  • Import xlrd will report an error and the xlrd module cannot be found.
  • Then what
  • You need to use cmd or powershell in anaconda to run the command line
  • Enter conda list or pip show xlrd to find that there is no xlrd
  • Installation: pip install xlrd

6.2 After installing xlrd, you can check the installed version number

import xlrd
print(xlrd.__version__)

7.1 Get existing EXCEL file workbook

  • xlrd.open_workbook(path) returns the object of the corresponding wb file

import xlrd
print(xlrd.__version__)

##Create wb object and open the corresponding wb table object
wb=xlrd.open_workbook(r’C:\Users\Administrator\Desktop\2001.xls’)



7.2 Get information about sheets under wb


7.2.1 Obtain the subordinate object array of wb step by step, sheets object array


    

  • First of all, it is the same structure as EXCEL itself
    • 

  • The subordinate object of workbook is sheets object
    • 

  • sheets=wb.sheets() returns an object array [Sheet 0:, Sheet 1:, Sheet 2:]
    • 

  • Get the array length, len(sheets) , not sheets.count
    • 





#Get the sheet object under the wb object, and it is an object array
 sheets=wb.sheets()
 print(sheets)
 print(len(sheets))
 print(sheets.count) #Error sheets.count 




    

  • After obtaining the sheets object, you can also obtain the properties of the sheet object, such as sheet.name, etc.
    • 





#How about directly obtaining the properties of the sheet object? Is it ok? Yes, but only sheet.name without sheets.name
 sheets=wb.sheets()
 for sh in sheets:
     print(sh.name)




7.2.2 Directly obtain some attributes of the subordinate sheets object of wb, use specific methods


    

  • You can also directly get the name array of the sheets object under the wb object. This is not an object array, but a string array.
    • 

  • sheetnames=wb.sheet_names()
    • 

  • Directly obtained is a string array [‘sheet101’, ‘sheet102’, ‘sheet103’]
    • 





#You can also directly get the name array of the sheets object under the wb object. This is not an object array, but a string array.
 sheetnames=wb.sheet_names()
 print(sheetnames)




7.2.3 Get a specific sheet


There are three ways to obtain a specific sheet


    

  • Method 1# Get a certain one in the sheet object array obtained by wb
    • 

  • sh1=wb.sheets()[0]
    • 



    

  • Method 2#Fetch according to the order of sheets
    • 

  • sh2=wb.sheet_by_index(1)
    • 



    

  • Method 3 #According to the specific sheet display name
    • 

  • The display name of the sheet in EXCEL is different from the real name or index inside the program
    • 

  • sh3=wb.sheet_by_name(“sheet101”)
    • 



    

  • Error handling
    • 

  • But please note that if you search according to these methods, you need to have a corresponding sheet, otherwise an error will be reported.
    • 

  • #sh4=wb.sheet_by_name(“sheet1001”) #Wrong sheet name will cause an error
    • 



    

  • Return content
    • 

  • #It should be noted that generally the sheet object is obtained. To obtain the object attributes, you need to use sh1.name, etc.
    • 



7.2.4 Get the number of rows and columns in a specific sheet


    

  • rows=sh1.nrows
    • 

  • columns=sh1.ncols #Write sh1.ncolumns and report an error…
    • 





 rows=sh1.nrows
 columns=sh1.ncols #Write sh1.ncolumns and report an error…
 print(rows)
 print(columns)


print(“{0}This table has {1} rows and {2} columns”.format(sh1.name,rows,columns))
 print(sh1.name,”This table has”,rows,”rows”,columns,”columns”)
 print(f”The table sh1.name has rows and columns”)
 print(“%sThis table has %d rows and %d columns” %(sh1,rows,columns))




7.2.5 Get the cells in a specific sheet, what about the range content? 



8.1 A piece of experimental code


    

  • Open the EXCEL workbook
    • 

  • Find sheets, sheet_names(), or specific sheet
    • 

  • Get the contents of the sheet
    • 



import xlrd
print(xlrd.__version__)

##Create wb object and open the corresponding wb table object
wb=xlrd.open_workbook(r'C:\Users\Administrator\Desktop\2001.xls')

#Get the sheet object under the wb object, and it is an object array
sheets=wb.sheets()
print(sheets)
print(len(sheets))
print(sheets.count) #Error sheets.count <built-in method count of list object at 0x00000205AD7AE5C0>

#You can also directly get the name array of the sheets object under the wb object. This is not an object array, but a string array.
sheetnames=wb.sheet_names()
print(sheetnames)

#How about directly obtaining the properties of the sheet object? Is it ok? Yes, but only sheet.name without sheets.name
sheets=wb.sheets()
for sh in sheets:
    print(sh.name)

print()
##Get a specific sheet
#Get a certain one in the sheet object array obtained by wb
sh1=wb.sheets()[0]
#Fetch according to the order of sheets
sh2=wb.sheet_by_index(1)
#According to the specific sheet display name (the display name of the sheet in EXCEL is different from the real name or index inside the program)
sh3=wb.sheet_by_name("sheet101")
#sh4=wb.sheet_by_name("sheet1001") #Wrong sheet name will cause an error


#Get the object from the sheet array of wb. To obtain the object attributes, you need to use sh1.name, etc.
print(sh1)
print(sh1.name)
print(sh2)
print(sh3)

print()
##Get the content in the sheet


rows=sh1.nrows
columns=sh1.ncols #Write sh1.ncolumns and report an error...
print(rows)
print(columns)

print("{0}This table has {1} rows and {2} columns".format(sh1.name,rows,columns))
print(sh1.name,"This table has",rows,"rows",columns,"columns")
print(f"The table sh1.name has rows and columns")
print("%sThis table has %d rows and %d columns" %(sh1,rows,columns))


#sheet1=sheetname[0]
#print(sheet1)

#Get the content in the sheet
#sheet1_data=sheet1.sheet_by_name("sheet101")
#print(sheet1.data.name)
#print(f"Table:{sheet1_data.name}\
Number of rows:" sheet1_data.nrows\
Number of columns:"sheet1_data.ncolumns)



#About the format settings of xlrd and xlwt, I don’t think it is important. You can check it when you need to use it