Operate excel with python read write and modify of

1. Write instance to excel:

Write the data of 1 list to excel, the first row is the header, and the following rows are specific data

import xlwt
# Can only write but not read 
stus = [[' Name ', ' Age ', ' Gender ', ' Fraction '],
    ['mary', 20, ' Female ', 89.9],
    ['mary', 20, ' Female ', 89.9],
    ['mary', 20, ' Female ', 89.9],
    ['mary', 20, ' Female ', 89.9]
    ]
book = xlwt.Workbook()# New 1 A excel
sheet = book.add_sheet('case1_sheet')# Add 1 A sheet Page 
row = 0# Control line 
for stu in stus:
  col = 0# Control column 
  for s in stu:# Recycle inside list The value of each 1 Column 
    sheet.write(row,col,s)
    col+=1
  row+=1
book.save('stu_1.xls')# Save to current directory 

2. Read excel:

import xlrd
# You can only read but not write 
book = xlrd.open_workbook('stu.xls')# Open 1 A excel
sheet = book.sheet_by_index(0)# Get according to order sheet
sheet2 = book.sheet_by_name('case1_sheet')# According to sheet Page name acquisition sheet
print(sheet.cell(0,0).value)# Specify rows and columns to get data 
print(sheet.cell(0,1).value)
print(sheet.cell(0,2).value)
print(sheet.cell(0,3).value)
print(sheet.ncols)# Get excel How many columns are there 
print(sheet.nrows)# Get excel How many lines are there 
print(sheet.get_rows())#
for i in sheet.get_rows():
  print(i)# Gets every 1 Row data 
print(sheet.row_values(0))# Get the 1 Row 
for i in range(sheet.nrows):#0 1 2 3 4 5
  print(sheet.row_values(i))# What row is the data to get 

print(sheet.col_values(1))# Take the first 1 Column data 
for i in range(sheet.ncols):
  print(sheet.col_values(i))# What column is the data to get 

3. Modification of excel:

Modify a value in excel and save it again

from xlutils.copy import copy
import xlrd
#xlutils: Modify excel
book1 = xlrd.open_workbook('stu.xls')
book2 = copy(book1)# Copy 1 A copy of the original excel
# print(dir(book2))
sheet = book2.get_sheet(0)# Get the number sheet Page, book2 Now it is xlutils The method in, not xlrd Adj. 
sheet.write(1,3,0)
sheet.write(1,0,'hello')
book2.save('stu.xls')

The above is the python to excel operation (read, write, modify) details, more about python to excel operation information please pay attention to this site other related articles!