def save(self, path=None):
path = path or self.path
if not path:
raise ValueError("No path defined for workbook")
book = xlutils_copy(self._book)
book.save(path)
def _book_write(self):
book = xlutils_copy(self._book)
yield book
fd = BytesIO()
try:
book.save(fd)
fd.seek(0)
self.close()
self.open(fd)
finally:
fd.close()
def letter2num(letter: str) -> int:
"""
字母 a-z 映射到数字，从 0 开始
"""
letter_lower = letter.lower()
return string.ascii_lowercase.index(letter_lower)
origin_ws = load_workbook(
r'C:\Users\luolongf\Desktop\学生名册修复版（所有文字可复制）.xlsx')['Sheet1']
all_origin_cells = origin_ws['A3':'K44']
target_file_name = r'C:\Users\luolongf\Desktop\一年级招生名册.xls'
workbook = xlrd.open_workbook(target_file_name, formatting_info=True)
target_wb = xlutils_copy(workbook)
target_ws = target_wb.get_sheet('中心校')
# tmp_ws = target_wb.get_sheet('Sheet1')
print('已读取所需表格信息，开始处理...')
birthday_regex = re.compile(
r'^20[012][0-9](?:0[1-9]|1[0-2])(?:0[1-9]|[12][0-9]|3[0-2])$')
row_num = 3
error_ID = {}
ok_num = 0
for row in all_origin_cells:
name = row[3].value.strip()
sex = row[4].value.strip()
ID = row[5].value
def post(self, request):
if request.POST['file_action'] == 'import':
form = MenuFileForm(request.POST, request.FILES)
if form.is_valid():
# upload menu
menu_file = MenuFile(upload=request.FILES['menu_file'])
menu_file.save()
# read xls
new_filepath = os.path.join(hlebsol.settings.MEDIA_ROOT,
menu_file.upload.url)
wb = xlrd.open_workbook(filename=new_filepath)
for sheet in wb.sheets():
continue_flag = True
category = 'Неизвестно'
category_counter = 1
for irow in range(sheet.nrows):
# search for start
row = sheet.row(irow)
if row[1].value == 'Наименование':
continue_flag = False
continue
elif not any([
row[0].value, row[1].value, row[2].value,
row[3].value
]):
continue_flag = True
if continue_flag:
continue
if row[0].ctype == 1: # Category getter
category_name = '{}. {}'.format(
category_counter, row[0].value)
category, created = Category.objects.get_or_create(
name=category_name)
category_counter += 1
else: # menu item getter
menu_date = datetime.datetime.strptime(
sheet.name.split()[0], '%d.%m.%y')
MenuItem.objects.update_or_create(
nrow=irow,
position=row[0].value,
name=row[1].value,
mass=row[2].value,
price=row[3].value,
menu_day=sheet.name,
menu_date=menu_date,
category=category,
defaults={'from_file': menu_file})
elif request.POST['file_action'] == 'export':
form = MenuFileForm(request.POST, request.FILES)
if form.is_valid():
filename = request.FILES['menu_file'].name
filedata = request.FILES['menu_file'].read()
filepath = urllib.parse.urljoin(hlebsol.settings.MEDIA_ROOT,
filename)
with open(filepath, 'wb') as f:
f.write(filedata)
rb = xlrd.open_workbook(filepath, formatting_info=True)
wb = xlutils_copy(rb)
# prepare data
menu_file = MenuFile.get_latest()
food_offers_list = (FoodOffer.objects.filter(
menu_item__from_file=menu_file).values(
'menu_item__nrow', 'menu_item__menu_day',
'menu_item__price').annotate(
total_quantity=Sum('quantity')))
food_offers_list = ((agg['menu_item__menu_day'],
agg['menu_item__nrow'],
agg['menu_item__price'],
agg['total_quantity'])
for agg in food_offers_list)
food_offers_group_gen = itertools.groupby(
sorted(food_offers_list, key=lambda x: x[0]),
lambda x: x[0])
# update all ordered positions: quantity and price
for sheet_name, values in food_offers_group_gen:
total_price = 0
# get target sheet
sheet = wb.get_sheet(sheet_name)
# find 'ВСЕГО' row number in sheet
total_price_nrow = self._get_total_price_nrow(
rb.sheet_by_name(sheet_name))
for _, nrow, price, total_quantity in values:
# write total quantity for specific position by row
sheet.write(nrow, XLS_NCOLUMNS_MAP['quantity'],
total_quantity)
# calculate and write sum price for specific position by row
position_sum_price = price * total_quantity
total_price += position_sum_price
sheet.write(nrow,
XLS_NCOLUMNS_MAP['position_sum_price'],
position_sum_price)
# write total price for target sheet
sheet.write(total_price_nrow,
XLS_NCOLUMNS_MAP['total_price'], total_price)
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response[
'Content-Disposition'] = 'attachment; filename=mercaux_menu.xls'
wb.save(response)
return response
return HttpResponseRedirect(request.path)
def match_invoice_and_external_sales(invoice_excel, ext_sales_excel, GUI_caller):
# Open source invoice details Excel file, which is of .xls format
sourceWb = xlrd.open_workbook(invoice_excel, formatting_info=True)
sheetName = "Sheet0"
sourceWs = sourceWb.sheet_by_name(sheetName)
sourceWb_temp = xlutils_copy(sourceWb)
sourceWs_temp = sourceWb_temp.get_sheet(0)
# check caller type
if GUI_caller:
print("match_invoice_and_external_sales is called from GUI")
#
# openpyxl to read external sales Excel file in order to read/modify/write .xlsx files
# External sales Excel file was created in the stage 總帳前處理
#
# targetWb = openpyxl.load_workbook(ext_sales_excel)
ext_sales_wb = openpyxl.load_workbook(ext_sales_excel)
# 0-based index, index of worksheet #1 is 0
ext_sales_ws_name = ext_sales_wb.sheetnames[0]
ext_sales_ws = ext_sales_wb[ext_sales_ws_name]
#
# Traverse the source invoice records
# pdb.set_trace()
number_of_matched_found = 0
sourceWs_temp.write(0,constant.COL_INVOICE_CHECKED, "發票配對")
bar = progressbar.ProgressBar(maxval=100, widgets=[progressbar.Bar('=', '[', ']'), ' ', progressbar.Percentage()])
bar.start()
for js in range(1, sourceWs.nrows):
p = (js/sourceWs.nrows) * 100
bar.update(p)
invoice_status = sourceWs.cell_value(js, constant.COL_INVOICE_STATUS)
if invoice_status == "作廢":
sourceWs_temp.write(js, constant.COL_INVOICE_CHECKED, "作廢")
continue
invoice_number = sourceWs.cell_value(js, constant.COL_INVOICE_NO)
buyer_name = sourceWs.cell_value(js, constant.COL_INVOICE_BUYER)
invoice_date = sourceWs.cell_value(js, constant.COL_INVOICE_DATE)
amount_nt_str = sourceWs.cell_value(js, constant.COL_INVOICE_TOTAL)
invoice_amount_nt = utility.comma_separated_amount_to_float(amount_nt_str)
# determine if it is a USD transaction, extract the exchange rate if
# it is a USD transaction
if utility.is_source_a_usd_transaction(sourceWs.row(js)):
function_currency = constant.FUNCTION_CURRENCY_USD
exchange_rate = utility.find_currency_exchange_rate(sourceWs.row(js))
else:
function_currency = constant.FUNCTION_CURRENCY_NTD
exchange_rate = 1.00
source = constant.DATA_SOURCE_INVOICE_DETAIL
source_transaction = class_transaction.Transaction(invoice_number,
buyer_name,
invoice_date,
invoice_amount_nt,
0.0,
function_currency,
exchange_rate,
source)
# call the class method to display the object contents
source_transaction.display_transaction()
# traverse the target worksheet and identify the record correspondent to the
# source transaction
match_found = False
# for jt in range(2, targetWs.max_row+1):
# for debug purpose
# ToDo: need to check if the ext_sales_ws is empty before continuing
if ext_sales_ws.max_row == 1:
ext_sales_wb.save(ext_sales_excel)
sys.exit()
for jt in range(2, ext_sales_ws.max_row+1):
if not utility.is_target_account_receivable(ext_sales_ws[jt]):
continue
invoice_number = ext_sales_ws.cell(row=jt, column=constant.COL_ES_INVOICE_NO+1).value
buyer_name = ext_sales_ws.cell(row=jt, column=constant.COL_ES_TEXT+1).value
invoice_date = ext_sales_ws.cell(row=jt, column=constant.COL_ES_INVOICE_DATE+1).value
invoice_amount_nt = ext_sales_ws.cell(row=jt, column=constant.COL_ES_AMOUNT+1).value
if utility.is_target_a_usd_transaction(ext_sales_ws[jt]):
function_currency = constant.FUNCTION_CURRENCY_USD
exchange_rate = ext_sales_ws.cell(row=jt, column=constant.COL_ES_EXCHANGE_RATE+1).value
invoice_amount_us = invoice_amount_nt / exchange_rate
else:
function_currency = constant.FUNCTION_CURRENCY_NTD
exchange_rate = 1.0
invoice_amount_us = 0.0
source = constant.DATA_SOURCE_GENERAL_LEDGER
target_transaction = class_transaction.Transaction(invoice_number,
buyer_name,
invoice_date,
invoice_amount_nt,
invoice_amount_us,
function_currency,
exchange_rate,
source)
if source_transaction.match_transaction(target_transaction):
match_found = True
logging.info(">>>>>>>>>>>>>> 找到匹配交易紀錄 <<<<<<<<<<<<<<<")
number_of_matched_found += 1
logging.info("已匹配交易數量: %d", number_of_matched_found)
target_transaction.display_transaction()
logging.info("==========================================================")
sourceWs_temp.write(js, constant.COL_INVOICE_CHECKED, "是")
ext_sales_ws.cell(row=jt,
column=constant.COL_ES_UNIFIED_INVOICE_NO+1,
value=source_transaction.invoice_number)
# if source_transaction.exchange_rate != 1.0:
# ext_sales_ws.cell(row=jt,
# column=constant.COL_ES_UINV_AMT+1,
# value=source_transaction.invoice_amount_NT)
# ext_sales_ws.cell(row=jt, column=constant.COL_ES_UINV_AMT+1).number_format='"$"#,##0_-'
ext_sales_ws.cell(row=jt,
column=constant.COL_ES_UINV_AMT+1,
value=source_transaction.invoice_amount_NT)
ext_sales_ws.cell(row=jt, column=constant.COL_ES_UINV_AMT+1).number_format='"$"#,##0_-'
ext_sales_ws.cell(row=jt,
column=constant.COL_ES_INVOICE_MATCHED+1,
value="配對")
if jt == ext_sales_ws.max_row and match_found is False:
logging.info(">>>>>>>>>>>>>> 無法找到匹配交易紀錄 <<<<<<<<<<<<<<<, ext_sales_ws.max_row %s", ext_sales_ws.max_row)
logging.info("==========================================================")
sourceWs_temp.write(js, constant.COL_INVOICE_CHECKED, "否")
bar.finish()
if GUI_caller:
GUI_caller.print_log("3. 原始發票資料檔比對完成，比對結果註記在 %s 的'發票配對'欄位" % invoice_excel)
else:
print("3. 原始發票資料檔比對完成，比對結果註記在 %s 的'發票配對'欄位" % invoice_excel)
sourceWb_temp.save(invoice_excel)
if GUI_caller:
GUI_caller.print_log("4. 總帳濾出應收帳款資料，儲存於 %s" % ext_sales_excel)
else:
print("4. 總帳濾出應收帳款資料，儲存於 %s" % ext_sales_excel)
ext_sales_wb.save(ext_sales_excel)
return True