python excel基本操作

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