python——Excel处理

Python处理Excel文件的库

大纲

1. 库概述

   • pandas
   • openpyxl
   • xlrd

2. 库的安装

   • 安装pandas
   • 安装openpyxl
   • 安装xlrd

3. 功能介绍

   • pandas功能
   • openpyxl功能
   • xlrd功能

4. 库的使用

   • pandas使用方法
     • 读取Excel文件
     • 数据统计计算
     • 写入Excel文件
   • openpyxl使用方法
     • Workbook对象
     • 读取和修改Excel文件
     • 自定义格式和样式
     • 插入公式
   • xlrd使用方法
     • 读取Excel文件

5. 结合使用场景

   • 读取数据
   • 处理和计算
   • 格式化和保存

6. 具体示例

   • 综合示例：使用pandas读取数据，使用openpyxl进行格式化

---

1. 库概述

1.1 pandas

• 主要功能：处理数据分析和计算，支持读取和写入Excel文件（.xls和.xlsx）。
• 适用场景：数据处理、统计分析、数据清洗等。

1.2 openpyxl

• 主要功能：处理.xlsx格式的Excel文件，包括读取、修改、格式化和写入。
• 适用场景：Excel文件的高级格式化、公式插入、样式设置等。

1.3 xlrd

• 主要功能：读取.xls格式的Excel文件，不支持写操作。
• 适用场景：老版Excel文件的读取。

2. 库的安装

2.1 安装pandas

pip install pandas
pip install openpyxl  # 处理.xlsx文件
pip install xlrd      # 处理.xls文件

2.2 安装openpyxl

pip install openpyxl

2.3 安装xlrd

pip install xlrd

3. 功能介绍

3.1 pandas功能

• 读取Excel文件：支持.xls和.xlsx格式，方便快速读取数据。
• 数据统计计算：提供丰富的数据分析功能，如求和、均值、最大值等。
• 写入Excel文件：将处理结果保存到新的Excel文件中。

3.2 openpyxl功能

• 读取和修改Excel文件：支持.xlsx格式的读取和修改。
• 自定义格式和样式：支持设置单元格的字体、边框、填充颜色等。
• 插入公式：支持在单元格中插入Excel公式。
• Workbook操作：创建新的工作簿、添加工作表、设置工作簿属性等。

3.3 xlrd功能

• 读取Excel文件：支持.xls格式的读取，但不支持写操作。

4. 库的使用

4.1 pandas使用方法

• 读取Excel文件

  import pandas as pd
  
  # 读取.xlsx文件
  df = pd.read_excel('example.xlsx', engine='openpyxl')
  
  # 读取.xls文件
  df = pd.read_excel('example.xls', engine='xlrd')
  

• 数据统计计算

  • 求和

    total = df['ColumnName'].sum()
    

    示例：计算Sales列的总和

    total_sales = df['Sales'].sum()
    print(f'Total Sales: {total_sales}')
    

  • 均值

    mean = df['ColumnName'].mean()
    

    示例：计算Age列的平均值

    average_age = df['Age'].mean()
    print(f'Average Age: {average_age}')
    

  • 最大值和最小值

    max_value = df['ColumnName'].max()
    min_value = df['ColumnName'].min()
    

    示例：计算Salary列的最大值和最小值

    max_salary = df['Salary'].max()
    min_salary = df['Salary'].min()
    print(f'Max Salary: {max_salary}')
    print(f'Min Salary: {min_salary}')
    

  • 分组统计

    grouped = df.groupby('GroupColumn')['NumericColumn'].sum()
    

    示例：按Department分组计算Salary的总和

    department_salaries = df.groupby('Department')['Salary'].sum()
    print(department_salaries)
    

  • 描述性统计

    description = df.describe()
    

    示例：获取所有数值列的统计摘要

    summary = df.describe()
    print(summary)
    

• 写入Excel文件

  df.to_excel('output.xlsx', index=False)
  

4.2 openpyxl使用方法

• Workbook对象

  • 创建工作簿

    from openpyxl import Workbook
    
    # 创建一个新的工作簿
    wb = Workbook()
    
    # 选择当前活动的工作表
    ws = wb.active
    
    # 为工作表命名
    ws.title = "Sheet1"
    

  • 添加工作表

    # 创建一个新的工作表
    ws2 = wb.create_sheet(title="Sheet2")
    

  • 删除工作表

    wb.remove(ws2)
    

• 读取和修改Excel文件

  from openpyxl import load_workbook
  
  # 加载工作簿
  wb = load_workbook('example.xlsx')
  
  # 选择工作表
  ws = wb['Sheet1']
  

• 自定义格式和样式

  from openpyxl.styles import NamedStyle, Font, Border, Side, PatternFill
  
  # 定义样式
  currency_style = NamedStyle(name='currency', number_format='$#,##0.00')
  bold_font = Font(bold=True)
  thin_border = Border(left=Side(border_style="thin"), 
                       right=Side(border_style="thin"), 
                       top=Side(border_style="thin"), 
                       bottom=Side(border_style="thin"))
  fill_color = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
  
  # 应用样式
  ws['A1'].style = currency_style
  ws['A1'].font = bold_font
  ws['A1'].border = thin_border
  ws['A1'].fill = fill_color
  

• 插入公式

  ws['C1'] = '=SUM(A:A)'  # 在C1单元格插入A列总和公式
  

• 保存工作簿

  wb.save('output.xlsx')
  

4.3 xlrd使用方法

• 读取Excel文件

  import xlrd
  
  # 打开文件
  workbook = xlrd.open_workbook('example.xls')
  
  # 选择工作表
  sheet = workbook.sheet_by_index(0)
  

  示例：计算第0列的总和

  total = sum(sheet.cell_value(row, 0) for row in range(sheet.nrows) if isinstance(sheet.cell_value(row, 0), (int, float)))
  print(f'Total: {total}')
  

5. 结合使用场景

在实际工作中，pandas和openpyxl常常结合使用，以充分利用它们的优势：

• 读取数据：使用pandas读取Excel文件，进行数据处理和分析。
• 处理和计算：在pandas中进行数据统计计算。
• 格式化和保存：使用openpyxl对数据进行格式化，并将结果保存为新的Excel文件。

示例：使用pandas读取数据，使用openpyxl进行格式化

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import NamedStyle, Font, PatternFill

# 使用pandas读取数据
df = pd.read_excel('example.xlsx', engine='openpyxl')

# 进行数据处理
total_sales = df['Sales'].sum()

# 将

处理结果写入新的Excel文件
df.to_excel('output.xlsx', index=False)

# 使用openpyxl加载新生成的Excel文件
wb = load_workbook('output.xlsx')
ws = wb['Sheet1']

# 定义样式
currency_style = NamedStyle(name='currency', number_format='$#,##0.00')
bold_font = Font(bold=True)
fill_color = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# 应用样式
ws['B1'].style = currency_style
ws['B1'].font = bold_font
ws['B1'].fill = fill_color
ws['B1'] = total_sales  # 写入总销售额

# 保存工作簿
wb.save('output.xlsx')

6. 具体示例

• 读取和处理数据

  import pandas as pd
  
  # 读取数据
  df = pd.read_excel('data.xlsx', engine='openpyxl')
  
  # 计算总销售额
  total_sales = df['Sales'].sum()
  print(f'Total Sales: {total_sales}')
  

• 格式化Excel文件

  from openpyxl import load_workbook
  from openpyxl.styles import Font, PatternFill
  
  # 加载工作簿
  wb = load_workbook('data.xlsx')
  ws = wb.active
  
  # 定义样式
  bold_font = Font(bold=True)
  fill_color = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
  
  # 应用样式
  ws['A1'].font = bold_font
  ws['A1'].fill = fill_color
  
  # 保存工作簿
  wb.save('formatted_data.xlsx')
  

• 处理旧版Excel文件

  import xlrd
  
  # 打开文件
  workbook = xlrd.open_workbook('old_data.xls')
  
  # 选择工作表
  sheet = workbook.sheet_by_index(0)
  
  # 计算第0列的总和
  total = sum(sheet.cell_value(row, 0) for row in range(sheet.nrows) if isinstance(sheet.cell_value(row, 0), (int, float)))
  print(f'Total: {total}')
  

结论

通过pandas、openpyxl和xlrd这三个库，Python提供了强大的Excel文件处理能力。pandas适合数据分析和处理，openpyxl适合Excel文件的高级操作和格式化，而xlrd则用于读取旧版Excel文件。结合这些库，可以高效地完成Excel文件的读取、处理、格式化和保存等任务。