python3读写excel之openxlpy

pip install openpyxl

 openpyxl 读写 xlsx 文件，不处理 xls 文件

import openpyxl
import locale
import datetime
import time
import os

# 读xlsx文件
excel = openpyxl.load_workbook('./source-files/info.xlsx')
sheet = excel.active
sheet = excel.get_sheet_by_name('test')
print(list(sheet.values))  # sheet.values 生成器
print(sheet.max_column)  # 最大列数
print(sheet.max_row)  # 最大行数
print(sheet['A1'].value)
print(sheet.cell(1, 1).value)
for row in sheet.iter_rows(max_row=1):
    title_row = [cell.value for cell in row]
print(title_row)
for row in sheet.iter_rows(min_row=2, max_row=5):
    row_data = [cell.value for cell in row]
    print(row_data)

# 写xlsx文件
locale.setlocale(locale.LC_CTYPE, 'chinese')
excel = openpyxl.Workbook()
sheet = excel.active
sheet.title = "info"
sheet.append(['name', 'age', 'class', 'datetime'])
for i in range(5)[1:]:
    for j in range(4)[1:]:
        sheet.cell(row=i+1, column=j).value = i+j
    sheet.cell(row=i+1, column=4).value = datetime.datetime.now()
    sheet.cell(
        row=i+1, column=5).value = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

sheet = excel.create_sheet('sheet', 0)  # 新建一个sheet
sheet.title = 'test'
sheet.sheet_properties.tabColor = '0d6efd'
sheet.merge_cells('A2:D4')
# sheet.unmerge_cells('A2:D4')
sheet.merge_cells(start_row=2, start_column=1, end_row=2, end_column=4)
# sheet.unmerge_cells(start_row=2, start_column=1, end_row=2, end_column=4)
img = openpyxl.drawing.image.Image('./source-files/img1.png')
sheet.add_image(img, 'D4')
sheet.row_dimensions[1].height = 22
sheet.column_dimensions['A'].width = 25
sheet.cell(row=1, column=1).alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')

copy = excel.copy_worksheet(excel['info'])
copy.title = 'backup'
for cell in copy['A']:
    print(cell.value)
for cols in copy['A:C']:
    for cell in cols:
        print(cell.value)
for row in copy['1:3']:
    for cell in row:
        print(cell.value)
for row in copy.iter_rows():
    for cell in row:
        print(cell.value)
for row in copy.iter_cols():
    for cell in row:
        print(cell.value)
for row in copy.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3):
    for cell in row:
        print(cell.value)
for row in copy.rows:
    for cell in row:
        print(cell.value)
for col in copy.columns:
    for cell in col:
        print(cell.value)
print(copy.max_row, copy.max_column, copy.min_row, copy.min_column)
print(excel.sheetnames)
for sheet in excel:
    print(sheet.title)

if os.path.exists('./gen-files/test.xlsx'):
    os.remove('./gen-files/test.xlsx')
excel.save('./gen-files/test.xlsx')