90 lines
4.0 KiB
Python
90 lines
4.0 KiB
Python
import pandas as pd
|
||
from datetime import datetime
|
||
from app.database.database import SessionLocal
|
||
from app.models import Project, User
|
||
|
||
|
||
def import_projects_from_excel():
|
||
"""从Excel文件导入项目数据"""
|
||
db = SessionLocal()
|
||
|
||
try:
|
||
# 读取Excel文件
|
||
excel_path = "/home/xsl/code/xsl_node/docs/example.xls"
|
||
print(f"Reading Excel file: {excel_path}")
|
||
|
||
# 读取所有工作表
|
||
xls = pd.ExcelFile(excel_path)
|
||
print(f"Excel file has sheets: {xls.sheet_names}")
|
||
|
||
# 读取第一个工作表
|
||
df = pd.read_excel(xls, sheet_name=0)
|
||
print(f"Read {len(df)} rows from Excel")
|
||
|
||
# 获取marketing用户ID
|
||
marketing_user = db.query(User).filter(User.username == "marketing").first()
|
||
if not marketing_user:
|
||
print("Marketing user not found. Exiting.")
|
||
return
|
||
|
||
# 准备导入的项目数据
|
||
projects_to_import = []
|
||
skipped_rows = 0
|
||
|
||
for index, row in df.iterrows():
|
||
try:
|
||
# 创建项目对象
|
||
project = Project(
|
||
project_code=str(row.get('项目编号', f'PROJ{index+1}')),
|
||
name=str(row.get('项目名称', f'项目{index+1}')),
|
||
project_type=str(row.get('工程类别', '')),
|
||
description=str(row.get('项目描述', '')),
|
||
department=str(row.get('所属部门', '未知部门')),
|
||
contract_amount=float(row.get('合同金额（万元）', 0)) if pd.notna(row.get('合同金额（万元）')) else 0,
|
||
project_manager=str(row.get('项目负责人', '')),
|
||
client_company=str(row.get('建设单位', '')),
|
||
client_contact=str(row.get('建设单位联系人', '')),
|
||
contract_date=pd.to_datetime(row.get('合同签订日期')).date() if pd.notna(row.get('合同签订日期')) else None,
|
||
start_date=pd.to_datetime(row.get('开始日期')).date() if pd.notna(row.get('开始日期')) else None,
|
||
planned_end_date=pd.to_datetime(row.get('计划结束日期')).date() if pd.notna(row.get('计划结束日期')) else None,
|
||
actual_end_date=pd.to_datetime(row.get('实际结束日期')).date() if pd.notna(row.get('实际结束日期')) else None,
|
||
progress=float(row.get('累计进度（%）', 0)) if pd.notna(row.get('累计进度（%）')) else 0,
|
||
actual_received_amount=float(row.get('实际收款金额（万元）', 0)) if pd.notna(row.get('实际收款金额（万元）')) else 0,
|
||
payment_completion_rate=float(row.get('回款完成率（%）', 0)) if pd.notna(row.get('回款完成率（%）')) else 0,
|
||
payment_terms=str(row.get('付款方式', '')),
|
||
status=str(row.get('状态', 'pending')),
|
||
created_by=marketing_user.id
|
||
)
|
||
projects_to_import.append(project)
|
||
|
||
# 每100条记录打印一次进度
|
||
if (index + 1) % 100 == 0:
|
||
print(f"Processed {index + 1} rows...")
|
||
|
||
except Exception as e:
|
||
print(f"Error processing row {index + 1}: {e}")
|
||
skipped_rows += 1
|
||
continue
|
||
|
||
# 批量导入数据
|
||
if projects_to_import:
|
||
print(f"Importing {len(projects_to_import)} projects to database...")
|
||
db.add_all(projects_to_import)
|
||
db.commit()
|
||
print(f"Successfully imported {len(projects_to_import)} projects!")
|
||
print(f"Skipped {skipped_rows} rows due to errors")
|
||
else:
|
||
print("No projects to import")
|
||
|
||
except Exception as e:
|
||
db.rollback()
|
||
print(f"Error importing projects: {e}")
|
||
import traceback
|
||
traceback.print_exc()
|
||
finally:
|
||
db.close()
|
||
|
||
|
||
if __name__ == "__main__":
|
||
import_projects_from_excel()
|