赞
踩
目录
python处理Excel的方式:openpyxl
指定虚拟环境为python3.9版本...
pip install openpyxl==3.0.7
我们先准备一个名为test.xlsx的表格。
- import openpyxl
-
- # 创建一个工作簿对象
- wb = openpyxl.load_workbook('./test.xlsx')
-
- # 获取工作簿的sheet表的名称
- sheet_list = wb.sheetnames
- print(sheet_list) # ['作家列表', '学生列表']
-
- sheet = wb['作家列表']
-
- # 获取活动表
- print(wb.active) # <Worksheet "学生列表">
-
- cell = sheet['A3']
- print(cell.value) # 余华
- print(cell.row) # 3
- print(cell.column) # 1
- print(cell.coordinate) # A3
-
- # 获取第1行第2列的值
- cell = sheet.cell(row=1, column=2).value
- print(cell) # 书籍
-
- # 进行切片操作,从而取得电子表格中一行、一列或一个矩形区域中所有Cell对象
- for cell_row in sheet['A1':'B4']:
- for cell in cell_row:
- print(cell.coordinate, cell.value)
-
- # 要访问特定行或列的单元格的值,也可以使用Worksheet对象的rows和columns属性
- for cell in list(sheet.columns)[0]: # 获取第一列的cell
- print(cell.value)
-
- # 获取工作表中行数和列数
- print(sheet.max_row) # 4
- print(sheet.max_column) # 2
- import openpyxl
-
- # 创建一个新的工作簿对象
- wb = openpyxl.Workbook()
- # 给工作簿设置名称
- sheet = wb.active
- sheet.title = '跟进记录表'
-
- # 保存工作表
- wb.save('./第一个工作簿.xlsx')
- import openpyxl
-
- # 创建一个新的工作簿对象
- wb = openpyxl.load_workbook('./第一个工作簿.xlsx')
- # 创建sheet
- wb.create_sheet(title='销售记录')
- wb.create_sheet(index=1, title='养殖技术')
-
- print(wb.sheetnames) # ['跟进记录表', '养殖技术', '销售记录']
-
- # 删除sheet页
- del wb['养殖技术']
- print(wb.sheetnames) # ['跟进记录表', '销售记录']
-
- sheet = wb['销售记录']
- sheet['A1'] = 'hello'
- sheet['B2'] = 'world'
- wb.save('./第一个工作簿.xlsx')
- from openpyxl.styles import Font
- import openpyxl
-
- wb = openpyxl.Workbook()
- sheet = wb.active
- sheet['A3'] = '字体'
- sheet['A3'].font = Font(name='楷体', color='8470FF')
- wb.save('./styles.xlsx')
Font()的参数有很多,比如:
- from openpyxl.styles import Font, PatternFill
- import openpyxl
-
- wb = openpyxl.Workbook()
- sheet = wb.active
- sheet['A3'] = '背景填充色'
- sheet['A3'].fill = PatternFill(patternType='solid', fgColor='8470FF')
- wb.save('./styles.xlsx')
- from openpyxl.styles import Side, Border
- import openpyxl
-
- wb = openpyxl.Workbook()
- sheet = wb.active
- sheet['F4'] = '效果1'
- sheet['F5'] = '效果2'
- s1 = Side(style='thin', color='8470FF')
- s2 = Side(style='double', color='ff0000')
- # 只作用上边框
- sheet['F4'].border = Border(top=s1)
- sheet['F5'].border = Border(top=s2, bottom=s1, left=s2, right=s1)
- wb.save('./styles.xlsx')
- from openpyxl.styles import Alignment
- import openpyxl
-
- wb = openpyxl.load_workbook('./cellBorder.xlsx')
- sheet = wb['Sheet1']
- # horizontal代表水平对齐 vertical代表垂直对齐
- c1 = sheet['C1'].alignment = Alignment(horizontal='right', vertical='center') # 水平靠右对齐 垂直居中对齐
- c2 = sheet['C2'].alignment = Alignment(vertical='center')
- c3 = sheet['C3'].alignment = Alignment(vertical='top')
- wb.save('./cellBorder.xlsx')
- import openpyxl
-
- wb = openpyxl.load_workbook('./筛选器.xlsx')
- sheet = wb['Sheet1']
- # 创建筛选器对象:auto_filter
- # ref:使得筛选器对象引用指定的区域
- sheet.auto_filter.ref = 'A1:D7'
- wb.save('./筛选器.xlsx')
- import openpyxl
-
- wb = openpyxl.load_workbook('./筛选器.xlsx')
- sheet = wb['Sheet1']
- # 创建筛选器对象:auto_filter
- # ref:使得筛选器对象引用指定的区域
- sheet.auto_filter.ref = 'A1:D7'
- # add_filter_column参数:参数1表示对指定区域哪一列进行设置筛选条件,参数2:筛选条件内容
- sheet.auto_filter.add_filter_column(1, ['北京', '深圳'])
- wb.save('./筛选器.xlsx')
- import openpyxl
-
- wb = openpyxl.load_workbook('./筛选器.xlsx')
- sheet = wb['Sheet1']
- # 创建筛选器对象:auto_filter
- # ref:使得筛选器对象引用指定的区域
- sheet.auto_filter.ref = 'A1:D7'
- # 参数1:排序列 参数2:升降序 True为降序 false为升序
- sheet.auto_filter.add_sort_condition(ref='D2:D7', descending=True)
- wb.save('./筛选器.xlsx')
- import openpyxl
-
- wb = openpyxl.Workbook()
- sheet = wb.active
- sheet['A1'] = 200
- sheet['A2'] = 300
- sheet['A3'] = '=SUM(A1:A2)'
-
- wb.save('./sum.xlsx')
- import openpyxl
-
- wb = openpyxl.load_workbook('./sum.xlsx')
- sheet = wb.active
- print(sheet['A3'].value) # =SUM(A1:A2)
这个结果居然是读取到了公式字符串,但我们想要的是公式计算的结果,也就是A3的结果,如何解决呢?
- import openpyxl
-
- wb = openpyxl.load_workbook('./sum.xlsx', read_only=True)
- sheet = wb.active
- # 注意:如果返回的是None,则打开Excel工作簿,将内容手动保存下即可,不方便但是没办法
- print(sheet['A3'].value)
设置行高和列宽:Worksheet对象有 row_dimensions 和 column_dimensions属性,控制行高和列宽。
- import openpyxl
-
- wb = openpyxl.Workbook()
- sheet = wb.active
- # 设置行高
- sheet.row_dimensions[2].height = 50
- # 设置列宽
- sheet.column_dimensions['A'].width = 80
-
- wb.save('./hw.xlsx')
- import openpyxl
-
- wb = openpyxl.Workbook()
- sheet = wb.active
- # 合并
- sheet.merge_cells('A1:D7')
- sheet['A1'] = 'Python'
- wb.save('./merge.xlsx')
- import openpyxl
-
- wb = openpyxl.load_workbook('./merge.xlsx')
- sheet = wb.active
- # 拆分
- sheet.unmerge_cells('A1:D7')
- wb.save('./merge.xlsx')
- import openpyxl
-
- wb = openpyxl.load_workbook('./produceSales.xlsx')
- sheet = wb.active
- # 冻结首行标题
- sheet.freeze_panes = 'A2'
- wb.save('./produceSales.xlsx')
- import openpyxl
-
- wb = openpyxl.load_workbook('./produceSales.xlsx')
- sheet = wb.active
- # 冻结首行标题
- sheet.freeze_panes = None
- wb.save('./produceSales.xlsx')
openpyxl支持利用工作表中单元格的数据,创建条形图、折线图、散点图和饼图。要创建图表,需要做下列事情:
- import openpyxl
-
- wb = openpyxl.load_workbook('./echarts.xlsx')
- sheet = wb.active
-
- # 1. 创建一个Reference对象,表示作用在图表中的数据区域
- values = openpyxl.chart.Reference(sheet, min_row=1, min_col=1, max_row=10, max_col=5)
-
- # 2. 创建图表对象
- chart = openpyxl.chart.BarChart()
-
- # 3. 往图表对象中添加数据
- chart.add_data(values)
-
- # 4. 将图表添加到指定sheet中
- sheet.add_chart(chart, 'G1')
- wb.save('./echarts.xlsx')
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。