Convert XLSX to PDF with Python and xtopdf

By Vasudev Ram


XLSX => PDF

This is a simple application of my xtopdf toolkit, showing how to use it to convert XLSX data, i.e. Microsoft Excel data, to PDF (Portable Document Format). It only converts text data, not the formatting, colors, fonts, etc., that may be present in the Excel file.

For the input, I will use this small Excel file, fruits2.xlsx, which I created. A screenshot of it is below (click to enlarge):

Here is the code for XLSXtoPDF.py:

# XLSXtoPDF.py

# Program to convert the data from an XLSX file to PDF.
# Uses the openpyxl library and xtopdf.

# Author: Vasudev Ram - http://jugad2.blogspot.com
# Copyright 2015 Vasudev Ram.

from openpyxl import load_workbook
from PDFWriter import PDFWriter

workbook = load_workbook('fruits2.xlsx', guess_types=True, data_only=True)
worksheet = workbook.active

pw = PDFWriter('fruits2.pdf')
pw.setFont('Courier', 12)
pw.setHeader('XLSXtoPDF.py - convert XLSX data to PDF')
pw.setFooter('Generated using openpyxl and xtopdf')

ws_range = worksheet.iter_rows('A1:H13')
for row in ws_range:
    s = ''
    for cell in row:
        if cell.value is None:
            s += ' ' * 11
        else:
            s += str(cell.value).rjust(10) + ' '
    pw.writeLine(s)
pw.savePage()
pw.close()

And here is a screenshot of the PDF output in fruits2.pdf:

There are some points worth mentioning in connection with conversion of data to and from PDF. I will discuss them in a follow-up post.

- Vasudev Ram - Online Python training and programming

Signup to hear about new products and services I create.

Posts about Python  Posts about xtopdf

My ActiveState recipes

Share |

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:

"""
xlwingsToPDF.py
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 - http://www.dancingbison.com
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: http://xlwings.org --- xtopdf: http://slid.es/vasudevram/xtopdf")

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

pw.close()

I ran it with this command:

py xlwingsToPDF.py

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

Share |

Errata for recent post ""Publish Microsoft Excel XLSX data to HTML with openpyxl"

By Vasudev Ram

Dear readers, while publishing my recent post,

Publish Microsoft Excel XLSX data to HTML with openpyxl, there were some errors in the HTML markup in the code listing, that had to do with missing or wrongly typed HTML entities, HTML elements, or quotes.

My apologies for the inconvenience caused.

I've now posted the corrected code below:

# XLSXtoHTML.py

# Program to convert the data from an XLSX file to HTML.
# Uses the openpyxl library.

# Author: Vasudev Ram - http://www.dancingbison.com

import openpyxl
from openpyxl import load_workbook

workbook = load_workbook('fruits.xlsx')
worksheet = workbook.get_active_sheet()

html_data = """
<html>
    <head>
        <title>
        XLSX to HTML demo
        </title>
    </head>
    <body>
        <h3>
        XLSX to HTML demo
        </h3>
        <table>
"""

ws_range = worksheet.range('A1:H13')
for row in ws_range:
    html_data += "<tr>"
    for cell in row:
        if cell.value is None:
            html_data += "<td>" + ' ' + "</td>"
        else:
            html_data += "<td>" + str(cell.value) + "</td>"
    html_data += "</tr>"
html_data += "</table></body></html>"

with open("fruits.html", "w") as html_fil:
    html_fil.write(html_data)

# EOF

- Vasudev Ram - Dancing Bison Enterprises

Share |

Publish Microsoft Excel XLSX data to HTML with openpyxl

By Vasudev Ram

I had come across openpyxl, a library by Eric Gazoni, for reading and writing Microsoft Excel XLSX files (Open Office XML), a while ago.

So today I wrote a demo program that reads the data from an XLSX file using openpyxl and writes that data to HTML as a table. Here is a screenshot of the sample XLSX file used, fruits.xlsx (click image to enlarge):

Here is the program, XLSXtoHTMLdemo.py:

# XLSXtoHTMLdemo.py

# Program to convert the data from an XLSX file to HTML.
# Uses the openpyxl library.

# Author: Vasudev Ram - http://www.dancingbison.com

import openpyxl
from openpyxl import load_workbook

workbook = load_workbook('fruits.xlsx')
worksheet = workbook.get_active_sheet()

html_data = """
<html>
    <head>
        <title>
        XLSX to HTML demo
        <title>
    <head>
    <body>
        <h3>
        XLSX to HTML demo
        <h3>
    <table>
"""

ws_range = worksheet.range('A1:H13')
for row in ws_range:
    html_data += "<tr>
    for cell in row:
        if cell.value is None:
            html_data += "<td> + ' ' + "<td>
        else:
            html_data += "<td> + str(cell.value) + "<td>
    html_data += "<tr>
html_data += "<table>lt;body>lt;html>

with open("fruits.html", "w") as html_fil:
    html_fil.write(html_data)

# EOF

You can run the program with:

python XLSXtoHTMLdemo.py

Then the program's HTML output will be in the file fruits.html, a screenshot of which is below (click to enlarge):

- Enjoy.

- Vasudev Ram - Python, C, Linux, databases, open source - training and consulting.

Read all Python posts on my blog.

Share |

3 ways to use Python in Excel

By Vasudev Ram


DataNitro seems powerful. It can be used interactively in Excel, or by writing scripts, including defining functions.

Here is an example of interactive use, from their web site:

>>> from ystockquote import get_price
>>> Cell('A1').value= get_price('GOOG')
>>> Cell('A1').value
642.35

Though they don't mention it on the home page, I think this example embedded formula's result probably will be updated when the spreadsheet is recalculated. Otherwise it would not be too useful.

The ystockquote module used in the example is by Corey Goldberg, a Python developer. It allows you to get stock quotes from Yahoo!. Corey's blog has good Python information and examples. He has also written many open source software tools, in Python and other languages.

Pyvot is a Python tool for Visual Studio. Excerpts:

[ Pyvot enables easy transit of data between Python and Excel.

For example, we can move a list of Python values to Excel, view and manipulate the data, and retrieve the new version. In a symmetric and equally common usage, we can grab and process Excel values in Python, and display the result to Excel.
...
It requires CPython 2.6 or 2.7 with the Python for Windows extensions (pywin32) installed, and Office 2010. ]

Pyvot uses Microsoft COM (Component Object Model) to communicate with Excel. One issue many people (including me) have found with COM, is that it is a) somewhat buggy, and b) is resource-heavy. At least, that was the case when I used it in a project a while ago.

pyxll (Python Excel Addins) makes it possible to write addins for Microsoft Excel in Python. Excerpts:

[ PyXLL makes it possible to write addins for Microsoft Excel in Python. Using simple decorators your Python code can instantly be exposed to Excel as worksheet functions, menu items or macros.
...
Excel addins written using PyXLL are fast to develop and offer high performance as well as being easier to maintain and deploy than other methods of developing Excel addins.
...
PyXLL is used by investment banks, hedge funds and engineering companies all over the world. ]

Finally, if all you need to do is to read the contents of Excel worksheets programmatically, and then process the contents in some way, you may want to check out my xtopdf toolkit, which provides some minimal support for that. This article by me on the Packt Publishing site, shows how to use xtopdf to read the basic contents of Excel files; it can read numbers and text only; it does not support reading font information, formatting, colors, etc. To achieve this, xtopdf uses the xlrd library for Python, so that is a prerequisite.

The steps for setting up xtopdf for reading Excel files, is given at the end of that article, in the section named "4. Conclusion." Note: I had used what was probably an earlier version of xlrd to develop this feature, since I did it some time ago, but after taking a look at the PyPI page for xlrd (linked above) just now, it looks as though things should still work.

An interesting point about xlrd is that is written in pure Python, so you don't need to be on Windows to use it. You can use it to process Excel files on any other platform (such as Linux or UNIX) that supports Python, which may be more convenient for some needs, since Linux and UNIX have powerful software development tools. One obvious way to leverage this is to read the Excel content using xlrd and convert it to tab-delimited values, then process it with AWK.

- Vasudev Ram - Dancing Bison Enterprises

Share |