Python–常用模块脚本–xlsx读写

#.xlsx读写

# coding=utf8
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, colors, Alignment  # 设置单元格风格
import datetime
from openpyxl.utils import get_column_letter  # 行列转换
from time import time
from random import choice

def read_xlsx():
    wb = load_workbook('小明工作表.xlsx')
    ws = wb['test1']
    # ws = wb.get_sheet_by_name['test1']

    row_max = ws.max_row  # 获取最大行
    col_max = ws.max_column  # 获取最大列
    # print(row_max, col_max)

    # for row in ws.rows:  # 获取每一行每一列
    #     for cell in row:
    #         print(cell.value, end=' ')
    #     print()

    # print(ws.cell(row=1, column=1).value)  # 单一单元格访问
    # cell_range = ws['A1':'C2']  # 多单元格访问

    #isinstance(ws.cell(row=row, column=2), MergedCell) #判断合并单元格

    for row in ws.iter_rows(min_row=2, max_col=3, max_row=3):
        for cell in row:
            print(cell.value, end=' ')
        print()

def write_xlsx():
    wb = Workbook()
    ws = wb.create_sheet('TEST1')
    # ws.sheet_properties.tabColor = "1072BA"  # 改变 sheet 标签按钮颜色
    # bold_itatic_24_font = Font(name='等线', size=24, italic=True, color="FF0000", bold=True)
    # centerali = Alignment(horizontal='center', vertical='center')
    # ws['A1'].font = bold_itatic_24_font  # 字体
    # ws['A1'].value = 100
    # ws['B1'].alignment = centerali  # 对齐方式
    # ws['B1'].value = 200

    # 设置行高和列宽
    # 第2行行高
    # ws.row_dimensions[2].height = 40
    # # C列列宽
    # ws.column_dimensions['C'].width = 30

    # 合并单元格， 往左上角写入数据即可
    # ws.merge_cells('C1:E3')  # 合并一行中的几个单元格
    # ws.merge_cells('C6:E8')  # 合并一个矩形区域中的单元格
    #
    # ws.unmerge_cells('C6:E8')

    ws.append(['TIME', 'TITLE', 'A-Z'])

    # 输入内容（500行数据）
    for i in range(500):
        TIME = datetime.datetime.now().strftime("%H:%M:%S")
        TITLE = str(time())
        A_Z = get_column_letter(choice(range(1, 50)))
        ws.append([TIME, TITLE, A_Z])

    wb.save('写工作表.xlsx')

# read_xlsx()
# write_xlsx()