How Python reads and writes excel files using xlrd and xlwt libraries

    name and surname    multilingualism    math    foreign languages
    Li Lei (1916-1997), Chinese * leader    95      99     96	
    Japanese apricot kernel (Prunus mume)    98      100    93
    Zhang Feng    94      95     95
    .... ....

import xlrd
 
# Read excel, return an object
book = xlrd.open_workbook('')
# Get all the sheets in a book, return a list where each item is a sheet object
print(()[0])
# Also get the table by sheet_by_name or sheet_by_name
sheet = book.sheet_by_index(0)
print(sheet)
# Get the number of rows and columns in a table
r_num = 
c_num = 
print(r_num, c_num)
'''
A book can contain many tables (sheet), a table is composed of a cell.
A cell is also a cell.
'''
# If you get the object of each cell, you need to pass in the row and column coordinates.
print((0, 0))
print((1, 1))
# The type of the content in the cell, it is an enumeration value, you can check the corresponding type of the enumeration value by xlrd.XL_CELL_[type].
print((0, 0).ctype)
print((1, 1).ctype)
# Get the value of the cell object
print((0, 0).value)
print((1, 1).value)
 
# Get one row of data or one column of data at a time, passing in the row or column number
r1 = (1)
# Returns a list, each of which is a cell object
print(r1)
# Don't want to get the cell object, just the value.
print(sheet.row_values(1))
# You can also specify a range of columns similar to the slicing operation
# The first parameter is the row number, the second parameter is the starting column
print(sheet.row_values(1, 1))
 
# Add cells to the table, parameters: row value, column value, type, content, alignment of fonts
# sheet.put_cell()
 
# Write excel
import xlwt
 
# Create excel, instantiate a workbook
w_book = ()
# Add table
w_sheet = w_book.add_sheet('sheet1')
# Add cells to the table
w_sheet.write(0, 0)
 
# Write to file, output file name of file
w_book.save('')

import xlrd, xlwt
 
# Open excel
r_book = xlrd.open_workbook('')
# 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 cell to table, pass in: row 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 column 1 and get a list of the contents of each subsequent cell to sum up
    t = sum(r_sheet.row_values(row, 1))
    # Add a cell that holds the total score for each student.
    r_sheet.put_cell(row, nc, xlrd.XL_CELL_NUMBER, t, None)
 
# Write to excel file
w_book = ()
w_sheet = w_book.add_sheet(r_sheet.name)
# Set the alignment to center both horizontally and vertically
style = ('align: vertical center, horizontal center')
# Write the contents of cell r_sheet to w_sheet
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 content
w_book.save('')