主要是对openpyxl扩展进行扩展,使用归类等
1、 安装
pip install openpyxl
想要在文件中插入图片文件,需要安装pillow,安装文件:PIL-fork-1.1.7.win-amd64-py2.7.exe
· font(字体类):字号、字体颜色、下划线等
· fill(填充类):颜色等
· border(边框类):设置单元格边框
· alignment(位置类):对齐方式
· number_format(格式类):数据格式
· protection(保护类):写保护
2、 创建一个excel 文件,并写入不同类的内容
from openpyxl import Workbook wb = Workbook() #创建excel文件对象 ws = wb.active #获取第一个sheet,第一sheet一般为默认的活动sheet ws['A1'] = 12 #单元格写入数字和文字等信息 ws['B1'] = "你好"+"world" ws.append([1,2,3]) #一次性写入多个单元格,按行写入 import datetime import time ws['A3'] = datetime.datetime.now() #写入一个当前时间 ws['A4'] = time.strftime('%Y{y}%m{m}%d{d} %H{h}%M{f}%S{s}').format(y='年',m='月',d='日',h='时',f='分',s='秒') #写入一个自定义的时间格式 wb.save('sample.xlsx') #保存excel文件
3、 创建sheet
from openpyxl import Workbook wb = Workbook() #创建excel文件对象 ws1 = wb.create_sheet("Mysheet") #创建新的sheet ws1.title = "NewTitle" #新sheet取名 ws2 = wb.create_sheet("Mysheet",0) #创建新的sheet,并插入到第一个位置 ws2.title = '你好' ws1.sheet_properties.tabColor = '1072BA' #sheet标签颜色 print(wb.get_sheet_by_name('你好')) #获取某个sheet对象 print(wb["NewTitle"]) #获取某个sheet对象 for sheet_name in wb.sheetnames: #获取sheet名称方法一 print(sheet_name) for sheet in wb: #获取sheet名称方法二 print(sheet.title) wb["NewTitle" ]["A1"]="zeke" #对特定的sheet的单元格赋值 source = wb["NewTitle" ] target = wb.copy_worksheet(source) #复制sheet wb.save('sample.xlsx') #保存excel文件
4、 操作单元格
from openpyxl import Workbook wb = Workbook() #创建excel文件对象 ws1 = wb.create_sheet("Mysheet") #创建新的sheet ws1["A1"] = 3.1415926 ws1["B2"] = "hello" d = ws1.cell(row=4, column=2, value=10) #相当于ws1["B4"]=10 print(ws1["A1"].value) print(ws1["B2"].value) print(d.value) wb.save('sample.xlsx') #保存excel文件
5、 操作批量的单元格
无论ws.rows还是ws.iter_rows都是一个对象
from openpyxl import Workbook wb = Workbook() #创建excel文件对象 ws1 = wb.create_sheet("Mysheet") #创建新的sheet ws1["A1"] = 3.1415926 ws1["A2"] = "hello" ws1["A3"] = "你好" ws1.append([5,2,3]) print(ws1["A"]) #操作单列:(<Cell 'Mysheet'.A1>, <Cell 'Mysheet'.A2>, <Cell 'Mysheet'.A3>, <Cell 'Mysheet'.A4>) for i in ws1["A"]: print(i.value) print(ws1["A:C"]) #操作多列,获取每一个值 for column in ws1["A:C"]: for cell in column: print(cell.value) #无论ws.rows还是ws.iter_rows都是一个对象 for row in ws1.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3): #操作多列,获取每一个值 for cell in row: print(cell.value) print(ws1.rows) #获取所有行 for row in ws1.rows: print(row) print(ws1.columns) #获取所有列 for column in ws1.columns: print(column) wb.save('sample.xlsx') #保存excel文件
使用百分数
from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook('sample.xlsx') #创建excel文件对象 # wb.guess_types = True #结果会打印小数 wb.guess_types = False #结果会打印百分数 ws = wb.active ws["D1"] = "12%" print(ws["D1"].value) wb.save('sample.xlsx') #保存excel文件
获取所有的行/列对象
from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook('sample.xlsx') #创建excel文件对象 ws = wb.active rows = [] colunms = [] #获取所有的行对象: for row in ws.iter_rows(): # for row in ws. rows: rows.append(row) print(rows) #所有行 print(rows[0]) #获取第一行 print(rows[0][0]) #获取第一行第一列的单元格对象 print(rows[0][0].value) #获取第一行第一列的单元格对象的值 print(rows[len(rows)-1]) #获取最后行 print rows[-1] print(rows[len(rows)-1][len(rows[0])-1]) #获取第后一行和最后一列的单元格对象 print(rows[len(rows)-1][len(rows[0])-1].value) #获取第后一行和最后一列的单元格对象的值 #获取所有的列对象: for col in ws.iter_cols(): # for col in ws.columns: colunms.append(col) print(colunms) #所有行 print(colunms[0]) #获取第一行 print(colunms[0][0]) #获取第一行第一列的单元格对象 print(colunms[0][0].value) #获取第一行第一列的单元格对象的值 print(colunms[len(colunms)-1]) #获取最后行 print rows[-1] print(colunms[len(colunms)-1][len(colunms[0])-1]) #获取第后一行和最后一列的单元格对象 print(colunms[len(colunms)-1][len(colunms[0])-1].value) #获取第后一行和最后一列的单元格对象的值 wb.save('sample.xlsx') #保存excel文件
6、 操作已经存在的文件
from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook('sample.xlsx') wb.guess_types = True #猜测单元格格式类型 ws=wb.active wb.save("sample.xlsx")
#注意如果原文件有一些图片或者图标,保存的时候可能会导致图片丢失
7、 单元格类型
from openpyxl import Workbook from openpyxl import load_workbook import datetime wb = load_workbook('sample.xlsx') #创建excel文件对象 ws = wb.active wb.guess_types = True #如果是常规,显示general,如果是数字,显示'0.00_ ',如果是百分数显示0% ws["A1"] = datetime.datetime.now() print(ws["A1"].number_format) #单元格中数据类型为yyyy-mm-dd h:mm:ss print(ws["A1"].value) ws["A2"] = "12%" print(ws["A2"].number_format) #单元格中数据类型为百分数,显示为0% print(ws["A2"].value) ws["A3"] = 1.154 print(ws["A3"].number_format) #单元格中数据类型为常规,显示general。注:数字需要在Excel中设置数字类型,直接写入的数字是常规类型 print(ws["A3"].value) ws["A4"] = "中国" print(ws["A4"].number_format) #单元格中数据类型为常规,显示general print(ws["A4"].value) wb.save('sample.xlsx') #保存excel文件
8、 使用公式
from openpyxl import Workbook from openpyxl import load_workbook import datetime wb = load_workbook('sample.xlsx') #创建excel文件对象 ws1 = wb.active ws1["A1"] = 1 ws1["A2"] = 2 ws1["A3"] = 3 ws1["A4"] = "SUM(1,1)" ws1["A5"] = "SUM(A1,A3)" print(ws1["A4"].value) #打印的是公式内容,不是公式计算后的值,程序无法取到计算后的值 print(ws1["A5"].value) #打印的是公式内容,不是公式计算后的值,程序无法取到计算后的值 wb.save('sample.xlsx') #保存excel文件
9、 合并单元格
from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook('sample.xlsx') ws1=wb.active ws.merge_cells('A2:D2') ws.unmerge_cells('A2:D2') #合并后的单元格,脚本单独执行拆分操作会报错,需要重新执行合并操作再拆分 # 或对应具体的行列,与上面两条结果相同 ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4) ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4) wb.save("sample.xlsx") #保存文件
10、插入一个图片
需要先安装Pilow,安全文件是:PIL-fork-1.1.7.win-amd64-py2.7.exe
from openpyxl import load_workbook from openpyxl.drawing.image import Image wb = load_workbook('e:\sample.xlsx') ws1=wb.active img = Image('1.png') ws1.add_image(img, 'A1') wb.save("sample.xlsx")
11、 隐藏单元格
from openpyxl import load_workbook wb = load_workbook('sample.xlsx') ws1=wb.active ws1.column_dimensions.group('A', 'D', hidden=True) #隐藏a到d列范围内的列 #注:ws1.row_dimensions 无group方法 wb.save("sample.xlsx")
12、 画一个柱状图
from openpyxl import load_workbook from openpyxl import Workbook from openpyxl.chart import BarChart, Reference, Series wb = load_workbook('sample.xlsx') ws1=wb.active wb = Workbook() ws = wb.active for i in range(10): ws.append([i]) values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10) #获取表格中具体的数值 chart = BarChart() #创建柱状图 chart.add_data(values) #柱状图中添加数据 ws.add_chart(chart, "E15") #将柱状图添加到工作薄中,并从E15单元格中开始添加 wb.save("sample.xlsx")
13、 画一个饼图
from openpyxl import load_workbook from openpyxl import Workbook from openpyxl.chart import (PieChart , ProjectedPieChart, Reference) from openpyxl.chart.series import DataPoint data = [['Pie', 'Sold'],['Apple', 50],['Cherry', 30],['Pumpkin', 10],['Chocolate', 40]] wb = Workbook() ws = wb.active for row in data: ws.append(row) pie = PieChart() #创建饼图 labels = Reference(ws, min_col=1, min_row=2, max_row=5) #饼图label取值单元格 data = Reference(ws, min_col=2, min_row=1, max_row=5) #饼图数据取值单元格 pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "Pies sold by category" slice = DataPoint(idx=0, explosion=20) pie.series[0].data_points = [slice] ws.add_chart(pie, "D1") ws = wb.create_sheet(title="Projection") #创建另外的工作薄并画图 data = [['Page', 'Views'],['Search', 95],['Products', 4],['Offers', 0.5],['Sales', 0.5]] for row in data: ws.append(row) projected_pie = ProjectedPieChart() projected_pie.type = "pie" projected_pie.splitType = "val" # 按取值拆分 labels = Reference(ws, min_col=1, min_row=2, max_row=5) data = Reference(ws, min_col=2, min_row=1, max_row=5) projected_pie.add_data(data, titles_from_data=True) projected_pie.set_categories(labels) ws.add_chart(projected_pie, "A10") from copy import deepcopy projected_bar = deepcopy(projected_pie) projected_bar.type = "bar" projected_bar.splitType = 'pos' # 按位置拆分 ws.add_chart(projected_bar, "A27") wb.save("sample.xlsx")
14、 设定一个表格区域,并设定表格的格式
from openpyxl import load_workbook from openpyxl import Workbook from openpyxl.worksheet.table import Table, TableStyleInfo wb = Workbook() ws = wb.active data = [['Apples', 10000, 5000, 8000, 6000],['Pears', 2000, 3000, 4000, 5000],['Bananas', 6000, 6000, 6500, 6000],['Oranges', 500, 300, 200, 700]] ws.append(["Fruit", "2011", "2012", "2013", "2014"]) #新增 for row in data:#数据添加到表格中 ws.append(row) tab = Table(displayName="Table1", ref="A1:E5") # 添加带有条带行和条带列的默认样式 style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=True, showLastColumn=True, showRowStripes=True, showColumnStripes=True) #第一列是否和样式第一行颜色一行,第二列是否··· #是否隔行换色,是否隔列换色 tab.tableStyleInfo = style ws.add_table(tab) wb.save("sample1.xlsx")
15、给单元格设定字体颜色
from openpyxl import Workbook from openpyxl.styles import colors from openpyxl.styles import Font wb = Workbook() ws = wb.active a1 = ws['A1'] d4 = ws['D4'] ft = Font(color=colors.RED) # color="FFBB00",颜色编码也可以设定颜色 a1.font = ft d4.font = ft #如果要更改字体的颜色,需要重新分配 #italic 倾斜字体 a1.font = Font(color=colors.RED, italic=True) # 仅设置a1 a1.value = "abc" wb.save("sample2.xlsx")
16、设定字体和大小
from openpyxl import Workbook from openpyxl.styles import colors wb = Workbook() ws = wb.active a1 = ws['A1'] d4 = ws['D4'] a1.value = "abc" from openpyxl.styles import Font from copy import copy ft1 = Font(name=u'宋体', size=14) ft2 = copy(ft1) #复制字体对象 ft2.name = "Tahoma" print(ft1.name) print(ft2.name) print(ft2.size) a1.font = ft1 wb.save("sample3.xlsx")
17、设定行和列的字体
from openpyxl import Workbook from openpyxl.styles import Font wb = Workbook() ws = wb.active col = ws.column_dimensions['A'] col.font = Font(bold=True) #将A列设定为粗体 row = ws.row_dimensions[1] row.font = Font(underline="single") #将第一行设定为下划线格式 wb.save("e:\\sample.xlsx")
18、设定单元格的边框、字体、颜色、大小和边框背景色
from openpyxl import Workbook from openpyxl.styles import Font from openpyxl.styles import NamedStyle, Font, Border, Side,PatternFill wb = Workbook() ws = wb.active highlight = NamedStyle(name="highlight") highlight.font = Font(bold=True, size=20,color= "ff0100") highlight.fill = PatternFill("solid", fgColor="DDDDDD")#背景填充 bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) print dir(ws["A1"]) ws["A1"].style =highlight wb.save("sample.xlsx")
from openpyxl import Workbook from openpyxl.styles import Font from openpyxl.styles import NamedStyle, Font, Border, Side,PatternFill from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font wb = Workbook() ws = wb.active #设置字体 ft = Font(name='微软雅黑', size=11, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') #设置填充 fill = PatternFill(fill_type="solid", start_color='FFEEFFFF', end_color='FF001100') #边框可以选择的值为:'hair', 'medium', 'dashDot', 'dotted', 'mediumDashDot', 'dashed', 'mediumDashed', 'mediumDashDotDot', 'dashDotDot', 'slantDashDot', 'double', 'thick', 'thin'] #diagonal 表示对角线 bd = Border(left=Side(border_style="thin",color='FF001000'), right=Side(border_style="thin",color='FF110000'), top=Side(border_style="thin",color='FF110000'), bottom=Side(border_style="thin",color='FF110000'), diagonal=Side(border_style=None,color='FF000000'), diagonal_direction=0, outline=Side(border_style=None,color='FF000000'), vertical=Side(border_style=None,color='FF000000'), horizontal=Side(border_style=None,color='FF110000') ) #设置对齐方式 alignment=Alignment(horizontal='general', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) number_format = 'General' #单元格数据类型 protection = Protection(locked=True,hidden=False) #保护格式 ws["B3"].font = ft ws["B3"].fill =fill ws["B3"].border = bd ws["B3"].alignment = alignment ws["B3"].number_format = number_format ws["B3"].value ="我在这里" wb.save("sample4.xlsx")