openpyxl 和 xlrd&xlwt 都能对excel进行读写，但是它们读写的格式不同，openpyxl 只能读写 xlsx格式的excel，xlrd&xlwt 只能读写 xls格式的excel。

openpyxl

创建excel文件

import openpyxl

# ====== 创建格式为xlsx的excel文件 ======

创建一个工作簿,若表格已存在，则覆盖

wb = openpyxl.Workbook()

创建一个名为test的sheet

wb.create_sheet('test')

保存文件。注：创建表格会有两个sheet,按顺序名称分别为Sheet和test

wb.save('test.xlsx')

关闭工作薄

wb.close()

读写已有excel文件

import openpyxl

# ====== 打开已有的excel表格 ======

打开一个工作簿

wb = openpyxl.load_workbook('test.xlsx')

选择一个sheet

sheet = wb["Sheet1"] # 通过表名选择

sheet = wb.worksheets[0] # 通过索引选择

关闭工作薄

wb.close()

对sheet进行读写

# coding:utf-8
import openpyxl

====== 向sheet写入数据 ======

打开一个工作簿

wb = openpyxl.load_workbook('test.xlsx')

选择一个sheet

sheet = wb["Sheet1"] # 通过表名选择

sheet = wb.worksheets[0] # 通过索引选择

获取行数

row = sheet.max_row

获取列数

column = sheet.max_column
print(row, column)

写入数据

sheet.append(["aaa", "bbb", "ccc"]) # 在最后一行写入一行数据，列表中每一个数据表示每列写入的数据
sheet.append([1, 2, 3, 4])

读取数据

ce = sheet.cell(row=1, column=1) # 读取第1行，第1列的数据
print(ce.value)

更新数据

ce.value = "ddd" # 更新第1行，第1列的数据为 ddd
sheet.cell(3, 1, '') # 更新第3行第1列的数据为 空串

删除数据

从第2行开始删除，删除1行

sheet.delete_rows(2, amount=1)

删除第3列

sheet.delete_cols(3)

保存文件

wb.save('test.xlsx')

关闭工作薄

wb.close()

# coding:utf-8
import openpyxl
import os

class Workbook:
workbook = None
filename = None

@classmethod  
def get\_workbook(cls, filename, is\_create=False):  
    """ 获取工作簿，若文件存在则打开，若文件不存在则新建 """  
    cls.filename = filename  
    is\_file = os.path.exists(filename)  
    if is\_create:  
        is\_file = False  
    if is\_file:  
        cls.workbook = openpyxl.load\_workbook(filename)  
    else:  
        cls.workbook = openpyxl.Workbook()  
    return cls()

def sheet\_by\_name(self, sheet\_name):  
    """ 通过表的名称获取sheet对象 """  
    ws = WorkSheet(self.workbook)  
    return ws.sheet\_by\_name(sheet\_name)

def sheet\_by\_index(self, index):  
    """ 通过表的索引获取sheet对象 """  
    ws = WorkSheet(self.workbook)  
    return ws.sheet\_by\_index(index)

def save(self, filename=None):  
    if filename is None:  
        filename = self.filename  
    self.workbook.save(filename)

def close(self):  
    self.workbook.close()

class WorkSheet:

def \_\_init\_\_(self, workbook):  
    self.workbook = workbook  
    self.sheet = None

def sheet\_by\_name(self, name):  
    """ 通过表的名称获取sheet对象 """  
    self.sheet = self.workbook\[name\]  
    return self

def sheet\_by\_index(self, index):  
    """ 通过表的索引获取sheet对象 """  
    self.sheet = self.workbook.worksheets\[index\]  
    return self

def get\_cell\_value(self,  row, column):  
    """ 获取单元格的值 """  
    return self.sheet.cell(row, column).value

def get\_col\_values(self, column):  
    """ 获取指定列的所有值 """  
    col\_values = \[\]  
    rows = self.rows  
    for row in range(rows):  
        row += 1  
        v = self.get\_cell\_value(row, column)  
        col\_values.append(v)  
    return col\_values

def get\_row\_values(self, row):  
    """ 获取指定行的所有值 """  
    row\_values = \[\]  
    columns = self.columns  
    for column in range(columns):  
        column += 1  
        v = self.get\_cell\_value(row, column)  
        row\_values.append(v)  
    return row\_values

def write\_cell\_value(self, row, column, value):  
    """ 写入或更新单元格的值 """  
    return self.sheet.cell(row, column, value)

def insert\_row\_values(self, values, row=None):  
    """  
    插入一行数据，默认插入最后一行  
    :param values: 插入的数据  
    :type values: list  
    :param row: 行号  
    :type row: int  
    """  
    if row is None:  
        self.sheet.append(values)  
    else:  
        self.sheet.insert\_rows(row)  
        col = 1  
        for v in values:  
            self.write\_cell\_value(row, col, v)  
            col += 1

def insert\_column\_values(self, values, column=None):  
    """ 插入一列数据，默认插入最后一列 """  
    if column is None:  
        col = self.columns  
        row = self.rows  
        v = self.get\_cell\_value(1, 1)  
        if col == 1 and row == 1 and v is None:  
            column = 1  
        else:  
            column = self.columns + 1  
    else:  
        self.sheet.insert\_cols(column)  
    row = 1  
    for v in values:  
        self.write\_cell\_value(row, column, v)  
        row += 1

def delete\_rows(self, row, amount=1):  
    """ 删除行数, row表示删除起始行，amount表示删除的行数 """  
    self.sheet.delete\_rows(row, amount)

@property  
def rows(self):  
    """ 表格的行数 """  
    return self.sheet.max\_row

@property  
def columns(self):  
    """ 表格的列数 """  
    return self.sheet.max\_column

def del_wrap(values):
""" 删除换行符 """
if isinstance(values, str):
values = [values]
new_values = []
for value in values:
new_values.append(value.replace("\n", ""))
return new_values

if __name__ == '__main__':
# 打开一个工作簿
filename = r'C:\Users\41850\Desktop\test.xlsx'
wb = Workbook.get_workbook(filename)

# 选择一个sheet  
sheet = wb.sheet\_by\_index(0)      # 通过索引选择

RequireNum = \[u"需求编号"\]  
RequireNum\_col\_values = None

TestModel = \[u"测试项"\]  
TestModel\_col\_values = None

CaseName = \[u"用例名称", u"TestSummary"\]  
CaseName\_col\_values = None

Step = \[u"操作步骤", u"Action"\]  
Step\_col\_values = None

ExpectResult = \[u"预期结果", u"Result"\]  
ExpectResult\_col\_values = None

# 获取各字段的值  
fields = sheet.get\_row\_values(1)  
for field in fields:  
    if field in RequireNum:  
        RequireNum\_col\_num = fields.index(field) + 1    # 需求编号列号  
        RequireNum\_col\_values = sheet.get\_col\_values(RequireNum\_col\_num)\[1:\]  
        RequireNum\_col\_values\_new = \[\]  
        t\_value = None  
        for value in RequireNum\_col\_values:  
            if value is not None:  
                RequireNum\_col\_values\_new.append(value)  
                t\_value = value  
            else:  
                RequireNum\_col\_values\_new.append(t\_value)  
        RequireNum\_col\_values = RequireNum\_col\_values\_new  
    elif field in TestModel:  
        TestModel\_col\_num = fields.index(field) + 1     # 测试项列号  
        TestModel\_col\_values = sheet.get\_col\_values(TestModel\_col\_num)\[1:\]  
        TestModel\_col\_values\_new = \[\]  
        t\_value = None  
        for value in TestModel\_col\_values:  
            if value is not None:  
                TestModel\_col\_values\_new.append(value)  
                t\_value = value  
            else:  
                TestModel\_col\_values\_new.append(t\_value)  
        TestModel\_col\_values = TestModel\_col\_values\_new  
    elif field in CaseName:  
        CaseName\_col\_num = fields.index(field) + 1  # 用例名称列号  
        CaseName\_col\_values = sheet.get\_col\_values(CaseName\_col\_num)\[1:\]  
        CaseName\_col\_values = del\_wrap(CaseName\_col\_values)  
    elif field in Step:  
        Step\_col\_num = fields.index(field) + 1  # 操作步骤列号  
        Step\_col\_values = sheet.get\_col\_values(Step\_col\_num)\[1:\]  
        Step\_col\_values = del\_wrap(Step\_col\_values)  
    elif field in ExpectResult:  
        ExpectResult\_col\_num = fields.index(field) + 1  # 操作步骤列号  
        ExpectResult\_col\_values = sheet.get\_col\_values(ExpectResult\_col\_num)\[1:\]  
        ExpectResult\_col\_values = del\_wrap(ExpectResult\_col\_values)

# 检查必要字段  
if not all(\[CaseName\_col\_values, Step\_col\_values, ExpectResult\_col\_values\]):  
    raise ValueError("缺少必要字段名称，必要字段名称为：用例名称、操作步骤、预期结果")

# 拼接用例编号和测试项  
if RequireNum\_col\_values:  
    TestModel\_col\_values\_new = \[\]  
    for RequireNum\_col\_value, TestModel\_col\_value in zip(RequireNum\_col\_values, TestModel\_col\_values):  
        TestModel\_col\_values\_new.append(u"【%s-%s】" % (RequireNum\_col\_value, TestModel\_col\_value))  
    TestModel\_col\_values = TestModel\_col\_values\_new  
CaseName\_col\_values\_new = \[\]

# 拼接测试项和用例名称  
for TestModel\_col\_value, CaseName\_col\_value in zip(TestModel\_col\_values, CaseName\_col\_values):  
    CaseName\_col\_values\_new.append(TestModel\_col\_value + CaseName\_col\_value)  
CaseName\_col\_values = CaseName\_col\_values\_new

# 拼接用例名称、操作步骤、预期结果  
insert\_values = \[\]  
insert\_values.append("TCID;TestSummary;Action;Result")

tcid = 1  
for case\_name, step, expect\_result in zip(CaseName\_col\_values, Step\_col\_values, ExpectResult\_col\_values):  
    v = "%s;%s;%s;%s" % (tcid, case\_name, step, expect\_result)  
    insert\_values.append(v)  
    tcid += 1

# 将数据另存到新的文件  
filename\_path = filename.split('\\\\')  
new\_filename = "new\_" + filename\_path\[-1\]  
filename\_path.pop()  
filename\_path.append(new\_filename)  
new\_filename\_path = "\\\\".join(filename\_path)  
new\_wb = Workbook.get\_workbook(new\_filename\_path, is\_create=True)

# 选择一个sheet  
sheet\_new = wb.sheet\_by\_index(0)  # 通过索引选择

# 写入新的数据  
sheet\_new.insert\_column\_values(insert\_values)

# 保存文件  
new\_wb.save(new\_filename\_path)

# 关闭工作薄  
wb.close()  
new\_wb.close()

xlrd&xlwt

xlrd

xlrd只能对excel文件进行读

# coding:utf-8
import xlrd

使用xlrd创建一个工作薄对象

workbook = xlrd.open_workbook('C:/Users/41850/Desktop/test.xls')

根据工作表的名称创建表格对象

sheet = workbook.sheet_by_name('Sheet1')

根据工作表的索引创建表格对象，索引从0开始

sheet = workbook.sheet_by_index(0)

获取sheet名称

sheet_name = sheet.name
print(u"表格名称: %s" % sheet_name)

获取工作表的行数

row_count = sheet.nrows

获取工作表的列数

col_count = sheet.ncols
print("行数: %s 列数: %s" % (row_count, col_count))

获取数据

row_value = sheet.row_values(0) # 获取第1行数据
col_value = sheet.col_values(0) # 获取第1列数据
cell_value = sheet.cell_value(0, 1) # 获取第1行第2列数据
print("获取的数据值为：%s, %s, %s" %(row_value, col_value, cell_value))

xlwt

xlwt只能对xls文件进行写

# coding:utf-8
import xlwt

打开一个工作薄

filename = 'C:/Users/41850/Desktop/test1.xls'
write_book = xlwt.Workbook(encoding="utf-8")

新增个表格，若文件已存在，则覆盖

sheet = write_book.add_sheet('test')

写入数据(行号， 列号， 写入值)

sheet.write(0, 0, 123.456)
sheet.write(1, 0, 789)
sheet.write(2, 0, 'hello')

保存

write_book.save(filename)

注意，xlwt没有直接修改已有 xls 文件的方法。通常的做法是，读取出文件，复制一份数据，对其进行修改，再保存。

# coding:utf-8
import xlrd
from xlutils.copy import copy

打开文件

filename = 'C:/Users/41850/Desktop/test1.xls'
rb = xlrd.open_workbook(filename)

复制

wb = copy(rb)

选取表单

s = wb.get_sheet(0)

写入数据

s.write(0, 1, 'new data')

保存

wb.save(filename)