Python操作excel读写

1.第三方库相关操作

工作簿：Workbook
    工作表：WorkSheet
        标题：Title
        行：  Row
        列：  Column
        单元格：Cell
        值：  Value
存储的行为：save()

2.openpyxl示例

import openpyxl
wb = openpyxl.Workbook()
ws = wb.active
ws
Out[5]: <Worksheet "Sheet">
ws.title = '课程表'
wb
Out[7]: <openpyxl.workbook.workbook.Workbook at 0x140cabdefd0>
ws['A1'].value = 'lebo.com'
ws['A1']
Out[9]: <Cell '课程表'.A1>
ws['C5'] = 'C5'
wb.save('course.xlsx')

n_list = ['4', '5', '1', '2', '9', '6']
index_rule = {value: index for index, value in enumerate(n_list)}
print(index_rule)
wb._sheets.sort(key=lambda x: index_rule[x.title])

3.xlrd、xlwt

import xlrd, xlwt

workbook_read = xlrd.open_workbook('course.xlsx')

workbook_write = xlwt.Workbook(encoding='UTF-8')

sheet_names = workbook_read.sheet_names()

print(sheet_names)

sheet1 = workbook_read.sheet_by_name('课程表')

style = xlwt.XFStyle()  # 初始化样式

font = xlwt.Font()  # 为样式创建字体

font.name = 'Times New Roman'

font.bold = True

style.font = font  # 为样式设置字体

col_list = ['课程表'] + sheet1.col_values(1)

for name in col_list:
    sheet = workbook_read.sheet_by_name(name)
    worksheet = workbook_write.add_sheet(name, cell_overwrite_ok=True)
    for i in range(sheet.nrows):
        for j in range(sheet.ncols):
            worksheet.write(i, j, sheet.cell_value(i, j), style)

workbook_write.save('course_filter.xlsx')