python如何实现办公自动化培训_Python办公自动化

使用xlrd读，xlwt写xls格式，openpyxl写xlsx格式(扩展自学)

#author: 飘香的城堡

import xlrd

import xlwt

import openpyxl

# data = xlrd.open_workbook("data1.xlsx")

# print( data.sheet_loaded(0))

#data.unload_sheet(0)

#print( data.sheet_loaded(0))

#print( data.sheet_loaded(1))

# try:

#     print( data.sheets() )

# except:

#     print("error")

#

# print(data.sheet_by_index(0))

# print(data.sheet_names())

# print(data.sheet_by_name('sheet3'))

# print(data.nsheets)

#操作excel行

# sheet=data.sheet_by_index(0)

# print(sheet.nrows)

# print(sheet.row(0))

# print(sheet.row_types(1))

# print(sheet.row(1)[2].value)

# print(sheet.row_values(1))

#操作excel列

# sheet=data.sheet_by_index(0)

# print(sheet.ncols)

# print(sheet.col(2))

# #使用xlwt，写入xls格式文件

# #写入excel

# wb=xlwt.Workbook()

# #创建工作表

# ws=wb.add_sheet('CNY')

# #填充数据

# ws.write_merge(0,1,0,5, '2019年货币兑换表')

# #写入货币数据

# data=(("Date","英镑",'人民币','港币',"日元","美元"),("01/01/2019",8.72251,1,0.877885,0.06722,6.8759),

#       ("02/01/2019",8.63251,1,0.877885,0.06722,6.8759))

# for i,item in enumerate(data):

#     for j,val in enumerate(item):

#         ws.write(i+2, j,val)

#

# #创建第二个工作表

# wsimage=wb.add_sheet('image')

# #写入图片

# wsimage.insert_bitmap('2017.bmp',0,0)

# #保存

# wb.save('2019-CNY.xls')

#使用openpyxl，写入xls格式文件

#写入excel

from openpyxl.drawing.image import Image

from openpyxl.styles import Alignment

wb=openpyxl.Workbook()

# #创建工作表

ws=wb.active

ws.title='CNY'

# #填充数据,下标必须从1开始

ws.merge_cells(start_row=1,start_column=1,end_row=2,end_column=6) # "A1:F2"

ws.cell(1,1, "2019年货币兑换表").alignment = Alignment(horizontal='center', vertical='center')

# #写入货币数据

data=(("Date","英镑",'人民币','港币',"日元","美元"),("01/01/2019",8.72251,1,0.877885,0.06722,6.8759),

("02/01/2019",8.63251,1,0.877885,0.06722,6.8759))

for i,item in enumerate(data):

for j,val in enumerate(item):

cell=ws.cell(i+3,j+1)

cell.value=val

cell.alignment = Alignment(horizontal='center', vertical='center')

#创建第二个工作表

wsimage=wb.create_sheet(str(2))

wsimage.title='image'

#写入图片

# wsimage.insert_bitmap('2017.bmp',0,0)

img=Image('2017.bmp')

wsimage.add_image(img,"a1")

#保存

wb.save('2019-CNY.xlsx')

#create at: 2021/2/3