python excel文件读、写、改

# ==========================================================================================
# xls文件写操作
# ==========================================================================================
import xlwt
#只能写不能读
stus = [['姓名', '年龄', '性别', '攻击'],
        ['安其拉', 20, '女', 80],
        ['王昭君', 20, '女', 80],
        ['花木兰', 20, '女', 80],
        ['甄姬', 20, '女', 80]
        ]
book = xlwt.Workbook()#新建一个excel
sheet = book.add_sheet('case1_sheet')#添加一个sheet页
row = 0#控制行
for stu in stus:
    col = 0#控制列
    for s in stu:#再循环里面list的值，每一列
        sheet.write(row,col,s)
        col+=1
    row+=1
book.save('./case.xls')#保存到当前目录下
====================================================================================================
# xlsx文件写操作
====================================================================================================
import xlsxwriter

#生成excel文件
def generate_excel(expenses):
    workbook = xlsxwriter.Workbook('./case.xlsx')
    worksheet = workbook.add_worksheet()

    # 设定格式，等号左边格式名称自定义，字典中格式为指定选项
    # bold：加粗，num_format:数字格式
    bold_format = workbook.add_format({'bold': True})
    #money_format = workbook.add_format({'num_format': '$#,##0'})
    #date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})

    # 将二行二列设置宽度为15(从0开始)
    worksheet.set_column(1, 1, 15)

    # 用符号标记位置，例如：A列1行
    worksheet.write('A1', 'sku_id', bold_format)
    worksheet.write('B1', 'sku_title', bold_format)
    worksheet.write('C1', 'id_1', bold_format)
    worksheet.write('D1', 'id_1_doc', bold_format)
    worksheet.write('E1', 'id_2_doc', bold_format)
    worksheet.write('F1', 'id_2_doc', bold_format)
    row = 1
    col = 0
    for item in (expenses):
            # 使用write_string方法，指定数据格式写入数据
            worksheet.write_string(row, col, str(item['sku_id']))
            worksheet.write_string(row, col + 1, item['sku_title'])
            worksheet.write_string(row, col + 2, str(item['id_1']))
            worksheet.write_string(row, col + 3, item['id_1_doc'])
            worksheet.write_string(row, col + 4, str(item['id_2']))
            worksheet.write_string(row, col + 5, item['id_2_doc'])
            row += 1
    workbook.close()


if __name__ == '__main__':
    rec_data = [{'sku_id':11,'id_1':22,'id_2':33,'id_2_doc':u'测试数据一','sku_title': u'测试数据二','id_1_doc': u'测试数据三 \n'}]
    generate_excel(rec_data)

# ============================================================================================
# excel 读操作
# ============================================================================================
import xlrd

book = xlrd.open_workbook('./case.xls')#打开一个excel
sheet = book.sheet_by_index(0)#根据顺序获取sheet
sheet2 = book.sheet_by_name('case1_sheet')#根据sheet页名字获取sheet
print(sheet.cell(0,0).value)#指定行和列获取数据
print(sheet.cell(0,1).value)
print(sheet.cell(0,2).value)
print(sheet.cell(0,3).value)
print(sheet.ncols)#获取excel里面有多少列
print(sheet.nrows)#获取excel里面有多少行
print(sheet.get_rows())#
for i in sheet.get_rows():
    print(i)#获取每一行的数据
print(sheet.row_values(0))#获取第一行
for i in range(sheet.nrows):#0 1 2 3 4 5
    print(sheet.row_values(i))#获取第几行的数据
print(sheet.col_values(1))#取第一列的数据
for i in range(sheet.ncols):
    print(sheet.col_values(i))#获取第几列的数据

 

---

  excel 修改操作

from xlutils.copy import copy
import xlrd
#xlutils:修改excel
stus = [
        ['闵月', 30, '女', 70],
        ['阿珂', 20, '女', 90]
       ]
book1 = xlrd.open_workbook('case.xls')
book2 = copy(book1)#拷贝一份原来的excel
# print(dir(book2))
sheet = book2.get_sheet(0)#获取第几个sheet页，book2现在的是xlutils里的方法，不是xlrd的
row = 0#控制行
for stu in stus:
    col = 0#控制列
    for s in stu:#再循环里面list的值，每一列
        sheet.write(row,col,stus)
        col+=1
    row+=1
book2.save('case.xls')