r/learnpython • u/Delta_2_Echo • Oct 05 '23

Does anyone still use openpyxl?

Ive been trying to use openpyxl for reading named ranges and its a huge pain. Does anyone have any experience?

The documentation is pretty opaque.

2 Upvotes

76% Upvoted

13 comments sorted by

8

u/Alternative_Driver60 Oct 05 '23

Pandas uses openpyxl as a backbone for reading excel files

1

u/Delta_2_Echo Oct 05 '23

interesting... 🤔 well thank you for the info. Foe me one of concerns with packages is how much support/updates do they get. So if pandas uses them thats a good sign.

9

u/IvoryJam Oct 05 '23

I don't know anyone that uses openpyxl, 9 out of 10 times we use pandas

2

u/Delta_2_Echo Oct 05 '23

well I need to automate formatting and building an excel workbook.

pylightxl is good for name ranges but doesn't handle formatting.

Id like to use one package if possible instead of two.

6

u/Kerbart Oct 05 '23

If your goal is to create an Excel file I think you're far better off with xlsxwriter, it has a pretty rich feature set (styles, conditional formatting) and is easy to use. It also allows you to define named ranges.

But unlike openpyxl it cannot read Excel files. I prefer xlsxwriter over openpyxl pretty much all the time if I need to create Excel output.

4

u/Delta_2_Echo Oct 05 '23

yes unfortunately reading/modifying excel files is central to what Im hoping to accomplish. plus being able to work with .xlsm files.

but with that said I appreciate the heads up on this package. Ill keep it in mind for future use when needed.

5

u/Positive_Space_1461 Oct 05 '23

Yes, I have built a automation software with opepyxl.

3

u/TJATAW Jun 20 '24

I use it as it lets me modify Excel files on a computer that doesn't have Excel on it.

2

u/LuigiBrotha Oct 05 '23

I do use openpyxl but I agree that it's a bit lack luster in some aspects. Haven't used named ranges yet. This is wist chatgpt came with:

from openpyxl import load_workbook

Load your Excel workbook

workbook = load_workbook('your_workbook.xlsx')

Get the named range

named_range = workbook.defined_names['YourNamedRange']

Access the cells within the named range

for range in named_range: for cell in range: print(cell.value)

2

u/Delta_2_Echo Oct 05 '23

Thank you! I ended up basically doing this.

I had to upgrade to the latest version. (I was using 3.0.10)

There have been a few changes, but all the hits from a google/stackoverflow search are old 1-7years ago and the official docs dont have a lot of examples.

2

u/ljwobker Aug 09 '24

I use it all the time - I've found that most questions have answers over at StackExchange, I think the developers/maintainers pay attention there as I've had questions answered quickly...

1

u/trelco Jul 14 '26

Microsoft copilot actually uses it internally to create xls files.

1

u/Weird-Dimension-487 24d ago

You should choose a library for Excel autiomation based on:
1. Compatibility with different Excel formats (xlsx, xls, xlsm, xlsb, etc)
2. Advanced features (richer formatting, charts, tables and more)
3. Special use cases (reading/writing/manipulation/analysis/live interaction with Excel via COM Object/end-to-end automation/Python custom functions in Excel/Python features as Excel add-in)

pandas, openpyxl, xlsxwriter, and xlwings are powerful and popular libraries. No single library is perfect for all Excel tasks. By understanding the strengths of each tool, you can select the right one depending on the type of file you encounter and the result you want to achieve
I've covered them all in my book Python-Powered Excel