数据分析课程笔记

大家好呀，今天是数据分析课程的第 6 课，用 Python 操作 excel 和 word。

在操作 excel 部分，我们会学习第三方模块 openpyxl 的基本使用，了解工作表的创建，掌握 excel 文件读取和表数据，了解绘制图形、添加图片、修改单元格样式等基本操作。

在操作 word 部分，我们会学习如何批量生成工作证明。

一、Python 操作 Excel

1、OpenPyXL 简介

openpyxl 是一个用来读/写 Excel 2010 xlsx/xlsm/xltx/xltm 类型文件的 Python 库。它是一个比较综合的工具，能够同时读取和修改 Excel 文档。其他很多与 Excel 相关的项目基本只支持读或者写 Excel 一种功能。

通过以下方式安装这个第三方库（用豆瓣的镜像源）：

pip install -U openpyxl -i https://pypi.douban.com/simple

2、创建工作薄和表

openpyxl 中的 Workbook 方法可以根据路径创建一个新的 Excel文件。

（1）导入模块的两种方法：

# 模块的导入import openpyxlfrom openpyxl import Workbook

（2）根据路径创建新 Excel 文件：

# 1. 创建空白工作薄并保存wb = openpyxl.Workbook()# 保存文件wb.save('./datafile/work1.xlsx')

注意：保存的路径下如果含有同名的文件会被覆盖。

（3）创建工作表并保存：

wb = Workbook()# 创建工作表时直接指定标题，0表示放在第一个位置ws = wb.create_sheet('ws1',0)# 不设置位置会在追加一个表ws2 = wb.create_sheet('ws2')# -1表示倒数第二个ws3 = wb.create_sheet('ws3',-1)# 保存文件wb.save('./datafile/work2.xlsx')

注意：无论是创建新的文件还是表，都需要保存。

3、读取文件和表内容

可以根据 openpyxl 的 load_workbook() 方法读取本地存在的文件。

（1）读取文件

from openpyxl import load_workbookwb = load_workbook('./work2.xlsx')

如果路径不存在，会报错：

image.png

文件对象常用属性和方法：

# 获取所有表的名字print(wb.sheetnames)# 获取默认工作表default = wb.active# 设置工作表的标题default.title = '默认' print(wb.sheetnames)# 根据表名获取表sheet = wb['Sheet'] sheet.title = '表一'print(wb.sheetnames)

注意：在设置属性值后要记得保存，不然Excel文件内并不会更改。

（2）读取表中数据：

wb = load_workbook('./datafile/读写.xlsx')# 获取表sheet = wb['读']# 获取表数据的行列数print('行数：',sheet.max_row) print('列数：',sheet.max_column)# 获取一列one_column = sheet['A'] print('一列：',one_column)# 获取一行one_row = sheet['1'] print('一行：',one_row)# 获取一个单元格one_cell = sheet['A1'] print('一个单元格：',one_cell)print('A1的值:',one_cell.value)

（3）向表中写入数据：

# 写入数据wb = load_workbook('./datafile/读写.xlsx')# 获取表sheet = wb['写']# 一个单元格写入内容sheet['A1'].value = 'A1'# 写入一列for i in range(1,11):     sheet['A{}'.format(i)].value = i# 写入一行for j in range(1,11):     sheet.cell(row=11,column=j,value=j)wb.save('./datafile/读写.xlsx')

4、OpenPyXL案例

用 openpyxl 模块来绘制图形、添加图片、修改单元格样式。

（1）添加列数据

现在，我们有一个销售表数据：

image.png

在整个数据中，我们可以根据单价和数量列计算出销售额列：

# 1. 了解数据from openpyxl import load_workbookwb = load_workbook('./datafile/案例.xlsx') sheet = wb['订单']# 获取数据的行列print(sheet.max_row) print(sheet.max_column)

获取单价和数量这两个列的值，并添加销售额列：

sheet.cell(row=1,column=19,value='销售额') # 获取列数据for row_index in range(2,sheet.max_row+1): # 单价数据    one_price = sheet.cell(row=row_index,column=17).value    # 获取数据    one_num = sheet.cell(row=row_index,column=18).value    if one_price==None or one_num==None:        sheet.cell(row=row_index,column=19).value = None     else:        sheet.cell(row=row_index,column=19).value = one_price * one_numwb.save('./datafile/案例.xlsx')

结果如下：

image.png

（2）绘制图形

根据各个省的订单量绘制出柱状图，例如：

image.png

from openpyxl import Workbookfrom openpyxl import load_workbookfrom openpyxl.chart import BarChart, Referencewb = load_workbook('./datafile/案例.xlsx') sheet = wb['各省销量']# 创建柱状图对象bar_chart = BarChart()lables= Reference(sheet,min_row=2,max_row=32,min_col=1,max_col=1) data = Reference(sheet,min_row=2,max_row=32,min_col=2,max_col=2)bar_chart.add_data(data) bar_chart.set_categories(lables) # 设置图例sheet.add_chart(bar_chart,'D10') wb.save('./datafile/案例.xlsx')

（3）添加图片

我们也可将专业的绘图工具绘制的图片，添加到指定的位置：

from openpyxl.drawing.image import Image wb = load_workbook('./datafile/案例.xlsx') sheet = wb['各省销量']img = Image('./datafile/image.png')sheet.add_image(img,'D27') wb.save('./datafile/案例.xlsx')

（4）修改单元格样式

# 修改单元格样式from openpyxl.styles import Alignment,Border,Side,Font from openpyxl.styles.fills import GradientFillwb = load_workbook('./datafile/案例.xlsx') sheet = wb['各省销量']# 设置单元格字体sheet['A1'].font = Font( name='黑体', # 设置单元格字体 size=36, #设置字体字号 bold=True, # 加粗 color='FFaa8844' # 单元格文本颜色)# 填充渐变颜色（起始颜色，结束颜色),其实颜色和结束颜色一致为固定颜色sheet['B1'].fill = GradientFill(stop=('00ffff','00ffff'))wb.save('./datafile/案例.xlsx')

二、Python 操作 word

0、关于 py-docx

py-docx 是用来处理 .docx 文件 （即 word 文档）的 python 第三方库。

在 docx 库中，word 文档内容有三种结构：

• Document 对象表示整个文档；
• Paragrapha 对象标识段落（在 word 文档中每一次回车产生一个新段落）；
• Run 对象标识相同样式的文本。

Document 对象包含一个 Paragrapha 对象的列表，Paragraph 对象包含一个 Run 对象的列表。

此外，还有 table 对象，它又包含了 row 和 column 对象，分别代表行和列。

大家可以通过 py-docx 的官方文档了解一下这个库的用法，也可以直接通过下面的小项目直观感受一下 docx 的基本操作。

1、项目需求：批量生成工作证明

基本思路：从 excel 中获取数据，再将对应的数据填入到 word 模板中使用英文字符串占位的位置即可。word模板如下:

image.png

需要填写的内容如下:

image.png

2、引入所需文件

# 一.引入所需文件import pandas as pd #pandas库,用来读取excel表格from docx import Document   # Document用来处理word文件

3、定义替换函数

替换文本, 获取 word 中的自然段, 在自然段中获取要替换的文本：

#二. 定义文本替换函数def replace_text(old_text, new_text): #读取所有的自然段 all_paragraphs = document.paragraphs #读取每个自然段 for paragraph in all_paragraphs: #循环读取所有的run(格式排版内容的东西统称) for run in paragraph.runs: #进行新旧文本的替换 run_text = run.text.replace(old_text, new_text) #进行新旧文本的替换 run.text = run_text

如果word中包含相应的表格, 需要到 word 中的 tables 中进行替换：

#读取所有的表格all_tables = document.tables print(all_tables)#循环所有表格for table in all_tables:     #循环表格中的每一行    for row in table.rows:        #循环读取表格中所有的单元格cells         for cell in row.cells:            #进行新旧文本的替换            cell_text = cell.text.replace(old_text, new_text)              #进行新旧文本的替换            cell.text = cell_text

4、读取 excel 内容进行替换

使用 pandas 读取 excel 文件：

#三. 读取excel内容pd = pd.read_excel('姓名.xls','Sheet1')

循环读取每一行内容, 将 excel 中的文本批量放入到 word 模板中, 并保存该 word：

#循环读取每一行内容for row in range(0,len(pd)):     #读取word模板内容    document = Document('模板.docx')    #获取excel中的列字段, 列字段与模板中所填内容的占位文字一致    my_col = pd.columns.tolist()     #获取excel中每一列内容    for col in range(0,pd.shape[1]):         #获取列字段,赋值给旧文本        old_text = my_col[col]        #获取每一个单元格内容,赋值给新内容        new_text = pd.iloc[row,col]         #进行文字替换        replace_text(str(old_text), str(new_text))     #定义文件名为当前excel第一列的内容    filename = str(pd.iloc[row,0])     #按定义的文件名进行保存    document.save('res/%s.docx'%(filename))