【Python成长之路】快速掌握用python处理Excel

from openpyxl import *# 打开已有test.xlsx表wb1 = load_workbook('test.xlsx')# 创建xlsx表,结束后以test.xlsx表保存wb2 = Workbook()ws = wb2.activewb2.save('test.xlsx')

、# 创建test1表ws1 = wb1.create_sheet('test1')# 选择test1表ws2 = wb2['test1']

# 方法一：value1 = ws['A4'].value# 方法二value2 = ws.cell(row=4, column=2).value

# 行ws.cell(row=4, column=2).row# 列ws.cell(row=4, column=2).column# 坐标ws.cell(row=4, column=2).coordinate

# 切片for row in ws['A1':'C3']:    for cell in row:        print(cell.value)# 通过行或者列，下方是行for row in ws[1:3]:    for cell in row:        print(cell.value)# 遍历for row in ws.iter_rows(min_row=1,max_row=3,min_col=1,max_col=3):    for cell in row:        print(cell.value)# 列表转化，下方是行for row in list(ws.rows)[0:3]:    for cell in row:        print(cell.value)

for row in ws['A1':'A4']:    print(row.value)报错：AttributeError: 'tuple' object has no attribute 'value'

# 单一单元格写入ws['A1']='test'# 一行数据写入ws.append([1,2,3,4,5])

ws.max_columnws.max_row

# 方式一wb.remove(sheet)# 方式二del wb[sheet]

from openpyxl.styles import Font, colors, Alignment
# 设置字体、颜色：等线24号，加粗斜体，字体颜色红色myfont = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)ws['A1'].font = myfont# 设置对齐：垂直居中和水平居中ws['A1'].alignment = Alignment(horizontal='center', vertical='center')# 设置行高 ：第1行行高ws.row_dimensions[1].height = 10# 设置列宽：A列列宽ws.column_dimensions['A'].width = 10# 合并单元格ws.merge_cells('A1:C3')

# coding=utf-8# @公众号 : "鹏哥贼优秀"# @Date : 2020/4/9# @Software : PyCharm # @Python version: Python 3.7.2
from openpyxl import *
# 将ws1的i-j行数据写入ws2表def get_value(ws1,ws2,i,j):    for row in list(ws1.rows)[i:j]:        res = []        for cell in row:            res.append(cell.value)        ws2.append(res)
print('start!')wb1 = load_workbook('原始数据.xlsx')wb2 = load_workbook('test.xlsx')sheets = ['1月','2月','3月','4月','5月','6月','7月','8月','9月','10月','11月','12月']ws_first = wb1[sheets[0]]names = []# 获取所有人名for row in ws_first['C4':'C22']:    for cell in row:        names.append(row.value)
for i,name in enumerate(names):    # 保留每张sheet表1-3行的表头    ws2 = wb2.create_sheet(name)    get_value(ws_first,ws2,0,3)    # 获取单人所有月分数据，并写入ws2    for sheet in sheets:        ws1 = wb1[sheet]        get_value(ws1,ws2,i+3,i+4)
wb2.save('test.xlsx')print('end!')