Python3 操作Excel--openpyxl

一、创建一个工作簿

---

• 使用openpyxl没有必要先在系统中新建一个.xlsx，我们需要做的只需要引入Workbook这个类，接着开始调用它。

>>> from openpyxl import Workbook
>>> wb = Workbook()

• 一个工作簿(workbook)在创建的时候同时至少也新建了一张工作表(worksheet)。你可以通过openpyxl.workbook.Workbook.active()调用得到正在运行的工作表。

>>> ws = wb.active

注意：该函数调用工作表的索引(_active_sheet_index)，默认是0。除非你修改了这个值，否则你使用该函数一直是在对第一张工作表进行操作。

• 使用openpyxl.workbook.Workbook.create_sheet()新建一张表

>>> ws1 = wb.create_sheet() #默认插在工作簿末尾
# or
>>> ws2 = wb.create_sheet(0) # 插入在工作簿的第一个位置

• 在创建工作表的时候系统自动命名。他们按照序列依次命名 (Sheet, Sheet1, Sheet2, ...)。你可以通过调用下面的属性修改工作表的名称：

ws.title = "New Title"

• 标签栏的背景色默认为白色。你可以通过提供一个RRGGBB颜色码改变标签栏的字体颜色：

ws.sheet_properties.tabColor = "1072BA"

• 一旦你获取工作表的名字，你可以通过workbook的key或者** openpyxl.workbook.Workbook.get_sheet_by_name()
  **方法得到该工作表

>>> ws3 = wb["New Title"]
>>> ws4 = wb.get_sheet_by_name("New Title")

• 你可以通过**openpyxl.workbook.Workbook.get_sheet_names()
  **方法得到工作簿的所有工作表。

>>> print(wb.get_sheet_names())
['Sheet2', 'New Title', 'Sheet1']

你也可以循环得到所有的工作表

二、操作数据

---

使用一个单元格

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

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

>>> 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'))
((<Cell Sheet1.A1>, <Cell Sheet1.B1>, <Cell Sheet1.C1>),
 (<Cell Sheet1.A2>, <Cell Sheet1.B2>, <Cell Sheet1.C2>))
>>> for row in ws.iter_rows('A1:C2'):
              for cell in row:
                    print cell
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>

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

>>> ws = wb.active
>>> ws['C9'] = 'hello world'
>>> ws.rows
((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
(<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),
(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),
(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),
(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),
(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))

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

>>> ws.columns
((<Cell Sheet.A1>,
<Cell Sheet.A2>,
<Cell Sheet.A3>,
<Cell Sheet.A4>,
<Cell Sheet.A5>,
<Cell Sheet.A6>,
...
<Cell Sheet.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
<Cell Sheet.C2>,
<Cell Sheet.C3>,
<Cell Sheet.C4>,
<Cell Sheet.C5>,
<Cell Sheet.C6>,
<Cell Sheet.C7>,
<Cell Sheet.C8>,
<Cell Sheet.C9>))

数据存储

• 一旦我们有一个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 -*-
import re
import openpyxl

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

#获取版本列表中日期最新的版本，正常情况下返回日期，如20170707
def getnewversion(versionList):
    datelist=[]
    result=""
    for versionline in versionList:
        datelist+=re.findall(r"(?<!SZ)(d{8}|d{7}|d{6})", versionline)#零宽负向断言，排除SZ开头的合同号
    if len(datelist) == 0:#如果未匹配出日期，直接返回
        return "无匹配日期"
    else:
        for index in range(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))]#返回提示信息+最新版本

#从软件版本信息中获取日期最新的版本
def getLatestDate(versionText):
    listVersion = []
    matchV = re.findall(r"((PRS-700U|PRS-7000|PRS7000|PRS700U).+?(?<!SZ)(d{8}|d{7}|d{6}))", versionText,
                        re.MULTILINE)  # 非贪婪（懒惰）匹配，匹配尽量少的内容，以免将两条版本信息匹配为一条
    for matchVersion in matchV:  # findall会匹配所有()分组，取第一个，即最外层()的匹配
        listVersion.append(matchVersion[0])
    return getnewversion(listVersion)

#wbname==即文件名称，sheetname==工作表名称，可以为空，若为空默认第一个工作表
def readwb(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 in ws.rows:
        list=[]
        thisIndex=ws.cell(row=i,column=1).value
        thisVersion=getLatestDate(ws.cell(row=i,column=5).value)
        if lastIndex=="":#如果是首次比较，更新编号即可
            lastIndex = thisIndex
            lastVersion = thisVersion
        elif thisIndex==lastIndex:#如果是相同工程，比较哪个日期更新
            if thisVersion=="无匹配日期":
                print(str(i)+"无匹配日期")
            if thisVersion>lastVersion:#提取的日期更大
                for cell in row:
                    aa = str(cell.value)
                    if (aa == "None"):
                        aa = ""
                    list.append(aa)
                data.pop()#删除原有的较小行，新增新的较大行
                data.append(list)
                lastIndex = thisIndex#更新编号
                lastVersion = thisVersion
        else:#如果是不同工程，直接加入结果
            for cell in row:
                aa = str(cell.value)
                if (aa == "None"):
                    aa = ""
                list.append(aa)
            data.append(list)
            lastIndex = thisIndex
            lastVersion = thisVersion
        i=i+1

    print (wbname +"-"+sheetname+"- 已成功读取")
    return data

#新建excel
def creatwb(wbname):
    wb=openpyxl.Workbook()
    wb.save(filename=wbname)
    print ("新建Excel："+wbname+"成功")

# 写入excel文件中 date 数据，date是list数据类型， fields 表头
def savetoexcel(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=1
    col1=0
    for 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)

def write_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 in records:
        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 += 1
    ws.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 

def write_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 in records:
        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 += 1
    ws.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")