目录
一、Python打开及读取Excel表格内容
二、Python向Excel表格中写
三、批量调整字体、样式
四、编程生成Excel内图表
一、Python打开及读取Excel表格内容
#打开以及读取Excel表格内容#列：column 行：row 格子：cell 表：sheet#Excel中从1开始计数from openpyxl import load_workbookworkbook = load_workbook(filename='aaa.xlsx')#只能打开存在表格print (workbook.sheetnames)#获取Sheel名称sheet = workbook['Sheet1']#通过Sheet名称获取表格print(sheet.dimensions)#获得表格尺寸大小sheet = workbook.active#打开活跃的/唯一的表格#cell = sheet['A1']#获取A1格子#.row行数#.column列数#.coordinate 坐标cell = sheet.cell(row=1, column=1)#可用行数和列数定位某个格子print(cell.value)#该格子的数据print(cell.row,cell.column, cell.coordinate)#获得某个格子的行数、列数、坐标cell = sheet['A1:A5']#获得一系列格子print(cell)#.iter_rows(min_row=最低行数,max_row=最高行数,min_col=最低列数,max_col=最高列数)#指定行和列的范围，按行获取#按行获取for row in sheet.iter_rows(min_row=2,max_row=3,min_col=1,max_col=2):for cell in row:print(cell)#第二行第一列 到 第三行到第二列print('-------------------------------------')#按列获取for col in sheet.iter_cols(min_row=2,max_row=3,min_col=1,max_col=2):for cell in col:print(cell)#第二行第一列 到 第三行到第二列print('-------------------------------------')#迭代整个表格的所有行 .rowsfor row in sheet.rows:for cell in row:print(cell)
二、Python向Excel表格中写
from openpyxl.utils import FORMULAE#查看openpyxl支持的公式from openpyxl import Workbook#创建新的Excel表格文件用Python列表数据插入一行 sheet.append(Python列表) 接在表格内已有数据后面插入公式 sheet['F1002'] = '=AVERAGE(F2:F1001)'插入一列 .insert_cols(idx=数子编号)插入多列 .insert_cols(idx=数子编号，amount=要插入的列数)
插入一行 .insert_rows(idx=数子编号)
插入多行 .insert_rows(idx=数子编号，amount=要插入的列数)删除一个sheet workbook.remove(sheet实例)创建新的sheet workbook.create_sheet(sheet名称)冻结窗格 sheet.freeze_panes = "G2"
#向Excel表格中写from openpyxl import load_workbookfrom openpyxl.utils import FORMULAE#查看openpyxl支持的公式from openpyxl import Workbook#创建新的Excel表格文件workbook = load_workbook(filename='aaa.xlsx')sheet = workbook.active#一、#向某个格子写入内容并保存sheet['A1'] = '你好啊'workbook.save(filename='aaa.xlsx')#二、#用Python列表数据插入一行#sheet.append(Python列表) 接在表格内已有数据后面data = [['a', 1],['b', 2],['c', 3],['d', 4],]for row in data:sheet.append(row)workbook.save(filename='aaa.xlsx')#三、#插入公式sheet['F1002'] = '=AVERAGE(F2:F1001)'workbook.save(filename='阿里云天池电商婴儿用户数据.xlsx')#四、#查看openpyxl支持的公式print(FORMULAE)#五、#插入一列 .insert_cols(idx=数子编号)#插入多列 .insert_cols(idx=数子编号，amount=要插入的列数)#插入一行 .insert_rows(idx=数子编号)#插入多行 .insert_rows(idx=数子编号，amount=要插入的列数)sheet.insert_cols(idx=2)workbook.save(filename='阿里云天池电商婴儿用户数据.xlsx')#六、#删除列 .delete_cols(idx=数子编号，amount=要删除的列数)#删除行 .delete_rows(idx=数子编号，amount=要删除的列数)sheet.delete_cols(idx=2, amount=2)workbook.save(filename='阿里云天池电商婴儿用户数据.xlsx')#七、#移动格子 .move_range("C2:D4",row=2,col=-2)向下移2行，向左移2行#正数为向下或向右，负数为向左或向上#八、#创建新的sheet workbook.create_sheet(sheet名称)print(workbook.sheetnames)workbook.create_sheet('表格2')print(workbook.sheetnames)workbook.save(filename='aaa.xlsx')#九、#删除一个sheet workbook.remove(sheet实例)print(workbook.sheetnames)sheet = workbook['表格21']workbook.remove(sheet)print(workbook.sheetnames)workbook.save(filename='aaa.xlsx')#十、#复制一个sheet workbook.copy_worksheet(sheet实例)print(workbook.sheetnames)workbook.create_sheet('表格1')sheet = workbook['表格1']workbook.copy_worksheet(sheet)print(workbook.sheetnames)workbook.save(filename='aaa.xlsx')#十一、#x修改表格名称 sheet.title=''#十二、#创建新的Excel表格文件workbook = Workbook()sheet = workbook.activesheet.title = '表格1'workbook.save(filename='这是一个新表格.xlsx')#十三、#冻结窗格 sheet.freeze_panes = "G2"sheet.freeze_panes = "B2"workbook.save(filename='aaa.xlsx')#十四、#添加筛选 sheet.auto_filter.ref = sheet.dimensionssheet = workbook['(sample)sam_tianchi_mum_baby_tr']sheet.auto_filter.ref = sheet.dimensionsworkbook.save(filename='aaa.xlsx')
三、批量调整字体、样式
from openpyxl.styles import Alignment #设置对其样式from openpyxl.styles import Side,Border #设置边框样式
from openpyxl.styles import PatternFill, GradientFill #设置填充样式Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)获取表格中字体的样式 cell.font.属性设置对其样式 Alignment(horizontal=水平对其模式,vertical=垂直对其模式,text_rotation=旋转角度,wrap_text=是否自动换行)设置边框样式 Side(style=边线样式,color=边线颜色)设置填充样式 PatternFill(fill_type=填充样式, fgColor=填充颜色)设置行高和列宽 row_dimensions[行编号].height=行高合并单元格
.merge_cells(待合并格子编号)
.merge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)
#批量调整字体、样式from openpyxl.styles import Fontfrom openpyxl import load_workbookfrom openpyxl.styles import Alignment#设置对其样式from openpyxl.styles import Side,Border#设置边框样式from openpyxl.styles import PatternFill, GradientFill#设置填充样式workbook = load_workbook(filename='aaa.xlsx')sheet = workbook.activecell = sheet['A1']#一、#Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)font = Font(name='思源黑体 Regular',size=12,bold=True, italic=True,color='FF0000')cell.font = fontworkbook.save(filename='aaa.xlsx')#二、#获取表格中字体的样式 cell.font.属性font = cell.fontprint(font.name, font.size, font.bold, font.italic)#三、#设置对其样式 Alignment(horizontal=水平对其模式,vertical=垂直对其模式,text_rotation=旋转角度,wrap_text=是否自动换行)alignment = Alignment(horizontal='center',vertical='center',text_rotation=45)cell.alignment = alignmentworkbook.save(filename='aaa.xlsx')#四、#设置边框样式#Side(style=边线样式,color=边线颜色)#Border(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)side = Side(style='thin',color='FF000000')border = Border(left=side,right=side,top=side,bottom=side)cell.border = borderworkbook.save(filename='aaa.xlsx')#五、#设置填充样式#PatternFill(fill_type=填充样式, fgColor=填充颜色)#GradientFill(stop=(渐变颜色1,渐变颜色2，.....))cell_a3 = sheet['A3']patternFill = PatternFill(fill_type='solid', fgColor='99ccff')cell_a3.fill = patternFillcell_a4 = sheet['A4']gradient_fill = GradientFill(stop=('FFFFFF','99ccff','000000'))cell_a4.fill = gradient_fillworkbook.save(filename='aaa.xlsx')#六、#设置行高和列宽#row_dimensions[行编号].height=行高#column_dimensions[列编号].width=列宽sheet.row_dimensions[1].height = 50sheet.column_dimensions['B'].width = 20workbook.save(filename='aaa.xlsx')#七、#合并单元格#.merge_cells(待合并格子编号)#.merge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)sheet.merge_cells('C1:D2')sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=4)workbook.save(filename='aaa.xlsx')
四、编程生成Excel内图表
from openpyxl.drawing.image import Image #openpyxl 插入图片from openpyxl.chart import BarChart, Reference#插入柱状图
from openpyxl.chart import LineChart, Reference#插入条形图openpyxl 插入图片 openpyxl.drawing.image sheet.add_image()插入柱状图 BarChart() Reference()
#编程生成Excel内图表from openpyxl import load_workbookfrom openpyxl.drawing.image import Image #openpyxl 插入图片from openpyxl.chart import BarChart, Reference#插入柱状图 from openpyxl.chart import LineChart, Reference#插入条形图 workbook = load_workbook(filename="阿里云天池电商婴儿用户数据.xlsx")sheet = workbook.active#一、#openpyxl 插入图片 openpyxl.drawing.image sheet.add_image()logo = Image("每颗豆.png")logo.height = 100logo.width = 100sheet.add_image(logo, "C1")workbook.save(filename="aaa.xlsx")#二、#插入柱状图 BarChart() Reference()chart = BarChart()data = Reference(worksheet=sheet,min_row=2,max_row=1001,min_col=5,max_col=6)#设置数据引用范围categories = Reference(sheet, min_col=10, min_row=10, max_row=10)#设定X轴项目名称chart.add_data(data, titles_from_data=True)#给柱状图添加数据 数据源中有标题chart.set_categories(categories)sheet.add_chart(chart, "H2")workbook.save(filename="阿里云天池电商婴儿用户数据.xlsx")#三、#插入条形图 BarChart() Reference()chart = LineChart()data = Reference(worksheet=sheet,min_row=20,max_row=21,min_col=1,max_col=13)#设置数据引用范围categories = Reference(sheet, min_col=19, min_row=2, max_row=13)#设定X轴项目名称chart.add_data(data, from_rows=True, titles_from_data=True)#给条形图添加数据 数据源中有标题chart.set_categories(categories)sheet.add_chart(chart, "E2")workbook.save(filename="阿里云天池电商婴儿用户数据.xlsx")
