I have a .xlsx file to edit, I found openpyxl could manipulate Excel 2007 files. I only want to change the value in some cells and leave other settings unchanged.
But after I went through the Documentation, I cannot find the examples to edit a existing file. It only demostrated reading a .xlsx file and writing to a new one.
I tried below way to edit an existing file, but after I saved it, the styles in the file has been removed( like fonts, colors):
from openpyxl.reader.excel import load_workbook
wb=load_workbook(r'd:\foo1.xlsx')
ws=wb.get_sheet_by_name('Bar')
ws.cell('A1').value= 'new_value'
# save the workbook to a new file to finish the editing
# but the style settings has been removed (such like font, color) in the new file
wb.save(r'd:\foo2.xlsx')
Openpyxl is a Python library for reading and writing Excel (with extension xlsx/xlsm/xltx/xltm) files. The openpyxl module allows Python program to read and modify Excel files.
Now openpyxl cannot handle styles good enough, so I tried using pywin32 COM and got the solution. Here is a good python-excel-mini-cookbook to use pywin32 COM for Excel
Styles aren't fully supported yet in openpyxl. Make sure you're using the latest version (1.5.3 as of now), as it recently improved its style capabilities.
The xlwt has more complete formatting for .xls files, but doesn't support xlsx as of yet.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With