======================================================================
ERROR: test_empty_json_data (__main__.TestSaveJsonToExcel)
测试空JSON数据
----------------------------------------------------------------------
Traceback (most recent call last):
File "D:\GitProjects\REQManagement\DDE\kotei_web_server\utils\test.py", line 607, in test_empty_json_data
result_path = save_json_data_to_excel(json_data, excel_path)
File "D:\GitProjects\REQManagement\DDE\kotei_web_server\utils\test.py", line 382, in save_json_data_to_excel
data_dict = json.loads(json_data)
File "C:\Users\kotei\AppData\Local\Programs\Python\Python310\Lib\json\__init__.py", line 339, in loads
raise TypeError(f'the JSON object must be str, bytes or bytearray, '
TypeError: the JSON object must be str, bytes or bytearray, not list
======================================================================
ERROR: test_invalid_row_number (__main__.TestSaveJsonToExcel)
测试无效行号处理
----------------------------------------------------------------------
Traceback (most recent call last):
File "D:\GitProjects\REQManagement\DDE\kotei_web_server\utils\test.py", line 566, in test_invalid_row_number
result_path = save_json_data_to_excel(json_data, excel_path)
File "D:\GitProjects\REQManagement\DDE\kotei_web_server\utils\test.py", line 382, in save_json_data_to_excel
data_dict = json.loads(json_data)
File "C:\Users\kotei\AppData\Local\Programs\Python\Python310\Lib\json\__init__.py", line 339, in loads
raise TypeError(f'the JSON object must be str, bytes or bytearray, '
TypeError: the JSON object must be str, bytes or bytearray, not list
======================================================================
ERROR: test_missing_column (__main__.TestSaveJsonToExcel)
测试缺少列名的情况
----------------------------------------------------------------------
Traceback (most recent call last):
File "D:\GitProjects\REQManagement\DDE\kotei_web_server\utils\test.py", line 587, in test_missing_column
result_path = save_json_data_to_excel(json_data, excel_path)
File "D:\GitProjects\REQManagement\DDE\kotei_web_server\utils\test.py", line 382, in save_json_data_to_excel
data_dict = json.loads(json_data)
File "C:\Users\kotei\AppData\Local\Programs\Python\Python310\Lib\json\__init__.py", line 339, in loads
raise TypeError(f'the JSON object must be str, bytes or bytearray, '
TypeError: the JSON object must be str, bytes or bytearray, not list
======================================================================
ERROR: test_non_existent_file (__main__.TestSaveJsonToExcel)
测试文件不存在的情况
----------------------------------------------------------------------
Traceback (most recent call last):
File "D:\GitProjects\REQManagement\DDE\kotei_web_server\utils\test.py", line 600, in test_non_existent_file
save_json_data_to_excel(json_data, non_existent_path)
File "D:\GitProjects\REQManagement\DDE\kotei_web_server\utils\test.py", line 382, in save_json_data_to_excel
data_dict = json.loads(json_data)
File "C:\Users\kotei\AppData\Local\Programs\Python\Python310\Lib\json\__init__.py", line 339, in loads
raise TypeError(f'the JSON object must be str, bytes or bytearray, '
TypeError: the JSON object must be str, bytes or bytearray, not list
======================================================================
ERROR: test_valid_modification (__main__.TestSaveJsonToExcel)
测试有效修改
----------------------------------------------------------------------
Traceback (most recent call last):
File "D:\GitProjects\REQManagement\DDE\kotei_web_server\utils\test.py", line 541, in test_valid_modification
result_path = save_json_data_to_excel(json_data, excel_path)
File "D:\GitProjects\REQManagement\DDE\kotei_web_server\utils\test.py", line 382, in save_json_data_to_excel
data_dict = json.loads(json_data)
File "C:\Users\kotei\AppData\Local\Programs\Python\Python310\Lib\json\__init__.py", line 339, in loads
raise TypeError(f'the JSON object must be str, bytes or bytearray, '
TypeError: the JSON object must be str, bytes or bytearray, not list
----------------------------------------------------------------------
class TestSaveJsonToExcel(unittest.TestCase):
@classmethod
def setUpClass(cls):
cls.test_dir = TemporaryDirectory()
cls.base_path = Path(cls.test_dir.name)
@classmethod
def tearDownClass(cls):
cls.test_dir.cleanup()
def create_temp_excel(self, filename):
"""创建测试用的Excel文件"""
file_path = self.base_path / filename
# 创建示例Excel文件
df = pd.DataFrame({
"NO": [1, 2, 3],
"変更後：制御仕様書No": ["CS-001", "CS-002", "CS-003"],
"シート名/ページ": ["Page_A", "Page_B", "Page_C"],
"変更後": ["内容A", "内容B", "内容C"]
})
# 添加空行作为表头行（第3行）
with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
df.to_excel(writer, index=False, startrow=2) # 从第3行开始写数据
print(f"📝 创建测试文件: {file_path}")
return str(file_path)
def test_valid_modification(self):
"""测试有效修改"""
excel_path = self.create_temp_excel("test_valid.xlsx")
# 测试数据：修改第1行(number=1)和第3行(number=3)
json_data = [
{
"number": "1",
"flag": "edit",
"シート名/ページ": "更新页面",
"変更後": "重要变更"
},
{
"number": "3",
"flag": "add",
"シート名/ページ": "最终页面",
"変更後": "最终内容"
}
]
# 执行修改
result_path = save_json_data_to_excel(json_data, excel_path)
self.assertEqual(result_path, Path(excel_path))
# 验证修改结果
wb = load_workbook(excel_path)
sheet = wb.active
# 验证第4行修改 (number=1 → 第4行)
self.assertEqual(sheet.cell(row=4, column=3).value, "更新页面") # シート名/ページ
self.assertEqual(sheet.cell(row=4, column=4).value, "重要变更") # 変更後
# 验证第6行修改 (number=3 → 第6行)
self.assertEqual(sheet.cell(row=6, column=3).value, "最终页面")
self.assertEqual(sheet.cell(row=6, column=4).value, "最终内容")
def test_invalid_row_number(self):
"""测试无效行号处理"""
excel_path = self.create_temp_excel("test_invalid_row.xlsx")
json_data = [
{"number": "100", "flag": "edit", "シート名/ページ": "无效行"}, # 超出范围
{"number": "abc", "flag": "edit", "変更後": "无效数字"}, # 非数字
{"number": "-1", "flag": "edit", "変更後": "负数"} # 无效数字
]
result_path = save_json_data_to_excel(json_data, excel_path)
wb = load_workbook(excel_path)
sheet = wb.active
# 验证没有修改
self.assertEqual(sheet.cell(row=4, column=3).value, "Page_A")
self.assertEqual(sheet.cell(row=5, column=3).value, "Page_B")
self.assertEqual(sheet.cell(row=6, column=3).value, "Page_C")
def test_missing_column(self):
"""测试缺少列名的情况"""
excel_path = self.create_temp_excel("test_missing_column.xlsx")
json_data = [
{
"number": "1",
"flag": "edit",
"不存在列": "测试值" # 不存在的列
}
]
result_path = save_json_data_to_excel(json_data, excel_path)
# 验证工作表未被修改
wb = load_workbook(excel_path)
sheet = wb.active
self.assertEqual(sheet.cell(row=4, column=3).value, "Page_A")
def test_non_existent_file(self):
"""测试文件不存在的情况"""
json_data = [{"number": "1", "flag": "edit", "変更後": "测试"}]
non_existent_path = str(self.base_path / "non_existent.xlsx")
with self.assertRaises(FileNotFoundError):
save_json_data_to_excel(json_data, non_existent_path)
def test_empty_json_data(self):
"""测试空JSON数据"""
excel_path = self.create_temp_excel("test_empty.xlsx")
json_data = []
result_path = save_json_data_to_excel(json_data, excel_path)
# 验证文件未被修改
wb = load_workbook(excel_path)
sheet = wb.active
self.assertEqual(sheet.cell(row=4, column=3).value, "Page_A")
def save_json_data_to_excel(json_data: list, output_path: str) -> Path:
"""
根据JSON数据修改Excel文件中的特定单元格
参数:
json_data (list): 包含修改信息的字典列表（已解析的JSON对象）
output_path (str): 完整的输出文件路径（包括文件名）
返回:
Path: 生成的Excel文件路径
"""
try:
# 确保输出目录存在
output_dir = Path(output_path).parent
output_dir.mkdir(parents=True, exist_ok=True)
# 验证JSON数据结构
if not isinstance(json_data, list):
raise TypeError("json_data应为字典列表")
# 检查文件是否存在
file_path = Path(output_path)
if not file_path.exists():
raise FileNotFoundError(f"目标Excel文件不存在: {file_path}")
# 加载Excel工作簿
wb = load_workbook(filename=output_path)
sheet = wb.active
# 验证表头行（第3行）
HEADER_ROW = 3
if sheet.max_column < 1 or sheet.max_row < HEADER_ROW:
raise ValueError("Excel文件缺少表头行")
# 构建列名映射（表头名称 -> 列字母）
header_map = {}
for col_idx in range(1, sheet.max_column + 1):
cell_value = sheet.cell(row=HEADER_ROW, column=col_idx).value
if cell_value:
header_map[cell_value] = col_idx
# 处理每条修改记录
for record in json_data:
# 验证记录格式
if not isinstance(record, dict):
print(f"⚠️ 跳过无效记录类型: {type(record)}")
continue
# 获取行号（number对应第4行开始）
try:
data_row = int(record.get("number", 0)) + 3 # number=1 -> 第4行
if data_row <= HEADER_ROW or data_row > sheet.max_row:
print(f"⚠️ 跳过无效行号: {record.get('number')}")
continue
except (TypeError, ValueError):
print(f"⚠️ 无效的number格式: {record.get('number')}")
continue
# 更新单元格
for field, value in record.items():
# 跳过系统字段
if field in ["number", "flag"]:
continue
# 查找对应列
if field not in header_map:
print(f"⚠️ 表头中未找到列: {field}")
continue
col_idx = header_map[field]
sheet.cell(row=data_row, column=col_idx, value=value)
print(f"🖊️ 更新单元格 [{field}-行{data_row}]: {value}")
# 保存修改后的Excel
wb.save(output_path)
print(f"✅ 成功更新Excel文件: {file_path}")
return file_path
except Exception as e:
print(f"❌ 修改失败: {str(e)}")
import traceback
traceback.print_exc()
raise
扫一扫
787
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
