自动化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 5RKH8BNC3L5DLF5R2HLE8WKZSU3NL 2R31U3UH5AZ42LL 5R2SV659OUJ945Y 4RA51CP8TR5A2L5RB2Q7DLDN6TH65R2RHFJV0UYBK3Y 1R2Z6JOQ94LFHEP 5RX27XIIWY5JPB4Name: star_rating, dtype: int64>>> # 使用索引找到 "R2EQL1V1L6E0C9 "的评论>>> df.loc["R2EQL1V1L6E0C9"]marketplaceUScustomer_id 15305006review_idR2EQL1V1L6E0C9product_id B004LURNO6product_parent892860326review_headline Five Starsreview_bodyLove itreview_date -08-31Name: R2EQL1V1L6E0C9, dtype: object
好了，不管我们是想用openpyxl来美化我们的Pandas数据集，还是想用Pandas来做一些核心的代数，我们已经掌握了在pandas和openpyxl之间自由切换了。
关注我们，了解后续更新。
