pip install xlrd
pip install xlwt
pip install xlutils

import xlrd
file = xlrd.open_workbook("excel_test.xlsx")
all_sheet = file.sheet_names() # 獲取所有的工作簿名
sheet_name1 = file.sheet_names()[0] # 通過(guò)sheet下標(biāo)獲取，第一個(gè)sheet下標(biāo)為0
sheet_name2 = file.sheet_by_index(0).name # 通過(guò)sheet索引獲取sheet名
print(all_sheet)
print(sheet_name1)
print(sheet_name2)
-----------------------------------------
# 返回結(jié)果
['員工信息表', 'api測(cè)試用例']
員工信息表
員工信息表

import xlrd
file = xlrd.open_workbook("excel_test.xlsx")
sheet_name1 = file.sheet_names()[0]
sheet1_obj = file.sheet_by_name(sheet_name1) # 通過(guò)sheet名獲取sheet對(duì)象
sheet2_obj = file.sheet_by_index(1) # 通過(guò)sheet索引獲取sheet對(duì)象
print(sheet1_obj)
print(sheet2_obj)
------------------------------------
# 返回結(jié)果
<xlrd.sheet.Sheet object at 0x0000000002AA09B0>
<xlrd.sheet.Sheet object at 0x0000000002AA0978>

import xlrd
file = xlrd.open_workbook("excel_test.xlsx")
sheet = file.sheet_by_index(0) # 通過(guò)sheet索引獲取sheet對(duì)象
nrows = sheet.nrows # 獲取行數(shù)
ncols = sheet.ncols # 獲取列數(shù)
nrows_data = sheet.row_values(1) # 獲取第二行數(shù)據(jù)，返回的是列表
ncols_data = sheet.col_values(0) # 獲取第一列數(shù)據(jù)，返回的是列表
cell = sheet.cell(1,2)  # 獲取單元格數(shù)據(jù),如第二行，第三列數(shù)據(jù)
print(nrows)
print(ncols)
print(nrows_data)
print(ncols_data)
print(cell)
-------------------------------
# 返回結(jié)果
6
5
['王五', '男', 32883.0, 'java開(kāi)發(fā)工程師', 233.0]
['姓名', '王五', '李四', '張三', '小紅', '小明']
xldate:32883.0 # 這里日期數(shù)據(jù)直接返回成浮點(diǎn)數(shù)了

print(sheet.cell(1,2).ctype)
------------
# 返回日期數(shù)據(jù)的結(jié)果
3

import xlrd
from datetime import date
file = xlrd.open_workbook("excel_test.xlsx")
sheet = file.sheet_by_index(0) # 通過(guò)sheet索引獲取sheet對(duì)象
nrows_data = sheet.row_values(1) # 獲取第二行數(shù)據(jù)，返回的是列表
ncols_data = sheet.col_values(0) # 獲取第一列數(shù)據(jù)，返回的是列表
cell = sheet.cell(1,2)  # 獲取單元格數(shù)據(jù),如第二行，第三列數(shù)據(jù)，返回的是浮點(diǎn)數(shù)
data_value = xlrd.xldate_as_tuple(sheet.cell_value(1,2) ,file.datemode) # xldate_as_tuple()方法得到日期數(shù)據(jù)年月日時(shí)分秒的值并返回為元組
datatime2 = date(*data_value[:3]).strftime('%Y/%m/%d') # 截取元組中的前三位，即年月日的值傳給data，并進(jìn)行時(shí)間格式化
 
print(cell)
print(data_value)
print(datatime2)
 
-----------------------
# 返回結(jié)果
xldate:32883.0
(1990, 1, 10, 0, 0, 0)
1990/01/10

if (sheet.cell(row,col).ctype == 3):
 date_value = xlrd.xldate_as_tuple(sheet.cell_value(row,col),file.datemode)
 date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d')

import xlwt
 
workbook = xlwt.Workbook(encoding = 'utf-8') # 創(chuàng)建工作簿
sheet = workbook.add_sheet('api_test') # 添加一個(gè)sheet
data = sheet.write(0,0,'test')  # 向第一行第一列寫(xiě)入數(shù)據(jù)：test
workbook.save('book.xlsx')   # 保存到book.xlsx中

import xlwt
import datetime
workbook = xlwt.Workbook(encoding = 'utf-8') # 創(chuàng)建工作簿
sheet = workbook.add_sheet('api_test') # 添加一個(gè)sheet
data = sheet.write(4,0,'test')
 
sheet.col(0).width = 5000   # 設(shè)置單元格寬度
 
style = xlwt.XFStyle()    # 初始化表格樣式
style.num_format_str = 'M/D/YY'  # 設(shè)置時(shí)間格式，如：M/D/YY
sheet.write(5, 1, datetime.datetime.now(), style) # 寫(xiě)入時(shí)間格式數(shù)據(jù)
 
# 合并多列和和并多行
# 表示合并的行數(shù)是：按行索引，從0行到第0行，按列索引從0列到第3列，合并后并寫(xiě)入數(shù)據(jù):test1
sheet.write_merge(0, 0, 0, 3, 'test1')
# 表示合并的行數(shù)是：按行索引，從1行到第2行，按列索引從0列到第3列，合并后并寫(xiě)入數(shù)據(jù):test2
sheet.write_merge(1, 2, 0, 3, 'test2')
 
# 向單元格添加超鏈接
sheet.write(6, 0, xlwt.Formula('HYPERLINK("https://www.baidu.com/";"baidu")'))
 
workbook.save('book.xlsx')   # 保存到book.xlsx中

import xlrd
from xlutils.copy import copy
excel_file = xlrd.open_workbook("book.xlsx")
new_file = copy(excel_file) # 拷貝文件對(duì)象
new_file.save("book1.xlsx") # 保存為excel文件

import xlrd
from xlutils.copy import copy
excel_file = xlrd.open_workbook("book.xlsx")
new_file = copy(excel_file)
sheet = new_file.get_sheet(0) # 獲取表格的第一個(gè)sheet
sheet.write(0,1,"測(cè)試") # 第一行第二列寫(xiě)入：測(cè)試
sheet.write(1,1,"測(cè)試1") # 第二行第二列寫(xiě)入：測(cè)試1
new_file.save("book.xlsx")

from xlrd import open_workbook
class Readexcel():
 
 def excel_data_list(self, filename, sheetname):
 '''
 :param filename: excel文件名稱
 :param sheetname: excel中表格sheet名稱
 :return: data_list
 '''
 data_list = []
 wb = open_workbook(filename) # 打開(kāi)excel
 sh = wb.sheet_by_name(sheetname) # 定位工作表
 header = sh.row_values(0) # 獲取標(biāo)題行的數(shù)據(jù)
 for i in range(1, sh.nrows): # 跳過(guò)標(biāo)題行，從第二行開(kāi)始獲取數(shù)據(jù)
  col_datas = dict(zip(header, sh.row_values(i))) # 將每一行的數(shù)據(jù)，組裝成字典
  data_list.append(col_datas) # 將字典添加到列表中 ，列表嵌套字典，每個(gè)元素就是一個(gè)字典
 return data_list
 
if __name__ == '__main__':
 Readexcel()

 data_list = Readexcel().excel_data_list('excel_test.xlsx','api測(cè)試用例')
 print(data_list)
-----------------------
# 返回結(jié)果
[
 {'module': '視頻安防', 'url': 'http://imp-t.tfgreenroad.com:18092/console_api/recep/tv/list', 'id': 1.0, 'params': '{ "queryMsg":"","regionCode":"","devtypeCode":"","online":"","offset":"","limit":1,"type":""}', 'method': 'get', 'actual_res': '', 'data': '', 'expect_res': '', 'test_res': '', 'case_name': '分頁(yè)查詢視頻安防設(shè)備列表', 'files': ''},
 {'module': '平臺(tái)管理', 'url': 'http://imp-t.tfgreenroad.com:18092/console_api/manage/appsys/info', 'id': 2.0, 'params': '', 'method': 'post', 'actual_res': '', 'data': '{"appName": "hahh","appId": "34434343","appUrl": "http://12306.com","appCode": "89","remark":""}', 'expect_res': '{"code": 200,"errMsg": ""}', 'test_res': '', 'case_name': '應(yīng)用管理-單個(gè)應(yīng)用系統(tǒng)添加', 'files': ''},
 {'module': '平臺(tái)管理', 'url': 'http://imp-t.tfgreenroad.com:18092/console_api/manage/appsys/info/upappid/89', 'id': 3.0, 'params': '', 'method': 'put', 'actual_res': '', 'data': '{"appId": "3232327676888"}', 'expect_res': '{"code": 200,"errMsg": ""}', 'test_res': '', 'case_name': '應(yīng)用管理-修改應(yīng)用appId', 'files': ''},
 {'module': '平臺(tái)管理', 'url': 'http://imp-t.tfgreenroad.com:18092/console_api/manage/devtype/list', 'id': 4.0, 'params': '{ "queryMsg":"15002","offset":"","limit":""}', 'method': 'get', 'actual_res': '', 'data': '', 'expect_res': '', 'test_res': '', 'case_name': '設(shè)備分類-分頁(yè)獲取設(shè)備類型', 'files': ''}
]

from xlutils.copy import copy
from xlrd import open_workbook
 
class Write_excel():
 def write_result(self, filename, row, col1,col2,actual_res, test_res,sheet_name):
 '''
 :param filename: 文件名
 :param row: 要寫(xiě)回的行
 :param col1: 要寫(xiě)回的列
 :param col2: 要寫(xiě)回的列
 :param actual_res: 實(shí)際結(jié)果
 :param test_res: 測(cè)試結(jié)果 ：pass/failed
 :param sheet_name:指定的sheet表索引
 :return:
 '''
 old_workbook = open_workbook(filename)
 # 將已存在的excel拷貝進(jìn)新的excel
 new_workbook = copy(old_workbook)
 # 獲取sheet
 new_worksheet = new_workbook.get_sheet(sheet_name) # 第n個(gè)sheet,0表示第一個(gè)sheet
 # 寫(xiě)入數(shù)據(jù)
 new_worksheet.write(row, col1, actual_res)
 new_worksheet.write(row, col2, test_res)
 # 保存
 new_workbook.save("book.xlsx")
 
if __name__ == '__main__':
 Write_excel()