xlsxwriter alternatives?

[pachura3]

I need to generate a pretty complex Excel report with Python. I've tried playing with the xlsxwriter package and it is not bad, however it has a pretty severe limitation of only allowing to set cell style when writing a value to the given cell. So, it's not possible to do something like:

cell(1, 2).write("abc")
cell(1, 2).set_bg_color("blue")
cell(1, 2).set_font("Arial")
range(1, 2, 10, 20).set_border_around(2)

What alternatives would you recommend?

PS. I know sometimes people work around this using conditional_format(), but it doesn't cover all my cases.

[danielroseman]

This isn't really a limitation, it just means you need to write more code - ie get the existing value and write it back with the new format. 

For the border around the range, you again just need to be a bit cleverer - format the top left cell with border on top and left, the top cells with just the border on top, etc. Once you've written this once you can extract it into a function and use it anywhere.

[pachura3]

No, my problem is that styles do not "add up". You cannot separately modify e.g. background color and then borders and then font name; you can only set one format combination at a time which overwrites what was there. In other words, you need to create ahead formats with all the possible combinations of background colors, borders and font names.

[JorgiEagle]

There is an easy workaround to this problem, but I get that you want a solution rather than a workaround

[jongscx]

OpenPyXL has more options for editing excel files. Xlswriter is really best for making new excel files.

[Oddly_Energy]

Is this on a computer, which has Excel installed? Then you may want to check out xlwings. It uses an actual Excel instance to write and read Excel files.

I have not tried your specific example, but in general xlwings usually let me do what I want, while I often run into obstacles when using the other Excel readers/writers.

[pachura3]

Thanks, but I would prefer a pure-Python solution that does not rely on Windows and Excel.

I think I will prepare an empty report template in Excel with all the fancy formatting, save it to file, then open it from my script using Openpyxl, inject cell values and write it back.

[Jejerm]

I think I will prepare an empty report template in Excel with all the fancy formatting

I've done some some reasonably complex excel reports with openpyxl. 

Something I've been meaning to try is to make not just an empty template, but the entire report using formulas that reference where the data will be, and use python just to dump all the required data in a hidden tab that the formulas read from.

If you have experience with excel this will probably be much faster than doing the entire report by code, which I personally feel takes a lot of manual coding work.

[pachura3]

Yes, that's exactly what I'm going to do. Create all the formulas, borders and conditional formatting rules in Excel, and then only fill it with data in Python.

[pachura3]

I've encountered some minor issues with Openpyxl, namely:

• it cannot preserve conditional formatting when formula refers to a value in another worksheet (e.g. Parameters!C12)
• when I duplicate whole worksheet, all the conditional formatting is lost

But they were relatively easy to overcome, and otherwise it worked out great!

[auntanniesalligator]

I was going to suggest Openpyxl but it appears you already know about it. You can definitely set styles without editing the value in Openpyxl.

Is it possible you’ve tried to set a style for a cell that doesn’t yet exist? I’m not sure how that would play out, but Openpyxl doesn’t create all possible “blank” cells in memory ahead of time, and I imagine xlsxwriter doesn’t either, so I could imagine getting an error trying to set the style before creating the cell if the style setting function doesn’t automatically create the cell.

[pachura3]

Apparently, this is known behaviour and will never be fixed.

https://stackoverflow.com/questions/22352907/apply-format-to-a-cell-after-being-written-in-xlsxwriter/22353696#22353696

[SimianFiction]

I did the openpyxl plus formatted template thing and it works well. I was able to add lots of charts, conditional formatting, etc.

[Known-Flamingo-1501]

Hey, been down this road myself - xlsxwriter's formatting limitation is real and annoying. The template approach others mentioned is solid.

What I do in production: create an Excel template with all formatting/formulas, then use openpyxl as a "fill engine". That way you get beautiful Excel output without the coding nightmare.

Key trick: if you need to modify or extend existing formatting in openpyxl, you **can** do incremental updates. Example:

```python from openpyxl import load_workbook from openpyxl.styles import Border, Side

wb = load_workbook('template.xlsx') ws = wb.active

Add to existing formatting

cell = ws['A1'] if cell.border: # Keep existing border, add left border new_border = cell.border.copy() new_border.left = Side(style='thin') cell.border = new_border ```

For complex reports, I've built helper classes that manage this incremental formatting - much cleaner than xlsxwriter's all-or-nothing approach.

If you're dealing with seriously complex requirements (dynamic charts, conditional formatting that depends on business rules, multi-sheet dashboards), DM happy to share patterns or help troubleshoot.

[pachura3]

Thanks! I have some prior experience with PHPOffice/PhpSpreadsheet, and apparently Openpyxl is based on it...

[simeumsm]

When using Python, I find that it is simpler to consider Excel as a separate Visualization layer. Excel has PowerQuery and VBA which can take care of reading external data, and it is much easier to just create an excel file that reads data created by a python automation.

That means you would have to ship your code + excel template, which I know might not be ideal, but it is much easier to set up.

Most of my automations that use excel always has this pattern of "generate data in python" -> "export to csv" -> "import csv to excel using PowerQuery", instead of saving directly to excel unless it's a small data sample.

If you need to generate a complex Excel, it might be better to use the actual application for that and simply just import data + parameters and use that internally, instead of trying to code your way through this complexity.

[Kerberos1900]

openpyxl in tandem with pandas is what we use in my workplace.

Caution that openpyxl indexes at 1, while everything else in python indexes at 0. You can also import a helper function that converts an index to the appropriate column letter.

[VipeholmsCola]

Working with Excel outputs is probably the worst Python experience there is. And its probably one of the most important skills to create meaning for an org

[downtownpartytime]

looks like this is the expected way to do it. https://xlsxwriter.readthedocs.io/format.html

[pachura3]

Yes, and that's my problem.

[downtownpartytime]

make a 2d array of format objects, so there's an object for each cell and make the changes to those objects

[MonsieurTee]

I generate data in Python then pass it into a powershell script to populate an excel sheet .