#coding=utf-8 import openpyxl def read_sheet(book_name, sheet_name): return openpyxl.load_workbook(book_name)[sheet_name] def get_pai2times(sheet, pai_col, time_col): records = dict() for row in sheet.iter_rows(min_row=2, max_row = sheet.max_row): pai, time = row[pai_col].value, row[time_col].value if pai not in records: records[pai] = [time] else: records[pai].append(time) return records def fetch_one(pai, ilist, olist): if not ilist: otime = olist[0] olist.remove(otime) return [pai, None, otime] if not olist: itime = ilist[0] ilist.remove(itime) return [pai, itime, None] itime = ilist[0] ilist.remove(itime) itime_nxt = ilist[0] if len(ilist) > 0 else None otime = None for curr_time in olist: # 没有下一次进场,出场时间比进场时间大就行 if itime_nxt is None: if curr_time > itime: otime = curr_time # 有下一次进场,出场时间必须介于两次进场时间中间 else: if curr_time >= itime and curr_time <= itime_nxt: otime = curr_time if otime is not None: olist.remove(otime) break return [pai, itime, otime] def write_xlsx(xlsx_file, inr, outr): all_pai = set(inr.keys()) | set(outr.keys()) new_xlsx = openpyxl.Workbook() sheet1 = new_xlsx.active sheet1.append(["车牌", "进场时间", "出场时间"]) for pai in all_pai: ilist = [] if pai not in inr else inr[pai] ilist = sorted(ilist, key=lambda x: x.timestamp()) olist = [] if pai not in outr else outr[pai] olist = sorted(olist, key=lambda x: x.timestamp()) while len(ilist) > 0 or len(olist) > 0: row = fetch_one(pai=pai, ilist=ilist, olist=olist) sheet1.append(row) with open(xlsx_file, "wb") as f: new_xlsx.save(f) if __name__ == '__main__': inr = get_pai2times( sheet=read_sheet(book_name='in.xlsx', sheet_name='Sheet1'), pai_col=7, time_col=8 ) outr = get_pai2times( sheet=read_sheet(book_name='out.xlsx', sheet_name='Sheet1'), pai_col=7, time_col=8) write_xlsx(xlsx_file="total.xlsx", inr=inr, outr=outr)
标签:xlsx,itime,sheet,openpyxl,示例,ilist,time,pai From: https://www.cnblogs.com/rex4399/p/18120570