187 lines
7.0 KiB
Python
187 lines
7.0 KiB
Python
|
#!/usr/bin/env python
|
|||
|
|
# -*- coding: utf-8 -*-
|
|||
|
|
"""
|
|||
|
|
测试表格数据转换为 SQL Server 格式
|
|||
|
|
"""
|
|||
|
|
import json
|
|||
|
|
from convert_table_to_sqlserver_format import convert_temperature_table_to_sqlserver
|
|||
|
|
|
|||
|
|
# 你提供的实际数据（最新版本）
|
|||
|
|
test_data = {
|
|||
|
|
"tables": [{
|
|||
|
|
"token": "scriptTable1",
|
|||
|
|
"startRow": 0,
|
|||
|
|
"startCol": 0,
|
|||
|
|
"cells": [
|
|||
|
|
{"row": 4, "col": 0, "value": "14.0"},
|
|||
|
|
{"row": 4, "col": 1, "value": "14.2"},
|
|||
|
|
{"row": 4, "col": 2, "value": "14.2"},
|
|||
|
|
{"row": 4, "col": 3, "value": "14.3"},
|
|||
|
|
{"row": 4, "col": 4, "value": "14.1"},
|
|||
|
|
{"row": 4, "col": 5, "value": "13.9"},
|
|||
|
|
{"row": 4, "col": 6, "value": ""},
|
|||
|
|
{"row": 5, "col": 0, "value": "13.7"},
|
|||
|
|
{"row": 5, "col": 1, "value": "13.9"},
|
|||
|
|
{"row": 5, "col": 2, "value": "13.9"},
|
|||
|
|
{"row": 5, "col": 3, "value": "13.9"},
|
|||
|
|
{"row": 5, "col": 4, "value": "13.7"},
|
|||
|
|
{"row": 5, "col": 5, "value": "13.6"},
|
|||
|
|
{"row": 5, "col": 6, "value": ""},
|
|||
|
|
{"row": 6, "col": 0, "value": "14.5"},
|
|||
|
|
{"row": 6, "col": 1, "value": "14.7"},
|
|||
|
|
{"row": 6, "col": 2, "value": "14.8"},
|
|||
|
|
{"row": 6, "col": 3, "value": "14.8"},
|
|||
|
|
{"row": 6, "col": 4, "value": "14.8"},
|
|||
|
|
{"row": 6, "col": 5, "value": "14.7"},
|
|||
|
|
{"row": 6, "col": 6, "value": ""},
|
|||
|
|
{"row": 7, "col": 0, "value": "13.9"},
|
|||
|
|
{"row": 7, "col": 1, "value": "14.1"},
|
|||
|
|
{"row": 7, "col": 2, "value": "14.2"},
|
|||
|
|
{"row": 7, "col": 3, "value": "14.3"},
|
|||
|
|
{"row": 7, "col": 4, "value": "14.2"},
|
|||
|
|
{"row": 7, "col": 5, "value": "14.1"},
|
|||
|
|
{"row": 7, "col": 6, "value": ""},
|
|||
|
|
{"row": 8, "col": 0, "value": "13.8"},
|
|||
|
|
{"row": 8, "col": 1, "value": "14.0"},
|
|||
|
|
{"row": 8, "col": 2, "value": "14.1"},
|
|||
|
|
{"row": 8, "col": 3, "value": "14.2"},
|
|||
|
|
{"row": 8, "col": 4, "value": "14.1"},
|
|||
|
|
{"row": 8, "col": 5, "value": "14.0"},
|
|||
|
|
{"row": 8, "col": 6, "value": ""},
|
|||
|
|
{"row": 9, "col": 0, "value": "12.5"},
|
|||
|
|
{"row": 9, "col": 1, "value": "12.7"},
|
|||
|
|
{"row": 9, "col": 2, "value": "12.8"},
|
|||
|
|
{"row": 9, "col": 3, "value": "12.9"},
|
|||
|
|
{"row": 9, "col": 4, "value": "12.8"},
|
|||
|
|
{"row": 9, "col": 5, "value": "12.7"},
|
|||
|
|
{"row": 9, "col": 6, "value": ""},
|
|||
|
|
{"row": 10, "col": 0, "value": "14.0"},
|
|||
|
|
{"row": 10, "col": 1, "value": "14.2"},
|
|||
|
|
{"row": 10, "col": 2, "value": "14.3"},
|
|||
|
|
{"row": 10, "col": 3, "value": "14.4"},
|
|||
|
|
{"row": 10, "col": 4, "value": "14.3"},
|
|||
|
|
{"row": 10, "col": 5, "value": "14.2"},
|
|||
|
|
{"row": 10, "col": 6, "value": ""},
|
|||
|
|
{"row": 11, "col": 0, "value": "14.8"},
|
|||
|
|
{"row": 11, "col": 1, "value": "15.0"},
|
|||
|
|
{"row": 11, "col": 2, "value": "15.1"},
|
|||
|
|
{"row": 11, "col": 3, "value": "15.2"},
|
|||
|
|
{"row": 11, "col": 4, "value": "15.1"},
|
|||
|
|
{"row": 11, "col": 5, "value": "15.0"},
|
|||
|
|
{"row": 11, "col": 6, "value": ""},
|
|||
|
|
{"row": 12, "col": 0, "value": "13.2"},
|
|||
|
|
{"row": 12, "col": 1, "value": "13.4"},
|
|||
|
|
{"row": 12, "col": 2, "value": "13.5"},
|
|||
|
|
{"row": 12, "col": 3, "value": "13.6"},
|
|||
|
|
{"row": 12, "col": 4, "value": "13.5"},
|
|||
|
|
{"row": 12, "col": 5, "value": "13.3"},
|
|||
|
|
{"row": 12, "col": 6, "value": ""},
|
|||
|
|
{"row": 13, "col": 0, "value": "13.0"},
|
|||
|
|
{"row": 13, "col": 1, "value": "13.3"},
|
|||
|
|
{"row": 13, "col": 2, "value": "13.3"},
|
|||
|
|
{"row": 13, "col": 3, "value": "13.4"},
|
|||
|
|
{"row": 13, "col": 4, "value": "13.3"},
|
|||
|
|
{"row": 13, "col": 5, "value": "13.2"},
|
|||
|
|
{"row": 13, "col": 6, "value": ""},
|
|||
|
|
{"row": 14, "col": 0, "value": "13.1"},
|
|||
|
|
{"row": 14, "col": 1, "value": "13.4"},
|
|||
|
|
{"row": 14, "col": 2, "value": "13.4"},
|
|||
|
|
{"row": 14, "col": 3, "value": "13.5"},
|
|||
|
|
{"row": 14, "col": 4, "value": "13.4"},
|
|||
|
|
{"row": 14, "col": 5, "value": "13.2"},
|
|||
|
|
{"row": 14, "col": 6, "value": ""},
|
|||
|
|
{"row": 15, "col": 0, "value": "14.4"},
|
|||
|
|
{"row": 15, "col": 1, "value": "14.6"},
|
|||
|
|
{"row": 15, "col": 2, "value": "14.7"},
|
|||
|
|
{"row": 15, "col": 3, "value": "14.7"},
|
|||
|
|
{"row": 15, "col": 4, "value": "14.4"},
|
|||
|
|
{"row": 15, "col": 5, "value": "14.3"},
|
|||
|
|
{"row": 15, "col": 6, "value": ""},
|
|||
|
|
{"row": 16, "col": 0, "value": "0.6"},
|
|||
|
|
{"row": 16, "col": 1, "value": "0.6"},
|
|||
|
|
{"row": 16, "col": 2, "value": "0.6"},
|
|||
|
|
{"row": 16, "col": 3, "value": "0.6"},
|
|||
|
|
{"row": 16, "col": 4, "value": "0.6"},
|
|||
|
|
{"row": 16, "col": 5, "value": "0.6"},
|
|||
|
|
{"row": 16, "col": 6, "value": ""},
|
|||
|
|
{"row": 1, "col": 1, "value": "2025-12-05 14:00:00"},
|
|||
|
|
{"row": 1, "col": 3, "value": "2025-12-05 17:30:00"},
|
|||
|
|
{"row": 0, "col": 1, "value": "13.4"}
|
|||
|
|
]
|
|||
|
|
}]
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
print("=" * 80)
|
|||
|
|
print("测试表格数据转换为 SQL Server 格式")
|
|||
|
|
print("=" * 80)
|
|||
|
|
|
|||
|
|
# 准备全局参数（模拟配置）
|
|||
|
|
global_params = {
|
|||
|
|
'runin_date': '2025-12-10',
|
|||
|
|
'operator_name': '张三',
|
|||
|
|
'power_end_part_no': 'PWR-600-001',
|
|||
|
|
'motor_speed_rpm': '980'
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
print("\n全局参数:")
|
|||
|
|
for key, value in global_params.items():
|
|||
|
|
print(f" {key}: {value}")
|
|||
|
|
|
|||
|
|
# 转换数据
|
|||
|
|
result = convert_temperature_table_to_sqlserver(test_data, "TEST_W2001150", global_params)
|
|||
|
|
|
|||
|
|
print("\n转换结果:")
|
|||
|
|
print(json.dumps(result, ensure_ascii=False, indent=2))
|
|||
|
|
|
|||
|
|
print("\n" + "=" * 80)
|
|||
|
|
print("字段统计:")
|
|||
|
|
print("=" * 80)
|
|||
|
|
print(f"总字段数: {len(result)}")
|
|||
|
|
|
|||
|
|
# 统计各类字段
|
|||
|
|
temp_fields = [k for k in result.keys() if k.startswith('temp_')]
|
|||
|
|
other_fields = [k for k in result.keys() if not k.startswith('temp_')]
|
|||
|
|
|
|||
|
|
print(f"温度字段数: {len(temp_fields)}")
|
|||
|
|
print(f"其他字段数: {len(other_fields)}")
|
|||
|
|
|
|||
|
|
print("\n其他字段:")
|
|||
|
|
for field in other_fields:
|
|||
|
|
print(f" {field}: {result[field]}")
|
|||
|
|
|
|||
|
|
print("\n温度字段示例（前10个）:")
|
|||
|
|
for field in temp_fields[:10]:
|
|||
|
|
print(f" {field}: {result[field]}")
|
|||
|
|
|
|||
|
|
# 检查是否有缺失的字段
|
|||
|
|
print("\n" + "=" * 80)
|
|||
|
|
print("字段完整性检查:")
|
|||
|
|
print("=" * 80)
|
|||
|
|
|
|||
|
|
expected_parts = [
|
|||
|
|
'main_1', 'main_2', 'main_3', 'main_4',
|
|||
|
|
'crosshead_1', 'crosshead_2', 'crosshead_3',
|
|||
|
|
'gbox_small_1', 'gbox_small_2',
|
|||
|
|
'gbox_big_3', 'gbox_big_4'
|
|||
|
|
]
|
|||
|
|
|
|||
|
|
expected_times = ['t05', 't10', 't15', 't20', 't25', 't30', 't35']
|
|||
|
|
|
|||
|
|
missing_fields = []
|
|||
|
|
for part in expected_parts:
|
|||
|
|
for time in expected_times:
|
|||
|
|
field = f"temp_{part}_{time}"
|
|||
|
|
if field not in result:
|
|||
|
|
missing_fields.append(field)
|
|||
|
|
|
|||
|
|
if missing_fields:
|
|||
|
|
print(f"⚠️ 缺失 {len(missing_fields)} 个字段:")
|
|||
|
|
for field in missing_fields[:10]:
|
|||
|
|
print(f" - {field}")
|
|||
|
|
if len(missing_fields) > 10:
|
|||
|
|
print(f" ... 还有 {len(missing_fields) - 10} 个")
|
|||
|
|
else:
|
|||
|
|
print("✅ 所有温度字段都已转换")
|
|||
|
|
|
|||
|
|
print("\n" + "=" * 80)
|