项目文档
# Excel库存分析系统
一个用于分析Excel库存数据的系统，特别专注于查询零覆盖门店（门店库存、门店在途、店间在途均为零的门店）。
## 功能特点
- 直接上传自己在小米pos系统中导出的产品覆盖数据表
- 支持多产品同时查询零覆盖门店
- 自动统计各城市库存总量
- 可视化展示库存数据分布
- 支持多种Excel格式（.xlsx, .xls）
## 目录说明
- `inventory_files`目录是门店库存概览数据源（支持前端上传覆盖）
- `css`目录是all.min.css的存放目录（index.html中改用链接了）不知道啥原因本地的无法使用链接可以
## 系统要求
- Python 3.6+
- 相关依赖包（见requirements.txt）
## 安装步骤
1. 克隆或下载本项目到本地
2. 安装依赖包：
pip install -r requirements.txt
## 使用方法
1. 运行系统：
python app.py
2. 系统会自动创建一个名为`inventory_files`的文件夹
3. 将您的Excel库存文件放入`inventory_files`文件夹中，确保文件包含以下列：
- 市（城市）
- 门店代码
- 机型代号（产品型号）
- 门店库存
- 门店在途
- 店间在途
4. 打开浏览器，访问 http://127.0.0.1:5000
5. 点击"加载/刷新数据"按钮加载库存数据
6. 在产品列表中选择一个或多个产品，点击"查询零覆盖门店"查看结果
## 注意事项
- 如果Excel文件格式不正确，系统会显示相应的错误提示
- 系统会加载`inventory_files`文件夹中第一个找到的Excel文件
- 更换Excel文件后，需要点击"加载/刷新数据"按钮重新加载
## 常见问题
Q: 系统提示"未找到Excel文件"怎么办？
A: 请检查`inventory_files`文件夹中是否有Excel文件，且文件格式正确（.xlsx或.xls）
Q: 如何更新库存数据？
A: 程序运行后前端点击上传文件（要求是.xlsx,xls文件）
Q: 零覆盖门店的定义是什么？
A: 零覆盖门店指的是门店库存、门店在途、店间在途三均为零的门店
系统后端（基于py）
import glob
import os
from io import BytesIO
import pandas as pd
import xlsxwriter
from flask import Flask, jsonify, send_from_directory, request, make_response
from flask import send_file
from flask_cors import CORS
app = Flask(__name__, static_folder='.', static_url_path='')
CORS(app)
EXCEL_DIRECTORY = "inventory_files"
parsed_data = None
city_summary = None
product_summary = None
products = None # 存储机型列表
total_stores = 0
loaded_filename = None
if not os.path.exists(EXCEL_DIRECTORY):
os.makedirs(EXCEL_DIRECTORY)
print(f"已创建Excel文件目录: {EXCEL_DIRECTORY}")
print(f"请将您的Excel库存文件放入此目录")
def load_excel_files():
"""加载本地目录中的Excel文件，处理空格、异常值和空产品型号"""
global parsed_data, city_summary, product_summary, products, total_stores, loaded_filename
excel_files = glob.glob(os.path.join(EXCEL_DIRECTORY, "*.xlsx")) + \
glob.glob(os.path.join(EXCEL_DIRECTORY, "*.xls"))
if not excel_files:
return {
"success": False,
"error": f"在 {EXCEL_DIRECTORY} 目录中未找到Excel文件",
"city_summary": [],
"product_summary": [],
"products": [],
"total_stores": 0,
"total_products": 0
}
loaded_filename = excel_files[0]
try:
df = pd.read_excel(loaded_filename)
# 清理列名中的空格
df.columns = df.columns.str.strip()
# 检查必要列
required_columns = [
'时间', '市', '区', '门店名称', '门店代码',
'机型', '门店库存', '门店在途', '店间在途'
]
missing_columns = [col for col in required_columns if col not in df.columns]
if missing_columns:
return {
"success": False,
"error": f"Excel文件缺少必要的列: {', '.join(missing_columns)}",
"city_summary": [],
"product_summary": [],
"products": [],
"total_stores": 0,
"total_products": 0
}
# 清理文本列中的空格
text_columns = ['市', '区', '门店名称', '门店代码', '机型']
for col in text_columns:
df[col] = df[col].astype(str).str.strip()
df[col] = df[col].replace('', None)
# 关键修改：过滤掉空的或无效的产品型号
df = df[df['机型'].notna() & (df['机型'] != '') & (df['机型'] != 'nan')]
# 处理时间列
if '时间' in df.columns:
try:
df['时间'] = pd.to_datetime(df['时间'], errors='coerce')
df['时间'] = df['时间'].dt.strftime('%Y-%m-%d %H:%M:%S')
df['时间'] = df['时间'].fillna('未知时间')
except Exception as e:
print(f"时间格式处理警告: {e}")
df['时间'] = df['时间'].astype(str).str.strip().fillna('未知时间')
# 处理数值列
numeric_columns = ['门店库存', '门店在途', '店间在途']
for col in numeric_columns:
df[col] = df[col].astype(str).str.strip()
df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)
df[col] = df[col].astype(int)
parsed_data = df.to_dict('records')
# 计算各城市库存总量
city_summary = df.groupby('市').agg({
'门店库存': 'sum',
'门店在途': 'sum',
'店间在途': 'sum'
}).reset_index().fillna(0).to_dict('records')
# 计算各产品库存总量
product_summary = df.groupby('机型').agg({
'门店库存': 'sum',
'门店在途': 'sum',
'店间在途': 'sum'
}).reset_index().fillna(0).to_dict('records')
# 获取所有产品列表（已过滤空值）
products = df['机型'].dropna().unique().tolist()
# 再次过滤可能的无效值
products = [p for p in products if p and p.strip() and p.lower() != 'nan']
# 获取总门店数
total_stores = df['门店代码'].dropna().nunique()
return {
"success": True,
"filename": os.path.basename(loaded_filename),
"city_summary": city_summary,
"product_summary": product_summary,
"products": products,
"total_stores": total_stores,
"total_products": len(products)
}
except Exception as e:
return {
"success": False,
"error": f'文件解析错误: {str(e).replace("'", "\"")}',
"city_summary": [],
"product_summary": [],
"products": [],
"total_stores": 0,
"total_products": 0
}
@app.route('/')
def index():
return send_from_directory(app.static_folder, 'index.html')
@app.route('/upload', methods=['POST'])
def upload_file():
"""处理文件上传并覆盖现有文件"""
if 'file' not in request.files:
return jsonify({"success": False, "error": "未找到文件"})
file = request.files['file']
if file.filename == '':
return jsonify({"success": False, "error": "未选择文件"})
# 验证文件类型
allowed_extensions = {'xlsx', 'xls'}
if '.' not in file.filename or \
file.filename.rsplit('.', 1)[1].lower() not in allowed_extensions:
return jsonify({"success": False, "error": "只允许上传.xlsx和.xls格式的文件"})
try:
# 清除现有文件
excel_files = glob.glob(os.path.join(EXCEL_DIRECTORY, "*.xlsx")) + \
glob.glob(os.path.join(EXCEL_DIRECTORY, "*.xls"))
for f in excel_files:
os.remove(f)
# 保存新文件
filename = file.filename
file_path = os.path.join(EXCEL_DIRECTORY, filename)
file.save(file_path)
# 重新加载数据
load_excel_files()
return jsonify({"success": True, "message": "文件上传成功"})
except Exception as e:
return jsonify({"success": False, "error": f"文件处理错误: {str(e)}"})
@app.route('/load', methods=['GET'])
def load_data():
return jsonify(load_excel_files())
@app.route('/download', methods=['GET'])
def download_results():
"""下载查询结果为Excel文件（支持判断逻辑）"""
global parsed_data
if parsed_data is None:
load_result = load_excel_files()
if not load_result["success"]:
return jsonify({"success": False, "error": "数据未加载，请检查Excel文件"})
# 获取查询的产品和判断逻辑
selected_products = request.args.getlist('products')
zero_logic = request.args.get('zero_logic', 'all')
if not selected_products:
return jsonify({"success": False, "error": "请选择至少一个产品"})
try:
df = pd.DataFrame(parsed_data)
# 过滤掉可能的空值或NaN产品
selected_products = [p for p in selected_products if p and p.strip() and p.lower() != 'nan']
if not selected_products:
return jsonify({"success": False, "error": "所选产品均无效，请重新选择"})
filtered_df = df[df['机型'].isin(selected_products)]
# 按门店+机型分组，合并多版本数据
grouped = filtered_df.groupby(['门店代码', '机型']).agg({
'时间': 'first',
'市': 'first',
'区': 'first',
'门店名称': 'first',
'门店库存': 'sum',
'门店在途': 'sum',
'店间在途': 'sum'
}).reset_index()
# 标记每个产品是否为零覆盖
grouped['is_zero_coverage'] = (
(grouped['门店库存'] == 0) &
(grouped['门店在途'] == 0) &
(grouped['店间在途'] == 0)
)
# 按门店分组，根据判断逻辑筛选
store_groups = grouped.groupby('门店代码')
if zero_logic == 'all':
# 全部产品均为零覆盖才算零覆盖门店
zero_coverage = pd.DataFrame()
for store_code, group in store_groups:
if len(group) == len(selected_products) and group['is_zero_coverage'].all():
zero_coverage = pd.concat([zero_coverage, group])
else:
# 任一产品为零覆盖就算零覆盖门店
zero_coverage = pd.DataFrame()
for store_code, group in store_groups:
if group['is_zero_coverage'].any():
zero_coverage_products = group[group['is_zero_coverage']]
zero_coverage = pd.concat([zero_coverage, zero_coverage_products])
# 清理不需要的列
if 'is_zero_coverage' in zero_coverage.columns:
zero_coverage = zero_coverage.drop(columns=['is_zero_coverage'])
# 清理空值
zero_coverage = zero_coverage.fillna({
'时间': '未知时间',
'市': '',
'区': '',
'门店名称': '',
'门店代码': '',
'机型': '',
'门店库存': 0,
'门店在途': 0,
'店间在途': 0
})
# 按产品在选择列表中的顺序排序
zero_coverage['机型'] = pd.Categorical(
zero_coverage['机型'],
categories=selected_products,
ordered=True
)
zero_coverage = zero_coverage.sort_values('机型')
# 准备下载的Excel文件
output = BytesIO()
with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
zero_coverage.to_excel(writer, index=False, sheet_name='零覆盖门店')
# 添加标题行格式
worksheet = writer.sheets['零覆盖门店']
header_format = writer.book.add_format({
'bold': True,
'bg_color': '#3B82F6',
'font_color': 'white',
'border': 1
})
# 应用标题格式
for col_num, value in enumerate(zero_coverage.columns.values):
worksheet.write(0, col_num, value, header_format)
# 调整列宽
worksheet.set_column('A:I', 15)
output.seek(0)
# 生成文件名，包含判断逻辑
logic_suffix = "全部为零" if zero_logic == 'all' else "任一为零"
filename = f"零覆盖门店查询结果_{logic_suffix}_{'_'.join(selected_products[:3])}{'...' if len(selected_products) > 3 else ''}.xlsx"
return send_file(
output,
mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
as_attachment=True,
download_name=filename
)
except Exception as e:
return jsonify({"success": False, "error": f"下载错误: {str(e).replace('"', "'")}"})
@app.route('/query', methods=['POST'])
def query_products():
"""查询零覆盖门店（增加判断逻辑参数）"""
global parsed_data
if parsed_data is None:
load_result = load_excel_files()
if not load_result["success"]:
return jsonify({"success": False, "error": "数据未加载，请检查Excel文件"})
try:
data = request.get_json()
selected_products = data.get('products', [])
zero_logic = data.get('zero_logic', 'all') # 获取判断逻辑，默认为全部为零
if not selected_products:
return jsonify({"success": False, "error": "请选择至少一个产品"})
# 过滤掉可能的空值或NaN产品
selected_products = [p for p in selected_products if p and p.strip() and p.lower() != 'nan']
if not selected_products:
return jsonify({"success": False, "error": "所选产品均无效，请重新选择"})
df = pd.DataFrame(parsed_data)
filtered_df = df[df['机型'].isin(selected_products)]
# 按门店+机型分组，合并多版本数据
grouped = filtered_df.groupby(['门店代码', '机型']).agg({
'时间': 'first',
'市': 'first',
'区': 'first',
'门店名称': 'first',
'门店库存': 'sum',
'门店在途': 'sum',
'店间在途': 'sum'
}).reset_index()
# 标记每个产品是否为零覆盖
grouped['is_zero_coverage'] = (
(grouped['门店库存'] == 0) &
(grouped['门店在途'] == 0) &
(grouped['店间在途'] == 0)
)
# 按门店分组，根据判断逻辑筛选
store_groups = grouped.groupby('门店代码')
if zero_logic == 'all':
# 全部产品均为零覆盖才算零覆盖门店
# 找到所有产品都为零覆盖的门店
zero_coverage_stores = []
for store_code, group in store_groups:
# 检查该门店的所有选中产品是否都为零覆盖
if len(group) == len(selected_products) and group['is_zero_coverage'].all():
zero_coverage_stores.extend(group.to_dict('records'))
else:
# 任一产品为零覆盖就算零覆盖门店
# 找到至少有一个产品为零覆盖的门店
zero_coverage_stores = []
for store_code, group in store_groups:
if group['is_zero_coverage'].any():
# 只添加那些为零覆盖的产品记录
zero_coverage_products = group[group['is_zero_coverage']]
zero_coverage_stores.extend(zero_coverage_products.to_dict('records'))
return jsonify({
"success": True,
"query_products": selected_products,
"zero_coverage_count": len(zero_coverage_stores),
"zero_coverage_stores": zero_coverage_stores
})
except Exception as e:
return jsonify({"success": False, "error": f"查询错误: {str(e).replace('"', "'")}"})
if __name__ == '__main__':
pd.set_option('display.unicode.east_asian_width', True)
load_excel_files()
app.run(host='0.0.0.0', port=5000, debug=True)
前端文件（基于HTML）
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Excel库存分析系统</title>
<!-- Tailwind CSS -->
<script src="https://cdn.tailwindcss.com"></script>
<!-- Font Awesome -->
<!-- 在 <head> 标签内添加 -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
<!-- Chart.js -->
<script src="chart.umd.min.js"></script>
<!-- 自定义配置 -->
<script>
tailwind.config = {
theme: {
extend: {
colors: {
primary: '#3B82F6',
secondary: '#64748B',
success: '#10B981',
warning: '#F59E0B',
danger: '#EF4444',
info: '#3B82F6',
light: '#F8FAFC',
dark: '#1E293B'
},
fontFamily: {
sans: ['Inter', 'system-ui', 'sans-serif'],
},
}
}
}
</script>
<style type="text/tailwindcss">
@layer utilities {
.content-auto {
content-visibility: auto;
}
.transition-custom {
transition: all 0.3s ease;
}
.shadow-custom {
box-shadow: 0 4px 20px rgba(0, 0, 0, 0.08);
}
.hover-lift {
transition: transform 0.2s ease, box-shadow 0.2s ease;
}
.hover-lift:hover {
transform: translateY(-2px);
box-shadow: 0 6px 25px rgba(0, 0, 0, 0.12);
}
}
.down {
background-color: #67e55a;
}
</style>
</head>
<body class="bg-gray-50 font-sans text-gray-800 min-h-screen flex flex-col">
<!-- 顶部导航栏 -->
<header class="bg-white shadow-sm sticky top-0 z-50">
<div class="container mx-auto px-4 py-4 flex justify-between items-center">
<div class="flex items-center space-x-3">
<div class="bg-primary text-white p-2 rounded-lg">
<i class="fas fa-chart-line text-xl"></i>
</div>
<h1 class="text-xl font-bold text-gray-800">Excel库存分析系统</h1>
</div>
<div class="flex items-center space-x-4">
<button id="helpBtn" class="text-gray-600 hover:text-primary transition-custom">
<i class="fas fa-question-circle text-lg"></i>
</button>
<button id="themeToggle" class="text-gray-600 hover:text-primary transition-custom">
<i class="fas fa-moon text-lg"></i>
</button>
</div>
</div>
</header>
<!-- 主要内容区域 -->
<main class="flex-grow container mx-auto px-4 py-8">
<!-- 文件加载区域 -->
<section id="loadSection" class="mb-8">
<div class="bg-white rounded-xl shadow-custom p-6 hover-lift">
<h2 class="text-xl font-semibold mb-4 flex items-center">
<i class="fas fa-folder-open text-primary mr-2"></i>
加载本地库存文件
</h2>
<div class="text-center">
<p class="text-gray-600 mb-4">系统将加载 <code
class="bg-gray-100 px-2 py-1 rounded">inventory_files</code> 目录中的Excel文件</p>
<div class="flex flex-col sm:flex-row justify-center gap-4">
<button id="loadBtn"
class="bg-primary text-white px-6 py-3 rounded-lg hover:bg-primary/90 transition-custom flex items-center justify-center">
<i class="fas fa-sync-alt mr-2"></i>加载/刷新数据
</button>
<button id="showDirBtn"
class="bg-gray-200 text-gray-700 px-6 py-3 rounded-lg hover:bg-gray-300 transition-custom flex items-center justify-center">
<i class="fas fa-folder mr-2"></i>上传新文件
</button>
<!-- 隐藏的文件上传控件 -->
<input type="file" id="fileUpload" accept=".xlsx,.xls" class="hidden"/>
</div>
<p id="loadedFileName" class="mt-4 text-sm text-gray-500 hidden"></p>
</div>
</div>
</section>
<!-- 数据概览区域 -->
<section id="overviewSection" class="mb-8 hidden">
<div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-4 gap-6">
<div class="bg-white rounded-xl shadow-custom p-6 hover-lift">
<div class="flex items-center justify-between">
<div>
<p class="text-gray-600 text-sm">总门店数</p>
<p class="text-2xl font-bold text-gray-800" id="totalStores">0</p>
</div>
<div class="bg-blue-100 text-primary p-3 rounded-lg">
<i class="fas fa-store text-xl"></i>
</div>
</div>
</div>
<div class="bg-white rounded-xl shadow-custom p-6 hover-lift">
<div class="flex items-center justify-between">
<div>
<p class="text-gray-600 text-sm">总产品数</p>
<p class="text-2xl font-bold text-gray-800" id="totalProducts">0</p>
</div>
<div class="bg-green-100 text-success p-3 rounded-lg">
<i class="fas fa-box text-xl"></i>
</div>
</div>
</div>
<div class="bg-white rounded-xl shadow-custom p-6 hover-lift">
<div class="flex items-center justify-between">
<div>
<p class="text-gray-600 text-sm">总库存数量</p>
<p class="text-2xl font-bold text-gray-800" id="totalInventory">0</p>
</div>
<div class="bg-yellow-100 text-warning p-3 rounded-lg">
<i class="fas fa-warehouse text-xl"></i>
</div>
</div>
</div>
<div class="bg-white rounded-xl shadow-custom p-6 hover-lift">
<div class="flex items-center justify-between">
<div>
<p class="text-gray-600 text-sm">总在途数量</p>
<p class="text-2xl font-bold text-gray-800" id="totalInTransit">0</p>
</div>
<div class="bg-purple-100 text-purple-600 p-3 rounded-lg">
<i class="fas fa-truck text-xl"></i>
</div>
</div>
</div>
</div>
</section>
<!-- 产品查询区域 -->
<section id="querySection" class="mb-8 hidden">
<div class="bg-white rounded-xl shadow-custom p-6 hover-lift">
<h2 class="text-xl font-semibold mb-4 flex items-center">
<i class="fas fa-search text-primary mr-2"></i>
产品零覆盖门店查询
</h2>
<!-- 在产品选择区域添加判断逻辑选项 -->
<div class="mb-4">
<label class="block text-gray-700 mb-2 font-medium">零覆盖判断逻辑选择</label>
<div class="flex items-center space-x-4">
<div class="flex items-center">
<input type="radio" id="allZero" name="zeroLogic" value="all" checked
class="w-4 h-4 text-primary border-gray-300 rounded focus:ring-primary">
<label for="allZero" class="ml-2 text-gray-700 text-sm cursor-pointer">
选择产品均没有才算
</label>
</div>
<div class="flex items-center">
<input type="radio" id="anyZero" name="zeroLogic" value="any"
class="w-4 h-4 text-primary border-gray-300 rounded focus:ring-primary">
<label for="anyZero" class="ml-2 text-gray-700 text-sm cursor-pointer">
任一产品为零覆盖就算
</label>
</div>
</div>
</div>
<div class="mb-4">
<label class="block text-gray-700 mb-2 font-medium">选择产品（可多选）</label>
<div class="grid grid-cols-2 sm:grid-cols-3 md:grid-cols-4 lg:grid-cols-6 gap-3" id="productCheckboxes">
<!-- 产品复选框将通过JavaScript动态生成 -->
</div>
</div>
<button id="queryBtn"
class="bg-primary text-white px-6 py-3 rounded-lg hover:bg-primary/90 transition-custom flex items-center justify-center w-full sm:w-auto">
<i class="fas fa-search mr-2"></i>查询零覆盖门店
</button>
<br>
<button id="downloadBtn"
class="down text-white px-6 py-3 rounded-lg hover:bg-primary/90 transition-custom flex items-center justify-center w-full sm:w-auto">
<i class="fas fa-folder mr-2"></i>下载零覆盖门店
</button>
</div>
</section>
<!-- 查询结果区域 -->
<section id="resultSection" class="mb-8 hidden">
<div class="bg-white rounded-xl shadow-custom p-6 hover-lift">
<h2 class="text-xl font-semibold mb-4 flex items-center">
<i class="fas fa-list-alt text-primary mr-2"></i>
查询结果
</h2>
<div id="querySummary" class="mb-4 p-4 bg-blue-50 rounded-lg">
<p class="text-gray-700">
<span class="font-medium">查询产品：</span>
<span id="queryProductsText">-</span>
</p>
<p class="text-gray-700 mt-1">
<span class="font-medium">零覆盖门店数量：</span>
<span id="zeroCoverageCount" class="font-bold text-danger">0</span>
</p>
</div>
<!-- 修改查询结果表格结构，添加产品分组标题 -->
<div class="overflow-x-auto">
<table class="min-w-full table-auto">
<thead class="bg-gray-50">
<tr>
<th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
时间
</th>
<th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
城市
</th>
<th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">区
</th>
<th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
门店名称
</th>
<th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
门店代码
</th>
<th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
产品型号
</th>
<th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
门店库存
</th>
<th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
门店在途
</th>
<th class="px-4 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
店间在途
</th>
</tr>
</thead>
<tbody id="resultTableBody" class="bg-white divide-y divide-gray-200">
<!-- 查询结果将通过JavaScript动态生成 -->
</tbody>
</table>
</div>
<div id="noResults" class="text-center py-8 text-gray-500 hidden">
<i class="fas fa-check-circle text-4xl text-success mb-2"></i>
<p>没有找到零覆盖门店，所有门店都有库存或在途</p>
</div>
</div>
</section>
<!-- 数据可视化区域 -->
<section id="visualizationSection" class="mb-8 hidden">
<div class="grid grid-cols-1 lg:grid-cols-2 gap-6">
<div class="bg-white rounded-xl shadow-custom p-6 hover-lift">
<h3 class="text-lg font-semibold mb-4 flex items-center">
<i class="fas fa-chart-bar text-primary mr-2"></i>
各城市库存总量
</h3>
<div class="h-80">
<canvas id="cityInventoryChart"></canvas>
</div>
</div>
<div class="bg-white rounded-xl shadow-custom p-6 hover-lift">
<h3 class="text-lg font-semibold mb-4 flex items-center">
<i class="fas fa-chart-pie text-primary mr-2"></i>
各产品库存分布
</h3>
<div class="h-80">
<canvas id="productInventoryChart"></canvas>
</div>
</div>
</div>
</section>
</main>
<!-- 页脚 -->
<footer class="bg-white border-t border-gray-200 py-6">
<div class="container mx-auto px-4 text-center text-gray-600">
<p>© 2024 Excel库存分析系统 - 高效管理您的库存数据</p>
</div>
</footer>
<!-- 帮助模态框 -->
<div id="helpModal" class="fixed inset-0 bg-black bg-opacity-50 z-50 hidden flex items-center justify-center">
<div class="bg-white rounded-xl shadow-xl max-w-2xl w-full max-h-[90vh] overflow-y-auto">
<div class="p-6 border-b border-gray-200 flex justify-between items-center">
<h3 class="text-xl font-bold text-gray-800">使用帮助</h3>
<button id="closeHelpModal" class="text-gray-500 hover:text-gray-700">
<i class="fas fa-times text-xl"></i>
</button>
</div>
<div class="p-6">
<div class="mb-6">
<h4 class="text-lg font-semibold mb-2 text-gray-800">1. 准备工作</h4>
<p class="text-gray-600">将您的Excel库存文件放入系统自动创建的"inventory_files"文件夹中。</p>
</div>
<div class="mb-6">
<h4 class="text-lg font-semibold mb-2 text-gray-800">2. 加载数据</h4>
<p class="text-gray-600">点击"加载/刷新数据"按钮，系统会读取inventory_files文件夹中的Excel文件。</p>
</div>
<div class="mb-6">
<h4 class="text-lg font-semibold mb-2 text-gray-800">3. 产品查询</h4>
<p class="text-gray-600">从产品列表中选择一个或多个产品，点击"查询零覆盖门店"按钮。</p>
</div>
<div class="mb-6">
<h4 class="text-lg font-semibold mb-2 text-gray-800">4. 零覆盖门店定义</h4>
<p class="text-gray-600">零覆盖门店指的是门店库存、门店在途、店间在途三者都为零的门店。</p>
</div>
<div class="mb-6">
<h4 class="text-lg font-semibold mb-2 text-gray-800">5. 数据可视化</h4>
<p class="text-gray-600">系统会自动生成各城市库存总量和各产品库存分布的图表。</p>
</div>
</div>
<div class="p-6 border-t border-gray-200 flex justify-end">
<button id="closeHelpModalBtn"
class="bg-primary text-white px-6 py-2 rounded-lg hover:bg-primary/90 transition-custom">
了解了
</button>
</div>
</div>
</div>
<!-- JavaScript -->
<script>
// 全局变量
let cityInventoryChart = null;
let productInventoryChart = null;
let uploadedData = null;
// DOM元素
const loadBtn = document.getElementById('loadBtn');
const showDirBtn = document.getElementById('showDirBtn');
const loadedFileName = document.getElementById('loadedFileName');
const overviewSection = document.getElementById('overviewSection');
const querySection = document.getElementById('querySection');
const resultSection = document.getElementById('resultSection');
const visualizationSection = document.getElementById('visualizationSection');
const productCheckboxes = document.getElementById('productCheckboxes');
const queryBtn = document.getElementById('queryBtn');
const resultTableBody = document.getElementById('resultTableBody');
const noResults = document.getElementById('noResults');
const queryProductsText = document.getElementById('queryProductsText');
const zeroCoverageCount = document.getElementById('zeroCoverageCount');
const totalStores = document.getElementById('totalStores');
const totalProducts = document.getElementById('totalProducts');
const totalInventory = document.getElementById('totalInventory');
const totalInTransit = document.getElementById('totalInTransit');
const helpBtn = document.getElementById('helpBtn');
const helpModal = document.getElementById('helpModal');
const closeHelpModal = document.getElementById('closeHelpModal');
const closeHelpModalBtn = document.getElementById('closeHelpModalBtn');
const themeToggle = document.getElementById('themeToggle');
// 页面加载时尝试自动加载数据
document.addEventListener('DOMContentLoaded', () => {
loadData();
});
// 加载数据按钮点击事件
loadBtn.addEventListener('click', () => {
loadData();
});
// 显示文件目录按钮点击事件
// 替换原有的showDirBtn点击事件
showDirBtn.addEventListener('click', () => {
// 触发文件选择对话框
document.getElementById('fileUpload').click();
});
// 添加文件上传处理
document.getElementById('fileUpload').addEventListener('change', (e) => {
const file = e.target.files[0];
if (!file) return;
// 验证文件类型
const fileExtension = file.name.split('.').pop().toLowerCase();
if (!['xlsx', 'xls'].includes(fileExtension)) {
alert('请上传Excel文件（.xlsx 或 .xls格式）');
return;
}
// 显示上传状态
showDirBtn.disabled = true;
showDirBtn.innerHTML = '<i class="fas fa-spinner fa-spin mr-2"></i>上传中...';
// 创建FormData对象
const formData = new FormData();
formData.append('file', file);
// 发送文件到服务器
fetch('/upload', {
method: 'POST',
body: formData
})
.then(response => {
if (!response.ok) {
throw new Error('文件上传失败');
}
return response.json();
})
.then(data => {
if (data.success) {
alert('文件上传成功，已覆盖原有文件');
// 自动加载新数据
loadData();
} else {
alert(`上传失败: ${data.error}`);
}
})
.catch(error => {
alert(`上传出错: ${error.message}`);
})
.finally(() => {
// 恢复按钮状态
showDirBtn.disabled = false;
showDirBtn.innerHTML = '<i class="fas fa-folder mr-2"></i>上传新文件';
// 重置文件输入，允许重复选择同一文件
document.getElementById('fileUpload').value = '';
});
});
// 加载数据
function loadData() {
// 显示加载状态
loadBtn.disabled = true;
loadBtn.innerHTML = '<i class="fas fa-spinner fa-spin mr-2"></i>加载中...';
loadedFileName.classList.add('hidden');
fetch('/load')
.then(response => {
if (!response.ok) {
throw new Error('数据加载失败');
}
return response.json();
})
.then(data => {
if (data.success) {
// 存储加载的数据
uploadedData = data;
// 更新UI
updateUIAfterLoad(data);
// 显示加载的文件名
loadedFileName.textContent = `已加载文件: ${data.filename}`;
loadedFileName.classList.remove('hidden');
// 显示其他区域
overviewSection.classList.remove('hidden');
querySection.classList.remove('hidden');
visualizationSection.classList.remove('hidden');
// 生成图表
generateCharts(data.city_summary, data.product_summary);
} else {
alert(data.error);
// 隐藏数据区域
overviewSection.classList.add('hidden');
querySection.classList.add('hidden');
resultSection.classList.add('hidden');
visualizationSection.classList.add('hidden');
}
})
.catch(error => {
alert(`加载失败: ${error.message}`);
})
.finally(() => {
// 恢复按钮状态
loadBtn.disabled = false;
loadBtn.innerHTML = '<i class="fas fa-sync-alt mr-2"></i>加载/刷新数据';
});
}
// 加载后更新UI
function updateUIAfterLoad(data) {
// 更新统计信息
totalStores.textContent = data.total_stores;
totalProducts.textContent = data.total_products;
// 计算总库存和总在途
const totalInv = data.city_summary.reduce((sum, city) => sum + city.门店库存, 0);
const totalTransit = data.city_summary.reduce((sum, city) => sum + city.门店在途 + city.店间在途, 0);
totalInventory.textContent = totalInv;
totalInTransit.textContent = totalTransit;
// 生成产品复选框
productCheckboxes.innerHTML = '';
data.products.forEach(product => {
const div = document.createElement('div');
div.className = 'flex items-center';
div.innerHTML = `
<input type="checkbox" id="product_${product}" value="${product}" class="w-4 h-4 text-primary border-gray-300 rounded focus:ring-primary">
<label for="product_${product}" class="ml-2 text-gray-700 text-sm cursor-pointer">${product}</label>
`;
productCheckboxes.appendChild(div);
});
}
// 查询按钮点击事件
queryBtn.addEventListener('click', () => {
// 获取选中的产品
const selectedProducts = Array.from(productCheckboxes.querySelectorAll('input:checked'))
.map(checkbox => checkbox.value);
if (selectedProducts.length === 0) {
alert('请至少选择一个产品');
return;
}
// 获取选中的零覆盖判断逻辑
const zeroLogic = document.querySelector('input[name="zeroLogic"]:checked').value;
// 显示加载状态
queryBtn.disabled = true;
queryBtn.innerHTML = '<i class="fas fa-spinner fa-spin mr-2"></i>查询中...';
resultTableBody.innerHTML = `
<tr>
<td colspan="6" class="px-4 py-8 text-center text-gray-500">
<i class="fas fa-spinner fa-spin text-xl mr-2"></i>正在查询...
</td>
</tr>
`;
resultSection.classList.remove('hidden');
noResults.classList.add('hidden');
// 发送查询请求
fetch('/query', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({
products: selectedProducts,
zero_logic: zeroLogic // 添加判断逻辑参数
})
})
.then(response => {
if (!response.ok) {
throw new Error('查询失败');
}
return response.json();
})
// 修改查询成功后的结果处理逻辑
.then(data => {
if (data.success) {
// 更新查询摘要
queryProductsText.textContent = data.query_products.join(', ');
zeroCoverageCount.textContent = data.zero_coverage_count;
// 更新结果表格 - 按产品分组显示
if (data.zero_coverage_count > 0) {
resultTableBody.innerHTML = '';
// 按产品对结果进行分组
const productsGroup = {};
data.zero_coverage_stores.forEach(store => {
const product = store.机型 || '未知产品';
if (!productsGroup[product]) {
productsGroup[product] = [];
}
productsGroup[product].push(store);
});
// 遍历每个产品组，生成表格内容
Object.keys(productsGroup).forEach(product => {
// 添加产品分组标题行
const groupHeader = document.createElement('tr');
groupHeader.className = 'bg-gray-100';
groupHeader.innerHTML = `
<td colspan="9" class="px-4 py-3 text-left text-base font-bold text-gray-800">
产品: ${product} (共 ${productsGroup[product].length} 条记录)
</td>
`;
resultTableBody.appendChild(groupHeader);
// 添加该产品的所有门店记录
productsGroup[product].forEach(store => {
const row = document.createElement('tr');
row.className = 'hover:bg-gray-50 transition-custom';
row.innerHTML = `
<td class="px-4 py-3 text-sm text-gray-900">${store.时间 || '未知时间'}</td>
<td class="px-4 py-3 text-sm text-gray-900">${store.市 || '-'}</td>
<td class="px-4 py-3 text-sm text-gray-900">${store.区 || '-'}</td>
<td class="px-4 py-3 text-sm font-medium text-gray-900">${store.门店名称 || '-'}</td>
<td class="px-4 py-3 text-sm text-gray-900">${store.门店代码 || '-'}</td>
<td class="px-4 py-3 text-sm font-medium text-gray-900">${store.机型 || '-'}</td>
<td class="px-4 py-3 text-sm text-danger font-medium">${store.门店库存}</td>
<td class="px-4 py-3 text-sm text-danger font-medium">${store.门店在途}</td>
<td class="px-4 py-3 text-sm text-danger font-medium">${store.店间在途}</td>
`;
resultTableBody.appendChild(row);
});
});
noResults.classList.add('hidden');
} else {
resultTableBody.innerHTML = '';
noResults.classList.remove('hidden');
}
} else {
throw new Error(data.error || '查询处理失败');
}
})
.catch(error => {
resultTableBody.innerHTML = `
<tr>
<td colspan="6" class="px-4 py-8 text-center text-danger">
<i class="fas fa-exclamation-triangle mr-2"></i>${error.message}
</td>
</tr>
`;
})
.finally(() => {
// 恢复按钮状态
queryBtn.disabled = false;
queryBtn.innerHTML = '<i class="fas fa-search mr-2"></i>查询零覆盖门店';
});
});
document.addEventListener('DOMContentLoaded', function () {
// 缓存元素引用
const downloadBtn = document.getElementById('downloadBtn');
const productCheckboxes = document.getElementById('productCheckboxes');
// 检查元素是否存在，避免错误
if (!downloadBtn || !productCheckboxes) {
console.error('下载功能所需元素未找到');
return;
}
// 添加下载按钮点击事件
// 在下载按钮点击事件中添加判断逻辑参数
downloadBtn.addEventListener('click', function () {
try {
const selectedProducts = Array.from(productCheckboxes.querySelectorAll('input:checked'))
.map(checkbox => checkbox.value)
.filter(Boolean);
if (selectedProducts.length === 0) {
alert('请至少选择一个产品');
return;
}
// 获取选中的零覆盖判断逻辑
const zeroLogic = document.querySelector('input[name="zeroLogic"]:checked').value;
// 构建下载URL，包含判断逻辑参数
const downloadUrl = new URL('/download', window.location.href);
selectedProducts.forEach(product => {
downloadUrl.searchParams.append('products', product);
});
downloadUrl.searchParams.append('zero_logic', zeroLogic); // 添加判断逻辑参数
// 触发下载
const link = document.createElement('a');
link.href = downloadUrl.toString();
link.download = `零覆盖门店查询结果.xlsx`;
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
} catch (error) {
console.error('下载功能出错:', error);
alert('下载失败，请稍后重试');
}
});
// 修改查询成功后的回调，确保兼容现有代码
// 找到原查询成功的代码，替换为：
function handleQuerySuccess(data) {
if (data.success) {
// 更新查询摘要
const queryProductsText = document.getElementById('queryProductsText');
const zeroCoverageCount = document.getElementById('zeroCoverageCount');
if (queryProductsText && zeroCoverageCount) {
queryProductsText.textContent = data.query_products?.join(', ') || '无';
zeroCoverageCount.textContent = data.zero_coverage_count || '0';
// 显示下载按钮
downloadBtn.classList.remove('hidden');
}
// 保留原有的表格更新逻辑
// ... 原有代码 ...
}
}
// 如果原代码使用的是Promise.then()形式，确保添加错误处理
// 例如：
// fetch(...)
// .then(response => response.json())
// .then(data => {
// handleQuerySuccess(data);
// })
// .catch(error => {
// console.error('查询出错:', error);
// alert('查询失败，请重试');
// });
});
// 生成图表
function generateCharts(cityData, productData) {
// 城市库存柱状图
const cityCtx = document.getElementById('cityInventoryChart').getContext('2d');
if (cityInventoryChart) {
cityInventoryChart.destroy();
}
cityInventoryChart = new Chart(cityCtx, {
type: 'bar',
data: {
labels: cityData.map(item => item.市),
datasets: [
{
label: '门店库存',
data: cityData.map(item => item.门店库存),
backgroundColor: 'rgba(59, 130, 246, 0.8)',
borderColor: 'rgba(59, 130, 246, 1)',
borderWidth: 1
},
{
label: '门店在途',
data: cityData.map(item => item.门店在途),
backgroundColor: 'rgba(16, 185, 129, 0.8)',
borderColor: 'rgba(16, 185, 129, 1)',
borderWidth: 1
},
{
label: '店间在途',
data: cityData.map(item => item.店间在途),
backgroundColor: 'rgba(245, 158, 11, 0.8)',
borderColor: 'rgba(245, 158, 11, 1)',
borderWidth: 1
}
]
},
options: {
responsive: true,
maintainAspectRatio: false,
plugins: {
legend: {
position: 'top',
},
tooltip: {
mode: 'index',
intersect: false,
}
},
scales: {
y: {
beginAtZero: true,
grid: {
color: 'rgba(0, 0, 0, 0.05)'
}
},
x: {
grid: {
display: false
}
}
},
animation: {
duration: 1000,
easing: 'easeOutQuart'
}
}
});
// 产品库存饼图
const productCtx = document.getElementById('productInventoryChart').getContext('2d');
if (productInventoryChart) {
productInventoryChart.destroy();
}
const colors = [
'rgba(59, 130, 246, 0.8)',
'rgba(16, 185, 129, 0.8)',
'rgba(245, 158, 11, 0.8)',
'rgba(239, 68, 68, 0.8)',
'rgba(139, 92, 246, 0.8)',
'rgba(236, 72, 153, 0.8)',
'rgba(236, 72, 458, 0.8)',
'rgba(26, 72, 153, 0.8)',
'rgba(36, 82, 153, 0.8)',
'rgba(346, 92, 13, 0.8)',
];
productInventoryChart = new Chart(productCtx, {
type: 'doughnut',
data: {
labels: productData.map(item => item.机型),
datasets: [{
data: productData.map(item => item.门店库存),
backgroundColor: colors.slice(0, productData.length),
borderColor: colors.slice(0, productData.length).map(color => color.replace('0.8', '1')),
borderWidth: 2,
hoverOffset: 10
}]
},
options: {
responsive: true,
maintainAspectRatio: false,
plugins: {
legend: {
position: 'bottom',
labels: {
padding: 20,
usePointStyle: true
}
},
tooltip: {
callbacks: {
label: function (context) {
const label = context.label || '';
const value = context.parsed;
const total = context.dataset.data.reduce((a, b) => a + b, 0);
const percentage = ((value / total) * 100).toFixed(1);
return `${label}: ${value} (${percentage}%)`;
}
}
}
},
animation: {
animateRotate: true,
duration: 1500,
easing: 'easeOutQuart'
}
}
});
}
// 帮助模态框
helpBtn.addEventListener('click', () => {
helpModal.classList.remove('hidden');
document.body.style.overflow = 'hidden';
});
function closeModal() {
helpModal.classList.add('hidden');
document.body.style.overflow = 'auto';
}
closeHelpModal.addEventListener('click', closeModal);
closeHelpModalBtn.addEventListener('click', closeModal);
helpModal.addEventListener('click', (e) => {
if (e.target === helpModal) {
closeModal();
}
});
// 主题切换
themeToggle.addEventListener('click', () => {
document.body.classList.toggle('dark-mode');
const icon = themeToggle.querySelector('i');
if (document.body.classList.contains('dark-mode')) {
icon.classList.remove('fa-moon');
icon.classList.add('fa-sun');
} else {
icon.classList.remove('fa-sun');
icon.classList.add('fa-moon');
}
});
</script>
</body>
</html>
配置文件
# requirements.txt
Flask==2.3.3
Flask-CORS==4.0.1
pandas==2.0.3
openpyxl==3.1.2 # 新增：解析.xlsx格式必需
xlrd==2.0.1 # 新增：解析.xls格式必需（兼容旧版Excel）
matplotlib==3.7.2 # 可选：确保数据可视化兼容性
xlsxwriter==3.2.0下载链接
那么好看到这里懒得复制的话直接下载
