npoi生成的表格数字左上角_如何用openpyxl自动化编写Excel电子表格 进阶篇 下

自动化Excel进阶技能之一：添加图片

尽管Excel电子表格中并不经常都是插入图片的，但是添加了图片以及图片里插入里url链接，这还是相当实用的。我们可以用它来做品牌宣传，或者让Excel表格更加个性化。

怎么做？

为了能够使用openpyxl向电子表格加载图片，我们必须安装Pillow。

除此之外，我们还需要准备一张要插入的图片。

我们可以在百度图片搜索pandas，然后在Chrome浏览器下面用“图片助手”的插件抓取png格式的pandas徽标图片，将最终的文件保存为 logo.png，并将这个图片复制到你运行示例的根目录下。

这是你需要将该图片导入到hello_word.xlsx电子表格中的代码：

from openpyxl import load_workbookfrom openpyxl.drawing.image import Image# 我们用一个数据很小的hello_world文件workbook = load_workbook(filename="hello_world.xlsx")sheet = workbook.activelogo = Image("logo.png")# 调整图片大小，免得全屏都是图片logo.height = 150logo.width = 150sheet.add_image(logo, "A3")workbook.save(filename="hello_world_logo.xlsx")

自动化Excel进阶技能之二：创建漂亮的图表

Excel之所以强大，是因为能够绘制各种图表。

Excel图表是一种快速可视化，解释数据的好方法。

Excel内有很多不同的图表类型：柱状图、饼状图、线状图等等。

openpyxl支持所有的Excel图表。

在这里，我列举几个怎么画图表的例子，其余图表类型都是一样，照模画虎就行。

请注意：openpyxl目前不支持的几种图表类型有：Funnel, Gantt, Pareto, Treemap, Waterfall, Map和Sunburst。

对于任何我们想构建的图表，我们都需要定义图表类型。BarChart, LineChart等，再加上用于图表的数据。

在构建图表之前，我们需要定义我们想在图表中看到什么数据的表示。有时，我们可以按原样使用数据集，但其他时候，你需要对数据进行一些清洗规整以获得原始数据之外的信息。

让我们先用一些样本数据建立一个新的工作簿：

from openpyxl import Workbookfrom openpyxl.chart import BarChart, Referenceworkbook = Workbook()sheet = workbook.active# 创建一些虚拟销售数据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)

现在我们要开始创建一个显示每个产品销售总数的条形图：

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")

下面你可以看到一个非常直接的柱状图，显示电商平台产品销售和线下门店产品销售的区别：

如图所示，图表的左上角是原始数据。在我们的案例中，它是在单元格E2上。

我们再接着尝试创建一个折线图，先改变一下数据：

import randomfrom openpyxl import Workbookfrom openpyxl.chart import LineChart, Referenceworkbook = Workbook()sheet = workbook.active# 创建一些虚拟销售数据样本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)

通过上面的代码，我们将生成一些随机数据，关于3种不同产品在一整年的销售情况。

一旦完成，我们可以很容易地用下面的代码创建一个折线图。

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")

这是上面这段代码的结果：

这里需要注意的是，我们在添加数据时，参数使用from_rows=True。这个参数让图表是逐行而不是逐列进行绘制。

在示例数据中，我们看到每个产品都有12个数据行(每个月1列)。这就是为什么要使用from_rows的原因。

如果我们不传递这个参数，默认情况下，图表是按列绘制，我们会得到一个逐月的销售比较。

另一个与上述参数变化有关的区别是，我们的Reference现在从第一列开始，min_col=1，而不是第二列。

这个变化是需要的，因为图表默认第一列有标题。

关于图表的样式，还有一些其他的参数我们可以改变。

例如，可以在图表中添加特定的类别。

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

在保存工作簿之前添加这段代码，我们应该会看到月名而不是数字出现

从代码上来说，这是一个最小的变化。但就电子表格的可读性而言，这让读者更容易打开电子表格并直接理解图表。

另一个可以提高图表可读性的方法是添加一个轴。

我们可以使用属性x_axis和y_axis来实现。

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

这将生成一个像下面这样的电子表格：

正如上图所示，这样的小变化使图表更容易读和理解。

还有一种方法是通过使用Excel的默认ChartStyle属性来设置图表的样式。

在这种情况下，我们需要在1和48之间选择一个数字。根据我们的选择，我们的图表颜色会发生改变。

# 我们可以选择颜色，从1到48里面填一个数字即可chart.style = 24

在上面选择的风格下，所有的线条都有一些橙色的阴影。

没有明确的文档说明每个样式会是什么样的，但这个案例有几个可用样式。

这里有更多的图表类型和自定义可以应用，所以如果我们需要一些特定的格式，我们需要查阅这方面的文档包。

自动化Excel进阶技能之三：将Python数据存储到Excel电子表格

我们演示里如何将Excel电子表格的数据转换为Python类，但现在让我们反过来做。

让我们想象一下，如果我们有一个数据库，并且正在使用一些对象关系映射 (ORM) 将 DB 对象映射到 Python 类中。现在，我们想把这些相同的对象导出到Excel中。

让我们假设以下数据类来表示来自数据库的有关产品销售的数据：

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

我们先生成一些随机数据，假设上述类存储在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)

通过运行这段代码，我们得到5个产品5个月的销售量，每个月的销售量是随机的。

现在，要将其转换为电子表格，然后需要对数据进行迭代，并将其写入到Excel中。

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

这可以让我们用来自数据库的一些数据创建一个电子表格。

然而，为什么不利用我们刚学的知识，添加一个图表，更直观地显示这些数据呢？

可以试试下面这样做：

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")

这是一个由数据库对象生成的电子表格，并带有图表和所有的东西。一个由数据库对象生成的电子表格，并带有图表和一切：

实践运用，就是我们学习新知识的最好方法!

自动化Excel进阶技能之四：Pandas与openpyxl双剑合璧

尽管可以使用Pandas来处理Excel文件，但有时候，Pandas完成不了，这时候我们首选openpyxl。

openpyxl可以轻松地使用样式、条件格式等自定义电子表格。

openpyxl既支持将Pandas DataFrame中的数据转换为工作簿，也支持将openpyxl工作簿转换为Pandas DataFrame。

首先要记得安装pandas包：

然后，让我们创建一个示例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)

现在我们已经有了一些数据，你可以使用.dataframe_to_rows()将其从DataFrame转换为工作表。

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")

我们会看到下图的Excel：

如果我们想添加DataFrame的索引，你可以改变index=True，它就会把每一行的索引添加到我们的Excel电子表格中。

另一方面，如果我们想把电子表格转换成DataFrame，这里有很直接的方法，比如这样做：

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

另外，如果我们想添加正确的标题，并使用review ID作为索引，那么我们也可以这样做来代替：

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

使用索引和列可以让我们轻松地从我们的DataFrame中访问数据。

>>> 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个评论的星级评价>>> df["star_rating"][:10]R3O9SGZBVQBV76    5RKH8BNC3L5DLF     5R2HLE8WKZSU3NL    2R31U3UH5AZ42LL    5R2SV659OUJ945Y    4RA51CP8TR5A2L     5RB2Q7DLDN6TH6     5R2RHFJV0UYBK3Y    1R2Z6JOQ94LFHEP    5RX27XIIWY5JPB     4Name: star_rating, dtype: int64>>> # 使用索引找到 "R2EQL1V1L6E0C9 "的评论>>> 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数据集，还是想用Pandas来做一些核心的代数，我们已经掌握了在pandas和openpyxl之间自由切换了。

关注我们，了解后续更新。