A colleague of mine sent me an Excel Add-In which utilize the EPANET Toolkit functions. This Add-In is just the right tool for all Excel users who want to make custom EPANET simulations without writing a line of code, just use function in Excel cells. From the Add-In documentation:
The EPANET Excel Add-in is open-source software which operates within the Excel environment, for providing an interface for the 2.0 version build 2.00.12 of EPANET (a hydraulic and quality modeling software created by the US EPA). With Excel, the user can perform complex network analyses without programming. The goal of the Add-In is to serve as a framework for:
- Combining different sources of data using Excel capabilities (e.g. SCADA data).
- Manipulating data in Excel.
- Sending data to the EPANET Toolkit.
- From Excel, performing network simulations using the EPANET Toolkit with no programming.
- Getting results easily from the EPANET Toolkit to Excel.
- Analyzing and presenting results already in Excel.
The EPANET Excel Add-In features an easy way to use EPANET Toolkit functions in Excel. The Add-in functions are described in EPANET 2 Programmer’s Toolkit help. The functions are divided into 2 categories: “EPANET Traditional” includes all the functions of the toolkit and “EPANET Extended” includes functions based on the fundamental toolkit functions. (e.g. ENXLSetLinkDiameter).
- Traditional functions are prefixed by ENx (e.g ENxgetcount). Extended functions are prefixed by ENXL e.g. ENXLSetLinkDiameter. There is some overlap between the categories.
- Almost every function’s first parameter is the trigger. The trigger should be referenced to a cell computed prior to that function. Because Excel must know the order of computations, the trigger cell “tells” this to Excel. Please look at the examples for more information.
- Functions getting data from the toolkit always return one value:
- E.g. ENxgetlinkvalue returns a link value.
- The function ENxgetlinknodes is devided into 2 functions: ENxgetlinknodeto and ENxgetlinknodefrom.
- “Traditional” functions return “#N/A” if any error occurs. To display the EPANET error code, instead of “#N/A”, pass TRUE to the optional ReturnError parameter of the function.
- “Extended” functions return an error string (e.g. “Error 103” if an Epanet error occurs or some other string otherwise).
- Functions setting data to the toolkit or performing a task always return an error code. The error code is the Epanet error code value plus a random value between 0 and 0.5. If another error occurs the error code is 900 plus that random value. This has 2 reasons:
- The user can debug the functions with the help of error codes.
- Excel is forced to compute the dependent functions after this one, as the return value is used as a trigger in the dependent function.
- “Extended” Functions setting data to the toolkit propose the option to run a simulation immediately after setting a value. This option is useful when the user wants to set a series of values (e.g. pipe diameters) to the toolkit and get a result (e.g. node pressure) for each value.
- “Extended” functions include specific solutions such as the ENXLTraceSources function that performs source tracing for multiple sources and selected targets.
- The Add-in’s functions can be used with other functionality of Excel e.g. “goal seek”. See examples 3 and 6.
- Also the ENXLLinInterp function for simple linear interpolation is included. For example, it can be used to compute pool volumes if the pool level and the volume curve are available in Excel.
- Epanet parameter codes (e.g. EN_BASEDEMAND) are added as defined names in Excel. Using them the formulas are much easier to understand and maintain.
- Warning: Some functions, especially Enclose and ENXLOpen may crash Excel. Save your work regarding this warning.
Attached are a few examples. The path in the Excel sheet is the directory where the example networks (Net1, Net2 and Net3) are stored. Although the examples do not need the Add-in to operate, it is recommended to use the Add-in for new projects as a better practice (e.g. Add-in updates).
- Parametric test: This example demonstrates the opening of an Inp file, how to pass a series of diameters to the Toolkit and getting a pressure value corresponding to each diameter.
- Online SCADA net solver : In this example the connection to the SCADA system is represented by a simple sql query. For every data change a snap shot simulation is performed. The user may change the data to check a What-If scenario.
- Goal seek: Excel functionality can be used to find answers to simple questions automatically e.g. what diameter should be placed for keeping the required pressure at a remote node.
- Get sources of each node: The purpose of this example is to illustrate the use of the ENXLTraceSources Extended function. This function performs multiple source tracing runs, one for each chosen source. This way the source mixture for desired nodes can be found.
- Compute pump working points: This example demonstrates how to run a 24 hour hydraulic simulation and get data of interest for every hour. “Extended” functions are used.
- Meeting a minimum chlorine residual target: This example demonstrates how to run a 24 hour quality simulation and get data of interest for every 5 minutes period (Cl residual value). “Traditional” and “Extended” functions are used.
- Compute pump combination data in a pumping station: The example demonstrates the computation of flow and head of pump combinations in a pumping station. The user is expected to supply the inp file and make the appropriate small changes in the worksheet to suit a specific pumping station.
The Excel Add-In is distributed under the MIT license. Please report any bugs, issues and fixes to EpanetXL at gmail.com. If you use this software you may also use this email address to send PayPal donations.
Download EPANET Excel Add-In (ZIP, ~1MB)