python操作excel表

T~1

已于 2024-09-19 09:29:59 修改

阅读量1.3k

点赞数 36

文章标签： python excel 开发语言

于 2024-09-06 11:10:45 首次发布

本文链接：https://blog.csdn.net/m0_46382869/article/details/141885933

版权

python操作excel表

第一章 Python 实现excel功能
第二章从数据库中导出excel表
第三章 Python 实现微信机器人功能

前言

excel是工作中数据分析的常用工具，因此，本文描述了如何将它与自动化数字化进行结合，更好的便于一些重复的数据分析，提高工作效率。（侵权即删！！！！）

一、Python 实现excel功能

读取单独表时，主要运用pandas模块；读取工作簿表时，主要运用load_workbook 模块。

1.引入库

#读取单独表
import pandas as pd
#读取工作簿
from openpyxl import load_workbook

2.打开表

#打开单独表,test.xlsx这个文件可以使用绝对路径或者相对路径
file=pd.read_excel("test.xlsx")
#打开工作簿book_file.xlsx中打开表
workbook = load_workbook('book_file.xlsx') 
#方式一：获取默认工作表，workbook.active是一个非常有用的属性，它用于获取当前活动的工作表（worksheet）。在Excel中，活动工作表通常是你最后查看或编辑的那个工作表。但在使用openpyxl打开Excel文件时，如果没有特别指定哪个工作表是活动的，那么workbook.active通常会返回文件中的第一个工作表。
workbook.active  
#方式二：通过索引打开工作簿中的表格，索引从0开始  
sheet = workbook.sheet_by_index(0) 
#或者
# 获取所有工作表的名称  
sheet_names = workbook.sheetnames    
# 通过名称访问特定的工作表  
specific_sheet = workbook[sheet_names[0]]  # 访问第一个工作表  
# 方式三：假设你知道工作表的名称是'Sheet1 ，通过工作簿中表的名字打开
specific_sheet = workbook.sheet_by_name('Sheet1')

3.实现excel功能(打开单独个表为例)

3.1 vlookup功能：

#filtered_data1要匹配的表，cell_name_data匹配表，可选字段[['test1', 'test2']]，必须有一列相同，根据基站名称进行匹配，得到'test2'数据
merged_data1 = pd.merge(filtered_data1, cell_name_data[['test1', 'test2']],on='test1', how='left')
#删掉列为校园名称重复的行
merged_data1 = merged_data1.dropna(subset=['校区名称'])

how参数在pandas.merge()函数中扮演着重要角色，它决定了两个DataFrame（或Series）进行连接（merge）的方式。how参数主要有以下几种取值，每种取值都对应着不同的连接逻辑：

3.1.1 ‘inner’（内连接）：

只返回两个DataFrame中匹配的行。即，如果两个DataFrame在连接键（on参数指定的列或left_on和right_on参数分别指定的列）上有相同的值，则这些行会被合并成一个新的DataFrame返回。如果某个DataFrame中有行在连接键上没有匹配项，则这些行不会出现在结果中。

3.1.2 ‘left’（左外连接）：

返回左DataFrame的所有行，即使右DataFrame中没有匹配的行。对于左DataFrame中的每一行，如果在右DataFrame中有匹配的行，则合并这些行的数据；如果没有匹配的行，则在结果DataFrame中对应右DataFrame的部分填充NaN（或NA，取决于pandas的设置）。

3.1.3 ‘right’（右外连接）：

与左外连接相反，返回右DataFrame的所有行，即使左DataFrame中没有匹配的行。对于右DataFrame中的每一行，如果在左DataFrame中有匹配的行，则合并这些行的数据；如果没有匹配的行，则在结果DataFrame中对应左DataFrame的部分填充NaN。

3.1.4 ‘outer’（外连接）：

返回两个DataFrame中所有的行。对于每个DataFrame中的行，如果在另一个DataFrame中有匹配的行，则合并这些行的数据；如果没有匹配的行，则在结果DataFrame中对应另一个DataFrame的部分填充NaN。这实际上是内连接、左外连接和右外连接的并集。

3.2 选择行和列功能：

import pandas as pd  
 
data = {  
    'Name': ['Tom', 'Jerry', 'Mickey'],  
    'Age': [5, 7, 8],  
    'City': ['New York', 'Chicago', 'Miami']  
}  
df = pd.DataFrame(data)  
#df.set_index('Name', inplace=True)  # 将'Name'列设置为索引

3.2.1 选择单行单列

df.loc['Tom', 'Age']  # 输出: 5

3.2.2 选择多行多列

df.loc[['Tom', 'Jerry'], ['Age', 'City']]

3.2.3 选择单行多列

df.loc['Tom']  # 注意这里没有指定列标签，因此选择'Tom'行的所有列

3.2.4 选择所有行特定列

df.loc[:, ['Age', 'City']]  # 注意这里使用了冒号(:)来选择所有行

3.2.5 条件选择行

# 选择Age大于7的行  
df.loc[df['Age'] > 7] 
# 这里实际上是使用了布尔索引，但结果可以通过.loc展示出来
# 修改某个单元格的值  
df.loc[0, '年龄'] = 29  # 将第一行（索引为0）的年龄修改为29

3.3 循环访问每一行：

for index,row in df.iterrows():  
        print(f"姓名: {row['姓名']}, 年龄: {row['年龄']}, 城市: {row['城市']}") 
#df是读取的excel表，姓名，年龄等是df的列

3.4 排序：

# 按年龄排序  
sorted_df = df.sort_values(by='年龄', ascending=False)

3.5 分组：

3.5.1 单列分组：

# 按照'category'列分组，并对'value'列进行计数  
grouped = df.groupby('category')['value'].count()

3.5.2 多列分组：

# 假设df是你的DataFrame  
grouped = df.groupby(['category', 'subcategory']).size()  
# 或者对特定列进行聚合操作  
grouped_sum = df.groupby(['category', 'subcategory'])['value'].sum()

3.5.3 过滤分组：

filter 方法允许你根据条件过滤分组。这在你想要基于整个组的属性（而不是单个行）来保留或丢弃组时非常有用。

# 过滤出值大于平均值的组  
filtered_groups = df.groupby('category').filter(lambda x: x['value'].mean() > df['value'].mean())

3.5.4 排序：

# 对分组计数结果进行排序  
sorted_counts = df.groupby('category').size().sort_values(ascending=False)

3.5.5 分组迭代：

使用apply函数
apply函数是groupby对象的一个非常强大的方法，它允许你对每个分组应用一个函数，并返回一个新的Series或DataFrame。这个函数可以是自定义的，也可以是Pandas内置的聚合函数。

# 假设df是你的DataFrame  
def custom_function(group):  
    # 对每个分组执行自定义操作  
    # 返回结果可以是一个标量、Series或DataFrame  
    return group['value'].mean()  
  
# 对每个分组应用自定义函数  
result = df.groupby('category').apply(custom_function)

链式调用
你可以将groupby与多个方法链式调用，以在单个语句中执行分组、过滤、转换和聚合等操作。
后面内容后面慢慢补充！！！！！！

# 假设你想要计算每个类别中大于某个值的值的平均值  
result = df[df['value'] > 10].groupby('category')['value'].mean()

3.分组后使用iterrows()
1.

for name, group in df.groupby('category'):  
    print(f"Group: {name}")  
    for index, row in group.iterrows():  
        # 这里同时使用了组名（name）、索引（index）和行（row）  
        # 对于行，我们通常不需要完整地打印它（因为它可能包含很多列），但这里为了演示，我们这样做  
        print(f"Index: {index}, Row: {row}")  
        # 如果你只想对特定列进行操作，可以这样做：  
        # print(f"Index: {index}, Value: {row['value_column_name']}")  
    print("---")  # 可选，用于分隔不同组的输出

2.`

import pandas as pd    
    
# 创建一个示例DataFrame    
# 注意：这里我假设'value'列被扩展到9个元素，但只填充了一些示例值  
data = {    
    'category1': ['A', 'A', 'B', 'B', 'C', 'A', 'B', 'B', 'C'],    
    'category2': ['X', 'Y', 'X', 'Y', 'Z', 'X', 'X', 'Y', 'Z'],    
    'value': [1, 2, 3, 4, 5, 6, 7, 8, 9]  # 添加了额外的值以匹配行数  
}    
df = pd.DataFrame(data)    
    
# 基于多列分组    
import pandas as pd    
    
# 创建一个示例DataFrame    
# 注意：这里我假设'value'列被扩展到9个元素，但只填充了一些示例值  
data = {    
    'category1': ['A', 'A', 'B', 'B', 'C', 'A', 'B', 'B', 'C'],    
    'category2': ['X', 'Y', 'X', 'Y', 'Z', 'X', 'X', 'Y', 'Z'],    
    'value': [1, 2, 3, 4, 5, 6, 7, 8, 9]  # 添加了额外的值以匹配行数  
}    
df = pd.DataFrame(data)    
    
# 基于多列分组    
for (name1, name2), group in df.groupby(['category1', 'category2']):    
    print(f"Group: ({name1}, {name2})")    
    for index, row in group.iterrows():    
        # 对每行执行操作    
        print(f"Index: {index}, Value: {row['value']}")    
    print("---")  # 可选，用于分隔不同组的输出
#输出

```python
Group: (A, X)  
Index: 0, Value: 1  
Index: 5, Value: 6  
---  
Group: (A, Y)  
Index: 1, Value: 2  
---  
Group: (B, X)  
Index: 2, Value: 3  
Index: 6, Value: 7  
---  
Group: (B, Y)  
Index: 3, Value: 4  
Index: 7, Value: 8  
---  
Group: (C, Z)  
Index: 4, Value: 5  
Index: 8, Value: 9  
---

        # 首先，按照'区'分组，并对每个区中的'站'进行计数
        counts_by_area = wireless_alarm_data.groupby('区')['站'].value_counts().reset_index(name='计数')
        print(counts_by_area)
        counts_by_area.columns = ['区', '站', '计数']  # 如果需要，可以重命名列以更清晰地表示
        # 由于value_counts()已经自动去除了重复的站（在每个区内），我们可以直接遍历结果
        # 初始化一个变量来跟踪当前的区
        current_area = None
        for _, row in counts_by_area.iterrows():
            if current_area is None or current_area != row['区']:
                # 如果是新的片区，或者current_area尚未设置，则打印片区
                print(f" {row['区']}:")
                current_area = row['区']  # 更新current_area以跟踪当前区
            # 总是打印区域和计数
            print(f"    {row['站']}: {row['计数数']}")

输出：

 区域1:
    G宁利坡站_站: 10
    良晓镇雅王站: 10
    宁邕宁雅王: 4
    那寻水库: 2
    邕宁太安乡: 2
 区域2:
    埠乐州: 5
    南水二: 2
    铁2号线: 2


# 二、从数据库中导出excel表

```python
cnx = pymysql.connect(user="root", password="   ", host="   ", database="  ")
    # 查询数据
query = "SELECT * FROM test where outdated_flag = 'new' "
df1 = pd.read_sql(query, con=cnx)
#中间可以加工，例如
#df = pd.merge(df1, df2[['source', 'cause']], on='source', how='left')
#df.rename(columns={'cause': '原因'}, inplace=True)
#df = df[df['name'].str.contains('异常告警|连接中断')]
df.to_excel("D:/YW/new_school/原始文件.xlsx")

二、 Python 实现微信机器人功能

1.引入库

import comtypes
import pymysql
from wxauto import *

2.代码

2.1 导入清单：

def Analy_School_Alarm():
    #将得到的清单导入通报中
    # 加载现有的工作簿
    wb=load_workbook('D:/YW/new_school/保障通报.xlsx')
    school_data=pd.read_excel('D:/YW/new_school_ba/清单.xlsx')
    try:
        ws = wb['清单格式']  # 假设我们要写入Sheet2
    except KeyError:
        print("清单格式")
        # 删除工作表中的所有行（包括标题行）
    for row in range(ws.max_row, 1, -1):  # 从最后一行向前遍历到第二行（标题行之后的下一行）
        ws.delete_rows(row)
        # 然后写入数据
    for r_idx, row in school_data.iterrows():
        for c_idx, value in enumerate(row, start=1):
            ws.cell(row=r_idx + 2, column=c_idx, value=value)
    wb.save('D:/YW/new_school_ba/通报_新.xlsx')

2.2 读取通报：

def TB_School_Alar_as_image_xx(file_path,image_path_xx):
        # 返回文字
    try:
        wps: object = comtypes.client.CreateObject('KET.Application')
    except comtypes.client.COMError as e:
        print(f"无法创建 WPS 应用程序对象: {e}")
        return None
            # 尝试打开工作簿
    try:
        wb = wps.Workbooks.Open(file_path)
    except comtypes.client.COMError as e:
        print(f"无法打开文件 {file_path}: {e}")

            # 访问第一个工作表（假设索引从 1 开始，但可能需要检查 WPS 的实际实现）
    try:
        ws = wb.Worksheets('Sheet1')
        ws.Visible = True
        # 第一张图片
        # 设置图片输出的尺寸
        ws.Range('A1:D64').CopyPicture()
        ws.Paste(ws.Range('O1'))  # 变成图片
        # 图片至剪贴板
        ws.shapes(ws.shapes.Count).Cut()
        # 保存图片
        img = ImageGrab.grabclipboard()
        img.save(image_path_xx)
    except IndexError:
        print("无法访问工作表")
    wb.Close(False)
    wps.Quit()
    del wps

2.3 微信发通报：

def weixin(who, msg):
    # 发微信消息
    wx = WeChat()
    wx.GetSessionList()

    # 发通报
    # wx.ChatWith(who)
    wx.SendMsg(msg)
    #WxUtils.SetClipboard(msg)  # 将内容复制到剪贴板，类似于Ctrl + C
    wx.ChatWith(who)  # 打开`文件传输助手`聊天窗口
    #wx.SendClipboard()
#微信发送文件

def weixin_file(who, file_path):
    # 发微信消息
    wx = WeChat()
    wx.GetSessionList()

    # 发通报
    wx.ChatWith(who)  # 打开`文件传输助手`聊天窗口
    # 发文件
    file = file_path

    # 一次最多发送9个文件，分两次
    wx.SendFiles(file)

# @责任人
def weixin_at_name(who, at_who, data):
    place_name = pd.read_excel('C:/Users/Administrator/Desktop/清单.xlsx', sheet_name='Sheet1')

    data_name = pd.merge(data, place_name, on='区域')

    # 去重
    data_name = data_name.drop_duplicates(subset='负责人')
    data_name.reset_index(drop=True, inplace=True)

    for a in range(0, len(data_name)):
        at_who.append(data_name.loc[a, '负责人'])

    # 发微信消息
    wx = WeChat()
    wx.GetSessionList()
    # 发通报
    wx.ChatWith(who)
    # @人
    wx.at_User(at_who)