def anchor(self):
""" returns the expected position of a cell in pixels from the top-left
of the sheet. For example, A1 anchor should be (0,0).
:rtype: tuple(int, int)
"""
left_columns = (column_index_from_string(self.column) - 1)
column_dimensions = self.parent.column_dimensions
left_anchor = 0
default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH)
for col_idx in range(left_columns):
letter = get_column_letter(col_idx + 1)
if letter in column_dimensions:
cdw = column_dimensions.get(letter).width or default_width
if cdw > 0:
left_anchor += points_to_pixels(cdw)
continue
left_anchor += default_width
row_dimensions = self.parent.row_dimensions
top_anchor = 0
top_rows = (self.row - 1)
default_height = points_to_pixels(DEFAULT_ROW_HEIGHT)
for row_idx in range(1, top_rows + 1):
if row_idx in row_dimensions:
rdh = row_dimensions[row_idx].height or default_height
if rdh > 0:
top_anchor += points_to_pixels(rdh)
continue
top_anchor += default_height
return (left_anchor, top_anchor)
def point_pos(self, left=0, top=0):
""" tells which cell is under the given coordinates (in pixels)
counting from the top-left corner of the sheet.
Can be used to locate images and charts on the worksheet """
current_col = 1
current_row = 1
column_dimensions = self.column_dimensions
row_dimensions = self.row_dimensions
default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH)
default_height = points_to_pixels(DEFAULT_ROW_HEIGHT)
left_pos = 0
top_pos = 0
while left_pos <= left:
letter = get_column_letter(current_col)
current_col += 1
if letter in column_dimensions:
cdw = column_dimensions[letter].width
if cdw is not None:
left_pos += points_to_pixels(cdw)
continue
left_pos += default_width
while top_pos <= top:
row = current_row
current_row += 1
if row in row_dimensions:
rdh = row_dimensions[row].height
if rdh is not None:
top_pos += points_to_pixels(rdh)
continue
top_pos += default_height
return (letter, row)
def point_pos(self, left=0, top=0):
""" tells which cell is under the given coordinates (in pixels)
counting from the top-left corner of the sheet.
Can be used to locate images and charts on the worksheet """
current_col = 1
current_row = 1
column_dimensions = self.column_dimensions
row_dimensions = self.row_dimensions
default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH)
default_height = points_to_pixels(DEFAULT_ROW_HEIGHT)
left_pos = 0
top_pos = 0
while left_pos <= left:
letter = get_column_letter(current_col)
current_col += 1
if letter in column_dimensions:
cdw = column_dimensions[letter].width
if cdw is not None:
left_pos += points_to_pixels(cdw)
continue
left_pos += default_width
while top_pos <= top:
row = current_row
current_row += 1
if row in row_dimensions:
rdh = row_dimensions[row].height
if rdh is not None:
top_pos += points_to_pixels(rdh)
continue
top_pos += default_height
return (letter, row)
def __fill_week(self, work_sheet, line_number, week_activities, monday_date):
calories_str = ''
total_calories_burnt = 0
for day_activities in week_activities:
for activity_key in day_activities.keys():
# print(str(activity_key) + " " + str(len(day_activities[activity_key])))
day_of_the_week = activity_key - monday_date
cell = work_sheet.cell(line_number, 3 + day_of_the_week.days)
activities_for_day = day_activities[activity_key]
calories_burnt = 0
if cell.comment is None or cell.value is None or work_sheet.cell(line_number, self.CALORIES_COLUMN) is None:
# If everything filled then there is nothing to do
calories_burnt = int(self.__fill_day(cell, activities_for_day))
if calories_str == '':
calories_str = str(calories_burnt)
else:
calories_str += "+" + str(calories_burnt)
#Looks like there is a bug in recent excel that resets size of comments
cell.comment.width = units.points_to_pixels(250)
cell.comment.height = units.points_to_pixels(cell.comment.content.count('\n') * 20)
week_report = self.__fill_week_report(week_activities, total_calories_burnt)
work_sheet.cell(line_number, self.REPORT_COLUMN).value = week_report
if work_sheet.cell(line_number, self.CALORIES_COLUMN).value is None:
work_sheet.cell(line_number, self.CALORIES_COLUMN).value = "=SUM(" + calories_str + ")"
def anchor(self):
""" returns the expected position of a cell in pixels from the top-left
of the sheet. For example, A1 anchor should be (0,0).
:rtype: tuple(int, int)
"""
left_columns = (column_index_from_string(self.column) - 1)
column_dimensions = self.parent.column_dimensions
left_anchor = 0
default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH)
for col_idx in range(left_columns):
letter = get_column_letter(col_idx + 1)
if letter in column_dimensions:
cdw = column_dimensions.get(letter).width or default_width
if cdw > 0:
left_anchor += points_to_pixels(cdw)
continue
left_anchor += default_width
row_dimensions = self.parent.row_dimensions
top_anchor = 0
top_rows = (self.row - 1)
default_height = points_to_pixels(DEFAULT_ROW_HEIGHT)
for row_idx in range(1, top_rows + 1):
if row_idx in row_dimensions:
rdh = row_dimensions[row_idx].height or default_height
if rdh > 0:
top_anchor += points_to_pixels(rdh)
continue
top_anchor += default_height
return (left_anchor, top_anchor)
def writeCell(cell, data):
if isinstance(data, list):
row_num = cell.row
column_num = cell.column
for el in data:
writeCell(sheet.cell(row=row_num, column=column_num), el)
column_num += 1
return
elif isinstance(data, tuple) and not isinstance(data, HyperLink):
if data[1]:
cell.comment = Comment(data[1], 'Generator')
cell.comment.width = units.points_to_pixels(300)
data = data[0]
if isinstance(data, HyperLink):
cell.value = data.text
cell.hyperlink = data.link
cell.style = 'Hyperlink'
else:
cell.value = data
if isinstance(data, int) or isinstance(data, float):
cell.alignment = Alignment(horizontal='right')
else:
cell.alignment = Alignment(horizontal='left')
def __fill_day(self, cell, activities_for_day):
cell_value = ''
comment_value = ''
#Calculate callories and effort
calories_burnt = 0
for activity in activities_for_day: # strava activity objects for a given date
comment_value = comment_value + str(activity.type) + ": "
cell_value = cell_value + str(activity.type) + ": " + str(activity.name) + "\n"
if activity.average_speed is not None and activity.moving_time is not None and activity.distance is not None:
calories = 0
if activity.type == "Run":
calories = float(self.bmr * activity.average_speed * 3.6 * activity.moving_time.seconds) / (24 * 3600)
elif activity.type == "Swim":
calories = self.bmr * 8.5 * activity.moving_time.seconds / (24 * 3600)
elif activity.type == "Ride":
calories = float(self.bmr * activity.average_speed * 3.6 * activity.moving_time.seconds) / (24 * 3600 * 3)
# training_stats.cycling_distance = round(float(activity.distance) / 1000, 3)
# training_stats.cycling_time_in_seconds = activity.moving_time.seconds
calories_burnt += calories
comment_value += " Calories:" + str(round(calories, 0)) + "\n"
if activity.average_speed is not None:
comment_value += " Average speed: " + str(round(float(activity.average_speed * 3.6), 2)) + "km/h\n"
if activity.max_speed is not None and int(activity.max_speed) != 0:
comment_value += " Max speed: " + str(round(float(activity.max_speed * 3.6), 2)) + "km/h\n"
if activity.moving_time is not None:
comment_value += " Moving time: " + str(activity.moving_time) + "\n"
if activity.average_heartrate is not None:
comment_value += " Average heart rate: " + str(activity.average_heartrate) + "bpm\n"
if activity.distance is not None:
comment_value += " Distance: " + str(round(float(activity.distance) / 1000, 3)) + "kms\n"
comment_value += "\n"
#Add comment
if cell.comment is None:
cell.comment = Comment(comment_value, "ME")
cell.comment.width = units.points_to_pixels(250)
cell.comment.height = units.points_to_pixels(comment_value.count('\n') * 20)
if cell.value is None:
cell.value = cell_value
return calories_burnt
def add_img(self, ws, ColRow, path_img):
"""Add an image
The image size is automatically adjusted.
Parameters
-------
ws : openpyxl.worksheet.worksheet.Worksheet
worksheet
ColRow : list[int]
range of the cell
path_img : str
the path of the image data to be added
"""
#Calculating the height of cells in a range with
#the number of cells and the default cell height
height_pt = (ColRow[3] - ColRow[1] + 1) * self.unit_pt_height
height_pix = points_to_pixels(height_pt)
#Calculating the width of cells in a range with
#the number of cells and the default cell width
width_pix = (ColRow[2] - ColRow[0] + 1) * self.unit_pix_width
tmp_img = Image(path_img)
aspct = tmp_img.height / tmp_img.width
if aspct >= 1:
tmp_img.height = height_pix
tmp_img.width = height_pix / aspct
if tmp_img.width > width_pix:
tmp_img.width = width_pix
tmp_img.height = tmp_img.width * aspct
else:
tmp_img.width = width_pix
tmp_img.height = width_pix * aspct
if tmp_img.height > height_pix:
tmp_img.height = height_pix
tmp_img.width = tmp_img.height / aspct
anc = ws.cell(column=ColRow[0], row=ColRow[1]).coordinate
ws.add_image(tmp_img, anc)
if __name__ == '__main__':
args = parser.parse_args()
cdn_model_test.backupcdn_ip = args.ip
cookies = {}
if args.cookies:
for cookie in args.cookies:
for kv in cookie.split(';'):
name, value = kv.strip().split('=', 1)
cookies[name] = value
runner = unittest.TextTestRunner(verbosity=2)
result = runner.run(suite(args.domain, cookies, args.head))
skip = NamedStyle(name="skip")
skip.fill = PatternFill('solid', fgColor='FFFFDC4C')
error = NamedStyle(name="error")
error.fill = PatternFill('solid', fgColor='FFFF0000')
for (case, desc) in result.errors[0:] + result.failures[0:]:
case._cell.style = error
case._cell.comment = Comment(desc,
'AutoTestor',
width=units.points_to_pixels(500),
height=units.points_to_pixels(100))
for (case, desc) in result.skipped:
case._cell.style = skip
case._cell.comment = Comment(desc, 'AutoTestor')
for (wb, file) in wbs:
t = os.path.splitext(file)
wb.save(''.join((t[0], '_result', t[1])))
def get(self):
wish_order_id = self.args["wish_order_id"]
wish_order = models.WishOrder.get_by_id(self.session, wish_order_id)
if not wish_order:
self.write("没有找到对应的意向单")
raise Finish()
# 所有订货单
demand_orders_shops = self.session.query(models.DemandOrder, models.Shop) \
.join(models.Shop) \
.filter(models.DemandOrder.wish_order_id == wish_order_id,
models.DemandOrder.status == 2) \
.all()
# 订货的门店列表，表头门店顺序基于此
shops = list({shop for o, shop in demand_orders_shops})
shop_order = {"仓库": -999, "总部": 1, "刘园": 2, "侯台": 3,
"咸水沽": 4, "华明": 5,
"大港": 6, "杨村": 7, "新立": 8, "大寺": 9, "汉沽": 10,
"沧州": 11, "静海": 13, "芦台": 14, "工农村": 15, "唐山": 16, "廊坊": 17,
"哈尔滨": 18, "西青道": 19, "双鸭山": 20, "承德": 21,
"张胖子": 22, "固安": 23, "燕郊": 24, "胜芳": 25, "蓟县": 26, }
shops = sorted(shops, key=lambda d: shop_order.get(d.abbreviation, 999))
# 意向单商品列表，左侧商品列表顺序基于此
wish_goods_list = self.session.query(models.WishOrderGoods, models.Goods) \
.join(models.Goods) \
.filter(models.WishOrderGoods.wish_order_id == wish_order_id,
models.WishOrderGoods.status >= 0) \
.order_by(models.WishOrderGoods.status.asc(),
models.WishOrderGoods.priority.asc()) \
.all()
# 所有订货单商品
demand_order_ids = {o.id for o, s in demand_orders_shops}
demand_goods_list = self.session.query(models.DemandOrderGoods) \
.filter(models.DemandOrderGoods.demand_order_id.in_(demand_order_ids)) \
.all()
demand_order_dict = {o.id: o for o, s in demand_orders_shops}
demand_goods_dict = {
"{}:{}".format(g.wish_order_goods_id,
demand_order_dict.get(g.demand_order_id).shop_id if g.demand_order_id else -1)
: g
for g in demand_goods_list
}
workbook = Workbook()
worksheet = workbook.active
worksheet.title = "母单"
worksheet.freeze_panes = "F4"
alignment_center = Alignment(horizontal="center", vertical="center", shrink_to_fit=True)
border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
fill_current_storage = PatternFill("solid", fgColor="A6A6A6")
fill_ambiguous_demand_amount = PatternFill("solid", fgColor="92D050")
font_title = Font(name="华文楷体", size=18, bold=True)
font_head = Font(name="宋体", size=11)
font_head_accent = Font(name="宋体", size=11, color="FF0000")
font_shop_name = Font(name="微软雅黑", size=11, bold=True)
font_goods_name = Font(name="宋体", size=11)
font_goods_name_accent = Font(name="宋体", size=11, color="FF0000")
font_sum_demand_amount = Font(name="微软雅黑", size=11, bold=True)
font_demand_amount = Font(name="微软雅黑", size=20, bold=True, color="FF0000")
font_current_storage = Font(name="微软雅黑", size=10)
# 库存列宽
worksheet.column_dimensions[get_column_letter(1)].width = points_to_pixels(3.83)
# 商品编码列宽
worksheet.column_dimensions[get_column_letter(2)].width = points_to_pixels(0)
# 商品名列宽
worksheet.column_dimensions[get_column_letter(3)].width = points_to_pixels(16.33)
# 负责人列宽
worksheet.column_dimensions[get_column_letter(4)].width = points_to_pixels(1.83)
# 总数量列宽
worksheet.column_dimensions[get_column_letter(5)].width = points_to_pixels(3.83)
# 门店名行高
worksheet.row_dimensions[2].height = points_to_pixels(28)
# 意向数量表头行高
worksheet.row_dimensions[3].height = points_to_pixels(13)
# 标题
title = "嘿嘛{}月{}日各店剩货及意向单".format(wish_order.wish_date.month, wish_order.wish_date.day)
cell_title = worksheet.cell(column=1, row=1, value=title)
cell_title.font = font_title
cell_title.alignment = alignment_center
worksheet.merge_cells(start_row=1, end_row=1,
start_column=1, end_column=5 + 2 * len(shops)) # 合并标题
style_range(worksheet, "{}{}:{}{}".format(get_column_letter(1), 1, get_column_letter(5 + 2 * len(shops)), 1), border=border)
# 表头
cell_head_storage = worksheet.cell(column=1, row=2, value="库存")
cell_head_storage.font = font_head
cell_head_storage.alignment = alignment_center
worksheet.merge_cells("A2:A3")
style_range(worksheet, "A2:A3", border=border)
cell_head_code = worksheet.cell(column=2, row=2, value="商品编码")
cell_head_code.font = font_head
cell_head_code.alignment = alignment_center
worksheet.merge_cells("B2:B3")
style_range(worksheet, "B2:B3", border=border)
cell_head_goods_name = worksheet.cell(column=3, row=2, value="商品名称")
cell_head_goods_name.font = font_head
cell_head_goods_name.alignment = alignment_center
cell_head_goods_name.border = border
worksheet.merge_cells("C2:C3")
style_range(worksheet, "C2:C3", border=border)
cell_head_purchaser = worksheet.cell(column=4, row=2, value="负责人")
cell_head_purchaser.font = font_head
cell_head_purchaser.alignment = alignment_center
cell_head_purchaser.border = border
worksheet.merge_cells("D2:D3")
style_range(worksheet, "D2:D3", border=border)
cell_head_sum = worksheet.cell(column=5, row=2, value="总数量")
cell_head_sum.font = font_head
cell_head_sum.alignment = alignment_center
cell_head_sum.border = border
cell_head_sum_demand_amount = worksheet.cell(column=5, row=3, value="意向数量")
cell_head_sum_demand_amount.font = font_head
cell_head_sum_demand_amount.alignment = alignment_center
cell_head_sum_demand_amount.border = border
# 填门店名
for i, shop in enumerate(shops):
# 跳过货品信息和总订货量列
base_column = 6 + i * 2
# 跳过标题
base_row = 2
# 门店名表头
cell_shop_name = worksheet.cell(column=base_column, row=base_row, value=shop.abbreviation)
cell_shop_name.font = font_shop_name
cell_shop_name.alignment = alignment_center
cell_shop_name.border = border
worksheet.merge_cells(start_row=base_row, end_row=base_row, start_column=base_column, end_column=base_column + 1)
style_range(worksheet, "{}{}:{}{}".format(get_column_letter(base_column), base_row,
get_column_letter(base_column + 1), base_row), border=border)
# 剩货数量表头
cell_head_current_storage = worksheet.cell(column=base_column, row=base_row + 1, value="剩货数量")
cell_head_current_storage.font = font_head
cell_head_current_storage.alignment = alignment_center
cell_head_current_storage.border = border
# 意向数量表头
cell_head_demand_amount = worksheet.cell(column=base_column + 1, row=base_row + 1, value="意向数量")
cell_head_demand_amount.font = font_head_accent
cell_head_demand_amount.alignment = alignment_center
cell_head_demand_amount.border = border
# 列宽
worksheet.column_dimensions[get_column_letter(base_column)].width = points_to_pixels(0.91)
worksheet.column_dimensions[get_column_letter(base_column + 1)].width = points_to_pixels(3.83)
# 填货品信息
ambiguous = False
ambiguous_row = -1
ambiguous_sum_dict = defaultdict(float)
for i, (wish_goods, goods) in enumerate(wish_goods_list):
# 跳过表头
base_row = 4 + i
# 不保证有货行
if wish_goods.status > 0 and not ambiguous:
ambiguous = True
ambiguous_row = base_row
cell_head_ambiguous_goods_name = worksheet.cell(column=3, row=base_row, value="下方产品不保证有货")
cell_head_ambiguous_goods_name.font = font_goods_name_accent
cell_head_ambiguous_goods_name.alignment = alignment_center
cell_head_ambiguous_goods_name.border = border
# 被不保证有货行下推一行
if ambiguous:
base_row += 1
# 库存
cell_storage = worksheet.cell(column=1, row=base_row, value=check_float(goods.stock / 100))
cell_storage.alignment = alignment_center
cell_storage.border = border
cell_storage.font = font_goods_name
# 商品编码
cell_code = worksheet.cell(column=2, row=base_row, value=goods.code)
cell_code.alignment = alignment_center
cell_code.border = border
cell_code.font = font_goods_name
# 商品名
cell_goods_name = worksheet.cell(column=3, row=base_row, value=wish_goods.goods_name or goods.name)
cell_goods_name.alignment = alignment_center
cell_goods_name.border = border
if wish_goods.goods_name_modified:
cell_goods_name.font = font_goods_name_accent
else:
cell_goods_name.font = font_goods_name
# 负责人
cell_purchaser = worksheet.cell(column=4, row=base_row, value="")
cell_purchaser.alignment = alignment_center
cell_purchaser.border = border
cell_purchaser.font = font_goods_name
demand_sum = 0
for j, shop in enumerate(shops):
demand_goods = demand_goods_dict.get("{}:{}".format(wish_goods.id, shop.id))
# 跳过货品信息和总订货量列
base_column = 6 + j * 2
# 门店库存
current_storage = check_float(demand_goods.current_storage / 100) if demand_goods else 0
cell_current_storage = worksheet.cell(column=base_column, row=base_row, value=current_storage)
cell_current_storage.font = font_current_storage
cell_current_storage.alignment = alignment_center
cell_current_storage.border = border
cell_current_storage.fill = fill_current_storage
# 门店订货量
if demand_goods:
if demand_goods.modified_demand_amount is None:
demand_amount = demand_goods.demand_amount
else:
demand_amount = demand_goods.modified_demand_amount
demand_amount = check_float(demand_amount / 100)
else:
demand_amount = 0
cell_demand_amount = worksheet.cell(column=base_column + 1, row=base_row, value=demand_amount)
cell_demand_amount.font = font_demand_amount
cell_demand_amount.alignment = alignment_center
cell_demand_amount.border = border
# 该商品各店总订货量
demand_sum += demand_amount
# 各店不保证有货商品总订货量
if wish_goods.status > 0:
ambiguous_sum_dict[shop.id] += demand_amount
# 总意向数量
cell_sum_demand_amount = worksheet.cell(column=5, row=base_row, value=check_float(demand_sum))
cell_sum_demand_amount.font = font_sum_demand_amount
cell_sum_demand_amount.alignment = alignment_center
cell_sum_demand_amount.border = border
# 不保证有货商品行的信息
if ambiguous_row >= 0:
for j, shop in enumerate(shops):
# 跳过货品信息和总订货量列，取意向数量列
base_column = 6 + j * 2 + 1
demand_sum = check_float(ambiguous_sum_dict.get(shop.id, 0))
cell_sum_ambiguous_demand_amount = worksheet.cell(column=base_column, row=ambiguous_row, value=demand_sum)
cell_sum_ambiguous_demand_amount.font = font_demand_amount
cell_sum_ambiguous_demand_amount.alignment = alignment_center
cell_sum_ambiguous_demand_amount.border = border
cell_sum_ambiguous_demand_amount.fill = fill_ambiguous_demand_amount
# 所有店不保证有货商品订货量总和
total_ambiguous_sum = reduce(lambda s, i: s + i, ambiguous_sum_dict.values(), 0)
cell_sum_ambiguous_demand_amount = worksheet.cell(column=5, row=ambiguous_row, value=check_float(total_ambiguous_sum))
cell_sum_ambiguous_demand_amount.font = font_sum_demand_amount
cell_sum_ambiguous_demand_amount.alignment = alignment_center
cell_sum_ambiguous_demand_amount.border = border
file_name = title
return self.export_xlsx(workbook, file_name)