Python处理Excel的14个常用操作详解

见贤思齐

更多资料获取📚个人网站：ipengtao.com在数据处理和分析的领域中，Excel是一种被广泛使用的工具。然而，通过Python处理Excel，能够更好地实现自动化和批量处理。本文将深入探讨Python中处理Excel的14个常用操作，并提供详尽的示例代码，以助您更全面地掌握这些技能。1.读取Excel文件使用pandas库读取Excel文件是一种常见的数据处理操作，它能够快速加载Excel中的表格数据，并将其转换为数据框架（DataFrame）。以下是一个简单的示例代码，演示如何使用pandas读取Excel文件。importpandasaspd#读取Excel文件file_path='example_data.xlsx'df=pd.read_excel(file_path)#打印读取的数据框架print("读取的数据框架：")print(df)123456789在这个例子中，使用pd.read_excel函数读取了一个名为‘example_data.xlsx’的Excel文件。读取后，数据被存储在一个pandas数据框架中。2.写入Excel文件在处理数据后，将结果写入新的Excel文件是一项常见的任务。使用pandas库，可以将处理过的数据写入新的Excel文件。以下是一个简单的示例代码，演示如何使用pandas将数据写入Excel文件。importpandasaspd#创建示例数据框架data={'Product':['A','B','C'],'Price':[25.5,30.2,15.8],'Quantity':[10,8,4]}df=pd.DataFrame(data)#将数据框架写入Excel文件df.to_excel('output_data.xlsx',index=False)12345678910111213在这个例子中，使用to_excel函数将数据框架写入Excel文件。参数index=False表示不包含行索引信息。生成的Excel文件名为‘output_data.xlsx’。3.数据筛选与过滤在数据分析中，经常需要根据特定条件筛选和过滤数据，以便只保留感兴趣的部分。使用pandas库，可以进行数据筛选和过滤。以下是一个简单的示例代码，演示如何使用pandas进行数据筛选与过滤。importpandasaspd#创建示例数据框架data={'Product':['A','B','C','A','B'],'Price':[25.5,30.2,15.8,22.0,18.5],'Quantity':[10,8,4,6,2]}df=pd.DataFrame(data)#筛选Price大于20的数据filtered_data=df[df['Price']>20]#打印筛选后的数据框架print("Price大于20的数据：")print(filtered_data)1234567891011121314151617'运行运行在这个例子中，使用了条件筛选，保留了‘Price’列大于20的行数据。你可以根据实际需求定义不同的筛选条件，以过滤符合条件的数据。4.数据排序在Excel中，数据排序是一种常见的操作，可以更好地理解数据的结构和趋势。使用pandas库，可以对数据进行排序。以下是一个简单的示例代码，演示如何使用pandas对数据进行排序。importpandasaspd#创建示例数据框架data={'Product':['B','A','C','D','A'],'Price':[30.2,25.5,15.8,40.0,20.5],'Quantity':[8,10,4,2,6]}df=pd.DataFrame(data)#按'Product'列升序排序df_sorted=df.sort_values(by='Product')#打印排序后的数据框架print("按'Product'列升序排序：")print(df_sorted)1234567891011121314151617'运行运行在这个例子中，使用sort_values函数按‘Product’列的值进行升序排序。也可以通过指定ascending=False参数来实现降序排序。这种排序方式使可以更容易地观察数据的特征和趋势。5.数据统计与汇总通过pandas的统计函数，可以快速了解数据的统计信息，如均值、中位数等。假设有一个包含销售数据的数据框架sales_data，其中包括产品销售额(sales_amount)、销售数量(quantity)和单价(unit_price)等列。我们将使用这个数据框架来演示如何进行全面的数据统计与汇总。importpandasaspd#假设我们有一个包含销售数据的数据框架data={'Product':['A','B','C','A','B','A'],'Sales_Amount':[100,150,200,120,180,130],'Quantity':[5,3,4,6,2,5],'Unit_Price':[20,50,50,20,90,26]}sales_data=pd.DataFrame(data)#打印原始数据print("原始数据：")print(sales_data)#统计与汇总mean_sales_amount=sales_data['Sales_Amount'].mean()median_quantity=sales_data['Quantity'].median()mode_product=sales_data['Product'].mode().values[0]std_unit_price=sales_data['Unit_Price'].std()min_sales_amount=sales_data['Sales_Amount'].min()max_quantity=sales_data['Quantity'].max()#打印统计结果print("\n统计与汇总结果：")print(f"平均销售额：{mean_sales_amount}")print(f"销售数量中位数：{median_quantity}")print(f"产品销售频率最高的是：{mode_product}")print(f"单价标准差：{std_unit_price}")print(f"最小销售额：{min_sales_amount}")print(f"最大销售数量：{max_quantity}")1234567891011121314151617181920212223242526272829303132'运行运行这个例子中，使用了均值、中位数、众数、标准差、最小值和最大值等统计方法来全面了解销售数据的特征。通过运用这些统计函数，可以更好地理解数据的分布、趋势和离散程度，为进一步的数据分析和决策提供了基础。6.单元格格式设置在处理Excel数据时，自定义单元格格式是提高数据可读性和呈现效果的关键步骤。使用openpyxl库，可以轻松地对Excel单元格进行格式设置。下面是一些常见的单元格格式设置的例子。fromopenpyxlimportWorkbookfromopenpyxl.stylesimportFont,Alignment,PatternFill#创建一个工作簿和工作表workbook=Workbook()sheet=workbook.active#示例数据data=[["Product","Price","Quantity"],["A",25.5,10],["B",30.2,8],["C",15.8,15],]#将数据写入工作表forrowindata:sheet.append(row)#单元格格式设置#设置行的字体为粗体、字号14、颜色为蓝色sheet['A1'].font=Font(bold=True,size=14,color="0000FF")#设置数据区域的对齐方式为居中forrowinsheet.iter_rows(min_row=2,max_row=sheet.max_row,min_col=1,max_col=sheet.max_column):forcellinrow:cell.alignment=Alignment(horizontal='center',vertical='center')#设置价格列的数值格式为货币格式forrowinsheet.iter_rows(min_row=2,max_row=sheet.max_row,min_col=2,max_col=2):forcellinrow:cell.number_format='"$"#,##0.00'#设置数量列的背景颜色为浅黄色forrowinsheet.iter_rows(min_row=2,max_row=sheet.max_row,min_col=3,max_col=3):forcellinrow:cell.fill=PatternFill(start_color="FFFF99",end_color="FFFF99",fill_type="solid")#保存工作簿workbook.save("formatted_excel.xlsx")12345678910111213141516171819202122232425262728293031323334353637383940在这个例子中，通过Font、Alignment和PatternFill类来设置单元格的字体、对齐方式和背景颜色。这种格式设置使得Excel表格更加美观、易读，有助于突出数据的重要性和结构。可以根据实际需求调整这些设置，以满足特定的数据展示要求。7.插入行与列在处理Excel数据时，插入新的行和列是保持数据整洁和有序的关键步骤。使用openpyxl库，可以轻松地在Excel表格中插入新的行和列。以下是一些插入行和列的示例代码。插入新的行fromopenpyxlimportWorkbook#创建一个工作簿和工作表workbook=Workbook()sheet=workbook.active#原始数据data=[["Name","Age","Country"],["Alice",25,"USA"],["Bob",30,"Canada"],]#将数据写入工作表forrowindata:sheet.append(row)#打印原始数据print("原始数据：")forrowinsheet.iter_rows(values_only=True):print(row)#插入新的行（在第二行之后插入）new_row_data=["Charlie",28,"UK"]sheet.insert_rows(new_row_data,row_idx=2)#打印插入新行后的数据print("\n插入新行后的数据：")forrowinsheet.iter_rows(values_only=True):print(row)#保存工作簿workbook.save("inserted_row.xlsx")123456789101112131415161718192021222324252627282930313233插入新的列fromopenpyxlimportWorkbook#创建一个工作簿和工作表workbook=Workbook()sheet=workbook.active#原始数据data=[["Name","Age","Country"],["Alice",25,"USA"],["Bob",30,"Canada"],]#将数据写入工作表forrowindata:sheet.append(row)#打印原始数据print("原始数据：")forrowinsheet.iter_rows(values_only=True):print(row)#插入新的列（在第二列之后插入）new_column_data=["Female","Male","Female"]sheet.insert_cols(values=new_column_data,col_idx=2)#打印插入新列后的数据print("\n插入新列后的数据：")forrowinsheet.iter_rows(values_only=True):print(row)#保存工作簿workbook.save("inserted_column.xlsx")123456789101112131415161718192021222324252627282930313233这些示例代码演示了如何使用insert_rows和insert_cols方法在Excel表格中插入新的行和列。8.合并单元格在Excel中，合并单元格是一种常用的操作，用于创建更复杂的表格结构或突出某些信息。使用openpyxl库，可以实现合并和取消合并单元格的操作。以下是一些合并单元格的示例代码。合并单元格fromopenpyxlimportWorkbook#创建一个工作簿和工作表workbook=Workbook()sheet=workbook.active#原始数据data=[["Name","Age","Country"],["Alice",25,"USA"],["Bob",30,"Canada"],]#将数据写入工作表forrowindata:sheet.append(row)#合并A1到C1的单元格sheet.merge_cells('A1:C1')#在合并的单元格中写入sheet['A1']='PersonalInformation'#保存工作簿workbook.save("merged_cells.xlsx")12345678910111213141516171819202122232425取消合并单元格fromopenpyxlimportload_workbook#加载已存在的工作簿workbook=load_workbook("merged_cells.xlsx")sheet=workbook.active#取消合并A1到C1的单元格sheet.unmerge_cells('A1:C1')#保存工作簿workbook.save("unmerged_cells.xlsx")1234567891011在这个示例中，首先合并了A1到C1的单元格，创建了一个包含的大单元格。然后，演示了如何取消合并这些单元格。这种操作使得表格的布局更加灵活，可以根据实际需要进行定制。9.公式计算在Excel中添加公式是一种常见的操作，可以实现自动计算，并随着数据的更新而动态调整。使用openpyxl库，可以轻松地在Excel中插入公式。以下是一个添加公式的示例代码。fromopenpyxlimportWorkbook#创建一个工作簿和工作表workbook=Workbook()sheet=workbook.active#原始数据data=[["Product","Price","Quantity","Total"],["A",25.5,10,None],["B",30.2,8,None],]#将数据写入工作表forrowindata:sheet.append(row)#添加公式计算Total列，Total=Price*Quantityforrowinrange(2,sheet.max_row+1):sheet[f'D{row}']=f'B{row}*C{row}'#保存工作簿workbook.save("formulas.xlsx")1234567891011121314151617181920212223在这个例子中，通过循环遍历数据行，使用Excel公式B(row)*C(row)来计算Total列的值。这样，无论数据如何变化，Total列都会自动更新。这种功能使得在Excel中进行复杂的数据计算变得更加方便和灵活。10.图表绘制在Excel中插入图表是一种直观且生动的方式，可以更清晰地展示数据的趋势和关系。使用openpyxl和matplotlib库，可以将数据可视化为图表，并插入到Excel工作表中。以下是一个插入柱状图的示例代码。fromopenpyxlimportWorkbookfromopenpyxl.chartimportBarChart,Referenceimportmatplotlib.pyplotaspltfromioimportBytesIO#创建一个工作簿和工作表workbook=Workbook()sheet=workbook.active#原始数据data=[["Category","Value"],["A",25],["B",30],["C",20],]#将数据写入工作表forrowindata:sheet.append(row)#创建柱状图chart=BarChart()chart.title="CategoryvsValue"chart.x_axis.title="Category"chart.y_axis.title="Value"#数据范围data_range=Reference(sheet,min_col=2,min_row=1,max_col=2,max_row=sheet.max_row)#设置图表数据chart.add_data(data_range,titles_from_data=True)#将图表插入到工作表中sheet.add_chart(chart,"D2")#保存工作簿workbook.save("chart_example.xlsx")1234567891011121314151617181920212223242526272829303132333435363738在这个示例中，创建了一个包含柱状图的Excel工作表。首先，使用BarChart创建一个柱状图对象，然后设置图表的、X轴和Y轴。接着，通过Reference定义数据范围，并使用add_data将数据添加到图表中。最后，使用add_chart将图表插入到工作表中。这样，就能够在Excel中通过图表直观地展示数据的分布和关系。11.数据透视表数据透视表是一种强大的数据分析工具，可以帮助我们快速透视和汇总数据。使用pandas的pivot_table函数，可以在Python中轻松创建数据透视表。以下是一个简单的示例代码。importpandasaspd#创建示例数据框架data={'Category':['A','B','A','B','A','B'],'Value':[10,15,20,25,30,35],'Quantity':[2,3,4,5,6,7]}df=pd.DataFrame(data)#创建数据透视表pivot_table=pd.pivot_table(df,values='Value',index='Category',columns='Quantity',aggfunc='sum',fill_value=0)#打印数据透视表print("数据透视表：")print(pivot_table)1234567891011121314151617'运行运行在这个例子中，我们使用pivot_table函数根据‘Category’和‘Quantity’列创建了一个数据透视表。我们指定了值列为‘Value’，使用‘sum’函数进行汇总，如果某些组合不存在则用0填充。最后，打印了生成的数据透视表。12.数据验证在Excel中设置数据验证规则是一种有效的方式，可以确保用户输入的数据符合预期的范围或格式。使用openpyxl库，可以添加数据验证规则。以下是一个简单的示例代码，演示如何在Excel中设置数据验证规则。fromopenpyxlimportWorkbookfromopenpyxl.worksheet.datavalidationimportDataValidation#创建一个工作簿和工作表workbook=Workbook()sheet=workbook.active#原始数据data=[["Name","Age","Country"],["Alice",25,"USA"],["Bob",30,"Canada"],]#将数据写入工作表forrowindata:sheet.append(row)#创建数据验证规则（Age列只允许输入18到60之间的整数）dv=DataValidation(type="whole",operator="between",formula1=18,formula2=60)dv.errorTitle="InvalidInput"dv.error="Agemustbebetween18and60."dv.add("B2:B1048576")#应用规则到整个B列#添加数据验证规则到工作表sheet.add_data_validation(dv)#保存工作簿workbook.save("data_validation_example.xlsx")1234567891011121314151617181920212223242526272829在这个例子中，使用DataValidation类创建了一个数据验证规则，要求在‘B’列（Age列）中输入整数，并且范围必须在18到60之间。然后，将这个规则应用到整个‘B’列。这样，用户在输入数据时，将受到相应范围和格式的限制，提高了数据的准确性。13.批量操作批量操作是在Excel中处理大量数据时提高效率的关键。使用循环和函数，可以对数据进行批量处理。以下是一个简单的示例代码，演示如何使用循环和函数批量操作Excel数据。fromopenpyxlimportWorkbook#创建一个工作簿和工作表workbook=Workbook()sheet=workbook.active#原始数据data=[["Product","Price","Quantity","Total"],["A",25.5,10,None],["B",30.2,8,None],]#将数据写入工作表forrowindata:sheet.append(row)#批量计算Total列的值（Total=Price*Quantity）forrowinrange(2,sheet.max_row+1):price=sheet[f'B{row}'].valuequantity=sheet[f'C{row}'].valuetotal=price*quantitysheet[f'D{row}']=total#打印批量计算后的数据print("批量计算后的数据：")forrowinsheet.iter_rows(values_only=True):print(row)#保存工作簿workbook.save("batch_operations.xlsx")12345678910111213141516171819202122232425262728293031在这个例子中，使用循环遍历数据行，并批量计算了‘Total’列的值。通过使用循环，可以对整个数据集进行高效的操作，而不需要逐个手动处理每一行数据。14.错误处理在处理Excel数据时，错误是不可避免的。为了提高代码的健壮性，可以使用异常处理机制来处理可能出现的错误。以下是一个简单的示例代码，演示如何使用异常处理来处理Excel操作中的错误。fromopenpyxlimportWorkbooktry:#创建一个工作簿和工作表workbook=Workbook()sheet=workbook.active#原始数据data=[["Product","Price","Quantity","Total"],["A",25.5,10,None],["B",30.2,8,None],]#将数据写入工作表forrowindata:sheet.append(row)#尝试计算Total列的值，但存在空值导致的错误forrowinrange(2,sheet.max_row+1):try:price=sheet[f'B{row}'].valuequantity=sheet[f'C{row}'].valuetotal=price*quantitysheet[f'D{row}']=totalexceptTypeErrorase:print(f"Errorinrow{row}:{e}")#打印处理后的数据print("处理后的数据：")forrowinsheet.iter_rows(values_only=True):print(row)#保存工作簿workbook.save("error_handling_example.xlsx")exceptExceptionase:print(f"Anerroroccurred:{e}")1234567891011121314151617181920212223242526272829303132333435363738在这个例子中，使用了两层异常处理。外层的异常处理捕获了可能发生的任何异常，而内层的异常处理仅捕获特定的TypeError，这是由于在计算‘Total’列时可能遇到的错误类型。总结在这篇博客中，分享了使用Python处理Excel数据的各种技巧和方法。首先，学习了如何使用pandas库读取Excel文件，将表格数据转换为数据框架，为后续处理打下了基础。接着，介绍了数据筛选与过滤的方法，利用条件筛选功能，轻松地过滤和保留感兴趣的数据。然后，学习了数据排序的操作，通过pandas的排序功能，使数据更具可读性，更容易理解数据的结构和趋势。还分享了数据的批量操作，通过循环和函数，高效地对Excel数据进行批量处理，提高了代码的复用性和效率。学习了如何利用pandas的数据透视表功能，轻松进行数据透视和汇总，以及如何通过数据验证规则提高数据的准确性。进一步，了解了如何处理Excel中的错误，通过异常处理机制提高代码的健壮性，确保在面对异常情况时程序能够正常执行。最后，学习了如何将处理过的数据写入新的Excel文件，为数据的分享和进一步分析提供了便捷的方式。通过这些技巧和方法，我们能够在Python中更灵活、高效地处理和分析Excel数据，为数据科学和数据处理工作提供了丰富的工具和思路。无论是初学者还是有经验的开发者，这些技能都将为处理实际工作中的Excel数据提供强大的支持。Python学习路线更多资料获取📚个人网站：ipengtao.com如果还想要领取更多更丰富的资料，可以点击文章下方名片，回复【优质资料】，即可获取全方位学习资料包。点击文章下方链接卡片，回复【优质资料】，可直接领取资料大礼包。