python操作Excel神器openpyxl怎么使用
本篇内容主要讲解“python操作Excel神器openpyxl怎么使用”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“python操作Excel神器openpyxl怎么使用”吧!
Excel xlsx
xlsx 是 Microsoft Excel 使用的开放 XML 电子表格文件格式的文件扩展名。 xlsm 文件支持宏。 xlsx 是专有的二进制格式，而 xlsx 是基于 Office Open XML 格式的。
$sudopip3installopenpyxl
我们使用pip3工具安装openpyxl。
Openpyxl 创建新文件
在第一个示例中，我们使用openpyxl创建一个新的 xlsx 文件。
write_xlsx.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookimporttimebook=Workbook()sheet=book.activesheet['A1']=56sheet['A2']=43now=time.strftime("%x")sheet['A3']=nowbook.save("sample.xlsx")在示例中，我们创建一个新的 xlsx 文件。 我们将数据写入三个单元格。
fromopenpyxlimportWorkbook
从openpyxl模块，我们导入Workbook类。 工作簿是文档所有其他部分的容器。
book=Workbook()
我们创建一个新的工作簿。 始终使用至少一个工作表创建一个工作簿。
sheet=book.active
我们获得对活动工作表的引用。
sheet['A1']=56sheet['A2']=43
我们将数值数据写入单元格 A1 和 A2。
now=time.strftime("%x")sheet['A3']=now我们将当前日期写入单元格 A3。
book.save("sample.xlsx")我们使用save()方法将内容写入sample.xlsx文件。
Openpyxl 写入单元格
写入单元格有两种基本方法：使用工作表的键（例如 A1 或 D3），或通过cell()方法使用行和列表示法。
write2cell.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookbook=Workbook()sheet=book.activesheet['A1']=1sheet.cell(row=2,column=2).value=2book.save('write2cell.xlsx')在示例中，我们将两个值写入两个单元格。
sheet['A1']=1
在这里，我们将数值分配给 A1 单元。
sheet.cell(row=2,column=2).value=2
在这一行中，我们用行和列表示法写入单元格 B2。
Openpyxl 附加值
使用append()方法，我们可以在当前工作表的底部附加一组值。
appending_values.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookbook=Workbook()sheet=book.activerows=((88,46,57),(89,38,12),(23,59,78),(56,21,98),(24,18,43),(34,15,67))forrowinrows:sheet.append(row)book.save('appending.xlsx')在示例中，我们将三列数据附加到当前工作表中。
rows=((88,46,57),(89,38,12),(23,59,78),(56,21,98),(24,18,43),(34,15,67))
数据存储在元组的元组中。
forrowinrows:sheet.append(row)
我们逐行浏览容器，并使用append()方法插入数据行。
OpenPyXL 读取单元格
在下面的示例中，我们从sample.xlsx文件中读取先前写入的数据。
read_cells.py
#!/usr/bin/envpythonimportopenpyxlbook=openpyxl.load_workbook('sample.xlsx')sheet=book.activea1=sheet['A1']a2=sheet['A2']a3=sheet.cell(row=3,column=1)print(a1.value)print(a2.value)print(a3.value)该示例加载一个现有的 xlsx 文件并读取三个单元格。
book=openpyxl.load_workbook('sample.xlsx')使用load_workbook()方法打开文件。
a1=sheet['A1']a2=sheet['A2']a3=sheet.cell(row=3,column=1)
我们读取 A1，A2 和 A3 单元的内容。 在第三行中，我们使用cell()方法获取 A3 单元格的值。
$./read_cells.py564310/26/16
这是示例的输出。
OpenPyXL 读取多个单元格
我们有以下数据表：
我们使用范围运算符读取数据。
read_cells2.py
#!/usr/bin/envpythonimportopenpyxlbook=openpyxl.load_workbook('items.xlsx')sheet=book.activecells=sheet['A1':'B6']forc1,c2incells:print("{0:8}{1:8}".format(c1.value,c2.value))在示例中，我们使用范围运算从两列读取数据。
cells=sheet['A1':'B6']
在这一行中，我们从单元格 A1-B6 中读取数据。
forc1,c2incells:print("{0:8}{1:8}".format(c1.value,c2.value))format()功能用于在控制台上整洁地输出数据。
$./read_cells2.pyItemsQuantitycoins23chairs3pencils5bottles8books30
Openpyxl 按行迭代
iter_rows()方法将工作表中的单元格返回为行。
iterating_by_rows.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookbook=Workbook()sheet=book.activerows=((88,46,57),(89,38,12),(23,59,78),(56,21,98),(24,18,43),(34,15,67))forrowinrows:sheet.append(row)forrowinsheet.iter_rows(min_row=1,min_col=1,max_row=6,max_col=3):forcellinrow:print(cell.value,end="")print()book.save('iterbyrows.xlsx')该示例逐行遍历数据。
forrowinsheet.iter_rows(min_row=1,min_col=1,max_row=6,max_col=3):
我们提供了迭代的边界。
$./iterating_by_rows.py884657893812235978562198241843341567
Openpyxl 按列迭代
iter_cols()方法将工作表中的单元格作为列返回。
iterating_by_columns.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookbook=Workbook()sheet=book.activerows=((88,46,57),(89,38,12),(23,59,78),(56,21,98),(24,18,43),(34,15,67))forrowinrows:sheet.append(row)forrowinsheet.iter_cols(min_row=1,min_col=1,max_row=6,max_col=3):forcellinrow:print(cell.value,end="")print()book.save('iterbycols.xlsx')该示例逐列遍历数据。
$./iterating_by_columns.py888923562434463859211815571278984367
统计
对于下一个示例，我们需要创建一个包含数字的 xlsx 文件。 例如，我们使用RANDBETWEEN()函数在 10 列中创建了 25 行数字。
mystats.py
#!/usr/bin/envpythonimportopenpyxlimportstatisticsasstatsbook=openpyxl.load_workbook('numbers.xlsx',data_only=True)sheet=book.activerows=sheet.rowsvalues=[]forrowinrows:forcellinrow:values.append(cell.value)print("Numberofvalues:{0}".format(len(values)))print("Sumofvalues:{0}".format(sum(values)))print("Minimumvalue:{0}".format(min(values)))print("Maximumvalue:{0}".format(max(values)))print("Mean:{0}".format(stats.mean(values)))print("Median:{0}".format(stats.median(values)))print("Standarddeviation:{0}".format(stats.stdev(values)))print("Variance:{0}".format(stats.variance(values)))在示例中，我们从工作表中读取所有值并计算一些基本统计信息。
importstatisticsasstats
导入statistics模块以提供一些统计功能，例如中值和方差。
book=openpyxl.load_workbook('numbers.xlsx',data_only=True)使用data_only选项，我们从单元格而不是公式中获取值。
rows=sheet.rows
我们得到所有不为空的单元格行。
forrowinrows:forcellinrow:values.append(cell.value)
在两个 for 循环中，我们从单元格中形成一个整数值列表。
print("Numberofvalues:{0}".format(len(values)))print("Sumofvalues:{0}".format(sum(values)))print("Minimumvalue:{0}".format(min(values)))print("Maximumvalue:{0}".format(max(values)))print("Mean:{0}".format(stats.mean(values)))print("Median:{0}".format(stats.median(values)))print("Standarddeviation:{0}".format(stats.stdev(values)))print("Variance:{0}".format(stats.variance(values)))我们计算并打印有关值的数学统计信息。 一些功能是内置的，其他功能是通过statistics模块导入的。
$./mystats.pyNumberofvalues:312Sumofvalues:15877Minimumvalue:0Maximumvalue:100Mean:50.88782051282051Median:54.0Standarddeviation:28.459203819700967Variance:809.9262820512821
Openpyxl 过滤器&排序数据
图纸具有auto_filter属性，该属性允许设置过滤条件和排序条件。
请注意，Openpyxl 设置了条件，但是我们必须在电子表格应用中应用它们。
filter_sort.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookwb=Workbook()sheet=wb.activedata=[['Item','Colour'],['pen','brown'],['book','black'],['plate','white'],['chair','brown'],['coin','gold'],['bed','brown'],['notebook','white'],]forrindata:sheet.append(r)sheet.auto_filter.ref='A1:B8'sheet.auto_filter.add_filter_column(1,['brown','white'])sheet.auto_filter.add_sort_condition('B2:B8')wb.save('filtered.xlsx')在示例中，我们创建一个包含项目及其颜色的工作表。 我们设置一个过滤器和一个排序条件。
Openpyxl 维度
为了获得那些实际包含数据的单元格，我们可以使用维度。
dimensions.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookbook=Workbook()sheet=book.activesheet['A3']=39sheet['B3']=19rows=[(88,46),(89,38),(23,59),(56,21),(24,18),(34,15)]forrowinrows:sheet.append(row)print(sheet.dimensions)print("Minimumrow:{0}".format(sheet.min_row))print("Maximumrow:{0}".format(sheet.max_row))print("Minimumcolumn:{0}".format(sheet.min_column))print("Maximumcolumn:{0}".format(sheet.max_column))forc1,c2insheet[sheet.dimensions]:print(c1.value,c2.value)book.save('dimensions.xlsx')该示例计算两列数据的维数。
sheet['A3']=39sheet['B3']=19rows=[(88,46),(89,38),(23,59),(56,21),(24,18),(34,15)]forrowinrows:sheet.append(row)
我们将数据添加到工作表。 请注意，我们从第三行开始添加。
print(sheet.dimensions)
dimensions属性返回非空单元格区域的左上角和右下角单元格。
print("Minimumrow:{0}".format(sheet.min_row))print("Maximumrow:{0}".format(sheet.max_row))使用min_row和max_row属性，我们可以获得包含数据的最小和最大行。
print("Minimumcolumn:{0}".format(sheet.min_column))print("Maximumcolumn:{0}".format(sheet.max_column))通过min_column和max_column属性，我们获得了包含数据的最小和最大列。
forc1,c2insheet[sheet.dimensions]:print(c1.value,c2.value)
我们遍历数据并将其打印到控制台。
$./dimensions.pyA3:B9Minimumrow:3Maximumrow:9Minimumcolumn:1Maximumcolumn:23919884689382359562124183415
工作表
每个工作簿可以有多个工作表。
Figure: Sheets
让我们有一张包含这三张纸的工作簿。
sheets.py
#!/usr/bin/envpythonimportopenpyxlbook=openpyxl.load_workbook('sheets.xlsx')print(book.get_sheet_names())active_sheet=book.activeprint(type(active_sheet))sheet=book.get_sheet_by_name("March")print(sheet.title)该程序可用于 Excel 工作表。
print(book.get_sheet_names())
get_sheet_names()方法返回工作簿中可用工作表的名称。
active_sheet=book.activeprint(type(active_sheet))
我们获取活动表并将其类型打印到终端。
sheet=book.get_sheet_by_name("March")我们使用get_sheet_by_name()方法获得对工作表的引用。
print(sheet.title)
检索到的工作表的标题将打印到终端。
$./sheets.py['January','February','March']<class'openpyxl.worksheet.worksheet.Worksheet'>March
这是程序的输出。
sheets2.py
#!/usr/bin/envpythonimportopenpyxlbook=openpyxl.load_workbook('sheets.xlsx')book.create_sheet("April")print(book.sheetnames)sheet1=book.get_sheet_by_name("January")book.remove_sheet(sheet1)print(book.sheetnames)book.create_sheet("January",0)print(book.sheetnames)book.save('sheets2.xlsx')在此示例中，我们创建一个新工作表。
book.create_sheet("April")使用create_sheet()方法创建一个新图纸。
print(book.sheetnames)
图纸名称也可以使用sheetnames属性显示。
book.remove_sheet(sheet1)
可以使用remove_sheet()方法将纸张取出。
book.create_sheet("January",0)可以在指定位置创建一个新图纸。 在我们的例子中，我们在索引为 0 的位置创建一个新工作表。
$./sheets2.py['January','February','March','April']['February','March','April']['January','February','March','April']
可以更改工作表的背景颜色。
sheets3.py
#!/usr/bin/envpythonimportopenpyxlbook=openpyxl.load_workbook('sheets.xlsx')sheet=book.get_sheet_by_name("March")sheet.sheet_properties.tabColor="0072BA"book.save('sheets3.xlsx')该示例修改了标题为“ March”的工作表的背景颜色。
sheet.sheet_properties.tabColor="0072BA"
我们将tabColor属性更改为新颜色。
第三工作表的背景色已更改为某种蓝色。
合并单元格
单元格可以使用merge_cells()方法合并，而可以不使用unmerge_cells()方法合并。 当我们合并单元格时，除了左上角的所有单元格都将从工作表中删除。
merging_cells.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookfromopenpyxl.stylesimportAlignmentbook=Workbook()sheet=book.activesheet.merge_cells('A1:B2')cell=sheet.cell(row=1,column=1)cell.value='Sunnyday'cell.alignment=Alignment(horizontal='center',vertical='center')book.save('merging.xlsx')在该示例中，我们合并了四个单元格：A1，B1，A2 和 B2。 最后一个单元格中的文本居中。
fromopenpyxl.stylesimportAlignment
为了使文本在最后一个单元格中居中，我们使用了openpyxl.styles模块中的Alignment类。
sheet.merge_cells('A1:B2')我们用merge_cells()方法合并四个单元格。
cell=sheet.cell(row=1,column=1)
我们得到了最后一个单元格。
cell.value='Sunnyday'cell.alignment=Alignment(horizontal='center',vertical='center')
我们将文本设置为合并的单元格并更新其对齐方式。
Openpyxl 冻结窗格
冻结窗格时，在滚动到工作表的另一个区域时，我们会保持工作表的某个区域可见。
freezing.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookfromopenpyxl.stylesimportAlignmentbook=Workbook()sheet=book.activesheet.freeze_panes='B2'book.save('freezing.xlsx')该示例通过单元格 B2 冻结窗格。
sheet.freeze_panes='B2'
要冻结窗格，我们使用freeze_panes属性。
Openpyxl 公式
下一个示例显示如何使用公式。openpyxl不进行计算； 它将公式写入单元格。
formulas.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookbook=Workbook()sheet=book.activerows=((34,26),(88,36),(24,29),(15,22),(56,13),(76,18))forrowinrows:sheet.append(row)cell=sheet.cell(row=7,column=2)cell.value="=SUM(A1:B6)"cell.font=cell.font.copy(bold=True)book.save('formulas.xlsx')在示例中，我们使用SUM()函数计算所有值的总和，并以粗体显示输出样式。
rows=((34,26),(88,36),(24,29),(15,22),(56,13),(76,18))forrowinrows:sheet.append(row)
我们创建两列数据。
cell=sheet.cell(row=7,column=2)
我们得到显示计算结果的单元格。
cell.value="=SUM(A1:B6)"
我们将一个公式写入单元格。
cell.font=cell.font.copy(bold=True)
我们更改字体样式。
OpenPyXL 图像
在下面的示例中，我们显示了如何将图像插入到工作表中。
write_image.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookfromopenpyxl.drawing.imageimportImagebook=Workbook()sheet=book.activeimg=Image("icesid.png")sheet['A1']='ThisisSid'sheet.add_image(img,'B2')book.save("sheet_image.xlsx")在示例中，我们将图像写到一张纸上。
fromopenpyxl.drawing.imageimportImage
我们使用openpyxl.drawing.image模块中的Image类。
img=Image("icesid.png")创建一个新的Image类。icesid.png图像位于当前工作目录中。
sheet.add_image(img,'B2')
我们使用add_image()方法添加新图像。
Openpyxl 图表
openpyxl库支持创建各种图表，包括条形图，折线图，面积图，气泡图，散点图和饼图。
根据文档，openpyxl仅支持在工作表中创建图表。 现有工作簿中的图表将丢失。
create_bar_chart.py
#!/usr/bin/envpythonfromopenpyxlimportWorkbookfromopenpyxl.chartimport(Reference,Series,BarChart)book=Workbook()sheet=book.activerows=[("USA",46),("China",38),("UK",29),("Russia",22),("SouthKorea",13),("Germany",11)]forrowinrows:sheet.append(row)data=Reference(sheet,min_col=2,min_row=1,max_col=2,max_row=6)categs=Reference(sheet,min_col=1,min_row=1,max_row=6)chart=BarChart()chart.add_data(data=data)chart.set_categories(categs)chart.legend=Nonechart.y_axis.majorGridlines=Nonechart.varyColors=Truechart.title="OlympicGoldmedalsinLondon"sheet.add_chart(chart,"A8")book.save("bar_chart.xlsx")在此示例中，我们创建了一个条形图，以显示 2012 年伦敦每个国家/地区的奥运金牌数量。
fromopenpyxl.chartimport(Reference,Series,BarChart)
openpyxl.chart模块具有使用图表的工具。
book=Workbook()sheet=book.active
创建一个新的工作簿。
rows=[("USA",46),("China",38),("UK",29),("Russia",22),("SouthKorea",13),("Germany",11)]forrowinrows:sheet.append(row)我们创建一些数据并将其添加到活动工作表的单元格中。
data=Reference(sheet,min_col=2,min_row=1,max_col=2,max_row=6)
对于Reference类，我们引用表中代表数据的行。 在我们的案例中，这些是奥运金牌的数量。
categs=Reference(sheet,min_col=1,min_row=1,max_row=6)
我们创建一个类别轴。 类别轴是将数据视为一系列非数字文本标签的轴。 在我们的案例中，我们有代表国家名称的文本标签。
chart=BarChart()chart.add_data(data=data)chart.set_categories(categs)
我们创建一个条形图并为其设置数据和类别。
chart.legend=Nonechart.y_axis.majorGridlines=None
使用legend和majorGridlines属性，可以关闭图例和主要网格线。
chart.varyColors=True
将varyColors设置为True，每个条形都有不同的颜色。
chart.title="OlympicGoldmedalsinLondon"
为图表设置标题。
sheet.add_chart(chart,"A8")
使用add_chart()方法将创建的图表添加到工作表中。
在本教程中，我们使用了 openpyxl 库。 我们已经从 Excel 文件中读取数据，并将数据写入 Excel 文件中。
到此，相信大家对“python操作Excel神器openpyxl怎么使用”有了更深的了解，不妨来实际操作一番吧！这里是主机评测网网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
上一篇：TypeScript基本类型之typeof和keyof怎么使用
下一篇：微信小程序怎么使用百度AI识别接口封装Promise