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 add_choice_row(sheet: xlwt.Worksheet, data: dict, row: int):
sheet.write(row, 0, data.get('q_description'))
sheet.write(row, 1, data.get('q_A'))
sheet.write(row, 2, data.get('q_B'))
sheet.write(row, 3, data.get('q_C'))
sheet.write(row, 4, data.get('q_D'))
sheet.write(row, 5, data.get('q_answer'))
sheet.write(row, 6, data.get('q_value'))
return
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 writeSheet(st: Worksheet, row, col, value):
"""
写入数据
:param st: Worksheet
:param row: 行数
:param col: 列数
:param value: 内容
"""
st.write(row, col, value)
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_rows(worksheet: xlwt.Worksheet):
print('Start to process write_rows in %s' % worksheet.name)
row_index = 0
while True:
row = yield
if row is None:
break
for col_index, cell in enumerate(row):
worksheet.write(row_index, col_index, cell.value)
row_index += 1
print('Complete writing %d rows in %s' % (row_index, worksheet.name))
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 write_problem_at_row(p: Problem, she: xlwt.Worksheet, curr: int):
style = xlwt.easyxf('font: bold 1, color red;')
if p.star:
she.write(curr, 0, xlwt.Formula(f'HYPERLINK("{p.link}";"{p.name}")'),
style)
else:
she.write(curr, 0, xlwt.Formula(f'HYPERLINK("{p.link}";"{p.name}")'))
she.write(curr, 1, p.judge)
she.write(curr, 2, p.topic)
she.write(curr, 4, p.difficulty)
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 _export_review_title(sheet: xlwt.Worksheet):
sheet.write(0, 0, '用户昵称')
sheet.write(0, 1, '商户ID')
sheet.write(0, 2, '商户名称')
sheet.write(0, 3, '星级评价')
sheet.write(0, 4, '评论文本')
sheet.write(0, 5, '评论时间')
def daily_sum(sheet: Worksheet, db: AccountDatabase):
header = ['货品类别', '货品名称', '规格', '数量']
for i, j in enumerate(header):
sheet.write(0, i, j, HEAD)
kinds = db.sorted_one('KIND')
idx = 1
for kind in kinds:
sheet.write(idx, 0, kind, KIND)
cur = db.select('DISTINCT NAME, SPEC', 'KIND={}'.format(repr(kind)))
ns = [i for i in cur]
for name, spec in ns:
where = 'NAME={} AND SPEC={}'.format(repr(name), repr(spec))
num = db.select('SUM(NUMBER)', where).__next__()[0]
sheet.write(idx, 1, name)
sheet.write(idx, 2, spec)
sheet.write(idx, 3, num)
idx += 1
sheet.set_panes_frozen(True)
sheet.set_horz_split_pos(1)
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 write_sales(sheet: Worksheet, db: AccountDatabase, sales: str,
nrow: int) -> int:
ssum = [0] * (len(HEADER) - 2)
for cli in db.sales_map[sales]:
sheet.write(nrow, 0, cli, BORD)
sheet.write(nrow, 1, sales, BORDC)
# write basic kinds
ksum = 0
for i, kind in enumerate(KINDS, 2):
whr = 'CLIENT={} AND KIND={}'.format(repr(cli), repr(kind))
val = db.select('SUM(WEIGHT)', whr).__next__()[0]
val = val / 1000 if val else 0
ksum += val
ssum[i - 2] += val
sheet.write(nrow, i, val if val else '', BORD)
sheet.write(nrow, i + 1, ksum if ksum else '', BORD)
ssum[i - 1] += ksum
# write extra kinds
for i, kind in enumerate(EXPANDS, i + 2):
try:
kind = '{}.0'.format(int(kind))
except:
pass
whr = 'CLIENT={} AND KIND={}'.format(repr(cli), repr(kind))
val = db.select('SUM(WEIGHT)', whr).__next__()[0]
val = val / 1000 if val else 0
ssum[i - 2] += val
sheet.write(nrow, i, val if val else '', BORD)
nrow += 1
# write sales sum
sheet.write(nrow, 1, '合计', UBORDC)
for i, val in enumerate(ssum, 2):
sheet.write(nrow, i, val)
nrow += 2
return nrow
def pivottable_2d(sheet: Worksheet, db: AccountDatabase,
vert: str, horz: str):
ys = db.sorted_one(vert)
xs = db.sorted_one(horz)
for i, x in enumerate(xs):
sheet.write(0, i+1, x, HEAD)
sheet.write(0, i+2, '总计', HEAD)
sheet.write(0, 0, '', HEAD)
col_sum = [0] * len(xs)
for i, y in enumerate(ys):
sheet.write(i+1, 0, y, DATE)
row_sum = 0
for j, x in enumerate(xs):
where = '{}={} AND {}={}'.format(vert, repr(y), horz, repr(x))
bsum = db.select('SUM(TOTAL)', where).__next__()[0]
sheet.write(i+1, j+1, bsum if bsum else '', TEXT)
row_sum += bsum if bsum else 0
col_sum[j] += bsum if bsum else 0
sheet.write(i+1, j+2, row_sum, TEXT)
sheet.write(i+2, 0, '总计', TEXT)
for j, s in enumerate(col_sum):
sheet.write(i+2, j+1, s, TEXT)
sheet.write(i+2, j+2, sum(col_sum), TEXT)
def pivottable_2d2(sheet: Worksheet, db: AccountDatabase,
vert: str, horz: str):
ys = db.sorted_one(vert)
xs = db.sorted_one(horz)
for i, x in enumerate(xs):
sheet.write_merge(0, 0, i*2+1, i*2+2, x, HEAD)
sheet.write(1, i*2+1, '数量', HEAD)
sheet.write(1, i*2+2, '货款', HEAD)
sheet.write_merge(0, 1, 0, 0, '', HEAD)
col_sum1 = [0] * len(xs)
col_sum2 = [0] * len(xs)
for i, y in enumerate(ys):
sheet.write(i+2, 0, y, TEXT)
for j, x in enumerate(xs):
where = '{}={} AND {}={}'.format(vert, repr(y), horz, repr(x))
sum1 = db.select('SUM(NUMBER)', where).__next__()[0]
sheet.write(i+2, j*2+1, sum1 if sum1 else '', TEXT)
col_sum1[j] += sum1 if sum1 else 0
sum2 = db.select('SUM(TOTAL)', where).__next__()[0]
sheet.write(i+2, j*2+2, sum2 if sum2 else '', TEXT)
col_sum2[j] += sum2 if sum2 else 0
sheet.write(i+3, 0, '总计', TEXT)
for j, s in enumerate(col_sum1):
sheet.write(i+3, j*2+1, s, TEXT)
for j, s in enumerate(col_sum2):
sheet.write(i+3, j*2+2, s, TEXT)
def add_judge_row(sheet: xlwt.Worksheet, data: dict, row: int):
sheet.write(row, 0, data.get('q_description'))
sheet.write(row, 1, data.get('q_answer'))
sheet.write(row, 2, data.get('q_value'))
return
def row_write(sheet: Worksheet, row, col_beg, cells):
for i, cell in enumerate(cells):
col = col_beg + i
if isinstance(cell, datetime):
_style.num_format_str = 'DD.MM.YYYY HH:MM:SS'
sheet.write(row, col, cell, _style)
def add_sheet_header(header: list, sheet: xlwt.Worksheet):
col = 0
for h in header:
sheet.write(0, col, h)
col += 1
return
def _export_review_record(sheet: xlwt.Worksheet, row: int, record: dict):
col = 0
if 'username' in record:
sheet.write(row, col, record['username'])
col += 1
if 'shop_id' in record:
sheet.write(row, col, record['shop_id'])
col += 1
if 'shop_name' in record:
sheet.write(row, col, record['shop_name'])
col += 1
if 'rating' in record:
sheet.write(row, col, _get_rating(record['rating']))
col += 1
if 'review' in record:
sheet.write(row, col, record['review'])
col += 1
if 'timestamp' in record:
sheet.write(row, col, _get_review_time(record['timestamp']))
col += 1
def write_student_info(sheet: Worksheet, student_info: dict, row_num: int) -> None:
sheet.write(row_num, 1, student_info['id'])
sheet.write(row_num, 2, student_info['name'])
sheet.write(row_num, 3, student_info['score'])
print('[write_student_info]')
def _export_shop_title(sheet: xlwt.Worksheet):
sheet.write(0, 0, '商户ID')
sheet.write(0, 1, '商户')
sheet.write(0, 2, '商户星级')
sheet.write(0, 3, '评论数量')
sheet.write(0, 4, '人均消费')
sheet.write(0, 5, '产品得分')
sheet.write(0, 6, '环境得分')
sheet.write(0, 7, '服务得分')
sheet.write(0, 8, '地址')
sheet.write(0, 9, '电话')
sheet.write(0, 10, 'URL')
def _export_shop_record(sheet: xlwt.Worksheet, row: int, record: dict):
col = 0
if '_id' in record:
sheet.write(row, col, record['_id'])
col += 1
if 'name' in record:
sheet.write(row, col, record['name'])
col += 1
if 'rating' in record:
sheet.write(row, col, record['rating'])
col += 1
if 'comments' in record:
sheet.write(row, col, record['comments'])
col += 1
if 'cost_avg' in record:
sheet.write(row, col, record['cost_avg'])
col += 1
if 'product_rating' in record:
sheet.write(row, col, record['product_rating'])
col += 1
if 'enviroment_rating' in record:
sheet.write(row, col, record['enviroment_rating'])
col += 1
if 'service_rating' in record:
sheet.write(row, col, record['service_rating'])
col += 1
if 'address' in record:
sheet.write(row, col, record['address'])
col += 1
if 'phone_number' in record:
sheet.write(row, col, record['phone_number'])
col += 1
if 'url' in record:
sheet.write(row, col, record['url'])
col += 1
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)