I’m in the process of upgrading a number of tools from Dynamo/DataShapes to Python with XAML interfaces and running up on a stump when it comes to getting data from specific schedules into specific tabs in a workbook that handles further processing of the data with VB scripts.
I would prefer to avoid using 3rd party packages like Pandas since it will complicate the setup for other users. I have no issues opening the workbook and iterating through the sheets using Microsoft.Office.Interop.Excel, and would love to send the data directly to specified sheets and ranges. I can send the data to CSV files if a middle man is necessary, but can’t for the life of me figure out how to get the data inserted into the workbook directly or from a csv.
It seems like Excel interop should be able to get the data in array form and plop it down where I want it, but I’ve googled for days and can’t find any helpful examples. Any pointers are welcome - Thanks a bunch!
Hi @Texmati, welcome to the forum!
I while ago I was able to package openpyxl into a library extension (folder with “.lib” suffix instead of “.extension”) to use it with Cpython scripts.
That library can append data to an already existing workbook.
You can install openpyxl into a python 3 environment (on windows I usually recommend to install mambaforge distribution, but feel free to use whatever you want), and then copy the openpyxl and et_xmlfile directories from the site-package directory into a openpyxl.lib folder (I put it in the same directory/repository of my extension, so I don’t need to add other directories in pyRevit’s config).
Be aware that this only works with Cpython, that unfortunately has still issues with the pyrevit library and XAML/WPF interfaces (something I started to investigate but I don’t have any ETA for the solution).
Thanks for that. For now I found it easier to just export schedules as csv files, and then suck the data up into my workbook and kill the files with VBA. I may see if I can use the .lib trick to access the SmartSheet API and pull project information from the cloud into Revit, though.