#coding=utf-8

'''

excel基本操作

'''

from openpyxl import Workbook

wb=Workbook()

ws1=wb.create_sheet('sh1')

ws2=wb.create_sheet('sh2')

ws3=wb.copy_worksheet(ws1)

ws3.title='copy_sh1'

ws1['A1']=1

ws1['A2']=2

ws1['A3']=3

ws1['B1']=4

ws1['B2']=5

ws1['B3']=6

ws1['C1']=7

ws1['C2']=8

ws1['C3']=9

ws1.append([10,20,30])

ws1.append([40,50,60])

wb.save('e:\\test\\ceshi.xlsx')

'''

for i in wb.sheetnames:

    print(type(i))

    print(i)

for i in wb:

    print(type(i))

    print(i)

#按行读取

for i in ws1.iter_rows():

    print('type(i):',type(i))

    print('i:',i)

    for j in i:

        print('type(j):',type(j))

        print('j:',j.value)

'''

#按列读取

for i in ws1.iter_cols():

    print('type(i):',type(i))

    print('i:',i)

    for j in i:

        print('type(j):',type(j))

        print('j:',j.value)

#引用excel的公式

ws1['A8']='=sum(1,2)'

ws1['A9']='=sum(A1:A5)'

wb.save('e:\\test\\ceshi.xlsx')

ws2['A1']=1.23

d=ws2.cell(row=2,column=4,value=203)

print('d.value:',d.value)

print(ws2.cell(row=2,column=4).value)

print(ws2['D2'].value)

#批量提取数据

data=[]

d={}

for i in ws1.iter_rows():

    temp=[]

    for j in i:

        temp.append(j.value)

    d[i]=temp

print(d)

dd={}

for i in range(1,6):

    temp=[]

    for j in range(1,4):

        temp.append(ws1.cell(row=i,column=j).value)

    dd[i]=temp

print('dd:',dd)

#操作多行

for i in ws1[1:5]:

    print('操作多行：',i)

    for j in i:

        print(j.value)

#操作多列

for i in ws1['A':'C']:

    print('操作多列',i)

    for j in i:

        if j.value!=None:

            print(j.value)

        else:

            print(0)

print('*'*20)

#print(ws1[1])

#print(ws1[1:3])

#按行定位单元格取数据：

for i in range(1,len(ws1[1:3])+1):

    print(ws1[i])

    for j in range(len(ws1[i])):

        print('%s,%s:%s'%(i,j,ws1[i][j].value))

print('按列定位单元格取数据')

for i in ws1['A':'C']:

    for j in range(len(i)):

        print(i[j].value)

#获取所有行、列

for i in ws1.rows:

    print(i)

for i in ws1.columns:

    print(i)

#打印有效区域

print(ws1.min_row,ws1.min_column)

print(ws1.max_row,ws1.max_column)

print(ws1['A':'C'])

print(ws1[1:3])