python EXCEL處理
學習完本篇，你將會
- 瞭解並掌握如何操作execl
- 能大資料操作excel
EXCEL組成結構
- EXCEL由工作薄、工作表、單元格三部分組成。
工作薄：工作薄相當於Word的文件，一個工作薄檔案由若干個工作表組成。
工作表：一張檔案表由若干大小相同的單元格組成。
單元格：單元格是EXCEL的最基本單位，用於輸入檔案和資料內容
Python操作EXCEL庫
- Python操作EXCEL庫有很多，常用的4中
1.Win32 COM:此方法只能在win平臺下操作EXCEL，跨平臺，如IOS，LINUX系統
下不支援。
2.xlrd:讀EXCEL表,讀寫速度快，但目前新版的excel操作有問題
3.xlwt：寫EXCEL表，操作速度快，但對新版的excel操作有問題
4.Openpyxl:可讀寫EXCEL表，專門處理EXCEL2007及以上版本產生的xlsx檔案，
可以轉換xls和xlsx之間。 - 注意事項：Excel檔案是gbk編碼，讀入時先decode(‘gbk’).encode為需要的編碼
Openpyxl下載安裝
- Openpyxl下載路徑： https://pypi.python.org/pypi/openpyxl
- 安裝檔案方式有兩種，一種是下載安裝包離線安裝，另一種是線上安裝。
1.離線安裝：下載安裝包openpyxl.tar.gz,如果在linux下，需要通過
“tar –xzvy openpyxl.tar.gz”命令解壓，進入目錄，找到setup.py,執行：python setup.py install
2.線上安裝：如果條件可以，可以通過pip命令安裝，如果pip沒有配置環境變數，需要進入pip目錄。如果是win下直接開啟cmd，輸入cd /d D:\Python27\Scripts對應python27中Scripts目錄。輸入3.pip install openpyxl命令，即可安裝。以及配置該目錄環境變數可直接輸入pip install openpyxl命令。如果發現pip命令不能識，可以輸入pip.exe install openpyxl命令。 - 如果想在EXCEL中處理圖片，還需要安裝pillow（PIL）模組，pip install pillow
Openpyxl資料型別
-
openpyxl定義多種資料型別，其中最重要的三種:
1.NULL空值：對應python中的None，表示這個cell裡沒有資料。
2.number：數字型，統一按照浮點數來進行處理。對應於python中的float。
3.string：字串型，對應於python中的unicode。 -
openpyxl處理excel檔案三個物件：
1.Workbook：工作簿，一個excel檔案包含多個sheet。
2.sheet：工作表，一個workbook有多個，表名識別，如“sheet1”,“sheet2”等。
3.cell： 單元格，儲存資料物件
匯入openpyxl檔案庫
from openpyxl import Workbook #匯入workbook類
from openpyxl import load_workbook #匯入load_workbook類
#匯入PatternFill, Border, Side, Alignment, Protection, Font, Color, Fill 這些類
#這些類處理單元格填充樣式
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, Color, Fill
from openpyxl.styles import colors
from openpyxl.styles import Fill,fills from openpyxl.formatting.rule import ColorScaleRule
openpyxl基本操作
- 開啟workbook：wb =workbook(‘file_name.xlsx’)
- 儲存workbook:wb.save (‘file_name.xlsx’)
- 開啟sheet：
開啟excel有多種方法
1.通過名稱：ws=wb[“sheet1”]
2.通過名稱：ws=wb.get_sheet_by_name(“sheet1”)
3.通過名字集合來查詢：sheet_names = wb.get_sheet_names()
ws = wb.get_sheet_by_name(sheet_names [index])
4.通過當前活動sheet：ws=wb.active
5.通過當前活動sheet：ws=wb.get_active_sheet() #預設為通過index為0的表
- 建立sheet
建立sheet方法有兩種，一種是通過預設的方式，再更改表名，另一種是通過直接建立表名稱建立檔案。
1.ws=wb.create_sheet()#預設插入到最後
ws.title=“New Title” #修改表明
2.ws=wb.create_sheet(0)#預設插入到開始
ws.title=“New Title” #修改表明
3.Ws=wb.create_sheet(title=“Pi”)
單元格讀寫
- 讀單元格內容：
讀單元格內容有多種方式：通過‘A4’方式，也可以通過row，column方式
通過‘A4’方式：c = ws[‘A4’].value也可以等同於c = ws.cell(‘A4’)
通過row，column方式：c=ws.cell(row=4,column=2)
- 寫單元格內容：
寫單元格內容跟讀單元格類似，同樣有兩種方式
通過‘A4’方式：ws[‘A4’] = 4
通過row，column方式:ws.cell(row=4,column=2).value=‘test’
ws.cell(row = 4,column = 2,value = ‘test’)
- 訪問多個單元格:
可通過cell_range = ws[‘A1’,’C2’] 獲取值cell_range[i][j]
- 訪問所有的單元格:
可通過get_cell_collection()
獲取單元格行數： ws.max_row
獲取單元格列數： ws.max_column
Openpyxl例子
- 通過上述函式完成以下內容：
1.讀取表中的行數
2.讀取表中的列數
3.讀取表中的所有資訊
Openpyxl簡單封裝
- 封裝一個讀寫excel類，方便以後呼叫
封裝步驟：
1.設定檔案編碼格式
2.編寫檔案說明註釋
3.匯入需要的庫檔案
4.建立一個operatexecl類名
5.編寫類的說明註釋
6.建立需要的類屬性
7.建立構造器__init__(),可在構造器中開啟檔案，
8.建立開啟檔案方法(註釋包含，方法功能，輸入引數，輸出引數，建立時間，建立人，方法描述)
9.建立一個儲存檔案方法
10建立修改單元格方法
11.建立獲取單元格方法
# -*- coding: gbk-*-
# author:IT策士
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, Color, Fill
from openpyxl.styles import colors
from openpyxl.styles import Fill,fills
from openpyxl.styles.colors import *
import os
class Excel:
workbook = ''
filepath = ''
def __init__(self,path):
self.filepath = path
if os.path.isfile(path) is not True:
os.mkdir(path)
self.workbook = load_workbook(path)
def add_date(self,date,sheetName,row,column,bgcolor = YELLOW,fontcolor = RED):
if self.workbook == '':
return False
try:
self.sheet=self.workbook.get_sheet_by_name(sheetName)
except BaseException,msg:
print u"表格:%s" %sheetName
return False
try:
self.sheet.cell(row=row, column=column, value=date)
self.sheet.cell(row=row, column=column).fill = PatternFill(start_color=Color(rgb=bgcolor),
end_color=Color(rgb=bgcolor), fill_type='solid')
#self.sheet.cell(row=row, column=column).font = Font(color=Color(rgb=fontcolor),name="Calibri", sz=27, family=2, b=False, i=False, scheme="minor")
self.sheet.cell(row=row, column=column).font = Font(color=Color(rgb=fontcolor))
self.workbook.save(self.filepath)
except BaseException,msg:
print u"дÈëÊý¾Ýʧ°Ü"
return False
def get_date(self,sheetName,row,column):
if self.workbook == '':
return False
try:
print sheetName
self.sheet = self.workbook.get_sheet_by_name(sheetName)
except BaseException, msg:
print u"ÎÞЧµÄsheetÃû³Æ:%s" % sheetName
return False
try:
getvalue = self.sheet.cell(row=row, column=column).value
except BaseException,msg:
print u"¶ÁÊý¾Ýʧ°Ü"
return False
return getvalue
def search(self,date,sheetName):
list_result= []
if self.workbook == '':
return False
try:
self.sheet = self.workbook.get_sheet_by_name(sheetName)
except BaseException, msg:
print u"ÎÞЧµÄsheetÃû³Æ:%s" % sheetName
return False
try:
maxrow = self.sheet.max_row
maxcolumn = self.sheet.max_column
for i in range(1,maxrow+1):
for j in range(1,maxcolumn+1):
try:
index = self.sheet.cell(row=i,column=j).value.find(date)
except BaseException,msg:
continue
if index>=0:
list_result.append([i, j])
except BaseException,msg:
print u"»ñÈ¡ÐС¢ÁÐÊýʧ°Ü"
return False
return list_result
def close_excel(self):
try:
self.workbook.close()
except BaseException,msg:
print u"¹Ø±ÕÎļþʧ°Ü"
return False
return True
if __name__ == '__main__':
excel = Excel('demo2.xlsx')
excel.add_date(u'tessdfsft',u'Sheet1',1,1)
print excel.get_date(u'Sheet1',1,1)
print excel.search('test',u'Sheet1')