1

I am trying to update an existing excel file with Pandas.

This files has several sheets:

  • A sheet that contains the raw data. This is the data I want to export from Pandas
  • Several sheets using the raw data that contain Pivot tables.

I use this code to update the first sheet of the excel files but keep the other sheets:

disq_df = pd.DataFrame(disqualifications)

writer = pd.ExcelWriter(args.output, engine='openpyxl')
if os.path.exists(args.output):
    f, ext = os.path.splitext(args.output)
    shutil.copyfile(args.output, f + "-saved" + ext)
    writer.book = load_workbook(args.output)
    writer.sheets = dict((ws.title, ws) for ws in writer.book.worksheets)

disq_df.to_excel(writer, sheet_name=args.sheet)
writer.save()

This mostly works fine (only the first sheet is modified), except that the Pivot Tables on the others sheets actually standard cells. I also had graphics on some of the sheets that disapeared as well.

Is there a way to keep these sheets totally unmodified? Or is it a limitation of openpyxl?

This is an extract of the sheet: https://www.dropbox.com/s/dd3jhiif93knh26/Disqualifications.xlsx?dl=0.

The corresponding disq_df dataframe is just build from dicts:

disq_df = pd.DataFrame({'Année naissance': {0: 2005, 1: 2006},
 'Catégorie': {0: 'Poussin', 1: 'Poussin'},
 'Club': {0: "CERCLE NAGEURS D'ANTIBES", 1: "CERCLE NAGEURS D'ANTIBES"},
 'Compétition': {0: 'Natathlon poussins N° 2', 1: 'Natathlon poussins N° 2'},
 'Date': {0: '2016-02-27 15:17:00', 1: '2016-02-27 15:49:50'},
 'Disqualification': {0: 'DSQ Vi', 1: 'DSQ Ni'},
 'Disqualification (libellé)': {0: 'Disqualifié pour Virage incorrect',
  1: 'Disqualifié pour Nage incorrecte'},
 'Ligne': {0: 2, 1: 1},
 'Nage': {0: 'Nage Libre', 1: 'Dos'},
 'Nage (Complet)': {0: '400 Nage Libre Messieurs', 1: '50 Dos Dames'},
 'Niveau': {0: 'Compétitions Départementales',
  1: 'Compétitions Départementales'},
 'Sexe': {0: 'M', 1: 'F'},
 'Série': {0: 1, 1: 3}})
Dric512
  • 3,525
  • 1
  • 20
  • 27
  • it would be much easier to help you if you would provide a sample Excel file (you may upload it somewhere and post a link here) and sample data for your `disq_df` data frame – MaxU - stand with Ukraine Mar 20 '16 at 17:44
  • Ok I added it. Thanks for looking at it. – Dric512 Mar 20 '16 at 21:31
  • your code works just fine for me. It creates new sheet. Are you sure that you don't specify an existing sheet-name in `args.sheet` and because of that it's being overwritten. PS MS Excel 2010 complains about your Excel file: 'found unreadable content ... Do you want to recover ...?' – MaxU - stand with Ukraine Mar 20 '16 at 22:02
  • Yes I am sure I override the correct sheet. I did not try to open the file with Excel, only Libreoffice. – Dric512 Mar 22 '16 at 13:31

2 Answers2

1

Openpyxl cannot handle styles and graphics easily. See Use openpyxl to edit a Excel2007 file (.xlsx) without changing its own styles?

It gives you the link to show you how to use win32com to do it.

You can also try xlwings for some basic operations. I believe they both require you to have Excel actually installed unfortunately.

Community
  • 1
  • 1
tvashtar
  • 4,055
  • 1
  • 14
  • 12
  • That does not really work for me as I don't have Excel installed and do not want to depend on it. However I guess that I will have to accept your answer as there is certainly no better answer... – Dric512 Mar 22 '16 at 13:32
  • Yeah I wish there were a better solution. I hunted for a good while a few months ago and couldn't find one. It seems the only two options are overwriting the original which openpyxl and xcelwriter do after reading in the data from the original and recreating it. Or interfacing with Excel to make edits which xlwings and win32com does but that requires Excel to be installed. In theory openpyxl could be expanding to read in formatting and charts it just needs more contributors. – tvashtar Mar 22 '16 at 14:21
0

according to this: https://openpyxl.readthedocs.org/en/2.3.3/#installation you also need to install pillow ($ pip install pillow)

joel goldstick
  • 4,393
  • 6
  • 30
  • 46
  • Pillow is installed on my system (PIL library is available). It looks like this is only required to deal with images. – Dric512 Mar 20 '16 at 21:15