Python - 操作Excel文件

Microsoft Excel 幾乎無處不在，使用 Excel 既可以保存客戶寿谴、庫存和雇員數(shù)據(jù)匣距，還可以跟蹤運營、銷售和財務(wù)活動上荡。人們在商業(yè)活動中使用 Excel 的方式五花八門趴樱，難以計數(shù)。

與 Python 的 csv 模塊不同酪捡，Python 中沒有處理 Excel 文件（就是帶有 .xls 和 .xlsx 擴展名的文件）的標(biāo)準(zhǔn)模塊叁征。xlrd 和 xlwt 擴展包使 Python 可以在任何操作系統(tǒng)上處理 Excel 文件，而且對 Excel 日期型數(shù)據(jù)的支持非常好.

內(nèi)省Excel工作簿

Excel 文件與 CSV 文件至少在兩個重要方面有所不同逛薇。

  1. 與CSV 文件不同捺疼，Excel 文件不是純文本文件，所以你不能在文本編輯器中打開它并查看數(shù)據(jù)
  2. 與 CSV 文件不同永罚，一個 Excel 工作簿被設(shè)計成包含多個工作表啤呼，

所以需要知道在不用手動打開工作簿的前提下，如何通過工作簿內(nèi)誓馗ぁ（也就是內(nèi)部檢查）獲取其中所有工作表的信息官扣。通過內(nèi)省一個工作簿，你可以在實際開始處理工作簿中的數(shù)據(jù)之前产捞，檢查工作表的數(shù)目和每個工作表中的數(shù)據(jù)類型和數(shù)據(jù)量醇锚。
內(nèi)省 Excel 文件有助于確定文件中的數(shù)據(jù)確實是你需要的，并對數(shù)據(jù)一致性和完整性做一個初步檢查。弄清楚輸入文件的數(shù)量焊唬，以及每個文件中的行數(shù)和列數(shù)恋昼，可以使你對數(shù)據(jù)處理工作的工作量和文件內(nèi)容的一致性有個大致的概念。

本節(jié)中的演示代碼只解釋新添加的赶促。沒有添加的都是在CSV文件讀寫的里面液肌。

確定工作簿中工作表的數(shù)量、名稱和每個工作表中行列的數(shù)量
import sys
from xlrd import open_workbook


input_file = sys.argv[1]
#打開輸入文件
workbook = open_workbook(input_file)

print('有%s個表' % workbook.nsheets)
# 遍歷輸入文件中的工作表
for worksheet in workbook.sheets():
    print('表名:',worksheet.name,
          '\t 行:',worksheet.nrows,
          '\t 列:',worksheet.ncols)
結(jié)果展示

處理單個工作表

1. 讀寫Excel文件

  • 基礎(chǔ)Python + xlrd + xlwt

    import sys
from  xlrd import open_workbook
from  xlwt import Workbook

input_file = sys.argv[1]
output_file = sys.argv[2]
#生產(chǎn)workbook對象鸥滨，以使我們可以將結(jié)果寫入用于輸出的Excel文件
output_workbook =  Workbook()
#為輸出工作簿添加一個工作表
output_worksheet = output_workbook.add_sheet('jan_2013_output')

with open_workbook(input_file) as workbook:
    #引用名字是‘january_2013’的工作表
    worksheet = workbook.sheet_by_name('january_2013')
    #迭代行嗦哆、列
    for row_index in range(worksheet.nrows):
        for column_index in  range(worksheet.ncols):
            #使用 xlwt 的 write 函數(shù)和行與列的索引將每個單元格的值寫入輸出文件的工作表
            output_worksheet.write(row_index,
                                   column_index,
                                   worksheet.cell_value(row_index,column_index))
#保存關(guān)閉
output_workbook.save(output_file)
    輸出輸入對比

    E 列的日期變成了數(shù)字。數(shù)值 1 代表 1900 年 1 月 1 日婿滓，因為從 1900 年 1 月 0 日過去了 1 天老速，所以輸出表的Purchase Date 沒有格式化。xlrd 擴展包提供了其他函數(shù)來格式化日期值凸主。xlrd API文檔橘券。

    轉(zhuǎn)換時間格式

    import sys
#可以將數(shù)值轉(zhuǎn)換成日期并對日期進行格式化。
from datetime import date
#open_workbook 打開Excel工作簿
#xldate_as_tuple 將Excel中的日期卿吐、時間或日期時間的數(shù)值轉(zhuǎn)換成玉足旁舰，
# 這樣就可以提取出具體的元素，并轉(zhuǎn)成不同的格式
from xlrd import open_workbook,xldate_as_tuple
from xlwt import  Workbook

input_file = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()

output_worksheet = output_workbook.add_sheet('jan_2013_output')

with open_workbook(input_file) as  workbook:
    worksheet = workbook.sheet_by_name('january_2013')
    for row_index in range(worksheet.nrows):
        row_list_output = []
        for col_index in  range(worksheet.ncols):
            # 判斷單元格里的值是否是日期數(shù)據(jù)
            if worksheet.cell_type(row_index,col_index)==3:
                print('第 %d 行' % row_index,'第 %d 列' % col_index)
                # 先將單元格里的表示日期數(shù)值轉(zhuǎn)換成元組
                # 使用cell_value 函數(shù)和行列索引來引用單元格中的值嗡官，或者使用 cell().value
                # workbook.datemode 為了確定是從1900年還是1904年開始計算的
                date_cell = xldate_as_tuple(worksheet.cell_value(row_index,col_index),
                                            workbook.datemode)
                print('轉(zhuǎn)成元組',date_cell)
                # 使用元組的索引來引用元組的前三個元素并將它們作為參數(shù)傳遞給date函數(shù)來轉(zhuǎn)換成date對象箭窜，
                # 用strftime()函數(shù)來將date對象轉(zhuǎn)換成特定格式的字符串
                # 前3個元素也就是年、月衍腥、日
                date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                print('轉(zhuǎn)換成時間', date_cell)
                row_list_output.append(date_cell)
                # 寫入對應(yīng)的格式化的日期
                output_worksheet.write(row_index,col_index,date_cell)
            else:
                # 將sheet中非表示日期的值賦給non_date_celld對象  
                non_date_cell = worksheet.cell_value(row_index,col_index)
                row_list_output.append(non_date_cell)
                # 將sheet中非日期的值位置填充到相應(yīng)位置  
                output_worksheet.write(row_index,col_index,non_date_cell)
output_workbook.save(output_file)

    參數(shù) workbook.datemode 是必需的磺樱，它可以使函數(shù)確定日期是基于 1900 年還是基于 1904 年，并據(jù)此將數(shù)值轉(zhuǎn)換成正確的元組（在 Mac 上的某些 Excel 版本從 1904 年 1 月 1 日開始計算日期紧阔》话眨可以參看微軟的文檔《Excel中1900和1904年日期系統(tǒng)之間的區(qū)別》.

    [圖片上傳失敗...(image-9bfd9a-1551800142451)]

    [圖片上傳失敗...(image-c2e55e-1551800142451)]

  • pandas

    import sys
import pandas as pd

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame =pd.read_excel(input_file,sheetname='january_2013')
writer = pd.ExcelWriter(output_file)
data_frame.to_excel(writer,sheet_name = 'jan_13_output',index=False)
writer.save()

2. 篩選特定行

  1. 行中的值滿足某個條件

任務(wù):篩選出Sale Amount 大于￥1400的行

- 基礎(chǔ)Python

```
import sys
from datetime import date
from xlrd import open_workbook,xldate_as_tuple
from xlwt import Workbook
input_file = sys.argv[1]
output_file = sys.argv[2]
output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('jan_2013_output')
#定位SaleAmount列
sale_amount_column_index = 3
with open_workbook(input_file) as workbook:
    worksheet = workbook.sheet_by_name('january_2013')
    #輸入文件中要寫入輸出文件的行的列表
    data = []
    #提取標(biāo)題
    header = worksheet.row_values(0)
    data.append(header)
    for row_index in range(1,worksheet.nrows):
        row_list = []
        #保存銷售額
        sale_amount = worksheet.cell_value(row_index,sale_amount_column_index)
        #判斷銷售額是否大于1400的行
        if sale_amount > 1400.0:
            for column_index in range(worksheet.ncols):
                #提取每個單元格的值
                cell_value = worksheet.cell_value(row_index,column_index)
                #提取每個單元格的類型
                cell_type = worksheet.cell_type(row_index,column_index)
                #判斷是否是日期類型
                if cell_type == 3:
                    #格式化日期
                    date_cell = xldate_as_tuple(cell_value,workbook.datemode)
                    date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                    row_list.append(date_cell)
                else:
                    row_list.append(cell_value)
        #判斷是否為空
        if row_list:
            data.append(row_list)
    print(data,'\n')
    #迭代data并寫入輸出文件
    # enumerate 可以得到新的連續(xù)索引,要不就會繼續(xù)使用輸入文件的索引
    for list_index,output_list in enumerate(data):
        print('\n','list_index--output_list',list_index,output_list)
        for element_index,element in enumerate(output_list):
            print('element_index--element',element_index, element)
            #按照新得到的索引填入數(shù)據(jù)
            output_worksheet.write(list_index,element_index,element)

output_workbook.save(output_file)

```
[python enumerate用法總結(jié)](http://blog.csdn.net/churximi/article/details/51648388)

[圖片上傳失敗...(image-5ca4d2-1551800142451)]

- pandas
 

```Python
import pandas as  pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file,'january_2013',index_col=None)
data_frame_value_meets_condition = \
    data_frame[data_frame['Sale Amount'].astype(float)>1400]
writer = pd.ExcelWriter(output_file)
data_frame_value_meets_condition.to_excel(writer,
                                          sheet_name = 'jan_13_output',
                                          index=False)
writer.save()
```

可以使用 pandas 篩選出符合某個條件的行，指定你想判斷的列的名稱擅耽，并在數(shù)據(jù)框名稱后面的方括號中設(shè)定具體的判斷條件.
 
如果你需要設(shè)定多個條件，那么可以將這些條件放在圓括號中物遇，根據(jù)需要的邏輯順序用“&”或“|”連接起來乖仇。使用“&”，表示兩個條件必須都為真询兴。使用“|”乃沙，表示只要一個條件為真就可以。

```
data_frame[(data_frame['Sale Amount'].astype(float)>1400)
               & (data_frame['Customer ID'].astype(float) < 3000)]

data_frame[(data_frame['Sale Amount'].astype(float)>1400)
               | (data_frame['Customer ID'].astype(float) < 3000)]

```

  1. 行中的值屬于某個集合

    任務(wù)：購買日期是'01/24/2013','01/31/2013'這兩個日期的

    • 基礎(chǔ)Python + xlwt + xlrd 實現(xiàn)
```Python
import sys
from datetime import date
from xlwt import Workbook
from xlrd import open_workbook,xldate_as_tuple

input_file = sys.argv[1]
output_fiel = sys.argv[2]

output_workbook = Workbook()

output_worksheet = output_workbook.add_sheet('jan_2013_output')
important_dates = ['01/24/2013','01/31/2013']
purchase_date_column_index=4
with open_workbook(input_file) as  workbook:
    worksheet = workbook.sheet_by_name('january_2013')
    data = []
    header = worksheet.row_values(0)
    data.append(header)
    for row_index in range(1,worksheet.nrows):
        purchase_date_time = \
            xldate_as_tuple(worksheet.cell_value(row_index,purchase_date_column_index)
                            ,workbook.datemode)
        purchase_date = date(*purchase_date_time[0:3]).strftime('%m/%d/%Y')
        row_list = []
        #判斷是否在important_dates中
        if purchase_date in important_dates:
            for column_index in range(worksheet.ncols):
                cell_value = worksheet.cell_value(row_index,column_index)
                cell_type = worksheet.cell_type(row_index,column_index)
                if cell_type ==3:
                    date_cell = xldate_as_tuple(cell_value,workbook.datemode)
                    date_cell = date(*date_cell[:3]).strftime('%m/%d/%Y')
                    row_list.append(date_cell)
                else:
                    row_list.append(cell_value)
        if row_list:
            data.append(row_list)

    for list_index,output_list in enumerate(data):
        for element_index,element in enumerate(output_list):
            output_worksheet.write(list_index,element_index,element)
output_workbook.save(output_fiel)
```
 
 - pandas 實現(xiàn)
 
```Python
import pandas as  pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]
data_frame = pd.read_excel(input_file,'january_2013',index_col=None)
important_datas = ['01/24/2013','01/31/2013']
data_frame_value_in_set = data_frame[data_frame['Purchase Date'].isin(important_datas)]
writer = pd.ExcelWriter(output_file)
data_frame_value_in_set.to_excel(writer,sheet_name = 'jan_13_output',index=False)
writer.save()
```

  1. 行中的值匹配與特定的正則表達式

    任務(wù)：客戶姓名以大寫字母 J 開頭

    • python + re + xlrd + xlwt
```Python
import re,sys
from datetime import date
from xlrd import open_workbook,xldate_as_tuple
from xlwt import Workbook

input_file = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('jan_2013_output')
# 使用re模塊的compile函數(shù)創(chuàng)建一個正則表達式pattern
# ?P<my_pattern> 捕獲了名為 <my_pattern> 的組中匹配了的子字符串
#  ^J.* 符號（^ ）表示“在字符串開頭搜索模式” , (.*) 除換行符之外的任意字符
pattren = re.compile(r'(?P<my_pattern>^J.*)')

custom_name_column_index = 1

with open_workbook(input_file) as workbook:
    worksheet = workbook.sheet_by_name('january_2013')
    data = []
    header = worksheet.row_values(0)

    data.append(header)

    for row_index in range(1,worksheet.nrows):
        row_list = []
        # 匹配 
        if pattren.search(worksheet.cell_value(row_index,custom_name_column_index)):
            for column_index in range(worksheet.ncols):
                cell_value = worksheet.cell_value(row_index,column_index)
                cell_type = worksheet.cell_type(row_index,column_index)
                if cell_type==3:
                    date_cell = xldate_as_tuple(cell_value,workbook.datemode)
                    date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                    row_list.append(date_cell)
                else:
                    row_list.append(cell_value)
            if row_list:
                data.append(row_list)


    for list_index,output_list in enumerate(data):
        for element_index ,element in enumerate(output_list):
            output_worksheet.write(list_index,element_index,element)

output_workbook.save(output_file)

```


> ` pattren = re.compile(r'(?P<my_pattern>^J.*)')  `

> r 表示單引號之間的模式是一個原始字符串诗舰。元字符 ?P<my_pattern> 捕獲了名為 <my_pattern> 的組中匹配了的子字符串警儒，以便在需要時將它們打印到屏幕上或?qū)懭胛募Ｎ覀円阉鞯膶嶋H模式是 ' ^J.* ' 。插入符號（^ ）是一個特殊符號蜀铲，表示“在字符串開頭搜索模式”边琉。所以，字符串需要以大寫字母 J 開頭记劝。句點 . 可以匹配任何字符变姨，除了換行符。所以除換行符之外的任何字符都可以跟在 J 后面厌丑。最后定欧，* 表示重復(fù)前面的字符 0 次或更多次。.* 組合在一起用來表示除換行符之外的任意字符可以在 J 后面出現(xiàn)任意次怒竿。
  • pandas
```Python
import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file,'january_2013',index_col=None)
data_frame_value_matches_pattern = \
    data_frame[data_frame['Customer Name'].str.startswith('J')]
writer = pd.ExcelWriter(output_file)
data_frame_value_matches_pattern.to_excel(writer,
                                          sheet_name='jan_2013_output',
                                          index = False)
writer.save()
```

3. 篩選特定列

  1. 使用列索引值

    從工作表中選取特定列的一種方法是使用要保留的列的索引值砍鸠。當(dāng)你想保留的列的索引值非常容易識別，或者在處理多個輸入文件過程中耕驰，各個輸入文件中列的位置是一致（也就是不會發(fā)生改變）的時候爷辱，這種方法非常有效。

    任務(wù)：保留 Customer Name 和 Purchase Date 兩列

    • 基礎(chǔ)Python + xlrd + xlwt
    import sys
from datetime import date
from xlrd import open_workbook,xldate_as_tuple
from xlwt import Workbook

input_file = sys.argv[1]
output_file =sys.argv[2]

output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('jan_2013_output')
# 代表 CustomerName和Purchase Date
my_columns = [1,4]
with open_workbook(input_file) as  workbook:
    worksheet = workbook.sheet_by_name('january_2013')
    data = []
    for row_index in range(worksheet.nrows):
        row_list = []
        for column_index in my_columns:
            cell_value = worksheet.cell_value(row_index,column_index)
            cell_type = worksheet.cell_type(row_index,column_index)
            if cell_type == 3:
                date_cell = xldate_as_tuple(cell_value,workbook.datemode)
                date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                row_list.append(date_cell)
            else:
                row_list.append(cell_value)

        if row_list:
                data.append(row_list)

    for list_index,output_list in enumerate(data):
        for element_index,element in enumerate(output_list):
            output_worksheet.write(list_index,element_index,element)
output_workbook.save(output_file)
    • pandas
      • 設(shè)置數(shù)據(jù)框耍属，在方括號中列出要保留的列的索引值或者名稱（字符串）
      • 設(shè)置數(shù)據(jù)框和iloc函數(shù)托嚣。iloc函數(shù)可以同時選擇特定的行與特定的列。所以使用iloc函數(shù)厚骗，就需要在列索引值前面加上一個冒號和一個逗號示启，表示想要為這些特定列保留所有行。否則领舰，iloc函數(shù)也會使用這些索引值去篩選行夫嗓。
    import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file,'january_2013',index_col=None)

data_frame_column_by_index = data_frame.iloc[:,[1,4]]

writer = pd.ExcelWriter(output_file)
    data_frame_column_by_index.to_excel(writer,sheet_name = 'jan_2013_output',index=False)
    writer.save()


2. 使用列標(biāo)題/列索引值
    
想保留的列的標(biāo)題非常容易識別，或者在處理多個輸入文件過程中冲秽，各個輸入文件中列的位置會發(fā)生改變舍咖，但標(biāo)題不變的時候，這種方法非常有效.

> 任務(wù)：保留 Customer Name 和 Purchase Date 兩列锉桑。因為類似所以只展示根據(jù)列標(biāo)題代碼排霉，跟據(jù)列索引的注釋掉了。因為大同小異民轴。

- 基礎(chǔ)Python + xlrd + xlwt    
    
    ```Python
    
    import sys
    from xlwt import Workbook
    from xlrd import open_workbook,xldate_as_tuple
    from datetime import  date
    
    input_file = sys.argv[1]
    output_file = sys.argv[2]
    
    output_workbook = Workbook()
    #給輸出文件中添加一個工作表
    output_worksheet = output_workbook.add_sheet('jan_2013_output')
    #要寫入的數(shù)據(jù)添加標(biāo)題
    my_columns = ['Customer ID','Purchase Date']
    #根據(jù)列索引
    # my_columns_index = [0,4]
    # my_columns = []
    
    with open_workbook(input_file) as  workbook:
        #按工作表名打開
        worksheet = workbook.sheet_by_name('january_2013')
        #要寫入的數(shù)據(jù)集合
        data = []
        #獲取輸入文件的標(biāo)題
        header_list = worksheet.row_values(0)
        #創(chuàng)建存標(biāo)題索引的列表
        header_index_list = []
    
        #根據(jù)列索引
        # for title_index in range(len(header_list)):
        #     if title_index in my_columns_index:
        #         my_columns.append(header_list[title_index])
    
        # 要寫入的數(shù)據(jù)添加標(biāo)題
        data = [my_columns]
    
        #迭代標(biāo)題索引
        for header_index in range(len(header_list)):
            #判斷標(biāo)題是否是要寫入的標(biāo)題
            if header_list[header_index] in my_columns:
                #添加寫入標(biāo)題的索引
                header_index_list.append(header_index)
        #從索引是1的行也就是內(nèi)容行開始遍歷
        for row_index in range(1,worksheet.nrows):
            #創(chuàng)建要寫入的內(nèi)容列表
            row_list = []
            #遍歷要寫入的標(biāo)題索引
            for column_index in  header_index_list:
                #取出表格內(nèi)容
                cell_value = worksheet.cell_value(row_index,column_index)
                #獲取內(nèi)容類型
                cell_type = worksheet.cell_type(row_index,column_index)
                #判斷是否是日期類型
                if cell_type ==3:
                    #把日期數(shù)據(jù)轉(zhuǎn)換更元組
                    date_cell = xldate_as_tuple(cell_value,workbook.datemode)
                    #取出日期元組中的前3個攻柠，格式化
                    date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                    #添加到要寫入的行l(wèi)ist中
                    row_list.append(date_cell)
                else:
                    #添加要寫入行l(wèi)ist中
                    row_list.append(cell_value)
            #添加到要寫入的list中
            data.append(row_list)
        #遍歷要寫入的數(shù)據(jù)，并給予新的索引
        # list_index 新的行索引
        # output_list 行中的數(shù)據(jù)列表
        for list_index,output_list in enumerate(data):
            # element_index 新的數(shù)據(jù)的列的索引
            # element 數(shù)據(jù)元素
            for element_index,element in enumerate(output_list):
                #按行列定位寫入數(shù)據(jù)
                output_worksheet.write(list_index,element_index,element)
    #保存關(guān)閉
    output_workbook.save(output_file)
    ```
    
- pandas
    
    一種方式是在數(shù)據(jù)框名稱后面的方括號中將列名以字符串方式列出后裸。
    另外一種方式是使用 loc 函數(shù)瑰钮。如果使用 loc 函數(shù)，那么需要在列標(biāo)題列表前面加上一個冒號和一個逗號微驶，表示你想為這些特定的列保留所有行浪谴。
    
    ```Python
    import pandas as pd
    import sys
    
    input_file = sys.argv[1]
    output_file = sys.argv[2]
    #打開輸入文件對應(yīng)的工作表
    data_frame = pd.read_excel(input_file,'january_2013',index_col=None)
    # 獲取符合這兩個標(biāo)題的列
    data_frame_column =\
        data_frame[data_frame.iloc['Customer ID','Purchase Date']]
    #根據(jù)列索引
    # data_frame_column = data_frame.iloc[:,[0,4]]
    #寫入
    writer = pd.ExcelWriter(output_file)
    data_frame_column.to_excel(writer,sheet_name = 'jan_2013_output',index = False)
    #保存
    writer.save()

讀取工作薄中的所有工作表

如何在一個工作薄的所有工作表中篩選特定的行與列？

在所有工作表中篩選特定行

任務(wù)：篩選出工作表中銷售額大于 $2000.00的所有行。

  • 基礎(chǔ)Python +xlrd + xlwt
import  sys
from datetime import date
from xlrd import open_workbook,xldate_as_tuple
from xlwt import Workbook

input_file = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()

output_worksheet = output_workbook.add_sheet('filtrrd_rows_all_worksheets')
#保存 Sale Amount 列的索引值
sales_column_index  =3
# 銷售額閾值
threshold = 2000.00
# 判斷是不是第一個工作表
first_worksheet = True
with open_workbook(input_file) as  workbook:
    data = []
    #遍歷文件中的所有工作表
    for worksheet in  workbook.sheets():
        # 判斷是不是第一個工作表
        if first_worksheet:
            # 取出標(biāo)題行
            header_row = worksheet.row_values(0)
            # 添加標(biāo)題
            data.append(header_row)
            first_worksheet = False
        #從不是標(biāo)題的地方開始迭代
        for row_index in range(1,worksheet.nrows):
            row_list = []
            # 根據(jù) 行列索引 取出 銷售額
            sale_amount = worksheet.cell_value(row_index,sales_column_index)
            # Sale Amount 列中的值與閾值比較
            if sale_amount > threshold:
                # 遍歷符合條件工作表中的符合要求行中的數(shù)據(jù)
                for column_index in  range(worksheet.ncols):
                    #獲取數(shù)據(jù)的值
                    cell_value = worksheet.cell_value(row_index,column_index)
                    #獲取數(shù)據(jù)類型
                    cell_type = worksheet.cell_type(row_index,column_index)
                    if cell_type ==3:
                        #日期類型數(shù)據(jù)轉(zhuǎn)換格式化并添加到行l(wèi)ist中
                        date_cell = xldate_as_tuple(cell_value,workbook.datemode)
                        date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                        row_list.append(date_cell)
                    else:
                        row_list.append(cell_value)
            if row_list:
                data.append(row_list)
    for list_index,coutput_index in enumerate(data):
        for element_index,element in enumerate(coutput_index):
            #更具行列索引添加數(shù)據(jù)
            output_worksheet.write(list_index,element_index,element)

output_workbook.save(output_file)

  • pandas

    pandas 中苟耻，通過在 read_excel 函數(shù)中設(shè)置 sheetname=None 篇恒，可以一次性讀取工作簿中的所有工作表。pandas 將這些工作表讀入一個數(shù)據(jù)框字典梁呈，字典中的鍵就是工作表的名稱婚度，值就是包含工作表中數(shù)據(jù)的數(shù)據(jù)框。所以官卡，通過在字典的鍵和值之間迭代蝗茁，你可以使用工作簿中所有的數(shù)據(jù)。當(dāng)你在每個數(shù)據(jù)框中篩選特定行時寻咒，結(jié)果是一個新的篩選過的數(shù)據(jù)框哮翘，所以你可以創(chuàng)建一個列表保存這些篩選過的數(shù)據(jù)框，然后將它們連接成一個最終數(shù)據(jù)框毛秘。

pandas 可以直接從多個文件中連接數(shù)據(jù)饭寺。基本過程就是將每個輸入文件讀取到 pandas 數(shù)據(jù)框中叫挟，將所有數(shù)據(jù)框追加到一個數(shù)據(jù)框列表艰匙，后使用 `concat()` 函數(shù)將所有數(shù)據(jù)框連接成一個數(shù)據(jù)框。`concat()` 函數(shù)可以使用 axis 參數(shù)來設(shè)置連接數(shù)據(jù)框的方式抹恳，`axis=0` 表示從頭到尾垂直堆疊员凝，`axis=1` 表示并排地平行堆疊。`ignore_index` 參數(shù)默認(rèn)值為False奋献，如果為True健霹，會對新生成的dataframe使用新的索引（自動產(chǎn)生），忽略原來數(shù)據(jù)的索引瓶蚂。


```

import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]
# sheet_name=None 表示讀取所有的工作表
# data_frame 是一個字典
data_frame = pd.read_excel(input_file,sheet_name=None,index_col=None)
row_output = []

#迭代
for worksheet_name,data in data_frame.items():
    #添加符合要求的數(shù)據(jù)
    row_output.append(data[data['Sale Amount'].astype(float) > 2000])
# axis=0 表示從頭到尾垂直堆疊
# gnore_index 參數(shù)默認(rèn)值為False糖埋，
# 如果為True，會對新生成的dataframe使用新的索引（自動產(chǎn)生）窃这，忽略原來數(shù)據(jù)的索引瞳别。
filtered_rows = pd.concat(row_output,axis=0,ignore_index=True)
writer = pd.ExcelWriter(output_file)
filtered_rows.to_excel(writer,sheet_name='allsheet_outPut_Amount_get2000',index=False)
writer.save()

```

在所有工作表中篩選特定列

任務(wù)：選取 Customer Name 和 Sale Amount 列

  • 基礎(chǔ)Python
```
import sys
from datetime import date
from xlrd import open_workbook,xldate_as_tuple
from xlwt import Workbook

input_file = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()

output_worksheet = output_workbook.add_sheet('filter_column')
#要保留的列
my_columns = ['Customer Name','Sale Amount']
#判斷是不是第一個工作表
frist_worksheet = True

with open_workbook(input_file) as workbook:
    data = [my_columns]
    #保存要保留的列的索引值
    index_of_cols_to_keep = []
    for worksheet in workbook.sheets():
        #是否在處理第一個工作表
        if frist_worksheet:
            header = worksheet.row_values(0)
            for column_index in range(len(header)):
                if header[column_index] in my_columns:
                    #標(biāo)題索引值list
                    index_of_cols_to_keep.append(column_index)
                    frist_worksheet = False
        for row_index in  range(1,worksheet.nrows):
            row_list = []
            #只處理標(biāo)題對應(yīng)的列
            for column_index in index_of_cols_to_keep:
                cell_value = worksheet.cell_value(row_index,column_index)
                cell_type = worksheet.cell_type(row_index,column_index)
                if cell_type==3:
                    date_cell = xldate_as_tuple(cell_value,workbook.datemode)
                    date_cell = date(*date_cell).strftime('%m/%d/%Y')
                    row_list.append(date_cell)
                else:
                    row_list.append(cell_value)
            if row_list:
                data.append(row_list)
    for list_index,output_index in enumerate(data):
        for element_index,element in enumerate(output_index):
            output_worksheet.write(list_index, element_index, element)
output_workbook.save(output_file)

```
  • pandas

使用 pandas 中的 read_excel 函數(shù)將所有工作表讀入一個字典。然后杭攻，使用 loc 函數(shù)在每個工作表中選取特定的列洒试，創(chuàng)建一個篩選過的數(shù)據(jù)框列表，并將這些數(shù)據(jù)框連接在一起朴上，形成一個最終數(shù)據(jù)框。

```
import pandas as pd
import sys

input_file = sys.argv[1]
out_file = sys.argv[2]

data_frame = pd.read_excel(input_file,sheet_name=None,index_col=None)
column_output = []

for worksheet_name,data in data_frame.items():
    #loc() 選取特定的列
    column_output.append(data.loc[:, ['Customer Name', 'Sale Amount']])
selsected_columns = pd.concat(column_output,axis=0,ignore_index=True)
writer = pd.ExcelWriter(out_file)
selsected_columns.to_excel(writer,sheet_name="all",index=False)
writer.save()

```

在Excel工作薄中讀取一組工作表

有時候卒煞，只需要處理Excel文件中的一部分表痪宰，比如有10張工作表，但是只需要處理其中的5張。

在一組工作表中篩選特定行

任務(wù)：從第一個和第二個工作表中篩選出“銷售額大于 $1900.00 的那些行

  • 基礎(chǔ)Python

    sheet_by_index 或 sheet_by_name 函數(shù)來處理一組工作表衣撬。

    import sys
from datetime import date
from xlrd import open_workbook,xldate_as_tuple
from xlwt import Workbook

input_file = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('group_line')
my_sheet = [0,1]
threshold = 1900.00
sales_column_index = 3
fristsheet = True
with open_workbook(input_file) as workbook:
    data = []
    for sheet_index in range(workbook.nsheets):
        # 判斷是不是需要的表
        if sheet_index in my_sheet:
            #根據(jù)索引獲取工作表
            worksheet = workbook.sheet_by_index(sheet_index)
            #判斷是不是第一次加載表
            if fristsheet:
                header_row = worksheet.row_values(0)
                data.append(header_row)
                fristsheet = False
            for row_index in  range(1,worksheet.nrows):
                row_list = []
                #獲取行中是金額的數(shù)據(jù)
                sale_amount = worksheet.cell_value(row_index,sales_column_index)
                #判斷是否符合要求
                if sale_amount > threshold:
                    for column_index in  range(worksheet.ncols):
                        cell_value = worksheet.cell_value(row_index,column_index)
                        cell_type = worksheet.cell_type(row_index,column_index)
                        if cell_type ==3:
                            date_cell = xldate_as_tuple(cell_value,workbook.datemode)
                            date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                            row_list.append(date_cell)
                        else:
                            row_list.append(cell_value)
                if row_list:
                    data.append(row_list)
    for list_index,output_list in enumerate(data):
        for element_index,element in enumerate(output_list):
            output_worksheet.write(list_index,element_index,element)
output_workbook.save(output_file)

  • pandas

```
import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

my_sheets = [0,1]
threshold = 1900.00

data_frame = pd.read_excel(input_file,sheet_name=my_sheets,index_col=None)
row_list = []

for worksheet_name, data in data_frame.items():
    row_list.append(data[data['Sale Amount'].astype(float) > threshold])

filtered_rows = pd.concat(row_list,axis=0,ignore_index=True)
writer = pd.ExcelWriter(output_file)
filtered_rows.to_excel(writer,sheet_name = 'all_line_group',index = False)
writer.save()    


### 處理多個工作簿(多個Excel文件)

這里就要從新引入`glob`模塊乖订，來獲得完整路徑。

#### 1. 工作表計數(shù)以及每個工作表中的行列計數(shù)

在有些時候我們不知道文件的內(nèi)容具练。與csv文件不同的是乍构，excel 文件可以包含多張工作表。所以 在處理之前獲取一些關(guān)于工作表的描述性信息非常重要扛点。比如：每個工作薄中的工作表數(shù)量哥遮。每張工作表中行列的數(shù)量。


    ```
    import glob,sys,os
    from xlrd import open_workbook
    
    input_directory = sys.argv[1]
    workbook_count = 0
    
    for input_file in glob.glob(os.path.join(input_directory,'*.xls*')):
        workbook = open_workbook(input_file)
        print('\n文件名 : %s' % os.path.basename(input_file))
        print('表的個數(shù): %d' % workbook.nsheets)
    
        for worksheet in workbook.sheets():
            print('表名:',worksheet.name,
                  '\t行數(shù):',worksheet.nrows,
                  '\t列數(shù):',worksheet.ncols)
        workbook_count +=1
    
    print('Number of Excel workbooks：%d' % workbook_count)
    
    ```
    [圖片上傳失敗...(image-d87a64-1551800142451)]

從多個工作簿中連接數(shù)據(jù)

  • 基礎(chǔ)Python
import sys,os,glob
from xlrd import open_workbook,xldate_as_tuple
from xlwt import Workbook
from datetime import date

input_folder = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('link_much_excel')
data = []
frist_worksheet = True
# 迭代文件夾中的文件
for input_file in glob.glob(os.path.join(input_folder,'*.xls*')):
    print(os.path.basename(input_file))

    with open_workbook(input_file) as workbook:
        for worksheet in workbook.sheets():
            if frist_worksheet:
                header_row = worksheet.row_values(0)
                data.append(header_row)
                frist_worksheet = False
            for row_index in range(1,worksheet.nrows):
                row_list = []
                for column_index in range(worksheet.ncols):
                    cell_value = worksheet.cell_value(row_index,column_index)
                    cell_type = worksheet.cell_type(row_index,column_index)
                    if cell_type==3:
                        date_cell = xldate_as_tuple(cell_value,workbook.datemode)
                        date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                        row_list.append(date_cell)
                    else:
                        row_list.append(cell_value)

                if row_list:
                    data.append(row_list)

for list_index,output_list in enumerate(data):
    for element_index,element in enumerate(output_list):
        output_worksheet.write(list_index,element_index,element)
output_workbook.save(output_file)

  • pandas

    關(guān)鍵函數(shù)：

    • concat() 函數(shù)來連接數(shù)據(jù)框
      • axis = 0 垂直堆疊
      • axis = 1 水平連接
    • merge()函數(shù) 可以提供類似SQL join 的操作
    import  pandas as pd
import sys,os,glob

input_folder = sys.argv[1]
output_file = sys.argv[2]

all_workbooks = glob.glob(os.path.join(input_folder,'*.xls*'))
data_frame = []
for workbook in all_workbooks:
    all_worksheets = pd.read_excel(workbook,sheetname=None,index_col=None)
    for worksheet_name, data in all_worksheets.items():
        data_frame.append(data)

all_data_concatenated = pd.concat(data_frame,axis=0,ignore_index=True)
writer = pd.ExcelWriter(output_file)
all_data_concatenated.to_excel(writer,sheet_name = 'all_group_all_workbooks',index = False)
writer.save()

為每個工作簿和工作表計算總數(shù)和均值

  • 基礎(chǔ)Python
import sys,glob,os
from datetime import date
from xlrd import open_workbook,xldate_as_tuple
from xlwt import Workbook

input_folder = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('sums_add_averages')
#輸出文件的所有行
all_data =[]
#Sale Amount 的索引值
sales_column_index = 3
#文件標(biāo)題列表
header = ['workbook',
          'worksheet',
          'worksheet_total',
          'worksheet_average',
           'workbook_total',
          'workbook_average']
all_data.append(header)

#遍歷文件中所有是 .xls* 的 文件
for input_file in  glob.glob(os.path.join(input_folder,'*.xls*')):
    #打開每次遍歷到的文件
    with open_workbook(input_file) as workbook:
        list_of_totals = [] #保存所有的銷售額
        list_of_number = [] #銷售額數(shù)據(jù)的個數(shù)
        workbook_output = [] #輸出文件的所有輸出列表
        #遍歷文件中的工作表
        for worksheet in workbook.sheets():
            total_sales = 0
            number_of_sales = 0
            worksheet_list = []  # 保存工作表的信息
            #添加工作簿名稱和工作表名稱
            worksheet_list.append(os.path.basename(input_file))
            worksheet_list.append(worksheet.name)
            #遍歷除標(biāo)題以外的行
            for row_index in range(1,worksheet.nrows):
                try:#算總值
                    total_sales += float(str(worksheet.cell_value \
                    (row_index, sales_column_index)) \
                    .strip('$').replace(',', ''))
                    number_of_sales += 1.
                except:
                    total_sales += 0
                    number_of_sales += 0
            #算均值
            average_sales = '%0.2f' % (total_sales / number_of_sales)
            # 添加銷售總計和均值
            worksheet_list.append(total_sales)
            worksheet_list.append(average_sales)
            # 將工作表的銷售額總計和銷售額數(shù)據(jù)個數(shù)加緊對應(yīng)列表
            list_of_totals.append(total_sales)
            list_of_number.append(float(average_sales))
            # 在工作簿級別保存信息
            workbook_output.append(worksheet_list)
            # 使用兩個列表計算出的工作簿的銷售額總計和銷售額均值
            workbook_total = sum(list_of_totals)
            workbook_average = sum(list_of_totals) / sum(list_of_number)

        #將工作簿級別的銷售額總計和均值追加到每個列表中
        for list_element in workbook_output:
            list_element.append(workbook_total)
            list_element.append(workbook_average)
        all_data.extend(workbook_output)

for list_index,output_list in enumerate(all_data):
    for element_index,element in enumerate(output_list):
        output_worksheet.write(list_index, element_index, element)

output_workbook.save(output_file)
  • pandas

pandas 可以直接在多個工作簿之間迭代陵究，并可以同時在工作簿級別和工作表級別計算統(tǒng)計量眠饮。計算工作簿級別的統(tǒng)計量，將它們轉(zhuǎn)換成一個數(shù)據(jù)框铜邮，然后通過基于工作簿名稱的左連接將兩個數(shù)據(jù)框合并在一起仪召，并將結(jié)果數(shù)據(jù)框添加到一個列表中。當(dāng)所有工作簿級別的數(shù)據(jù)框都進入列表之后松蒜，將這些數(shù)據(jù)框連接成一個獨立數(shù)據(jù)框扔茅，并寫入輸出文件.

import pandas as pd
import sys,os,glob

input_path = '/Users/yyf/PycharmProjects/ReadTest/DataSouce/Excel'
output_file = '/Users/yyf/PycharmProjects/ReadTest/DataSouce/total_average.xls'

all_workbooks = glob.glob(os.path.join(input_path,'*.xls*'))

data_frames = []
for workbook in all_workbooks:
    all_worksheets = pd.read_excel(workbook,sheetname=None,index_col=None)
    workbook_total_sales = []
    workbook_number_of_sales = []
    worksheet_data_frames = []
    worksheets_data_frame = None
    workbook_data_frame = None
    for worksheet_name,data in all_worksheets.items():
        total_sales= pd.DataFrame([float(str(value).strip('$').replace(',','')) for value in data.loc[:,'Sale Amount']]).sum()
        number_of_sales = len(data.loc[:,'Sale Amount'])
        average_sales = pd.DataFrame(total_sales/number_of_sales)
        workbook_total_sales.append(total_sales)
        workbook_number_of_sales.append(number_of_sales)
        data = {'workbook':os.path.basename(workbook),
                'worksheet':worksheet_name,
                'worksheet_total':total_sales,
                'worksheet_average':average_sales}

        worksheet_data_frames.append(pd.DataFrame(data,columns=['workbook','worksheet','worksheet_total','worksheet_average']))
    worksheets_data_frame = pd.concat(worksheet_data_frames,axis=0,ignore_index=True)
    workbook_total = pd.DataFrame(workbook_total_sales).sum()
    workbook_total_number_of_sales = pd.DataFrame(workbook_number_of_sales).sum()
    workbook_average = pd.DataFrame(workbook_total / workbook_total_number_of_sales)
    workbook_stats = {'workbook': os.path.basename(workbook),
                          'workbook_total': workbook_total,
                          'workbook_average': workbook_average}
    workbook_stats = pd.DataFrame(workbook_stats, columns=['workbook', 'workbook_total', 'workbook_average'])
    workbook_data_frame = pd.merge(worksheets_data_frame, workbook_stats, on='workbook', how='left')
    data_frames.append(workbook_data_frame)

all_data_concatenated = pd.concat(data_frames,axis=0,ignore_index=True)

writer = pd.ExcelWriter(output_file)
all_data_concatenated.to_excel(writer,sheet_name ='sums_and_averages',index = False )
writer.save()
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個濱河市秸苗，隨后出現(xiàn)的幾起案子召娜，更是在濱河造成了極大的恐慌，老刑警劉巖难述，帶你破解...
    沈念sama閱讀 211,639評論 6贊 492
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件萤晴，死亡現(xiàn)場離奇詭異，居然都是意外死亡胁后，警方通過查閱死者的電腦和手機店读，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,277評論 3贊 385
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來攀芯，“玉大人屯断，你說我怎么就攤上這事÷屡担” “怎么了殖演？”我有些...
    開封第一講書人閱讀 157,221評論 0贊 348
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長年鸳。 經(jīng)常有香客問我趴久，道長，這世上最難降的妖魔是什么搔确？ 我笑而不...
    開封第一講書人閱讀 56,474評論 1贊 283
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任彼棍，我火速辦了婚禮灭忠，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘座硕。我一直安慰自己弛作，他們只是感情好，可當(dāng)我...
    茶點故事閱讀 65,570評論 6贊 386
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布华匾。 她就那樣靜靜地躺著映琳，像睡著了一般。 火紅的嫁衣襯著肌膚如雪蜘拉。 梳的紋絲不亂的頭發(fā)上萨西，一...
    開封第一講書人閱讀 49,816評論 1贊 290
  • 城市分裂傳說
    那天，我揣著相機與錄音诸尽，去河邊找鬼原杂。 笑死，一個胖子當(dāng)著我的面吹牛您机，可吹牛的內(nèi)容都是我干的穿肄。 我是一名探鬼主播，決...
    沈念sama閱讀 38,957評論 3贊 408
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼际看，長吁一口氣：“原來是場噩夢啊……” “哼咸产！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起仲闽，我...
    開封第一講書人閱讀 37,718評論 0贊 266
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤脑溢，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后赖欣，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體屑彻，經(jīng)...
    沈念sama閱讀 44,176評論 1贊 303
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,511評論 2贊 327
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年顶吮，在試婚紗的時候發(fā)現(xiàn)自己被綠了社牲。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 38,646評論 1贊 340
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡悴了，死狀恐怖搏恤，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情湃交，我是刑警寧澤熟空，帶...
    沈念sama閱讀 34,322評論 4贊 330
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布，位于F島的核電站搞莺，受9級特大地震影響息罗，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜才沧，卻給世界環(huán)境...
    茶點故事閱讀 39,934評論 3贊 313
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一阱当、第九天 我趴在偏房一處隱蔽的房頂上張望俏扩。 院中可真熱鬧，春花似錦弊添、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,755評論 0贊 21
  • 一樁弒父案油坝，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至刨裆，卻和暖如春澈圈，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背帆啃。 一陣腳步聲響...
    開封第一講書人閱讀 31,987評論 1贊 266
  • 情欲美人皮
    我被黑心中介騙來泰國打工瞬女， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人努潘。 一個月前我還...
    沈念sama閱讀 46,358評論 2贊 360
  • 代替公主和親
    正文 我出身青樓诽偷，卻偏偏與公主長得像，于是被迫代替她去往敵國和親疯坤。 傳聞我的和親對象是個殘疾皇子报慕，可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 43,514評論 2贊 348

推薦閱讀更多精彩內(nèi)容