python3 excel基本操作及格式设置 python3 excel基本操作及格式设置

d:anaconda3python3python.exe -m pip install --upgrade pip

pip install xlrd
pip install xlwt
pip install xlutils

pip install properties
pip install xlwt

doskey /history

d:anaconda3python3python.exe -m pip install --upgrade pip

import xlrd,xlwt
from xlutils.copy import copy

import xlrd #导入模块
from xlutils.copy import copy #导入copy模块
#rb = xlrd.open_workbook('lll.xlsx') #打开weng.xls文件

file_path=r'D: ote202003春雷log4mail801my200330aaa.xlsx'
file2path=r'D: ote202003春雷log4mail801my200330aa1.xlsx'
read_file = xlrd.open_workbook(file_path,formatting_info=True)
rb = xlrd.open_workbook(file_path,formatting_info=True)

wb = copy(rb) #利用xlutils.copy下的copy函数复制
wb.save(file2path) #保存文件

import xlrd                           #导入模块
from xlutils.copy import copy        #导入copy模块
rb = xlrd.open_workbook('lll.xlsx')    #打开weng.xls文件
wb = copy(rb)                          #利用xlutils.copy下的copy函数复制
ws = wb.get_sheet(0)                   #获取表单0
ws1 = wb.get_sheet(1)
wb.save('sss.xls')                    #保存文件

ws.write(0, 0, 'changed!') #改变（0,0）的值
ws.write(8,0,label = '好的') #增加（8,0）的值

#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

#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

python3 excel基本操作及格式设置 python3 excel基本操作及格式设置

相关推荐