python 操作excel坐标_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')

('自定义参数', '自定义值')