python workbook写单元格合并 python合并单元格居中

10.设置单元格风格

（1）需要导入的类

from openpyxl.styles import Font, colors, Alignment

（2）字体

• 下面的代码指定了等线24号，加粗斜体，字体颜色红色。直接使用cell的font属性，将Font对象赋值给它。

bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)

sheet['A1'].font = bold_itatic_24_font

（3）对齐方式

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

# 设置B1中的数据垂直居中和水平居中
sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')

（4）设置行高和列宽

# 第2行行高
sheet.row_dimensions[2].height = 40
# C列列宽
sheet.column_dimensions['C'].width = 30

（5）合并和拆分单元格

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

# 合并单元格， 往左上角写入数据即可
sheet.merge_cells('B1:G1') # 合并一行中的几个单元格
sheet.merge_cells('A1:C3') # 合并一个矩形区域中的单元格

• 合并后只可以往左上角写入数据，也就是区间中:左边的坐标。
• 如果这些要合并的单元格都有数据，只会保留左上角的数据，其他则丢弃。换句话说若合并前不是在左上角写入数据，合并后单元格中不会有数据。
• 以下是拆分单元格的代码。拆分后，值回到A1位置

扫码可以免费领取Python自动化办公教程源码笔

11.示例代码

import datetime
 from random import choice
 from time import time
 from openpyxl import load_workbook
 from openpyxl.utils import get_column_letter

 # 设置文件 mingc
 addr = "openpyxl.xlsx"
 # 打开文件
 wb = load_workbook(addr)
 # 创建一张新表
 ws = wb.create_sheet()
 # 第一行输入
 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])

 # 获取最大行
 row_max = ws.max_row
 # 获取最大列
 con_max = ws.max_column
 # 把上面写入内容打印在控制台
 for j in ws.rows:    # we.rows 获取每一行数据
     for n in j:
         print(n.value, end="\t")   # n.value 获取单元格的值
     print()
 # 保存，save（必须要写文件名（绝对地址）默认 py 同级目录下，只支持 xlsx 格式）
 wb.save(addr)

5.2 openpyxl生成2D图表

示例代码：

from openpyxl import Workbook
 from openpyxl.chart import BarChart, Series, Reference

 wb = Workbook(write_only=True)
 ws = wb.create_sheet()

 rows = [
     ('Number', 'Batch 1', 'Batch 2'),
     (2, 10, 30),
     (3, 40, 60),
     (4, 50, 70),
     (5, 20, 10),
     (6, 10, 40),
     (7, 50, 30),
 ]

 for row in rows:
     ws.append(row)

 chart1 = BarChart()
 chart1.type = "col"
 chart1.style = 10
 chart1.title = "Bar Chart"
 chart1.y_axis.title = 'Test number'
 chart1.x_axis.title = 'Sample length (mm)'

 data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
 cats = Reference(ws, min_col=1, min_row=2, max_row=7)
 chart1.add_data(data, titles_from_data=True)
 chart1.set_categories(cats)
 chart1.shape = 4
 ws.add_chart(chart1, "A10")

 from copy import deepcopy

 chart2 = deepcopy(chart1)
 chart2.style = 11
 chart2.type = "bar"
 chart2.title = "Horizontal Bar Chart"
 ws.add_chart(chart2, "G10")

 chart3 = deepcopy(chart1)
 chart3.type = "col"
 chart3.style = 12
 chart3.grouping = "stacked"
 chart3.overlap = 100
 chart3.title = 'Stacked Chart'
 ws.add_chart(chart3, "A27")

 chart4 = deepcopy(chart1)
 chart4.type = "bar"
 chart4.style = 13
 chart4.grouping = "percentStacked"
 chart4.overlap = 100
 chart4.title = 'Percent Stacked Chart'
 ws.add_chart(chart4, "G27")

 wb.save("bar.xlsx")

效果如下：

5.3 openpyxl生成3D图表

示例代码：

from openpyxl import Workbook
 from openpyxl.chart import (
     Reference,
     Series,
     BarChart3D,
 )

 wb = Workbook()
 ws = wb.active

 rows = [
     (None, 2013, 2014),
     ("Apples", 5, 4),
     ("Oranges", 6, 2),
     ("Pears", 8, 3)
 ]

 for row in rows:
     ws.append(row)

 data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
 titles = Reference(ws, min_col=1, min_row=2, max_row=4)
 chart = BarChart3D()
 chart.title = "3D Bar Chart"
 chart.add_data(data=data, titles_from_data=True)
 chart.set_categories(titles)

 ws.add_chart(chart, "E5")
 wb.save("bar3d.xlsx")

效果如下：

5.4 实战训练

1.openpyxl 新建Excel

程序示例：

# 3.5.2 openpyxl 新建Excel
 def fun3_5_2():
     wb = Workbook()

     # 注意：该函数调用工作表的索引(_active_sheet_index)，默认是0。
     # 除非你修改了这个值，否则你使用该函数一直是在对第一张工作表进行操作。
     ws = wb.active

     # 设置sheet名称
     ws.title = "New Title"

     # 设置sheet颜色
     ws.sheet_properties.tabColor = "1072BA"

     # 保存表格
     wb.save('保存一个新的excel.xlsx')

执行效果：

并对sheet设置了标题和背景颜色：

2.openpyxl 打开已存在Excel

程序示例：

# 3.5.3 openpyxl 打开已存在Excel
 def fun3_5_3():
     wb = load_workbook("./3_5 openpyxl 修改操作练习.xlsx")

     # 注意：该函数调用工作表的索引(_active_sheet_index)，默认是0。
     # 除非你修改了这个值，否则你使用该函数一直是在对第一张工作表进行操作。
     ws = wb.active

     # 保存表格
     wb.save('copy.xlsx')

效果如下：

3.openpyxl 读写Excel

程序示例：

# 3.5.4 openpyxl 读写Excel
 def fun3_5_4():
     wb = load_workbook("./3_5 openpyxl 修改操作练习.xlsx")

     # 注意：该函数调用工作表的索引(_active_sheet_index)，默认是0。
     # 除非你修改了这个值，否则你使用该函数一直是在对第一张工作表进行操作。
     ws = wb.active

     # 读取单元格信息
     cellB2_value = ws['B2'].value
     print("单元格B2内容为：",cellB2_value)

     # 写入单元格
     ws['A1'].value = "OPENPYXL"

     # 保存表格
     wb.save('copy.xlsx')

执行结果：

6. Python xlswriter 写入 操作Excel

• XlsxWriter是一个用来写Excel2007和xlsx文件格式的python模块。它可以用来写文本、数字、公式并支持单元格格式化、图片、图表、文档配置、自动过滤等特性
• 优点：功能更多、文档高保真、扩展格式类型、更快并可配置 缺点：不能用来读取和修改excel文件

官方文档：https://xlsxwriter.readthedocs.io/

6.1 xlswriter基本操作

1.安装 xlswriter 模块

pip install XlsxWriter

由于我已经安装过了，所以提示已经安装：

2.创建excel文件

# 创建文件
workbook = xlsxwriter.Workbook("new_excel.xlsx") 

3.创建sheet

# 创建sheet
worksheet = workbook.add_worksheet("first_sheet") 

4.写入数据

（1）写入文本

# 法一：
worksheet.write('A1', 'write something')
# 法二：
worksheet.write(1, 0, 'hello world')

（2）写入数字

# 写入数字
worksheet.write(0, 1, 32)
worksheet.write(1, 1, 32.3)

（3）写入函数

worksheet.write(2, 1, '=sum(B1:B2)')

（4）写入图片

# 插入图片
worksheet.insert_image(0, 5, 'test.png')
worksheet.insert_image(0, 5, 'test.png', {'url': 'http://httpbin.org/'})

（5）写入日期

# 写入日期
d = workbook.add_format({'num_format': 'yyyy-mm-dd'})
worksheet.write(0, 2, datetime.datetime.strptime('2017-09-13', '%Y-%m-%d'), d)

（6）设置行、列属性

# 设置行属性，行高设置为40
worksheet.set_row(0, 40)

# 设置列属性，把A到B列宽设置为20
worksheet.set_column('A:B', 20)

5.自定义格式

常用格式：

• 字体颜色：color
• 字体加粗：bold
• 字体大小：font_site
• 日期格式：num_format
• 超链接：url
• 下划线设置：underline
• 单元格颜色：bg_color
• 边框：border
• 对齐方式：align

# 自定义格式
f = workbook.add_format({'border': 1, 'font_size': 13, 'bold': True, 'align': 'center','bg_color': 'cccccc'})
worksheet.write('A3', "python excel", f)
worksheet.set_row(0, 40, f)
worksheet.set_column('A:E', 20, f)

6.批量往单元格写入数据

# 批量往单元格写入数据
worksheet.write_column('A15', [1, 2, 3, 4, 5])  # 列写入，从A15开始
worksheet.write_row('A12', [6, 7, 8, 9])        # 行写入，从A12开始

7.合并单元格写入

# 合并单元格写入
worksheet.merge_range(7,5, 11, 8, 'merge_range')

8.关闭文件

workbook.close()

6.3 xlswriter 生成折线图

示例代码：

# -*- coding:utf-8 -*-

import xlsxwriter

# 创建一个excel
workbook = xlsxwriter.Workbook("chart_line.xlsx")
# 创建一个sheet
worksheet = workbook.add_worksheet()
# worksheet = workbook.add_worksheet("bug_analysis")

# 自定义样式，加粗
bold = workbook.add_format({'bold': 1})

# --------1、准备数据并写入excel---------------
# 向excel中写入数据，建立图标时要用到
headings = ['Number', 'testA', 'testB']
data = [
    ['2017-9-1', '2017-9-2', '2017-9-3', '2017-9-4', '2017-9-5', '2017-9-6'],
    [10, 40, 50, 20, 10, 50],
    [30, 60, 70, 50, 40, 30],
]

# 写入表头
worksheet.write_row('A1', headings, bold)

# 写入数据
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])

# --------2、生成图表并插入到excel---------------
# 创建一个柱状图(line chart)
chart_col = workbook.add_chart({'type': 'line'})

# 配置第一个系列数据
chart_col.add_series({
    # 这里的sheet1是默认的值，因为我们在新建sheet时没有指定sheet名
    # 如果我们新建sheet时设置了sheet名，这里就要设置成相应的值
    'name': '=Sheet1!$B$1',
    'categories': '=Sheet1!$A$2:$A$7',
    'values':   '=Sheet1!$B$2:$B$7',
    'line': {'color': 'red'},
})

# 配置第二个系列数据
chart_col.add_series({
    'name': '=Sheet1!$C$1',
    'categories':  '=Sheet1!$A$2:$A$7',
    'values':   '=Sheet1!$C$2:$C$7',
    'line': {'color': 'yellow'},
})

# 配置第二个系列数据(用了另一种语法)
# chart_col.add_series({
#     'name': ['Sheet1', 0, 2],
#     'categories': ['Sheet1', 1, 0, 6, 0],
#     'values': ['Sheet1', 1, 2, 6, 2],
#     'line': {'color': 'yellow'},
# })

# 设置图表的title 和 x，y轴信息
chart_col.set_title({'name': 'The xxx site Bug Analysis'})
chart_col.set_x_axis({'name': 'Test number'})
chart_col.set_y_axis({'name':  'Sample length (mm)'})

# 设置图表的风格
chart_col.set_style(1)

# 把图表插入到worksheet并设置偏移
worksheet.insert_chart('A10', chart_col, {'x_offset': 25, 'y_offset': 10})

workbook.close()

效果如下：

6.4 xlswriter 生成柱状图

示例代码：

# -*- coding:utf-8 -*-

 import xlsxwriter

 # 创建一个excel
 workbook = xlsxwriter.Workbook("chart_column.xlsx")
 # 创建一个sheet
 worksheet = workbook.add_worksheet()
 # worksheet = workbook.add_worksheet("bug_analysis")

 # 自定义样式，加粗
 bold = workbook.add_format({'bold': 1})

 # --------1、准备数据并写入excel---------------
 # 向excel中写入数据，建立图标时要用到
 headings = ['Number', 'testA', 'testB']
 data = [
     ['2017-9-1', '2017-9-2', '2017-9-3', '2017-9-4', '2017-9-5', '2017-9-6'],
     [10, 40, 50, 20, 10, 50],
     [30, 60, 70, 50, 40, 30],
 ]

 # 写入表头
 worksheet.write_row('A1', headings, bold)

 # 写入数据
 worksheet.write_column('A2', data[0])
 worksheet.write_column('B2', data[1])
 worksheet.write_column('C2', data[2])

 # --------2、生成图表并插入到excel---------------
 # 创建一个柱状图(column chart)
 chart_col = workbook.add_chart({'type': 'column'})

 # 配置第一个系列数据
 chart_col.add_series({
     # 这里的sheet1是默认的值，因为我们在新建sheet时没有指定sheet名
     # 如果我们新建sheet时设置了sheet名，这里就要设置成相应的值
     'name': '=Sheet1!$B$1',
     'categories': '=Sheet1!$A$2:$A$7',
     'values':   '=Sheet1!$B$2:$B$7',
     'line': {'color': 'red'},
 })

 # 配置第二个系列数据(用了另一种语法)
 chart_col.add_series({
     'name': '=Sheet1!$C$1',
     'categories':  '=Sheet1!$A$2:$A$7',
     'values':   '=Sheet1!$C$2:$C$7',
     'line': {'color': 'yellow'},
 })

 # 配置第二个系列数据(用了另一种语法)
 # chart_col.add_series({
 #     'name': ['Sheet1', 0, 2],
 #     'categories': ['Sheet1', 1, 0, 6, 0],
 #     'values': ['Sheet1', 1, 2, 6, 2],
 #     'line': {'color': 'yellow'},
 # })

 # 设置图表的title 和 x，y轴信息
 chart_col.set_title({'name': 'The xxx site Bug Analysis'})
 chart_col.set_x_axis({'name': 'Test number'})
 chart_col.set_y_axis({'name':  'Sample length (mm)'})

 # 设置图表的风格
 chart_col.set_style(1)

 # 把图表插入到worksheet以及偏移
 worksheet.insert_chart('A10', chart_col, {'x_offset': 25, 'y_offset': 10})

 workbook.close()

效果如下：

6.5 xlswriter 生成饼图

示例代码：
# -*- coding:utf-8 -*-

 import xlsxwriter

 # 创建一个excel
 workbook = xlsxwriter.Workbook("chart_pie.xlsx")
 # 创建一个sheet
 worksheet = workbook.add_worksheet()

 # 自定义样式，加粗
 bold = workbook.add_format({'bold': 1})

 # --------1、准备数据并写入excel---------------
 # 向excel中写入数据，建立图标时要用到
 data = [
     ['closed', 'active', 'reopen', 'NT'],
     [1012, 109, 123, 131],
 ]

 # 写入数据
 worksheet.write_row('A1', data[0], bold)
 worksheet.write_row('A2', data[1])

 # --------2、生成图表并插入到excel---------------
 # 创建一个柱状图(pie chart)
 chart_col = workbook.add_chart({'type': 'pie'})

 # 配置第一个系列数据
 chart_col.add_series({
     'name': 'Bug Analysis',
     'categories': '=Sheet1!$A$1:$D$1',
     'values': '=Sheet1!$A$2:$D$2',
     'points': [
         {'fill': {'color': '#00CD00'}},
         {'fill': {'color': 'red'}},
         {'fill': {'color': 'yellow'}},
         {'fill': {'color': 'gray'}},
     ],

 })

 # 设置图表的title 和 x，y轴信息
 chart_col.set_title({'name': 'Bug Analysis'})

 # 设置图表的风格
 chart_col.set_style(10)

 # 把图表插入到worksheet以及偏移
 worksheet.insert_chart('B10', chart_col, {'x_offset': 25, 'y_offset': 10})
 workbook.close()效果如下：

6.6 实战训练

1.xlswriter新建并写入Excel

程序示例：

# 3.6.2 xlswriter新建并写入Excel
 def fun3_6_2():
     # 创建Exce并添加sheet
     workbook = xlsxwriter.Workbook('demo.xlsx')
     worksheet = workbook.add_worksheet()

     # 设置列宽
     worksheet.set_column('A:A', 20)

     # 设置格式
     bold = workbook.add_format({'bold': True})

     # 添加文字内容
     worksheet.write('A1', 'Hello')

     # 按格式添加内容
     worksheet.write('A2', 'World', bold)

     # 写一些数字
     worksheet.write(2, 0, 123)
     worksheet.write(3, 0, 123.456)

     # 添加图片
     worksheet.insert_image('B5', 'demo.png')

     workbook.close()

效果如下：

7. Python win32com 读取 写入 修改 操作Excel

python可以使用一个第三方库叫做win32com达到操作com的目的，win32com功能强大，可以操作word、调用宏等等等。

7.1 pip安装win32com

pip install pypiwin32

由于我已经安装过了，所以提示已经安装：

7.2 Python使用win32com读写Excel

程序示例：

import win32com
from win32com.client import Dispatch, constants
import os

# 获取当前脚本路径
 def getScriptPath():
     nowpath = os.path.split(os.path.realpath(__file__))[0]
     print(nowpath)
     return nowpath

 # 3.7.2 Python使用win32com读写Excel
 def fun3_7_2():
     app = win32com.client.Dispatch('Excel.Application')

     # 后台运行，不显示，不警告
     app.Visible = 0
     app.DisplayAlerts = 0

     # 创建新的Excel
     # WorkBook = app.Workbooks.Add()
     # 新建sheet
     # sheet = WorkBook.Worksheets.Add()

     # 打开已存在表格，注意这里要用绝对路径
     WorkBook = app.Workbooks.Open(getScriptPath() + "\\3_7 win32com 修改操作练习.xlsx")
     sheet = WorkBook.Worksheets('Sheet1')

     # 获取单元格信息 第n行n列，不用-1
     cell01_value = sheet.Cells(1,2).Value
     print("cell01的内容为：",cell01_value)

     # 写入表格信息
     sheet.Cells(2, 1).Value = "win32com"

     # 保存表格
     #WorkBook.Save()

     # 另存为实现拷贝
     WorkBook.SaveAs(getScriptPath() + "\\new.xlsx")

     # 关闭表格
     WorkBook.Close()
     app.Quit()


 if __name__ == '__main__':
     fun3_7_2()

效果如下：

内容为：

8. Python pandas 读取 写入 操作Excel

简介：

pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。Pandas 纳入了大量库和一些标准的数据模型，提供了高效地操作大型数据集所需的工具。pandas提供了大量能使我们快速便捷地处理数据的函数和方法。你很快就会发现，它是使Python成为强大而高效的数据分析环境的重要因素之一。

官方网站：https://pandas.pydata.org/

官方文档：https://pandas.pydata.org/pandas-docs/stable/

8.1 pip安装pandas

pip install pandas

8.2 pandas 读写 Excel

表格内容如下：

程序示例：

import pandas as pd
 from pandas import DataFrame

 # 3.8.2 pandas读写Excel
 def fun3_8_2():
     data = pd.read_excel('3_8 pandas 修改操作练习.xlsx', sheet_name='Sheet1')
     print(data)

     # 增加行数据，在第5行新增
     data.loc[4] = ['4', 'john', 'pandas']

     # 增加列数据，给定默认值None
     data['new_col'] = None

     # 保存数据
     DataFrame(data).to_excel('new.xlsx', sheet_name='Sheet1', index=False, header=True)


 if __name__ == '__main__':
     fun3_8_2()

效果如下：

生成的excel如下：

pandas功能非常强大，这里只是做了又给很简单的示例，还有很多其它操作，可参考官方文档或快速入门进行学习。