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?
9
-
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 bottomAaron– Aaron2016-07-19 15:56:50 +00:00Commented Jul 19, 2016 at 15:56
-
Possible duplicate of Search and replace a line in a file in PythonBryce Drew– Bryce Drew2016-07-19 15:56:56 +00:00Commented Jul 19, 2016 at 15:56
-
But I don't know if it will work with the excel file being binary.Bryce Drew– Bryce Drew2016-07-19 15:58:04 +00:00Commented Jul 19, 2016 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– Jerry Shi2016-07-19 16:00:29 +00:00Commented Jul 19, 2016 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– Aaron2016-07-19 16:03:10 +00:00Commented Jul 19, 2016 at 16:03
|
Show 4 more comments
2 Answers
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.
Comments
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
