Python操作Excel

Python操作Excel

需要安装第三方模块，这里选用openpyxl（适用于最新的xlsx格式）

pip install openpyxl

读Excel

1.sheet相关操作

from openpyxl import load_workbook
wb = load_workbook("pdata.xlsx")

# 1.获取所有sheet名称
print(wb.sheetnames)

# 2.选择sheet，基于名称
sheet = wb["Sheet1"]
cell = sheet.cell(2,1) # 获取指定单元格 (行,列)
print(cell.value) # 获取指定单元格的值

# 3.选择sheet,基于索引
sheet = wb.worksheets[0]
cell = sheet.cell(2,1) # 获取指定单元格 
print(cell.value) # 获取指定单元格的值

2.单元格操作

from openpyxl import load_workbook
wb = load_workbook("pdata.xlsx")
sheet = wb.worksheets[0]

# 1.获取某个单元格
# cell = sheet.cell(2,1) # 获取指定单元格，方式一
cell = sheet['A2']       # 获取指定单元格，方式一
print(cell.value) # 取内容
print(cell.style) # 样式
print(cell.alignment) # 对齐方式

# 2.获取第N行所有单元格数据
for cell in sheet[1]:
    print(cell.value)
    
# 3.获取所有行(某一列)的单元格数据
for row in sheet.rows:
    print(row[0].value,row[1].value)
    
# 4.获取所有列(某一行)的单元格数据
for col in sheet.columns:
    print(col[0].value,col[1].value)
# 5.有合并单元格，值为左上角，其他为None

写Excel

1.原文件基础上写

from openpyxl import load_workbook

wb = load_workbook("pdata.xlsx")

sheet = wb.worksheets[0]
cell = sheet.cell(2,1)
cell.value = '新接口'  # 直接赋予新值
wb.save("pdata.xlsx")  # 保存时文件需要关闭状态

2.新建文件写

from openpyxl import workbook

wb = workbook.Workbook()  # 创建excel默认会创建一个sheet

sheet = wb.worksheets[0]
cell = sheet.cell(2,1)
cell.value = '新接口'  # 直接赋予值
wb.save("pdata1.xlsx")  # 保存时文件需要关闭状态

3.操作sheet

# 修改sheet名称
sheet = wb.worksheets[0]
sheet.title = '个人计划'
wb.save("pdata1.xlsx")

# 新建sheet并设置颜色
sheet = wb.create_sheet("新计划",0)
sheet.sheet_properties.tabColor = '3c7d0d'
wb.save("pdata1.xlsx")

# 默认打开sheet
wb.active = 0
wb.save("pdata1.xlsx")

# 拷贝sheet
new_sheet = wb.copy_worksheet(wb["个人计划"])
wb.save("pdata1.xlsx")

# 删除sheet
del wb["个人计划"]
wb.save("pdata1.xlsx")

4.写单元格操作

from openpyxl import workbook

wb = workbook.Workbook()  # 创建excel默认会创建一个sheet
sheet = wb.worksheets[0]

# 修改某一单元格
cell = sheet.cell(2,1)
cell.value = '开始'  # 直接赋予值 或者sheet["A1"] = '开始'

# 修改某些单元格
cell_list = sheet['B3':'C4']
for row in cell_list:
    for cell in row:
        cell.value = '填充'
wb.save("pdata2.xlsx") 

# 插入行列
sheet.insert_rows(idx=5,amount=10)
sheet.insert_cols(idx=3,amount=2)
wb.save("pdata2.xlsx") 

# 写入公式
sheet = wb.workbooks[0]
sheet["D3"] = "=sum(B3,C3)" # 或者 sheet["D3"] = "=B3+C3"
wb.save("pdata2.xlsx") 

设置单元格风格

openpyxl的单元格样式由6种属性决定，每一种都是一个类

• font(字体类)：字号、字体颜色、下划线等
• fill(填充类)：颜色等
• border(边框类)：设置单元格边框
• alignment(位置类)：对齐方式
• number_format(格式类)：数据格式
• protection(保护类)：写保护

① 需要导入的类

from openpyxl.styles import Font, colors, Alignment, Border, PatternFill

② 字体

微软雅黑24号，加粗斜体，字体颜色红色

font = Font(name='微软雅黑', size=24, italic=True, color=colors.RED, bold=True) sheet['A1'].font = font

③ 背景颜色

sheet['A1'].fill = PatternFill('solid',fgColor='b6eafb')

④ 对齐方式

cell的属性aligment，这里指定垂直居中和水平居中。除了center，还可以使用right、left等等参数。

设置A1中的数据垂直居中和水平居中

sheet['A1'].alignment = Alignment(horizontal='center', vertical='center')

⑤ 设置边框

side = Side(style='thin',color='000000') # 边框样式与颜色

border = Border(top=side,bottom=side,left=side,right=side)

sheet['A1'].border = border

⑥ 设置行高和列宽

设置第1行行高

sheet.row_dimensions[1].height = 20

设置A列列宽

sheet.column_dimensions['A'].width = 25

⑦ 合并和拆分单元格

以合并区域的左上角的那个单元格为基准，覆盖其他单元格为一个单元格。
相反，拆分单元格后将这个大单元格的值返回到原来的左上角位置。

合并单元格， 保留左上角数据，其余值丢弃

sheet.merge_cells('B1:G1') # 合并一行中的几个单元格

sheet.merge_cells('A1:C3') # 合并一个矩形区域中的单元格

合并后只可以往左上角写入数据，也就是区间中:左边的坐标。

拆分单元格的代码，拆分后，左上角保留原值。

sheet.unmerge_cells('A1:C3')

综合实例

• 读取.ini文件配置信息，并整理写入Excel(此处使用了MySQL的配置文件)

import os
import configparser
from openpyxl import workbook
from openpyxl.styles import Font, Alignment, Border, PatternFill,Side

# 文件路径处理
base_dir = os.path.dirname(os.path.abspath('my.ini'))
file_path = os.path.join(base_dir,'my.ini')
out_file_path = os.path.join(base_dir,'my.xlsx')

wb = workbook.Workbook()
del wb['Sheet']

# 解析ini格式文件
config = configparser.ConfigParser()
config.read(file_path,encoding='utf-8')

# 循环获取节点
for section in config.sections():
    sheet = wb.create_sheet(section)
    
    # 边框和居中
    side = Side(style='thin',color='000000')
    border = Border(top=side,bottom=side,left=side,right=side)
    align = Alignment(horizontal='center',vertical='center')
    sheet.column_dimensions['A'].width = 30
    sheet.column_dimensions['B'].width = 30
    
    # 表头
    title_dict = {"A1":"键","B1":"值"}
    for position,text in title_dict.items():
        cell = sheet[position]
        # 设置值
        cell.value = text
        # 设置居中
        cell.alignment = align
        # 设置背景色
        cell.fill = PatternFill('solid',fgColor='b6eafb')
        # 设置字体颜色
        cell.font = Font(name='微软雅黑',color="FFFFFF")
        # 设置边框
        cell.border = border
    row_index = 2
    # 读取此节点下所有键值对，并写入sheet
    for group in config.items(section):     # ('port', '3306')
        print(group)
        for col,text in enumerate(group,1):
            cell = sheet.cell(row_index,col)
            # 设置值
            cell.value = text
            # 设置居中
            cell.alignment = align
            # 设置边框
            cell.border = border
        row_index+=1

wb.save(out_file_path)

('port', '3306')
('default-character-set', 'utf8')
('port', '3306')
('basedir', 'E:/MySQL/MySQL Server 5.7/')
('datadir', 'D:/MySQL/MySQL Server 5.7/Data')
('character-set-server', 'utf8')
('default-storage-engine', 'INNODB')
('sql-mode', '"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"')
('general-log', '0')
('general_log_file', '"ZHAN.log"')
('slow_query_log_file', '"ZHAN-slow.log"')
('long_query_time', '10')
('log-error', '"ZHAN.err"')
('relay_log', '"ZHAN-relay"')
('server-id', '1')
('report_port', '3306')
('lower_case_table_names', '1')
('secure-file-priv', '"D:/MySQL/MySQL Server 5.7/Uploads"')
('myisam_max_sort_file_size', '100G')
('sync_relay_log', '10000')
('sync_relay_log_info', '10000')
('自定义参数', '自定义值')