15

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')
Russ
  • 10,835
  • 12
  • 42
  • 57
Xiao
  • 12,235
  • 2
  • 29
  • 36

2 Answers2

18

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

Xiao
  • 12,235
  • 2
  • 29
  • 36
  • 6
    Nice link! Should it ever go offline, here's a gist with the same content: https://gist.github.com/airstrike/5469478 – airstrike Apr 26 '13 at 18:54
1

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.

Adam Morris
  • 8,265
  • 12
  • 45
  • 68
  • Yes, I am using v1.5.3, so it seems that there is no good way right now. Would 'COM with pywin32' work? – Xiao Jul 22 '11 at 01:28
  • 1
    COM supposedly works - and uses office's native interface. I haven't tried it though as it only works on windows. Good luck with it! – Adam Morris Jul 22 '11 at 09:38
  • Is the styles able keep after updating column with new version? – conandor Nov 25 '14 at 12:19
  • 1
    @conandor - yes. The less common named styles still aren't supported, but as of openpyxl 2.1, the majority of your style information is preserved (and even editable!), including row/col styles and conditional formatting. – Adam Morris Nov 25 '14 at 21:16