python EXCEL處理

IT策士 發表於 2021-09-20
Python Excel

python EXCEL處理

學習完本篇,你將會

  • 瞭解並掌握如何操作execl
  • 能大資料操作excel

EXCEL組成結構

  • EXCEL由工作薄、工作表、單元格三部分組成。
    工作薄:工作薄相當於Word的文件,一個工作薄檔案由若干個工作表組成。
    工作表:一張檔案表由若干大小相同的單元格組成。
    單元格:單元格是EXCEL的最基本單位,用於輸入檔案和資料內容
    圖1

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
  • 通過上述函式完成以下內容:
    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')