def generate_request(company, project, data, request_no, ym):
"""請求書を生成する。
:param company:
:param project:
:param data:
:param request_no: 請求番号
:param ym:
:return:
"""
if is_win32:
pythoncom.CoInitializeEx(pythoncom.COINIT_MULTITHREADED)
if project.is_lump:
request_file = company.request_lump_file
else:
request_file = project.client.request_file if project.client.request_file else company.request_file
if not request_file or not os.path.exists(request_file.path):
raise errors.FileNotExistException(constants.ERROR_TEMPLATE_NOT_EXISTS)
template_book = get_excel_template(request_file.path)
template_sheet = template_book.Worksheets(1)
book = get_new_book()
cnt = book.Sheets.Count
# テンプレートを生成対象ワークブックにコピーする。
template_sheet.Copy(None, book.Worksheets(cnt))
template_book.Close()
sheet = book.Worksheets(cnt + 1)
replace_excel_dict(sheet, data['DETAIL'])
replace_excel_dict(sheet, data['detail_all'])
replace_excel_list(sheet, data['MEMBERS'])
replace_excel_list(sheet, data['EXPENSES'], range_start="EXPENSES_START", range_end="EXPENSES_END")
for i in range(cnt, 0, -1):
book.Worksheets(i).Delete()
path = common.get_request_file_path(request_no, project.client.name, ym)
book.SaveAs(path)
return path
else:
return generate_request_linux(project, data, request_no, ym)
def generate_request_linux(project, data, request_no, ym):
path = common.get_request_file_path(request_no, project.client.name, ym)
book = xlsxwriter.Workbook(path)
sheet = book.add_worksheet()
# タイトル設定
title_format = book.add_format({'bold': True,
'align': 'center',
'valign': 'vcenter',
'font_size': 18,
'underline': 2})
sheet.merge_range('A1:P1', u" 御 請 求 書 ", title_format)
sheet.write_string('M3', u"請求番号")
sheet.write_number('O3', int(request_no))
sheet.write_string('M4', u"発 行 日")
sheet.write_string('O4', data['DETAIL']['PUBLISH_DATE'])
sheet.write_string('B3', u"〒" + data['DETAIL']['CLIENT_POST_CODE'])
sheet.write_string('B4', data['DETAIL']['CLIENT_ADDRESS'])
sheet.write_string('B6', u"Tel: " + data['DETAIL']['CLIENT_TEL'])
name_format = book.add_format({'bold': True,
'font_size': 12,
'underline': 1})
sheet.write_string('B8', data['DETAIL']['CLIENT_COMPANY_NAME'] + u"御中", name_format)
sheet.write_string('B10', u" 下記のとおりご請求申し上げます。")
format1 = book.add_format({'bold': True,
'font_size': 12})
sheet.write_string('B12', u"御請求額 ： ", format1)
format1 = book.add_format({'bold': True,
'font_size': 14,
'underline': 2})
sheet.write_string('E12', u"\\" + data['DETAIL']['ITEM_AMOUNT_ALL_COMMA'] + u"円", format1)
sheet.write_string('B14', u"作業期間 ：")
sheet.write_string('E14', data['DETAIL']['WORK_PERIOD'])
sheet.write_string('B16', u"注文番号 ：")
sheet.write_string('E16', data['DETAIL']['ORDER_NO'])
sheet.write_string('B18', u"注文日 ：")
sheet.write_string('E18', data['DETAIL']['REQUEST_DATE'])
sheet.write_string('B20', u"契約件名 ： ")
sheet.write_string('E20', data['DETAIL']['CONTRACT_NAME'])
sheet.write_string('B22', u"お支払い期限 ：")
sheet.write_string('E22', data['DETAIL']['REMIT_DATE'])
sheet.write_string('M10', u"〒" + data['DETAIL']['POST_CODE'])
sheet.write_string('M11', data['DETAIL']['ADDRESS'])
sheet.write_string('M12', data['DETAIL']['COMPANY_NAME'])
sheet.write_string('M13', u"代表取締役 花 東 江")
sheet.write_string('M14', u"TEL：" + data['DETAIL']['TEL'])
sheet.insert_textbox('M15', '', {'width': 90,
'height': 90,
'x_offset': 4,
'y_offset': 6,
'align': {'vertical': 'middle', 'horizontal': 'center'}
}
)
sheet.insert_textbox('O15', '', {'width': 90,
'height': 90,
'x_offset': 6,
'y_offset': 6,
'align': {'vertical': 'middle', 'horizontal': 'center'}
}
)
sheet.insert_textbox('P15', '', {'width': 90,
'height': 90,
'x_offset': -2,
'y_offset': 6,
'align': {'vertical': 'middle', 'horizontal': 'center'}
}
)
title_format = book.add_format({'font_size': 11,
'border': 1,
'align': 'center',
'valign': 'vcenter', })
cell_format = book.add_format({'font_size': 11,
'border': 1, })
range1_format = book.add_format({'font_size': 11,
'left': 1,
'top': 1,
'bottom': 1})
range2_format = book.add_format({'font_size': 11,
'top': 1,
'bottom': 1})
range3_format = book.add_format({'font_size': 11,
'right': 1,
'top': 1,
'bottom': 1})
num_format = book.add_format({'num_format': '#,###', 'border': 1})
float_format = book.add_format({'num_format': '#,###.00', 'border': 1})
start_row = 24
def border_row(row_index, is_lump=False):
sheet.write_string(row_index, 1, '', cell_format)
sheet.write_string(row_index, 14, '', cell_format)
sheet.write_string(row_index, 15, '', cell_format)
if is_lump:
sheet.write_string(row_index, 2, '', range1_format)
sheet.write_string(row_index, 3, '', range2_format)
sheet.write_string(row_index, 4, '', range2_format)
sheet.write_string(row_index, 5, '', range2_format)
sheet.write_string(row_index, 6, '', range2_format)
sheet.write_string(row_index, 7, '', range2_format)
sheet.write_string(row_index, 8, '', range2_format)
sheet.write_string(row_index, 9, '', range2_format)
sheet.write_string(row_index, 10, '', range3_format)
sheet.write_string(row_index, 11, '', range1_format)
sheet.write_string(row_index, 12, '', range2_format)
sheet.write_string(row_index, 13, '', range3_format)
else:
sheet.write_string(row_index, 2, '', range1_format)
sheet.write_string(row_index, 3, '', range2_format)
sheet.write_string(row_index, 4, '', range2_format)
sheet.write_string(row_index, 5, '', range2_format)
sheet.write_string(row_index, 6, '', range3_format)
sheet.write_string(row_index, 7, '', cell_format)
sheet.write_string(row_index, 8, '', cell_format)
sheet.write_string(row_index, 9, '', cell_format)
sheet.write_string(row_index, 10, '', cell_format)
sheet.write_string(row_index, 11, '', cell_format)
sheet.write_string(row_index, 12, '', cell_format)
sheet.write_string(row_index, 13, '', cell_format)
if data['MEMBERS']:
sheet.write_string('B24', u"番号", title_format)
sheet.merge_range('C24:G24', u"項 目", title_format)
sheet.write_string('H24', u"単価", title_format)
sheet.write_string('I24', u"作業H", title_format)
sheet.write_string('J24', u"率", title_format)
sheet.write_string('K24', u"Min/MaxH", title_format)
sheet.write_string('L24', u"減", title_format)
sheet.write_string('M24', u"増", title_format)
sheet.write_string('N24', u"その他", title_format)
sheet.write_string('O24', u"金額", title_format)
sheet.write_string('P24', u"備考", title_format)
for item in data['MEMBERS']:
sheet.write_number(start_row, 1, int(item['NO']), cell_format)
sheet.merge_range(start_row, 2, start_row, 6, item['ITEM_NAME'], cell_format)
sheet.write_number(start_row, 7, item['ITEM_PRICE'], num_format)
if item['ITEM_WORK_HOURS']:
sheet.write_number(start_row, 8, float(item['ITEM_WORK_HOURS']), float_format)
else:
sheet.write_string(start_row, 8, '', cell_format)
sheet.write_number(start_row, 9, item['ITEM_RATE'], float_format)
sheet.write_string(start_row, 10, item['ITEM_MIN_MAX'], cell_format)
sheet.write_number(start_row, 11, item['ITEM_MINUS_PER_HOUR'], num_format)
sheet.write_number(start_row, 12, item['ITEM_PLUS_PER_HOUR'], num_format)
sheet.write_string(start_row, 13, item['ITEM_OTHER'], cell_format)
sheet.write_number(start_row, 14, item['ITEM_AMOUNT_TOTAL'], num_format)
sheet.write_string(start_row, 15, item['ITEM_COMMENT'], cell_format)
start_row += 1
elif data['detail_all']:
item = data['detail_all']
sheet.write_string('B24', u"番号", title_format)
sheet.merge_range('C24:K24', u"項 目", title_format)
sheet.merge_range('L24:N24', u"単位", title_format)
sheet.write_string('O24', u"金額", title_format)
sheet.write_string('P24', u"備考", title_format)
sheet.write_number('B25', int(item['NO']), cell_format)
sheet.merge_range('C25:K25', item['ITEM_NAME_ATTENDANCE_TOTAL'], cell_format)
sheet.merge_range('L25:N25', item['ITEM_UNIT'], cell_format)
sheet.write_number('O25', data['DETAIL']['ITEM_AMOUNT_ATTENDANCE'], num_format)
sheet.write_string('P25', item['ITEM_COMMENT'], cell_format)
start_row += 1
if start_row < 44:
for i in range(start_row, 44):
border_row(i, project.is_lump)
start_row = 44
else:
start_row += 1
for i in range(start_row, start_row + 5):
border_row(i, project.is_lump)
sheet.merge_range(start_row + 0, 3, start_row + 0, 5, u"（小計）", range2_format)
sheet.write_number(start_row + 0, 14, data['DETAIL']['ITEM_AMOUNT_ATTENDANCE'], num_format)
sheet.merge_range(start_row + 1, 3, start_row + 1, 5, u"(消費税）", range2_format)
sheet.write_number(start_row + 1, 14, data['DETAIL']['ITEM_AMOUNT_ATTENDANCE_TAX'], num_format)
sheet.merge_range(start_row + 2, 3, start_row + 2, 5, u"(合計）", range2_format)
sheet.write_number(start_row + 2, 14, data['DETAIL']['ITEM_AMOUNT_ATTENDANCE_ALL'], num_format)
sheet.merge_range(start_row + 3, 3, start_row + 3, 5, u"[控除、追加]", range2_format)
sheet.write_string(start_row + 4, 1, u"控除", cell_format)
start_row += 5
if data['EXPENSES']:
for i, item in enumerate(data['EXPENSES']):
border_row(start_row, project.is_lump)
if i == 0:
sheet.write_string(start_row, 1, u"追加", cell_format)
#sheet.write_string(start_row, 3, item['ITEM_EXPENSES_CATEGORY_SUMMARY'], range2_format)
sheet.merge_range(start_row, 3, start_row, 13, item['ITEM_EXPENSES_CATEGORY_SUMMARY'], range3_format)
sheet.write_number(start_row, 14, item['ITEM_EXPENSES_CATEGORY_AMOUNT'], num_format)
start_row += 1
else:
border_row(start_row, project.is_lump)
sheet.write_string(start_row, 1, u"追加", cell_format)
start_row += 1
border_row(start_row, project.is_lump)
sheet.merge_range(start_row, 3, start_row, 4, u"(総計）", range2_format)
sheet.write_number(start_row, 14, data['DETAIL']['ITEM_AMOUNT_ALL'], num_format)
sheet.write_string(start_row + 1, 1, u"お振込銀行口座")
sheet.write_string(start_row + 2, 2, data['DETAIL']['BANK_NAME'])
sheet.write_string(start_row + 3, 2, u"%s（%s）" % (data['DETAIL']['BRANCH_NAME'], data['DETAIL']['BRANCH_NO']))
sheet.write_string(start_row + 4, 2, u"%s %s" % (data['DETAIL']['ACCOUNT_TYPE'], data['DETAIL']['ACCOUNT_NUMBER']))
sheet.write_string(start_row + 5, 2, u"名義 %s" % (data['DETAIL']['BANK_ACCOUNT_HOLDER'],))
border_right_format = book.add_format({'right': 1})
border_top_format = book.add_format({'top': 1})
for i in range(start_row + 1, start_row + 6):
sheet.write_string(i, 0, '', border_right_format)
for i in range(start_row + 1, start_row + 6):
sheet.write_string(i, 15, '', border_right_format)
for i in range(1, 16):
sheet.write_string(start_row + 6, i, '', border_top_format)
# 全体の設定
sheet.hide_gridlines()
sheet.fit_to_pages(1, 1)
sheet.set_column('A:A', 0.9)
sheet.set_column('B:B', 4.9)
sheet.set_column('C:C', 2.9)
sheet.set_column('D:D', 2.9)
sheet.set_column('E:E', 3.0)
sheet.set_column('F:F', 3.0)
sheet.set_column('G:G', 3.0)
sheet.set_column('H:H', 10.3)
sheet.set_column('I:I', 10.7)
sheet.set_column('J:J', 4.9)
sheet.set_column('K:K', 15.3)
sheet.set_column('L:L', 4.9)
sheet.set_column('M:M', 4.9)
sheet.set_column('N:N', 6.6)
sheet.set_column('O:O', 13.6)
sheet.set_column('P:P', 12.4)
sheet.set_row(0, 23.25)
for i in range(1, 23):
sheet.set_row(i, 15.5)
book.close()
return path