使用Python构建Excel电子表格

操作大型数据集的最直观和最友好的方式之一就是使用电子表格。因此，今天有很多电子表格软件包可用。

然而，Microsoft Excel作为分析数据集的最受欢迎的电子表格包占据了领先地位。

在这篇文章中，我们将学习如何用Python的库OpenPyxl 和XlsxWriter 来编写和可视化Excel电子表格。

前提条件

本教程假定你具备以下条件。

• 对Microsoft Excel有基本了解。
• Python编程语言。
• 一个IDE（代码编辑器）；在本教程中我将使用Jupyter笔记本。

下面是Excel的基本术语的快速列表。

• 工作簿/工作表- 这是我们的Excel文件。
• 工作表/工作表--这是Excel文件中的一个页面。我们在工作簿中可以有多个工作表。
• 列--这是有字母的垂直标题。它从A 开始到XFD 。
• 行- 这是有数字的水平标题。它从1 到1048576 开始。
• 单元格- 这些是工作表中的矩形框。

单元格是根据它们所对应的行的编号和列的字母来命名的。例如，工作表中的第一个单元格是A1 。

开始使用OpenPyxl和XlsxWriter

就像python中的其他库一样，在开始之前我们将首先安装和导入这些库。

Python的OpenPyxl和XlsxWriter模块使我们能够读取和修改Excel电子表格文件。当涉及到将python数据自动化到Excel时，这些库很有帮助。

然而，与OpenPyXl不同，不能读取和更新现有的Excel文件。

要安装这些模块，请在交互式外壳中输入以下内容。

安装OpenPyxl

pip install openpyxl

安装XlsxWriter

pip install xlsxwriter

如果模块被正确安装，你应该不会得到错误信息。

在随后的章节中，我将讨论如何使用OpenPyxl创建、读取和可视化数据；然后，我将转到XlsxWriter。

用OpenPyxl创建、读取和加载现有工作簿

Openpyxl允许我们读/写Excel 2010 xlsx/xlsm/xltx/xltm文件。

安装完毕后，我们将使用以下代码创建一个简单的电子表格。

from openpyxl import Workbook  #importing our library

your_workbook = Workbook()    #creating the workbook
sheet = your_workbook.active
sheet["A1"] = "Hello"
sheet["B1"] = "Sectionio!"
sheet["A2"] = "EngEd"
sheet["B2"] = "is!"
sheet["C2"] = "Two"

your_workbook.save(filename="hello_world_openpyxl.xlsx") #saving the file with the 'xlsx' excel extension

作者的屏幕截图

OpenPyxl允许我们加载预先存在的工作簿。为了跟上进度，你可以在这里下载我们将要使用的数据集。使用load_workbook function 来加载这个数据集。

from openpyxl import load_workbook

your_workbook2 = load_workbook('Test.xlsx')
print(your_workbook2.sheetnames)

your_workbook2.sheetnames 告诉我们在我们的excel工作簿中的可用工作表。

使用OpenPyxl的基本excel操作

一旦你加载了你的数据，我们将从它那里获取信息。这可以通过调用.value 函数来完成。

print(your_workbook2['Products']['A2'].value)

但是，如果我想读取整个工作簿呢？

首先，我们需要找出工作簿的行数和列数。这可以通过.max_row 和.max_column 函数来实现。

row = your_workbook2['Products'].max_row
col = your_workbook2['Products'].max_column

print(row)
print(col)

接下来，我将运行两个for 循环；一个将返回我们行上的所有数据，另一个将返回我们列上的数据。

for i in range(1, row+1):
    for j in range (1, col +1):
        print(your_workbook2['Products'].cell(i, j).value)

现在，让我们转到单元格格式化。

单元格格式化可以自定义工作簿中的单元格的外观。我们将对我们加载的测试工作簿进行简单的格式化。但是，首先，我们将创建一个工作表对象。

Wb = your_workbook2['Products']

然后，我们将导入PatternFill 。

图案填充允许我们改变单元格的颜色和图案。为了定义我们的PatternFill，我们需要传递一个pattern type 和fgColor ，这是一个十六进制代码。

你可以从Rapid表中获得任何颜色的十六进制代码。

from openpyxl.styles import PatternFill

first_style = PatternFill(patternType = 'solid', fgColor = '00FF00')
Wb['B7'].fill = first_style

由于我们正在改变工作簿，所以一旦完成运行我们的代码，我们必须保存它。所以现在，看一下电子表格。你会看到单元格B7 已经做了修改。

要保存电子表格，请使用.save() 函数。

your_workbook2.save(filename='Test.xlsx') 

条件格式化与单元格格式化不同，因为它允许我们根据一个条件突出显示特定的单元格。这将我们的注意力吸引到特定的单元格，并使获得洞察力变得容易。

我将导入ColorScaleRule() ，在Excel中启动色标。

色标是视觉指南，有助于理解数据分布。颜色的深浅代表了处于极端的数值。

from openpyxl.formatting.rule import ColorScaleRule

Condition_style = ColorScaleRule(start_type = 'min', start_color = 'E0FFFF', end_type = 'max', end_color = '008080')

Wb.conditional_formatting.add('A2:A11', Condition_style)

your_workbook2.save(filename='Test.xlsx')

作者的屏幕截图

最后，让我们把第一栏中的数字加起来。为了实现这一目标，我们将在一个单元格中输入求和公式。

Wb['A13'] = 'Total'
Wb['B13'] = '=SUM(A2:A11)'
your_workbook2.save(filename='Test.xlsx') 

如何用OpenPyxl实现数据集的可视化

作为人类，如果我们能够直观地看到事物，我们就会迅速地内化它们。

数据可视化是信息的图形化表示。因为有了图形等视觉元素，我们可以对今天接触到的很多信息或数据进行更有逻辑性的理解。

OpenPyXL支持创建条形图、线形图、散点图和饼图。我们将使用我们加载的测试工作簿来可视化一个条形图。

from openpyxl.chart import BarChart, Reference

现在，让我们使用这些库，看看它是如何发挥的。在交互式外壳中输入下面的代码来创建一个条形图。

sheet = your_workbook2.active 

#we would give it the range of our data
values = Reference(sheet, min_col = 1, min_row = 1, max_col = 2, max_row = 11) 

chart = BarChart()  # adding a bar chart
chart.add_data(values)
chart.title = 'BAR-CHART'    # giving the chart a title 
chart.y_axis.title = 'Products'
sheet.add_chart(chart, "D2")  #D2 represent where we want the chart to start from/

your_workbook2.save(filename='Test.xlsx') 

我们已经使用BarChart() 创建了一个条形图。我们还可以使用PieChart() 、LineChart() 、ScatterChart() ，创建饼图、线图和散点图。

现在，让我们用XlsxWriter 来尝试所有这些。

用XlsxWriter创建、读取和加载现有工作簿

在本教程的前面，我们导入了XlsxWriter。导入后，我们将使用下面的代码创建一个简单的电子表格。

import xlsxwriter #Importing our library

your_workbook = xlsxwriter.Workbook('hello_world_xlwt.xlsx')  #Creating the workbook
sheet1 = your_workbook.add_worksheet('Sheet 1')  #The add.worksheet() helps you add a worksheet into your excel files

.write() 函数允许我们添加数据。它需要两样东西。

• 将要输入数据的单元格。
• 要输入的数据。

sheet1.write('A1' , 'Hello') 
sheet1.write('B1' , 'World')
sheet1.write('A2' , 'EngEd')
sheet1.write('B2', 'is')
sheet1.write('C2' , 'Two')

your_workbook.close() #once done, you close using .close

我们也可以通过指定行和列来写上面的代码。

sheet1.write(0, 0 , 'Hello')  #Row 0 and column 0 which is A1
sheet1.write(0, 1, 'World')
sheet1.write(1, 0 , 'EngEd')
sheet1.write(1, 2, 'is')
sheet1.write(1, 3, 'Two')

your_workbook.close() #close the workbook

不幸的是，XlsxWriter ，不允许我们读和写预先存在的Excel工作簿。

使用XlsxWriter的基本Excel操作

让我们试着执行一些基本的excel操作。

我们从一个简单的SUM 操作开始。

在这之前，我们需要使用.write_column 和.write_row ，将数据填充到我们的工作表中。

.write_column 将数据添加到一列，而.write_row 将数据添加到一行。我们只需要指定起始单元格。这在建立页眉时很有用。之后，我们在D列上添加数据。

import xlsxwriter  #Importing our library

your_workbook = xlsxwriter.Workbook('hello_world_xlwt.xlsx')  #Creating the workbook
sheet1 = your_workbook.add_worksheet('Sheet 1')  #The add.worksheet() helps you add a worksheet into your excel files

sheet1.write_column('D1', [1, 2, 3, 4, 5]) #Adding data
sheet1.write_row('A4', [1, 2, 3, 4, 5])

sheet1.write('C7', 'TOTAL:')
sheet1.write('D7', '=SUM(D1:D5)')

your_workbook.close()

让我们来做一些格式化。

我们可以通过利用主题来应用单元格格式化。例如，可以创建和存储不同的主题JSON格式，并将它们应用于你的脚本。

Cyan_design = your_workbook.add_format({'bg_color' :  'cyan'}) #Creating a theme

sheet1.write('A7', '=PI()', Cyan_design) #Applying it on cell A7

我们可以使用下面的代码来做条件格式化。你需要在关闭工作簿之前把这段代码和下面的代码附加起来。

就像我前面提到的，XlsxWriter不允许我们对预先存在的Excel工作簿进行读写。

oak_design = your_workbook.add_format({'bg_color' :  'green'',
                                       'font_name' : 'Century',
                                       'bold': True,
                                       'font_size' : 25 })

sheet1.conditional_format('D1:D5' ,
                                  { 'type' : 'cell',
                                   'criteria': '>',
                                   'value' : 3,
                                   'format' : oak_design})

或数字格式。

currency_format = your_workbook.add_format({'num_format': '$'})

sheet1.write('A8', 1234.56, currency_format)

需要记住的一点是，Python有一个叫做help 的函数。可以用这个来调用。

help(your_workbook.formats[0])

如何用XlsxWriter实现数据集的可视化

为了制作饼状图，我们将利用前面的函数知识建立一个数据集，其中包含西非国家的列表和州/地区的数量。

import xlsxwriter 

workbook= xlsxwriter.Workbook('hello_viz_xlwt.xlsx')
worksheet = workbook.add_worksheet()

headings = ['Countries', 'State_no']

Data = [['Benin', 'Burkina Faso', 'Cape Verde', 'Gambia', 'Ghana', 'Guinea', 'Guinea-Bissau', 'Ivory Coast', 
         'Liberia', 'Mali', 'Mauritania', 'Niger', 'Nigeria', 'Senegal', 'Sierra Leone', 'Togo'],
        [10, 14, 24, 17, 16, 3, 19, 15, 9, 9, 8, 36, 14, 4, 5, 3]]

我们将使用.write_row ，为我们的数据创建标题和.write_column 。

worksheet.write_row('A1', headings) #Write a row of data starting from A1 for our heading 
 
# Write a column of data starting from A2, B2, C2 respectively.
worksheet.write_column('A2', Data[0])
worksheet.write_column('B2', Data[1])

现在，让我们来创建一个饼图。

进入这个交互式外壳示例，使用.add_chart({'type': 'pie'}) 创建一个饼图，并使用.insert_chart 将其添加到电子表格中。

chart1 = workbook.add_chart({'type': 'pie'})

# Add our data series to our chart
chart1.add_series({
    'name':'Number of States/Regions',
     'categories':['Sheet1', 1,0,16,0],
     'values':['Sheet1', 1,1,16,1],
})

chart1.set_title({'name':'Number of States/Regions'}) #Chart title
chart1.set_style(10) #Chart style
worksheet.insert_chart('C2', chart1, {'x_offset':25, 'y_offset':10}) #insert chart to cell C2

workbook.close()

总结

尽管本教程涵盖了使用该库进行操作和可视化的一些基础知识，但你可以通过访问其官方文档来了解更多关于这些库的工作原理。