使用Python实现Excel表格处理自动化的方法技巧

tomatop

34人浏览 · 2026-04-01 01:24:56

tomatop · 2026-04-01 01:24:56 发布

在日常办公中，Excel是最常用的数据处理工具之一。通过Python自动化Excel操作，可以大幅提高工作效率，减少重复劳动，降低人为错误。本文将介绍几种常用的Python操作Excel的方法，并提供实用的代码示例和应用场景。

组合xlrd、xlwt、xlutils实现excel读写操作

这三个库是早期Python操作Excel的经典组合，各司其职：xlrd负责读取，xlwt负责写入，xlutils作为两者的桥梁。虽然现在有了更强大的库，但在一些特定场景下，这个组合仍然有其价值。

安装这些库

1	`pip` `install` `xlrd xlwt xlutils`

读取Excel文件

import xlrd

def read_excel_file(file_path):

"""读取Excel文件并打印内容"""

# 打开工作簿

workbook = xlrd.open_workbook(file_path)

# 获取所有工作表名称

sheet_names = workbook.sheet_names()

print(f"工作表列表: {sheet_names}")

# 遍历每个工作表

for sheet_name in sheet_names:

sheet = workbook.sheet_by_name(sheet_name)

print(f"\n工作表: {sheet_name}, 行数: {sheet.nrows}, 列数: {sheet.ncols}")

# 打印表头

if sheet.nrows > 0:

header = [sheet.cell_value(0, col) for col in range(sheet.ncols)]

print(f"表头: {header}")

# 打印数据（最多显示5行）

for row in range(1, min(6, sheet.nrows)):

row_data = [sheet.cell_value(row, col) for col in range(sheet.ncols)]

print(f"第{row}行: {row_data}")

# 使用示例

read_excel_file("员工信息.xls")

写入Excel文件

import xlwt

def create_excel_file(file_path):

"""创建新的Excel文件"""

# 创建工作簿

workbook = xlwt.Workbook(encoding='utf-8')

# 添加工作表

sheet = workbook.add_sheet('员工信息')

# 定义样式

header_style = xlwt.easyxf('font: bold on; align: horiz center')

date_style = xlwt.easyxf(num_format_str='yyyy-mm-dd')

# 写入表头

headers = ['ID', '姓名', '部门', '入职日期', '薪资']

for col, header in enumerate(headers):

sheet.write(0, col, header, header_style)

# 准备数据

data = [

[1001, '张三', '技术部', '2020-01-15', 12000],

[1002, '李四', '市场部', '2019-05-23', 15000],

[1003, '王五', '财务部', '2021-03-08', 13500],

[1004, '赵六', '人事部', '2018-11-12', 14000],

]

# 写入数据

for row, row_data in enumerate(data, 1):

for col, cell_value in enumerate(row_data):

# 对日期使用特殊格式

if col == 3: # 入职日期列

import datetime

date_parts = cell_value.split('-')

date_obj = datetime.datetime(int(date_parts[0]), int(date_parts[1]), int(date_parts[2]))

sheet.write(row, col, date_obj, date_style)

else:

sheet.write(row, col, cell_value)

# 保存文件

workbook.save(file_path)

print(f"Excel文件已创建: {file_path}")

# 使用示例

create_excel_file("新员工信息.xls")

修改现有Excel文件

import xlrd

import xlwt

from xlutils.copy import copy

def update_excel_file(file_path, employee_id, new_salary):

"""更新指定员工的薪资信息"""

# 打开原工作簿（只读模式）

rb = xlrd.open_workbook(file_path, formatting_info=True)

sheet = rb.sheet_by_index(0)

# 创建一个可写的副本

wb = copy(rb)

w_sheet = wb.get_sheet(0)

# 查找员工ID并更新薪资

found = False

for row in range(1, sheet.nrows):

if int(sheet.cell_value(row, 0)) == employee_id: # 假设ID在第一列

w_sheet.write(row, 4, new_salary) # 假设薪资在第五列

found = True

break

if found:

# 保存修改后的文件

wb.save(file_path)

print(f"已更新员工ID {employee_id} 的薪资为 {new_salary}")

else:

print(f"未找到员工ID: {employee_id}")

# 使用示例

update_excel_file("员工信息.xls", 1002, 16000)

使用openpyxl实现excel的读写修改

openpyxl是目前最流行的Python Excel处理库之一，功能全面，API友好，特别适合处理较新的Excel格式（.xlsx）。

安装openpyxl

1	`pip` `install` `openpyxl`

读取Excel文件

from openpyxl import load_workbook

def read_excel_with_openpyxl(file_path):

"""使用openpyxl读取Excel文件"""

# 加载工作簿

wb = load_workbook(file_path, read_only=True)

# 获取所有工作表名称

sheet_names = wb.sheetnames

print(f"工作表列表: {sheet_names}")

# 遍历每个工作表

for sheet_name in sheet_names:

sheet = wb[sheet_name]

print(f"\n工作表: {sheet_name}")

# 获取表格尺寸

if not sheet.max_row: # 对于read_only模式，需要遍历才能获取尺寸

print("工作表为空或使用read_only模式无法直接获取尺寸")

continue

# 打印表头

header = [cell.value for cell in next(sheet.iter_rows())]

print(f"表头: {header}")

# 打印数据（最多5行）

row_count = 0

for row in sheet.iter_rows(min_row=2): # 从第二行开始

if row_count >= 5:

break

row_data = [cell.value for cell in row]

print(f"行 {row_count + 2}: {row_data}")

row_count += 1

# 关闭工作簿

wb.close()

# 使用示例

read_excel_with_openpyxl("员工信息.xlsx")

创建新的Excel文件

from openpyxl import Workbook

from openpyxl.styles import Font, Alignment, PatternFill

from openpyxl.utils import get_column_letter

import datetime

def create_excel_with_openpyxl(file_path):

"""使用openpyxl创建格式化的Excel文件"""

# 创建工作簿

wb = Workbook()

sheet = wb.active

sheet.title = "销售数据"

# 定义样式

header_font = Font(name='Arial', size=12, bold=True, color="FFFFFF")

header_fill = PatternFill("solid", fgColor="4F81BD")

centered = Alignment(horizontal="center")

# 写入表头

headers = ['产品ID', '产品名称', '类别', '单价', '销售日期', '销售量', '销售额']

for col_num, header in enumerate(headers, 1):

cell = sheet.cell(row=1, column=col_num)

cell.value = header

cell.font = header_font

cell.fill = header_fill

cell.alignment = centered

# 准备数据

data = [

[101, '笔记本电脑', '电子产品', 5999, datetime.date(2023, 1, 15), 10, '=D2*F2'],

[102, '办公椅', '办公家具', 899, datetime.date(2023, 1, 16), 20, '=D3*F3'],

[103, '打印机', '办公设备', 1299, datetime.date(2023, 1, 18), 5, '=D4*F4'],

[104, '显示器', '电子产品', 1499, datetime.date(2023, 1, 20), 15, '=D5*F5'],

[105, '文件柜', '办公家具', 699, datetime.date(2023, 1, 22), 8, '=D6*F6'],

]

# 写入数据

for row_num, row_data in enumerate(data, 2):

for col_num, cell_value in enumerate(row_data, 1):

cell = sheet.cell(row=row_num, column=col_num)

cell.value = cell_value

if col_num == 5: # 日期列使用日期格式

cell.number_format = 'yyyy-mm-dd'

elif col_num == 7: # 销售额列使用公式和货币格式

cell.number_format = '¥#,##0.00'

# 添加合计行

total_row = len(data) + 2

sheet.cell(row=total_row, column=1).value = "合计"

sheet.cell(row=total_row, column=1).font = Font(bold=True)

# 销售量合计

sheet.cell(row=total_row, column=6).value = f"=SUM(F2:F{total_row-1})"

sheet.cell(row=total_row, column=6).font = Font(bold=True)

# 销售额合计

sheet.cell(row=total_row, column=7).value = f"=SUM(G2:G{total_row-1})"

sheet.cell(row=total_row, column=7).font = Font(bold=True)

sheet.cell(row=total_row, column=7).number_format = '¥#,##0.00'

# 调整列宽

for col in range(1, len(headers) + 1):

sheet.column_dimensions[get_column_letter(col)].width = 15

# 保存文件

wb.save(file_path)

print(f"Excel文件已创建: {file_path}")

# 使用示例

create_excel_with_openpyxl("销售数据.xlsx")

处理大型Excel文件

from openpyxl import load_workbook

import time

def process_large_excel(file_path):

"""使用read_only模式处理大型Excel文件"""

start_time = time.time()

# 使用read_only模式加载工作簿

wb = load_workbook(file_path, read_only=True)

sheet = wb.active

# 统计数据

row_count = 0

sum_value = 0

# 假设第5列是数值，我们要计算其总和

for row in sheet.iter_rows(min_row=2, values_only=True):

row_count += 1

if len(row) >= 5 and row[4] is not None:

try:

sum_value += float(row[4])

except (ValueError, TypeError):

pass

# 每处理10000行打印一次进度

if row_count % 10000 == 0:

print(f"已处理 {row_count} 行...")

# 关闭工作簿

wb.close()

end_time = time.time()

print(f"处理完成，共 {row_count} 行数据")

print(f"第5列数值总和: {sum_value}")

print(f"处理时间: {end_time - start_time:.2f} 秒")

# 使用示例（对于大型文件）

# process_large_excel("大型数据集.xlsx")

使用xlwings模块操控excel文档

xlwings是一个强大的库，可以直接与Excel应用程序交互，实现自动化操作，甚至可以调用Excel的VBA函数。

安装xlwings

1	`pip` `install` `xlwings`

基本操作

import xlwings as xw

def automate_excel_with_xlwings():

"""使用xlwings自动化Excel操作"""

# 启动Excel应用

app = xw.App(visible=True) # visible=True让Excel可见，便于观察操作过程

try:

# 创建新工作簿

wb = app.books.add()

sheet = wb.sheets[0]

sheet.name = "销售报表"

# 添加表头

sheet.range("A1").value = "产品"

sheet.range("B1").value = "一季度"

sheet.range("C1").value = "二季度"

sheet.range("D1").value = "三季度"

sheet.range("E1").value = "四季度"

sheet.range("F1").value = "年度总计"

# 设置表头格式

header_range = sheet.range("A1:F1")

header_range.color = (0, 112, 192) # 蓝色背景

header_range.font.color = (255, 255, 255) # 白色文字

header_range.font.bold = True

# 添加数据

data = [

["产品A", 100, 120, 140, 130],

["产品B", 90, 100, 110, 120],

["产品C", 80, 85, 90, 95],

]

# 写入数据

sheet.range("A2").value = data

# 添加公式计算年度总计

for i in range(len(data)):

row = i + 2 # 数据从第2行开始

sheet.range(f"F{row}").formula = f"=SUM(B{row}:E{row})"

# 添加合计行

total_row = len(data) + 2

sheet.range(f"A{total_row}").value = "合计"

sheet.range(f"A{total_row}").font.bold = True

# 添加合计公式

for col in "BCDEF":

sheet.range(f"{col}{total_row}").formula = f"=SUM({col}2:{col}{total_row-1})"

sheet.range(f"{col}{total_row}").font.bold = True

# 添加图表

chart = sheet.charts.add()

chart.set_source_data(sheet.range(f"A1:E{len(data)+1}"))

chart.chart_type = "column_clustered"

chart.name = "季度销售图表"

chart.top = sheet.range(f"A{total_row+2}").top

chart.left = sheet.range("A1").left

# 调整列宽

sheet.autofit()

# 保存文件

wb.save("xlwings_销售报表.xlsx")

print("Excel文件已创建并保存")

finally:

# 关闭工作簿和应用

wb.close()

app.quit()

# 使用示例

# automate_excel_with_xlwings()

与Excel VBA结合使用

import xlwings as xw

def run_excel_macro():

"""运行Excel中的VBA宏"""

# 打开包含宏的工作簿

wb = xw.Book("带宏的工作簿.xlsm")

try:

# 运行名为'ProcessData'的宏

wb.macro("ProcessData")()

print("宏已执行完成")

# 读取宏处理后的结果

sheet = wb.sheets["结果"]

result = sheet.range("A1:C10").value

print("处理结果:")

for row in result:

print(row)

finally:

# 保存并关闭工作簿

wb.save()

wb.close()

# 使用示例（需要有包含'ProcessData'宏的Excel文件）

# run_excel_macro()

使用Pandas轻松处理多个excel工作薄

Pandas是数据分析的利器，它提供了强大的数据结构和操作功能，特别适合处理表格数据。

安装Pandas

1	`pip` `install` `pandas`

读取Excel文件

import pandas as pd

def read_excel_with_pandas(file_path):

"""使用pandas读取Excel文件"""

# 读取所有工作表

xlsx = pd.ExcelFile(file_path)

# 获取所有工作表名称

sheet_names = xlsx.sheet_names

print(f"工作表列表: {sheet_names}")

# 遍历每个工作表

for sheet_name in sheet_names:

# 读取工作表到DataFrame

df = pd.read_excel(xlsx, sheet_name)

print(f"\n工作表: {sheet_name}, 形状: {df.shape}")

# 显示前5行数据

print("\n数据预览:")

print(df.head())

# 显示基本统计信息

print("\n数值列统计信息:")

print(df.describe())

# 使用示例

read_excel_with_pandas("销售数据.xlsx")

数据处理与分析

import pandas as pd

import matplotlib.pyplot as plt

def analyze_sales_data(file_path):

"""使用pandas分析销售数据"""

# 读取Excel文件

df = pd.read_excel(file_path)

# 显示基本信息

print("数据基本信息:")

print(df.info())

# 按类别分组统计

category_stats = df.groupby('类别').agg({

'销售量': 'sum',

'销售额': 'sum'

}).sort_values('销售额', ascending=False)

print("\n按类别统计:")

print(category_stats)

# 按月份分析销售趋势

df['月份'] = pd.to_datetime(df['销售日期']).dt.month

monthly_sales = df.groupby('月份').agg({

'销售量': 'sum',

'销售额': 'sum'

})

print("\n按月份统计:")

print(monthly_sales)

# 创建图表

plt.figure(figsize=(12, 5))

# 销售额柱状图

plt.subplot(1, 2, 1)

category_stats['销售额'].plot(kind='bar', color='skyblue')

plt.title('各类别销售额')

plt.ylabel('销售额')

plt.xticks(rotation=45)

# 月度销售趋势图

plt.subplot(1, 2, 2)

monthly_sales['销售额'].plot(marker='o', color='green')

plt.title('月度销售趋势')

plt.xlabel('月份')

plt.ylabel('销售额')

plt.tight_layout()

plt.savefig('销售分析.png')

plt.close()

print("\n分析图表已保存为'销售分析.png'")

# 返回处理后的数据

return df, category_stats, monthly_sales

# 使用示例

# analyze_sales_data("销售数据.xlsx")

合并多个Excel文件

import pandas as pd

import os

def merge_excel_files(directory, output_file):

"""合并目录下的所有Excel文件"""

# 获取目录下所有Excel文件

excel_files = [f for f in os.listdir(directory)

if f.endswith('.xlsx') or f.endswith('.xls')]

if not excel_files:

print(f"目录 {directory} 中没有找到Excel文件")

return

print(f"找到 {len(excel_files)} 个Excel文件")

# 创建一个空的DataFrame列表

dfs = []

# 读取每个Excel文件

for file in excel_files:

file_path = os.path.join(directory, file)

print(f"处理文件: {file}")

# 读取所有工作表

xlsx = pd.ExcelFile(file_path)

for sheet_name in xlsx.sheet_names:

# 读取工作表

df = pd.read_excel(xlsx, sheet_name)

# 添加文件名和工作表名列

df['源文件'] = file

df['工作表'] = sheet_name

# 添加到列表

dfs.append(df)

# 合并所有DataFrame

if dfs:

merged_df = pd.concat(dfs, ignore_index=True)

# 保存合并后的数据

merged_df.to_excel(output_file, index=False)

print(f"已将 {len(dfs)} 个工作表合并到 {output_file}")

print(f"合并后的数据形状: {merged_df.shape}")

else:

print("没有找到有效的数据表")

# 使用示例

# merge_excel_files("excel_files", "合并数据.xlsx")

实际应用场景

场景一：销售数据自动化报表

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

import pandas as pd

import matplotlib.pyplot as plt

from openpyxl import Workbook

from openpyxl.chart import BarChart, Reference, LineChart

from openpyxl.styles import Font, PatternFill, Alignment

from openpyxl.utils import get_column_letter

import datetime

def generate_sales_report(sales_data_file, output_file):

"""生成销售数据分析报表"""

# 读取销售数据

df = pd.read_excel(sales_data_file)

# 数据清洗和准备

df['销售日期'] = pd.to_datetime(df['销售日期'])

df['月份'] = df['销售日期'].dt.month

df['季度'] = df['销售日期'].dt.quarter

# 按产品和月份分组统计

product_monthly = df.pivot_table(

index='产品名称',

columns='月份',

values='销售额',

aggfunc='sum',

fill_value=0

)

# 按类别和季度分组统计

category_quarterly = df.pivot_table(

index='类别',

columns='季度',

values=['销售量', '销售额'],

aggfunc='sum',

fill_value=0

)

# 计算总计和环比

product_monthly['总计'] = product_monthly.sum(axis=1)

product_monthly = product_monthly.sort_values('总计', ascending=False)

# 创建Excel工作簿

wb = Workbook()

# 创建产品月度销售工作表

ws1 = wb.active

ws1.title = "产品月度销售"

# 写入表头

headers = ['产品名称'] + [f"{i}月" for i in sorted(product_monthly.columns[:-1])] + ['总计']

for col_num, header in enumerate(headers, 1):

cell = ws1.cell(row=1, column=col_num)

cell.value = header

cell.font = Font(bold=True)

cell.fill = PatternFill("solid", fgColor="4F81BD")

cell.alignment = Alignment(horizontal="center")

# 写入数据

for row_num, (index, data) in enumerate(product_monthly.iterrows(), 2):

ws1.cell(row=row_num, column=1).value = index # 产品名称

for col_num, value in enumerate(data.values, 2):

cell = ws1.cell(row=row_num, column=col_num)

cell.value = value

cell.number_format = '#,##0.00'

# 添加合计行

total_row = len(product_monthly) + 2

ws1.cell(row=total_row, column=1).value = "总计"

ws1.cell(row=total_row, column=1).font = Font(bold=True)

for col in range(2, len(headers) + 1):

col_letter = get_column_letter(col)

ws1.cell(row=total_row, column=col).value = f"=SUM({col_letter}2:{col_letter}{total_row-1})"

ws1.cell(row=total_row, column=col).font = Font(bold=True)

ws1.cell(row=total_row, column=col).number_format = '#,##0.00'

# 创建图表

chart = BarChart()

chart.title = "产品销售额对比"

chart.x_axis.title = "产品"

chart.y_axis.title = "销售额"

# 设置图表数据范围

data = Reference(ws1, min_col=2, min_row=1, max_row=min(11, total_row-1), max_col=len(headers)-1)

cats = Reference(ws1, min_col=1, min_row=2, max_row=min(11, total_row-1))

chart.add_data(data, titles_from_data=True)

chart.set_categories(cats)

# 添加图表到工作表

ws1.add_chart(chart, "A" + str(total_row + 2))

# 创建类别季度销售工作表

ws2 = wb.create_sheet(title="类别季度分析")

# 重新组织数据以便于写入

category_data = []

for category in category_quarterly.index:

row = [category]

for quarter in sorted(df['季度'].unique()):

row.append(category_quarterly.loc[category, ('销售量', quarter)])

row.append(category_quarterly.loc[category, ('销售额', quarter)])

category_data.append(row)

# 写入表头

headers = ['类别']

for quarter in sorted(df['季度'].unique()):

headers.extend([f"Q{quarter}销量", f"Q{quarter}销售额"])

for col_num, header in enumerate(headers, 1):

cell = ws2.cell(row=1, column=col_num)

cell.value = header

cell.font = Font(bold=True)

cell.fill = PatternFill("solid", fgColor="4F81BD")

cell.alignment = Alignment(horizontal="center")

# 写入数据

for row_num, row_data in enumerate(category_data, 2):

for col_num, value in enumerate(row_data, 1):

cell = ws2.cell(row=row_num, column=col_num)

cell.value = value

if col_num % 2 == 0: # 销量列

cell.number_format = '#,##0'

elif col_num % 2 == 1 and col_num > 1: # 销售额列

cell.number_format = '#,##0.00'

# 创建折线图

line_chart = LineChart()

line_chart.title = "季度销售趋势"

line_chart.x_axis.title = "季度"

line_chart.y_axis.title = "销售额"

# 设置图表数据范围（只取销售额列）

quarters = len(df['季度'].unique())

data = Reference(ws2, min_col=3, min_row=1, max_row=len(category_data)+1, max_col=2*quarters, min_col_offset=1)

cats = Reference(ws2, min_col=1, min_row=2, max_row=len(category_data)+1)

line_chart.add_data(data, titles_from_data=True)

line_chart.set_categories(cats)

# 添加图表到工作表

ws2.add_chart(line_chart, "A" + str(len(category_data) + 3))

# 调整列宽

for ws in [ws1, ws2]:

for col in range(1, ws.max_column + 1):

ws.column_dimensions[get_column_letter(col)].width = 15

# 保存工作簿

wb.save(output_file)

print(f"销售报表已生成: {output_file}")

# 使用示例

# generate_sales_report("原始销售数据.xlsx", "销售分析报表.xlsx")

场景二：库存管理系统

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

import pandas as pd

from openpyxl import load_workbook, Workbook

from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

from openpyxl.utils import get_column_letter

import datetime

import os

class InventoryManager:

def __init__(self, inventory_file):

"""初始化库存管理系统"""

self.inventory_file = inventory_file

# 如果文件不存在，创建一个新的库存文件

if not os.path.exists(inventory_file):

self._create_new_inventory_file()

# 加载库存数据

self.load_inventory()

def _create_new_inventory_file(self):

"""创建新的库存文件"""

wb = Workbook()

ws = wb.active

ws.title = "库存"

# 设置表头

headers = ['产品ID', '产品名称', '类别', '供应商', '单价', '库存量', '库存价值', '最后更新']

for col_num, header in enumerate(headers, 1):

cell = ws.cell(row=1, column=col_num)

cell.value = header

cell.font = Font(bold=True)

cell.fill = PatternFill("solid", fgColor="4F81BD")

cell.alignment = Alignment(horizontal="center")

# 设置示例数据

sample_data = [

[1001, '笔记本电脑', '电子产品', 'A供应商', 5999, 10, '=E2*F2', datetime.datetime.now()],

[1002, '办公椅', '办公家具', 'B供应商', 899, 20, '=E3*F3', datetime.datetime.now()],

]

for row_num, row_data in enumerate(sample_data, 2):

for col_num, value in enumerate(row_data, 1):

cell = ws.cell(row=row_num, column=col_num)

cell.value = value

if col_num == 5: # 单价列

cell.number_format = '¥#,##0.00'

elif col_num == 7: # 库存价值列

cell.number_format = '¥#,##0.00'

elif col_num == 8: # 日期列

cell.number_format = 'yyyy-mm-dd hh:mm:ss'

# 创建入库记录工作表

ws_in = wb.create_sheet(title="入库记录")

headers = ['记录ID', '产品ID', '产品名称', '入库数量', '单价', '总价值', '供应商', '入库日期', '操作人']

for col_num, header in enumerate(headers, 1):

cell = ws_in.cell(row=1, column=col_num)

cell.value = header

cell.font = Font(bold=True)

cell.fill = PatternFill("solid", fgColor="4F81BD")

cell.alignment = Alignment(horizontal="center")

# 创建出库记录工作表

ws_out = wb.create_sheet(title="出库记录")

headers = ['记录ID', '产品ID', '产品名称', '出库数量', '单价', '总价值', '客户', '出库日期', '操作人']

for col_num, header in enumerate(headers, 1):

cell = ws_out.cell(row=1, column=col_num)

cell.value = header

cell.font = Font(bold=True)

cell.fill = PatternFill("solid", fgColor="4F81BD")

cell.alignment = Alignment(horizontal="center")

# 调整所有工作表的列宽

for ws in wb.worksheets:

for col in range(1, len(headers) + 1):

ws.column_dimensions[get_column_letter(col)].width = 15

# 保存文件

wb.save(self.inventory_file)

print(f"已创建新的库存文件: {self.inventory_file}")

def load_inventory(self):

"""加载库存数据"""

# 使用pandas读取Excel文件的所有工作表

self.inventory_df = pd.read_excel(self.inventory_file, sheet_name="库存")

self.in_records_df = pd.read_excel(self.inventory_file, sheet_name="入库记录")

self.out_records_df = pd.read_excel(self.inventory_file, sheet_name="出库记录")

print("库存数据已加载")

print(f"当前库存: {len(self.inventory_df)} 种产品")

print(f"入库记录: {len(self.in_records_df)} 条")

print(f"出库记录: {len(self.out_records_df)} 条")

def add_product(self, product_id, name, category, supplier, price, quantity):

"""添加新产品到库存"""

# 检查产品ID是否已存在

if product_id in self.inventory_df['产品ID'].values:

print(f"错误: 产品ID {product_id} 已存在")

return False

# 创建新产品记录

new_product = {

'产品ID': product_id,

'产品名称': name,

'类别': category,

'供应商': supplier,

'单价': price,

'库存量': quantity,

'库存价值': price * quantity,

'最后更新': datetime.datetime.now()

}

# 添加到DataFrame

self.inventory_df = self.inventory_df.append(new_product, ignore_index=True)

# 添加入库记录

in_record = {

'记录ID': len(self.in_records_df) + 1,

'产品ID': product_id,

'产品名称': name,

'入库数量': quantity,

'单价': price,

'总价值': price * quantity,

'供应商': supplier,

'入库日期': datetime.datetime.now(),

'操作人': 'system'

}

self.in_records_df = self.in_records_df.append(in_record, ignore_index=True)

# 保存更改

self._save_to_excel()

print(f"已添加新产品: {name} (ID: {product_id})")

return True

def update_stock(self, product_id, quantity_change, is_incoming=True, customer_or_supplier=None, operator='system'):

"""更新库存"""

# 查找产品

product_mask = self.inventory_df['产品ID'] == product_id

if not any(product_mask):

print(f"错误: 产品ID {product_id} 不存在")

return False

# 获取产品信息

product_idx = product_mask.idxmax()

product = self.inventory_df.loc[product_idx]

# 计算新库存量

new_quantity = product['库存量'] + quantity_change if is_incoming else product['库存量'] - quantity_change

# 检查库存是否足够（出库时）

if not is_incoming and new_quantity < 0:

print(f"错误: 产品 {product['产品名称']} 库存不足，当前库存: {product['库存量']}")

return False

# 更新库存

self.inventory_df.at[product_idx, '库存量'] = new_quantity

self.inventory_df.at[product_idx, '库存价值'] = new_quantity * product['单价']

self.inventory_df.at[product_idx, '最后更新'] = datetime.datetime.now()

# 添加记录

if is_incoming:

# 入库记录

record = {

'记录ID': len(self.in_records_df) + 1,

'产品ID': product_id,

'产品名称': product['产品名称'],

'入库数量': quantity_change,

'单价': product['单价'],

'总价值': quantity_change * product['单价'],

'供应商': customer_or_supplier or product['供应商'],

'入库日期': datetime.datetime.now(),

'操作人': operator

}

self.in_records_df = self.in_records_df.append(record, ignore_index=True)

else:

# 出库记录

record = {

'记录ID': len(self.out_records_df) + 1,

'产品ID': product_id,

'产品名称': product['产品名称'],

'出库数量': quantity_change,

'单价': product['单价'],

'总价值': quantity_change * product['单价'],

'客户': customer_or_supplier or '未指定',

'出库日期': datetime.datetime.now(),

'操作人': operator

}

self.out_records_df = self.out_records_df.append(record, ignore_index=True)

# 保存更改

self._save_to_excel()

action = "入库" if is_incoming else "出库"

print(f"已{action} {product['产品名称']} {quantity_change} 个，当前库存: {new_quantity}")

return True

def generate_inventory_report(self, output_file):

"""生成库存报表"""

# 创建一个新的工作簿

wb = Workbook()

ws = wb.active

ws.title = "库存报表"

# 添加报表标题

ws.merge_cells('A1:H1')

title_cell = ws['A1']

title_cell.value = "库存状况报表"

title_cell.font = Font(size=16, bold=True)

title_cell.alignment = Alignment(horizontal="center")

# 添加报表生成时间

ws.merge_cells('A2:H2')

date_cell = ws['A2']

date_cell.value = f"生成时间: {datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"

date_cell.alignment = Alignment(horizontal="center")

# 添加表头

headers = ['产品ID', '产品名称', '类别', '供应商', '单价', '库存量', '库存价值', '库存状态']

for col_num, header in enumerate(headers, 1):

cell = ws.cell(row=4, column=col_num)

cell.value = header

cell.font = Font(bold=True)

cell.fill = PatternFill("solid", fgColor="4F81BD")

cell.alignment = Alignment(horizontal="center")

# 添加数据

# 计算库存状态

def get_stock_status(row):

if row['库存量'] <= 0:

return "缺货"

elif row['库存量'] < 5:

return "库存不足"

elif row['库存量'] > 20:

return "库存过多"

else:

return "正常"

# 添加库存状态列

self.inventory_df['库存状态'] = self.inventory_df.apply(get_stock_status, axis=1)

# 按类别和库存状态排序

sorted_df = self.inventory_df.sort_values(['类别', '库存状态'])

# 写入数据

for row_num, (_, row) in enumerate(sorted_df.iterrows(), 5):

for col_num, column in enumerate(headers, 1):

cell = ws.cell(row=row_num, column=col_num)

value = row[column] if column in row else ""

cell.value = value

# 设置格式

if column == '单价':

cell.number_format = '¥#,##0.00'

elif column == '库存价值':

cell.number_format = '¥#,##0.00'

# 设置库存状态的颜色

if column == '库存状态':

if value == "缺货":

cell.fill = PatternFill("solid", fgColor="FF0000")

elif value == "库存不足":

cell.fill = PatternFill("solid", fgColor="FFC000")

elif value == "库存过多":

cell.fill = PatternFill("solid", fgColor="92D050")

# 添加合计行

total_row = len(sorted_df) + 5

ws.cell(row=total_row, column=1).value = "合计"

ws.cell(row=total_row, column=1).font = Font(bold=True)

# 计算总库存量和总价值

ws.cell(row=total_row, column=6).value = sorted_df['库存量'].sum()

ws.cell(row=total_row, column=6).font = Font(bold=True)

ws.cell(row=total_row, column=7).value = sorted_df['库存价值'].sum()

ws.cell(row=total_row, column=7).font = Font(bold=True)

ws.cell(row=total_row, column=7).number_format = '¥#,##0.00'

# 添加类别统计

ws.cell(row=total_row + 2, column=1).value = "类别统计"

ws.cell(row=total_row + 2, column=1).font = Font(bold=True)

category_stats = sorted_df.groupby('类别').agg({

'产品ID': 'count',

'库存量': 'sum',

'库存价值': 'sum'

}).reset_index()

# 写入类别统计表头

category_headers = ['类别', '产品数量', '总库存量', '总库存价值']

for col_num, header in enumerate(category_headers, 1):

cell = ws.cell(row=total_row + 3, column=col_num)

cell.value = header

cell.font = Font(bold=True)

cell.fill = PatternFill("solid", fgColor="A5A5A5")

# 写入类别统计数据

for row_num, (_, row) in enumerate(category_stats.iterrows(), total_row + 4):

ws.cell(row=row_num, column=1).value = row['类别']

ws.cell(row=row_num, column=2).value = row['产品ID']

ws.cell(row=row_num, column=3).value = row['库存量']

ws.cell(row=row_num, column=4).value = row['库存价值']

ws.cell(row=row_num, column=4).number_format = '¥#,##0.00'

# 调整列宽

for col in range(1, len(headers) + 1):

ws.column_dimensions[get_column_letter(col)].width = 15

# 保存报表

wb.save(output_file)

print(f"库存报表已生成: {output_file}")

return output_file

def _save_to_excel(self):

"""保存数据到Excel文件"""

with pd.ExcelWriter(self.inventory_file, engine='openpyxl') as writer:

self.inventory_df.to_excel(writer, sheet_name="库存", index=False)

self.in_records_df.to_excel(writer, sheet_name="入库记录", index=False)

self.out_records_df.to_excel(writer, sheet_name="出库记录", index=False)

# 使用示例

# inventory = InventoryManager("库存管理.xlsx")

# inventory.add_product(1003, "打印机", "办公设备", "C供应商", 1299, 5)

# inventory.update_stock(1001, 5, is_incoming=True, customer_or_supplier="A供应商", operator="张三")

# inventory.update_stock(1002, 2, is_incoming=False, customer_or_supplier="客户A", operator="李四")

# inventory.generate_inventory_report("库存报表.xlsx")

通过这些代码示例和实际应用场景，你可以轻松掌握Python Excel自动化的各种技巧，大幅提高工作效率。无论是简单的数据处理，还是复杂的报表生成，Python都能帮你轻松应对。

AI Agent技术社区

Agent 垂直技术社区，欢迎活跃、内容共建。

更多推荐

一文看懂BeltDrivenEnder3版本与变体：V3.6/V3.7、单Z双Z、V轮与导轨到底怎么选？

你是否受够了 Ender 3 原厂 Z 轴丝杆的弯曲、抖动与错位问题？**BeltDrivenEnder3** 是一款火爆开源社区的皮带驱动 Ender 3 改装项目（Belt Driven Z Mod），它用皮带传动替代传统丝杆，彻底解决 X 轴右侧悬垂、Z 轴层纹等老毛病，且**无需刷固件**。不过，面对 V3.6/V3.7 版本、单Z/双Z、V轮/MGN 导轨等一大堆变体，新手往往一头雾水。

AI Agent技术社区

RapidSMS 测试完整指南：脚本化测试与测试工具链从零实战

RapidSMS 是一个用 Python 构建短信应用的开源框架，而 **RapidSMS 测试** 则是保证消息路由、应用处理逻辑稳定可靠的关键一环。本文面向新手，完整介绍 RapidSMS 的脚本化测试（Scripted Testing）与内置测试工具链（Test Harness），从环境准备、编写第一个测试用例到运行整个测试套件，带你从零实战、快速上手。[![RapidSMS 测试架构图

AI Agent技术社区

Mountify 是什么？用 OverlayFS 实现全局模块挂载的开源神器完整解析

**Mountify** 是一款基于 **OverlayFS** 实现**全局模块挂载**的开源神器，它同时兼容 KernelSU、APatch 与 Magisk，能让你的系统模块以一种更隐蔽、更接近 OEM 原厂的方式"整体叠加上去"。本文将从原理、安装、配置到进阶玩法，为你带来一份面向新手的完整解析。## 一、Mountify 是什么？先看它的核心定位简单来说，Mountify 是一个