一、Python打开及读取Excel表格内容
二、Python向Excel表格中写
三、批量调整字体、样式
四、编程生成Excel内图表
一、Python打开及读取Excel表格内容 #打开以及读取Excel表格内容 #列：column 行：row 格子：cell 表：sheet #Excel中从1开始计数 from openpyxl import load_workbook workbook = 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('-------------------------------------') #迭代整个表格的所有行 .rows for 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_workbook from 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.active sheet.title = '表格1' workbook.save(filename='这是一个新表格.xlsx') #十三、 #冻结窗格 sheet.freeze_panes = "G2" sheet.freeze_panes = "B2" workbook.save(filename='aaa.xlsx') #十四、 #添加筛选 sheet.auto_filter.ref = sheet.dimensions sheet = workbook['(sample)sam_tianchi_mum_baby_tr'] sheet.auto_filter.ref = sheet.dimensions workbook.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 Font from openpyxl import load_workbook from openpyxl.styles import Alignment#设置对其样式 from openpyxl.styles import Side,Border#设置边框样式 from openpyxl.styles import PatternFill, GradientFill#设置填充样式 workbook = load_workbook(filename='aaa.xlsx') sheet = workbook.active cell = sheet['A1'] #一、 #Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色) font = Font(name='思源黑体 Regular',size=12,bold=True, italic=True,color='FF0000') cell.font = font workbook.save(filename='aaa.xlsx') #二、 #获取表格中字体的样式 cell.font.属性 font = cell.font print(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 = alignment workbook.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 = border workbook.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 = patternFill cell_a4 = sheet['A4'] gradient_fill = GradientFill(stop=('FFFFFF','99ccff','000000')) cell_a4.fill = gradient_fill workbook.save(filename='aaa.xlsx') #六、 #设置行高和列宽 #row_dimensions[行编号].height=行高 #column_dimensions[列编号].width=列宽 sheet.row_dimensions[1].height = 50 sheet.column_dimensions['B'].width = 20 workbook.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_workbook from 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 = 100 logo.width = 100 sheet.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")
