Schedule data to sheets in an xlsm file

Texmati · July 28, 2023, 5:41pm

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!

shampoon · July 28, 2023, 6:02pm

@Texmati
PyRevit is offering xlswriter.

github.com

eirannejad/pyRevit/blob/master/site-packages/xlsxwriter/core.py

###############################################################################
#
# Core - A class for writing the Excel XLSX Worksheet file.
#
# Copyright 2013-2017, John McNamara, jmcnamara@cpan.org
#

# Standard packages.
from datetime import datetime

# Package imports.
from . import xmlwriter


class Core(xmlwriter.XMLwriter):
    """
    A class for writing the Excel XLSX Core file.


    """

This file has been truncated. show original

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

github.com

aussieBIMguru/guRoo/blob/main/guRoo.tab/Tools.panel/Export.pulldown/ScheduleXcl.pushbutton/ScheduleXcl_script.py

# import pyrevit libraries
from pyrevit import revit, DB, forms, script

# import libraries
import os
import xlsxwriter
from datetime import datetime

# Store current document into variable
doc = revit.doc

# Retrieve data from the schedule
sched = revit.active_view

try:
	tableData = sched.GetTableData()
	sectionData = tableData.GetSectionData(DB.SectionType.Body)
	numbRows = sectionData.NumberOfRows
	numbCols = sectionData.NumberOfColumns
except:

This file has been truncated. show original

Texmati · July 28, 2023, 6:30pm

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.

sanzoghenzo · July 31, 2023, 7:03pm

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).

Texmati · August 1, 2023, 2:41pm

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.