import openpyxl
path=r'C:\Users\Administrator\Desktop\*.xlsx'
try : wb = openpyxl.load_workbook(path) #加载excel文件
except : wb=openpyxl.Workbook() #创建新的excel
#工作簿操作
wb.sheetnames #获取全部工作簿
sheet = wb.active #获得当前活动sheet
sheet = wb[sheetname] #设定工作簿
sheet.title #获取当前sheet名
wb.create_sheet() #创建新的工作簿
del wb["Sheet1"] #删除指定工作簿
wb.copy_worksheet(sheet) #复制指定工作簿
#表格信息
sheet['A1'].value #获取A1数值
sheet['A1']=1 #A1赋值为1
sheet.cell(row=1, column=2).value=1
sheet.max_row #获得行数
sheet.max_column #获得列数
sheet['A1':'C3'] #获得矩形区域
for cell in sheet['A1':'C3']:
cell.value #获得矩形区域数据
X = [x.value for x in sheet[get_column_letter(1)]]
for cell in sheet["A"]:
cell.value #获得一列数据
for cell in sheet[1]:
cell.value #获得一行数据
row_range = sheet[1:3]
for row in row_range:
for cell in row:
cell.value #获得多行数据
sheet.rows #获得所有行数据
sheet.columns #获得所有列数据
#增加到最后一行数据
sheet.append(["Fruit", "2011", "2012", "2013", "2014"])
#保存并退出excel
wb.save(path)
wb.close()
获得全部数据
def get_all(sheet,dic=False): #默认返回列表
if dic : data={}
else : data=[]
for rows in sheet.rows:
row=[]
for cell in rows:
row.append(cell.value)
if dic :
if row[0] : data[row[0].strip()]=row
else : data.append(row)
return data
data=get_all(sheet)
获得字典数据
def get_dict(sheet,nums=None):
d={} ; n=0
for rows in sheet.rows:
row=[] ;sub={}
for cell in rows : row.append(cell.value)
if n==0 : index=row
else :
if not nums:
for val1,val2 in zip(index,row) :
try :sub[val1.strip()]=val2
except : print('第一行指标存在None')
sub.pop(index[0].strip())
else :
for num in nums:
try :sub[index[num].strip()]=row[num]
except : print('第一行指标存在None')
try : d[row[0].strip()]=sub
except : print('第一列指标存在None')
n += 1
return d
a=get_dict(sheet,nums=[2,3])
列字母和数字之间的转换
from openpyxl.utils import get_column_letter, column_index_from_string
# 根据列的数字返回字母
print(get_column_letter(2)) # B
# 根据字母返回列的数字
print(column_index_from_string('D')) # 4
单元格类型
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
import datetime
wb = load_workbook('e:\\sample.xlsx')
ws=wb.active
wb.guess_types = True
ws["A1"]=datetime.datetime(2010, 7, 21)
print ws["A1"].number_format
ws["A2"]="12%"
print ws["A2"].number_format
ws["A3"]= 1.1
print ws["A4"].number_format
ws["A4"]= "中国"
print ws["A5"].number_format
# Save the file
wb.save("e:\\sample.xlsx")
执行结果:
yyyy-mm-dd h:mm:ss
0%
General
General
#如果是常规,显示general,如果是数字,显示'0.00_ ',如果是百分数显示0%
数字需要在Excel中设置数字类型,直接写入的数字是常规类型
设置字体风格
import os, openpyxl
from openpyxl.styles import Font
os.chdir("C:\\Users\\Administrator\\Desktop\\")
wb = openpyxl.Workbook()
sheet = wb['Sheet']
italic24Font = Font(size = 24, italic = True,name='Times New Roman', bold=True)
sheet['A1'].font = italic24Font
sheet['A1'] = 'Hello world!'
wb.save('styled.xlsx')
公式
sheet['B9'] = '=SUM(B1:B8)'
设置行高列宽
sheet.row_dimensions[1].height = 70 #设置行高
sheet.column_dimensions['B'].width = 20 #设置列宽
合并和拆分单元格
sheet.merge_cells('A1:D3') #合并单元格
sheet.unmerge_cells('A1:D3') #拆分单元格
去掉无法识别字符
ILLEGAL_CHARACTERS_RE = re.compile(r'[\000-\010]|[\013-\014]|[\016-\037]')
content = ILLEGAL_CHARACTERS_RE.sub(r'', content)