自動(dòng)化Excel進(jìn)階技能之一：添加圖片

盡管Excel電子表格中并不經(jīng)常都是插入圖片的，但是添加了圖片以及圖片里插入里url鏈接，這還是相當(dāng)實(shí)用的。我們可以用它來做品牌宣傳，或者讓Excel表格更加個(gè)性化。

怎么做？

為了能夠使用openpyxl向電子表格加載圖片，我們必須安裝Pillow。

除此之外，我們還需要準(zhǔn)備一張要插入的圖片。

我們可以在百度圖片搜索pandas，然后在Chrome瀏覽器下面用“圖片助手”的插件抓取png格式的pandas徽標(biāo)圖片，將最終的文件保存為 logo.png，并將這個(gè)圖片復(fù)制到你運(yùn)行示例的根目錄下。

這是你需要將該圖片導(dǎo)入到hello_word.xlsx電子表格中的代碼：

from openpyxl import load_workbookfrom openpyxl.drawing.image import Image#?我們用一個(gè)數(shù)據(jù)很小的hello_world文件workbook = load_workbook(filename="hello_world.xlsx")sheet = workbook.activelogo = Image("logo.png")# 調(diào)整圖片大小，免得全屏都是圖片logo.height = 150logo.width = 150sheet.add_image(logo, "A3")workbook.save(filename="hello_world_logo.xlsx")

自動(dòng)化Excel進(jìn)階技能之二：創(chuàng)建漂亮的圖表

Excel之所以強(qiáng)大，是因?yàn)槟軌蚶L制各種圖表。

Excel圖表是一種快速可視化，解釋數(shù)據(jù)的好方法。

Excel內(nèi)有很多不同的圖表類型：柱狀圖、餅狀圖、線狀圖等等。

openpyxl支持所有的Excel圖表。

在這里，我列舉幾個(gè)怎么畫圖表的例子，其余圖表類型都是一樣，照模畫虎就行。

請(qǐng)注意：openpyxl目前不支持的幾種圖表類型有：Funnel, Gantt, Pareto, Treemap, Waterfall, Map和Sunburst。

對(duì)于任何我們想構(gòu)建的圖表，我們都需要定義圖表類型。BarChart, LineChart等，再加上用于圖表的數(shù)據(jù)。

在構(gòu)建圖表之前，我們需要定義我們想在圖表中看到什么數(shù)據(jù)的表示。有時(shí)，我們可以按原樣使用數(shù)據(jù)集，但其他時(shí)候，你需要對(duì)數(shù)據(jù)進(jìn)行一些清洗規(guī)整以獲得原始數(shù)據(jù)之外的信息。

讓我們先用一些樣本數(shù)據(jù)建立一個(gè)新的工作簿：

from openpyxl import Workbookfrom openpyxl.chart import BarChart, Referenceworkbook = Workbook()sheet = workbook.active# 創(chuàng)建一些虛擬銷售數(shù)據(jù)rows = [ ["Product", "Online", "Store"], [1, 30, 45], [2, 40, 30], [3, 40, 25], [4, 50, 30], [5, 30, 25], [6, 25, 35], [7, 20, 40],]for row in rows: sheet.append(row)

現(xiàn)在我們要開始創(chuàng)建一個(gè)顯示每個(gè)產(chǎn)品銷售總數(shù)的條形圖：

chart = BarChart()data = Reference(worksheet=sheet, min_row=1, max_row=8, min_col=2, max_col=3)chart.add_data(data, titles_from_data=True)sheet.add_chart(chart, "E2")workbook.save("chart.xlsx")

下面你可以看到一個(gè)非常直接的柱狀圖，顯示電商平臺(tái)產(chǎn)品銷售和線下門店產(chǎn)品銷售的區(qū)別：

如圖所示，圖表的左上角是原始數(shù)據(jù)。在我們的案例中，它是在單元格E2上。

我們?cè)俳又鴩L試創(chuàng)建一個(gè)折線圖，先改變一下數(shù)據(jù)：

import randomfrom openpyxl import Workbookfrom openpyxl.chart import LineChart, Referenceworkbook = Workbook()sheet = workbook.active#?創(chuàng)建一些虛擬銷售數(shù)據(jù)樣本rows = [ ["", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"], [1, ], [2, ], [3, ],]for row in rows: sheet.append(row)for row in sheet.iter_rows(min_row=2, max_row=4, min_col=2, max_col=13): for cell in row: cell.value = random.randrange(5, 100)

通過上面的代碼，我們將生成一些隨機(jī)數(shù)據(jù)，關(guān)于3種不同產(chǎn)品在一整年的銷售情況。

一旦完成，我們可以很容易地用下面的代碼創(chuàng)建一個(gè)折線圖。

chart = LineChart()data = Reference(worksheet=sheet, min_row=2, max_row=4, min_col=1, max_col=13)chart.add_data(data, from_rows=True, titles_from_data=True)sheet.add_chart(chart, "C6")workbook.save("line_chart.xlsx")

這是上面這段代碼的結(jié)果：

這里需要注意的是，我們?cè)谔砑訑?shù)據(jù)時(shí)，參數(shù)使用from_rows=True。這個(gè)參數(shù)讓圖表是逐行而不是逐列進(jìn)行繪制。

在示例數(shù)據(jù)中，我們看到每個(gè)產(chǎn)品都有12個(gè)數(shù)據(jù)行(每個(gè)月1列)。這就是為什么要使用from_rows的原因。

如果我們不傳遞這個(gè)參數(shù)，默認(rèn)情況下，圖表是按列繪制，我們會(huì)得到一個(gè)逐月的銷售比較。

另一個(gè)與上述參數(shù)變化有關(guān)的區(qū)別是，我們的Reference現(xiàn)在從第一列開始，min_col=1，而不是第二列。

這個(gè)變化是需要的，因?yàn)閳D表默認(rèn)第一列有標(biāo)題。

關(guān)于圖表的樣式，還有一些其他的參數(shù)我們可以改變。

例如，可以在圖表中添加特定的類別。

cats = Reference(worksheet=sheet, min_row=1, max_row=1, min_col=2, max_col=13)chart.set_categories(cats)

在保存工作簿之前添加這段代碼，我們應(yīng)該會(huì)看到月名而不是數(shù)字出現(xiàn)

從代碼上來說，這是一個(gè)最小的變化。但就電子表格的可讀性而言，這讓讀者更容易打開電子表格并直接理解圖表。

另一個(gè)可以提高圖表可讀性的方法是添加一個(gè)軸。

我們可以使用屬性x_axis和y_axis來實(shí)現(xiàn)。

chart.x_axis.title = "Months"chart.y_axis.title = "Sales (per unit)"

這將生成一個(gè)像下面這樣的電子表格：

正如上圖所示，這樣的小變化使圖表更容易讀和理解。

還有一種方法是通過使用Excel的默認(rèn)ChartStyle屬性來設(shè)置圖表的樣式。

在這種情況下，我們需要在1和48之間選擇一個(gè)數(shù)字。根據(jù)我們的選擇，我們的圖表顏色會(huì)發(fā)生改變。

#?我們可以選擇顏色，從1到48里面填一個(gè)數(shù)字即可chart.style = 24

在上面選擇的風(fēng)格下，所有的線條都有一些橙色的陰影。

沒有明確的文檔說明每個(gè)樣式會(huì)是什么樣的，但這個(gè)案例有幾個(gè)可用樣式。

這里有更多的圖表類型和自定義可以應(yīng)用，所以如果我們需要一些特定的格式，我們需要查閱這方面的文檔包。

自動(dòng)化Excel進(jìn)階技能之三：將Python數(shù)據(jù)存儲(chǔ)到Excel電子表格

我們演示里如何將Excel電子表格的數(shù)據(jù)轉(zhuǎn)換為Python類，但現(xiàn)在讓我們反過來做。

讓我們想象一下，如果我們有一個(gè)數(shù)據(jù)庫，并且正在使用一些對(duì)象關(guān)系映射 (ORM) 將 DB 對(duì)象映射到 Python 類中。現(xiàn)在，我們想把這些相同的對(duì)象導(dǎo)出到Excel中。

讓我們假設(shè)以下數(shù)據(jù)類來表示來自數(shù)據(jù)庫的有關(guān)產(chǎn)品銷售的數(shù)據(jù)：

from dataclasses import dataclassfrom typing import List@dataclassclass Sale: quantity: int@dataclassclass Product: id: str name: str sales: List[Sale]

我們先生成一些隨機(jī)數(shù)據(jù)，假設(shè)上述類存儲(chǔ)在db_classes.py文件中。

import random# Ignore these for now. You'll use them in a sec ;)from openpyxl import Workbookfrom openpyxl.chart import LineChart, Referencefrom db_classes import Product, Saleproducts = []# Let's create 5 productsfor idx in range(1, 6): sales = [] # Create 5 months of sales for _ in range(5): sale = Sale(quantity=random.randrange(5, 100)) sales.append(sale) product = Product(id=str(idx), name="Product %s" % idx, sales=sales) products.append(product)

通過運(yùn)行這段代碼，我們得到5個(gè)產(chǎn)品5個(gè)月的銷售量，每個(gè)月的銷售量是隨機(jī)的。

現(xiàn)在，要將其轉(zhuǎn)換為電子表格，然后需要對(duì)數(shù)據(jù)進(jìn)行迭代，并將其寫入到Excel中。

workbook = Workbook()sheet = workbook.active#?先添加列名sheet.append(["Product ID", "Product Name", "Month 1", "Month 2", "Month 3", "Month 4", "Month 5"])# 寫入數(shù)據(jù)for product in products: data = [product.id, product.name] for sale in product.sales: data.append(sale.quantity) sheet.append(data)

這可以讓我們用來自數(shù)據(jù)庫的一些數(shù)據(jù)創(chuàng)建一個(gè)電子表格。

然而，為什么不利用我們剛學(xué)的知識(shí)，添加一個(gè)圖表，更直觀地顯示這些數(shù)據(jù)呢？

可以試試下面這樣做：

chart = LineChart()data = Reference(worksheet=sheet, min_row=2, max_row=6, min_col=2, max_col=7)chart.add_data(data, titles_from_data=True, from_rows=True)sheet.add_chart(chart, "B8")cats = Reference(worksheet=sheet, min_row=1, max_row=1, min_col=3, max_col=7)chart.set_categories(cats)chart.x_axis.title = "Months"chart.y_axis.title = "Sales (per unit)"workbook.save(filename="oop_sample.xlsx")

這是一個(gè)由數(shù)據(jù)庫對(duì)象生成的電子表格，并帶有圖表和所有的東西。一個(gè)由數(shù)據(jù)庫對(duì)象生成的電子表格，并帶有圖表和一切：

實(shí)踐運(yùn)用，就是我們學(xué)習(xí)新知識(shí)的最好方法!

自動(dòng)化Excel進(jìn)階技能之四：Pandas與openpyxl雙劍合璧

盡管可以使用Pandas來處理Excel文件，但有時(shí)候，Pandas完成不了，這時(shí)候我們首選openpyxl。

openpyxl可以輕松地使用樣式、條件格式等自定義電子表格。

openpyxl既支持將Pandas DataFrame中的數(shù)據(jù)轉(zhuǎn)換為工作簿，也支持將openpyxl工作簿轉(zhuǎn)換為Pandas DataFrame。

首先要記得安裝pandas包：

然后，讓我們創(chuàng)建一個(gè)示例DataFrame：

import pandas as pddata = { "Product Name": ["Product 1", "Product 2"], "Sales Month 1": [10, 20], "Sales Month 2": [5, 35],}df = pd.DataFrame(data)

現(xiàn)在我們已經(jīng)有了一些數(shù)據(jù)，你可以使用.dataframe_to_rows()將其從DataFrame轉(zhuǎn)換為工作表。

from openpyxl import Workbookfrom openpyxl.utils.dataframe import dataframe_to_rowsworkbook = Workbook()sheet = workbook.activefor row in dataframe_to_rows(df, index=False, header=True): sheet.append(row)workbook.save("pandas.xlsx")

我們會(huì)看到下圖的Excel：

如果我們想添加DataFrame的索引，你可以改變index=True，它就會(huì)把每一行的索引添加到我們的Excel電子表格中。

另一方面，如果我們想把電子表格轉(zhuǎn)換成DataFrame，這里有很直接的方法，比如這樣做：

import pandas as pdfrom openpyxl import load_workbookworkbook = load_workbook(filename="sample.xlsx")sheet = workbook.activevalues = sheet.valuesdf = pd.DataFrame(values)

另外，如果我們想添加正確的標(biāo)題，并使用review ID作為索引，那么我們也可以這樣做來代替：

import pandas as pdfrom openpyxl import load_workbookfrom mapping import REVIEW_IDworkbook = load_workbook(filename="sample.xlsx")sheet = workbook.activedata = sheet.values# 將第一行設(shè)置為DataFrame的列。cols = next(data)data = list(data)# 設(shè)置字段 "review_id "作為每行的索引。idx = [row[REVIEW_ID] for row in data]df = pd.DataFrame(data, index=idx, columns=cols)

使用索引和列可以讓我們輕松地從我們的DataFrame中訪問數(shù)據(jù)。

>>> df.columnsIndex(['marketplace', 'customer_id', 'review_id', 'product_id', 'product_parent', 'product_title', 'product_category', 'star_rating', 'helpful_votes', 'total_votes', 'vine', 'verified_purchase', 'review_headline', 'review_body', 'review_date'], dtype='object')>>> # 獲得前10個(gè)評(píng)論的星級(jí)評(píng)價(jià)>>> df["star_rating"][:10]R3O9SGZBVQBV76 5RKH8BNC3L5DLF 5R2HLE8WKZSU3NL 2R31U3UH5AZ42LL 5R2SV659OUJ945Y 4RA51CP8TR5A2L 5RB2Q7DLDN6TH6 5R2RHFJV0UYBK3Y 1R2Z6JOQ94LFHEP 5RX27XIIWY5JPB 4Name: star_rating, dtype: int64>>> # 使用索引找到 "R2EQL1V1L6E0C9 "的評(píng)論>>> df.loc["R2EQL1V1L6E0C9"]marketplace UScustomer_id 15305006review_id R2EQL1V1L6E0C9product_id B004LURNO6product_parent 892860326review_headline Five Starsreview_body Love itreview_date 2015-08-31Name: R2EQL1V1L6E0C9, dtype: object

好了，不管我們是想用openpyxl來美化我們的Pandas數(shù)據(jù)集，還是想用Pandas來做一些核心的代數(shù)，我們已經(jīng)掌握了在pandas和openpyxl之間自由切換了。

關(guān)注我們，了解后續(xù)更新。

總結(jié)

以上是生活随笔為你收集整理的npoi生成的表格数字左上角_如何用openpyxl自动化编写Excel电子表格 进阶篇 下的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。