python中openpyxl的用法_python的openpyxl?

import openpyxl
from openpyxl.utils import get_column_letter

wb = openpyxl.Workbook()
sheet = wb.active

#改变表单名字
print(sheet.title)
sheet.title = 'Happy2017'
print(wb.get_sheet_names())
wb.save('example.xlsx')

#创建第一张表单名为First sheet
wb.create_sheet(index=0,title='First sheet')
wb.create_sheet(index=2,title='xp')
print(wb.get_sheet_names())

#删掉表单(通过表单名字拿到表单对象)
wb.remove_sheet(wb.get_sheet_by_name("xp"))
print(wb.get_sheet_names())
wb.save('templ.xlsx')


#单元格写内容
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 'Hello python'
print(sheet['A1'].value)

#创建表单range names往1到9行填入1到9数字
ws1 = wb.create_sheet('range names')
for row in range(1,10):
    ws1.append(range(8))

#创建表单list,
ws2 = wb.create_sheet('List')

rows = [['Number','Batch 1','Batch2'],
        [2,40,30],
        [3,40,25],
        [4,50,30],
        [6,50,30],]
for row in rows:
    ws2.append(row)

ws3 = wb.create_sheet('Data')
for row in range(5,20):
    for col in range(10,15):
        ws3.cell(column=col,row=row,value=get_column_letter(col))
print(ws3['A10'].value)

wb.save('example_1.xlsx')


例3：
import openpyxl
#大批量修改表单上产品价格
PRICE_UPDATE = {
    'Garlic':3.17,
    'Celery':1.19,
    'Lemon':1.27
}

wb = openpyxl.load_workbook('produceSales.xlsx')
ws = wb.get_sheet_by_name('sheet')

#通过遍历表单产品名与字典key相同可以直接修改价格
for row in range(2,ws.max_row+1):
    productName = ws.cell(row=row,column=1).value
    if productName in PRICE_UPDATE:
        ws.cell(row=row,column=2).value=PRICE_UPDATE[productName]
#另存为procuct2.xlsx
ws.save('product2.xlsx')


#指定行或列输入数值

import openpyxl as op

def write():                                      
    num_list = [1,2,3,4,5,6]
 
# 应先将excel文件放入到工作目录下                                  
    bg = op.load_workbook(r"要打开excel的前缀名.xlsx") 
# “Sheet1”表示将数据写入到excel文件的sheet1下    
    sheet = bg["Sheet1"] 
# sheet.cell(1,1,num_list[0])表示将num_list列表的第0个数据1写入到excel表格的第一行第一列                         		 	
    for i in range(1, len(num_list)+1):						
        sheet.cell(i , 1, num_list[i - 1])	
# 对文件进行保存   				
    bg.save("要打开excel的前缀名.xlsx")            			      

write()