python—Excel file

1. Reading and writing of Excel files

1. Reading operation steps:
(1) Import module xlrd
(2) Open the workbook Book
(3) Specify the form Sheet in the workbook
(4) Read the content according to the row and column serial number

Example 1:

#Example 1: Read the contents of the Excel file school.xls in Stu_pack and save the results in the following list
#(1) Import module xlrd
import xlrd
    
# (2) Open the workbook Book
wb = xlrd.open_workbook('../Stu_pack/school.xls')

# (3) Specify the form Sheet in the workbook
sheet=wb.sheet_by_index(0)
    
    
# (4) Read the content according to the row and column serial number
schools=[] #Define a two-dimensional list schools
for row in range(sheet.nrows):
    school =[] #Define a one-dimensional list school
    for col in range(sheet.ncols):
        content = sheet.cell_value(row,col)
        school.append(content)
    schools.append(school)
    
#Only read the top ten information:
i=0
for school in schools:
    if i<12:
        print(school)
        i =i + 1

Output:

['Enrollment unit code', 'Enrollment unit name', 'Province', 'Whether it is 985', 'Whether it is 211', 'Whether it is marked independently', 'types of school']
['10001', 'Peking University', 'Beijing City', 'is', 'is', 'is', 'Comprehensive category']
['10002', 'Renmin University of China', 'Beijing', 'is', 'is', 'is', 'Comprehensive category']
['10003', 'Tsinghua University', 'Beijing', 'is', 'is', 'is', 'Science and Engineering']
['10004', 'Beijing Jiaotong University', 'Beijing City', 'No', 'Yes', 'No', 'Science and Engineering']
['10005', 'Beijing University of Technology', 'Beijing City', 'No', 'Yes', 'No', 'Science and Engineering']
['10006', 'Beijing University of Aeronautics and Astronautics', 'Beijing City', 'is', 'is', 'is', 'Science and Engineering']
['10007', 'Beijing Institute of Technology', 'Beijing City', 'is', 'is', 'is', 'Science and Engineering']
['10008', 'University of Science and Technology Beijing', 'Beijing City', 'No', 'Yes', 'No', 'Science and Engineering']
['10009', 'Northern University of Technology', 'Beijing', 'No', 'No', 'No', 'Science and Engineering']
['10010', 'Beijing University of Chemical Technology', 'Beijing City', 'No', 'Yes', 'No', 'Science and Engineering']
['10011', 'Beijing Technology and Business University', 'Beijing', 'No', 'No', 'No', '']

2. Steps for writing Excel files:

(1) Import module: xlwt
(2)Construct workbook: Workbook
(3) Add a worksheet to the workbook: Worksheet
(4) Write content according to row and column serial numbers
(5) Save file

Example 2: Write the contents of the excel file read in Example 1 to another Excel and save it to the R&Q_pic folder to make a simple judgment on the province where the school is located. The first row of merged cells displays the title

#(1) Import module: xlwt
import xlwt
import xlrd

#(2)Read file content
def read_excel(file_name):
    wb = xlrd.open_workbook(file_name)
    sheet=wb.sheet_by_index(0)
    schools=[] #Define a two-dimensional list schools
    for row in range(sheet.nrows):
        school =[] #Define a one-dimensional list school
        for col in range(sheet.ncols):
            content = sheet.cell_value(row,col)
            school.append(content)
        schools.append(school)
    return schools


#(3)Write file content
def write_excel(school):
  #Construct workbook: Workbook and create object wb
    wb = xlwt.Workbook(encoding ='utf-8')
    
   #Add a worksheet to the workbook: Worksheet
    sheet=wb.add_sheet('Shanghai University Information Sheet')
    
    #Write content based on row and column serial numbers
    sheet.write_merge(0,0,0,6,'Shanghai University Information Table')#The merged cells are written into the header
    
    for col in range(7): #Traverse the list names (list fields) written to the worksheet
        sheet.write(1,col,schools[0][col])
        
    row_num =2
    for school in schools:
        if school[2]=='Shanghai City':
            for col in range(7):
                sheet.write(row_num,col,school[col])
            row_num =row_num + 1 #Add 1 to yourself, jump out of the inner loop and add 1
                
    wb.save('../R & amp;Q_pic/Shanghai University Information Table.xls')

#Call function
school_list= read_excel('../Stu_pack/school.xls')
write_excel(schools)

schools=read_excel('../R & amp;Q_pic/Shanghai University Information Table.xls')
for school in schools:
    print(school)

Output:

['Shanghai University Information Table', '', '', '', '', '', '']
['Enrollment unit code', 'Enrollment unit name', 'Province', 'Whether it is 985', 'Whether it is 211', 'Whether it is marked independently', 'School type']
['10246', 'Fudan University', 'Shanghai City', 'is', 'is', 'is', 'Comprehensive category']
['10247', 'Tongji University', 'Shanghai', 'is', 'is', 'is', 'Science and Engineering']
['10248', 'Shanghai Jiao Tong University', 'Shanghai City', 'is', 'is', 'is', 'Comprehensive category']
['10251', 'East China University of Science and Technology', 'Shanghai', 'No', 'Yes', 'No', 'Science and Engineering']
['10252', 'University of Shanghai for Science and Technology', 'Shanghai City', 'No', 'No', 'No', 'Science and Engineering']
['10254', 'Shanghai Maritime University', 'Shanghai City', 'No', 'No', 'No', 'Science and Engineering']
['10255', 'Donghua University', 'Shanghai', 'No', 'Yes', 'No', 'Science and Engineering']
['10256', 'Shanghai Electric Power University', 'Shanghai City', 'No', 'No', 'No', 'Science and Engineering']
['10259', 'Shanghai University of Technology', 'Shanghai City', 'No', 'No', 'No', 'Science and Engineering']
['10264', 'Shanghai Ocean University', 'Shanghai City', 'No', 'No', 'No', 'Agriculture and Forestry']
['10268', 'Shanghai University of Traditional Chinese Medicine', 'Shanghai City', 'No', 'No', 'No', 'Pharmaceuticals']
['10269', 'East China Normal University', 'Shanghai City', 'Yes', 'Yes', 'No', 'Normal education category']
['10270', 'Shanghai Normal University', 'Shanghai City', 'No', 'No', 'No', 'Normal College class']
['10271', 'Shanghai International Studies University', 'Shanghai City', 'No', 'Yes', 'No', 'Language category']
['10272', 'Shanghai University of Finance and Economics', 'Shanghai City', 'No', 'Yes', 'No', 'Finance and Economics']
['10273', 'Shanghai University of International Business and Economics', 'Shanghai City', 'No', 'No', 'No', '']
['10274', 'Shanghai Customs College', 'Shanghai City', 'No', 'No', 'No', '']
['10276', 'East China University of Political Science and Law', 'Shanghai City', 'No', 'No', 'No', 'Politics and Law']
['10277', 'Shanghai Institute of Physical Education', 'Shanghai City', 'No', 'No', 'No', 'Sports Category']
['10278', 'Shanghai Conservatory of Music', 'Shanghai City', 'No', 'No', 'No', 'Arts']
['10279', 'Shanghai Theater Academy', 'Shanghai City', 'No', 'No', 'No', 'Arts']
['10280', 'Shanghai University', 'Shanghai City', 'No', 'Yes', 'No', '']
['10856', 'Shanghai University of Engineering and Technology', 'Shanghai City', 'No', 'No', 'No', 'Science and Engineering']
['11047', 'Shanghai Lixin University of Accounting and Finance', 'Shanghai City', 'No', 'No', 'No', '']
['11458', 'Shanghai Institute of Electrical Engineering', 'Shanghai City', 'No', 'No', 'No', '']
['11835', 'Shanghai University of Political Science and Law', 'Shanghai City', 'No', 'No', 'No', '']
['12044', 'Shanghai Second Polytechnic University', 'Shanghai City', 'No', 'No', 'No', '']
['80402', 'Shanghai National Accounting Institute', 'Shanghai City', 'No', 'No', 'No', '']
['82707', 'Shanghai Institute of Materials', 'Shanghai City', 'No', 'No', 'No', '']
['82717', 'Shanghai Power Generation Equipment Complete Design Institute', 'Shanghai City', 'No', 'No', 'No', 'Science and Engineering' ]
['82718', 'Shanghai Internal Combustion Engine Research Institute', 'Shanghai City', 'No', 'No', 'No', '']
['82805', 'Shanghai Nuclear Engineering Research and Design Institute', 'Shanghai City', 'No', 'No', 'No', '']
['82937', 'China Aviation Research Institute 640', 'Shanghai City', 'No', 'No', 'No', '']
['83009', 'East China Institute of Computing Technology', 'Shanghai City', 'No', 'No', 'No', '']
['83285', 'Shanghai Institute of Aerospace Technology (Eighth Aerospace Institute)', 'Shanghai City', 'No', 'No', 'No', '\ ']
['83502', 'Shanghai Research Institute of Chemical Industry', 'Shanghai City', 'No', 'No', 'No', '']
['83901', 'Shanghai Shipping Research Institute', 'Shanghai City', 'No', 'No', 'No', '']
['84002', 'First Institute of Telecommunications Science and Technology (Shanghai)', 'Shanghai City', 'No', 'No', 'No', '\ ']
['84505', 'Shanghai Institute of Biological Products', 'Shanghai City', 'No', 'No', 'No', '']
['85901', 'China Pharmaceutical Industry Research Institute', 'Shanghai City', 'No', 'No', 'No', 'Pharmaceuticals']
['86206', 'China Shipbuilding and Marine Engineering Design Institute', 'Shanghai City', 'No', 'No', 'No', '']
['86207', 'Shanghai Marine Equipment Research Institute', 'Shanghai City', 'No', 'No', 'No', '']
['86208', 'Shanghai Marine Diesel Engine Research Institute', 'Shanghai City', 'No', 'No', 'No', '']
['86219', 'Shanghai Marine Electronic Equipment Research Institute', 'Shanghai City', 'No', 'No', 'No', '']
['87901', 'Shanghai Institute of Computing Technology', 'Shanghai City', 'No', 'No', 'No', '']
['87902', 'Shanghai Institute of International Studies', 'Shanghai City', 'No', 'No', 'No', '']
['87903', 'Shanghai Academy of Social Sciences', 'Shanghai City', 'No', 'No', 'No', '']
['89631', 'Party School of Shanghai Municipal Committee of the Communist Party of China', 'Shanghai City', 'No', 'No', 'No', '']
['90030', 'Second Military Medical University', 'Shanghai City', 'No', 'Yes', 'No', '']

Only output the first ten lines:

schools=read_excel('../R & amp;Q_pic/Shanghai University Information Table.xls')
i=0
for school in schools:
    if i<12:
        print(school)
        i =i + 1

Output:

['Shanghai University Information Table', '', '', '', '', '', '']

['Enrollment unit code', 'Enrollment unit name', 'Province', 'Whether it is 985', 'Whether it is 211', 'Whether it is marked independently', 'School type']

['10246', 'Fudan University', 'Shanghai City', 'is', 'is', 'is', 'Comprehensive category']

['10247', 'Tongji University', 'Shanghai', 'is', 'is', 'is', 'Science and Engineering']

['10248', 'Shanghai Jiao Tong University', 'Shanghai City', 'is', 'is', 'is', 'Comprehensive category']

['10251', 'East China University of Science and Technology', 'Shanghai', 'No', 'Yes', 'No', 'Science and Engineering']

['10252', 'University of Shanghai for Science and Technology', 'Shanghai City', 'No', 'No', 'No', 'Science and Engineering']

['10254', 'Shanghai Maritime University', 'Shanghai City', 'No', 'No', 'No', 'Science and Engineering']

['10255', 'Donghua University', 'Shanghai', 'No', 'Yes', 'No', 'Science and Engineering']

['10256', 'Shanghai Electric Power University', 'Shanghai City', 'No', 'No', 'No', 'Science and Engineering']

['10259', 'Shanghai University of Technology', 'Shanghai City', 'No', 'No', 'No', 'Science and Engineering']

['10264', 'Shanghai Ocean University', 'Shanghai City', 'No', 'No', 'No', 'Agriculture and Forestry']