def print_account_totals(self, _xs, xlwt, ws, row_start_account, row_position, current_account, _p): cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] + _xs[ 'wrap'] + _xs['top'] cell_style = xlwt.easyxf(cell_format) cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) c_specs = [ ('acc_title', 2, 0, 'text', current_account.name), ('code', 1, 0, 'text', current_account.code), ] for column in range(3, 7): if ( _p.comparison_mode == 'single' and column == 6 ): #in case of one single comparison, the column 6 will contain percentages total_diff = rowcol_to_cell(row_position, column - 1) total_balance = rowcol_to_cell(row_position, column - 2) account_formula = 'Round(' + total_diff + '/' + total_balance + '*100;0)' else: account_start = rowcol_to_cell(row_start_account, column) account_end = rowcol_to_cell(row_position - 1, column) account_formula = 'Round(SUM(' + account_start + ':' + account_end + ');2)' c_specs += [('total%s' % column, 1, 0, 'text', None, account_formula, None, cell_style_decimal)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_position = self.xls_write_row(ws, row_position, row_data, cell_style) return row_position + 1
def print_cumul_account(self, row_position, row_start_account, account, _p, data): #print by account the totals of the credit and debit + balance calculation #This procedure will create an Excel sumif function that will check in the column "label" for the "Cumulated Balance.." string and make a sum of the debit & credit data start_col = 5 #the text "Cumulated Balance on Partner starts in column 4 when selecting the option regroup by currency, 5 in the other case reference_start = rowcol_to_cell(row_start_account, start_col) #range in which we search for the text "Cumulated Balance on Partner" reference_stop = rowcol_to_cell(row_position -1 , start_col) range_debit_start = rowcol_to_cell(row_start_account, start_col + 3) #range in which we make the sum of all the cumulated balance lines (debit) range_debit_stop = rowcol_to_cell(row_position -1, start_col + 3) range_credit_start = rowcol_to_cell(row_start_account, start_col + 4) #range in which we make the sum of all the cumulated balance lines (crebit) range_credit_stop = rowcol_to_cell(row_position -1, start_col + 4) search_key = _('Cumulated Balance on Partner') total_debit_account = 'SUMIF(' + reference_start + ':' + reference_stop + ';"' + search_key + '";' + range_debit_start + ':' + range_debit_stop + ')' total_credit_account = 'SUMIF(' + reference_start + ':' + reference_stop + ';"' + search_key + '";' + range_credit_start + ':' + range_credit_stop + ')' bal_account_debit = rowcol_to_cell(row_position, start_col + 3) bal_account_credit = rowcol_to_cell(row_position, start_col + 4) bal_account_total = bal_account_debit + '-' + bal_account_credit bal_curr_start = rowcol_to_cell(row_start_account, start_col + 6) bal_curr_end = rowcol_to_cell(row_position-1, start_col + 6) cumul_balance_curr = 'SUMIF(' + reference_start + ':' + reference_stop + ';"' + search_key + '";' + bal_curr_start + ':' + bal_curr_end + ')' c_specs = [ ('acc_title', start_col, 0, 'text', ' - '.join([account.code, account.name])), ('init_bal', 2, 0, 'text', _('Cumulated Balance on Account')), ('empty2', 1, 0, 'text', None), ('debit', 1, 0, 'number', None, total_debit_account, style_account_title_decimal), ('credit', 1, 0, 'number', None, total_credit_account, style_account_title_decimal), ('balance', 1, 0, 'number', None, bal_account_total, style_account_title_decimal), ] if _p.amount_currency(data): if account.currency_id: c_specs += [('cumul_bal_curr', 1, 0, 'number', None, cumul_balance_curr), ('curr_name', 1, 0, 'text', account.currency_id.name, None, style_account_title_right), ] else: c_specs += [('cumul_bal_curr', 1, 0, 'text', "-", None, style_account_title_right), ('curr_name', 1, 0, 'text', "", None, style_account_title_right) ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_position = self.xls_write_row(ws, row_position, row_data, style_account_title) return row_position+1
def print_account_totals(self, _xs, xlwt, ws, row_start_account, row_position, current_account,_p): cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] + _xs['wrap'] + _xs['top'] cell_style = xlwt.easyxf(cell_format) cell_style_decimal = xlwt.easyxf(cell_format + _xs['right'], num_format_str = report_xls.decimal_format) c_specs = [ ('acc_title', 2, 0, 'text', current_account.name), ('code', 1, 0, 'text', current_account.code), ] for column in range(3,7): if (_p.comparison_mode == 'single' and column == 6): #in case of one single comparison, the column 6 will contain percentages total_diff = rowcol_to_cell(row_position, column-1) total_balance = rowcol_to_cell(row_position, column-2) account_formula = 'Round('+ total_diff + '/' + total_balance + '*100;0)' else: account_start = rowcol_to_cell(row_start_account, column) account_end = rowcol_to_cell(row_position -1, column) account_formula = 'Round(SUM(' + account_start + ':' + account_end + ');2)' c_specs += [('total%s' %column, 1, 0, 'text', None, account_formula, None, cell_style_decimal)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_position = self.xls_write_row(ws, row_position, row_data, cell_style) return row_position + 1
def print_cumul_partner(self, row_position, row_start_partner, account, _p, data): #print by partner the totals and cumulated balance (Excel formulas) start_col = 5 #the text "Cumulated Balance on Partner starts in column 4 when selecting the option regroup by currency, 5 in the other case debit_partner_start = rowcol_to_cell(row_start_partner, start_col + 3) debit_partner_end = rowcol_to_cell(row_position-1, start_col + 3) debit_partner_total = 'SUM(' + debit_partner_start + ':' + debit_partner_end + ')' credit_partner_start = rowcol_to_cell(row_start_partner, start_col + 4) credit_partner_end = rowcol_to_cell(row_position-1, start_col + 4) credit_partner_total = 'SUM(' + credit_partner_start + ':' + credit_partner_end + ')' bal_curr_start = rowcol_to_cell(row_start_partner, start_col + 6) bal_curr_end = rowcol_to_cell(row_position-1, start_col + 6) cumul_balance_curr = 'SUM(' + bal_curr_start + ':' + bal_curr_end + ')' bal_partner_debit = rowcol_to_cell(row_position, start_col + 3) bal_partner_credit = rowcol_to_cell(row_position, start_col + 4) bal_partner_total = bal_partner_debit + '-' + bal_partner_credit c_specs = [('empty%s' %x, 1, 0, 'text', None) for x in range(start_col)] c_specs += [ ('init_bal', 1, 0, 'text', _('Cumulated Balance on Partner')), ('rec', 1, 0, 'text', None), ('empty5', 1, 0, 'text', None), ('debit', 1, 0, 'number', None, debit_partner_total, style_partner_cumul_decimal), ('credit', 1, 0, 'number', None, credit_partner_total, style_partner_cumul_decimal), ('cumul_bal', 1, 0, 'number', None, bal_partner_total, style_partner_cumul_decimal), ] if _p.amount_currency(data): if account.currency_id: c_specs += [('cumul_bal_curr', 1, 0, 'number', None, cumul_balance_curr, style_partner_cumul_decimal), ('curr_name', 1, 0, 'text', account.currency_id.name, None, style_partner_cumul_right), ] else: c_specs += [('cumul_bal_curr', 1, 0, 'text', '-', None, style_partner_cumul_right), ('curr_name', 1, 0, 'text', '', None, style_partner_cumul_right) ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_position = self.xls_write_row(ws, row_position, row_data, style_partner_cumul) return row_position+1
def generate_xls_report(self, _p, _xs, data, objects, wb): ws = wb.add_sheet(_p.report_name[:31]) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 row_pos = 0 # set print header/footer ws.header_str = self.xls_headers['standard'] ws.footer_str = self.xls_footers['standard'] # cf. account_report_trial_balance.mako initial_balance_text = {'initial_balance': _('Computed'), 'opening_balance': _('Opening Entries'), False: _('No')} # Title cell_style = xlwt.easyxf(_xs['xls_title']) report_name = ' - '.join([_p.report_name.upper(), _p.company.partner_id.name, _p.company.currency_id.name]) c_specs = [ ('report_name', 1, 0, 'text', report_name), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style) # write empty row to define column sizes c_sizes = self.column_sizes c_specs = [('empty%s'%i, 1, c_sizes[i], 'text', None) for i in range(0,len(c_sizes))] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, set_column_size=True) # Header Table cell_format = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('fy', 1, 0, 'text', _('Fiscal Year')), ('af', 2, 0, 'text', _('Accounts Filter')), ('df', 1, 0, 'text', _p.filter_form(data) == 'filter_date' and _('Dates Filter') or _('Periods Filter')), ('tm', 2, 0, 'text', _('Target Moves'), None, cell_style_center), ('ib', 1, 0, 'text', _('Initial Balance'), None, cell_style_center), ('coa', 1, 0, 'text', _('Chart of Account'), None, cell_style_center), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style) cell_format = _xs['borders_all'] + _xs['wrap'] + _xs['top'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-'), ('af', 2, 0, 'text', _p.accounts(data) and ', '.join([account.code for account in _p.accounts(data)]) or _('All')), ] df = _('From') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.start_date if _p.start_date else u'' else: df += _p.start_period.name if _p.start_period else u'' df += ' ' + _('\nTo') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.stop_date if _p.stop_date else u'' else: df += _p.stop_period.name if _p.stop_period else u'' c_specs += [ ('df', 1, 0, 'text', df), ('tm', 2, 0, 'text', _p.display_target_move(data), None, cell_style_center), ('ib', 1, 0, 'text', initial_balance_text[_p.initial_balance_mode], None, cell_style_center), ('coa', 1, 0, 'text', _p.chart_account.name, None, cell_style_center), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style) # comparison header table if _p.comparison_mode in ('single', 'multiple'): row_pos += 1 cell_format_ct = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all'] cell_style_ct = xlwt.easyxf(cell_format_ct) c_specs = [('ct', 8, 0, 'text', _('Comparisons'))] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_ct) cell_style_center = xlwt.easyxf(cell_format) for index, params in enumerate(_p.comp_params): c_specs = [('c', 3, 0, 'text', _('Comparison') + str(index + 1) + ' (C' + str(index + 1) + ')')] if params['comparison_filter'] == 'filter_date': c_specs += [('f', 3, 0, 'text', _('Dates Filter') + ': ' + _p.formatLang(params['start'], date=True) + ' - ' + _p.formatLang(params['stop'], date=True))] elif params['comparison_filter'] == 'filter_period': c_specs += [('f', 3, 0, 'text', _('Periods Filter') + ': ' + params['start'].name + ' - ' + params['stop'].name)] else: c_specs += [('f', 3, 0, 'text', _('Fiscal Year') + ': ' + params['fiscalyear'].name)] c_specs += [('ib', 2, 0, 'text', _('Initial Balance') + ': ' + initial_balance_text[params['initial_balance_mode']])] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_center) row_pos += 1 # Column Header Row cell_format = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all'] + _xs['wrap'] + _xs['top'] cell_style = xlwt.easyxf(cell_format) cell_style_right = xlwt.easyxf(cell_format + _xs['right']) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) if len(_p.comp_params) == 2: account_span = 3 else: account_span = _p.initial_balance_mode and 2 or 3 c_specs = [ ('code', 1, 0, 'text', _('Code')), ('account', account_span, 0, 'text', _('Account')), ] if _p.comparison_mode == 'no_comparison': if _p.initial_balance_mode: c_specs += [('init_bal', 1, 0, 'text', _('Initial Balance'), None, cell_style_right)] c_specs += [ ('debit', 1, 0, 'text', _('Debit'), None, cell_style_right), ('credit', 1, 0, 'text', _('Credit'), None, cell_style_right), ] if _p.comparison_mode == 'no_comparison' or not _p.fiscalyear: c_specs += [('balance', 1, 0, 'text', _('Balance'), None, cell_style_right)] else: c_specs += [('balance_fy', 1, 0, 'text', _('Balance %s') % _p.fiscalyear.name, None, cell_style_right)] if _p.comparison_mode in ('single', 'multiple'): for index in range(_p.nb_comparison): if _p.comp_params[index]['comparison_filter'] == 'filter_year' and _p.comp_params[index].get('fiscalyear', False): c_specs += [('balance_%s' %index, 1, 0, 'text', _('Balance %s') % _p.comp_params[index]['fiscalyear'].name, None, cell_style_right)] else: c_specs += [('balance_%s' %index, 1, 0, 'text', _('Balance C%s') % (index + 1), None, cell_style_right)] if _p.comparison_mode == 'single': c_specs += [ ('diff', 1, 0, 'text', _('Difference'), None, cell_style_right), ('diff_percent', 1, 0, 'text', _('% Difference'), None, cell_style_center), ] c_specs += [('type', 1, 0, 'text', _('Type'), None, cell_style_center)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style) ws.set_horz_split_pos(row_pos) last_child_consol_ids = [] last_level = False # cell styles for account data view_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] view_cell_style = xlwt.easyxf(view_cell_format) view_cell_style_center = xlwt.easyxf(view_cell_format + _xs['center']) view_cell_style_decimal = xlwt.easyxf(view_cell_format + _xs['right'], num_format_str = report_xls.decimal_format) view_cell_style_pct = xlwt.easyxf(view_cell_format + _xs['center'], num_format_str = '0') regular_cell_format = _xs['borders_all'] regular_cell_style = xlwt.easyxf(regular_cell_format) regular_cell_style_center = xlwt.easyxf(regular_cell_format + _xs['center']) regular_cell_style_decimal = xlwt.easyxf(regular_cell_format + _xs['right'], num_format_str = report_xls.decimal_format) regular_cell_style_pct = xlwt.easyxf(regular_cell_format + _xs['center'], num_format_str = '0') for current_account in objects: if not current_account.to_display: continue if current_account.type == 'view': cell_style = view_cell_style cell_style_center = view_cell_style_center cell_style_decimal = view_cell_style_decimal cell_style_pct = view_cell_style_pct else: cell_style = regular_cell_style cell_style_center = regular_cell_style_center cell_style_decimal = regular_cell_style_decimal cell_style_pct = regular_cell_style_pct comparisons = current_account.comparisons if current_account.id in last_child_consol_ids: # current account is a consolidation child of the last account: use the level of last account level = last_level level_class = 'account_level_consol' else: # current account is a not a consolidation child: use its own level level = current_account.level or 0 level_class = "account_level_%s" % (level,) last_child_consol_ids = [child_consol_id.id for child_consol_id in current_account.child_consol_ids] last_level = current_account.level c_specs = [ ('code', 1, 0, 'text', current_account.code), ('account', account_span, 0, 'text', current_account.name), ] if _p.comparison_mode == 'no_comparison': debit_cell = rowcol_to_cell(row_pos, 3) credit_cell = rowcol_to_cell(row_pos, 4) bal_formula = debit_cell + '-' + credit_cell if _p.initial_balance_mode: init_cell = rowcol_to_cell(row_pos, 3) debit_cell = rowcol_to_cell(row_pos, 4) credit_cell = rowcol_to_cell(row_pos, 5) bal_formula = init_cell + '+' + debit_cell + '-' + credit_cell c_specs += [('init_bal', 1, 0, 'number', current_account.init_balance, None, cell_style_decimal)] c_specs += [ ('debit', 1, 0, 'number', current_account.debit, None, cell_style_decimal), ('credit', 1, 0, 'number', current_account.credit, None, cell_style_decimal), ] c_specs += [('balance', 1, 0, 'number', None, bal_formula, cell_style_decimal)] else: c_specs += [('balance', 1, 0, 'number', current_account.balance, None, cell_style_decimal)] if _p.comparison_mode in ('single', 'multiple'): c = 1 for comp_account in comparisons: c_specs += [('balance_%s' %c, 1, 0, 'number', comp_account['balance'], None, cell_style_decimal)] c += 1 if _p.comparison_mode == 'single': c_specs += [ ('diff', 1, 0, 'number', comp_account['diff'], None, cell_style_decimal), ('diff_percent', 1, 0, 'number', comp_account['percent_diff'] and comp_account['percent_diff'] or 0, None, cell_style_pct), ] c_specs += [('type', 1, 0, 'text', current_account.type, None, cell_style_center)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style)
def print_group_lines(self, row_position, account, line,_p, line_number): # Fill in rows of invoice line when the option currency regroup is selected label_elements = [line.get('lname') or ''] if line.get('invoice_number'): label_elements.append("(%s)" % (line['invoice_number'],)) label = ' '.join(label_elements) # Mako: <div class="act_as_row lines ${line.get('is_from_previous_periods') and 'open_invoice_previous_line' or ''} ${line.get('is_clearance_line') and 'clearance_line' or ''}"> if line.get('is_from_previous_periods') or line.get('is_clearance_line'): style_line_default = style_default_italic style_line_right = style_right_italic style_line_date = style_date_italic style_line_decimal = style_decimal_italic else: style_line_default = style_default style_line_right = style_right style_line_date = style_date style_line_decimal = style_decimal debit_cell = rowcol_to_cell(row_position, 7) credit_cell = rowcol_to_cell(row_position, 8) previous_balance = rowcol_to_cell(row_position - 1, 9) if line_number == 1: #if it is the first line, the balance is only debit - credit cumul_balance = debit_cell + '-' + credit_cell else: # cumulate devit - credit and balance of previous line cumul_balance = debit_cell + '-' + credit_cell + '+' + previous_balance if line.get('ldate'): c_specs = [('date', 1, 0, 'date', datetime.strptime(line['ldate'],'%Y-%m-%d'), None, style_line_date)] else: c_specs = [('date', 1, 0, 'text', None)] c_specs += [ ('period_code', 1, 0, 'text', line.get('period_code') or '' ), ('entry', 1, 0, 'text', line.get('move_name') or '' ), ('journal', 1, 0, 'text', line.get('jcode') or '' ), ('label', 1, 0, 'text', label), ('rec', 1, 0, 'text', line.get('rec_name') or '' ), ] if line.get('date_maturity'): c_specs += [('datedue', 1, 0, 'date', datetime.strptime(line['date_maturity'],'%Y-%m-%d'), None, style_line_date)] else: c_specs += [('datedue', 1, 0, 'text', None)] c_specs += [ ('debit', 1, 0, 'number', line.get('debit') or 0.0, None, style_line_decimal), ('credit', 1, 0, 'number', line.get('credit') or 0.0, None, style_line_decimal), ('cumul', 1, 0, 'number', None, cumul_balance, style_line_decimal), ] if account.currency_id: c_specs += [ ('curramount', 1, 0, 'number', line.get('amount_currency') or 0.0, None, style_line_decimal), ('currcode', 1, 0, 'text', line.get('currency_code') or '', None, style_line_right), ] else: c_specs += [ ('curramount', 1, 0, 'text', '-', None, style_line_right), ('currcode', 1, 0, 'text', '', None, style_line_right), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_position = self.xls_write_row(ws, row_position, row_data, style_line_default) return (row_position, cumul_balance)
def generate_xls_report(self, _p, _xs, data, objects, wb): # Initialisations ws = wb.add_sheet(_p.report_name[:31]) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 row_pos = 0 ws.header_str = self.xls_headers['standard'] ws.footer_str = self.xls_footers['standard'] # Print Title row_pos = self.print_title(ws, _p, row_pos, xlwt, _xs) # Print empty row to define column sizes row_pos = self.print_empty_row(ws, row_pos) # Print Header Table titles (Fiscal Year - Accounts Filter - Periods Filter...) row_pos = self.print_header_titles(ws, _p, data, row_pos, xlwt, _xs) initial_balance_text = { 'initial_balance': _('Computed'), 'opening_balance': _('Opening Entries'), False: _('No') } # cf. account_report_partner_balance.mako # Print Header Table data row_pos = self.print_header_data(ws, _p, data, row_pos, xlwt, _xs, initial_balance_text) # Print comparison header table if _p.comparison_mode in ('single', 'multiple'): row_pos += 1 row_pos = self.print_comparison_header(_xs, xlwt, row_pos, _p, ws, initial_balance_text) # Freeze the line ws.set_horz_split_pos(row_pos) # cell styles for account data regular_cell_format = _xs['borders_all'] regular_cell_style = xlwt.easyxf(regular_cell_format) regular_cell_style_center = xlwt.easyxf(regular_cell_format + _xs['center']) regular_cell_style_decimal = xlwt.easyxf( regular_cell_format + _xs['right'], num_format_str=report_xls.decimal_format) regular_cell_style_pct = xlwt.easyxf(regular_cell_format + _xs['center'], num_format_str='0') row_pos += 1 for current_account in objects: partners_order = current_account.partners_order # do not display accounts without partners if not partners_order: continue comparisons = current_account.comparisons # in multiple columns mode, we do not want to print accounts without any rows if _p.comparison_mode in ('single', 'multiple'): all_comparison_lines = [ comp['partners_amounts'][partner_id[1]] for partner_id in partners_order for comp in comparisons ] if not display_line(all_comparison_lines): continue current_partner_amounts = current_account.partners_amounts if _p.comparison_mode in ('single', 'multiple'): comparison_total = {} for i, comp in enumerate(comparisons): comparison_total[i] = {'balance': 0.0} # print row: Code - Account name row_pos = self.print_row_code_account(ws, current_account, row_pos, _xs, xlwt) row_account_start = row_pos # Print row: Titles "Account/Partner Name-Code/ref-Initial Balance-Debit-Credit-Balance" or "Account/Partner Name-Code/ref-Balance Year-Balance Year2-Balance C2-Balance C3" row_pos = self.print_account_header(ws, _p, _xs, xlwt, row_pos) for (partner_code_name, partner_id, partner_ref, partner_name) in partners_order: partner = current_partner_amounts.get(partner_id, {}) # in single mode, we have to display all the partners even if their balance is 0.0 because the initial balance should match with the previous year closings # in multiple columns mode, we do not want to print partners which have a balance at 0.0 in each comparison column if _p.comparison_mode in ('single', 'multiple'): all_comparison_lines = [ comp['partners_amounts'][partner_id] for comp in comparisons if comp['partners_amounts'].get(partner_id) ] if not display_line(all_comparison_lines): continue # display data row if len(_p.comp_params) == 2: account_span = 3 else: account_span = _p.initial_balance_mode and 2 or 3 c_specs = [('acc_title', account_span, 0, 'text', partner_name if partner_name else _('Unallocated')) ] c_specs += [('partner_ref', 1, 0, 'text', partner_ref if partner_ref else '')] if _p.comparison_mode == 'no_comparison': bal_formula = '' if _p.initial_balance_mode: init_bal_cell = rowcol_to_cell(row_pos, 3) bal_formula = init_bal_cell + '+' debit_col = 4 c_specs += [ ('init_bal', 1, 0, 'number', partner.get('init_balance', 0.0), None, regular_cell_style_decimal), ] else: debit_col = 3 c_specs += [ ('debit', 1, 0, 'number', partner.get('debit', 0.0), None, regular_cell_style_decimal), ('credit', 1, 0, 'number', partner.get('credit', 0.0), None, regular_cell_style_decimal), ] debit_cell = rowcol_to_cell(row_pos, debit_col) credit_cell = rowcol_to_cell(row_pos, debit_col + 1) bal_formula += debit_cell + '-' + credit_cell c_specs += [ ('bal', 1, 0, 'number', None, bal_formula, regular_cell_style_decimal), ] else: c_specs += [ ('bal', 1, 0, 'number', partner.get('balance', 0.0), None, regular_cell_style_decimal), ] if _p.comparison_mode in ('single', 'multiple'): for i, comp in enumerate(comparisons): comp_partners = comp['partners_amounts'] balance = diff = percent_diff = 0 if comp_partners.get(partner_id): balance = comp_partners[partner_id]['balance'] diff = comp_partners[partner_id]['diff'] percent_diff = comp_partners[partner_id][ 'percent_diff'] comparison_total[i]['balance'] += balance c_specs += [ ('balance_%s' % i, 1, 0, 'number', balance, None, regular_cell_style_decimal), ] if _p.comparison_mode == 'single': ## no diff in multiple comparisons because it shows too much data c_specs += [ ('balance_diff', 1, 0, 'number', diff, None, regular_cell_style_decimal), ] if percent_diff is False: c_specs += [ ('balance', 1, 0, 'number', diff, None, regular_cell_style_decimal), ] else: c_specs += [ ('perc_diff', 1, 0, 'number', int(round(percent_diff))), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, regular_cell_style) row_pos = self.print_account_totals(_xs, xlwt, ws, row_account_start, row_pos, current_account, _p)
def generate_xls_report(self, _p, _xs, data, objects, wb): report_name = objects[0]._description or objects[0]._name ws = wb.add_sheet(report_name[:31]) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 row_pos = 0 # set print header/footer ws.header_str = self.xls_headers['standard'] ws.footer_str = self.xls_footers['standard'] # Title cell_style = xlwt.easyxf(_xs['xls_title']) c_specs = [ ('report_name', 1, 0, 'text', report_name), ] row_data = self.xls_row_template(c_specs, ['report_name']) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style) row_pos += 1 # Column headers rh_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] rh_cell_style = xlwt.easyxf(rh_cell_format) rh_cell_style_center = xlwt.easyxf(rh_cell_format + _xs['center']) rh_cell_style_right = xlwt.easyxf(rh_cell_format + _xs['right']) c_specs = [ ('move', 1, 20, 'text', _('Move')), ('name', 1, 42, 'text', _('Name')), ('date', 1, 13, 'text', _('Effective Date')), ('period', 1, 12, 'text', _('Period')), ('partner', 1, 36, 'text', _('Partner')), ('account', 1, 12, 'text', _('Account')), ('maturity', 1, 13, 'text', _('Maturity Date')), ('debit', 1, 18, 'text', _('Debit'), None, rh_cell_style_right), ('credit', 1, 18, 'text', _('Credit'), None, rh_cell_style_right), ('balance', 1, 18, 'text', _('Balance'), None, rh_cell_style_right), ('reconcile', 1, 12, 'text', _('Rec.'), None, rh_cell_style_center), ('reconcile_partial', 1, 12, 'text', _('Part. Rec.'), None, rh_cell_style_center), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=rh_cell_style, set_column_size=True) ws.set_horz_split_pos(row_pos) # account move lines aml_cell_format = _xs['borders_all'] aml_cell_style = xlwt.easyxf(aml_cell_format) aml_cell_style_center = xlwt.easyxf(aml_cell_format + _xs['center']) aml_cell_style_date = xlwt.easyxf(aml_cell_format + _xs['left'], num_format_str = report_xls.date_format) aml_cell_style_decimal = xlwt.easyxf(aml_cell_format + _xs['right'], num_format_str = report_xls.decimal_format) for line in objects: debit_cell = rowcol_to_cell(row_pos, 7) credit_cell = rowcol_to_cell(row_pos, 8) bal_formula = debit_cell + '-' + credit_cell c_specs = [ ('move', 1, 0, 'text', line.move_id.name or ''), ('name', 1, 0, 'text', line.name or ''), ('date', 1, 0, 'date', datetime.strptime(line.date,'%Y-%m-%d'), None, aml_cell_style_date), ('period', 1, 0, 'text', line.period_id.code or ''), ('partner', 1, 0, 'text', line.partner_id and line.partner_id.name or ''), ('account', 1, 0, 'text', line.account_id.code), ] if line.date_maturity.val: c_specs += [ ('maturity', 1, 0, 'date', datetime.strptime(line.date_maturity,'%Y-%m-%d'), None, aml_cell_style_date), ] else: c_specs += [ ('maturity', 1, 0, 'text', None), ] c_specs += [ ('debit', 1, 0, 'number', line.debit, None, aml_cell_style_decimal), ('credit', 1, 0, 'number', line.credit, None, aml_cell_style_decimal), ('balance', 1, 0, 'number', None, bal_formula, aml_cell_style_decimal), ('reconcile', 1, 0, 'text', line.reconcile_id.name or '', None, aml_cell_style_center), ('reconcile_partial', 1, 0, 'text', line.reconcile_partial_id.name or '', None, aml_cell_style_center), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=aml_cell_style) # Totals rt_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] rt_cell_style = xlwt.easyxf(rt_cell_format) rt_cell_style_right = xlwt.easyxf(rt_cell_format + _xs['right']) rt_cell_style_decimal = xlwt.easyxf(rt_cell_format + _xs['right'], num_format_str = report_xls.decimal_format) aml_cnt = len(objects) debit_start = rowcol_to_cell(row_pos - aml_cnt, 7) debit_stop = rowcol_to_cell(row_pos - 1, 7) debit_formula = 'SUM(%s:%s)' %(debit_start, debit_stop) credit_start = rowcol_to_cell(row_pos - aml_cnt, 8) credit_stop = rowcol_to_cell(row_pos - 1, 8) credit_formula = 'SUM(%s:%s)' %(credit_start, credit_stop) debit_cell = rowcol_to_cell(row_pos, 7) credit_cell = rowcol_to_cell(row_pos, 8) bal_formula = debit_cell + '-' + credit_cell c_specs = [('empty%s'%i, 1, 0, 'text', None) for i in range(1,8)] c_specs += [ ('debit', 1, 0, 'number', None, debit_formula, rt_cell_style_decimal), ('credit', 1, 0, 'number', None, credit_formula, rt_cell_style_decimal), ('balance', 1, 0, 'number', None, bal_formula, rt_cell_style_decimal), ] c_specs += [('empty%s'%i, 1, 0, 'text', None) for i in range(11,13)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=rt_cell_style_right)
def generate_xls_report(self, _p, _xs, data, objects, wb): # Initialisations ws = wb.add_sheet(_p.report_name[:31]) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 row_pos = 0 ws.header_str = self.xls_headers['standard'] ws.footer_str = self.xls_footers['standard'] # Print Title row_pos = self.print_title(ws, _p, row_pos, xlwt, _xs) # Print empty row to define column sizes row_pos = self.print_empty_row(ws, row_pos) # Print Header Table titles (Fiscal Year - Accounts Filter - Periods Filter...) row_pos = self.print_header_titles(ws, _p, data, row_pos, xlwt, _xs) initial_balance_text = {'initial_balance': _('Computed'), 'opening_balance': _('Opening Entries'), False: _('No')} # cf. account_report_partner_balance.mako # Print Header Table data row_pos = self.print_header_data(ws, _p, data, row_pos, xlwt, _xs, initial_balance_text) # Print comparison header table if _p.comparison_mode in ('single', 'multiple'): row_pos += 1 row_pos = self.print_comparison_header(_xs, xlwt, row_pos, _p, ws, initial_balance_text) # Freeze the line ws.set_horz_split_pos(row_pos) # cell styles for account data regular_cell_format = _xs['borders_all'] regular_cell_style = xlwt.easyxf(regular_cell_format) regular_cell_style_center = xlwt.easyxf(regular_cell_format + _xs['center']) regular_cell_style_decimal = xlwt.easyxf(regular_cell_format + _xs['right'], num_format_str = report_xls.decimal_format) regular_cell_style_pct = xlwt.easyxf(regular_cell_format + _xs['center'], num_format_str = '0') row_pos += 1 for current_account in objects: partners_order = current_account.partners_order # do not display accounts without partners if not partners_order: continue comparisons = current_account.comparisons # in multiple columns mode, we do not want to print accounts without any rows if _p.comparison_mode in ('single', 'multiple'): all_comparison_lines = [comp['partners_amounts'][partner_id[1]] for partner_id in partners_order for comp in comparisons] if not display_line(all_comparison_lines): continue current_partner_amounts = current_account.partners_amounts if _p.comparison_mode in ('single', 'multiple'): comparison_total = {} for i, comp in enumerate(comparisons): comparison_total[i] = {'balance': 0.0} # print row: Code - Account name row_pos = self.print_row_code_account(ws, current_account, row_pos, _xs, xlwt) row_account_start = row_pos # Print row: Titles "Account/Partner Name-Code/ref-Initial Balance-Debit-Credit-Balance" or "Account/Partner Name-Code/ref-Balance Year-Balance Year2-Balance C2-Balance C3" row_pos = self.print_account_header(ws, _p, _xs, xlwt, row_pos) for (partner_code_name, partner_id, partner_ref, partner_name) in partners_order: partner = current_partner_amounts.get(partner_id, {}) # in single mode, we have to display all the partners even if their balance is 0.0 because the initial balance should match with the previous year closings # in multiple columns mode, we do not want to print partners which have a balance at 0.0 in each comparison column if _p.comparison_mode in ('single', 'multiple'): all_comparison_lines = [comp['partners_amounts'][partner_id] for comp in comparisons if comp['partners_amounts'].get(partner_id)] if not display_line(all_comparison_lines): continue # display data row if len(_p.comp_params) == 2: account_span = 3 else: account_span = _p.initial_balance_mode and 2 or 3 c_specs = [('acc_title', account_span, 0, 'text', partner_name if partner_name else _('Unallocated'))] c_specs += [('partner_ref', 1, 0, 'text', partner_ref if partner_ref else '')] if _p.comparison_mode == 'no_comparison': bal_formula = '' if _p.initial_balance_mode: init_bal_cell = rowcol_to_cell(row_pos, 3) bal_formula = init_bal_cell + '+' debit_col = 4 c_specs += [ ('init_bal', 1, 0, 'number', partner.get('init_balance', 0.0), None, regular_cell_style_decimal), ] else: debit_col = 3 c_specs += [ ('debit', 1, 0, 'number', partner.get('debit', 0.0), None, regular_cell_style_decimal), ('credit', 1, 0, 'number', partner.get('credit', 0.0), None, regular_cell_style_decimal), ] debit_cell = rowcol_to_cell(row_pos, debit_col) credit_cell = rowcol_to_cell(row_pos, debit_col+1) bal_formula += debit_cell + '-' + credit_cell c_specs += [('bal', 1, 0, 'number', None, bal_formula, regular_cell_style_decimal),] else: c_specs += [('bal', 1, 0, 'number', partner.get('balance', 0.0), None, regular_cell_style_decimal),] if _p.comparison_mode in ('single', 'multiple'): for i, comp in enumerate(comparisons): comp_partners = comp['partners_amounts'] balance = diff = percent_diff = 0 if comp_partners.get(partner_id): balance = comp_partners[partner_id]['balance'] diff = comp_partners[partner_id]['diff'] percent_diff = comp_partners[partner_id]['percent_diff'] comparison_total[i]['balance'] += balance c_specs += [('balance_%s' %i, 1, 0, 'number', balance, None, regular_cell_style_decimal), ] if _p.comparison_mode == 'single': ## no diff in multiple comparisons because it shows too much data c_specs += [('balance_diff', 1, 0, 'number', diff, None, regular_cell_style_decimal), ] if percent_diff is False: c_specs += [('balance', 1, 0, 'number', diff, None, regular_cell_style_decimal), ] else: c_specs += [('perc_diff', 1, 0, 'number', int(round(percent_diff))), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, regular_cell_style) row_pos = self.print_account_totals(_xs, xlwt, ws, row_account_start, row_pos, current_account,_p)
def generate_xls_report(self, _p, _xs, data, objects, wb): ws = wb.add_sheet(_p.report_name[:31]) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 row_pos = 0 # set print header/footer ws.header_str = self.xls_headers['standard'] ws.footer_str = self.xls_footers['standard'] # cf. account_report_partner_ledger.mako initial_balance_text = { 'initial_balance': _('Computed'), 'opening_balance': _('Opening Entries'), False: _('No') } # Title cell_style = xlwt.easyxf(_xs['xls_title']) report_name = ' - '.join([ _p.report_name.upper(), _p.company.partner_id.name, _p.company.currency_id.name ]) c_specs = [ ('report_name', 1, 0, 'text', report_name), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style) # write empty row to define column sizes c_sizes = self.column_sizes c_specs = [('empty%s' % i, 1, c_sizes[i], 'text', None) for i in range(0, len(c_sizes))] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, set_column_size=True) # Header Table nbr_columns = 10 if _p.amount_currency(data): nbr_columns = 12 cell_format = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('coa', 2, 0, 'text', _('Plan de Cuentas')), ('fy', 1, 0, 'text', _('Año Fiscal')), ('df', 2, 0, 'text', _p.filter_form(data) == 'filter_date' and _('Filtro fecha') or _('Filtro Periodos')), ('af', 1, 0, 'text', _('Filtro Cuentas')), ('tm', 2, 0, 'text', _('Movimientos Contables')), ('ib', nbr_columns - 8, 0, 'text', _('Balance Inicial')), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_center) cell_format = _xs['borders_all'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('coa', 2, 0, 'text', _p.chart_account.name), ('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-'), ] df = _('From') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.start_date if _p.start_date else u'' else: df += _p.start_period.name if _p.start_period else u'' df += ' ' + _('To') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.stop_date if _p.stop_date else u'' else: df += _p.stop_period.name if _p.stop_period else u'' c_specs += [ ('df', 2, 0, 'text', df), ('af', 1, 0, 'text', _('Custom Filter') if _p.partner_ids else _p.display_partner_account(data)), ('tm', 2, 0, 'text', _p.display_target_move(data)), ('ib', nbr_columns - 8, 0, 'text', initial_balance_text[_p.initial_balance_mode]), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_center) ws.set_horz_split_pos(row_pos) row_pos += 1 # Account Title Row cell_format = _xs['xls_title'] + _xs['bold'] + _xs['fill'] + _xs[ 'borders_all'] account_cell_style = xlwt.easyxf(cell_format) account_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) account_cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) # Column Title Row cell_format = _xs['bold'] c_title_cell_style = xlwt.easyxf(cell_format) # Column Header Row cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] c_hdr_cell_style = xlwt.easyxf(cell_format) c_hdr_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_hdr_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_hdr_cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) # Column Initial Balance Row cell_format = _xs['italic'] + _xs['borders_all'] c_init_cell_style = xlwt.easyxf(cell_format) c_init_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_init_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_init_cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) # Column Cumulated balance Row cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] c_cumul_cell_style = xlwt.easyxf(cell_format) c_cumul_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_cumul_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_cumul_cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) # Column Partner Row cell_format = _xs['bold'] c_part_cell_style = xlwt.easyxf(cell_format) c_specs = [ ('date', 1, 0, 'text', _('Fecha'), None, c_hdr_cell_style), ('period', 1, 0, 'text', _('Periodo'), None, c_hdr_cell_style), ('move', 1, 0, 'text', _('Asiento'), None, c_hdr_cell_style), ('journal', 1, 0, 'text', _('Diario'), None, c_hdr_cell_style), ('partner', 1, 0, 'text', _('Empresa'), None, c_hdr_cell_style), ('account_analytic', 1, 0, 'text', _('Centro de Costo'), None, c_hdr_cell_style), ('label', 1, 0, 'text', _('Descripcion'), None, c_hdr_cell_style), ('rec', 1, 0, 'text', _('Rec.'), None, c_hdr_cell_style), ('debit', 1, 0, 'text', _('Debe'), None, c_hdr_cell_style_right), ('credit', 1, 0, 'text', _('Haber'), None, c_hdr_cell_style_right), ('cumul_bal', 1, 0, 'text', _('Bal. Acum.'), None, c_hdr_cell_style_right), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'text', _('Curr. Bal.'), None, c_hdr_cell_style_right), ('curr_code', 1, 0, 'text', _('Curr.'), None, c_hdr_cell_style_center), ] c_hdr_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) # cell styles for ledger lines ll_cell_format = _xs['borders_all'] ll_cell_style = xlwt.easyxf(ll_cell_format) ll_cell_style_right = xlwt.easyxf(ll_cell_format + _xs['right']) ll_cell_style_center = xlwt.easyxf(ll_cell_format + _xs['center']) ll_cell_style_date = xlwt.easyxf(ll_cell_format + _xs['left'], num_format_str=report_xls.date_format) ll_cell_style_decimal = xlwt.easyxf( ll_cell_format + _xs['right'], num_format_str=report_xls.decimal_format) cnt = 0 for account in objects: if account.ledger_lines or account.init_balance: if not account.partners_order: continue cnt += 1 account_total_debit = 0.0 account_total_credit = 0.0 account_balance_cumul = 0.0 account_balance_cumul_curr = 0.0 c_specs = [ ('acc_title', nbr_columns, 0, 'text', ' - '.join([account.code, account.name]), None, account_cell_style), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_title_cell_style) row_pos += 1 row_start_account = row_pos for partner_name, p_id, p_ref, p_name in account.partners_order: total_debit = 0.0 total_credit = 0.0 cumul_balance = 0.0 cumul_balance_curr = 0.0 part_cumul_balance = 0.0 part_cumul_balance_curr = 0.0 c_specs = [ ('partner_title', nbr_columns, 0, 'text', partner_name or _('No Partner'), None, c_part_cell_style), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_title_cell_style) row_pos = self.xls_write_row(ws, row_pos, c_hdr_data) row_start_partner = row_pos total_debit = account.init_balance.get( p_id, {}).get('debit') or 0.0 total_credit = account.init_balance.get( p_id, {}).get('credit') or 0.0 init_line = False if _p.initial_balance_mode and (total_debit or total_credit): init_line = True part_cumul_balance = account.init_balance.get( p_id, {}).get('init_balance') or 0.0 part_cumul_balance_curr = account.init_balance.get( p_id, {}).get('init_balance_currency') or 0.0 balance_forward_currency = account.init_balance.get( p_id, {}).get('currency_name') or '' cumul_balance += part_cumul_balance cumul_balance_curr += part_cumul_balance_curr debit_cell = rowcol_to_cell(row_pos, 8) credit_cell = rowcol_to_cell(row_pos, 9) init_bal_formula = debit_cell + '-' + credit_cell ################## Print row 'Initial Balance' by partner ################# c_specs = [('empty%s' % x, 1, 0, 'text', None) for x in range(6)] c_specs += [ ('init_bal', 1, 0, 'text', _('Balance Inicial')), ('rec', 1, 0, 'text', None), ('debit', 1, 0, 'number', total_debit, None, c_init_cell_style_decimal), ('credit', 1, 0, 'number', total_credit, None, c_init_cell_style_decimal), ('cumul_bal', 1, 0, 'number', None, init_bal_formula, c_init_cell_style_decimal), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'number', part_cumul_balance_curr, None, c_init_cell_style_decimal), ('curr_code', 1, 0, 'text', balance_forward_currency), ] row_data = self.xls_row_template( c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_init_cell_style) for line in account.ledger_lines.get(p_id, []): total_debit += line.get('debit') or 0.0 total_credit += line.get('credit') or 0.0 label_elements = [line.get('lname') or ''] if line.get('invoice_number'): label_elements.append("(%s)" % (line['invoice_number'], )) label = ' '.join(label_elements) cumul_balance += line.get('balance') or 0.0 if init_line or row_pos > row_start_partner: cumbal_formula = rowcol_to_cell(row_pos - 1, 10) + '+' else: cumbal_formula = '' debit_cell = rowcol_to_cell(row_pos, 8) credit_cell = rowcol_to_cell(row_pos, 9) cumbal_formula += debit_cell + '-' + credit_cell ################## Print row ledger line data ################# if line.get('ldate'): c_specs = [ ('ldate', 1, 0, 'date', datetime.strptime(line['ldate'], '%Y-%m-%d'), None, ll_cell_style_date), ] else: c_specs = [ ('ldate', 1, 0, 'text', None), ] c_specs += [ ('period', 1, 0, 'text', line.get('period_code') or ''), ('move', 1, 0, 'text', line.get('move_name') or ''), ('journal', 1, 0, 'text', line.get('jcode') or ''), ('partner', 1, 0, 'text', line.get('partner_name') or ''), ('account_analytic', 1, 0, 'text', line.get('account_analytic_name') or ''), ('label', 1, 0, 'text', label), ('rec_name', 1, 0, 'text', line.get('rec_name') or ''), ('debit', 1, 0, 'number', line.get('debit'), None, ll_cell_style_decimal), ('credit', 1, 0, 'number', line.get('credit'), None, ll_cell_style_decimal), ('cumul_bal', 1, 0, 'number', None, cumbal_formula, ll_cell_style_decimal), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'number', line.get('amount_currency') or 0.0, None, ll_cell_style_decimal), ('curr_code', 1, 0, 'text', line.get('currency_code') or '', None, ll_cell_style_center), ] row_data = self.xls_row_template( c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, ll_cell_style) #end for line ################## Print row Cumulated Balance by partner ################# debit_partner_start = rowcol_to_cell(row_start_partner, 8) debit_partner_end = rowcol_to_cell(row_pos - 1, 8) debit_partner_total = 'SUM(' + debit_partner_start + ':' + debit_partner_end + ')' credit_partner_start = rowcol_to_cell(row_start_partner, 9) credit_partner_end = rowcol_to_cell(row_pos - 1, 9) credit_partner_total = 'SUM(' + credit_partner_start + ':' + credit_partner_end + ')' bal_partner_debit = rowcol_to_cell(row_pos, 8) bal_partner_credit = rowcol_to_cell(row_pos, 9) bal_partner_total = bal_partner_debit + '-' + bal_partner_credit c_specs = [('empty%s' % x, 1, 0, 'text', None) for x in range(6)] c_specs += [ ('init_bal', 1, 0, 'text', _('Balance Acumulado por Empresa')), ('rec', 1, 0, 'text', None), ('debit', 1, 0, 'number', None, debit_partner_total, c_cumul_cell_style_decimal), ('credit', 1, 0, 'number', None, credit_partner_total, c_cumul_cell_style_decimal), ('cumul_bal', 1, 0, 'number', None, bal_partner_total, c_cumul_cell_style_decimal), ] if _p.amount_currency(data): if account.currency_id: c_specs += [ ('curr_bal', 1, 0, 'number', cumul_balance_curr or 0.0, None, c_cumul_cell_style_decimal) ] else: c_specs += [('curr_bal', 1, 0, 'text', '-', None, c_cumul_cell_style_right)] c_specs += [('curr_code', 1, 0, 'text', account.currency_id.name if account.currency_id else u'', None, c_cumul_cell_style_center)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_cumul_cell_style) row_pos += 1 account_total_debit += total_debit account_total_credit += total_credit account_balance_cumul += cumul_balance account_balance_cumul_curr += cumul_balance_curr ################## Print row Cumulated Balance by account ################# c_specs = [ ('acc_title', 6, 0, 'text', ' - '.join([account.code, account.name])), ] c_specs += [ ('label', 1, 0, 'text', _('Balance Acumulado por Cuenta')), ('rec', 1, 0, 'text', None), ('debit', 1, 0, 'number', account_total_debit, None, account_cell_style_decimal), ('credit', 1, 0, 'number', account_total_credit, None, account_cell_style_decimal), ('cumul_bal', 1, 0, 'number', account_balance_cumul, None, account_cell_style_decimal), ] if _p.amount_currency(data): if account.currency_id: c_specs += [('curr_bal', 1, 0, 'number', account_balance_cumul_curr or 0.0, None, account_cell_style_decimal)] else: c_specs += [('curr_bal', 1, 0, 'text', '-', None, account_cell_style_right)] c_specs += [ ('curr_code', 1, 0, 'text', account.currency_id.name if account.currency_id else u'', None, account_cell_style) ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, account_cell_style) row_pos += 2
def generate_xls_report(self, _p, _xs, data, objects, wb): ws = wb.add_sheet(_p.report_name[:31]) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 row_pos = 0 # set print header/footer ws.header_str = self.xls_headers['standard'] ws.footer_str = self.xls_footers['standard'] # cf. account_report_general_ledger.mako initial_balance_text = { 'initial_balance': _('Computed'), 'opening_balance': _('Opening Entries'), False: _('No') } # Title cell_style = xlwt.easyxf(_xs['xls_title']) report_name = ' - '.join([ _p.report_name.upper(), _p.company.partner_id.name, _p.company.currency_id.name ]) c_specs = [ ('report_name', 1, 0, 'text', report_name), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style) # write empty row to define column sizes c_sizes = self.column_sizes c_specs = [('empty%s' % i, 1, c_sizes[i], 'text', None) for i in range(0, len(c_sizes))] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, set_column_size=True) # Header Table cell_format = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('coa', 2, 0, 'text', _('Chart of Account')), ('fy', 1, 0, 'text', _('Fiscal Year')), ('df', 3, 0, 'text', _p.filter_form(data) == 'filter_date' and _('Dates Filter') or _('Periods Filter')), ('af', 1, 0, 'text', _('Accounts Filter')), ('tm', 2, 0, 'text', _('Target Moves')), ('ib', 2, 0, 'text', _('Initial Balance')), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_center) cell_format = _xs['borders_all'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('coa', 2, 0, 'text', _p.chart_account.name), ('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-'), ] df = _('From') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.start_date if _p.start_date else u'' else: df += _p.start_period.name if _p.start_period else u'' df += ' ' + _('To') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.stop_date if _p.stop_date else u'' else: df += _p.stop_period.name if _p.stop_period else u'' c_specs += [ ('df', 3, 0, 'text', df), ('af', 1, 0, 'text', _p.accounts(data) and ', '.join([account.code for account in _p.accounts(data)]) or _('All')), ('tm', 2, 0, 'text', _p.display_target_move(data)), ('ib', 2, 0, 'text', initial_balance_text[_p.initial_balance_mode]), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_center) ws.set_horz_split_pos(row_pos) row_pos += 1 # Column Title Row cell_format = _xs['bold'] c_title_cell_style = xlwt.easyxf(cell_format) # Column Header Row cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] c_hdr_cell_style = xlwt.easyxf(cell_format) c_hdr_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_hdr_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_hdr_cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) # Column Initial Balance Row cell_format = _xs['italic'] + _xs['borders_all'] c_init_cell_style = xlwt.easyxf(cell_format) c_init_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_init_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_init_cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) c_specs = [ ('date', 1, 0, 'text', _('Date'), None, c_hdr_cell_style), ('period', 1, 0, 'text', _('Period'), None, c_hdr_cell_style), ('move', 1, 0, 'text', _('Entry'), None, c_hdr_cell_style), ('journal', 1, 0, 'text', _('Journal'), None, c_hdr_cell_style), ('account_code', 1, 0, 'text', _('Account'), None, c_hdr_cell_style), ('partner', 1, 0, 'text', _('Partner'), None, c_hdr_cell_style), ('label', 1, 0, 'text', _('Label'), None, c_hdr_cell_style), ('counterpart', 1, 0, 'text', _('Counterpart'), None, c_hdr_cell_style), ('debit', 1, 0, 'text', _('Debit'), None, c_hdr_cell_style_right), ('credit', 1, 0, 'text', _('Credit'), None, c_hdr_cell_style_right), ('cumul_bal', 1, 0, 'text', _('Cumul. Bal.'), None, c_hdr_cell_style_right), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'text', _('Curr. Bal.'), None, c_hdr_cell_style_right), ('curr_code', 1, 0, 'text', _('Curr.'), None, c_hdr_cell_style_center), ] c_hdr_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) # cell styles for ledger lines ll_cell_format = _xs['borders_all'] ll_cell_style = xlwt.easyxf(ll_cell_format) ll_cell_style_right = xlwt.easyxf(ll_cell_format + _xs['right']) ll_cell_style_center = xlwt.easyxf(ll_cell_format + _xs['center']) ll_cell_style_date = xlwt.easyxf(ll_cell_format + _xs['left'], num_format_str=report_xls.date_format) ll_cell_style_decimal = xlwt.easyxf( ll_cell_format + _xs['right'], num_format_str=report_xls.decimal_format) cnt = 0 for account in objects: display_initial_balance = account.init_balance and ( account.init_balance.get('debit', 0.0) != 0.0 or account.init_balance.get('credit', 0.0) != 0.0) display_ledger_lines = account.ledger_lines if _p.display_account_raw(data) == 'all' or ( display_ledger_lines or display_initial_balance): #TO DO : replace cumul amounts by xls formulas cnt += 1 cumul_debit = 0.0 cumul_credit = 0.0 cumul_balance = 0.0 cumul_balance_curr = 0.0 c_specs = [ ('acc_title', 11, 0, 'text', ' - '.join([account.code, account.name])), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_title_cell_style) row_pos = self.xls_write_row(ws, row_pos, c_hdr_data) row_start = row_pos if display_initial_balance: cumul_debit = account.init_balance.get('debit') or 0.0 cumul_credit = account.init_balance.get('credit') or 0.0 cumul_balance = account.init_balance.get( 'init_balance') or 0.0 cumul_balance_curr = account.init_balance.get( 'init_balance_currency') or 0.0 debit_cell = rowcol_to_cell(row_pos, 8) credit_cell = rowcol_to_cell(row_pos, 9) bal_formula = debit_cell + '-' + credit_cell c_specs = [('empty%s' % x, 1, 0, 'text', None) for x in range(6)] c_specs += [ ('init_bal', 1, 0, 'text', _('Initial Balance')), ('counterpart', 1, 0, 'text', None), ('debit', 1, 0, 'number', cumul_debit, None, c_init_cell_style_decimal), ('credit', 1, 0, 'number', cumul_credit, None, c_init_cell_style_decimal), ('cumul_bal', 1, 0, 'number', cumul_balance, None, c_init_cell_style_decimal), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'number', cumul_balance_curr, None, c_init_cell_style_decimal), ('curr_code', 1, 0, 'text', None), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_init_cell_style) for line in account.ledger_lines: cumul_debit += line.get('debit') or 0.0 cumul_credit += line.get('credit') or 0.0 cumul_balance_curr += line.get('amount_currency') or 0.0 cumul_balance += line.get('balance') or 0.0 label_elements = [line.get('lname') or ''] if line.get('invoice_number'): label_elements.append("(%s)" % (line['invoice_number'], )) label = ' '.join(label_elements) if line.get('ldate'): c_specs = [ ('ldate', 1, 0, 'date', datetime.strptime(line['ldate'], '%Y-%m-%d'), None, ll_cell_style_date), ] else: c_specs = [ ('ldate', 1, 0, 'text', None), ] c_specs += [ ('period', 1, 0, 'text', line.get('period_code') or ''), ('move', 1, 0, 'text', line.get('move_name') or ''), ('journal', 1, 0, 'text', line.get('jcode') or ''), ('account_code', 1, 0, 'text', account.code), ('partner', 1, 0, 'text', line.get('partner_name') or ''), ('label', 1, 0, 'text', label), ('counterpart', 1, 0, 'text', line.get('counterparts') or ''), ('debit', 1, 0, 'number', line.get('debit', 0.0), None, ll_cell_style_decimal), ('credit', 1, 0, 'number', line.get('credit', 0.0), None, ll_cell_style_decimal), ('cumul_bal', 1, 0, 'number', cumul_balance, None, ll_cell_style_decimal), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'number', line.get('amount_currency') or 0.0, None, ll_cell_style_decimal), ('curr_code', 1, 0, 'text', line.get('currency_code') or '', None, ll_cell_style_center), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, ll_cell_style) debit_start = rowcol_to_cell(row_start, 8) debit_end = rowcol_to_cell(row_pos - 1, 8) debit_formula = 'SUM(' + debit_start + ':' + debit_end + ')' credit_start = rowcol_to_cell(row_start, 9) credit_end = rowcol_to_cell(row_pos - 1, 9) credit_formula = 'SUM(' + credit_start + ':' + credit_end + ')' balance_debit = rowcol_to_cell(row_pos, 8) balance_credit = rowcol_to_cell(row_pos, 9) balance_formula = balance_debit + '-' + balance_credit c_specs = [ ('acc_title', 7, 0, 'text', ' - '.join([account.code, account.name])), ('cum_bal', 1, 0, 'text', _('Cumulated Balance on Account'), None, c_hdr_cell_style_right), ('debit', 1, 0, 'number', None, debit_formula, c_hdr_cell_style_decimal), ('credit', 1, 0, 'number', None, credit_formula, c_hdr_cell_style_decimal), ('balance', 1, 0, 'number', None, balance_formula, c_hdr_cell_style_decimal), ] if _p.amount_currency(data): if account.currency_id: c_specs += [ ('curr_bal', 1, 0, 'number', cumul_balance_curr, None, c_hdr_cell_style_decimal) ] else: c_specs += [('curr_bal', 1, 0, 'text', None)] c_specs += [('curr_code', 1, 0, 'text', None)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_hdr_cell_style) row_pos += 1
def generate_xls_report(self, _p, _xs, data, objects, wb): ws = wb.add_sheet(_p.report_name[:31]) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 row_pos = 0 # set print header/footer ws.header_str = self.xls_headers['standard'] ws.footer_str = self.xls_footers['standard'] # cf. account_report_general_ledger.mako initial_balance_text = {'initial_balance': _('Computed'), 'opening_balance': _('Opening Entries'), False: _('No')} # Title cell_style = xlwt.easyxf(_xs['xls_title']) report_name = ' - '.join([_p.report_name.upper(), _p.company.partner_id.name, _p.company.currency_id.name]) c_specs = [ ('report_name', 1, 0, 'text', report_name), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style) # write empty row to define column sizes c_sizes = self.column_sizes c_specs = [('empty%s'%i, 1, c_sizes[i], 'text', None) for i in range(0,len(c_sizes))] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, set_column_size=True) # Header Table cell_format = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('coa', 2, 0, 'text', _('Chart of Account')), ('fy', 1, 0, 'text', _('Fiscal Year')), ('df', 3, 0, 'text', _p.filter_form(data) == 'filter_date' and _('Dates Filter') or _('Periods Filter')), ('af', 1, 0, 'text', _('Accounts Filter')), ('tm', 2, 0, 'text', _('Target Moves')), ('ib', 2, 0, 'text', _('Initial Balance')), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_center) cell_format = _xs['borders_all'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('coa', 2, 0, 'text', _p.chart_account.name), ('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-'), ] df = _('From') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.start_date if _p.start_date else u'' else: df += _p.start_period.name if _p.start_period else u'' df += ' ' + _('To') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.stop_date if _p.stop_date else u'' else: df += _p.stop_period.name if _p.stop_period else u'' c_specs += [ ('df', 3, 0, 'text', df), ('af', 1, 0, 'text', _p.accounts(data) and ', '.join([account.code for account in _p.accounts(data)]) or _('All')), ('tm', 2, 0, 'text', _p.display_target_move(data)), ('ib', 2, 0, 'text', initial_balance_text[_p.initial_balance_mode]), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_center) ws.set_horz_split_pos(row_pos) row_pos += 1 # Column Title Row cell_format = _xs['bold'] c_title_cell_style = xlwt.easyxf(cell_format) # Column Header Row cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] c_hdr_cell_style = xlwt.easyxf(cell_format) c_hdr_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_hdr_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_hdr_cell_style_decimal = xlwt.easyxf(cell_format + _xs['right'], num_format_str = report_xls.decimal_format) # Column Initial Balance Row cell_format = _xs['italic'] + _xs['borders_all'] c_init_cell_style = xlwt.easyxf(cell_format) c_init_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_init_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_init_cell_style_decimal = xlwt.easyxf(cell_format + _xs['right'], num_format_str = report_xls.decimal_format) c_specs = [ ('date', 1, 0, 'text', _('Date'), None, c_hdr_cell_style), ('period', 1, 0, 'text', _('Period'), None, c_hdr_cell_style), ('move', 1, 0, 'text', _('Entry'), None, c_hdr_cell_style), ('journal', 1, 0, 'text', _('Journal'), None, c_hdr_cell_style), ('account_code', 1, 0, 'text', _('Account'), None, c_hdr_cell_style), ('partner', 1, 0, 'text', _('Partner'), None, c_hdr_cell_style), ('label', 1, 0, 'text', _('Label'), None, c_hdr_cell_style), ('counterpart', 1, 0, 'text', _('Counterpart'), None, c_hdr_cell_style), ('debit', 1, 0, 'text', _('Debit'), None, c_hdr_cell_style_right), ('credit', 1, 0, 'text', _('Credit'), None, c_hdr_cell_style_right), ('cumul_bal', 1, 0, 'text', _('Cumul. Bal.'), None, c_hdr_cell_style_right), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'text', _('Curr. Bal.'), None, c_hdr_cell_style_right), ('curr_code', 1, 0, 'text', _('Curr.'), None, c_hdr_cell_style_center), ] c_hdr_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) # cell styles for ledger lines ll_cell_format = _xs['borders_all'] ll_cell_style = xlwt.easyxf(ll_cell_format) ll_cell_style_right = xlwt.easyxf(ll_cell_format + _xs['right']) ll_cell_style_center = xlwt.easyxf(ll_cell_format + _xs['center']) ll_cell_style_date = xlwt.easyxf(ll_cell_format + _xs['left'], num_format_str = report_xls.date_format) ll_cell_style_decimal = xlwt.easyxf(ll_cell_format + _xs['right'], num_format_str = report_xls.decimal_format) cnt = 0 for account in objects: display_initial_balance = account.init_balance and (account.init_balance.get('debit', 0.0) != 0.0 or account.init_balance.get('credit', 0.0) != 0.0) display_ledger_lines = account.ledger_lines if _p.display_account_raw(data) == 'all' or (display_ledger_lines or display_initial_balance): #TO DO : replace cumul amounts by xls formulas cnt += 1 cumul_debit = 0.0 cumul_credit = 0.0 cumul_balance = 0.0 cumul_balance_curr = 0.0 c_specs = [ ('acc_title', 11, 0, 'text', ' - '.join([account.code, account.name])), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_title_cell_style) row_pos = self.xls_write_row(ws, row_pos, c_hdr_data) row_start = row_pos if display_initial_balance: cumul_debit = account.init_balance.get('debit') or 0.0 cumul_credit = account.init_balance.get('credit') or 0.0 cumul_balance = account.init_balance.get('init_balance') or 0.0 cumul_balance_curr = account.init_balance.get('init_balance_currency') or 0.0 debit_cell = rowcol_to_cell(row_pos, 8) credit_cell = rowcol_to_cell(row_pos, 9) bal_formula = debit_cell + '-' + credit_cell c_specs = [('empty%s' %x, 1, 0, 'text', None) for x in range(6)] c_specs += [ ('init_bal', 1, 0, 'text', _('Initial Balance')), ('counterpart', 1, 0, 'text', None), ('debit', 1, 0, 'number', cumul_debit, None, c_init_cell_style_decimal), ('credit', 1, 0, 'number', cumul_credit, None, c_init_cell_style_decimal), ('cumul_bal', 1, 0, 'number', cumul_balance, None, c_init_cell_style_decimal), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'number', cumul_balance_curr, None, c_init_cell_style_decimal), ('curr_code', 1, 0, 'text', None), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_init_cell_style) for line in account.ledger_lines: cumul_debit += line.get('debit') or 0.0 cumul_credit += line.get('credit') or 0.0 cumul_balance_curr += line.get('amount_currency') or 0.0 cumul_balance += line.get('balance') or 0.0 label_elements = [line.get('lname') or ''] if line.get('invoice_number'): label_elements.append("(%s)" % (line['invoice_number'],)) label = ' '.join(label_elements) if line.get('ldate'): c_specs = [ ('ldate', 1, 0, 'date', datetime.strptime(line['ldate'],'%Y-%m-%d'), None, ll_cell_style_date), ] else: c_specs = [ ('ldate', 1, 0, 'text', None), ] c_specs += [ ('period', 1, 0, 'text', line.get('period_code') or ''), ('move', 1, 0, 'text', line.get('move_name') or ''), ('journal', 1, 0, 'text', line.get('jcode') or ''), ('account_code', 1, 0, 'text', account.code), ('partner', 1, 0, 'text', line.get('partner_name') or ''), ('label', 1, 0, 'text', label), ('counterpart', 1, 0, 'text', line.get('counterparts') or ''), ('debit', 1, 0, 'number', line.get('debit', 0.0), None, ll_cell_style_decimal), ('credit', 1, 0, 'number', line.get('credit', 0.0), None, ll_cell_style_decimal), ('cumul_bal', 1, 0, 'number', cumul_balance, None, ll_cell_style_decimal), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'number', line.get('amount_currency') or 0.0, None, ll_cell_style_decimal), ('curr_code', 1, 0, 'text', line.get('currency_code') or '', None, ll_cell_style_center), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, ll_cell_style) debit_start = rowcol_to_cell(row_start, 8) debit_end = rowcol_to_cell(row_pos-1, 8) debit_formula = 'SUM(' + debit_start + ':' + debit_end + ')' credit_start = rowcol_to_cell(row_start, 9) credit_end = rowcol_to_cell(row_pos-1, 9) credit_formula = 'SUM(' + credit_start + ':' + credit_end + ')' balance_debit = rowcol_to_cell(row_pos, 8) balance_credit = rowcol_to_cell(row_pos, 9) balance_formula = balance_debit + '-' + balance_credit c_specs = [ ('acc_title', 7, 0, 'text', ' - '.join([account.code, account.name])), ('cum_bal', 1, 0, 'text', _('Cumulated Balance on Account'), None, c_hdr_cell_style_right), ('debit', 1, 0, 'number', None, debit_formula, c_hdr_cell_style_decimal), ('credit', 1, 0, 'number', None, credit_formula, c_hdr_cell_style_decimal), ('balance', 1, 0, 'number', None, balance_formula, c_hdr_cell_style_decimal), ] if _p.amount_currency(data): if account.currency_id: c_specs += [('curr_bal', 1, 0, 'number', cumul_balance_curr, None, c_hdr_cell_style_decimal)] else: c_specs += [('curr_bal', 1, 0, 'text', None)] c_specs += [('curr_code', 1, 0, 'text', None)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_hdr_cell_style) row_pos += 1
def generate_xls_report(self, _p, _xs, data, objects, wb): row_pos, row_data, ws = self.create_ws(_p, _xs, data, wb) # Column Title Row cell_format = _xs['bold'] c_title_cell_style = xlwt.easyxf(cell_format) # Column Header Row cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] c_hdr_cell_style = xlwt.easyxf(cell_format) c_hdr_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_hdr_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_hdr_cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) # Column Initial Balance Row cell_format = _xs['italic'] + _xs['borders_all'] c_init_cell_style = xlwt.easyxf(cell_format) c_init_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_init_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_init_cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) c_specs = [ ('date', 1, 0, 'text', _('Fecha'), None, c_hdr_cell_style), ('period', 1, 0, 'text', _('Periodo'), None, c_hdr_cell_style), ('move', 1, 0, 'text', _('Asiento'), None, c_hdr_cell_style), ('journal', 1, 0, 'text', _('Diario'), None, c_hdr_cell_style), ('account_code', 1, 0, 'text', _('Cuenta'), None, c_hdr_cell_style), ('account_analytic', 1, 0, 'text', _('Centro de Costo'), None, c_hdr_cell_style), ('partner', 1, 0, 'text', _('Empresa'), None, c_hdr_cell_style), ('label', 1, 0, 'text', _('Descripcion'), None, c_hdr_cell_style), ('counterpart', 1, 0, 'text', _('Contrapartida'), None, c_hdr_cell_style), ('debit', 1, 0, 'text', _('Debito'), None, c_hdr_cell_style_right), ('credit', 1, 0, 'text', _('Credito'), None, c_hdr_cell_style_right), ('cumul_bal', 1, 0, 'text', _('Cumul. Bal.'), None, c_hdr_cell_style_right), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'text', _('Curr. Bal.'), None, c_hdr_cell_style_right), ('curr_code', 1, 0, 'text', _('Curr.'), None, c_hdr_cell_style_center), ] c_hdr_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) # cell styles for ledger lines ll_cell_format = _xs['borders_all'] ll_cell_style = xlwt.easyxf(ll_cell_format) ll_cell_style_right = xlwt.easyxf(ll_cell_format + _xs['right']) ll_cell_style_center = xlwt.easyxf(ll_cell_format + _xs['center']) ll_cell_style_date = xlwt.easyxf(ll_cell_format + _xs['left'], num_format_str=report_xls.date_format) ll_cell_style_decimal = xlwt.easyxf( ll_cell_format + _xs['right'], num_format_str=report_xls.decimal_format) cnt = 0 sheet_number = 1 for account in objects: if len(account.ledger_lines) >= 65536: raise ValueError( "La cuenta %s tiene mas 65536 registros y es imposible presentarla." % (account.code + ' - ' + account.name)) elif (row_pos + len(account.ledger_lines)) >= 65536: ws.name = _p.report_name[:31] + ' ' + str(sheet_number) sheet_number += 1 row_pos, row_data, ws = \ self.create_ws(_p, _xs, data, wb, context={'name': _p.report_name[:31] + ' ' + str(sheet_number)}) display_initial_balance = account.init_balance and ( account.init_balance.get('debit', 0.0) != 0.0 or account.init_balance.get('credit', 0.0) != 0.0) display_ledger_lines = account.ledger_lines if _p.display_account_raw(data) == 'all' or ( display_ledger_lines or display_initial_balance): #TO DO : replace cumul amounts by xls formulas cnt += 1 cumul_debit = 0.0 cumul_credit = 0.0 cumul_balance = 0.0 cumul_balance_curr = 0.0 c_specs = [ ('acc_title', 11, 0, 'text', ' - '.join([account.code, account.name])), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_title_cell_style) row_pos = self.xls_write_row(ws, row_pos, c_hdr_data) row_start = row_pos if display_initial_balance: cumul_debit = account.init_balance.get('debit') or 0.0 cumul_credit = account.init_balance.get('credit') or 0.0 cumul_balance = account.init_balance.get( 'init_balance') or 0.0 cumul_balance_curr = account.init_balance.get( 'init_balance_currency') or 0.0 debit_cell = rowcol_to_cell(row_pos, 8) credit_cell = rowcol_to_cell(row_pos, 9) bal_formula = debit_cell + '-' + credit_cell c_specs = [('empty%s' % x, 1, 0, 'text', None) for x in range(7)] c_specs += [ ('init_bal', 1, 0, 'text', _('Balance Inicial')), ('counterpart', 1, 0, 'text', None), ('debit', 1, 0, 'number', cumul_debit, None, c_init_cell_style_decimal), ('credit', 1, 0, 'number', cumul_credit, None, c_init_cell_style_decimal), ('cumul_bal', 1, 0, 'number', cumul_balance, None, c_init_cell_style_decimal), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'number', cumul_balance_curr, None, c_init_cell_style_decimal), ('curr_code', 1, 0, 'text', None), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_init_cell_style) for line in account.ledger_lines: cumul_debit += line.get('debit') or 0.0 cumul_credit += line.get('credit') or 0.0 cumul_balance_curr += line.get('amount_currency') or 0.0 cumul_balance += line.get('balance') or 0.0 label_elements = [line.get('lname') or ''] if line.get('invoice_number'): label_elements.append("(%s)" % (line['invoice_number'], )) label = ' '.join(label_elements) if line.get('ldate'): c_specs = [ ('ldate', 1, 0, 'date', datetime.strptime(line['ldate'], '%Y-%m-%d'), None, ll_cell_style_date), ] else: c_specs = [ ('ldate', 1, 0, 'text', None), ] c_specs += [ ('period', 1, 0, 'text', line.get('period_code') or ''), ('move', 1, 0, 'text', line.get('move_name') or ''), ('journal', 1, 0, 'text', line.get('jcode') or ''), ('account_code', 1, 0, 'text', account.code), ('account_analytic', 1, 0, 'text', line.get('account_analytic_name') or ''), ('partner', 1, 0, 'text', line.get('partner_name') or ''), ('label', 1, 0, 'text', label), ('counterpart', 1, 0, 'text', line.get('counterparts') or ''), ('debit', 1, 0, 'number', line.get('debit', 0.0), None, ll_cell_style_decimal), ('credit', 1, 0, 'number', line.get('credit', 0.0), None, ll_cell_style_decimal), ('cumul_bal', 1, 0, 'number', cumul_balance, None, ll_cell_style_decimal), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'number', line.get('amount_currency') or 0.0, None, ll_cell_style_decimal), ('curr_code', 1, 0, 'text', line.get('currency_code') or '', None, ll_cell_style_center), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, ll_cell_style) debit_start = rowcol_to_cell(row_start, 9) debit_end = rowcol_to_cell(row_pos - 1, 9) debit_formula = 'SUM(' + debit_start + ':' + debit_end + ')' credit_start = rowcol_to_cell(row_start, 10) credit_end = rowcol_to_cell(row_pos - 1, 10) credit_formula = 'SUM(' + credit_start + ':' + credit_end + ')' balance_debit = rowcol_to_cell(row_pos, 9) balance_credit = rowcol_to_cell(row_pos, 10) balance_formula = balance_debit + '-' + balance_credit c_specs = [ ('acc_title', 8, 0, 'text', ' - '.join([account.code, account.name])), ('cum_bal', 1, 0, 'text', _('Balance Acumulado en la Cuenta'), None, c_hdr_cell_style_right), ('debit', 1, 0, 'number', None, debit_formula, c_hdr_cell_style_decimal), ('credit', 1, 0, 'number', None, credit_formula, c_hdr_cell_style_decimal), ('balance', 1, 0, 'number', None, balance_formula, c_hdr_cell_style_decimal), ] if _p.amount_currency(data): if account.currency_id: c_specs += [ ('curr_bal', 1, 0, 'number', cumul_balance_curr, None, c_hdr_cell_style_decimal) ] else: c_specs += [('curr_bal', 1, 0, 'text', None)] c_specs += [('curr_code', 1, 0, 'text', None)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_hdr_cell_style) row_pos += 1
def generate_xls_report(self, _p, _xs, data, objects, wb): ws = wb.add_sheet(_p.report_name[:31]) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 row_pos = 0 # set print header/footer ws.header_str = self.xls_headers['standard'] ws.footer_str = self.xls_footers['standard'] # cf. account_report_partner_ledger.mako initial_balance_text = {'initial_balance': _('Computed'), 'opening_balance': _('Opening Entries'), False: _('No')} # Title cell_style = xlwt.easyxf(_xs['xls_title']) report_name = ' - '.join([_p.report_name.upper(), _p.company.partner_id.name, _p.company.currency_id.name]) c_specs = [ ('report_name', 1, 0, 'text', report_name), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style) # write empty row to define column sizes c_sizes = self.column_sizes c_specs = [('empty%s'%i, 1, c_sizes[i], 'text', None) for i in range(0,len(c_sizes))] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, set_column_size=True) # Header Table nbr_columns = 10 if _p.amount_currency(data): nbr_columns = 12 cell_format = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('coa', 2, 0, 'text', _('Chart of Account')), ('fy', 1, 0, 'text', _('Fiscal Year')), ('df', 2, 0, 'text', _p.filter_form(data) == 'filter_date' and _('Dates Filter') or _('Periods Filter')), ('af', 1, 0, 'text', _('Accounts Filter')), ('tm', 2, 0, 'text', _('Target Moves')), ('ib', nbr_columns-8, 0, 'text', _('Initial Balance')), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_center) cell_format = _xs['borders_all'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('coa', 2, 0, 'text', _p.chart_account.name), ('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-'), ] df = _('From') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.start_date if _p.start_date else u'' else: df += _p.start_period.name if _p.start_period else u'' df += ' ' + _('To') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.stop_date if _p.stop_date else u'' else: df += _p.stop_period.name if _p.stop_period else u'' c_specs += [ ('df', 2, 0, 'text', df), ('af', 1, 0, 'text', _('Custom Filter') if _p.partner_ids else _p.display_partner_account(data)), ('tm', 2, 0, 'text', _p.display_target_move(data)), ('ib', nbr_columns-8, 0, 'text', initial_balance_text[_p.initial_balance_mode]), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_center) ws.set_horz_split_pos(row_pos) row_pos += 1 # Account Title Row cell_format = _xs['xls_title'] + _xs['bold'] + _xs['fill'] + _xs['borders_all'] account_cell_style = xlwt.easyxf(cell_format) account_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) account_cell_style_decimal = xlwt.easyxf(cell_format + _xs['right'], num_format_str = report_xls.decimal_format) # Column Title Row cell_format = _xs['bold'] c_title_cell_style = xlwt.easyxf(cell_format) # Column Header Row cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] c_hdr_cell_style = xlwt.easyxf(cell_format) c_hdr_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_hdr_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_hdr_cell_style_decimal = xlwt.easyxf(cell_format + _xs['right'], num_format_str = report_xls.decimal_format) # Column Initial Balance Row cell_format = _xs['italic'] + _xs['borders_all'] c_init_cell_style = xlwt.easyxf(cell_format) c_init_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_init_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_init_cell_style_decimal = xlwt.easyxf(cell_format + _xs['right'], num_format_str = report_xls.decimal_format) # Column Cumulated balance Row cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] c_cumul_cell_style = xlwt.easyxf(cell_format) c_cumul_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_cumul_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_cumul_cell_style_decimal = xlwt.easyxf(cell_format + _xs['right'], num_format_str = report_xls.decimal_format) # Column Partner Row cell_format = _xs['bold'] c_part_cell_style = xlwt.easyxf(cell_format) c_specs = [ ('date', 1, 0, 'text', _('Date'), None, c_hdr_cell_style), ('period', 1, 0, 'text', _('Period'), None, c_hdr_cell_style), ('move', 1, 0, 'text', _('Entry'), None, c_hdr_cell_style), ('journal', 1, 0, 'text', _('Journal'), None, c_hdr_cell_style), ('partner', 1, 0, 'text', _('Partner'), None, c_hdr_cell_style), ('label', 1, 0, 'text', _('Label'), None, c_hdr_cell_style), ('rec', 1, 0, 'text', _('Rec.'), None, c_hdr_cell_style), ('debit', 1, 0, 'text', _('Debit'), None, c_hdr_cell_style_right), ('credit', 1, 0, 'text', _('Credit'), None, c_hdr_cell_style_right), ('cumul_bal', 1, 0, 'text', _('Cumul. Bal.'), None, c_hdr_cell_style_right), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'text', _('Curr. Bal.'), None, c_hdr_cell_style_right), ('curr_code', 1, 0, 'text', _('Curr.'), None, c_hdr_cell_style_center), ] c_hdr_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) # cell styles for ledger lines ll_cell_format = _xs['borders_all'] ll_cell_style = xlwt.easyxf(ll_cell_format) ll_cell_style_right = xlwt.easyxf(ll_cell_format + _xs['right']) ll_cell_style_center = xlwt.easyxf(ll_cell_format + _xs['center']) ll_cell_style_date = xlwt.easyxf(ll_cell_format + _xs['left'], num_format_str = report_xls.date_format) ll_cell_style_decimal = xlwt.easyxf(ll_cell_format + _xs['right'], num_format_str = report_xls.decimal_format) cnt = 0 for account in objects: if account.ledger_lines or account.init_balance: if not account.partners_order: continue cnt += 1 account_total_debit = 0.0 account_total_credit = 0.0 account_balance_cumul = 0.0 account_balance_cumul_curr = 0.0 c_specs = [ ('acc_title', nbr_columns, 0, 'text', ' - '.join([account.code, account.name]), None, account_cell_style), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_title_cell_style) row_pos += 1 row_start_account = row_pos for partner_name, p_id, p_ref, p_name in account.partners_order: total_debit = 0.0 total_credit = 0.0 cumul_balance = 0.0 cumul_balance_curr = 0.0 part_cumul_balance = 0.0 part_cumul_balance_curr = 0.0 c_specs = [ ('partner_title', nbr_columns, 0, 'text', partner_name or _('No Partner'), None, c_part_cell_style), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_title_cell_style) row_pos = self.xls_write_row(ws, row_pos, c_hdr_data) row_start_partner = row_pos total_debit = account.init_balance.get(p_id, {}).get('debit') or 0.0 total_credit = account.init_balance.get(p_id, {}).get('credit') or 0.0 init_line = False if _p.initial_balance_mode and (total_debit or total_credit): init_line = True part_cumul_balance = account.init_balance.get(p_id, {}).get('init_balance') or 0.0 part_cumul_balance_curr = account.init_balance.get(p_id, {}).get('init_balance_currency') or 0.0 balance_forward_currency = account.init_balance.get(p_id, {}).get('currency_name') or '' cumul_balance += part_cumul_balance cumul_balance_curr += part_cumul_balance_curr debit_cell = rowcol_to_cell(row_pos, 7) credit_cell = rowcol_to_cell(row_pos, 8) init_bal_formula = debit_cell + '-' + credit_cell ################## Print row 'Initial Balance' by partner ################# c_specs = [('empty%s' %x, 1, 0, 'text', None) for x in range(5)] c_specs += [ ('init_bal', 1, 0, 'text', _('Initial Balance')), ('rec', 1, 0, 'text', None), ('debit', 1, 0, 'number', total_debit, None, c_init_cell_style_decimal), ('credit', 1, 0, 'number', total_credit, None, c_init_cell_style_decimal), ('cumul_bal', 1, 0, 'number', None, init_bal_formula, c_init_cell_style_decimal), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'number', part_cumul_balance_curr, None, c_init_cell_style_decimal), ('curr_code', 1, 0, 'text', balance_forward_currency), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_init_cell_style) for line in account.ledger_lines.get(p_id, []): total_debit += line.get('debit') or 0.0 total_credit += line.get('credit') or 0.0 label_elements = [line.get('lname') or ''] if line.get('invoice_number'): label_elements.append("(%s)" % (line['invoice_number'],)) label = ' '.join(label_elements) cumul_balance += line.get('balance') or 0.0 if init_line or row_pos > row_start_partner: cumbal_formula = rowcol_to_cell(row_pos-1, 9) + '+' else: cumbal_formula = '' debit_cell = rowcol_to_cell(row_pos, 7) credit_cell = rowcol_to_cell(row_pos, 8) cumbal_formula += debit_cell + '-' + credit_cell ################## Print row ledger line data ################# if line.get('ldate'): c_specs = [ ('ldate', 1, 0, 'date', datetime.strptime(line['ldate'],'%Y-%m-%d'), None, ll_cell_style_date), ] else: c_specs = [ ('ldate', 1, 0, 'text', None), ] c_specs += [ ('period', 1, 0, 'text', line.get('period_code') or ''), ('move', 1, 0, 'text', line.get('move_name') or ''), ('journal', 1, 0, 'text', line.get('jcode') or ''), ('partner', 1, 0, 'text', line.get('partner_name') or ''), ('label', 1, 0, 'text', label), ('rec_name', 1, 0, 'text', line.get('rec_name') or ''), ('debit', 1, 0, 'number', line.get('debit'), None, ll_cell_style_decimal), ('credit', 1, 0, 'number', line.get('credit'), None, ll_cell_style_decimal), ('cumul_bal', 1, 0, 'number', None, cumbal_formula, ll_cell_style_decimal), ] if _p.amount_currency(data): c_specs += [ ('curr_bal', 1, 0, 'number', line.get('amount_currency') or 0.0, None, ll_cell_style_decimal), ('curr_code', 1, 0, 'text', line.get('currency_code') or '', None, ll_cell_style_center), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, ll_cell_style) #end for line ################## Print row Cumulated Balance by partner ################# debit_partner_start = rowcol_to_cell(row_start_partner, 7) debit_partner_end = rowcol_to_cell(row_pos-1, 7) debit_partner_total = 'SUM(' + debit_partner_start + ':' + debit_partner_end + ')' credit_partner_start = rowcol_to_cell(row_start_partner, 8) credit_partner_end = rowcol_to_cell(row_pos-1, 8) credit_partner_total = 'SUM(' + credit_partner_start + ':' + credit_partner_end + ')' bal_partner_debit = rowcol_to_cell(row_pos, 7) bal_partner_credit = rowcol_to_cell(row_pos, 8) bal_partner_total = bal_partner_debit + '-' + bal_partner_credit c_specs = [('empty%s' %x, 1, 0, 'text', None) for x in range(5)] c_specs += [ ('init_bal', 1, 0, 'text', _('Cumulated balance on Partner')), ('rec', 1, 0, 'text', None), ('debit', 1, 0, 'number', None, debit_partner_total, c_cumul_cell_style_decimal), ('credit', 1, 0, 'number', None, credit_partner_total, c_cumul_cell_style_decimal), ('cumul_bal', 1, 0, 'number', None, bal_partner_total, c_cumul_cell_style_decimal), ] if _p.amount_currency(data): if account.currency_id: c_specs += [('curr_bal', 1, 0, 'number', cumul_balance_curr or 0.0, None, c_cumul_cell_style_decimal)] else: c_specs += [('curr_bal', 1, 0, 'text', '-', None, c_cumul_cell_style_right)] c_specs += [('curr_code', 1, 0, 'text', account.currency_id.name if account.currency_id else u'', None, c_cumul_cell_style_center)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_cumul_cell_style) row_pos += 1 account_total_debit += total_debit account_total_credit += total_credit account_balance_cumul += cumul_balance account_balance_cumul_curr += cumul_balance_curr ################## Print row Cumulated Balance by account ################# c_specs = [('acc_title', 5, 0, 'text', ' - '.join([account.code, account.name])), ] c_specs += [ ('label', 1, 0, 'text', _('Cumulated balance on Account')), ('rec', 1, 0, 'text', None), ('debit', 1, 0, 'number', account_total_debit, None, account_cell_style_decimal), ('credit', 1, 0, 'number', account_total_credit, None, account_cell_style_decimal), ('cumul_bal', 1, 0, 'number', account_balance_cumul, None, account_cell_style_decimal), ] if _p.amount_currency(data): if account.currency_id: c_specs += [('curr_bal', 1, 0, 'number', account_balance_cumul_curr or 0.0, None, account_cell_style_decimal)] else: c_specs += [('curr_bal', 1, 0, 'text', '-', None, account_cell_style_right)] c_specs += [('curr_code', 1, 0, 'text', account.currency_id.name if account.currency_id else u'', None, account_cell_style)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, account_cell_style) row_pos += 2