python excel的操作

1、在测试用例中生成的数据报错到已存在的excel里面

 1 import xlrd
 2 from xlutils.copy import copy
 3 class test:
 4     def write_data_into_excel(self, file_path, data_list):
 5         wk = xlrd.open_workbook(file_path)
 6         workbook = copy(wk)   # a writable copy (I can't read values out of this, only write to it)
 7         s = workbook.get_sheet(0)
 8         s.write(0, 0, data_list[0])    # 写入第一行第一列
 9         s.write(0, 1, data_list[1])    # 写入第一行第二列
10         workbook.save(file_path)    PS: save的file_path与open的可以一样。

 

2、把excel中的某个单元格的值删除（等价于把这个单元格的值赋值为空值）

import xlrd
from xlutils.copy import copy
class test:
    def delete_excel_value(self, file_path):
        wk = xlrd.open_workbook(file_path)
        workbook = copy(wk)
        s = workbook.get_sheet(0)
        s.write(0, 0, '')
        s.write(0, 1, '')
        workbook.save(file_path)
        print("Delete finished.")

 

3、读取全部单元格的值

import xlrd
class test:
    def get_excel_column(self, file_path):
        workbook = xlrd.open_workbook(file_path)
        sheet = workbook.sheet_by_name('Sheet1')
        clo_num = sheet.ncols  # 获取列数
        row_num = sheet.nrows  # 获取行数
        data_list = []
        i = 0
        if i in range(row_num):
            data_list.append(sheet.row_values(i))
        return data_list

 

4、获取指定单元格的值

wb = xlrd.open_workbook('excel.xlsx')
sheet = wb.sheet_by_name('Sheet1')
v1 = sheet.cell_value(0, 0)   # 获取第1行第1列