700字范文 > python处理Excel实现自动化办公教学（数据筛选公式操作单元格拆分合并冻结窗口

python处理Excel实现自动化办公教学（数据筛选公式操作单元格拆分合并冻结窗口

时间：2020-02-20 15:22:27

python处理Excel实现自动化办公教学（含实战）【一】

python处理Excel实现自动化办公教学（含实战）【二】

python处理Excel实现自动化办公教学（数据筛选、公式操作、单元格拆分合并、冻结窗口、图表绘制等）【三】

相关码源：B站python资料.zip-数据挖掘文档类资源-CSDN下载

1.数据筛选

样例：

from openpyx1. styles import Alignmentimport openpyx1wb =openpyxl.load workbook( './data/ cellBorder .xlsx')sheet =wb[ '数据筛选']#创建筛选器对象: auto_ filter#ref:使得筛选器对象引用指定的区域sheet.auto_filter.ref = 'B1:D7'wb.save( './data/ cellBorder.xlsx' )

#设置筛选条件from openpyx1. styles import Alignmentimport openpyxlwb = openpyxl.load workbook( ' . /data/ cellBorder .xlsx')sheet=wb['数据筛选']#创建筛选器对象: auto_ filter#ref:使得筛选器对象引用指定的区域sheet.auto_filter.ref ='B1:D7 '#add_filter_column参数:参数1表示对指定区域那一列进行设置筛选条件，参数2: 筛选条件内容sheet.auto_filter.add_filter_column(0,['北京'，'深圳'])wb.save('./data/ cellBorder.xlsx')#样式需要去excel里点击保存即可

#排序from openpyxl. styles import Alignmentimport openpyx1wb = openpyxl.load workbook('. /data/ cellBorder .xlsx')sheet = wb['数据筛选']sheet.auto_filter.ref = 'B1:D7'#参数1:排序列.参数2:升降序sheet.auto_filter.add_sort_condition(ref= 'D2 :D9 '，descending=True)wb.save('./data/ cellBorder . xlsx')

2.公式

利用 openpyxl 模块，用编程的方式在单元格中添加公式，就像添加普通的值一样。例如: sheet['B9'] = '=SUM(B1:B8)'

import openpyxlwb = openpyxl.Workbook()sheet = wb.activesheet['A1'] = 200sheet['A2'] = 300sheet['A3'] = '=SUM(A1:A2)'wb.save('./data/sum.xlsx')

读取公式：

import openpyxlwb = openpyxl.load_workbook( '.[data/sum.xlsx',read only=True)sheet = wb.active#注意:如果返回的是None,则打开exce1.工作簿，将内容手动保存下即可，不方便，但是没有办法print(sheet[ 'A3' ] .value )

3.调整行列

在 Excel 中，调整行和列的大小非常容易，只要点击并拖动行的边缘，或列的头部。但如果你需要根据单元格的内容来设置行或列的大小，或者希望设置大量电子表格文件中的行列大小，编写 Python 程序来做就要快得多。设置行高和列宽 Worksheet 对象有 row_dimensions 和 column_dimensions 属性，控制行高和列宽。

import openpyxlwb = openpyxl.Workbook()sheet = wb.get_active_sheet()sheet['A1'] = 'Tall row'sheet['B2'] = 'Wide column'wb.save('./data/dimensions.xlsx')

sheet.row_dimensions#因为表里只有两行数据所以显示两行{1: <openpyxl.worksheet.dimensions.RowDimension at 0x108af1f98>,2: <openpyxl.worksheet.dimensions.RowDimension at 0x108af1dd8>}sheet.column_dimensionsDimensionHolder([('A',<openpyxl.worksheet.dimensions.ColumnDimension at 0x108af1c88>),('B',<openpyxl.worksheet.dimensions.ColumnDimension at 0x108af1978>)])#设置行高sheet.row_dimensions[2].height = 50#设置列宽sheet.column_dimensions['A'].width = 80wb.save('./data/dimensions.xlsx')

注意：
行的高度可以设置为 0 到 409 之间的整数或浮点值。这个值表示高度的点数。一点等于 1/72 英寸。默认的行高是 12.75。列宽可以设置为 0 到 255 之间的整数或浮点数。默认的列宽是 8.43 个字符。列宽为零或行高为零，将使单元格隐藏。

4. 合并和拆分单元格

利用 merge_cells()工作表方法，可以将一个矩形区域中的单元格合并为一个单元格。要拆分单元格，就调用 unmerge_cells()工作表方法。

import openpyxlwb = openpyxl.Workbook()sheet = wb.get_active_sheet()sheet.merge_cells('A1:D7') #合并sheet['A1'] = 'hello bobo'wb.save('./data/merged.xlsx')

sheet.unmerge_cells('A1:D7')sheet['A2'].value

5.冻结窗格

对于太大而不能一屏显示的电子表格，“冻结”顶部的几行或最左边的几列，是很有帮助的。在 OpenPyXL 中，每个 Worksheet 对象都有一个 freeze_panes 属性，可以设置为一个 Cell 对象或一个单元格坐标的字符串。请注意，单元格上边的所有行和左边的所有列都会冻结，但单元格所在的行和列不会冻结。要解冻所有的单元格，就将freeze_panes 设置为 None 或'A1'

#冻结import openpyxlwb = openpyxl.load_workbook('./data/produceSales.xlsx')sheet = wb.get_active_sheet()sheet.freeze_panes = 'A2'wb.save('./data/produceSales.xlsx')

#解冻结sheet.freeze_panes = Nonewb.save('./data/produceSales.xlsx')

6.绘制图表

openpyxl 支持利用工作表中单元格的数据，创建条形图、折线图、散点图和饼图。要创建图表，需要做下列事情： 1．从一个矩形区域选择的单元格，创建一个 Reference 对象。2．通过传入 Reference 对象，创建一个 Series 对象。3．创建一个 Chart 对象。4．将 Series 对象添加到 Chart 对象。5．可选地设置Chart 对象的drawing.top、drawing.left、drawing.width 和drawing.height变量用来设置图表的位置和大小。6．将 Chart 对象添加到 Worksheet 对象。Reference 对象需要一些解释。Reference 对象表示图表要引用的数据区域。是通过调用 openpyxl.charts.Reference()函数并传入 3 个参数创建的： 1．包含图表数据的 Worksheet 对象。2．两个整数的元组，代表矩形选择区域的左上角单元格，该区域包含图表数据：元组中第一个整数是行，第二个整数是列。请注意第一行是 1，不是 0。3．两个整数的元组，代表矩形选择区域的右下角单元格，该区域包含图表数据：元组中第一个整数是行，第二个整数是列。

import openpyxlwb = openpyxl.load_workbook(filename='./data/sampleChart.xlsx')sheet = wb.get_active_sheet()refObj = openpyxl.charts.Reference(sheet, (1, 2),(10,2))seriesObj = openpyxl.charts.Series(refObj, title='First series')chartObj = openpyxl.charts.BarChart()sheet.add_data(refObj ) #图标中添加数据chartObj.append(seriesObj) #图标中添加数据两种方式chartObj.drawing.top = 200 # set the positionchartObj.drawing.left = 200chartObj.drawing.width = 300 # set the sizechartObj.drawing.height = 200chartObj.title = sheet_name chartObj.x_axis.title = '日期'chartObj.y_axis.title = '营收额'sheet.add_chart(chartObj,"G1") #添加到制定sheet中wb.save('./data/sampleChart.xlsx')

我们可以调用 openpyxl.charts.BarChart()，创建一个条形图。也可以调用openpyxl.charts.LineChart()、openpyxl.charts.ScatterChart()和 openpyxl.charts.PieChart()，创建折线图、散点图和饼图。

效果：图的绘制

import openpyxlfrom openpyxl.charts import Reference,BarChart,Seriesimport osfor file_name in os.listdir('./data/Bobo公司-2030年营收报表'):if file_name != '.DS_Store': #苹果电脑会有一个隐藏文件，去掉不处理file_name = os.path.join('./data/Bobo公司-2030年营收报表',file_name)ex_file = openpyxl.load_workbook(file_name)sheet_names = ex_file.get_sheet_names() #获取所有表for sheet_name in sheet_names:sheet_file = ex_file.get_sheet_by_name(sheet_name)data=Reference(sheet_file, min_col=2,min_row =1，max_row=32，max_col=4 ) #推荐这种写法方便""" data1 = Reference(sheet_file, (2, 2),(32,2))data2 = Reference(sheet_file, (2, 3),(32,3))data3 = Reference(sheet_file, (2, 4),(32,4))seriesObj1 = Series(data1,title='手机')seriesObj2 = Series(data2,title='彩妆')seriesObj3 = Series(data3,title='电脑')"""bc = BarChart()bc.title = sheet_namebc.x_axis.title = '日期'bc.y_axis.title = '营收额'#bc.append(seriesObj1)#bc.append(seriesObj2)#bc.append(seriesObj3)bc.add_data(data, titles_from_data=True )bc.drawing.top = 200 # set the positionbc.drawing.left = 400bc.drawing.width = 600 # set the sizebc.drawing.height = 400#使用日期这一列作为x轴x_label=Reference(sheet_file, min_col=1,min_row= =2,max_row=32 )bc.set_categories(x_label )sheet_file.add_chart(bc,'E5' ) #sheet_file.add_chart(bc)ex_file.save(file_name)

7. 练习题【知识点回顾】

1.openpyxl.load_workbook()函数返回什么?
2.get_sheet_names()工作簿方法返回什么?
3.如何取得名为'Sheet1'的工作表的 Worksheet 对象?
4.如何取得工作簿的活动工作表的 Worksheet 对象?
5.如何取得单元格 C5 中的值?
6.如何将单元格 C5 中的值设置为"Hello"?
7.工作表方法 get_highest_column()和 get_highest_row()返回什么?
8.如何取得从 A1 到 F1 的所有 Cell 对象的元组?
9.如何将工作簿保存到文件名 example.xlsx?
10.如何在一个单元格中设置公式?
11.如果需要取得单元格中公式的结果，而不是公式本身，必须先做什么?
12.如何将第 5 行的高度设置为 100?
13.如何设置列 C 的宽度?
14.什么是冻结窗格?