【python 让繁琐工作自动化】第13章处理 Excel 电子表格 (2)

最新推荐文章于 2022-03-16 08:23:31 发布

今岁成蹊

最新推荐文章于 2022-03-16 08:23:31 发布

阅读量494

点赞数

分类专栏： Python学习笔记文章标签： python

本文链接：https://blog.csdn.net/HPP_CSDN/article/details/108294056

版权

Python学习笔记专栏收录该内容

19 篇文章 20 订阅

订阅专栏

Automate the Boring Stuff with Python: Practical Programming for Total Beginners (2nd Edition)
Copyright © 2020 by Al Sweigart.

13.6 设置单元格的字体样式

使用 openpyxl.styles 模块的 Font() 函数自定义单元格中的字体样式：

>>> import openpyxl
>>> from openpyxl.styles import Font
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> italic24Font = Font(size=24, italic=True) # Create a font.
>>> sheet['A1'].font = italic24Font # Apply the font to A1.
>>> sheet['A1'] = 'Hello, world!'
>>> wb.save('styles.xlsx')

字体对象

要设置 font 属性，需要将关键字实参传递给 Font()。

表13-2：Font 对象的关键字参数

关键字参数	数据类型	描述
name	String	字体名称，例如 “Calibri” 或 “Times New Roman”
size	Integer	字体的点大小
bold	Boolean	True 用于粗体
italic	Boolean	True 用于斜体字

可以调用 Font() 创建一个 Font 对象并将该 Font 对象存储在变量中。然后将该变量分配给 Cell 对象的 font 属性。

>>> import openpyxl
>>> from openpyxl.styles import Font
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']

>>> fontObj1 = Font(name='Times New Roman', bold=True)
>>> sheet['A1'].font = fontObj1
>>> sheet['A1'] = 'Bold Times New Roman'

>>> fontObj2 = Font(size=24, italic=True)
>>> sheet['B3'].font = fontObj2
>>> sheet['B3'] = '24 pt Italic'

>>> wb.save('styles.xlsx')

13.7 公式

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> sheet['A1'] = 200
>>> sheet['A2'] = 300
>>> sheet['A3'] = '=SUM(A1:A2)' # Set the formula.
>>> wb.save('writeFormula.xlsx')

13.8 调整行和列

设置行高和列宽

Worksheet 对象具有控制行高和列宽的 row_dimensions 和 column_dimensions 属性。

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> sheet['A1'] = 'Tall row'
>>> sheet['B2'] = 'Wide column'
>>> # Set the height and width:
>>> sheet.row_dimensions[1].height = 70
>>> sheet.column_dimensions['B'].width = 20
>>> wb.save('dimensions.xlsx')

合并与取消合并单元格

可以使用 merge_cells() 工作表方法将矩形区域的单元格合并为单个单元格。

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> sheet.merge_cells('A1:D3') # Merge all these cells.
>>> sheet['A1'] = 'Twelve cells merged together.'
>>> sheet.merge_cells('C5:D5') # Merge these two cells.
>>> sheet['C5'] = 'Two merged cells.'
>>> wb.save('merged.xlsx')

要取消合并单元格，调用 unmerge_cells() 工作表方法。

>>> import openpyxl
>>> wb = openpyxl.load_workbook('merged.xlsx')
>>> sheet = wb.active
>>> sheet.unmerge_cells('A1:D3') # Split these cells up.
>>> sheet.unmerge_cells('C5:D5')
>>> wb.save('merged.xlsx')

冷冻窗格

对于过大而无法一次显示的电子表格，“冻结”屏幕上的几行顶部或最左侧的列会很有帮助。例如，冻结的列标题或行标题在用户滚动电子表格时始终对用户可见。这些称为冻结窗格 (freeze panes)。
在 OpenPyXL 中，每个 Worksheet 对象都有一个 Frozen_panes 属性，可以将其设置为 Cell 对象或单元格坐标的字符串。注意，此单元格上方的所有行和左侧的所有列将被冻结，但是单元格本身的行和列将不会被冻结。

表13-3：冻结窗格示例

freeze_panes 设置	冻结的行和列
`sheet.freeze_panes = 'A2'`	第 1 行
`sheet.freeze_panes = 'B1'`	第 A 列
`sheet.freeze_panes = 'C2'`	第 1 行和第 A - B 列
`sheet.freeze_panes = 'A1'` 或 `sheet.freeze_panes = None`	没有冻结窗格

>>> import openpyxl
>>> wb = openpyxl.load_workbook('produceSales.xlsx')
>>> sheet = wb.active
>>> sheet.freeze_panes = 'A2' # Freeze the rows above A2.
>>> wb.save('freezeExample.xlsx')

13.9 图表

OpenPyXL 支持使用工作表单元格中的数据创建条形图，折线图，散点图和饼图。要制作图表，需要执行以下操作：

从单元格的矩形选区中创建 Reference 对象。
通过传入 Reference 对象来创建 Series 对象。
创建一个 Chart 对象。
将 Series 对象添加到 Chart 对象。
将 Chart 对象添加到 Worksheet 对象，可以选择指定哪个单元格应该在图表的左上角。

Reference 对象需要一些解释。可以通过调用 openpyxl.chart.Reference() 函数并传递三个参数来创建 Reference 对象：

包含图表数据的工作表对象。
两个整数的元组，表示包含图表数据的单元格的矩形选区的左上角单元格：元组中的第一个整数是行，第二个是列。注意，第一行是1，而不是0。
两个整数的元组，表示包含图表数据的单元格的矩形选区的右下角单元格：元组中的第一个整数是行，第二个是列。

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> for i in range(1, 11): # create some data in column A
...     sheet['A' + str(i)] = i
...
>>> refObj = openpyxl.chart.Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10)
>>> seriesObj = openpyxl.chart.Series(refObj, title='First series')

>>> chartObj = openpyxl.chart.BarChart()	# create a bar chart (条形图)
>>> chartObj.title = 'My Chart'
>>> chartObj.append(seriesObj)

>>> sheet.add_chart(chartObj, 'C5')
>>> wb.save('sampleChart.xlsx')

这将产生一个插入图表的电子表格。

openpyxl.chart.BarChart()：创建一个条形图。
openpyxl.charts.LineChart()：创建一个折线图。
openpyxl.chart.ScatterChart()：创建一个散点图。
openpyxl.chart.PieChart()：创建一个饼图。

13.10 实践项目

乘法表制作工具

创建一个程序 multiplicationTable.py，该程序从命令行获取数字 N，并在 Excel 电子表格中创建 N×N 乘法表。
例如，当运行 py multiplicationTable.py 6 时，应该创建一个电子表格如下：

乘法表
第 1 行和 A 列应用于标签，并且应为粗体。

#! python3
# multiplicationTable.py - creates an N×N multiplication table in an Excel spreadsheet.

import sys, openpyxl

if len(sys.argv) < 2:
    print('Usage: python multiplicationTable.py [number] - pass the number N, create NxN multiplication table')
    sys.exit()
N = int(sys.argv[1])      # first command line arg is the number N

wb = openpyxl.Workbook() # Create a blank workbook.
sheet = wb.active

boldFont = Font(bold=True) # Create a font.

# Row 1 and column A should be used for labels and should be in bold.
for i in range(2, N+2):
	sheet['A'+str(i)] = i - 1	# A 列数据
	sheet['A'+str(i)].font = boldFont # Apply the font to A1.
	sheet.cell(row=1, column=i).value = i - 1	# 第 1 行数据
	sheet.cell(row=1, column=i).font = boldFont

for r in range(2, N+2):
	for c in range(2, N+2):
		a = sheet.cell(row=1, column=c).value
		b = sheet.cell(row=r, column=1).value
		sheet.cell(row=r, column=c).value = a * b

wb.save('multiplicationTable.xlsx') # Save the workbook.

空白行插入工具

创建一个程序 blankRowInserter.py，该程序将两个整数和一个文件名字符串作为命令行参数。第一个整数为 N，第二个整数为 M。从第 N 行开始，程序应在电子表格中插入 M 个空白行。
例如，运行 python blankRowInserter.py 3 2 myProduce.xlsx 后，电子表格更改内容如下：

插入空白行
可以通过阅读电子表格的内容来编写此程序。然后，在写出新的电子表格时，使用 for 循环复制前 N 行。对于剩余的行，将 M 添加到输出电子表格中的行号。

#! python3
# blankRowInserter.py - Starting at row N, the program insert M blank rows into the spreadsheet.

import sys, openpyxl

if len(sys.argv) < 4:
    print('Usage: python blankRowInserter.py [N] [M] [filename] - Starting at row N, the program insert M blank rows into the spreadsheet.')
    sys.exit()
N = int(sys.argv[1])
M = int(sys.argv[2])
filename = sys.argv[3]

wbRead = openpyxl.load_workbook(filename)
sheetRead = wbRead.active

wbWrite = openpyxl.Workbook() # Create a blank workbook.
sheetWrite = wbWrite.active

for r in range(1,N):
	for c in range(1, sheetRead.max_column+1):
		sheetWrite.cell(row=r, column=c).value = sheetRead.cell(row=r, column=c).value

for r in range(N, sheetRead.max_row+1):
	for c in range(1, sheetRead.max_column+1):
		sheetWrite.cell(row=r+M, column=c).value = sheetRead.cell(row=r, colum=c).value

wbWrite.save('insertBlankRow.xlsx')

电子表格单元格反转工具

编写一个程序，反转电子表格中单元格的行和列。例如，第5行第3列的值将在第3行第5列。对电子表格中的所有单元格执行此操作。
可以使用嵌套的 for 循环来编写该程序，以将电子表格的数据读取到 list 数据结构列表中。
对于第 x 列和第 y 行的单元格，此数据结构具有 sheetData[x][y]。然后，在写出新的电子表格时，将sheetData[y][x] 用于 x 列和 y 行的单元格。

#! python3
# spreadsheetCellInverter.py - Invert the row and column of the cells in the spreadsheet.

import sys, openpyxl

if len(sys.argv) < 2:
	print('Usage: python spreadsheetCellInverter.py [filename] - the .xlsx filename.')
filename = sys.argv[1]

wbRead = openpyxl.load_workbook(filename)
sheetRead = wbRead.active

wbWrite = openpyxl.Workbook()
sheetWrite = wbWrite.active

for r in range(1, sheetRead.max_row+1):
	for c in range(1, sheetRead.max_column+1):
		sheetWrite.cell(row=c, column=r).value = sheetRead.cell(row=r, column=c).value
		
wbWrite.save('invertCell.xlsx')

文本文件到电子表格

编写程序，读取多个文本文件的内容，然后将这些内容插入电子表格中，每行写入一行文本。第一个文本文件的行写入A列的单元格中，第二个文本文件的行写入B列的单元格中，依此类推。
使用 readlines() File 对象方法返回字符串列表，文件中的每一行一个字符串。对于第一个文件，将第一行输出到第1列第1行，第二行应写入第1列第2行，依此类推。下一个使用 readlines() 读取的文件将被写入第2列，下一个文件将被写入第3列，依此类推。

#! python3
# textFilesToSpreadsheet.py - Read in the contents of several text files and insert those contents into a spreadsheet, with one line of text per row.

import os, openpyxl

wb = openpyxl.Workbook()
sheet = wb.active

colNum = 1
for txtFilename in os.listdir('.'):
	if not txtFilename.endswith('.txt'):
		continue	# skip non-txt files
	
	txtFile = open(txtFilename)
	rowNum = 1
	for line in txtFile.readlines()
		sheet.cell(row=rowNum, column=colNum).value = line.strip() # clear '\n' at the end of the string
		rowNum += 1
	txtFile.close()
	colNum += 1

wb.save('textFilesToSpreadsheet.xlsx')

电子表格到文本文件

编写一个程序，执行与上一个程序顺序相反的任务：该程序应打开电子表格，并将A列的单元格写入一个文本文件，将B列的单元格写入另一个文本文件，依此类推。

#! python3
# spreadsheetToTextFiles.py - Open a spreadsheet and write the cells of column A into one text file, the cells of column B into another text file, and so on.

import os, openpyxl

wb = openpyxl.load_workbook('your_spreadsheet.xlsx')
sheet = wb.active

os.makedirs('textFiles', exist_ok=True)

for c in range(1, sheet.max_column+1):
	fileName = str(c) + ".txt"
	txtFile = open(os.path.join('textFiles', fileName), 'w')
	for r in range(1, sheet.max_row+1):
		data = sheet.cell(row=r, column=c).value + '\n'
		txtFile.write(data)
	txtFile.close()