CODEWITHSUNDEEP
pythonexcelpandas
PaulDong
PaulDong

Reputation: 793

Read from / write to a specific location in Excel file

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

Upvotes: 1

Views: 1528

Answers (2)

PaulDong
PaulDong

Reputation: 793

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

Upvotes: 2

emican
emican

Reputation: 276

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.

Upvotes: 1

Related Questions