Actual case ：
Microsoft Excel It is the most frequently used software in daily office , The data format is xls,xlsx, A very common spreadsheet . Grade of a class in primary school , Recorded in the excel In file ：
full name Chinese language and literature mathematics Foreign Languages
Li lei 95 99 96
Han Mei 98 100 93
zhang 94 95 95
.... ....utilize python Reading and writing excel, add to " Total score " Column , Calculate the total score of each person .
Solution ：
Use pip3 install ：$ pip3 install xlrd xlwt
Using third party libraries xlrd and xlwt, These two libraries are used for excel read reader(rd) And write writer(wt) .
Be careful ：[1] xlrd 2.1.0 The latest version does not support xlsx file , You can create xls Paste the contents before copying the file , Or uninstall the current version and reinstall xlrd==1.1.0.
[2] excel The file format type is "Microsoft Excel 97-2003 file (*.xls)"
（1）excel The file to read xlrd and xlwt Write using
import xlrd
# Read excel, Return an object
book = xlrd.open_workbook('demo.xls')
# Get one book All the tables , Returns a list where each item is sheet object
print(book.sheets()[0])
# You can also use sheet_by_name or sheet_by_name Get the table
sheet = book.sheet_by_index(0)
print(sheet)
# Get the number of rows and columns of a table
r_num = sheet.nrows
c_num = sheet.ncols
print(r_num, c_num)
'''
One book Can contain many tables (sheet), A table is made up of cells ,
A cell is a cell cell.
'''
# If you get each cell( Cell ) The object of , Row and column coordinates need to be passed in
print(sheet.cell(0, 0))
print(sheet.cell(1, 1))
# The type of content in the cell , It is an enumeration value , Can pass xlrd.XL_CELL_[ type ] View the corresponding types of enumeration values
print(sheet.cell(0, 0).ctype)
print(sheet.cell(1, 1).ctype)
# obtain cell The value of the object
print(sheet.cell(0, 0).value)
print(sheet.cell(1, 1).value)
# Get a row of data or a column of data at one time , Pass in the row number or column number
r1 = sheet.row(1)
# Return a list , Each of them is cell object
print(r1)
# Don't want to get cell object , Just want to get the value
print(sheet.row_values(1))
# You can also specify the range of a column similar to a slice operation
# The first 1 The first parameter is the line number , The first 2 The first parameter is the starting column
print(sheet.row_values(1, 1))
# Add cells to the table , Parameters ： Row value 、 The column value 、 type 、 Content 、 Font alignment
# sheet.put_cell()
# Write excel
import xlwt
# establish excel, Instantiate a workbook
w_book = xlwt.Workbook()
# add table
w_sheet = w_book.add_sheet('sheet1')
# Add cells to the table
w_sheet.write(0, 0)
# Write to a file , The filename of the output file
w_book.save('output.xls')（2） Realization excel Text processing and saving
import xlrd, xlwt
# open excel
r_book = xlrd.open_workbook('demo.xls')
# Get the first table
r_sheet = r_book.sheet_by_index(0)
# Get the number of columns in the table
nc = r_sheet.ncols
# Add... To the table cell Cell , Pass in ： Line number , Column number , type
r_sheet.put_cell(0, nc, xlrd.XL_CELL_TEXT, ' Total score ', None)
# Iterate over each row to calculate the total score
for row in range(1, r_sheet.nrows):
# Skip the first 1 Column , After that, sum the contents of each cell list
t = sum(r_sheet.row_values(row, 1))
# Add one cell Cell , Store the total score of each student
r_sheet.put_cell(row, nc, xlrd.XL_CELL_NUMBER, t, None)
# Write to excel In file
w_book = xlwt.Workbook()
w_sheet = w_book.add_sheet(r_sheet.name)
# Set alignment , Horizontal and vertical are in the middle
style = xlwt.easyxf('align: vertical center, horizontal center')
# take r_sheet The cell contents are written to w_sheet among
for r in range(r_sheet.nrows):
for c in range(r_sheet.ncols):
w_sheet.write(r, c, r_sheet.cell_value(r, c), style)
# Save the content
w_book.save('output.xls')
