Many spreadsheets have formulas and formatting that Python tools for reading and writing Excel files cannot faithfully reproduce. That means that any file I want to create programmatically must be something I basically create from scratch, and then other Excel files (with the aforementioned sophistication) have to refer to that file (which creates a variety of other dependency issues).
My understanding of Excel file 'tabs' is that they're actually just a collection of XML files. Well, is it possible to use pandas (or one of the underlying read/write engines such as xlsxwriter or openpyxl to modify just one of the tabs, leaving other tabs (with more wicked stuff in there) intact?
EDIT: I'll try to further articulate the problem with an example.
Can I do that and, if so, how?
To write to an existing Excel file without overwriting data using Python Pandas, we can use ExcelWriter . to create the ExcelWriter instance with the Excel file path. And then we call save to save the changes.
Most of the tasks you can do in Excel can be done in Pandas too and vice versa. That said, there are many areas where Pandas outperforms Excel. In this introduction to Pandas, we will compare Pandas dataframes and Excel Spreadsheet, learn different ways to create a dataframe, and how to make pivot tables.
As shown above, the easiest way to read an Excel file using Pandas is by simply passing in the filepath to the Excel file. The io= parameter is the first parameter, so you can simply pass in the string to the file. What is this? The parameter accepts both a path to a file, an HTTP path, an FTP path or more.
Read an Excel file into a pandas DataFrame. Supports xls , xlsx , xlsm , xlsb , odf , ods and odt file extensions read from a local filesystem or URL. Supports an option to read a single sheet or a list of sheets.
I had a similar question regarding the interaction between excel and python (in particular, pandas), and I was referred to this question.
Thanks to some pointers by stackoverflow community, I found a package called xlwings that seems to cover a lot of the functionalities HaPsantran required.
To use the OP's example:
Working with an existing excel file, you can drop an anchor in the data block (Sheet3) you want to import to pandas by naming it in excel and do:
# opened an existing excel file
wb = Workbook(Existing_file)
# Find in the excel file a named cell and reach the boundary of the cell block (boundary defined by empty column / row) and read the cell
df = Range(Anchor).table.value
# import pandas and manipulate the data block
df = pd.DataFrame(df) # into Pandas DataFrame
df['sum'] = df.sum(axis= 1)
# write back to Sheet3
Range(Anchor).value = df.values
Let me know if this solves your problem and if there's anything I can help.
Big kudos to the developer of xlwings, they made this possible.
Below is an update to my earlier answer after further question from @jamzsabb, and to reflect a changed API after xlwings updated to >= 0.9.0.
import xlwings as xw
import pandas as pd
target_df = xw.Range('A7').options(pd.DataFrame, expand='table').value # only do this if the 'A7' cell (the cell within area of interest) is in active worksheet
#otherwise do:
#sht = xw.Book(r'path to your xlxs file\name_of_file.xlsx`).sheets['name of sheet']
#target_df = sht.Range('A7').options(pd.DataFrame, expand='table').value # you can also change 'A7' to any name that you've given to a cell like 'interest_table`
I'm 90% confident the answer to "can pandas
do this" is no. Posting a negative is tough, because there always might be something clever that I've missed, but here's a case:
Possible interface engines are xlrd/xlwt/xlutils
, openpyxl
, and xlsxwriter
. None will work for your purposes, as xlrd/wt
don't support all formulae, xlsxwriter
can't modify existing xlsx
files, and openpyxl
loses images and charts.
Since I often need to do this, I've taken to only writing simple output to a separate file and then calling the win32api directly to copy the data between the workbooks while preserving all of my colleague's shiny figures. It's annoying, because it means I have to do it under Windows instead of *nix, but it works.
If you're working under Windows, you could do something similar. (I wonder if it makes sense to add a native insert option using this approach to help people in this situation, or if we should simply post a recipe.)
P.S.: This very problem has annoyed me enough from time to time that I've thought of learning enough of the modern Excel format to add support for this to one of the libraries.
P.P.S.: But since ignoring things you're not handling and returning them unmodified seems easy enough, the fact that no one seems to support it makes me think there are some headaches, and where Redmond's involved I'm willing to believe it. @john-machin would know the details, if he's about..
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With