1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
| import win32com.client as win32
import pythoncom
import json
import os
pythoncom.CoInitialize()
exel = win32com.client.Dispatch('excel.Application')
excel = win32com.client.DispatchEx('Excel.Application')
w.Visible = 0
w.DisplayAlerts = 0
result_file_path = os.path.join(os.path.dirname(__file__), "result.json")
excel_result_path = r"{}".format(os.path.join(os.path.dirname(__file__), "result.xlsx"))
def _get_cell(ws, row, col):
return ws.Cells(row, col).Value
def write_json_file(file, js, encoding='utf-8'):
return write_file(file, json.dumps(js, sort_keys=True, indent=4, separators=(",", ": ")), encoding)
def write_file(file, contents, encoding='utf-8'):
try:
with open(file, 'w', encoding=encoding) as f:
f.write(contents)
f.flush()
os.fsync(f.fileno())
except Exception as e:
return str(e)
def read_file(file, encoding='utf-8'):
try:
with open(file, 'r', encoding=encoding) as f:
return f.read()
except Exception:
pass
def read_json_file(file, encoding='utf-8'):
contents = read_file(file, encoding)
if not contents:
return {}
try:
return json.loads(contents)
except Exception:
return {}
def read_data_from_excel(file, sheet_name, rows, cols):
data = []
pythoncom.CoInitialize()
excel = win32.DispatchEx("excel.application")
try:
wb = excel.workbooks.Open(file)
ws = wb.Worksheets(sheet_name)
except Exception as e:
raise RuntimeError("Read excel file error:{}".format(e))
for r in range(rows[0], rows[1]):
row_data = []
for c in range(cols[0], cols[1]):
row_data.append(_get_cell(ws, r, c+1))
data.append(row_data)
wb.Close()
excel.Application.Quit()
return data
def write_to_excel(file, sheet_name):
pythoncom.CoInitialize()
excel = win32.DispatchEx("excel.application")
excel.Visible = 1
try:
wb = excel.workbooks.Open(file)
ws = wb.Worksheets(sheet_name)
result_json = read_json_file(result_file_path)
except Exception as e:
raise RuntimeError("Read excel or json file error:{}".format(e))
header = ["来料顺序","SPU箱代码","长(mm)","宽(mm)","高(mm)","毛重(kg)","体积(m3)","地址","托盘序号",'码垛体积(m3)']
ws.Range('A1:J1').value = header
beg_row = 2
for i in range(len(result_json)):
placed_volume = 0
for j in range(len(result_json[i])):
row = beg_row + j
volume = result_json[i][j]["size"][0]/1000 * result_json[i][j]["size"][1]/1000 * result_json[i][j]["size"][2]/1000
ws.Cells(row,1).value = j + 1
ws.Cells(row,2).value = result_json[i][j]["property"]["type"]
ws.Cells(row,3).value = result_json[i][j]["size"][0]
ws.Cells(row,4).value = result_json[i][j]["size"][1]
ws.Cells(row,5).value = result_json[i][j]["size"][2]
ws.Cells(row,6).value = result_json[i][j]["property"]["weight"]
ws.Cells(row,7).value = ('%.4f' % volume)
ws.Cells(row,8).value = "呼和浩特"
placed_volume += volume
end_row = beg_row + len(result_json[i]) - 1
pallet_range = 'I' +str(beg_row) + ':' + 'I' + str(end_row)
volume_range = 'J' +str(beg_row) + ':' + 'J' + str(end_row)
ws.Range(pallet_range).Merge()
ws.Range(volume_range).Merge()
ws.Cells(beg_row,9).value = i + 1
ws.Cells(beg_row,10).value = ('%.4f' % placed_volume)
beg_row = end_row + 1
write_to_excel(excel_result_path, "Sheet1")
|
