Hi,
I am working on creating a heat map of lighting usage. I have an excel file with the data of watts/square footage of each building and have the conditional formatting giving me the color representing each building.
I also have a map of the entire facility that is already made in MicroStation. How do I apply the data and coloring of the excel file to color each building according to the color represented in the excel file? (All buildings in the drawing are currently under the same level and are the same color.). The drawing is a 2D drawing so I can't access the datagroup definition tab.
If possible I would like this to be a link of some sort so the data can be refreshed and the map can still be relevant as the data changes.
Thanks
the easiest way is via generative components/ openbuilding designer but you need to litttle bit study GC, then directly from excel you can create map with value of needed parameters, /youtube generative components/
bye
karol
Hi Ryan,
what MicroStation version do you use (I recommend to follow the best practices and to always specify product + version exactly).
There is no specialized to solve your request available in MicroStation, so some sort of programming is required.
As Karol iNGs mentioned, GenerativeComponents available in OBD can be used, but I think it's not valid answer to you, because you mentioned you use MicroStation (and not OpenBuilding Designer).
To access Excel data from MicroStation can be done using MicroStation VBA, C# (NET in general) or C++. It's usually not very complicated (of course programming skills are required), but what is not clear from your description is how to link value (color) in Excel and related element (building) in MicroStation model.
You mentioned the buildings are shapes in 2D file. How it can be distinguished that a particular shape in the file should have color defined in Excel cell e.g. H12?
With regards,
Jan
Bentley Accredited Developer: iTwin Platform - AssociateLabyrinth Technology | dev.notes() | cad.point
Jan,
I am using MicroStation V8i (SELECTseries 10). I was able to download AECOsim Building Designer CONNECT if that's easier to use.
Each particular shape in the file represents a building on the campus. I have one cell of data in the Excel sheet that aligns with one building, but I don't know how to assign each value to each building in MicroStation.
For example, under "Building A" there is a value of 1.5. It is colored red because it is a higher value compared to the other values. All I need is to access this data in MicroStation so that the building appears red on the map. (I understand I could manually do this but my map needs to be adaptive and there are a lot of buildings.)
-Ryan
Ryan Carpenter said: I was able to download AECOsim Building Designer CONNECT if that's easier to use.
I am not sure. I am not OBD expert, but I guess for full functionality, the model has to be created using OBD tools from beginning. When the drawing is just MicroStation graphics, it will not provide extra functionality.
Ryan Carpenter said:I don't know how to assign each value to each building in MicroStation.
It's the crucial thing. Without defining unambiguous system (rules) how to identify what shape belongs to what Excel record, no code can be written.
Ryan Carpenter said:For example, under "Building A" there is a value of 1.5.
Ok, so in Excel, there is a column Name with value "Building A" and column value (at the same row) with value 1.5. It's a good start, but it's not enough. How color is calculated should be defined. I assume the cell color can be read by e.g. Excel VBA, but it has to be checked.
What about MicroStation drawing? How buildings are identified? When there is no identification, sorry, you have to do it manually.
What can be enough is when in every shape there is one text with the same value as in Excel. It's not so complicated to find text inside a shape, but it has become more complex when there are more texts available.
Each building in the drawing has a text in it that is the same as the text in Excel.
The color values come from calculations in separate sheets. Each building has it's own calculation sheet and then the final number is referenced in one sheet that shows values for all buildings (This final sheet is where the conditional formatting is used to color the cells based on their values in relation to the other values.).
I am able to open the file and edit it in both MicroStation and the OBD program.
Ryan Carpenter said:The color values come from calculations in separate sheets.
Ok, so it's a bit more complicated, but I think it does not matter as long as there is one final value per a building.
Ryan Carpenter said:Each building in the drawing has a text in it that is the same as the text in Excel.
Great, in such case I think it can be done using VBA or C# ... so the question is transformed into new question "how to write VBA code" ;-)
General MicroStation VBA code flow should be something like:
BTW How colors should be managed? Colors from Excel probably do not exist in MicroStation color table. Two solutions exist: RGB colors are used in MicroStation (outside color table) or every color is transformed to the closest color from color table.
... I guess you should move this thread to MicroStation Programming forum.
Regards,
Thanks so much for your help! I'll try to get this thread moved since I only have a little programming experience.
Best Regards,
Ryan
Ryan Carpenter said:I'll try to get this thread moved
Use More > Move tool available under your original post.
Ryan Carpenter said:I only have a little programming experience.
You should try to find somebody around you.
Even when the problem is not extremely complicated, it requires some time investment (but probably not many hours ;-).
Hi ,
if you have openbuilding designer you have to install it with generative components , forget about programming I believe that this video help you to do it
https://www.youtube.com/watch?v=QqM7f6gctgg
generative components are really wonderful and funny software
good luck
How do I download the generative components?