python–xlrd/xlwt/xlutils

• xlrd只能读取，不能改,支持 xlsx和xls 格式
• xlwt只能改，不能读
• xlwt只能保存为.xls格式
• xlutils能将xlrd.Book转为xlwt.Workbook，从而得以在现有xls的基础上修改数据，并创建一个新的xls，实现修改

xlrd

• 打开文件

import xlrd
excel=xlrd.open_workbook('E:/test.xlsx') 

返回值为xlrd.book.Book对象,不能修改

• 获取sheet

table_list = excel.sheet_names() #获取Book对象中所有sheet名称,以列表方式显示

table = excel.sheets[i]       #通过索引获取  

table = excel.sheet_by_index(i)    #通过索引获取  

table = excel.sheet_by_name('Sheet1')   #通过表名获取   

• 获取行数和列数：

 rows=table.nrows   #获取行数

cols=table.ncols    #获取列数 

• 读取单元格

Data=table.cell(row,col).value
cell_A1 = table.row(0)[0].value
cell_A2 = table.col(1)[0].value

第一行第一列是从0开始的，注意不要丢掉 .value

• 获取整行或整列内容

Row_values=table.row_values(i)   #获取整行内容
Col_values=table.col_values(i)   #获取整列内容 

xlwt

import xlwt
from datetime import datetime

style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
    num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MMM-YY')

wb = xlwt.Workbook()
ws = wb.add_sheet('A Test Sheet')

ws.write(0, 0, 1234.56, style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3+B3"))

wb.save('example.xls')

xlutils

官网链接 xlutiles

xlutils.copy

Tools for copying xlrd.Book objects to xlwt.Workbook objects.
You would start by opening the file with xlrd:

>>> from os.path import join
>>> from xlrd import open_workbook
>>> rb = open_workbook(join(test_files,'testall.xls'), formatting_info=True, on_demand=True)

You would then use xlutils.copy to copy the xlrd.Book object into an xlwt.Workbook object:

>>> from xlutils.copy import copy
>>> wb = copy(rb)

Now that you have an xlwt.Workbook, you can modify cells and then save the changed workbook back to a file:

>>> wb.get_sheet(0).write(0,0,'changed!')
>>> wb.save(join(temp_dir.path,'output.xls'))
>>> temp_dir.listdir()
output.xls