最全總結 | 聊聊 Python 辦公自動化之 Excel（下）

AirPython發表於2020-11-11
PythonExcel

image

1. 前言

前面談到 Python 處理 Excel 檔案最常見的兩種方式，即：xlrd/xlwt、openpyxl

​其中，

xlrd/xlwt 這一組合，xlrd 可以負責讀取資料，而 xlwt 則負責寫入資料，缺點是不支援 xlsx

openpyxl 同時支援對 Excel 文件的讀取、寫入操作，缺點是不支援 xls

本篇文章將繼續聊聊 Python 操作 Excel 文件的其他幾種方式

2. xlsxwriter

xlsxwriter 主要用於將資料、圖表寫入到 Excel 檔案中，可以配置使用較小的記憶體快速寫入資料

它的缺點是：無法讀取、修改已有的 Excel 檔案；如果需要讀取修改 Excel 檔案，只能搭配其他依賴庫使用，比如：xlrd

首先安裝 xlsxwriter 的依賴包

# 安裝依賴包
pip3 install xlsxwriter

xlsxwriter 提供了 Workbook(filename) 方法，用於建立一個工作簿物件

使用工作簿物件的 add_worksheet(sheet_name) 函式，就可以在工作簿中建立 Sheet 了

def create_workbook_and_worksheet(filename, worksheet_names):
    """
    建立工作簿和Sheet
    :param filename: 檔名稱
    :param worksheet_names: sheet名稱列表
    :return:
    """
    wb = xlsxwriter.Workbook(filename)

    sheets = []

    # 新增sheet
    for worksheet_name in worksheet_names:
        sheets.append(wb.add_worksheet(worksheet_name))

    return wb, sheets

接著，就可以往某個 Sheet 單元格中寫入資料了

如果需要定製單元格的樣式，比如：字型大小、字型、顏色、背景、是否加粗等，可以使用工作簿物件的 add_format() 方法建立一個樣式

def create_format_styles(wb, format_stuyles):
    """
    建立一個樣式，包含：字型大小、字型、顏色、背景、是否加粗等
    :param wb:
    :param format_stuyles:
    :return:
    """
    return wb.add_format(format_stuyles)

# 單元格字型樣式
self.title_style = {'bold': True, 'bg_color': '#B0C4DE', 'font_size': 10,'font_name': 'Microsoft yahei'}

# 建立標題字型樣式
title_font_style = create_format_styles(self.wb, self.title_style)

Sheet 物件的 write(...) 函式用於向單元格中寫入資料，引數包含：行索引、列索引、值、字型樣式等

需要注意的是，預設 xlsxwriter 的行索引、列索引都是從 0 開始，即： 0 代表第一行

寫入資料的同時配置單元格樣式的寫法如下：

def write_to_cell(sheet, row_index, column_index, value, format_styles=None):
    """
    往單元格中寫入資料
    :param row_index: 行索引，1：第一行
    :param column_index: 列索引，1：第一列
    :param format_styles 字型樣式
    :return:
    """
    if row_index < 1 or column_index < 1:
        print('引數輸入不正確，寫入失敗！')
    else:
        # 注意：預設xlsxwriter的行索引、列索引從0開始
        sheet.write(row_index - 1, column_index - 1, value, format_styles)

# 往worksheet中寫入資料
# 第一行
write_to_cell(self.current_sheet, 1, 1, "姓名", title_font_style)
write_to_cell(self.current_sheet, 1, 2, "年齡", title_font_style)
# 第二行
write_to_cell(self.current_sheet, 2, 1, 'xingag')
write_to_cell(self.current_sheet, 2, 2, 23)

xlsxwriter 同樣支援在單元格中插入圖片，包含：本地圖片和網路圖片

使用的方法是：insert_image()

引數包含：單元格行索引（索引從 0 開始）、單元格列索引、圖片檔案、可選引數（圖片位置、縮放、url 超連結、image_data 圖片位元組流等）

以插入一張網路圖片為例

首先，定義一個圖片展示可選引數，指定圖片的縮放比、url 超連結

def create_image_options(x_offset=0, y_offset=0, x_scale=1, y_scale=1, url=None, tip=None, image_data=None,
                         positioning=None):
    """
    插入圖片的引數配置
    包含：偏移量、縮放比、網路圖片連結、超連結、懸停提示燈
    :param x_offset:
    :param y_offset:
    :param x_scale:
    :param y_scale:
    :param url:
    :param tip:
    :param image_data:
    :param positioning:
    :return:
    """
    image_options = {
        'x_offset': x_offset,
        'y_offset': y_offset,
        'x_scale': x_scale,
        'y_scale': y_scale,
        'url': url,
        'tip': tip,
        'image_data': image_data,
        'positioning': positioning,
    }
    return image_options

image_options = create_image_options(x_scale=0.5, y_scale=0.5, url='https://www.jianshu.com/u/f3b476549169')

接著，將網路圖片轉為位元組流

from io import BytesIO
import ssl

def get_image_data_from_network(url):
    """
    獲取網路圖片位元組流
    :param url: 圖片地址
    :return:
    """
    ssl._create_default_https_context = ssl._create_unverified_context
    # 獲取網路圖片的位元組流
    image_data = BytesIO(urlopen(url).read())
    return image_data

最後，將圖片插入到單元格中

def insert_network_image(sheet, row_index, column_index, url, filepath, image_options=None):
    """
    插入網路圖片
    :param sheet:
    :param row_index:
    :param column_index:
    :param url:
    :param filepath:
    :param image_options:
    :return:
    """
    if row_index < 1 or column_index < 1:
        return "引數輸入有誤，插入失敗！"

    # 獲取圖片位元組流
    image_data = get_image_data_from_network(url)

    if image_options:
        image_options['image_data'] = image_data
    print(image_options)

    sheet.insert_image(row_index - 1, column_index - 1, filepath, image_options)

insert_network_image(self.current_sheet, 1, 1, url, '1.png', image_options4)

使用 set_column() 方法可以設定列寬

和 openpyxl 類似，有 2 種使用方式，分別是：字串索引、列索引數字索引

def set_column_width(sheet, index_start, index_end, width):
    """
    設定列寬
    :param sheet:
    :param index_start: 開始位置，從1開始
    :param index_end: 結束位置
    :param width: 寬度
    :return:
    """
    # 方式二選一
    # self.current_sheet.set_column('A:C', width)

    # 預設0代表第一列
    sheet.set_column(index_start - 1, index_end - 1, width)

# 設定列寬度
# 設定第1列到第3列的寬度為：100
set_column_width(self.current_sheet, 1, 3, 100)

行高使用 set_row() 方法，傳入行索引和高度即可

def set_row_height(sheet, row_index, height):
    """
    設定行高
    :param sheet:
    :param row_index: 行索引，從1開始
    :param height:
    :return:
    """
    sheet.set_row(row_index - 1, height)

# 設定行高
set_row_height(self.current_sheet, 1, 50)
set_row_height(self.current_sheet, 2, 100)

寫入資料完畢之後，將工作簿關閉，檔案會自動儲存到本地

def teardown(self):
    # 寫入檔案，並關閉檔案
    self.wb.close()

xlsxwriter 還支援插入圖表，比如：條形圖、柱狀圖、雷達圖等，受限於篇幅，這部分內容就不展開說明了

3. 其他方式

還有一種比較常見的方式是：xlwings

xlwings 是一款開源免費的依賴庫，同時支援 Excel 檔案的讀取、寫入、修改

它功能非常強大，還可以和 Matplotlib、Numpy 和 Pandas 無縫連線，支援讀寫 Numpy、Pandas 資料型別；同時，xlwings 可以直接呼叫 Excel 檔案中 VBA 程式

需要注意的是，xlwings 依賴於 Microsoft Excel 軟體，所以使用 WPS 的使用者建議直接使用 openpyxl

官方文件：

https://docs.xlwings.org/zh_CN/latest/quickstart.html

另外，還有一個操作 Excel 比較強大的方式，即：Pywin32

其中，

Pywin32 相當於呼叫 Win 下的系統 API 來操作 Excel 檔案

優點是：可以處理複雜圖表的資料表

缺點也非常明顯，包含：速度慢、佔用 CPU 高，僅支援 Win 系統

4. 最後

綜合發現，xlrd/xlwt、openpyxl、xlsxwriter 基本上可以滿足大部分的日常 Excel 文件操作

要獲取全部原始碼，關注公眾號「 AirPython 」，後臺回覆「 excel 」即可獲得全部原始碼

如果你覺得文章還不錯，請大家 點贊、分享、留言下，因為這將是我持續輸出更多優質文章的最強動力！

推薦閱讀

最全總結 | 聊聊 Python 辦公自動化之 Excel（上）

最全總結 | 聊聊 Python 辦公自動化之 Excel（中）

相關文章