Spreadsheets: How to excel with Db2 data

Generated chart in Excel file

Recently, I had to produce a spreadsheet from database data. One naive way is to export the data to a CSV file, then import the data from that file into the spreadsheet. Another option was to quickly script a small Python program that fetches the data and directly generates a Microsoft Excel file. As a bonus, I added even a line chart (as shown).

Reading and writing Excel file

There exist code libraries for many programming languages to read and write files in the Microsoft Excel format, usually in the XML-based "xlsx" format. For Python, a popular library is openpyxl. The documentation let's you get started with simple code samples. A spreadsheet consists of the workbook (consider it the file), one or more worksheets (where the tables and charts are), and some other elements, e.g., for styling. You can create a spreadsheet file with literally 10 lines of code.

Fetching data from Db2

To interface with Db2, I opted for SQLAlchemy and its core engine. I could have used one of the other Python drivers for Db2, but wanted to keep it as database-agnostic as possible.

After creating the SQLAlchemy engine and establishing a database connection, I executed the SQL SELECT statement, fetched the result set and added the rows to my data set for the spreadsheet.

result = conn.execute(text(fetchStmt),{"ID":email})

for row in result:

values.append([row[1].isoformat(),row[2], row[0]])

Creating the spreadsheet and line chart

With openpyxl and a workbook and worksheet created, I added the rows from my result set.

for row in dataset[0]['data']:

ws.append(row)

Thereafter, I followed the code sample for a line chart with date axis. It configures a line chart, points to the added data for input, and adds the chart to the worksheet.

Then, I am done 😀:

wb.save("line.xlsx")

Conclusions

Instead of exporting data from the database, then importing it into a spreadsheet, why not directly generate the spreadsheet and even add a chart? It can be easily done using one of the many existing code libraries.

 If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik), Mastodon (@data_henrik@mastodon.social), or LinkedIn.