python读写excel

Xlrd、xlwt读写xlsx

读xlsx

import xlrd

def read_activity_xls_file(src_activity):

    dataset = []

    workbook = xlrd.open_workbook(src_activity)

    table = workbook.sheets()[0]

    print(table.nrows)

    for row in range(table.nrows-1):

    dataset.append(table.row_values(row+1))   #dataset：[[],[]...]

    # print(dataset[0:2])  

data = DataFrame(dataset)

写xlsx

import xlwt

def write():

    # 设置单元格样式，生成style

    style = xlwt.XFStyle()

    font = xlwt.Font()

    font.name = "Times New Roman"

    font.bold = False

    font.colour_index = 4

    font.height = 220

    style.font = font

   

    #写入xlsx

    wd = xlwt.Workbook()                                   #表示一个表格空间   

    sheet = wd.add_sheet('activity',cell_overwrite_ok=True)     #添加一个            sheet..cell_overwrite_ok=True表示内容可覆盖

    row0 = ['activity']                #列名

    for i in range(0,len(row0)):

        sheet.write(0,i,row0[i])       #write(行，列，value，style)

    for i in range(1,len(list)+1):     #list = [['物品体力'], ['物品钻石']...]

        for j in list[i-1]:

            sheet.write(i,0,j)

    #以上三行的另一种表达

    #i = 1

    #for j in list:

    #   sheet.write(i,0,j[0])

    #   i += 1

wd.save('test2.xlsx')

写入数据超过65535行时会报错ValueError: row index was 65536, not allowed by .xls format

解决方法：xlrd 和 xlwt 是python中用来处理 xls 文件的函数，其单个 sheet 限制最大行数为65535，因此，当读写数据量超出时就会出现如上错误。如果希望有更大的存储，建议使用 openpyxl 函数，其最大行数为1048576，存储的文件类型为 xlsx 。

 

Openpyxl读写xlsx

import openpyxl

def writetoxlsx():

data = open('./data.txt', 'r')

outwb = openpyxl.Workbook()  # 打开一个将写的文件

# outwb.create_sheet() 中 index 表示表示 sheet 序号，也可以用 ’title’ 参数设置名称。

outws = outwb.create_sheet(index=0)  # 在将写的文件创建sheet

i = 1  # 注意：'cell'函数中行列起始值为1

for line in data:  

    for x in range(0,len(line)):        

        ws.cell(column = x+1 , row = i , value = "%s" % line[x])  

    i += 1  

  

    savexlsx = "./results.xlsx"

    outwb.save(savexlsx)  # 保存结果

data.close()

删除工作表格sheet

ws = workbook["Sheet"]
workbook.remove(ws)

 

 

Demo

 

result_path = os.path.join(".\", "%s.xlsx" % day)
if os.path.exists(result_path):
    workbook = xl.load_workbook(result_path)
else:
    workbook = xl.Workbook()
    # 移除默认sheet
    ws = workbook["Sheet"]
    workbook.remove(ws)
sheet = workbook.create_sheet(title="%s_1" % sheet_name)
# 列名
row0 = ["eslid", "number of flashes", "maximum time interval", "minimum time interval", "mean time interval"]
for i in range(1, len(row0) + 1):
    sheet.cell(1, i, row0[i - 1])
# 写入数据
i = 2
for k, v in first_dict.items():
    sheet.cell(i, 1, k)
    for j in range(2, len(row0) + 1):
        sheet.cell(i, j, v[j - 2])
    i += 1
workbook.save("%s.xlsx" % day)