# Excel导入资料库
**Repository Path**: sen_shan/excelToDataBase
## Basic Information
- **Project Name**: Excel导入资料库
- **Description**: Excel导入资料库
- **Primary Language**: Python
- **License**: MulanPSL-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No
## Statistics
- **Stars**: 0
- **Forks**: 0
- **Created**: 2025-06-03
- **Last Updated**: 2025-07-25
## Categories & Tags
**Categories**: Uncategorized
**Tags**: None
## README
# Excel 数据导入数据库系统文档说明
---
## 一、项目概述
本项目是一个基于 Python 的自动化工具，用于将 **Excel 文件中的数据批量导入到数据库**，支持 SQL Server、MySQL 和 Oracle 等多种数据库类型。通过命令行参数控制程序行为，并利用 AES 加密保护敏感信息。
项目具备以下核心功能：
- **Excel 文件读取与处理**
- **字段映射与默认值填充**
- **数据库连接与批量插入**
- **文件备份与错误日志记录**
- **加密/解密交互操作**
- **日志管理与定时清理**
---
## 二、系统架构
### 技术栈
- **开发语言**：Python 3.8+
- **核心库**：
- `pandas`：读取和处理 Excel 文件
- `sqlalchemy`：数据库连接与操作
- `openpyxl`：支持 [.xlsx] 格式文件读取
- `oracledb`, `pymssql`, `mysql-connector-python`：不同数据库的驱动
- **加密模块**：使用 AES 对称加密算法保护敏感配置（如数据库密码）
- **日志管理**：自定义日志类支持按级别输出到控制台与文件，并支持定时轮转
- **配置管理**：通过 `.env` 文件进行环境和数据库配置
- **导入管理**：通过 `ImpConfig.xlsx` 文件实现数据库与导入配置的关联，该文件用于定义数据库表与 Excel 文件之间的映射关系。
- **文件处理**：封装了递归查找、移动、删除等通用文件操作函数
---
## 三、主要模块说明
### 1. 主程序入口
[excelToDataBaseMain.py]
主程序根据命令行参数执行以下功能：
| 参数 | 功能描述 |
|------------|------------------|
| [encrypt] | 启动加密输入流程 |
| [decrypt] | 启动解密输入流程 |
| [load] | 加载并处理 Excel 文件 |
| 无参数 | 默认执行 [load]操作 |
```python
def main():
if len(sys.argv) > 1:
param = sys.argv[1]
if param == 'encrypt':
crypt_input.input_encrypt_data()
elif param == 'decrypt':
crypt_input.input_decrypt_data()
elif param == 'load':
excel.load_file()
else:
print("无效的参数")
else:
excel.load_file()
logger_cust.delete()
```
#### 加密操作批处理文件
[run_encrypt_set.bat]
```dos
@echo off
:: 设置可执行文件的路径
set EXE_FILE=excelToDataBaseMain.exe
:: 直接传递加密 参数给可执行文件
%EXE_FILE% encrypt
```
#### 主程序批处理文件
[run_main.bat]
```dos
@echo off
:: 设置可执行文件的路径
set EXE_FILE=excelToDataBaseMain.exe
:: 无参数运行可执行文件
%EXE_FILE%
```
---
### 2. 加密/解密模块
`src/core/crypt_input.py`
提供交互式的加密和解密功能，用户可手动输入内容进行加密或解密操作。
#### 函数说明：
- [input_encrypt_data()]：循环接收用户输入，调用 AES 加密函数输出加密结果
- [input_decrypt_data()]：循环接收加密字符串，调用 AES 解密函数输出原文
> 依赖：crypt_utils.aes_encrypt(data) / crypt_utils.aes_decrypt(data)
---
### 3. 加密工具类
`src/core/crypt_utils.py`
实现 AES 对称加密算法，支持加密与解密操作。
#### 核心函数说明：
- [aes_encrypt(data, key=get_crypt_key())]：使用 AES/CBC/PKCS7 填充方式对数据进行加密，返回 Base64 编码的密文
- [aes_decrypt(ciphertext, key=get_crypt_key())]：解密 Base64 编码的密文，还原原始数据
- [get_crypt_key()]：获取配置文件中定义的密钥，并拼接其反转版本以增强安全性
- [md5(data)]：计算输入数据的 MD5 哈希值（可用于校验）
---
### 4. 字符串与数据处理模块
`src/core/str_utils.py`
封装常用字符串、日期、路径等实用工具函数。
#### 核心函数说明：
- [is_empty(value)] / [is_not_empty(value)]：判断值是否为空（包括 None、空字符串、空列表、空字典等）
- [is_number(value)]：判断字符串或变量是否为数字（整数或浮点数）
- [is_date(date_string, date_format='%Y-%m-%d')]：判断字符串是否符合指定格式的日期
- [reverse(s)]：反转字符串字符顺序
- [generate_id(RemoveDash=True)]：生成 UUID，支持去横线处理
- [get_project_root()]：获取当前项目的根目录路径
- [is_drive_path(file_path)] / [is_path(file_path)]：判断是否为 Windows 盘符路径或有效文件路径
- [isFileExists(file_path)]：检查文件是否存在
- [get_file_info(file_path)]：提取文件名、扩展名、不带扩展名的文件名
---
### 5. 文件处理模块
`src/core/files_utils.py`
封装文件系统的常见操作，包括查找、移动、删除、错误日志写入等功能。
#### 核心函数说明：
- `read_files_with_pattern(directory_path, pattern, include_subdirs=False)`：查找指定目录下符合模式的文件
- `move_file(source_file, backup_folder, overwrite=False)`：将源文件移动至目标目录，避免覆盖冲突
- `delete_files(folder_path, days, delete_empty_folders=False)`：删除超过指定天数的文件，可选删除空文件夹
- `ErrMessage(folder_path, file_name, message)`：将错误消息写入日志文件，自动创建文件夹结构
---
### 6. 日志管理模块
`src/core/logging_utils.py`
自定义日志模块，支持按级别输出到控制台和文件，并支持日志文件定时轮转。
#### 类：[Logger]
- 支持 DEBUG/INFO/WARNING/ERROR/CRITICAL 多个级别日志输出
- 支持日志文件按天切割，保留历史日志（最多 5 份）
- 支持日志删除（根据配置保留天数）
---
### 7. Excel 处理模块
`src/core/excel_loader.py`
负责读取 Excel 文件并按照配置规则进行字段映射、默认值填充、数据清洗等操作。
#### 核心函数说明：
- load_excel_file(file_path, sheet_name=None, usecols=None)：读取 Excel 文件，返回 DataFrame
- [load_config_table_file()]：加载 `ImportConfig.xlsx` 中的 `Table` Sheet，获取任务配置
- [load_config_Filed_file(ModelName)]：根据模型名筛选出对应的字段映射规则
- [load_file()]：执行整个导入流程：
- 读取配置 → 查找文件 → 映射字段 → 插入数据库 → 错误处理 → 文件备份
---
### 8. 数据库操作模块
`src/core/db_sqlalchemy_utils.py`
封装数据库连接与批量插入逻辑，支持多数据库类型。
#### 类：[DatabaseOperations]
- [connect()]：根据配置建立数据库连接（支持 SQL Server、MySQL、Oracle）
- [bulk_insert(table_name, data)]：将 DataFrame 数据批量插入指定表中
- [convert_to_str(x)]：将非 NaN 值转换为字符串，支持简繁体转换（可选）
---
## 四、配置说明
### 1. 环境变量配置文件
`.env`
| 配置项 | 描述 |
|-------------------------------|--------------------------------|
| [ENV_STATE] | 环境模式（dev/test/prod） |
| [DB_TYPE] | 数据库类型（sql_server/mysql/oracle） |
| [DB_HOST] | 数据库主机地址（需加密存储） |
| [DB_PORT] | 数据库主机端口（需加密存储） |
| [DB_SID] | 数据库名（需加密存储） |
| [DB_USERNAME] | 用户名（需加密存储） |
| [DB_PASSWORD] | 密码（需加密存储） |
| [LOG_LEVEL] | 日志级别 |
| [LOG_DIR] | 日志路径 |
| [BACKUP_DAYS] | 备份文件保留天数 |
| [IMP_CONFIG_PATCH] | 导入配置文件路径 |
| [CRYPT_KEY] | 加密密钥 |
| [OPENCC_CONVERSION_ENABLED] | 是否启用简繁体转换 |
| [OPENCC_CONVERSION_TYPE] | 简繁体转换类型（如 s2t.json） |
> ⚠️ 注意：所有数据库连接信息应加密保存，在代码中使用 `crypt_utils.aes_decrypt()` 解密读取。
> 数据库连接信息/CRYPT KEY应该放在dev/prod文件中,不要放在.env文件中避免重要信息泄露。
---
## 五、使用说明
### 1. 安装依赖
```bash
pip install pandas sqlalchemy opencc openpyxl oracledb pymssql mysql-connector-python numpy cryptography
```
### 2. 配置文件准备
- `config/ImportConfig.xlsx`
- `Table` Sheet：每个条目表示一个导入任务，包含文件路径、表名、控制表、备份路径等
- `Field` Sheet：字段映射规则，Excel 列名 → 数据库字段名 + 默认值设定
### 3. 运行方式
```bash
python excelToDataBaseMain.py [encrypt|decrypt|load]
```
- 不带参数：默认执行 [load]
- [encrypt]：进入加密输入模式
- [decrypt]：进入解密输入模式
- [load]：加载并导入 Excel 数据
---
# 六、导入配置说明文档
`impConfig.xlsx`
---
## 1、配置文件结构
`impConfig.xlsx` 是 Excel 数据导入数据库系统的核心配置文件，包含两个关键 Sheet：
**Table**：定义数据源与目标数据库表的映射关系
**Field**：定义字段级别的映射规则和默认值设定
该配置文件路径由 `.env` 文件中的 `IMP_CONFIG_PATCH=config/impConfig.xlsx` 指定。
---
## 2、`Table`定义
用于定义每个 Excel 文件与目标数据库表之间的整体映射关系。
| 字段名 | 类型 | 必填 | 描述 |
|-----------------|----------|----|---------------------------|
| ModelName | string | 是 | 模型名称，用于关联 Field 表中的配置 |
| Desc | string | 是 | 描述说明 |
| FilePath | string | 是 | Excel 文件所在的目录路径（可使用盘符路径） |
| FileName | string | 是 | Excel 文件名匹配模式（如 `*.xlsx`） |
| SheetName | string | 否 | Excel 中的工作表名称，默认读取第一个工作表 |
| TableName | string | 是 | 目标数据库中要插入的表名 |
| ControlTable | string | 否 | 控制表名（可用于状态管理） |
| AfterImporting | string | 否 | 导入前处理程序 |
| BeforeImporting | string | 否 | 导入后处理程序 |
| BakPath | string | 是 | 成功导入后文件备份路径 |
| ErrMesPath | string | 是 | 错误日志写入路径 |
### 示例：
| ModelName | Desc | FilePath | FileName | SheetName | TableName | ControlTable | BakPath | ErrMesPath |
|--------------|-----------|--------------------|--------------|-----------|-----------|--------------|-----------------|------------------|
| SN_ASS_C_CFM | C类料交期回复| ..\docs\Imp\SN | SN-Ass大料况*.xlsx | Sheet1 | EPI_PO_ASS_CONFIRM | CONTROL_TABLE | ..\docs\Bak\SN | ..\docs\ErrMes\SN |
> ✅ **注意**：
> - `FilePath` 支持通配符，例如 `*.xlsx` 或 `user_*.xlsx`
> - 如果 `SheetName` 留空，则默认读取第一个工作表
> - `BakPath` 和 `ErrMesPath` 若存在则清空旧文件，否则自动创建目录，BakPath为空会导致文件不会被移转一直在FilePath文件夹中
---
## 3、`Field`定义
用于定义每个模型对应的数据列映射规则及字段处理逻辑。
| 字段名 | 类型 | 必填 | 描述 |
|--------------|----------|------|---------------------------------------------------------------------------------------------------------------------------------|
| ModelName | string | 是 | 关联 Table 表中的 ModelName |
| ExcelField | string | 是 | Excel 中的列名 |
| TableField | string | 是 | 对应数据库表的字段名 |
| DefaultValue | string | 否 | 默认值。支持以下特殊值：
`auto-generate` 自动生成批次 ID
`auto-datetime` 使用当前时间
`auto-line-number` 自动行号
`auto-generate-row` 每行生成唯一 ID |
| Desc | string | 否 | 描述说明 |
| Remark | string | 否 | 备注说明 |
### 示例：
| ModelName | ExcelField | TableField | DefaultValue | Desc | remark |
|-----------------|------------------------|-----------------------|----------------------|----------------|------------------------------------------------------------------------|
| SN_ASS_C_CFM | WO COMPONENT NO. | ITEM_NO | | 物料编码 | |
| SN_ASS_C_CFM | 采购CFM | CONFIRM_DATE | | 供给日期 | |
| SN_ASS_C_CFM | 说明 | CONFIRM_EXPLANATION | | 说明 | |
| SN_ASS_C_CFM | 问题 | CONFIRM_PROBLEM | | 问题 | |
| SN_ASS_C_CFM | SUPPLY TYPE | SUPPLY_TYPE | | 供给类型 | |
| SN_ASS_C_CFM | WO OUTSTANDING QTY. | CONFIRM_QTY | | 供给数量 | |
| SN_ASS_C_CFM | PO NO. | PO_NUMBER | | 采购单号码 | |
| SN_ASS_C_CFM | SUPPLIER NAME | SUPPLIER_NAME | | 供应商 | |
| SN_ASS_C_CFM | | BATCH_ID | auto-generate | 批号 | "auto-generate是一个文档一个auto-generate-row每行一个" |
| SN_ASS_C_CFM | | BATCH_LINE_NUMBER | auto-line-number | 项次号码 | |
| SN_ASS_C_CFM | | PROCESS_CODE | 2000 | 工程代码 | |
| SN_ASS_C_CFM | | SERVER_CODE | SERCOMM | 服务代码 | |
| SN_ASS_C_CFM | | STATUS_CODE | 10 | 状态代码 | |
| SN_ASS_C_CFM | | B2B_CREATION_DATE_TIME| auto-datetime | 创建时间 | |
| SN_ASS_C_CFM | | ORG_CODE | SN | 组织代码 | |
| SN_ASS_C_CFM | | DATA_SOURCE | ASS | 资料源 | |
> ✅ **注意**：
> - `DefaultValue` 可为空，表示不填充默认值
> - 列名大小写不敏感，但建议保持一致以避免歧义
> - 若 Excel 中没有对应的列名，会根据 `DefaultValue` 规则自动填充
---
## 4、配置示例说明
### 示例 1：基本字段映射
```excel
ModelName: SN_ASS_C_CFM
ExcelField: WO COMPONENT NO.
TableField: ITEM_NO
DefaultValue:
```
→ 将 Excel 中的“WO COMPONENT NO.”列映射到数据库表的 `ITEM_NO` 字段，无默认值。
---
### 示例 2：自动填充批次 ID
```excel
ModelName: SN_ASS_C_CFM
ExcelField:
TableField: batch_id
DefaultValue: auto-generate
```
→ 在整个 Excel 导入任务开始时自动生成一个批次 ID，并在所有行中填充到 `batch_id` 字段。
---
### 示例 3：自动填充当前时间
```excel
ModelName: SN_ASS_C_CFM
ExcelField:
TableField: B2B_CREATION_DATE_TIME
DefaultValue: auto-datetime
```
→ 使用当前时间戳填充 `B2B_CREATION_DATE_TIME` 字段。
---
### 示例 4：自动添加行号
```excel
ModelName: SN_ASS_C_CFM
ExcelField:
TableField: BATCH_LINE_NUMBER
DefaultValue: auto-line-number
```
→ 自动为每一行添加递增行号，从 1 开始。
---
### 示例 5：每行生成唯一 ID
```excel
ModelName: SN_ASS_C_CFM
ExcelField:
TableField: ID
DefaultValue: auto-generate-row
```
→ 每行生成一个唯一的 UUID，用于标识每条记录。
---
### 示例 6：默认值
```excel
ModelName: UserImport
ExcelField:
TableField: ORG_CODE
DefaultValue: SN
```
→ 把SN填充到`ORG_CODE` 字段所有行中 。
---
## 5、字段匹配机制
1. 程序首先根据 `Table` Sheet 中的 `ModelName` 加载对应的字段映射。
2. 然后尝试将 Excel 的列名与 `Field` Sheet 中的 `ExcelField` 匹配。
3. 匹配成功后，将数据映射到 `TableField`。
4. 若未找到匹配列且设置了 `DefaultValue`，则按规则填充默认值。
---
## 6、CONTROL_TABLE定义
| ModelName | ExcelField | TableField | DefaultValue | Desc | remark |
|---------------------|-------------------------------|-----------------------------|-------------------------|------------------|---------------------|
| CONTROL_TABLE_WRITE | | BATCH_ID | default-batch-id | 默认批次号码 | |
| CONTROL_TABLE_WRITE | | PROCESS_CODE | 2000 | 项目代码 | |
| CONTROL_TABLE_WRITE | | SERVER_CODE | SERCOMM | 服务代码 | |
| CONTROL_TABLE_WRITE | | DATA_COUNT | default-data-count | 笔数 | |
| CONTROL_TABLE_WRITE | | STATUS_CODE | 100 | 状态代码 | |
| CONTROL_TABLE_WRITE | | ERROR_MESSAGE | default-error-message | 错误代码 | |
| CONTROL_TABLE_WRITE | | B2B_CREATION_DATE_TIME | auto-datetime | 创建日期 | |
| CONTROL_TABLE_WRITE | | B2B_LAST_UPDATE_DATE_TIME | auto-datetime | 修改日期 | |
| CONTROL_TABLE_READY | | BATCH_ID | default-batch-id | 默认批次号码 | |
| CONTROL_TABLE_READY | | PROCESS_CODE | 2000 | 项目代码 | |
| CONTROL_TABLE_READY | | SERVER_CODE | SERCOMM | 服务代码 | |
| CONTROL_TABLE_READY | | DATA_COUNT | default-data-count | 笔数 | |
| CONTROL_TABLE_READY | | RETRY_COUNT | 0 | | |
| CONTROL_TABLE_READY | | STATUS_CODE | 120 | 状态代码 | |
| CONTROL_TABLE_READY | | ERROR_MESSAGE | default-error-message | 错误代码 | |
| CONTROL_TABLE_READY | | B2B_CREATION_DATE_TIME | auto-datetime | 创建日期 | |
| CONTROL_TABLE_READY | | B2B_LAST_UPDATE_DATE_TIME | auto-datetime | 修改日期 | |
| CONTROL_TABLE_FAILL | | BATCH_ID | default-batch-id | 默认批次号码 | |
| CONTROL_TABLE_FAILL | | PROCESS_CODE | 2000 | 项目代码 | |
| CONTROL_TABLE_FAILL | | SERVER_CODE | SERCOMM | 服务代码 | |
| CONTROL_TABLE_FAILL | | DATA_COUNT | default-data-count | 笔数 | |
| CONTROL_TABLE_FAILL | | STATUS_CODE | 120 | 状态代码 | |
| CONTROL_TABLE_FAILL | | ERROR_MESSAGE | default-error-message | 错误代码 | |
| CONTROL_TABLE_FAILL | | B2B_CREATION_DATE_TIME | auto-datetime | 创建日期 | |
| CONTROL_TABLE_FAILL | | B2B_LAST_UPDATE_DATE_TIME | auto-datetime | 修改日期 | |
- `CONTROL_TABLE_WRITE`写入中配置，暂未使用
- `CONTROL_TABLE_READY`写入完成配置，栏位根据实际表自定义
- `CONTROL_TABLE_FAILL`写入失败配置，暂未使用
---
## 7、注意事项
- `impConfig.xlsx` 文件必须为 [.xlsx]格式，不能是 `.xls`
- `ModelName` 必须唯一，确保 Table 与 Field 映射正确
- `DefaultValue` 支持字符串或预设关键字，不可混用非标准值
- 若 Excel 列名与配置项不一致，会导致导入失败，请严格匹配或使用工具函数转换（如全大写）
- 配置文件修改后需重启程序或重新加载配置，确保生效
---
## 8、推荐命名规范
| 项目 | 推荐命名方式 |
|--------------|--------------------------|
| ModelName | PascalCase（如 `UserImport`） |
| ExcelField | 中文或英文列名（保持统一） |
| TableField | snake_case（如 `user_name`） |
| DefaultValue | 使用预设关键字，避免硬编码 |
---
## 9、常见问题排查
| 问题现象 | 可能原因 | 解决方法 |
|----------------------------------|------------------------------------|----------|
| 数据未映射 | Excel 列名与 `ExcelField` 不一致 | 检查列名是否完全匹配 |
| 缺少默认值 | `DefaultValue` 设置错误 | 检查是否拼写错误或使用非法值 |
| 程序报错“找不到列” | Excel 中缺少某列且未设置默认值 | 添加默认值或检查 Excel 文件内容 |
| 批量导入失败 | 某些字段类型不匹配 | 检查数据库字段类型与 Excel 数据格式是否兼容 |
| 备份路径未生效 | `BakPath` 路径错误 | 检查路径是否存在权限问题 |
| 日志未输出或路径错误 | `ErrMesPath` 设置错误 | 检查路径是否存在或是否有写入权限 |
---
## 10、扩展建议
- 支持更多 `DefaultValue` 类型，如 `auto-user`（操作用户）、`auto-filename`（文件名）
- 支持正则表达式匹配列名
- 支持字段校验规则（如非空、类型检查等）
---
## 11、附录：配置文件结构图
```
impConfig.xlsx
├── Table
│ ├── ModelName
│ ├── FilePath
│ ├── FileName
│ ├── SheetName
│ ├── TableName
│ ├── ControlTable
│ ├── BakPath
│ └── ErrMesPath
└── Field
├── ModelName
├── ExcelField
├── TableField
└── DefaultValue
```
---
通过以上配置，你可以灵活地控制 Excel 数据如何映射到数据库表中，并利用内置默认值机制提升数据完整性与自动化程度。
## 七、日志与错误处理
- 日志输出：默认打印到控制台，也可配置写入文件
- 错误信息：会写入指定的错误目录下的 `Err.txt` 或以 `batch_id.txt` 形式保存
- 成功导入后：原始文件会被移动到备份目录（按批次 ID 存放）
---
## 八、注意事项
- 使用 Oracle 时，需要安装 Instant Client 并配置路径，instantclient_11_2为11.2版本数据库链接Client
- 所有数据库连接信息应加密存储
- Excel 文件格式需与配置一致，避免列名不匹配问题
- 默认值规则需在配置文件中正确填写，否则可能导致导入失败
---
## 九、版本更新记录
| 版本号 | 更新内容 |
|--------------|----------|
| v1.0.0 | 实现基础导入功能，支持字段映射 |
| v1.1.0 | 增加默认值填充、自动生成批次 ID |
| v1.2.0 | 支持多数据库类型（SQL Server、MySQL、Oracle） |
| v1.3.0 | 增加简繁体转换、完善错误处理机制 |
| v1.4.0 | 新增交互式加密/解密功能，提升配置安全 |
| v1.5.0 | 新增 [str_utils] 工具类，增强路径、日期、UUID 等通用能力 |
| v1.6.0 | 新增 [files_utils] 模块，支持递归查找、文件移动、日志写入等功能 |
| v1.7.0 | 新增 [logging_utils] 日志模块，支持按级别输出与定时清理 |
---
## 十、联系方式
- 作者：[Sen Shan]
- 邮箱：sen_shan@139.com
- Gitee 地址：https://gitee.com/sen_shan/excelToDataBase.git
---
请根据您的实际项目内容进一步补充和完善该文档。