I have to read a certain .xlsx file (file1.xlsx), pull data from another .csv files (file2.csv) and write the processed data into the original file (file1.xlsx) along with some charts. The file has existing charts, I have looked into openpyxl, xlswriter, xlsread, and other python libraries.
It seems openpyxl is the only library which allows you to read and write files back with the same library. Others like xlswriter only write, or xlsread only read.
However, even openpyxl is limited, in that if I read a file which already has charts, the charts are lost.
The only way I can think of doing this from python only, is using a combination of openpyxl and pywin32. 1. extract data from file2.csv, process data and printout to another .xlsx file (or hold in memory) 2. copy data from memory or from file3.xlsx and past into file1.xlsx using pywin32.
Before, I dive into pywin32, I wanted to check if this is a feasible strategy. I am working in a Linux environment, so would have preferred to stay in this environment while doing the data processing, but I dont know whether there is another way out.
Has anybody read an excel workbook with charts and written data out to the same excel workbook with openpyxl or other libraries (WITHOUT LOSING THE CHARTS)?
Panda's maybe able to read and write to Excel. It uses xlrd to read excel files and xlswriter to write it out. So it seems that it can load the Excel file usign xlrd, into memory and pass it out to xlsxwriter, to save the file.
https://pandas.pydata.org/pandas-docs/stable/io.html#io-excel-writer