#encoding=utf-8 ''' excel基本操作整理 ''' #openpyxl 版本2.5.4 from openpyxl import * import datetime as dt from datetime import datetime as dtt #新建create_sheet、copy_worksheet wb=Workbook() wb.create_sheet('新创建sheet') ws=wb.active print(ws.title) ws.title='测试用例' ws1=wb.copy_worksheet(ws) ws1.title='测试步骤' print(ws1.title) ws1.sheet_properties.tabColor='FF3030' #设置sheet标签颜色 ws2=wb.copy_worksheet(ws1) ws2.title='测试数据' #新建sheet,设置了位置2 wb.create_sheet('第三个位置sheet',2) #sheet写入数据 ws['A2']='大家好' ws['B4']=dtt.now() ws.append(['nihao','wohao','heihei123',4,5,6]) wb.save('f:\pylianxi\test\test.xlsx') #加载已存在的excel wb=load_workbook('f:\pylianxi\test\test.xlsx') print('load active_sheet:',wb.active.title) print(wb.sheetnames) print('测试数据的标题:',wb['测试数据'].title) for i in wb['测试用例'].rows: for j in i: print(j.value,end=' ') print() print(type(wb)) print(type(wb['测试用例'])) for sheet in wb: print('**',sheet.title) #使用cell读写单元格 ws=wb['测试用例'] #写入 ws.cell(row=2,column=1).value='大家真的好吗' #行号和列号从1开始 ws.cell(row=5,column=3,value='设么东东') wb.save('f:\pylianxi\test\test.xlsx') #读 print('ws.cell(row=2,column=1).value:',ws.cell(row=2,column=1).value) print('ws.cell(row=5,column=3).value:',ws.cell(5,3).value) #小练习 for i in range(1,4): for j in range(1,4): ws.cell(i,j).value=i*j wb.save('f:\pylianxi\test\test.xlsx') #可以设置默认打开的sheet页 #wb.active=1 默认是0 #注意 cell的 行列 是从1,1开始的,不是0,0,rows、columns的迭代器都是从0开始的 print('#'*20) #取某一列 for cell in ws["C"]: print (cell.value) #取多列的值 for i in ws['A':'C']: #for column in ws["A:C"]: for j in i: print(j.value) #按列取 print('*'*20) for i in ws[1:3]: for j in i: print(j.value) print('*'*20) #指定区域范围取值 for i in ws.iter_rows(min_row=1,max_row=2,min_col=1,max_col=2): for j in i: print(j.value) print('*'*20) print(list(ws.rows)[1:]) #从第2行开始取值 for i in ws.rows: print(i) for j in i: print(j.value) print('*'*20) #按列取值 for i in ws.columns: for j in i: print(j.value,end=' ') print() #获取最大行列,最小行列 print(ws.max_row,ws.min_row,ws.max_column,ws.min_column) # print(ws.rows) #行生成器 print(list(ws.rows)[0])#第一行 print(list(ws.rows)[0][0])#第一个单元格 print(list(ws.rows)[0][0].value)#第一个单元格的值 print(ws.columns) #列生成器 print(list(ws.columns)[0])#第一列 print(list(ws.columns)[0][0])#列第一个单元格 print(list(ws.columns)[0][0].value)#列第一个单元格的值 #单元格格式 number_format import datetime wb = load_workbook('e:\sample.xlsx') ws=wb.active wb.guess_types = True ws["A1"]=datetime.datetime(2010, 7, 21) print (ws["A1"].number_format) ws["A2"]="12%" print (ws["A2"].number_format) ws["A3"]= 1.1 print (ws["A3"].number_format) ws["A4"]= "中国" print (ws["A4"].number_format) # Save the file wb.save("e:\sample.xlsx") #guess_types 猜测单元格的类型 wb=load_workbook('e:\sample.xlsx') wb.guess_types=True #可以猜测 存储的某字段值 是什么类型 ws=wb.active ws['D2']='12%' #如果是True 则会按0.12处理,否则会按str处理 print(ws['D2'].value) print(type(ws['D2'].value)) wb.save('e:\sample.xlsx') #单元格输入公式,打开excel会自动计算 ws["A4"] = "=SUM(1, 1)" ws["A5"] = "=SUM(A1:A3)" print (ws["A4"].value) #打印的是公式内容,不是公式计算后的值,程序无法取到计算后的值 print (ws["A5"].value) #打印的是公式内容,不是公式计算后的值,程序无法取到计算后的值 #合并单元格(2种方式) #ws.merge_cells('A2:D2') #合并完了之后取值,按A2取值,B2、C2、D2取不到值 #ws.unmerge_cells('A2:D2') print("ws['A2'].value:",ws['A2'].value) #合并单元格区域 ws.merge_cells(start_row=1,start_column=1,end_row=2,end_column=4) ws.unmerge_cells(start_row=1,start_column=1,end_row=2,end_column=4) #单元格插入图片 from openpyxl.drawing.image import Image img = Image('e:\Snap1.jpg') #ws.add_image(img, 'A15') wb.save('e:\sample.xlsx') #设置单元格字体 from openpyxl.styles import colors from openpyxl.styles import Font ft=Font(name="楷体",color=colors.BLUE,size=20,bold=True,underline="single",family=1) ws['A1'].font=ft ws['A1']='你好吗哈哈' wb.save('e:\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=u'微软雅黑', size=11, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') #ft=Font(bold=True, size=20,color= "ff0100") #fill = PatternFill(fill_type="solid",start_color='FFEEFFFF',end_color='FF001100') fill = PatternFill("solid", fgColor="DDDDDD") #边框可以选择的值为:'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') ) #bdline = Side(style='thick', color="000000") #bd = Border(left=bdline, top=bdline, right=bdline, bottom=bdline) 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["B5"].font = ft ws["B5"].fill =fill ws["B5"].border = bd ws["B5"].alignment = alignment ws["B5"].number_format = number_format ws["B5"].value ="glory road" # Save the file wb.save("e:\sample.xlsx")