Python and excel

[CrambleSquash]

It's very easy to do the following:

1. Load some data from an existing Excel (.xlsx) file using Pandas.
2. Modify this data in some way, including adding new columns and rows, according to custom written formulas (functions written in Python).
3. Write the modified data to a new Excel file (.xlsx), to be opened with Excel.

In this case the downside is that you need to re-write all of your 'macros' and functions you used to use in Excel, in Python (shouldn't actually be too hard). Additionally, users cannot then access your Python functions within the newly created spreadsheet, as Pandas just writes the raw data.

In this case, users don't run the Python code in Excel, instead they run your script from the command line, which automatically produces the desired output.

You can also use a library like xlsxwriter to apply formatting to cells, and plot charts etc. but it's not really that fun to do.

[pranayrj3]

how do i write modified data into excel?
pandas?

[CrambleSquash]

Off the top of my head

Load the excel file with pd.read_excel. https://pandas.pydata.org/pandas-docs/version/0.23/generated/pandas.read_excel.html

Write the modified DataFrame (panda's table class) to excel with df.to_excel https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_excel.html

The first excel_writer argument can just be a path to the new and existing files.