python3读写excel之openxlpy

pip install openpyxl

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

读文件 

import openpyxl

# 打开xlsx文件
excel = openpyxl.load_workbook('./source-files/info.xlsx')
for sheet in excel:
    print(sheet.title)
# 获取所有sheet的名称
print(excel.sheetnames)
# 获取活跃的sheet
sheet = excel.active
# 通过名称获取sheet
sheet = excel['test']
# 获取前两行的数据
print(list(sheet.values)[0:2])
print(sheet.title)
# 最大列数
print(sheet.max_column)
# 最大行数
print(sheet.max_row)
# 获取单元格的行号
print(sheet['A1'].row)
# 获取单元格的列号
print(sheet['A1'].column)
# 获取单元格的值
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)
# 获取某列的数据
for cell in sheet['A']:
    print(cell.value)
# 获取某行的数据
for cell in sheet[5]:
    print(cell.value)
# 获取某些行的数据
for row in sheet[5:10]:
    row_data = [cell.value for cell in row]
    print(row_data)
# 获取某些列的数据
for column in sheet['A':'C']:
    column_data = [cell.value for cell in column]
    print(column_data)
# 获取某些区域的数据
for row in sheet['A1':'B2']:
    row_data = [cell.value for cell in row]
    print(row_data)
# 获取某些行的数据
for row in list(sheet.rows)[0:2]:
    row_data = [cell.value for cell in row]
    print(row_data)
# 获取某些列的数据
for column in list(sheet.columns)[0:2]:
    column_data = [cell.value for cell in column[0:2]]
    print(column_data)

 

写文件  

import openpyxl
from datetime import datetime
import time
import os
import random
from openpyxl.chart import LineChart, Reference
from openpyxl.utils import get_column_letter
from copy import deepcopy
from openpyxl.styles import Alignment, Font


# 新建xlsx文件
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.now()
    sheet.cell(row=i+1, column=5).value = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    sheet.cell(row=i+1, column=6).value = time.strftime("%Y-%m-%d %H:%M:%S")
    sheet.cell(row=i+1, column=7).value = get_column_letter(random.choice(range(1, 50)))
    # 公式
    sheet.cell(row=i+1, column=5).value = "=sum(A{:d}:C{:d})".format(i+1, i+1)

data = [
    ['Date', 'num1', 'num2', 'num3'],
    ['2022-09', 40, 30, 25],
    ['2022-10', 40, 25, 30],
    ['2022-11', 50, 30, 45],
    ['2022-12', 30, 25, 40],
]
for item in data:
    sheet.append(item)
# 插入图表
chart = LineChart()
# 定义数据源并添加数据
refer = Reference(sheet, min_col=2, min_row=6, max_col=4, max_row=10)
chart.add_data(refer, titles_from_data=True)
# 定义类目轴，并添加类目数据
cats = Reference(sheet, min_col=1, max_col=1, min_row=7, max_row=10)
chart.set_categories(cats)
# 设定图表样式
s1 = chart.series[0]
s1.marker.symbol = "triangle"
s1.marker.graphicalProperties.solidFill = "FF0000"  # Marker filling
s1.marker.graphicalProperties.line.solidFill = "FF0000"  # Marker outline
s1.graphicalProperties.line.noFill = True
s2 = chart.series[1]
s2.graphicalProperties.line.solidFill = "00AAAA"
s2.graphicalProperties.line.dashStyle = "sysDot"
s2.graphicalProperties.line.width = 100050  # width in EMUs
s2 = chart.series[2]
s2.smooth = True
# 设定图表属性
chart.title = "Line Chart"
chart.style = 12
chart.y_axis.title = 'num'
chart.x_axis.title = 'month'
sheet.add_chart(chart, "A12")
# 图表 group
stacked = deepcopy(chart)
stacked.grouping = "stacked"
stacked.title = "Stacked Line Chart"
sheet.add_chart(stacked, "K12")
# 插入图片
img = openpyxl.drawing.image.Image('./source-files/img1.png')
sheet.add_image(img, 'A27')


# 新建一个sheet
sheet = excel.create_sheet('sheet', 0)
sheet.title = 'test'
# 设置sheet标签的颜色
sheet.sheet_properties.tabColor = '0d6efd'
# 合并单元格
sheet.merge_cells('A1:A4')
# 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)

# 写入值
sheet['A1'] = '中文'

# 设置行高
sheet.row_dimensions[1].height = 22
# 设置列宽
sheet.column_dimensions['A'].width = 25

# 设置字体样式
style_font = Font(name='宋体', size=24, italic=True, bold=True, vertAlign=None, underline='none', strike=False, color='FF0000')
sheet['A1'].font = style_font


# 设置单元格对齐方式
style_alignment = Alignment(horizontal='general', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0)
sheet.cell(row=1, column=1).alignment = style_alignment

# 复制工作表 不包括源工作表中的图片和图表
copy = excel.copy_worksheet(excel['info'])
copy.title = 'backup'

# 删除工作表
sheet = excel.create_sheet('del')
excel.remove(sheet)

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