1

Have a real use case for this. Want to be able to do some data aggregation and manipulation with Pandas, envisioned workflow as such:

  1. Find in an Excel file a named cell
  2. reach the boundary of the cell block (boundary defined by empty column / row)
  3. read the cell block into Pandas DataFrame
  4. do stuff with Pandas
  5. Write the resulting DataFrame back to the same Excel file, to a location defined by another named cell
  6. Preserving the charts and formula of the Excel file
pnuts
  • 58,317
  • 11
  • 87
  • 139
PaulDong
  • 711
  • 7
  • 19
  • I think you can read [this post](http://stackoverflow.com/questions/28142420/can-pandas-read-and-modify-a-single-excel-file-worksheet-tab-without-modifying) as an answer. It is a possible duplicate. – Romain Sep 17 '15 at 11:48
  • I wouldn't consider the above post as the answer to this question. As it only partially covers the impossibility of writing back to the excel file. How about reading data from a named cell? – PaulDong Sep 18 '15 at 01:28

2 Answers2

2

Since the question has been down-voted, it is unlikely someone else will provide answers. Just to post my implementation here:

I used the excellent python package called xlwings that can be easily installed if you have the conda distribution of python.

wb = Workbook(Existing_file) # opened an existing excel file
df = Range('tb_st').table.value # 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 block 
import pandas as pd
df = pd.DataFrame(df) # into Pandas DataFrame
df['sum'] = df.sum(axis= 1) # do stuff with Pandas

Range('cp_tb').value = df.values # Write the resulting DataFrame back to the same Excel file, to a location defined by another named cell

# tested that this implementation didn't temper any existing formula in the excel file
PaulDong
  • 711
  • 7
  • 19
1

I think you will need to use an additional reasources like DataNitro. https://datanitro.com

Or with a small amount of VBA you initiate the process, dump the named range to a csv, run python from command prompt and pass csv file, open it with VBA and work the results into the sheet.

emican
  • 256
  • 1
  • 6
  • Thank you for pointing me to the right direction! I went to datanitro, but it requires installation and pricing was a bit steep. So I found xlwings, and I think one of the use case they provided can cover my use case: [link](http://docs.xlwings.org/datastructures.html). Will try it out and provide a detailed result here. – PaulDong Sep 18 '15 at 04:37
  • Great! A few years ago Data Nitro or its precursor was free. I'm going to look into xlwings, too! – emican Sep 18 '15 at 11:26
  • xlwings is indeed very good. It also covers the user defined function - meaning you can run python functions in excel, and package them for distribution. This question has been down-voted, would you mind help upvote this topic to spread the words? I don't have enough rep to upvote yet. Thanks – PaulDong Sep 18 '15 at 14:51
  • xlwings is an improvement over Ironspread (which rebranded as DataNitro) It allows me to use excel as a presentation layer – emican Sep 19 '15 at 16:30