介绍一个PB直接读 .xlsx 的方法

本文介绍如何将.xlsx文件视为ZIP格式进行处理,利用uo_zip对象读取.xlsx内部结构,包括文件列表、大小、压缩信息等,并演示了如何以blob形式读取文件或解压指定内容。

.xlsx 实际上是ZIP格式文件,完全可以当作 ZIP 文件来处理。

然后直接用 uo_zip 解压它为指定文件,或者直接当作 blob 读出来,都可以。

在各PB群里,找到我的 PB_Json_httpclient_crypto_ftp_xxxxxx.rar 包,里面有个 uo_zip 对象。

1. 打开它:zip.open("c:\temp\Book1.xlsx",false,"")

2.zip 事件 ue_open 里读列表

//取出ZIP文件里的列表
long index[]
string strName[]
long fileSize[]
long fileSizeComp[]
string crc[]
string strDateTime[]
boolean encrypted[]
long ll_count,i
long ll_filecount,ll_dircount
string ls_text
ll_count = zip.GetEntryList(ref index[],ref strName[],ref fileSize[],ref fileSizeComp[],ref crc[],ref strDateTime[],ref encrypted[])
if ll_count = 0 then
    blob data
    data = blob("欢迎使用uo_zip对象.大自在2020/5/1")
    zip.add("hello",data)
end if
ls_text = "序号  大小      压缩大小   CRC码    修改时间             加密~t文件名~r~n"
for i = 1 to ll_count

from ortools.constraint_solver import routing_enums_pb2 from ortools.constraint_solver import pywrapcp import pandas as pd # 取数据文件 customers_file = r"D:\Matlab\code111\数模作业\题目4\研模训练题4\附件2.xlsx" distance_matrix_file = r"D:\Matlab\code111\数模作业\题目4\研模训练题4\distance_matrix.xlsx" # 取客户信息 (A9:G109) -> customers_df = pd.read_excel(customers_file, sheet_name='C101', header=None, skiprows=8, nrows=101) customers = customers_df.values # 提取客户属性(直接提取,不插入 depot) demands = customers_df.iloc[:, 3].astype(int).tolist() ready_time = customers_df.iloc[:, 4].astype(int).tolist() due_time = customers_df.iloc[:, 5].astype(int).tolist() service_time = customers_df.iloc[:, 6].astype(int).tolist() # 打印客户数量确认是否正确(应该是 101 个节点:1个 depot + 100个客户) num_customers = len(demands) print(f"总节点数: {num_customers}") # 取距离矩阵(101x101) distance_matrix = pd.read_excel(distance_matrix_file, header=None).values.astype(int) # 创建路由模型 def create_data_model(): return { 'distance_matrix': distance_matrix, 'demands': demands, 'ready_time': ready_time, 'due_time': due_time, 'service_time': service_time, 'num_vehicles': 30, 'vehicle_capacities': [200] * 30, # 注意这里是列表 'depot': 0 } def print_solution(data, manager, routing, solution): """打印路径结果""" total_distance = 0 total_load = 0 for vehicle_id in range(data['num_vehicles']): index = routing.Start(vehicle_id) plan_output = f'Route for vehicle {vehicle_id}:\n' route_distance = 0 route_load = 0 while not routing.IsEnd(index): node_index = manager.IndexToNode(index) route_load += data['demands'][node_index] plan_output += f' {node_index} Load({route_load}) -> ' previous_index = index index = solution.Value(routing.NextVar(index)) route_distance += routing.GetArcCostForVehicle(previous_index, index, vehicle_id) plan_output += f' {manager.IndexToNode(index)} Load({route_load})\n' plan_output += f'Distance of the route: {route_distance} units\n' plan_output += f'Load of the route: {route_load}\n' print(plan_output) total_distance += route_distance total_load += route_load print(f'Total Distance of all routes: {total_distance}') print(f'Total Load of all routes: {total_load}') def main(): data = create_data_model() # 创建管理器和路由模型 manager = pywrapcp.RoutingIndexManager(len(data['distance_matrix']), data['num_vehicles'], data['depot']) routing = pywrapcp.RoutingModel(manager) # 定义距离回调函数 def distance_callback(from_index, to_index): from_node = manager.IndexToNode(from_index) to_node = manager.IndexToNode(to_index) return data['distance_matrix'][from_node][to_node] transit_callback_index = routing.RegisterTransitCallback(distance_callback) routing.SetArcCostEvaluatorOfAllVehicles(transit_callback_index) # 添加容量约束(车辆载重限制) def demand_callback(from_index): from_node = manager.IndexToNode(from_index) return data['demands'][from_node] demand_callback_index = routing.RegisterUnaryTransitCallback(demand_callback) routing.AddDimensionWithVehicleCapacity( demand_callback_index, 0, # slack_max data['vehicle_capacities'], # 这里是 list 类型 True, # fix_start_cumul_to_zero 'Capacity' ) # 添加时间窗约束 time = 'Time' routing.AddDimension( transit_callback_index, 2000, # 大的松弛时间作为等待时间上限 2000, # 最大时间跨度 False, # 不强制从零开始 time ) time_dimension = routing.GetDimensionOrDie(time) # 设置每个节点的时间窗和服务时间 for location_idx in range(len(data['distance_matrix'])): if location_idx == data['depot']: continue index = manager.NodeToIndex(location_idx) time_dimension.CumulVar(index).SetRange(data['ready_time'][location_idx], data['due_time'][location_idx]) routing.AddToAssignment(time_dimension.TransitVar(index)) time_dimension.SetSpanCostCoefficientForAllVehicles(100) # 设置搜索参数 search_parameters = pywrapcp.DefaultRoutingSearchParameters() search_parameters.first_solution_strategy = ( routing_enums_pb2.FirstSolutionStrategy.PATH_CHEAPEST_ARC) search_parameters.local_search_metaheuristic = ( routing_enums_pb2.LocalSearchMetaheuristic.GUIDED_LOCAL_SEARCH) search_parameters.time_limit.FromSeconds(30) # 求解 solution = routing.SolveWithParameters(search_parameters) if solution: print_solution(data, manager, routing, solution) else: print('No solution found.') if __name__ == '__main__': main() 将VRPTW解进行可视化,物流中心编号0.放在中间点,100个客户在平面中用点表示。车辆路径用不同颜色的闭合环路表示。
最新发布
07-16
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值