openpyxl库

1、建立新文件并保存到指定路径

from openpyxl import Workbook
wb = Workbook() #创建一个新的工作簿
ws=wb.active
print(ws.title)
wb.save(r'E:\个人资料\编程\python\数据文件\test.xlsx')

2、打开路径的excel文件

from openpyxl import Workbook,load_workbook
wb = load_workbook(r'E:\个人资料\编程\python\数据文件\test.xlsx')
ws = wb.active
print(ws.title)

print(ws.max_row)
print(ws.max_column)
print(ws.dimensions)

3、操作工作表（sheet）

from openpyxl import *
wb=Workbook()
ws1=wb.active
# print(ws1.title)

ws2=wb.create_sheet('new1',1)
ws3=wb.create_sheet('new2',2)

#移动工作表，注意参数是ws3，不是new2
wb.move_sheet(ws3,-1)
#删除工作表,注意括号中是表的名字
del wb['new2']

print(wb.sheetnames)

4、访问单元格

from openpyxl import Workbook
import pandas as pd

wb=Workbook()
ws=wb.active
cell=ws.cell(6,1,'hello')  #第六行第一列，值为hell0

for l in range(1,6):
    for h in range(1,11):
        ws.cell(h,l,(l-1)*10+h)
wb.save('3.访问单元格.xlsx')
# print(cell.value)
# print(cell.row)
# print(cell.column)
# print(cell.column_letter)
# print(cell.coordinate)   #结果为A6


#遍历指定单元格
for cells in ws['a2:c4']:
    for cell in cells:
        print(cell)

# df = pd.read_excel('3.访问单元格.xlsx',header=None)
# df.columns=['a','b','c','d','e']
# df.to_excel('3.访问单元格.xlsx')
# print(df.head())

5、操作单元格

from openpyxl import Workbook
import pandas as pd

wb=Workbook()
ws=wb.active

#写入10*10的一个数据矩阵
for l in range(1,11):
    for h in range(1,11):
        ws.cell(h,l,(l-1)*10+h)

# ws.merge_cells('b2:d4')
# ws.insert_cols(2,3)     #表示从第二列开始插入三列
# ws.insert_rows(3,2)
#删除操作，和插入一样
# ws.delete_cols()
# ws.delete_rows()

ws.move_range('c4:d5',2,-1)   #表示向下移动两行，向前移动一列

wb.save('4.操作单元格.xlsx')

df = pd.read_excel('4.操作单元格.xlsx',header=None)
print(df)

6、excel公式操作

from openpyxl import Workbook
from openpyxl.utils import FORMULAE
from openpyxl.formula.translate import Translator   #引入，复制公式
import pandas as pd


wb=Workbook()
ws=wb.active

print(len(FORMULAE))   #结果是352，表明有352个可用的公式

print('SUM' in FORMULAE)  #结果是True，表明Sum公式是可用的

#下边的代码是创建基本的价格数据，3行2列
ws.append(['价格1','价格2','求和','均值'])
ws.append([11,13])
ws.append([21,15])
ws.append([24,33])

ws['c2']='=SUM(A2:B2)'
ws['d2']='=AVERAGE(A2:B2)'
#c3单元格用C2的公式
ws['c3']=Translator(formula='=SUM(A2:B2)',origin='c2').translate_formula('c3')


#使用循环遍历单元格
for cells in ws['c2:c4']:
    cells[0].value=Translator(formula='=SUM(A2:B2)',origin='c2').translate_formula(cells[0].coordinate)
for cells in ws['d2:d4']:
    cells[0].value=Translator(formula='=AVERAGE(A2:B2)',origin='d2').translate_formula(cells[0].coordinate)

wb.save('5.使用公式.xlsx')

df=pd.read_excel('5.使用公式.xlsx')
print(df)
#
for cells in ws['a2:d4']:
    for cell in cells:
        print(cell.value)

7、设置格式

from openpyxl.styles import Font,Alignment   #Alignment用于对齐
from openpyxl import Workbook,load_workbook
wb = load_workbook('6.设置格式.xlsx')
ws=wb.active

# ws['a1'].font=Font(name='微软雅黑',color='0000FF',bold=True,strike=True,italic=True)
# ws['a1:c3']语法错误

for cell in ws['a1:c3']:
    for i in range(3):
        cell[i].font=Font(name='微软雅黑',color='0000FF',bold=True,strike=True,italic=True)

#设置行高和列宽
ws.row_dimensions[2].height=30
ws.column_dimensions['b'].width=30

wb.save('6.设置格式.xlsx')

8、vlookup功能

import pandas as pd
from openpyxl import Workbook

df=pd.read_excel(r'E:\Desktop\每日交易策略.xlsx',sheet_name='Sheet2')
df1=pd.read_excel(r'E:\Desktop\每日交易策略.xlsx',sheet_name='Sheet3')
df['辅助列']=df['材料名称'] + df['型号']
m_column=df.pop('辅助列')
df.insert(0,'辅助列',m_column)

df1['辅助列']=df1['材料名称'] + df1['型号']
m_column1=df1.pop('辅助列')
df1.insert(0,'辅助列',m_column)
# print(df)
# print(df1)

new_table=df.merge(df1,how='left',on='辅助列')
print(new_table)