def monthly(sheet: Worksheet, db: AccountDatabase):
y = db.distinct('YEAR').__next__()[0]
header = (['单位全名', '邮编'] + ['{}月合计'.format(i + 1)
for i in range(12)] + ['年合计'])
for i, j in enumerate(header):
sheet.write(0, i, j, BORDC)
sheet.col(i).width_mismatch = True
sheet.col(i).width = 3400 if i < 1 else 2100
clients = []
for cli in db.sales_map.values():
clients += cli
clients.sort()
for i, cli in enumerate(clients, 1):
sheet.write(i, 0, cli, BORD)
sheet.write(i, 1, db.client_map[cli], BORDC)
rsum = 0
for j in range(1, 13):
whr = 'MONTH={} AND CLIENT={} AND BASIC=1'.format(j, repr(cli))
val = db.select('SUM(WEIGHT)', whr).__next__()[0]
val = val / 1000 if val else 0
sheet.write(i, j + 1, val if val else '', BORD)
rsum += val if val else 0
sheet.write(i, 14, rsum, BORD)
def pivottable_3d(sheet: Worksheet, db: AccountDatabase):
dates = db.sorted_one('DATE')
month = xlrd.xldate_as_tuple(dates[0], 0)[1]
meal = db.select('DISTINCT MEAL').__next__()[0]
school = db.select('DISTINCT SCHOOL').__next__()[0]
title = '横县农村义务教育学生营养改善计划每日开餐情况统计 （{}月{}）'
title = title.format(month, meal)
sheet.write_merge(0, 0, 0, len(dates)+1, title, TITLE)
sheet.write(1, 0, '学校名称：{}'.format(school))
sheet.write(2, 0, '明细', HEAD)
for i, date in enumerate(dates):
xld = xlrd.xldate_as_tuple(date, 0)[1]
sheet.write(2, i+1, date, MONTH)
sheet.col(i+1).width_mismatch = True
sheet.col(i+1).width = 1500
sheet.write(2, i+2, '总计', HEAD)
where = 'DATE={} AND NAME={}'
idx = 3
sheet.write(idx, 0, '大米', SUM)
row_sum = 0
for i, date in enumerate(dates):
cur = db.select('SUM(TOTAL)', where.format(date, repr('大米')))
xs = cur.__next__()[0]
row_sum += xs if xs else 0
sheet.write(idx, i+1, xs if xs else '', SUM)
sheet.write(idx, i+2, row_sum, SUM)
idx += 1
kinds = db.sorted_one('KIND')
for kind in kinds:
names = db.sorted_one('NAME', 'KIND={}'.format(repr(kind)))
col_sum = [0] * len(dates)
for name in names:
row_sum = 0
sheet.write(idx, 0, name, TEXT)
for i, date in enumerate(dates):
cur = db.select('SUM(TOTAL)', where.format(date, repr(name)))
xs = cur.__next__()[0]
row_sum += xs if xs else 0
col_sum[i] += xs if xs else 0
sheet.write(idx, i+1, xs if xs else '', TEXT)
sheet.write(idx, i+2, row_sum, TEXT)
idx += 1
sheet.write(idx, 0, '{}合计'.format(kind), SUM)
for i, s in enumerate(col_sum):
sheet.write(idx, i+1, s, SUM)
sheet.write(idx, i+2, sum(col_sum), SUM)
idx += 1
for i in ['每日合计', '每日开餐人数', '人均开餐金额', '陪餐人数']:
sheet.write(idx, 0, i, TEXT)
for j in range(1, len(dates)+2):
sheet.write(idx, j, '', TEXT)
idx += 1
def header_write(sheet: Worksheet, row, col_beg, header):
style = StyleTitle()
for i, field in enumerate(header):
col = col_beg + i
title, width = field
sheet.write(row, col, title, style)
sheet.col(col).width = width
def write_old_xls(worksheet: xlwt.Worksheet, data, offset=0, ):
style = xlwt.Style.easyxf(
f"font: height 200, name 宋体; align: vert centre, horiz center, wrap 1; borders: left {xlwt.Borders.THIN},"
f" right {xlwt.Borders.THIN}, top {xlwt.Borders.THIN}, bottom {xlwt.Borders.THIN}")
for k, v in enumerate(data):
k = k + offset
height = 50
for key, value in enumerate(v):
width = worksheet.col(key).width
if str(value).split(".")[-1].upper() in ["JPG", "JPEG", "PNG"]:
value = value.replace(config["FILE"]["PATH"], config["FILE"]["IP"])
if len(str(value)) * 200 > worksheet.col(key).width:
width = len(value) * 200
worksheet.col(key).width = width
if "http" in str(value):
value = xlwt.Formula(f'Hyperlink("{value}";"{value}")')
worksheet.write(k, key, value, style=style)
worksheet.row(k).height_mismatch = True
worksheet.row(k).height = int(20 * height)
def salesman(sheet: Worksheet, db: AccountDatabase):
s = db.distinct('SALES').__next__()[0]
clients = db.sorted_one('CLIENT')
for i, j in enumerate(HEADER):
sheet.write(0, i, j, BORDC)
sheet.col(i).width_mismatch = True
sheet.col(i).width = 3400 if i < 1 else 2100
nrow = 1
write_sales(sheet, db, s, nrow)
def write_to_book(sheet: Worksheet, row: int, cur_inf: dict):
"""
Запись обработанной информации в Worksheet.
"""
row = sheet.row(row)
style = XFStyle()
for index, value in enumerate(cur_inf.values()):
if (row != 0) and (index != 0 or 3):
style.num_format_str = '"₽"#,##0.0000;"₽"#,##0.0000'
sheet.col(index).width = 4000
row.write(index, value, style=style)
def write(sheet: xlwt.Worksheet, row: int, col: int, value: Any,
style: xlwt.XFStyle) -> None:
"""Write text to a cell and auto-fit the column width."""
sheet.write(row, col, value, style)
char_width = 256
text_width = len(str(value)) * char_width
column = sheet.col(col)
if column.get_width() < text_width:
column.set_width(text_width)
def distribute(sheet: Worksheet, db: AccountDatabase, route_map: RouteMap):
cur = db.select('DISTINCT ROUTE')
route = cur.__next__()[0]
sheet.write(0, 0, route, SCHOOL)
sheet.write(0, 1, '规格', SCHOOL)
# write goods tags in kinds' order
kinds = db.sorted_one('KIND')
goods = []
for kind in kinds:
cur = db.select('DISTINCT NAME, SPEC', 'KIND={}'.format(repr(kind)))
goods += list(cur)
for i, (good, spec) in enumerate(goods):
sheet.write(i + 1, 0, good, TEXT)
sheet.write(i + 1, 1, spec, TEXT)
goods_sum = [0] * len(goods)
# write schools in appearing order
cur = db.select('DISTINCT SCHOOL')
schools = route_map.sort_school([i[0] for i in cur])
for i, school in enumerate(schools, 1):
sheet.col(i + 1).width_mismatch = True
sheet.col(i + 1).width = 2000
sheet.write(0, i + 1, route_map.schools[school][1], SCHOOL)
for j, (good, spec) in enumerate(goods):
where = 'SCHOOL={} AND NAME={} AND SPEC={}'.format(
repr(school), repr(good), repr(spec))
cur = db.select('SUM(NUMBER)', where)
value = cur.__next__()[0]
goods_sum[j] += value if value else 0
sheet.write(j + 1, i + 1, value if value else '', TEXT)
# write goods sum
sheet.col(i + 2).width_mismatch = True
sheet.col(i + 2).width = 2000
sheet.write(0, i + 2, '总计', SCHOOL)
for j, gd in enumerate(goods_sum):
sheet.write(j + 1, i + 2, gd, TEXT)
def annually(sheet: Worksheet, db: AccountDatabase):
sheet.write(0, 0, '', BORD)
header = HEADER[2:]
for i, h in enumerate(header, 1):
sheet.write(0, i, h, BORDC)
for i in range(len(header) + 1):
sheet.col(i).width_mismatch = True
sheet.col(i).width = 2100
ssum = [0] * len(header)
for month in range(1, 13):
sheet.write(month, 0, '{}月'.format(month), BORD)
# write basic kinds
ksum = 0
for i, kind in enumerate(KINDS, 1):
whr = 'MONTH={} AND KIND={}'.format(month, repr(kind))
val = db.select('SUM(WEIGHT)', whr).__next__()[0]
val = val / 1000 if val else 0
ksum += val
ssum[i - 1] += val
sheet.write(month, i, val if val else '', BORD)
sheet.write(month, i + 1, ksum if ksum else '', BORD)
ssum[i] += ksum
# write extra kinds
for i, kind in enumerate(EXPANDS, i + 2):
try:
kind = '{}.0'.format(int(kind))
except:
pass
whr = 'MONTH={} AND KIND={}'.format(month, repr(kind))
val = db.select('SUM(WEIGHT)', whr).__next__()[0]
val = val / 1000 if val else 0
ssum[i - 1] += val
sheet.write(month, i, val if val else '', BORD)
# write col sum
sheet.write(13, 0, '合计', UBORDC)
for i, val in enumerate(ssum, 1):
sheet.write(13, i, val)