python 快速操作excel_python操作excel

pip install openpyxl

if __name__ == '__main__':
    work_book = openpyxl.Workbook()
    sheet = work_book.create_sheet('username_password')
    sheet.append(['id', 'loginName', 'userName', 'password'])
    users = list()
    for index, user in enumerate(users):
        password = getPassword(user['id'])
        data = [user['id'], user['loginName'], user['userName'], password]
        sheet.append(data)
    work_book.save("教师用户.xlsx")

import openpyxl
from openpyxl.worksheet.worksheet import Worksheet
 
if __name__ == '__main__':
    # 读取excel
    workbook = openpyxl.load_workbook(
        r"E:\weixin\WeChat Files\wxid_lkkvjah3yew822\FileStorage\File\2022-10\学校清单.xlsx")
    # 获取工作簿
    sheet: Worksheet = workbook['Sheet1']
    # 获取A列
    aList = sheet['A']
    # 遍历数据
    for a in aList:
        # 值
        value = a.value
        # 行索引
        row = a.row
        # 列索引
        column = a.column
        # 处理数据
        if '初级中学' in value:
            # 设置值
            sheet.cell(row, column + 1).value = '初中'
        elif '小学' in value:
            # 设置值
            sheet.cell(row, column + 1).value = '小学'
        elif '幼儿园' in value:
            # 设置值
            sheet.cell(row, column + 1).value = '幼儿园'
        elif '幼教点' in value:
            # 设置值
            sheet.cell(row, column + 1).value = '幼儿园'
        elif '教学点' in value:
            # 设置值
            sheet.cell(row, column + 1).value = '幼儿园'
 
    workbook.save("学校清单-.xlsx")

设置单元格格式：

# 设置标题格式
        for t in sheet[1]:
            t.style = "Title"
        # 设置C列格式
        for i, c in enumerate(sheet['C']):
            # 标题跳过
            if i == 0:
                continue
            # 格式
            c.number_format = 'yyyy-mm-dd hh:mm:ss'
            # 字体
            c.font = Font(name="Arial", size=14, color="00000000")
            # 对齐
            c.alignment = Alignment(horizontal='center', vertical='center')
            # 边框
            c.border = Border(top=Side(border_style="thin", color='00000000'),
                              left=Side(border_style="thin", color='00000000'),
                              right=Side(border_style="thin", color='00000000'),
                              bottom=Side(border_style="thin", color='00000000'))
            # 背景色
            c.fill = PatternFill(start_color='FFFFFFFF', end_color='FFFFFFFF', fill_type="solid")

设置列宽和行高

sheet.column_dimensions['K'].width = 35
sheet.row_dimensions[1].height = 40

合并单元：需要先合并，后填写
 

ws.merge_cells(range_string='A1:B3')
ws.merge_cells(start_row=5, start_column=4, end_row=8, end_column=8)