Excel Report OpenERP

Salah satu report yang bisa dihasilkan oleh OpenERP adalah excel report. Biasanya report ini banyak digunakan untuk pengolahan data accounting seperti General Ledger, Trial Balance, Profit Loss dan Balance Sheet. Pada kesempatan kali ini penulis mencoba mejelaskan tentang pembuatan excel report khususnya Balance Sheet report.

Seperti biasa, dalam mengenerate report-report yang bersifat rekapan penulis sering menggunakan wizard untuk menghasilkannya. Maka kali ini kita juga gunakan wizard untuk membuat excel report.

Awalan, kita buat modul wizard yang berisikan file python dengan code dibawah ini :



class AccountLegal(osv.osv_memory):
    _name = "accounting.legal"
    _columns = {
                'report': fields.selection((('gl','General Ledger'), ('tb','Trial Balance'), ('bs','Balance Sheet'), ('pl','Profit Loss')), 'Report'),
                'fiscalyear_id': fields.many2one('account.fiscalyear', 'Fiscal Year', required=True),
                'period_from': fields.many2one('account.period', 'Start Period', domain="[('fiscalyear_id', '=', fiscalyear_id)]", required=True),
                'period_to': fields.many2one('account.period', 'End Period', domain="[('fiscalyear_id', '=', fiscalyear_id)]", required=True),
                'name': fields.char('File Name', 16),               
    }   
    
    _defaults = {
                'report': 'bs',
                'fiscalyear_id':1,
                'period_from': 2,
                'period_to': 12,
    }   

AccountLegal()

Setelah kita membuat object/tabel yang berisikan beberapa field/kolom, maka selanjutnya kita membuat code interfacenya dalam file xml seperti dibawah ini :



		<record model="ir.ui.view" id="view_wizard_acccounting_legal">
            <field name="name">Accounting Legal</field>
            <field name="model">accounting.legal</field>
            <field name="type">form</field>
            <field name="arch" type="xml">
                <form col="4" string="Report" version="7.0">
                	<group>
                	    <field name="report"/>
                	</group>
					<group col="2">
						<field name="fiscalyear_id" colspan="2"/>
						<field name="period_from"/>
						<field name="period_to"/>
						<field name="name" invisible="True"/>
					</group>
					<footer>
	                    <button name="eksport_excel" string="Print" type="object" default_focus="1" class="oe_highlight"/>
	                    or 
	                    <button string="Cancel" class="oe_link" special="cancel" />
	                </footer>
                </form>
            </field>
        </record>
              
        <record model="ir.actions.act_window" id="action_accounting_legal">
            <field name="name">Accounting Legal</field>
            <field name="res_model">accounting.legal</field>
            <field name="view_type">form</field>
            <field name="view_mode">form</field>
            <field name="target">new</field>
        </record>
       
        <menuitem id="menu_accounting_legal" name="Legal Report" action="action_accounting_legal" parent="account.final_accounting_reports"/>



Seperti biasa, dalam membuat sebuah wizard ada beberapa hal yang mesti kita definiskan yaitu menu, event , dan tampilan form wizard itu sendiri. Pada tampilan form diatas kita membuat sebuah button yang bernama ‘eksport_excel’ dengan tipe ‘object’. Artinya kita harus membuat sebuah method pada object tersebut dengan nama yang sama sebagai action dari button tersebut. Code dari method tersebut kita isi seperti ini :


 
    def eksport_excel(self, cr, uid, ids, context=None):
        data = []
        val = self.browse(cr, uid, ids)[0]
        obj_account = self.pool.get('account.account')
        obj_move = self.pool.get('account.move') 
        
        # Kita buat rule untuk memastikan user memasukan period yang benar urutannya
        if val.period_from.id > val.period_to.id:
            raise osv.except_osv(('Error'), ('Start period should be smaller then End period'))
        
        # Rule ini berfungsi untuk memastikan bahwa account dengan nama BALANCE SHEET telah ada di CoA
        bsid = obj_account.search(cr, uid, [('name', '=', 'BALANCE SHEET')])
        if not bsid:
            raise osv.except_osv(('Error'), ('You must create BALANCE SHEET account'))
        
        # Query semua hirarki turunan account BALANCE SHEET & ambil datanya 
        ids_acc = obj_account._get_children_and_consol(cr, uid, [bsid[0]])
        acc_data = obj_account.browse(cr, uid, ids_acc)
        
        # Ambil semua transaksi yang berada di semua jurnal yang terkait dengan account diatas
        mid = obj_move.search(cr, uid, [('state', '=', 'posted'), ('period_id', 'in', tuple([x.id for x in val.fiscalyear_id.period_ids if x.id <= val.period_from.id]))])
        
        # Lakukan looping dari semua account dan ambil mutasi amountnya (debit & kredit) dengan method self.get_amount() 
        for acc in acc_data:
            if acc.type == 'view':
                ending = self.get_amount(cr, uid, ids, acc, mid)
                data.append([acc.code, acc.name, ending[0]-ending[1]])
        
        if context is None:
            context = {}
            
        # Persiapkan parameter untuk excel report
        nilai = self.read(cr, uid, ids)[0]
        datas = {'ids': [nilai['id']]}
        datas['model'] = 'accounting.legal'
        datas['form'] = nilai
        datas['csv'] = data
        
        return {
            'type': 'ir.actions.report.xml',
            'report_name': 'balance.sheet.excel',
            'nodestroy': True,
            'datas': datas,
        }
        
    def get_amount(self, cr, uid, ids, account, move):
        ids_acc = self.pool.get('account.account')._get_children_and_consol(cr, uid, [account.id])
        acc_data = self.pool.get('account.account').browse(cr, uid, ids_acc)
        debit = 0; credit = 0
        for a in acc_data:
            if a.type != 'view':
                lid = self.pool.get('account.move.line').search(cr, uid, [('move_id', 'in', move), ('account_id', '=', a.id)])
                lad = self.pool.get('account.move.line').browse(cr, uid, lid)
                debit += sum([x.debit for x in lad])
                credit += sum([x.credit for x in lad])
        return (debit, credit)
    

Setelah form interface & data telah disiapkan, maka selanjutnya kita harus membuat 1 folder didalam modul tersebut dengan nama ‘report’. Folder report tersebut berisi 3 file, yaitu :

1. File __init__.py yang berisi :


import report_mutasi

2. File report_excel.mako yang isinya kosong karna sebagai dummy

3. File report_mutasi.py yang isinya sebagai berikut :


import re
import time
import xlwt
from report import report_sxw
from report_engine_xls import report_xls

class ReportStatus(report_sxw.rml_parse):
    def __init__(self, cr, uid, name, context=None):
        super(ReportStatus, self).__init__(cr, uid, name, context=context)
        self.localcontext.update({
            'time': time,            
        })


class balance_sheet_xls(report_xls):

    def generate_xls_report(self, parser, data, obj, wb):
        ws = wb.add_sheet(('Balance Sheet'))
        ws.panes_frozen = True
        ws.remove_splits = True
        ws.portrait = 0 # Landscape
        ws.fit_width_to_pages = 1

        cols_specs = [
                      ('Account Code', 1, 70, 'text', lambda x, d, p: x[0]),
                      ('Account Name', 1, 120, 'text', lambda x, d, p: x[1]),
                      ('Ending Balance', 1, 100, 'text', lambda x, d, p: x[2]),
        ]
       
        style = xlwt.easyxf('pattern: pattern solid, fore_colour light_blue;')
        title = self.xls_row_template(cols_specs, ['Account Code', 'Account Name', 'Ending Balance'])
        self.xls_write_row_header(ws, 0, title, style, set_column_size=True)
        
        
        row_count = 1
        for x in data['csv']:
            ws.write(row_count, 0, x[0])
            ws.write(row_count, 1, x[1])
            ws.write(row_count, 2, x[2])
            row_count += 1
            
        pass

balance_sheet_xls('report.balance.sheet.excel', 'account.move', 'addons/ad_account_report/report/report_excel.mako', parser=ReportStatus, header=False)


4. File report_engine_xls.py yang isinya :



from report import report_sxw
import tools
import xlwt
import cStringIO
import time
import datetime
import pooler

class report_xls(report_sxw.report_sxw):

    xls_types = {
        'bool': xlwt.Row.set_cell_boolean,
        'date': xlwt.Row.set_cell_date,
        'text': xlwt.Row.set_cell_text,
        'number': xlwt.Row.set_cell_number,
    }
    xls_types_default = {
        'bool': False,
        'date': None,
        'text': '',
        'number': 0,
    }

    def create(self, cr, uid, ids, data, context=None):
        pool = pooler.get_pool(cr.dbname)
        ir_obj = pool.get('ir.actions.report.xml')
        report_xml_ids = ir_obj.search(cr, uid,
                [('report_name', '=', self.name[7:])], context=context)
        if report_xml_ids:
            report_xml = ir_obj.browse(cr, uid, report_xml_ids[0], context=context)
        else:
            title = ''
            rml = tools.file_open(self.tmpl, subdir=None).read()
            report_type= data.get('report_type', 'pdf')
            class a(object):
                def __init__(self, *args, **argv):
                    for key,arg in argv.items():
                        setattr(self, key, arg)
            report_xml = a(title=title, report_type=report_type, report_rml_content=rml, name=title, attachment=False, header=self.header)
        report_type = report_xml.report_type
        ## ajm override :: begin
        report_type = 'xls'
        ## ajm override :: end
        if report_type in ['sxw','odt']:
            fnct = self.create_source_odt
        elif report_type in ['pdf','raw','html']:
            fnct = self.create_source_pdf
        elif report_type=='html2html':
            fnct = self.create_source_html2html
        ## ajm override :: begin
        elif report_type == 'xls':
            fnct = self.create_source_xls
        ## ajm override :: end
        else:
            raise 'Unknown Report Type'
        fnct_ret = fnct(cr, uid, ids, data, report_xml, context)
        if not fnct_ret:
            return (False,False)
        return fnct_ret

    def create_source_xls(self, cr, uid, ids, data, report_xml, context=None):
        print("START: "+time.strftime("%Y-%m-%d %H:%M:%S"))

        if not context:
            context = {}
        context = context.copy()
        rml_parser = self.parser(cr, uid, self.name2, context=context)
        objs = self.getObjects(cr, uid, ids, context=context)
        rml_parser.set_context(objs, data, ids, 'xls')

        n = cStringIO.StringIO()
        wb = xlwt.Workbook(encoding='utf-8')
        for i, a in enumerate(rml_parser.localcontext['objects']):
            self.generate_xls_report(rml_parser, data, a, wb)
        wb.save(n)
        n.seek(0)

        print("END: "+time.strftime("%Y-%m-%d %H:%M:%S"))

        return (n.read(), 'xls')

    def generate_xls_report(self, parser, data, obj, wb):
        raise NotImplementedError()

    def dt_to_datetime(self, date_str):
        return datetime.datetime.fromtimestamp(time.mktime(time.strptime(date_str, '%Y-%m-%d %H:%M:%S')))

    def d_to_datetime(self, date_str):
        return datetime.datetime.fromtimestamp(time.mktime(time.strptime(date_str, '%Y-%m-%d')))

    def xls_row_template(self, specs, wanted_list):
        """
        Return a row template, each column contains:
        0: Column Name
        1: Column Colspan
        2: Column Size
        3: Column Type (from report_xls.xls_types)
        4: Column data_get_function(x, d, p)
        5: Column write_cell_func
        6: Column Style
        """
        r = []
        col = 0
        for w in wanted_list:
            found = False
            for s in specs:
                if s[0] == w:
                    found = True
                    c = list(s[:])
                    c.append(report_xls.xls_types[c[3]])

                    # Set custom cell style
                    if len(s) > 5 and s[5] is not None:
                        c.append(s[5])
                    else:
                        c.append(None)

                    r.append((col, c[1], c))
                    col += c[1]
                    break
            if not found:
                print("column '%s' not found in specs" % (w))
        return r
    def xls_write_row(self, ws, x, d, p, row_count, row_template, row_style):
        r = ws.row(row_count)
        for col, size, spec in row_template:
            data = spec[4](x, d, p)
            style = spec[6] and spec[6] or row_style
            if not data:
                # if no data, use default values
                data = report_xls.xls_types_default[spec[3]]
            if size != 1:
                ws.write_merge(row_count, row_count,
                               col, col+size-1,
                               data, style)
            else:
                spec[5](r, col, data, style)

    def xls_write_row_header(self, ws, row_count, row_template, row_style=None, set_column_size=False):
        r = ws.row(row_count)
        for col, size, spec in row_template:
            data = spec[0]
            if size != 1:
                ws.write_merge(row_count, row_count,
                               col, col+size-1,
                               data, row_style)
            else:
                r.set_cell_text(col, data, row_style)
            if set_column_size:
                ws.col(col).width = spec[2] * 54

Pastikan folder report tersebut ikut di import pada file __init__.py di awal modul.
Alhamdulillah akhirnya telah selesai tutorial pembuatan report excel di OpenERP, selamat mencoba dan semoga bermanfaat …

Advertisements

27 thoughts on “Excel Report OpenERP

  1. 2014-01-03 07:31:49,000 2862 ERROR ? openerp.service.web_services: Exception: File not found: addons/bs_pl_export_to_excel/report/report_excel.mako
    Traceback (most recent call last):
    File “/home/openerp/server/7.0/openerp/service/web_services.py”, line 700, in go
    (result, format) = obj.create(cr, uid, ids, datas, context)
    File “/home/openerp/addons/7.0/bs_pl_export_to excel/report/report_engine_xls.py”, line 33, in create
    rml = tools.file_open(self.tmpl, subdir=None).read()
    File “/home/openerp/server/7.0/openerp/tools/misc.py”, line 172, in file_open
    return _fileopen(name, mode=mode, basedir=rtp, pathinfo=pathinfo, basename=basename)
    File “/home/openerp/server/7.0/openerp/tools/misc.py”, line 219, in _fileopen
    raise IOError(‘File not found: %s’ % basename)
    IOError: File not found: addons/bs_pl_export_to_excel/report/report_excel.mako
    2014-01-03 07:31:49,214 2862 ERROR None openerp.netsvc: File not found: addons/bs_pl_export_to_excel/report/report_excel.mako
    (, IOError(‘File not found: addons/bs_pl_export_to_excel/report/report_excel.mako’,), )
    Traceback (most recent call last):
    File “/home/openerp/server/7.0/openerp/netsvc.py”, line 289, in dispatch_rpc
    result = ExportService.getService(service_name).dispatch(method, params)
    File “/home/openerp/server/7.0/openerp/service/web_services.py”, line 642, in dispatch
    res = fn(db, uid, *params)
    File “/home/openerp/server/7.0/openerp/service/web_services.py”, line 748, in exp_report_get
    return self._check_report(report_id)
    File “/home/openerp/server/7.0/openerp/service/web_services.py”, line 726, in _check_report
    netsvc.abort_response(exc, exc.message, ‘warning’, exc.traceback)
    File “/home/openerp/server/7.0/openerp/netsvc.py”, line 72, in abort_response
    raise openerp.osv.osv.except_osv(description, details)
    except_osv: (u’File not found: addons/bs_pl_export_to_excel/report/report_excel.mako’, (, IOError(‘File not found: addons/bs_pl_export_to_excel/report/report_excel.mako’,), ))
    2014-01-03 07:31:49,250 2862 INFO None werkzeug: 127.0.0.1 – – [03/Jan/2014 07:31:49] “POST /web/report HTTP/1.1” 500 –

    • maaf om, lupa keterangannya,, kenapa dia gak nemu directory: addons/bs_pl_export_to_excel/report/report_excel.mako, padahal filenya tersebut ada dan sesuai dengan alamatnya, mohon bantuanya om…

  2. Om, gimana ya caranya Kalau kita ingin mengkalikan antar cell di reportnya.
    misalnya ada 2 cell yang di ambil datanya dari database untuk di export, tapi ketika di eksport ke excel ada cell lain hasil perkalian dari ke 2 cell tsb?

    mohon pencerahanya om,

    • Untuk contoh kasus diatas (Balance Sheet) jika kita tambahkan 1 kolom lagi, maka hasilnya seperti ini :

      
              for x in data['csv']:
                  ws.write(row_count, 0, x[0])
                  ws.write(row_count, 1, x[1])
                  ws.write(row_count, 2, x[2])
                  ws.write(row_count, 3, x[1]*x[2]) # ===> Kolom baru yang nilainya merupakan hasil dari perkalian kolom 2 dan kolom 3
                  row_count += 1
      
      

      Code diatas hanya sebagai contoh jika nilai yang diinginkan pada kolom yang baru sudah ada pada kolom lain yang kita ‘parsing’ dari pythonnya. Jangan lupa ditambahkan kolom pada variable cols_specs dan title …

      • saya sudah coba seperti itu om pada balance sheet, tapi kan masalahnya di array yang di kalkulasi itu ada di array ke-2, kalau array yang ke-1 itu kan nama accountnya om. jadi saya mau kalikan looping pertama array ke-2 dengan looping ke-2 array ke-2, begitu om ..

      • Betul mas itu kolom pertama string. Maksud saya itu hanya sebagai contoh saja, jika mas praktekan pasti akan error (karna string dikalikan integer). Untuk solusi permasalahan mas, mungkin caranya seperti ini :

        ws.write(row_count, 3, x[2]*(data['csv'][data['csv'].index(x)+1][2])
        
  3. thanks om, berhasil..

    Cuman ada satu kendala ketika saya print dengan jumlah data banyak, saya coba di 13 ribuan data invoice saja, loadingnya lama banget, berbeda dengan yang bawaanya (pdf) yang loadingnya sebentar. Kenapa om ya??

  4. Saya Sdh coba searching gan, cuman belum nemu logicnya, karena yang sekarang dia kan untuk mendapatkan nilai BS PL dia search langsung ke account move line yang datanya puluhan ribu itu, bisa minta logic / algoritma nya aja ga gan, biar ane explore sendiri codingnya πŸ™‚

  5. Om, mau nanya.. apakah ada cara untuk menghapus/menyembunyikan menu Export yang aslinya dari OpenERP (yang di bawah menu More saat memilih data)?

    Lalu, jika menggunakan menu Export tersebut, saat mengeksport field number, hasil di Excel nya malah berupa string sehingga tidak bisa langsung di-sum.. Apakah ada cara agar hasilnya tetap number?

    Terima kasih banyak.. web ini memberi banyak sekali pencerahan πŸ™‚

    • Terima kasih telah mengunjungi blog ini, semoga bermanfaat….

      Untuk tombol export pada button more, saya belum dapat menemukannya. Saya sudah coba buka data product, partner, sales order tetapi tombol export blum kunjung ‘hadir’. Bagaimana cara melihatnya ? πŸ™‚

      Untuk merubah string menjadi number mungkin bisa langsung pada excelnya di menu ‘Format Cell’, jika ingin otomatis dari data exportnya bisa langsung dirubah codingnya (ex: int(string)) pada modul bersangkutan ….

      • Halo lagi πŸ™‚ Terima kasih untuk reply nya yang cepat.
        Saya tidak tahu bagaimana cara memunculkan menu export itu, karena saat saya install sudah langsung muncul menunya. Menu tersebut hanya muncul pada tampilan tree dan saat men-checklist 1 atau lebih data. Persis seperti yang diterangkan di sini http://www.serpentcs.com/serpentcs-openerp7-export-import-configuration

        Sedangkan masalah yang saya utarakan persis seperti yang dijelaskan di sini https://bugs.launchpad.net/openerp-web/+bug/1001668

        Saya sudah berusaha menggunakan cara di launchpad tersebut tapi tidak membuahkan hasil 😦 semoga Om dapat memberi pencerahan πŸ™‚

      • hoh ternyata cara di bugs itu bisa diterapkan, sekarang numbernya berlaku seperti number seharusnya.. maaf sudah merepotkan Om πŸ™‚ Terus post guide-guide tentang OpenERP ya Om, sungguh membantu kami-kami para pemula.. karena dokumentasinya sangat kurang 😦 Good luck!

  6. Pak, Mau nanya nich,

    Misalkan pada . openERP invoice-nya menggunakan beberapa currency ( USD, EURO, dll) sedangkan Company currency nya menggunakan IDR.
    nah, secara default, pada partner balance dan aged partner balance , balance-nya akan otomatis di kalkulasikan berdasarkan company currency (IDR).

    yang saya tanyakan, bagaimana cara menampilkan currency asli (amount currency) pada partner balance dan aged partner balance.

    Mohon bimbingannya.

    Terima Kasih

    • Betul mba, semua transaksi mata uang asing pada saat penjurnalan akan dikonversi otomatis sesuai dengan rate yang berlaku pada tanggal tersebut.

      Secara default memang hanya menampilkan base currency system aja. Untuk menampilkannya memang harus customize dengan menambahkan field amount_currency yang diambil dari jurnal items (account.move.line) …

      • Misal kita ingin tambah fieldnya di report “Partner Balance”. Pertama yaitu kita telusuri file reportnya, caranya masuk ke menu report “Partner Balance” lalu gunakan tampilan developer mode dan cek ada di object apa & nama methodnya apa untuk memanggil report tersebut ? setelah ketemu objectnya yaitu objectnya “account.partner.balance”

        Kedua, kita gunakan bantuan IDE Eclipse untuk mengexplore modulnya. Gunakan fitur search (Ctrl + H). Setelah ketemu ternyata ada di modul “account” tepatnya “account/wizard/account_report_partner_balance.py”. Trus telusuri sampai ketemu file reportnya yaitu “account_partner_balance.rml”

        Ketiga, ketika kita sudah terbiasa membuat report dengan RML maka untuk menambahkan field original currency yang aslinya bernama “amount_currency” maka hal ini tidak akan sulit. Tambahkan kolom untuk tampilan reportnya pada file rml dan untuk datanya dapat mengoverriide file pythonnya. Silahkan untuk mengcustomize sesuai dengan yang diinginkan …

  7. Pingback: Technical Training -Part 10- | Tutorial OpenERP

  8. bagaimana cara menggunakannya mas azzis,, saya binggung dibuat dengan folder apa dan nama filenya .py nya apa?? jika boleh saya minta modulnya ke email saya….??

  9. mas, waktu tekan tombol “Print” muncul seperti ini kenapa ya?
    Required report does not exist: balance.sheet.excel

    ,Required report does not exist: balance.sheet.excel,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s