I use Excel extensively for analyzing the results of EnergyPlus but would like to know more about approaches to use Excel to create and manipulate IDF files.
I have been using E+ as a practioner for a long time now and one of the issues we had here in the office was the data input time. So we started to use excel to make this much much quicker. We often have models that have 400-500 thermal zones and we use excel to detail out all the inputs that we need and then write it to idf code. Below is an example of how we put people, equipment and lighting into the model:
The yellow boxes we fill in by hand, everything else gets filled in automatically from the input verification file from e+ and from the zone types chosen. the purple columns are the EnergyPlus input data that can be directly pasted into the text file.
We do a similar process with the HVAC, have one sheet that details out what is in the model, the spreadsheet that creates the HVAC input data however is much larger. The main advantage though is that we can leverage the functionality of excel (copy down, formulas etc...) and then write directly into the text editor. It also makes the file easier to manipulate, if the lighting levels change for instance, you change it in the spreadsheet, delete all the object in e+ and then just replace them with the spreadsheet code.
When it comes to App G baseline stuff, there are a ton of equations and rules that are spelled out in App G. We basically have a spreadsheet which will calculate all these bits and pieces to ensure that the correct information is written into the idf file. Again leveraging the functionality of excel.
Finally we also do geometry manipulation but this is often less clean. We do a lot of models with Radiant ceilings. To put these into the model via OpenStudio plugin would take us many many hours, so we have a spreadsheet that finds all the zones with chilled ceilings, then all the surfaces and replaces the constructions on those that have chilled ceilings with an internal source construction. It will also create the surface groups for each zone.
Let me know if you have any specific examples or questions in mind and I would happily answer them. I love our data manipulation with excel, it has halved our input times at least and improved our accuracy significantly.
This is your OpenStudio Excel plug-in?
@__AmirRoth__ ummm... well maybe not, but I am totally willing to try. More the simulation diagnostics stuff actually, that would fit into OpenStudio very well. That is my next task.
Radiant ceilings are really quick to input in the model with the OpenStudio API. <50 lines of code. I have a measure that does it.
An OpenStudio excel-plugin would be nifty. Most engineers I encounter like Excel too much to bother to learn code. Making an OpenStudio GUI in the lowest common denominator of tools/languages engineers is a version of Atwood's law for non-programmers: "Any application that can be written in Excel, will eventually be written in Excel."
Excel has great functionality and most engineers already know how to use it. That is why we have stuck with it. Also I agree with you @mdahlhausen if we can write it in excel we aren't going to learn programming because we have already solved the problem sufficient to our needs. Not great I grant you, but unfortunately how we are right now.
a few questions :
1)is this all excel or a mix of excel & vba (I assume there is some vba to write to the idf file...yes?).
2) this does parts of the idf file, not a complete idf 'ready to run' idf OR does it do it all?
3) when making changes to the model/file, is the idf read back into excel modified and recreated? OR are the changes made in excel, new idf created and either this is run or the bits needed pasted/merged into the existing idf file?
4) how are changes / updates managed to keep up with new releases of E+? This could become a tedious process to chase E+ at all times...
With Pandas library in Python, you can read Excel files. Also there is Eppy, that can manipulate IDF. Both of them works with lists. If you have a good naming convention in the IDF it's quite easy to loop through these lists and fill up your IDF objects with data from Excel.
I like this workflow a lot. You get to leverage everything you've built up over the years in Excel, and have a really easy Excel tablular UI for data entry, plus the simple writing to IDF from Eppy.
You could also make the choice of using ExcelPython to write python - instead of VBA - macros directly in Excel. I've used it before, it works nicely. Or you can also call your python script directly from VBA using a Shell command (link your macro to a button or something, so you don't have to open a terminal and launch a script...)
I was just looking into using Pandas to read my EP output files the other day. However, I had trouble parsing the very long headers (with spaces) from the EP output in Pandas. Do you have any recommendations? Did you throw away the header names and just use indexes instead? If you are willing to share, an example would be very appreciated.
@JustinShultz - if you are using Pandas to look at EP output, I would recommend querying the SQL output file
@Lyle K, that is a very good suggestion. Thank you, I will start there!
It's worth mentioning that if you are looking for a way to go from an Excel table to an IDF snippet - or to paste directly in the IDF Editor - you should check my answer here that in turns references my github repository Useful Excel Macros for Energy Modeling where you'll find a macro that does just that (and more).
(The IDF editor already has the capability to export to Excel, I just wrote a macro to go the other way around)
I never circled back on my original question. I wrote a small Excel macro to help people who are Excel users and use EnergyPlus called Drumlin. Essentially, it allows you to create a modified IDF file based on an original IDF file and an Excel spreadsheet, thus energy efficiency measures can be implemented in Excel. It is still in a "beta" phase.
Try EpXL (EnergyPlus-Excel). It's basically an Excel-based alternative to both IDF Editor and EP Launch. It is convenient for editing large IDF files, as the input data is tabulated in a compact format (one row per object), and you can copy/paste and use equations in cells. It has other useful features such as automatic vetting and popup parameter descriptions (both from the EP schema), epJSON file import/export, and batch simulations (Parametric / MonteCarlo).
EpXL's homepage is https://github.com/SchildCode/EpXL
.png "dradair")
Comments