import xlwt

from datetime import datetime

from openerp.osv import orm

from openerp.osv import osv

from openerp.addons.report_xls.report_xls import report_xls

from openerp.addons.report_xls.utils import rowcol_to_cell, _render

#from openerp.addons.point_of_sale import pos_details

from openerp.tools.translate import _

from openerp.report import report_sxw

import logging

_logger = logging.getLogger(__name__)

class export_excel_xls_parser(report_sxw.rml_parse):

def __init__(self, cr, uid, name, context):

super(export_excel_xls_parser, self).__init__(cr, uid, name,

context=context)

self.context = context

wanted_list = excel_obj._report_xls_fields(cr, uid, context)

template_changes = excel_obj._report_xls_template(cr, uid, context)

self.localcontext.update({

'datetime': datetime,

'wanted_list': wanted_list,

'template_changes': template_changes,

})

class export_excel_xls(report_xls):

def __init__(self, name, table, rml=False, parser=False, header=True,

store=False):

super(export_excel_xls, self).__init__(

name, table, rml, parser, header, store)

# Cell Styles

_xs = self.xls_styles

# header

rh_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']

self.rh_cell_style = xlwt.easyxf(rh_cell_format)

self.rh_cell_style_center = xlwt.easyxf(rh_cell_format + _xs['center'])

self.rh_cell_style_right = xlwt.easyxf(rh_cell_format + _xs['right'])

# lines

aml_cell_format = _xs['borders_all']

self.aml_cell_style = xlwt.easyxf(aml_cell_format)

self.aml_cell_style_center = xlwt.easyxf(

aml_cell_format + _xs['center'])

self.aml_cell_style_date = xlwt.easyxf(

aml_cell_format + _xs['left'],

num_format_str=report_xls.date_format)

self.aml_cell_style_decimal = xlwt.easyxf(

aml_cell_format + _xs['right'],

num_format_str=report_xls.decimal_format)

# totals

rt_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']

self.rt_cell_style = xlwt.easyxf(rt_cell_format)

self.rt_cell_style_right = xlwt.easyxf(rt_cell_format + _xs['right'])

self.rt_cell_style_decimal = xlwt.easyxf(

rt_cell_format + _xs['right'],

num_format_str=report_xls.decimal_format)

def _excel_title(self, o, ws, _p, row_pos, _xs):

cell_style = xlwt.easyxf(_xs['xls_title'])

report_name = (10 * ' ').join([

_p.company.name,

_p.title(o)[0],

_p.title(o)[1],

_p._("excel Overview") + ' - ' + _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)

return row_pos + 1

def _excel_lines(self, o, ws, _p, row_pos, _xs):

wanted_list = self.wanted_list

debit_pos = self.debit_pos

credit_pos = self.credit_pos

# Column headers

c_specs = map(lambda x: self.render(

x, self.col_specs_lines_template, 'header',

render_space={'_': _p._}), wanted_list)

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=self.rh_cell_style,

set_column_size=True)

ws.set_horz_split_pos(row_pos)

# export move lines

aml_start_pos = row_pos

aml_cnt = len(_p.lines(o))

cnt = 0

for l in _p.lines(o):

cnt += 1

debit_cell = rowcol_to_cell(row_pos, debit_pos)

credit_cell = rowcol_to_cell(row_pos, credit_pos)

bal_formula = debit_cell + '-' + credit_cell

_logger.debug('dummy call - %s', bal_formula)

c_specs = map(

lambda x: self.render(x, self.col_specs_lines_template,

'lines'), wanted_list)

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=self.aml_cell_style)

if l['draw_line'] and cnt != aml_cnt:

row_pos += 1

def _excel_vat_summary(self, o, ws, _p, row_pos, _xs):

if not _p.tax_codes(o):

return row_pos

title_cell_style = xlwt.easyxf(_xs['bold'])

c_specs = [('summary_title', 1, 0, 'text', _p._("VAT Declaration"))]

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=title_cell_style) + 1

wanted_list = self.wanted_list

vat_summary_wanted_list = ['tax_case_name', 'tax_code', 'tax_amount']

# calculate col_span

cols_number = len(wanted_list)

vat_summary_cols_number = len(vat_summary_wanted_list)

if vat_summary_cols_number > cols_number:

raise orm.except_orm(

_('Programming Error!'),

_("vat_summary_cols_number should be < cols_number !"))

index = 0

for i in range(vat_summary_cols_number):

col = vat_summary_wanted_list[i]

col_size = self.col_specs_lines_template[

wanted_list[index]]['header'][1]

templ_col_size = self.col_specs_vat_summary_template[

col]['header'][1]

# _logger.warn("col=%s, col_size=%s, templ_col_size=%s",

# col, col_size, templ_col_size)

col_span = 1

if templ_col_size > col_size:

new_size = col_size

while templ_col_size > new_size:

col_span += 1

index += 1

new_size += self.col_specs_lines_template[

wanted_list[index]]['header'][1]

self.col_specs_vat_summary_template[col]['header'][0] = col_span

self.col_specs_vat_summary_template[

col]['tax_totals'][0] = col_span

index += 1

c_specs = map(lambda x: self.render(

x, self.col_specs_vat_summary_template, 'header'),

vat_summary_wanted_list)

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=self.rh_cell_style)

for t in _p.tax_codes(o):

c_specs = map(lambda x: self.render(

x, self.col_specs_vat_summary_template, 'tax_totals'),

vat_summary_wanted_list)

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=self.aml_cell_style)

return row_pos

def generate_xls_report(self, _p, _xs, data, objects, wb):

wanted_list = _p.wanted_list

if _p.display_currency:

wanted_list += ['amount_currency', 'currency_name']

self.wanted_list = wanted_list

self.col_specs_lines_template.update(_p.template_changes)

self.debit_pos = 'debit' in wanted_list and wanted_list.index('debit')

self.credit_pos = 'credit' in wanted_list and wanted_list.index(

'credit')

if not (self.credit_pos and self.debit_pos) and 'balance' \

in wanted_list:

raise orm.except_orm(_('Customisation Error!'),

_("The 'Balance' field is a calculated XLS \

field requiring the presence of the \

'Debit' and 'Credit' fields !"))

for o in objects:

sheet_name = ' - '.join([o[1].code, o[0].code]

)[:31].replace('/', '-')

sheet_name = sheet_name[:31].replace('/', '-')

ws = wb.add_sheet(sheet_name)

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']

# Data

row_pos = self._excel_title(o, ws, _p, row_pos, _xs)

row_pos = self._excel_lines(o, ws, _p, row_pos, _xs)

row_pos = self._excel_vat_summary(o, ws, _p, row_pos, _xs)

export_excel_xls('report.nov.export.excel.xls', 'export.excel.period',

parser=export_excel_xls_parser)