1

I have an Excel file(xlsx) that already has lots of data in it. Now I am trying to use Python to write new data into this Excel file. I looked at xlwt, xldd, xlutils, and openpyxl, all of these modules requires you to load the data of my excel sheet, then apply changes and save to a new Excel file. Is there any way to just change the data in the existing excel sheet rather than load workbook or saving to new files?

Jerry Shi
  • 43
  • 1
  • 4
  • Is is a simple sheet that could be represented as a .csv file? if so, you can open as a text file in append mode and just begin writing lines to the bottom – Aaron Jul 19 '16 at 15:56
  • Possible duplicate of [Search and replace a line in a file in Python](http://stackoverflow.com/questions/39086/search-and-replace-a-line-in-a-file-in-python) – Bryce Drew Jul 19 '16 at 15:56
  • But I don't know if it will work with the excel file being binary. – Bryce Drew Jul 19 '16 at 15:58
  • @Aaron it's an Excel Sheet not .csv file. And I don't think it works the same way as a text file. – Jerry Shi Jul 19 '16 at 16:00
  • @JerryShi I understand it is currently an excel sheet, but can the data be represented as columns of values? if so, you can export the existing sheet as a csv file, then use native python to write to the end of the file (append mode) – Aaron Jul 19 '16 at 16:03
  • @Aaron You cannot always use CSV to replace something like XLSX. For one thing CSV files have no type information. – Charlie Clark Jul 19 '16 at 16:45
  • @CharlieClark that's why I asked if his data would support that... I realize it's not always possible, but that was the only other way to solve his problem given his constraints. (maybe not the **only** way, but the only one obvious to me) – Aaron Jul 19 '16 at 16:48
  • @Aaron when the question specifies the file format then that is the place to start. – Charlie Clark Jul 19 '16 at 20:43
  • @JerryShi consider using built-in excel [Visual Basic](https://en.wikibooks.org/wiki/Excel_VBA) – Bryce Drew Jul 21 '16 at 15:49

2 Answers2

7

This is not possible because XLSX files are zip archives and cannot be modified in place. In theory it might be possible to edit only a part of the archive that makes up an OOXML package but, in practice this is almost impossible because relevant data may be spread across different files.

Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
-1

Please check Openpyxl once again. You can load the data, do things with python, write your results in a new sheet in the same file or same sheet and save it (as everything is happening in memory).

e.g: load data

wb = openpyxl.load_workbook("file.xlsx", data_only=True)

manipulate with python

# python codes

create sheet

some_sheet = wb.create_sheet("someSheet") # by default at the end

program to write in sheet

# program to write in sheet

save file (don't forget to close the excel file if its open before saving, as it will raise "Permission Error")

wb.save("file.xlsx"

here is the link https://openpyxl.readthedocs.io/en/default/tutorial.html

Arun Kumar Khattri
  • 1,519
  • 15
  • 25