python處理excel已經(jīng)有大量包，主流代表有：

·xlwings：簡單強大，可替代VBA

·openpyxl：簡單易用，功能廣泛

·pandas：使用需要結(jié)合其他庫，數(shù)據(jù)處理是pandas立身之本

·win32com：不僅僅是excel，可以處理office;不過它相當于是 windows COM 的封裝，新手使用起來略有些痛苦。

·Xlsxwriter：豐富多樣的特性，缺點是不能打開/修改已有文件，意味著使用 xlsxwriter 需要從零開始。

·DataNitro：作為插件內(nèi)嵌到excel中，可替代VBA，在excel中優(yōu)雅的使用python

·xlutils：結(jié)合xlrd/xlwt，老牌python包，需要注意的是你必須同時安裝這三個庫

 

 

 

openpyxl的使用

openpyxl（可讀寫excel表）專門處理Excel2007及以上版本產(chǎn)生的xlsx文件，xls和xlsx之間轉(zhuǎn)換容易

注意：如果文字編碼是“gb2312” 讀取后就會顯示亂碼，請先轉(zhuǎn)成Unicode

 

openpyxl定義多種數(shù)據(jù)格式

最重要的三種：

NULL空值：對應(yīng)于python中的None，表示這個cell里面沒有數(shù)據(jù)。

numberic： 數(shù)字型，統(tǒng)一按照浮點數(shù)來進行處理。對應(yīng)于python中的float。

string： 字符串型，對應(yīng)于python中的unicode。

Excel文件三個對象

workbook： 工作簿，一個excel文件包含多個sheet。

sheet：工作表，一個workbook有多個，表名識別，如“sheet1”,“sheet2”等。

cell： 單元格，存儲數(shù)據(jù)對象

1創(chuàng)建一個workbook（工作簿）

wb = Workbook()  # 一個工作簿(workbook)在創(chuàng)建的時候同時至少也新建了一張工作表(worksheet)。

 

2 打開一個已有的workbook：

 wb = load_workbook('file_name.xlsx')

3 打開sheet：

通過名字

    ws = wb["frequency"] 或ws2 = wb.get_sheet_by_name('frequency')

不知道名字用index

    sheet_names = wb.get_sheet_names()  #方法得到工作簿的所有工作表

    ws = wb.get_sheet_by_name(sheet_names[index])# index為0為第一張表 

或者（調(diào)用得到正在運行的工作表）

    ws =wb.active或ws = wb.get_active_sheet() #通過_active_sheet_index設(shè)定讀取的表，默認0讀第一個表

    活動表表名wb.get_active_sheet().title

4 新建sheet（工作表）

ws1 = wb.create_sheet() #默認插在最后

ws2 = wb.create_sheet(0) #插在開頭 ，在創(chuàng)建工作表的時候系統(tǒng)自動命名，依次為Sheet, Sheet1, Sheet2 ...

ws.title = "New Title" #修改表名稱

簡化 ws2 = wb.create_sheet(title="Pi")

5 讀寫單元格

當一個工作表被創(chuàng)建時，其中是不包含單元格。只有當單元格被獲取時才被創(chuàng)建。這種方式下，我們不會創(chuàng)建我們使用不到的單元格，從而減少了內(nèi)存消耗。

 

可以直接根據(jù)單元格的索引直接獲得

c = ws['A4']     #讀取單元格，如果不存在將在A4新建一個

可以通過cell()方法獲取單元格(行號列號從1開始)

d = ws.cell(row = 4, column = 2) #通過行列讀

d = ws.cell('A4')

寫入單元格（cell）值

ws['A4'] = 4      #寫單元格 

ws.cell(row = 4, column = 2).value = 'test'

ws.cell(row = 4, column = 2, value = 'test')

6 訪問多個單元格

cell_range = ws['A1':'C2']    #使用切片獲取多個單元格

get_cell_collection()     #讀所有單元格數(shù)據(jù)

7 按行、按列操作

逐行讀

 ws.iter_rows(range_string=None, row_offset=0, column_offset=0) #返回一個生成器, 獲得多個單元格

 例如：

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

      for cell in row:

          print cell

迭代文件中所有的行或者列:

ws.rows         #迭代讀取行row 

ws.columns      #迭代讀取列column

直接讀取行列數(shù)據(jù) 

print rows[n]      #顯示第n行數(shù)據(jù) 

print columns[n]   #顯示第n列數(shù)據(jù)

逐行寫，添加一行到當前sheet的最底部。 

1,如果是list,將list從頭到尾順序添加。 2，如果是dict,按照相應(yīng)的鍵添加相應(yīng)的鍵值。

 append([‘This is A1’, ‘This is B1’, ‘This is C1’])

 append({‘A’ : ‘This is A1’, ‘C’ : ‘This is C1’})

 append({1 : ‘This is A1’, 3 : ‘This is C1’})

通過公式計算產(chǎn)生寫入的值

ws["A1"] = "=SUM(1, 1)"

ws["A1"] = "=SUM(B1:C1)"

8 顯示有多少張sheet表

wb.get_sheet_names()  

#顯示表名，表行數(shù)，表列數(shù)   

print ws.title  

print ws.max_row

print ws.max_column

9 獲得列號的字母

from openpyxl.utils import get_column_letter

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

    col = get_column_letter(x)    # 默認x從1開始

    ws.cell( '%s%s' %(col, i)).value = x

通過列字母獲取多個excel數(shù)據(jù)塊

cell_range = "E3:{0}28".format(get_column_letter(bc_col))

ws["A1"] = "=SUM(%s)"%cell_range

10 excel文件是gbk編碼，讀入時需要先編碼為gbk，再解碼為unicode，再編碼為utf8

cell_value.encode('gbk').decode('gbk').encode('utf8')  

11保存到文件

wb = Workbook()

wb.save('balances.xlsx')

save（）會在不提示的情況下用現(xiàn)在寫的內(nèi)容，覆蓋掉原文件中的所有內(nèi)容

寫入例子一

from openpyxl import Workbook

 

wb = Workbook()

# 激活 worksheet

ws = wb.active

# 數(shù)據(jù)可以直接分配到單元格中

ws['A1'] = 42

# 可以附加行，從第一列開始附加

ws.append([1, 2, 3])

# Python 類型會被自動轉(zhuǎn)換

import datetime

 

ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")

# 保存文件

wb.save("sample.xlsx")

寫入例子二

# workbook相關(guān)

from openpyxl import Workbook

from openpyxl.compat import range

from openpyxl.utils import get_column_letter

 

wb = Workbook()

 

dest_filename = 'empty_book.xlsx'

 

ws1 = wb.active

ws1.title = "range names"

 

for row in range(1, 40):

    ws1.append(range(600))

 

ws2 = wb.create_sheet(title="Pi")

 

ws2['F5'] = 3.14

 

ws3 = wb.create_sheet(title="Data")

for row in range(10, 20):

    for col in range(27, 54):

        _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))

print(ws3['AA10'].value)

wb.save(filename=dest_filename)

讀取例子一

from openpyxl.reader.excel import load_workbook
import json

# 讀取excel2007文件
wb = load_workbook(filename=r'test_book.xlsx')

# 顯示有多少張表
print "Worksheet range(s):", wb.get_named_ranges()
print "Worksheet name(s):", wb.get_sheet_names()

# 取第一張表
sheetnames = wb.get_sheet_names()
ws = wb.get_sheet_by_name(sheetnames[0])

# 顯示表名，表行數(shù)，表列數(shù)
print "Work Sheet Titile:", ws.title
print "Work Sheet Rows:", ws.max_row
print "Work Sheet Cols:", ws.max_column


# 建立存儲數(shù)據(jù)的字典
data_dic = {}

# 把數(shù)據(jù)存到字典中
for rx in range(1, ws.max_row + 1):
    temp_list = []
    pid = rx
    w1 = ws.cell(row=rx, column=1).value
    w2 = ws.cell(row=rx, column=2).value
    w3 = ws.cell(row=rx, column=3).value
    w4 = ws.cell(row=rx, column=4).value
    temp_list = [w1, w2, w3, w4]

    data_dic[pid] = temp_list

# 打印字典數(shù)據(jù)個數(shù)
print 'Total:%d' % len(data_dic)
print json.dumps(data_dic, encoding="UTF-8", ensure_ascii=False)

 讀取結(jié)果：

Worksheet range(s): []

Worksheet name(s): [u'\u6d3b\u52a8\u8868', u'\u7528\u6237\u4fe1\u606f', u'Sheet3']

Work Sheet Titile: 活動表

Work Sheet Rows: 3

Work Sheet Cols: 5

Total:3

{"1": ["張三", 18, "男", "廣州"], "2": ["李四", 20, "女", "湖北"], "3": ["王五", 25, "女", "北京"]}

實例

from openpyxl import Workbook

from openpyxl.compat import range

from openpyxl.cell import get_column_letter

dest_filename = 'empty_book.xlsx'

wb = Workbook()

ws1 = wb.active

ws1.title = "range names"

for row in range(1, 40):

   ws1.append(range(600))

ws3 = wb.create_sheet(title="Data")

for row in range(10, 20):

   for col in range(27, 54):

       _ = ws3.cell(column=col, row=row, value="%s" % get_column_letter(col))

print(ws3['AA10'].value)

wb.save(filename = dest_filename)

sheet_ranges = wb['range names']

print(sheet_ranges['D18'].value)

ws['A1'] = datetime.datetime(2010, 7, 21)

ws['A1'].number_format #輸出'yyyy-mm-dd h:mm:ss'

rows = [

    ['Number', 'Batch 1', 'Batch 2'],

    [2, 40, 30],

    [3, 40, 25],

    [4, 50, 30],

    [5, 30, 10],

    [6, 25, 5],

    [7, 50, 10],

]

rows = [

    ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],

    [date(2015,9, 1), 40, 30, 25],

    [date(2015,9, 2), 40, 25, 30],

    [date(2015,9, 3), 50, 30, 45],

    [date(2015,9, 4), 30, 25, 40],

    [date(2015,9, 5), 25, 35, 30],

    [date(2015,9, 6), 20, 40, 35],

]

for row in rows:

    ws.append(row)

 

excel中圖片的處理，PIL模塊

    try:

        from openpyxl.drawing import image

        import PIL            

    except ImportError, e:

        print "[ERROR]",e

    report_file = self.excel_path + "/frquency_report_%d.xlsx" %id

    shutil.copyfile(configs.PATTEN_FILE, report_file)

    if not os.path.exists(report_file):

       print "generate file failed: ", report_file

       sys.exit(1)

    wb = load_workbook(report_file)

    ws = wb.get_sheet_by_name('frequency')

    img_f = configs.IMAGE_LOGO

    if os.path.exists(img_f):

        try:

            img = image.Image(img_f)

            ws.add_image(img, 'A1')

        except Exception, e:

            print "[ERROR]%s:%s" % (type(e), e)

            ws['A1'] = "程序化營銷平臺"

        else:

            ws['A1'] = "程序化營銷平臺"

        font1 = Font(size=22)

        ws['A1'].font = font1

        ws['B4'] = ad_plan #等同ws.cell('B4') = ad_plan

        ws['B5'] = ad_names

        ws['B6'] = str(start_d) + '  to  ' + str(end_d)

        wb.save(report_file)

    try:

        wb = load_workbook(report_file)

        ws = wb.get_sheet_by_name('frequency')            

        row = 9

        for it in query_result:

            one_row = it.split('\t')

            print one_row

            if '10' == one_row[0]:

                one_row[0] = '10+'

            col = 1

            for one_cell in one_row:

                ws.cell(row = row, column = col).value = one_cell

                col = col + 1

            row = row + 1      

    except Thrift.TException, tx:

        print '[ERROR] %s' % (tx.message)

    else:

        wb.save(report_file)

    finally:

        pass