def write_total(sheet):
count_ages = 20
col = FIRST_COLUMN + 1
row = FIRST_ROW
count_do = get_institution(0).count()
count_school = get_institution(1).count()
count_row = get_institution().count() + 3
for i, age in enumerate(get_list_age()):
formula_do = 'SUM({}:{})'.format(
Utils.rowcol_to_cell(row - 1 + i, col + 2),
Utils.rowcol_to_cell(row - 1 + i, col + 1 + count_do)
)
formula_school = 'SUM({}:{})'.format(
Utils.rowcol_to_cell(row - 1 + i, col + 2 + count_do + 1),
Utils.rowcol_to_cell(row - 1 + i, col + 2 + count_do + count_school)
)
formula_total_row = '{} + {} + {}'.format(
Utils.rowcol_to_cell(row - 1 + i, col + 1),
Utils.rowcol_to_cell(row - 1 + i, col + 1 + count_do + 1),
Utils.rowcol_to_cell(row - 1 + i, col + 1 + count_do + 2 + count_school),
)
sheet.write(col + i, row - 1, Formula(formula_total_row), style_bold)
sheet.write(col + i, row, Formula(formula_do), style_bold)
sheet.write(col + i, row + count_do + 1, Formula(formula_school), style_bold)
for j in range(count_row + 1):
formula_total_col = 'SUM({}:{})'.format(
Utils.rowcol_to_cell(row - 1, col + j),
Utils.rowcol_to_cell(row - 1 + count_ages - 1, col + j)
)
sheet.write(col + count_ages, row - 1 + j, Formula(formula_total_col), style_bold)
def CountAv(self):
numstyle = XFStyle()
numstyle.num_format_str = "0.0000"
if self.mode == 0:
i = 0
while i <= (self.finSize - self.startSize) / 100:
formula = 'AVERAGE(C1:%s)' % (Utils.rowcol_to_cell(
i, self.repeat + 1))
self.res.write(i, 2 + self.repeat, xlwt.Formula(formula),
numstyle)
i += 1
elif self.mode == 2:
formula = 'AVERAGE(C1:%s)' % (Utils.rowcol_to_cell(
0, self.repeat + 1))
self.res.write(0, 2 + self.repeat, xlwt.Formula(formula), numstyle)
def write_sitdsc(sitlineno=0, str_row=None, str_col=0, cross_col_no=2, csvlineno=None, height=None, style_head=None, style=None):
ws_sit.row(str_row).height_mismatch = 1
ws_sit.row(str_row).height = height
cont_list = lines[csvlineno].split(',')
ws_sit.write_merge(str_row, str_row, str_col, str_col + cross_col_no, cont_list[0], style_head)
for (i, content) in enumerate(cont_list[1:]):
if content == 'Non':
cellname = Utils.rowcol_to_cell(sitlineno, i + cross_col_no + 1)
ws_sit.write(str_row, i + cross_col_no + 1,
Formula("VLOOKUP(%s,sit_chn_dsc!$A$1:$B$1000,2,FALSE)" % cellname), style)
else:
ws_sit.write(str_row, i + cross_col_no + 1, content, style)
def write_sitdsc(sitlineno=0, str_row=None, str_col=0, cross_col_no=2, csvlineno=None, height=None, style_head=None, style=None):
ws_sit.row(str_row).height_mismatch = 1
ws_sit.row(str_row).height = height
cont_list = lines[csvlineno].split(',')
ws_sit.write_merge(str_row, str_row, str_col, str_col + cross_col_no, cont_list[0], style_head)
for (i, content) in enumerate(cont_list[1:]):
if content == 'Non':
cellname = Utils.rowcol_to_cell(sitlineno, i + cross_col_no + 1)
ws_sit.write(str_row, i + cross_col_no + 1,
Formula("VLOOKUP(%s,sit_chn_dsc!$A$1:$B$1000,2,FALSE)" % cellname), style)
else:
ws_sit.write(str_row, i + cross_col_no + 1, content, style)
from xlwt import Utils
print 'AA ->',Utils.col_by_name('AA')
print 'A ->',Utils.col_by_name('A')
print 'A1 ->',Utils.cell_to_rowcol('A1')
print '$A$1 ->',Utils.cell_to_rowcol('$A$1')
print 'A1 ->',Utils.cell_to_rowcol2('A1')
print (0,0),'->',Utils.rowcol_to_cell(0,0)
print (0,0,False,True),'->',
print Utils.rowcol_to_cell(0,0,False,True)
print (0,0,True,True),'->',
print Utils.rowcol_to_cell(
row=0,col=0,row_abs=True,col_abs=True
)
print '1:3 ->',Utils.cellrange_to_rowcol_pair('1:3')
print 'B:G ->',Utils.cellrange_to_rowcol_pair('B:G')
print 'A2:B7 ->',Utils.cellrange_to_rowcol_pair('A2:B7')
print 'A1 ->',Utils.cellrange_to_rowcol_pair('A1')
print (0,0,100,100),'->',
print Utils.rowcol_pair_to_cellrange(0,0,100,100)
print (0,0,100,100,True,False,False,False),'->',
print Utils.rowcol_pair_to_cellrange(
row1=0,col1=0,row2=100,col2=100,
row1_abs=True,col1_abs=False,
row2_abs=False,col2_abs=True
)
def excel_table(self):
items_info = self.items_info_all()
excel_table = []
temp_row = []
data_start_row = 2
data_start_col = 4
# 表头
for header_cn in HEADER_BEFORE_DATE:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
for m, m_len in items_info['months'].items():
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, str(m) + u"月", StyleTypes.header, 0, m_len - 1))
for i in range(0, m_len - 1):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for header_cn in HEADER_AFTER_DATE:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
excel_table.append(temp_row) # 第一行写完
temp_row = []
for i in range(0, len(HEADER_BEFORE_DATE)):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for d in items_info['dates']:
if d.isoweekday() in [6, 7]:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base_weekend))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base))
for i in range(0, len(HEADER_AFTER_DATE)):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
excel_table.append(temp_row) # 第二行写完
# 填表
temp_row = []
for v, sale_type_cn, sale_type_items in items_info['items']:
if not len(sale_type_items):
break
if sale_type_cn == u"配送":
item_type = StyleTypes.gift
item_weekend_type = StyleTypes.gift_weekend
else:
item_type = StyleTypes.base
item_weekend_type = StyleTypes.base_weekend
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, sale_type_cn, item_type, len(sale_type_items) - 1, 0))
index = 1
for item in sale_type_items:
if index != 1:
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, ITEM_STATUS_CN[item.item_status], item_type))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.name, item_type))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.standard_cn, item_type))
for i in range(0, len(items_info['dates'])):
d = items_info['dates'][i]
if d.isoweekday() in [6, 7]:
if item.schedule_by_date(d):
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_weekend_type))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_weekend_type))
else:
if item.schedule_by_date(d):
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_type))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_type))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, data_start_col),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 总预订量
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.position.price, item_type)) # 刊例单价
formula = '%s*%s' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, len(temp_row) - 2),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 刊例总价
if sale_type_cn == u"配送":
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_GIFT) / 100, StyleTypes.gift_discount))
elif sale_type_cn == u"补量":
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_ADD) / 100, StyleTypes.discount))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(self.discount) / 100, StyleTypes.discount))
formula = '%s*%s' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, len(temp_row) - 2),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 净价
excel_table.append(temp_row)
index += 1
temp_row = []
# totle
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "total", StyleTypes.base, 0, 3))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for i in range(0, len(items_info['dates']) + 1):
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, data_start_col + i),
Utils.rowcol_to_cell(len(excel_table) - 1, data_start_col + i))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, len(temp_row)),
Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, len(temp_row)),
Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
excel_table.append(temp_row)
return excel_table
def prepare_each_day(wb,start_date,end_date):
sheet = wb.add_sheet('일별 Feed 개수(기간 내)')
sheet.write(0,0,'시작',easyxf('font: bold True, height 220;' 'alignment: horizontal center;'))
sheet.write(0,1,str(start_date))
sheet.write(1,0,'종료',easyxf('font: bold True, height 220;' 'alignment: horizontal center;'))
sheet.write(1,1,str(end_date))
header_style=easyxf( 'font: name Arial, bold True, height 220;' 'border: bottom thin;' 'pattern: pattern solid, fore_color light_green;')
header_style2=easyxf( 'font: name Arial, bold True, height 220;' 'border: top thin;' 'pattern: pattern solid, fore_color light_green;')
header_style3=easyxf( 'font: name Arial, bold True, height 220;' 'pattern: pattern solid, fore_color light_green;')
sheet.write(3,0,'시간',header_style3)
sheet.write(3,1,'사용자',header_style3)
sheet.write(4,0,'날짜',header_style)
user_profiles = UserProfile.objects.filter(is_deactivated=False)
row=4
col=0
for user_profile in user_profiles:
try:
user = user_profile.user
col+=1
if col != 1:
sheet.write(row-1,col,'',header_style3)
sheet.write(row,col,user.last_name,header_style)
except Exception as e:
print str(e)
pass
col+=1
sheet.write(row-1,col,'',header_style3)
sheet.write(row,col,'합계',header_style)
time = start_date
row=4
col=0
while time < end_date:
next_time = time + dt.timedelta(1) - dt.timedelta(seconds=1)
col=0
row+=1
sheet.write(row,col,time.strftime('%Y.%m.%d'),easyxf('font: bold False, height 220;', num_format_str='YYYY.MM.DD'))
row_total=0
for user_profile in user_profiles:
try:
user = user_profile.user
col+=1
count = Message.objects.filter(is_deleted=False, reg_date__range=(time,next_time), author=user).count()
row_total+=count
if count != 0:
sheet.write(row,col,count,font_style)
except Exception as e:
print str(e)
pass
col+=1
start = Utils.rowcol_to_cell(row,1)
end = Utils.rowcol_to_cell(row,col-1)
sheet.write(row,col,Formula("SUM("+start+":"+end+")"),font_style)
time = time + dt.timedelta(1)
row+=1
col=0
sheet.write(row,col,'합계',header_style2)
for user_profile in user_profiles:
try:
col+=1
start = Utils.rowcol_to_cell(5,col)
end = Utils.rowcol_to_cell(row-1,col)
count = Formula("SUM("+start+":"+end+")")
sheet.write(row,col,count,header_style2)
except Exception as e:
print str(e)
pass
col+=1
start = Utils.rowcol_to_cell(5,col)
end = Utils.rowcol_to_cell(row-1,col)
count = Formula("SUM("+start+":"+end+")")
sheet.write(row,col,count,header_style2)
from xlwt import Utils
print 'AA ->', Utils.col_by_name('AA')
print 'A ->', Utils.col_by_name('A')
print 'A1 ->', Utils.cell_to_rowcol('A1')
print '$A$1 ->', Utils.cell_to_rowcol('$A$1')
print 'A1 ->', Utils.cell_to_rowcol2('A1')
print(0, 0), '->', Utils.rowcol_to_cell(0, 0)
print(0, 0, False, True), '->',
print Utils.rowcol_to_cell(0, 0, False, True)
print(0, 0, True, True), '->',
print Utils.rowcol_to_cell(row=0, col=0, row_abs=True, col_abs=True)
print '1:3 ->', Utils.cellrange_to_rowcol_pair('1:3')
print 'B:G ->', Utils.cellrange_to_rowcol_pair('B:G')
print 'A2:B7 ->', Utils.cellrange_to_rowcol_pair('A2:B7')
print 'A1 ->', Utils.cellrange_to_rowcol_pair('A1')
print(0, 0, 100, 100), '->',
print Utils.rowcol_pair_to_cellrange(0, 0, 100, 100)
print(0, 0, 100, 100, True, False, False, False), '->',
print Utils.rowcol_pair_to_cellrange(row1=0,
col1=0,
row2=100,
col2=100,
row1_abs=True,
col1_abs=False,
row2_abs=False,
def excel_table(self):
items_info = self.items_info_all()
excel_table = []
temp_row = []
data_start_row = 2
data_start_col = 4
# 表头
for header_cn in HEADER_BEFORE_DATE:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
for m, m_len in items_info['months'].items():
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, str(m) + u"月", StyleTypes.header, 0, m_len - 1))
for i in range(0, m_len - 1):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for header_cn in HEADER_AFTER_DATE:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, header_cn, StyleTypes.header, 1, 0))
excel_table.append(temp_row) # 第一行写完
temp_row = []
for i in range(0, len(HEADER_BEFORE_DATE)):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for d in items_info['dates']:
if d.isoweekday() in [6, 7]:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base_weekend))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, d.day, StyleTypes.base))
for i in range(0, len(HEADER_AFTER_DATE)):
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
excel_table.append(temp_row) # 第二行写完
# 填表
temp_row = []
for v, sale_type_cn, sale_type_items in items_info['items']:
if not len(sale_type_items):
break
if sale_type_cn == u"配送":
item_type = StyleTypes.gift
item_weekend_type = StyleTypes.gift_weekend
else:
item_type = StyleTypes.base
item_weekend_type = StyleTypes.base_weekend
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, sale_type_cn, item_type, len(sale_type_items) - 1, 0))
index = 1
for item in sale_type_items:
if index != 1:
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, ITEM_STATUS_CN[item.item_status], item_type))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.name, item_type))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, item.position.standard_cn, item_type))
for i in range(0, len(items_info['dates'])):
d = items_info['dates'][i]
if d.isoweekday() in [6, 7]:
if item.schedule_by_date(d):
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_weekend_type))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_weekend_type))
else:
if item.schedule_by_date(d):
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.schedule_by_date(d).num, item_type))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, " ", item_type))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, data_start_col),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 总预订量
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, item.position.price, item_type)) # 刊例单价
formula = '%s*%s' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, len(temp_row) - 2),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 刊例总价
if sale_type_cn == u"配送":
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_GIFT) / 100, StyleTypes.gift_discount))
elif sale_type_cn == u"补量":
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(DISCOUNT_ADD) / 100, StyleTypes.discount))
else:
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_NUM, float(self.discount) / 100, StyleTypes.discount))
formula = '%s*%s' % (
Utils.rowcol_to_cell(
data_start_row + len(excel_table) - 2, len(temp_row) - 2),
Utils.rowcol_to_cell(data_start_row + len(excel_table) - 2, len(temp_row) - 1))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, item_type)) # 净价
excel_table.append(temp_row)
index += 1
temp_row = []
# totle
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "total", StyleTypes.base, 0, 3))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
temp_row.append(ExcelCellItem(EXCEL_DATA_TYPE_MERGE))
for i in range(0, len(items_info['dates']) + 1):
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, data_start_col + i),
Utils.rowcol_to_cell(len(excel_table) - 1, data_start_col + i))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, len(temp_row)),
Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_STR, "/", StyleTypes.base))
formula = 'SUM(%s:%s)' % (
Utils.rowcol_to_cell(data_start_row, len(temp_row)),
Utils.rowcol_to_cell(len(excel_table) - 1, len(temp_row)))
temp_row.append(
ExcelCellItem(EXCEL_DATA_TYPE_FORMULA, formula, StyleTypes.base))
excel_table.append(temp_row)
return excel_table