python3openpyxl_Python3 操作Excel--openpyxl

二、操作数据

使用一个单元格

现在我们已经知道如何使用一张工作表了，下面我我们开始修改工作表中单元格的内容

单元格可以直接根据他们的索引直接获得

>>> c = ws['A4']

通过上述的语句，将返回在A4处的单元格，如果不存在将在A4新建一个。* * 单元格的值也可以直接赋值

>>> ws['A4'] = 4

还提供** openpyxl.worksheet.Worksheet.cell()

**方法获取单元格

>>> c = ws.cell('A4')

也可以根据行列值获取单元格

>>> d = ws.cell(row = 4, column = 2)

注意：当一个工作表被创建是，其中不包含单元格。只有当单元格被获取是才被创建。这种方式我们不会创建我们从不会使用的单元格，从而减少了内存消耗。

警告：由于上述特性，你如果遍历了单元格而非想要使用它们也将会在内存当中创建。比如下面：

>>> for i in range(1,101):

for j in range(1,101):

ws.cell(row = i, column = j)

上述代码将会在内存中创建100*100个单元格。

当然，这里也有方法来清理这些不想要的单元格，在后续我们将会介绍。

使用多个单元格

使用切片获取多个单元格

>>> cell_range = ws['A1':'C2']

使用**openpyxl.worksheet.Worksheet.iter_rows()

**方法获得多个单元格

>>> tuple(ws.iter_rows('A1:C2'))

((, , ),

(, , ))

>>> for row in ws.iter_rows('A1:C2'):

for cell in row:

print cell

如果你需要迭代文件中所有的行或者列，你可以使用openpyxl.worksheet.Worksheet.rows()

>>> ws = wb.active

>>> ws['C9'] = 'hello world'

>>> ws.rows

((, , ),

(, , ),

(, , ),

(, , ),

(, , ),

(, , ),

(, , ),

(, , ),

(, , ))

或者使用openpyxl.worksheet.Worksheet.columns()方法

>>> ws.columns

((,

,

,

,

,

,

...

,

,

),

(,

,

,

,

,

,

,

,

))

数据存储

一旦我们有一个openpyxl.cell.Cell，我们可以直接为该单元格赋值

>>> c.value = 'hello, world'

>>> print(c.value)

'hello, world'

>>> d.value = 3.14

>>> print(d.value)

3.14

你也可以使用Python中的其他类型和格式

>>> wb = Workbook(guess_types=True)

>>> c.value = '12%'

>>> print(c.value)

0.12

>>> import datetime

>>> d.value = datetime.datetime.now()

>>> print d.value

datetime.datetime(2010, 9, 10, 22, 25, 18)

>>> c.value = '31.50'

>>> print(c.value)

31.5

三、保存到文件

保存工作簿最简单最安全的方式是使用openpyxl.workbook.Workbook的**openpyxl.workbook.Workbook.save()

**方法

>>> wb = Workbook()

>>> wb.save('balances.xlsx')

！特别警告：这个操作将会在没有认识提示的情况下用现在写的内容，覆盖掉原文件中的所有内容

你也可以 as_template=True，将文件保存称为一个模板

>>> wb = load_workbook('document.xlsx')

>>> wb.save('document_template.xltx', as_template=True)

如果as_template=False(默认)，则将文件或模板保存为文件

>>> wb = load_workbook('document_template.xltx')

>>> wb.save('document.xlsx', as_template=False)

>>> wb = load_workbook('document.xlsx')

>>> wb.save('new_document.xlsx', as_template=False)

警告：在保存文件到文件模板中的时候你应该监控数据的属性和文件扩展名，反之亦然；否则，你得到的工作簿可能无法打开。

比如下面的：

>>> wb = load_workbook('document.xlsx')

>>> # Need to save with the extension *.xlsx

>>> wb.save('new_document.xlsm')

>>> # MS Excel can't open the document

>>>

>>> # or

>>>

>>> # Need specify attribute keep_vba=True

>>> wb = load_workbook('document.xlsm')

>>> wb.save('new_document.xlsm')

>>> # MS Excel can't open the document

>>>

>>> # or

>>>

>>> wb = load_workbook('document.xltm', keep_vba=True)

>>> # If us need template document, then we need specify extension as *.xltm.

>>> # If us need document, then we need specify attribute as_template=False.

>>> wb.save('new_document.xlsm', as_template=True)

>>> # MS Excel can't open the document

四、从文件中导入

和写入文件的方式相同，你可以引入openpyxl.load_workbook()来打开一个已经存在的工作簿

>>> from openpyxl import load_workbook

>>> wb2 = load_workbook('test.xlsx')

>>> print wb2.get_sheet_names()

['Sheet2', 'New Title', 'Sheet1']

五、举例：

#-*- coding: utf-8 -*-

importreimportopenpyxl

DATA_FILE= './项目清单.xlsx'#当前目录下待处理文件

SHEETNAME='反措清单'STORED_FILE= './项目清单out.xlsx'#处理结果保存到当前目录下目标文件

#获取版本列表中日期最新的版本，正常情况下返回日期，如20170707

defgetnewversion(versionList):

datelist=[]

result=""

for versionline inversionList:

datelist+=re.findall(r"(?

if len(datelist) == 0:#如果未匹配出日期，直接返回

return "无匹配日期"

else:for index inrange(len(datelist)):if len(str(datelist[index]))==8:

result+=""

elif len(datelist[index])==6:#如果是六位日期，先补齐为8位

datelist[index]='20'+datelist[index]else:#如果是其他位数(7位)的日期，提示

result+="请检查%s日期格式."%datelist[index]return result+datelist[datelist.index(max(datelist))]#返回提示信息+最新版本

#从软件版本信息中获取日期最新的版本

defgetLatestDate(versionText):

listVersion=[]

matchV= re.findall(r"((PRS-700U|PRS-7000|PRS7000|PRS700U).+?(?

re.MULTILINE)#非贪婪(懒惰)匹配，匹配尽量少的内容，以免将两条版本信息匹配为一条

for matchVersion in matchV: #findall会匹配所有()分组，取第一个，即最外层()的匹配

listVersion.append(matchVersion[0])returngetnewversion(listVersion)#wbname==即文件名称，sheetname==工作表名称，可以为空，若为空默认第一个工作表

defreadwb(wbname,sheetname):

wb=openpyxl.load_workbook(filename=wbname,read_only=True)if (sheetname==""):

ws=wb.get_active_sheet()else:

ws=wb.get_sheet_by_name(sheetname)

data=[]

lastIndex=""lastVersion=""i=1

for row inws.rows:

list=[]

thisIndex=ws.cell(row=i,column=1).value

thisVersion=getLatestDate(ws.cell(row=i,column=5).value)if lastIndex=="":#如果是首次比较，更新编号即可

lastIndex =thisIndex

lastVersion=thisVersionelif thisIndex==lastIndex:#如果是相同工程，比较哪个日期更新

if thisVersion=="无匹配日期":print(str(i)+"无匹配日期")if thisVersion>lastVersion:#提取的日期更大

for cell inrow:

aa=str(cell.value)if (aa == "None"):

aa= ""list.append(aa)

data.pop()#删除原有的较小行，新增新的较大行

data.append(list)

lastIndex= thisIndex#更新编号

lastVersion =thisVersionelse:#如果是不同工程，直接加入结果

for cell inrow:

aa=str(cell.value)if (aa == "None"):

aa= ""list.append(aa)

data.append(list)

lastIndex=thisIndex

lastVersion=thisVersion

i=i+1

print (wbname +"-"+sheetname+"- 已成功读取")returndata#新建excel

defcreatwb(wbname):

wb=openpyxl.Workbook()

wb.save(filename=wbname)print ("新建Excel："+wbname+"成功")#写入excel文件中 date 数据，date是list数据类型， fields 表头

defsavetoexcel(data,fields,sheetname,wbname):print("写入excel：")

wb=openpyxl.load_workbook(filename=wbname)

sheet=wb.active

sheet.title=sheetname

field=1

for field in range(1,len(fields)+1): #写入表头

_=sheet.cell(row=1,column=field,value=str(fields[field-1]))

row1=1col1=0for row1 in range(2,len(data)+2): #写入数据

for col1 in range(1,len(data[row1-2])+1):

_=sheet.cell(row=row1,column=col1,value=str(data[row1-2][col1-1]))

wb.save(filename=wbname)print("保存成功")

datas=readwb(DATA_FILE,SHEETNAME)

headerlist=['编号', '名称', '省份', '装置型号', '软件版本', '软件路径', '汇总', '来源']

creatwb(STORED_FILE)

savetoexcel(datas,headerlist,SHEETNAME,STORED_FILE)

pyxl举例

defwrite_to_excel_with_openpyxl(records,sheetname,wbname):

wb=openpyxl.Workbook()

wb.save(filename=wbname)

ws= openpyxl.load_workbook(filename=wbname)

sheet=ws.active

sheet.title=sheetname

sheet.sheet_properties.tabColor= "1072BA"i= 1

for record inrecords:

record_list=str(record).strip().split()###第一种方法：

#for x in range(1,len(record_list)+1):

#col = get_column_letter(x)

#sheet['%s%s' % (col,i)] = record_list[x-1]

###第二种方法：

for x in range(1,len(record_list)+1):

sheet.cell(row=i,column=x).value = record_list[x-1]

i+= 1ws.save(filename=wbname)

dada=['下游单号 系统单号 上游单号 通道 子帐号 商品 数量 方向 价格 发包时间 回包时间 成交时间 状态 提示 回包COST(秒) 成交COST(秒)','S1-20190712-092457-175-0893X S1-20190712-092457-029-0479P 20190712092445179_4 FDTHK tit1-SG 603160 2000 buy 142 20190712-01:24:57.419 20190712-01:24:58.029 20190712-01:25:01.162 部分成交 ok 0.61 3.743','S1-20190712-092457-606-2172X S1-20190712-092457-551-1778P 20190712092445694_5 FDTHK tit1-SG 002938 4000 sell 30.2 20190712-01:24:57.607 20190712-01:24:57.784 20190712-01:25:00.384 全部成交 ok 0.177 2.777','S1-20190712-092502-298-1344X S1-20190712-092502-253-0313P 20190712092450405_6 FDTHK tit1-SG 600489 30000 sell 9.46 20190712-01:25:02.298 20190712-01:30:03.280 20190712-01:30:13.032 全部成交 ok 300.982 310.734']

write_to_excel_with_openpyxl(dada,"order","order.xlsx

defwrite_to_excel_with_openpyxl(records,sheetname,wbname):

wb=openpyxl.Workbook()

wb.save(filename=wbname)

ws= openpyxl.load_workbook(filename=wbname)

sheet=ws.active

sheet.title=sheetname

sheet.sheet_properties.tabColor= "1072BA"i= 1

for record inrecords:

record_list=str(record).strip().split()###第一种方法：

#for x in range(1,len(record_list)+1):

#col = get_column_letter(x)

#sheet['%s%s' % (col,i)] = record_list[x-1]

###第二种方法：

for x in range(1,len(record_list)+1):

sheet.cell(row=i,column=x).value = record_list[x-1]

i+= 1ws.save(filename=wbname)

dada=['下游单号 系统单号 上游单号 通道 子帐号 商品 数量 方向 价格 发包时间 回包时间 成交时间 状态 提示 回包COST(秒) 成交COST(秒)','S1-20190712-092457-175-0893X S1-20190712-092457-029-0479P 20190712092445179_4 FDTHK tit1-SG 603160 2000 buy 142 20190712-01:24:57.419 20190712-01:24:58.029 20190712-01:25:01.162 部分成交 ok 0.61 3.743','S1-20190712-092457-606-2172X S1-20190712-092457-551-1778P 20190712092445694_5 FDTHK tit1-SG 002938 4000 sell 30.2 20190712-01:24:57.607 20190712-01:24:57.784 20190712-01:25:00.384 全部成交 ok 0.177 2.777','S1-20190712-092502-298-1344X S1-20190712-092502-253-0313P 20190712092450405_6 FDTHK tit1-SG 600489 30000 sell 9.46 20190712-01:25:02.298 20190712-01:30:03.280 20190712-01:30:13.032 全部成交 ok 300.982 310.734']

write_to_excel_with_openpyxl(dada,"order","order.xlsx")

pyxl举例2