Sunday, February 22, 2015

Excel to PDF with xlwings and xtopdf

By Vasudev Ram

Excel to PDF with xlwings and xtopdf - how many x in that? :)

I came across xlwings recently via the Net.

xlwings is by Zoomer Analytics, a startup based in Zürich, Switzerland, by a team with background in financial institutions.

Excerpt from the xlwings documentation:

[ xlwings is a BSD-licensed Python library that makes it easy to call Python from Excel and vice versa:

Interact with Excel from Python using a syntax that is close to VBA yet Pythonic.

Replace your VBA macros with Python code and still pass around your workbooks as easily as before.

xlwings fully supports NumPy arrays and Pandas DataFrames. It works with Microsoft Excel on Windows and Mac. ]

I checked out the xlwings quickstart.

Then did a quick test of using xlwings with xtopdf, my toolkit for PDF creation, to create a simple Excel spreadsheet, then read back its contents, and convert that to PDF.

Here is the code:
A demo program to show how to convert the text extracted from Excel 
content, using xlwings, to PDF. It uses the xlwings library, to create 
and read the Excel input, and the xtopdf library to write the PDF output.
Author: Vasudev Ram -
Copyright 2015 Vasudev Ram

import sys
from xlwings import Workbook, Sheet, Range, Chart
from PDFWriter import PDFWriter

# Create a connection with a new workbook.
wb = Workbook()

# Create the Excel data.
# Column 1.
Range('A1').value = 'Foo 1'
Range('A2').value = 'Foo 2'
Range('A3').value = 'Foo 3'
# Column 2.
Range('B1').value = 'Bar 1'
Range('B2').value = 'Bar 2'
Range('B3').value = 'Bar 3'

pw = PDFWriter("xlwingsTo.pdf")
pw.setFont("Courier", 10)
pw.setHeader("Testing Excel conversion to PDF with xlwings and xtopdf")
pw.setFooter("xlwings: --- xtopdf:")

for row in Range('A1..B3').value:
    s = ''
    for col in row:
        s += col + ' | '

I ran it with this command:
and here is a screenshot of the output PDF file:

Note: The xlwings library can be installed with:
pip install xlwings
But a prerequisite for it, pywin32, did not install automatically. pywin32 is a very useful and powerful Windows API wrapper library for Python, by Mark Hammond. I've used it a few times earlier, in earlier Python versions than Python 2.7.8, which I currently am using. I usually installed it directly in those earlier versions. This time, though it was a dependency for xlwings, it did not get installed automatically, and the above Python program gave a runtime error. I had to manually install pywin32 before the program could work.

- Enjoy.

- Vasudev Ram - Dancing Bison Enterprises

Signup to hear about new products or services from me.

Contact Page

No comments: