#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")
