Schedule data to sheets in an xlsm file

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!

PyRevit is offering xlswriter.

I think you can get a very good example how to export data from schedule to excel file from aussieBIMguru github.

Thank you for that! Looks like what I’m after. . . .

[Edit] But not really, apparently one cannot append to an existing xlsx file with xlsxwriter, it seems you have to generate a completely new file every time.

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.

1 Like