广东甜品美食联盟

用python读取excel数据(三)

楼主:怪兽宇 时间:2018-03-19 09:55:46

更新电子表格

在这个项目中,您将编写一个程序来更新产品销售电子表格中的单元格。您的程序将通过电子表格查找特定种类的产品,并更新其价格。从http://nostarch.com/automatestuff/下载此电子表格。图12-3显示了电子表格的样子。

每一行代表个人销售。这些列是销售的产品的类型(A),每磅的成本(B),销售的磅数(C)和销售总收入(D)。 TOTAL列设置为Excel公式= ROUND(B3 * C3,2),它将每磅的成本乘以销售的磅数,并将结果舍入到最接近的百分比。使用此公式,如果列B或C中有更改,则TOTAL列中的单元格将自动更新。

现在想象大蒜,芹菜和柠檬的价格输入错误,让您在这个电子表格中经历数千行的无聊任务更新任何大蒜,芹菜和柠檬行的每磅成本。您不能为价格做简单的查找和替换,因为可能会有其他与您不想错误地“正确”的价格相同的项目。对于数千行,手动需要几个小时。但是你可以编写一个可以在几秒内完成的程序。

您的程序执行以下操作:

  • 循环在所有行。

  • 如果这行是用于大蒜,芹菜或柠檬,则会改变价格。

这意味着您的代码将需要执行以下操作:

  • 打开电子表格文件。

  • 对于每一行,检查列A中的值是否为芹菜,大蒜或柠檬。

  • 如果是,请更新列B中的价格。

  • 将电子表格保存到新文件(以防您丢失旧的电子表格,以防万一)。

步骤1:使用更新信息设置数据结构

您需要更新的价格如下:
Celery 1.19
Garlic 3.07
Lemon 1.27

你可以编写这样的代码:

if produceName == 'Celery':
    cellObj = 1.19
if produceName == 'Garlic':
    cellObj = 3.07
if produceName == 'Lemon':
    cellObj = 1.27

拥有这样的硬编码的产品和更新的价格数据有点不起眼。如果您需要以不同的价格或不同的产品再次更新电子表格,则必须更改大量的代码。每次更改代码时,都会冒险引入错误。

更灵活的解决方案是将更正的价格信息存储在字典中,并编写代码以使用此数据结构。在新的文件编辑器窗口中,输入以下代码:

#更正生产销售电子表格中的成本。
# updateProduce.py - 更正生产销售电子表格中的成本。

import openpyxl

wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb.get_sheet_by_name('Sheet')

# 产品种类及其更新价格
PRICE_UPDATES = {'Garlic': 3.07,
                 'Celery': 1.19,
                 'Lemon': 1.27}

# TODO: 循环行并更新价格。

将其另存为updateProduce.py。如果您需要再次更新电子表格,则只需要更新PRICE_UPDATES字典,而不是任何其他代码。

步骤2:检查所有行并更新不正确的价格

程序的下一部分将循环遍历电子表格中的所有行。将以下代码添加到updateProduce.py的底部:

   --snip--

   # Loop through the rows and update the prices.
for rowNum in range(2, sheet.max_row):  # skip the first row❶
    produceName = sheet.cell(row=rowNum, column=1).value#❷   
    if produceName in PRICE_UPDATES:#❸
        sheet.cell(row=rowNum, column=2).value = PRICE_UPDATES[produceName]

wb.save('updatedProduceSales.xlsx')#❹

我们循环从行2开始的行,因为行1只是标题❶。列1中的单元格(即列A)将存储在变量produceName❷中。如果productName作为PRICE_UPDATES字典❸中的键存在,那么您知道这是必须对其价格进行纠正的行。正确的价格将在PRICE_UPDATES [produceName]。

注意使用PRICE_UPDATES的清洁程序使代码成为可能。只有一个if语句,而不是像generateName ==’Garlic’:,这样的代码对于每种类型的产品都是必需的。并且由于代码使用PRICE_UPDATES字典而不是将产品名称和更新的成本硬编码到for循环中,所以只有在生产销售电子表格需要额外的更改时才修改PRICE_UPDATES字典而不修改代码。

通过整个电子表格进行更改后,代码将Workbook对象保存到updatedProduceSales.xlsx❹。它不会覆盖旧的电子表格,以防万一你的程序有一个错误,更新的电子表格是错误的。检查更新的电子表格是否正确后,您可以删除旧的电子表格。

您可以从http://nostarch.com/automatestuff/下载此程序的完整源代码。

类似程序的想法

由于许多上班族一直使用Excel电子表格,可以自动编辑和编写Excel文件的程序可能非常有用。这样一个程序可以做到以下几点:

  • 从一个电子表格读取数据并将其写入其他电子表格的部分。

  • 从网站,文本文件或剪贴板读取数据,并将其写入电子表格。

  • 自动“清理”电子表格中的数据。例如,它可以使用正则表达式读取多种格式的电话号码,并将其编辑为单一的标准格式。

设置单元格的字体样式

对某些单元格,行或列进行样式可以帮助您强调电子表格中的重要区域。例如,在生产电子表格中,您的程序可以将粗体文本应用于马铃薯,大蒜和parsnip行。或者您可能希望以每磅重大于5美元的成本排列每行。手工制作大型电子表格的部件将是乏味的,但您的程序可以立即执行。

要在单元格中自定义字体样式,重要的是从openpyxl.styles模块导入Font()函数。

from openpyxl.styles import Font

这允许您键入Font()而不是openpyxl.styles.Font()。 (请参阅导入模块以查看此样式的import语句。)

以下是创建新工作簿并将单元格A1设置为具有24点斜体字体的示例。

import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook()
sheet = wb.get_sheet_by_name('Sheet')
italic24Font = Font(size=24, italic=True) #❶
sheet['A1'].font = italic24Font #❷
sheet['A1'] = 'Hello world!'
wb.save('styled.xlsx')

可以通过将Font对象分配给style属性来设置单元格的样式。
在这个例子中,Font(size = 24,italic = True)返回一个Font对象,它存储在italic24Font❶中。 Font(),size和italic的关键字参数配置Font对象。当fontObj分配给单元格的字体属性❷时,所有字体样式信息都将应用于单元格A1。

字体对象

要设置字体样式属性,将关键字参数传递给Font()。 Font()函数可能的关键字参数如表12-2所示。

关键词参数数据类型描述
namestring字体名称,如“Calibri”或“Times New Roman”
size整数点大小
bold布尔值True, 用于粗体字体
italic布尔值True,  用于斜体字体

您可以调用Font()创建一个Font对象,并将该Font对象存储在一个变量中。然后将其传递给Style(),将生成的Style对象存储在变量中,并将该变量分配给Cell对象的style属性。例如,此代码创建各种字体样式:

import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook()
sheet = wb.get_sheet_by_name('Sheet')

fontObj1 = Font(name='Times New Roman', bold=True)
sheet['A1'].font = fontObj1
sheet['A1'] = 'Bold Times New Roman'

fontObj2 = Font(size=24, italic=True)
sheet['B3'].font = fontObj2
sheet['B3'] = '24 pt Italic'
wb.save('styles.xlsx')

这里,我们在FontObj1中存储一个Font对象,然后将A1 Cell对象的font属性设置为fontObj1。我们用另一个Font对象重复该过程,以设置第二个单元格的样式。运行此代码后,电子表格中A1和B3单元格的样式将设置为自定义字体样式,如图12-4所示。

对于单元格A1,我们将字体名称设置为“Times New Roman”,并将bold设置为true,因此我们的文本以粗体Times New Roman显示。我们没有指定大小,所以使用openpyxl默认值11。在单元格B3中,我们的文本为斜体,大小为24;我们没有指定字体名称,所以使用openpyxl默认值Calibri。

公式

以等号开头的公式可以将单元格配置为包含从其他单元格计算的值。在本节中,您将使用openpyxl模块以编程方式将公式添加到单元格,就像任何正常值一样。例如:

sheet['B9'] = '=SUM(B1:B8)'

这将存储= SUM(B1:B8)作为单元格B9中的值。这将B9单元格设置为计算单元格B1至B8中值的总和的公式。您可以在图12-5中看到这一点。

单元格B9包含公式= SUM(B1:B8),它将单元格B1添加到B8。

公式与单元格中的任何其他文本值一样设置。在交互式shell中输入以下内容:

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=SUM(A1:A2)'
wb.save('writeFormula.xlsx')

A1和A2中的单元分别设置为200和300。单元格A3中的值设置为将A1和A2中的值相加的公式。当电子表格在Excel中打开时,A3将显示其值为500。

Excel公式为电子表格提供了一定程度的可编程性,但是对于复杂的任务可能会很快变得难以管理。例如,即使您对Excel公式非常熟悉,尝试破译什么是令人头痛的
=IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!1:10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!1:10000, 2, FALSE), “ ”, “”),“”)), “”)

实际上 Python代码更加可读。

调整行和列

在Excel中,调整行和列的大小与单击和拖动行或列标题的边缘一样简单。但是,如果您需要根据其单元格的内容设置行或列的大小,或者如果要在大量电子表格文件中设置大小,则编写Python程序将会更快。

行和列也可以完全隐藏在视图中。或者它们可以“冻结”到位,以便它们始终可以在屏幕上显示,并在电子表格打印时显示在每个页面上(可用于标题)。

设置行高和列宽

工作表对象具有row_dimensions和column_dimensions属性,可以控制行高和列的宽度。输入到交互式shell中:

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 'Tall row'
sheet['B2'] = 'Wide column'
sheet.row_dimensions[1].height = 70
sheet.column_dimensions['B'].width = 20
wb.save('dimensions.xlsx')

工作表的row_dimensions和column_dimensions是类似字典的值; row_dimensions包含RowDimension对象,column_dimensions包含ColumnDimension对象。在row_dimensions中,可以使用行数(在这种情况下为1或2)访问其中一个对象。在column_dimensions中,您可以使用列的字母访问其中一个对象(在本例中为A或B)。

一旦你有RowDimension对象,你可以设置它的高度。一旦有了ColumnDimension对象,你可以设置它的宽度。行高可以设置为0到409之间的整数或浮点值。该值表示以点为单位的高度,其中一点等于1/72英寸。默认行高为12.75。列宽可以设置为0到255之间的整数或浮点值。该值表示可以在单元格中显示的默认字体大小(11点)的字符数。默认列宽为8.43个字符。宽度为0或高度为0的行的列从用户隐藏。

合并和拆分

可以使用merge_cells()sheet方法将单元格的矩形区域合并到单个单元格中。在交互式shell中输入以下内容:

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet.merge_cells('A1:D3')
sheet['A1'] = 'Twelve cells merged together.'
sheet.merge_cells('C5:D5')
sheet['C5'] = 'Two merged cells.'
wb.save('merged.xlsx')

merge_cells()的参数是要合并的矩形区域的左上角和右下角单元格的单个字符串:“A1:D3”将12个单元格合并到单个单元格中。要设置这些合并的单元格的值,只需设置合并组的左上角单元格的值即可。

要取消合并单元格,请调用unmerge_cells()表单方法。将其输入到交互式shell中。

import openpyxl
wb = openpyxl.load_workbook('merged.xlsx')
sheet = wb.active
sheet.unmerge_cells('A1:D3')
sheet.unmerge_cells('C5:D5')
wb.save('merged.xlsx')

如果您保存更改,然后查看电子表格,则会看到合并的单元格已恢复为单个单元格。

冻结面板

如果电子表格太大,无法一次显示,那么“冻结”屏幕顶部的几列或最左列是有帮助的。例如,冻结的列或行标题对用户来说总是可见的,即使他们滚动浏览电子表格。这些被称为冻结窗格。在OpenPyXL中,每个Worksheet对象都有一个可以设置为Cell对象或单元格坐标字符串的freeze_panes属性。请注意,此单元格左侧的所有列和所有列将被冻结,但单元格本身的行和列将不会被冻结。

要解冻所有窗格,请将freeze_panes设置为None或“A1”。表12-3显示了对于freeze_panes的某些示例设置,哪些行和列将被冻结。

|freeze_panes设置|  行和列冻结|
|sheet.freeze_panes = ‘A2’|Row 1|
|sheet.freeze_panes = ‘B1’| Column A|
|sheet.freeze_panes = ‘C1’|     Columns A and B|
|sheet.freeze_panes = ‘C2’|     Row 1 and columns A and B|
|sheet.freeze_panes = ‘A1’ or sheet.freeze_panes = None|没有冷冻窗格|

确保您有http://nostarch.com/automatestuff/的销售电子表格。

import openpyxl
wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb.active
sheet.freeze_panes = 'A2'
wb.save('freezeExample.xlsx')

如果将freeze_panes属性设置为“A2”,则无论用户在电子表格中滚动的位置,行1将始终是可见的。

图表

OpenPyXL支持使用工作表单元格中的数据创建条形图,线条,散点图和饼图。要制作图表,您需要执行以下操作:

  • 从矩形选择的单元格创建一个Reference对象。

  • 通过传入Reference对象来创建一个Series对象。

  • 创建一个图表对象。

  • 将Series对象附加到Chart对象

  • 将图表对象添加到工作表对象,可选择指定图表左上角应位于哪个单元格。

Reference对象需要一些解释。引用对象通过调用openpyxl.chart.Reference()函数并传递三个参数来创建:

  • 包含图表数据的Worksheet对象。

  • 两个整数的元组,表示包含图表数据的单元格的矩形选择的左上角单元格:元组中的第一个整数是行,第二个是列。注意1是第一行,而不是0。

  • 两个整数的元组,表示包含图表数据的单元格的矩形选择的右下角单元格:元组中的第一个整数是行,第二个是列。

创建条形图并将其添加到电子表格中:

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1, 11):         # create some data in column A
    sheet['A' + str(i)] = i

refObj = openpyxl.chart.Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10)

seriesObj = openpyxl.chart.Series(refObj, title='First series')

chartObj = openpyxl.chart.BarChart()
chartObj.title = 'My Chart'
chartObj.append(seriesObj)
sheet.add_chart(chartObj, 'C5')
wb.save('sampleChart.xlsx')

我们通过调用openpyxl.chart.BarChart()创建了一个条形图。您还可以通过调用openpyxl.chart.LineChart(),openpyxl.chart.ScatterChart()和openpyxl.chart.PieChart()来创建折线图,散点图和饼图。

不幸的是,在当前版本的OpenPyXL(2.3.3)中,load_workbook()函数不会在Excel文件中加载图表。即使Excel文件具有图表,加载的Workbook对象也不会包含它们。如果您加载Workbook对象并立即将其保存到相同的.xlsx文件名,您将有效地从其中删除图表。

概要

处理信息的困难部分通常不是处理本身,而只是以适当的格式获取数据。但是,一旦你将你的电子表格加载到Python中,你可以比手工提取和操纵它的数据更快。

您还可以生成电子表格作为程序的输出。所以,如果同事需要您的文本文件或数千个销售联系人的PDF转移到电子表格文件,则不必将其全部复制并粘贴到Excel中。

配备了openpyxl模块和一些编程知识,即使是最大的电子表格也可以处理一块蛋糕。

朋友 图片 表情 草稿箱
请遵守社区公约言论规则,不得违反国家法律法规