Everybody loves spreadsheets! As much as I am not fond of Microsoft, I should admit one good thing they have done long time ago and kept up delivering on is their MS Excel. My preferred operating system is Mac, yet I never use Numbers or any other MS Office alternatives. There are alternatives for MS Excel like Google spreadsheets, LibreOffice, etc. In my opinion, nothing is better than Excel. It has been kept so simple, while also maintaining it as very powerful software. Users from all levels of skillsets can take advantage of what Excel offers. I use spreadsheets all the time both working on spreadsheets manually and also programmatically. Automating excel spreadsheets is what I like the best. As usual I utilize python for automation scripts.
My main library for spreadsheet automation is Openpyxl. It is a very powerful framework just like Excel itself. It gives an ability to read, create, write, edit spreadsheets programatically. Over the years I have written many scripts that work with various spreadsheets and openpyxl is definitely the best library to use. Cool thing about python is there are always alternatives and additional tools we can use. For spreadsheet automation there are other modules available as well. Among them I decide to add to my toolkit next is XlsxWriter. There are very important difference between the two modules. I will still go to openpyxl as my first pick to anything spreadsheet related. However, there are thing openpyxl cannot do, but xlsxwriter does with ease. At the same time there are things xlsxwriter cannot do what openpyxl does like a champ.
I wouldn't even look at other alternative tools, if openpyxl could do one thing I needed for a project. Openpyxl is good with styling cells and values. What it absolutely cannot do is apply different styles to the section of the value within a cell. For example, let's say we have three lines of text in one cell and we want to make each of them different colors, sizes, fonts. Openpyxl cannot do this. But xlsxwrite can definitely perform these operations. This is reason why xlsxwriter caught my attention. Useful tools like this are always go too have in our toolbox.
I should emphasize that both are powerful tools and are awesome. But they serve different purposes and excel in different scenarios. While Openpyxl is designed to read, write, and modify Excel spreadsheets, XlsxWriter focuses exclusively on creating and writing Excel spreadsheet files. Openpyxl has functionality is broad and can do many things including formulas, charts, styles and even macros. XlsxWriter however, is best for creating richly formatted and visually appealing Excel files. While it cannot read excel files, and is write only, it is optimized for creating large Excel files quickly.
Let's take a look at an example code:
from openpyxl import Workbook
# Create a new workbook
wb = Workbook()
sheet = wb.active
# Add data
sheet['A1'] = 'Hello, Openpyxl!'
sheet['B1'] = 42
# Save the workbook
wb.save('example.xlsx')
Very easy, very simple, and gets job done.
import xlsxwriter
# Create a new workbook and add a worksheet
workbook = xlsxwriter.Workbook('example.xlsx')
worksheet = workbook.add_worksheet()
# Write data with formatting
worksheet.write('A1', 'Hello, XlsxWriter!')
worksheet.write('B1', 42)
# Add a chart
chart = workbook.add_chart({'type': 'line'})
chart.add_series({'values': '=Sheet1!$B$1:$B$1'})
worksheet.insert_chart('C1', chart)
workbook.close()
This isn't too complicated either. With very few lines of codes we get started working with spreadsheets programmatically. Isn't that awesome? While you can already see both take slightly different approach in how to create sheets and assign values to cells, both are self explanatory and easy to get started with. Xlxswriter has an excellent support for adding charts, images, and other visual elements.
If there is no need for rich formatting, and often there isn't. Let's say if we are dealing with large amount of spreadsheets and main focus is in the actual data, data manipulation, data analysis then openpyxl would be the right choice. However, if our project involves data visualization and presentation of data then better choice will be xlsxwriter. The beauty of accomplishing goals programmatically is that we can use all the tools that we have. We don't need to limit ourselves to one solution, but perhaps it is even better to utilize two or three more tools and each would be used in the areas they are best at.
I will continue using Openpyxl for most of my projects, because they involve both reading existing files and creating new ones. However, I will definitely start using XlsxWriter if there is a need for advanced formatting, charts, visuals, and efficiency. I already know what I will be using XlsxWriter for one of my existing projects that requires formatting sections of the cell values. I am sure I will find a lot more use for it too. If you use spreadsheets programatically, don't forget we have really good options when it comes to python libraries. Openpyxl and XlsxWriter are definitely the ones I would recommend to get familiar with. By understanding the strengths and limitations of each library, we can select the right tool for the projects we are working on.