python——Excel处理

读取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)

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')

读取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}')

读取和处理数据

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}')