Trying to get only part of the data on an excel page. (Python?)

, , ,
1

Context:
I made a Dynamo Graph some time ago to recalculate certain parameters based on an excel file (which does the actual calculations).
What I’ve been doing is:
Read out some data from an element in a Revit project, sort it, write it to the excel file, read out the results, and then send data back into the parameter of the Revit element.
I’ve set it up so that, using a List.Combine node, I can do this process for a lot of elements.
This works fine, but what I’m not happy about is that I get a lot of redundant data. From writing to an excel file once I’m already getting a list of 25272 values, but I only need 6.
When running this graph on only a couple of elements this works just fine, but considering this may run on hundreds of elements this adds up and I think it might slow down the process significantly.

Now, my actual question:
I’m looking for a way to send data to an excel file, then before I get the output, filter out a specific column or row. I’m okay to use Python, but I haven’t really used that yet to look into excel files.

Thanks in advance.

2

Why not remove the slow, cumbersome, and error prone process of in-out from excel and do the math in Dynamo directly?

1 Like
3

I agree…
It’d be a bit tricky the first time but on long term that would save a lot of time.

4

Many of the excel workflows which I’ve seen over the years were originally developed in the 90’s, and they just keep going on and on because of this logic. It’s been 30 years and they still run along, not because they are efficient, but because it would be a lot of work to rebuild them.

Eventually the hours lost to ‘calculating 25,266 unneeded values’ exceeds the hours it’d take to rebuild the workflow.

5

using Excel Interrop maybe a solution
only_Visible Cells

import sys
import clr
import System

clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' )
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal

specsVisu = System.Enum.Parse(Excel.XlCellType, "xlCellTypeVisible")

class Lst_Xls():
	def __init__(self):
		try:   
			ex = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")
		except: 
			ex = Excel.ApplicationClass()   
		ex.Visible = True
		self.app = ex 
		self.error = None
		self.data = []  
		self.workbook = ex.ActiveWorkbook
		self.ws = ex.ActiveSheet
		
	def ReadVisibleCells(self):
		lst_xls = []
		plagefiltrevisible = self.ws.UsedRange.SpecialCells(specsVisu).Rows
		for row in plagefiltrevisible:
			lst_xls.append(row.Value2)
		return lst_xls

objxls = Lst_Xls()	

OUT = objxls.ReadVisibleCells()
1 Like
6

While smart, the situation is as @jacob.small thought: it’s a cranky old Excel file set up ages ago and these alterations aren’t easily made.
Funnily you did end up giving me an answer to my question, as you’ve shown me a method to read data from excel via Python, I did however, figure out that this does not actually solve my problem, as when I write data to Excel I still, inevitably, get the entire page as an output.