Python-表格操作（xlrd、xlwt、xlutils）

# Python的表格操作

import xlrd # 读表格
import xlwt # 写表格
import xlutils # 复制表格

 

file_name = 'xxx.xlsx'
# 读xlrd
# 打开
r_b = xlrd.open_workbook(filename = file_name, encoding_overrde=False, formatting_info=Ture)

# 从工作簿选取表单
# 获取所有表单的名字
r_sheets = r_b.sheet_names()
# 获取所有表格对象组合的列表
r_sheets = r_b.sheets
r_s = r_b.sheet_by_names('xxx')
r_s1 = r_b.sheet_by_index('xxxx')
r_s2 = r_b.sheets[0]
...

# 获取单个表格的信息
# 行、列
# 获取行、列数
rows = r_s.nrows()
cols = r_s.ncols()

# 读取行列、行列值
row = [r_s.row(i) for i in range(rows)] # row()带格式描述 row_values()为值
# col_value = [r_s.col[i].value for i in range(cols)]
row_value = r_s.row_values(rowx[, start_colx[, end_colx]])
col_value = r_s.col_values(colx[, start_rowx[, end_rowx]])

# 读取单元格
cell1 = r_s.cell(1,1)
cell_value = [r_s.cell(i,j) for i in range(r_s.nrows) for j in range(r_s.ncols)]
# 读取格式
cell_types = [(i, i.ctype) for i in cell_value] # 0,1,2,3,4,5 空、字符串、数字、date、bollean、error

# 根据不同的格式处理值
from date import datetime,date
cell = cell_value[0]
if cell.type == 3: (日期)
date_cell = xlrd.xldate_as_tuple(cell,workbook.datemode)
date = date(*date_cell[0:3]) # 切片取到3个元素

# 合并的单元格
merge_cells = r_s.merged_cells
# 返回元组（row,row_range,col,col_range) 从0开始
# (1,5,4,8)，0行最高，合并(1,2,3,4)行的(4,5,6,7)列 合并的值为cell_value(1,4)

# 写xlwt
# 创建工作簿对象
w_b = xlwt.Workbook()
# 新增工作表
w_s = xlwt.add_sheet('sheet1',cell_overwrite_ok=True)
# 设置写入的格式
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = 'Times New Roman'
font.hight = 200 # 字体高度
border = xlwt.Borders() # 边框
style.font = font
style.borders = border
# 写入单元格内容
w_s.write(row_index, col_index, info, style) # 从0开始编号
# 写入合并单元格
w_s.write_merge(strat_row, end_row, strat_col, end_col, info, style)
# 保存
w_b.save(file_name)

# 使用xltils复制并写入
r_b = xlrd.open_workbook(filename='file_name', encoding_override=False, formatting_info=True)
sheet_names = r_b.sheet_names()

w_b = xltils.copy.copy(r_b)
w_s = w-b.get_sheet(sheet_names[0])

w_s.write(row,col,value[,style])
# 替换原文件 导入os
os.replace(old_file_name,new_file_name)

 

import openpyxl
import xlrd
import xlutils.copy
import xlwt
import os

# openpyxl的三大对象 工作簿、工作表、单元格


# 创建工作簿对象
# wb0 = openpyxl.Workbook()
# # 创建一个testcase表单
# ws0 = wb0.create_sheet('test_case')
# # 保存为一个xlsx格式的文件
# wb0.save('cases.xlsx')

# 读取表格中的数据


# 打开工作簿
def opnexl():
    wb = openpyxl.load_workbook('cases.xlsx',)

    # 选择表单
    sh = wb['test_case']

    # 读取数据
    ce = sh.cell(row=1,column=1)
    print(ce.value)
    # print(dir(sh))
    # 获取表头
    titles = [title.value for title in list(sh.rows)[0]]
    print(titles)
    # 按行读取数据
    data = list(sh.rows)[1:]
    # print(data)
    for cases in data:
        case_id = cases[0].value
        case_excepted = cases[1].value
        case_data = cases[2].value
        print(type(cases[2].value))
        if isinstance(cases[2].value,str):
            case_data = eval(cases[2].value)
            print('str')
        else:
            case_data = cases[2].value
        print(case_excepted,case_data)
    wb.close()
    print('ok')

def use_xlrd():

    a = xlrd.open_workbook(filename='cases.xlsx',)
    # w_s = a.sheet_by_name('xx')
    w_s = a.sheets()[1]
    s_row = [w_s.row_values(i) for i in range(w_s.nrows)]
    cells = [w_s.cell(i,j) for i in range(w_s.nrows) for j in range(w_s.ncols)]
    cells_type = [(cell, cell.ctype) for cell in cells]
    cell = cells[2].value

    print('cells',cells)
    print(s_row)
    print(cells_type)
    return 1
    # s_r = w_s.row()
# b0 = w_s.row_values(1)
# b1 = w_s.col_values()
# b = a.sheet_names()
# c = a._sheet_list
# for i in c:
#     print(i)
#     print(i.nrows)
# print(b,c)
#
# d = a.sheets()[0]
# print(d)


def set_style(name,height,bold=False):
    style = xlwt.XFStyle()
    font = xlwt.Font()
    font.name = name
    font.bold = bold
    font.height = height

    borders = xlwt.Borders()
    borders.left = 2
    borders.right = 2
    borders.top = 2
    borders.bottom = 2

    style.font = font
    style.borders = borders
    return style

def write_excel():
    # by xlwt
    # w_b = xlwt.Workbook()
    # w_s = w_b.add_sheet('case_data')
    # style = set_style('Times New Roman',200)
    # for i in range(10):
    #     for j in range(10):
    #         w_s.write(i,j,i+j,style)
    # w_s.write_merge(11,14,1,2,'this is a mergeCell', style)
    # w_b.save('newbook.xlsx')
    # print('ok')

    r_b = xlrd.open_workbook('newbook.xlsx',formatting_info=True)
    sheet_names = r_b.sheet_names()
    r_s = r_b.sheet_by_name(sheet_names[0])
    rows = r_s.nrows
    cols = r_s.ncols

    w_b = xlutils.copy.copy(r_b)
    w_s = w_b.get_sheet(sheet_names[0])
    print(w_s)
    for i in range(rows):
        for j in range(cols):
            cell = r_s.cell(i,j)
            if cell.ctype == 2:
                cell_value = int(cell.value)
            else:cell_value = cell.value
            print(cell_value)
    style = set_style('Times New Roman',200,True)
    w_s.write(rows,cols,'this is a new value',style)
    w_b.save('test_copy.xlsx')

    # 覆盖原表
    os.replace('test_copy.xlsx','newbook.xlsx')






if __name__ == '__main__':
    # style = set_style(u'Times New Roman',300)
    write_excel()