Web2py and xlsxwriter

[Stefan van den Eertwegh]

Hi,

I am writing for my invoice app a excel (xlsx) function to export a view of al my invoices to excel.

Does anyone has some experience with xlsxwriter? Or some tips or alternatives?

Or maybe you advise me to use xlwt from python-excel.org?

I am having trouble with the translatable string that i use in the excel function.

It seems it returns the lazyT object in the string but xlsxwriter fails on this.

When i set T.lazy = False it has no change with the error..

Does anyone knows the problem?

I have written the following code:

# -*- coding: utf-8 -*-

@auth.requires_login()

def invoice():

    import xlsxwriter

    # Create a workbook and add a worksheet.

    workbook = xlsxwriter.Workbook('Expenses03.xlsx')

    worksheet = workbook.add_worksheet()

    # Add a bold format to use to highlight cells.

    bold = workbook.add_format({'bold': 1})

    eurosign = u"€"

    # Add a number format for cells with money.

    money_format = workbook.add_format({'num_format': eurosign + '#,##0'})

    # Add an Excel date format.

    date_format = workbook.add_format({'num_format': 'd mmmm yyyy'})

    # Adjust the column width.

    worksheet.set_column(1, 1, 30)

    # Write some data headers.

    worksheet.write('A1', T('Invoice no.'), bold)

    worksheet.write('B1', T('Debtor'), bold)

    worksheet.write('C1', T('Invoice date'), bold)

    worksheet.write('D1', T('Amount incl. VAT'), bold)

    worksheet.write('E1', T('Type'), bold)

    # Some data we want to write to the worksheet.

    data = []

    rows = db(db.invoice.company == u.company).select()

    for record in rows:

        if not record.debtor.company_name:

            prefix = ' ' + record.debtor.prefix if record.debtor.prefix else ''

            debtor = record.debtor.first_name + prefix + ' ' + record.debtor.last_name

        else:

            debtor = record.debtor.company_name

        data.append([record.no, debtor, record.date, record.amount_incl_VAT, record.type])

    # Start from the first cell below the headers.

    row = 1

    col = 0

    for no, debtor, date, cost, rtype in data:

        worksheet.write_string  (row, col,     no                )

        worksheet.write_string  (row, col + 1, debtor            )

        worksheet.write_datetime(row, col + 2, date, date_format )

        worksheet.write_number  (row, col + 3, int(cost), money_format)

        worksheet.write_string  (row, col + 4, rtype              )

        row += 1

    # # Write a total using a formula.

    worksheet.write(row, 0, T('Total'), bold)

    worksheet.write(row, 3, '=SUM(C2:C' + str(len(data)+1) + ')', money_format)

    workbook.close()

[Massimo Di Pierro]

If you have reportlab installed you can make them directly in PDF:

from reportlab.pdfgen.canvas import Canvas

from reportlab.platypus import Table

from reportlab.lib.pagesizes import A4

from reportlab.lib.units import cm

import cStringIO

class PDF(object):

    def __init__(self, page_size=A4, font_face='Helvetica'):

        self.page_size = page_size

        self.font_face = font_face

        self.logo = None

    def format_currency(self,value):

        a = list(str(int(value)))

        for k in range(len(a)-3,0,-3):

            a.insert(k,',')

        a = ''.join(a)

        b = ("%.2f" % (value-int(value)))[2:]

        return "%s.%s" % (a,b)

    def draw(self, invoice, items_page=10):

        """ Draws the invoice """

        buffer = cStringIO.StringIO()

        pages = max((len(invoice['items'])-2)/items_page+1,1)

        canvas = Canvas(buffer, pagesize=self.page_size)

        for page in range(pages):

            canvas.translate(0, 29.7 * cm)

            canvas.setFont(self.font_face, 10)

            canvas.saveState()

            canvas.setStrokeColorRGB(0.9, 0.5, 0.2)

            canvas.setFillColorRGB(0.2, 0.2, 0.2)

            canvas.setFont(self.font_face, 16)

            canvas.drawString(18 * cm, -1 * cm, invoice['title'])

            if self.logo:

                canvas.drawInlineImage(self.logo, 1 * cm, -1 * cm, 250, 16)

            canvas.setLineWidth(4)

            canvas.line(0, -1.25 * cm, 21.7 * cm, -1.25 * cm)

            canvas.restoreState()

            

            canvas.saveState()

            notes = invoice.get('notes',[])

            textobject = canvas.beginText(1 * cm, -23 * cm)

            for line in notes:

                textobject.textLine(line)

            canvas.drawText(textobject)

            textobject = canvas.beginText(18 * cm, -28 * cm)

            textobject.textLine('Pag.%s/%s' % (page+1,pages))

            canvas.drawText(textobject)

            canvas.restoreState()

        

            canvas.saveState()

            business_details = invoice['business']

            canvas.setFont(self.font_face, 9)

            textobject = canvas.beginText(13 * cm, -2.5 * cm)

            for line in business_details:

                textobject.textLine(line)

            canvas.drawText(textobject)

            canvas.restoreState()

        

            textobject = canvas.beginText(1.5 * cm, -2.5 * cm)

            for line in invoice['client_address']:

                textobject.textLine(line)

            canvas.drawText(textobject)

            textobject = canvas.beginText(1.5 * cm, -6.75 * cm)

            textobject.textLine(u'Invoice ID: %s' % invoice['id'])

            textobject.textLine(u'Invoice Date: %s' % invoice['date'])

            textobject.textLine(u'Client: %s' % invoice['client_name'])

            canvas.drawText(textobject)

            items = invoice['items'][1:][page*items_page:(page+1)*items_page]

            if items:

                data = [invoice['items'][0]]

                for item in items:

                    data.append([

                            self.format_currency(x) 

                            if isinstance(x,float) else x

                            for x in item])

                righta = [k for k,v in enumerate(items[0])

                          if isinstance(v,(int,float))]

                if page == pages-1:

                    total = self.format_currency(invoice['total'])

                else:

                    total = ''

                data.append(['']*(len(items[0])-1)+[total])

                colWidths = [2.5*cm]*len(items[0])

                colWidths[1] = (21.5-2.5*len(items[0]))*cm

                table = Table(data, colWidths=colWidths)

                table.setStyle([

                        ('FONT', (0, 0), (-1, -1), self.font_face),

                        ('FONTSIZE', (0, 0), (-1, -1), 8),

                        ('TEXTCOLOR', (0, 0), (-1, -1), (0.2, 0.2, 0.2)),

                        ('GRID', (0, 0), (-1, -2), 1, (0.7, 0.7, 0.7)),

                        ('GRID', (-1, -1), (-1, -1), 1, (0.7, 0.7, 0.7)),

                        ('BACKGROUND', (0, 0), (-1, 0), (0.8, 0.8, 0.8)),

                        ]+[('ALIGN',(k,0),(k,-1),'RIGHT') for k in righta])

                tw, th, = table.wrapOn(canvas, 15 * cm, 19 * cm)

                table.drawOn(canvas, 1 * cm, -8 * cm - th)

            if page == pages-1:

                items = invoice['totals'][1:]

                if items:

                    data = [invoice['totals'][0]]

                    for item in items:

                        data.append([

                                self.format_currency(x) 

                                if isinstance(x,float) else x

                                for x in item])

                    righta = [k for k,v in enumerate(items[0]) 

                              if isinstance(v,(int,float))]

                    total = self.format_currency(invoice['total'])

                    data.append(['']*(len(items[0])-1)+[total])

                    colWidths = [2.5*cm]*len(items[0])

                    colWidths[1] = (21.5-2.5*len(items[0]))*cm

                    table = Table(data, colWidths=colWidths)

                    table.setStyle([

                            ('FONT', (0, 0), (-1, -1), self.font_face),

                            ('FONTSIZE', (0, 0), (-1, -1), 8),

                            ('TEXTCOLOR', (0, 0), (-1, -1), (0.2, 0.2, 0.2)),

                            ('GRID', (0, 0), (-1, -2), 1, (0.7, 0.7, 0.7)),

                            ('GRID', (-1, -1), (-1, -1), 1, (0.7, 0.7, 0.7)),

                            ('BACKGROUND', (0, 0), (-1, 0), (0.8, 0.8, 0.8)),

                            ]+[('ALIGN',(k,0),(k,-1),'RIGHT') for k in righta])

                    tw, th, = table.wrapOn(canvas, 15 * cm, 19 * cm)

                    table.drawOn(canvas, 1 * cm, -16 * cm - th)

            canvas.showPage()

            canvas.save()

        return buffer.getvalue()

if __name__=='__main__':

    invoice = {

        'title':          'Invoice',

        'id':             '00001',

        'date':           '10/10/2013',

        'business':       ['FROM:', 'Mr Accountant','1st Street'],

        'client_name':    'Mr Blue',

        'client_address': ['TO:','Blue','2nd Street'],

        'notes':          ['no comment!'],

        'total':          2000.00,

        'items': [

            ['Code','Descr','Quantity','Unit price','Total']]+[

            ['000001','Chair',k,20.0,80.0] for k in range(30)],

        'totals': [

            ['Code','Descr','Totale']]+[

            ['000001','Test',80.0] for k in range(5)],

        }

    print PDF().draw(invoice)

[Stefan van den Eertwegh]

Hi Massimo,

No thank you, i use weasyprint for making PDFs, which is very easy with self made html views.

This topic goes about using web2py to make excel sheets.

Maybe you have some ideas Massimo?

Thank you!

Op zondag 26 oktober 2014 16:34:58 UTC+1 schreef Stefan van den Eertwegh:

[Niphlod]

I think your problems comes from the fact that any external library doesn't know how to handle T objects.
You should use str(T('something')) instead to convert T instances to strings, that will be digested by external libs pretty easily.