Bay City may have located elusive water main break
Bay City officials believe they have finally found a water main break that has drained the city’s water system. This sign greeted people entering the Bay … …read more
Water service restored after Monsey main break
MONSEY – Water service has been restored for dozens of residents in Rockland County following a water main break early Saturday morning. …read more
Watermain Break in Rotterdam Causes Flooding
Water pressure may be low in some areas and residents should limit water usage. Police advise residents to avoid the area of the water main break. …read more
Live chat: Bay City officials holding press conference to update on water emergency
Crews have searched throughout the day for the source of the water main break, but have come up empty handed. An emergency meeting was … …read more
Bay City/Bay County water emergency in effect
According to The Associated Press, Bay City officials were searching Sunday for the source of a water main break that was draining 10 million gallons … …read more
Bay City losing millions of gallons in water break
Bay City Bay City officials were searching Sunday for the source of a water main break that was draining 10 million gallons of water a day and … …read more
Water main break reported in Sorrento Valley
SAN DIEGO – Customers in Sorrento Valley are without water service Saturday while crews work to repair a water main break. The 12-inch concrete … …read more
Rotterdam Water Main Break Closes One Road
ROTTERDAM, N.Y. — One road is closed in Rotterdam after a water main break, leaving some residents with low water pressure. The break happened … …read more
EPANET Excel Add-In
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.
Overview
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).
Usage
- 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.
Examples
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)
The creator of EPANET has retired
Elad SalomonsPosted on
Posted in Announcements, EPANET related, Free software, Programming, Water simulation Packages
—
10 Comments ↓
Jim Uber posted on the EPANET-Users list that Lew Rossman, the creator of EPANET, has retired. He also sent the code of QHCSIM which Lew published back in 1991 and might be the first public version of a combined hydraulic and water quality solver. Here is Jim’s message:
Lew Rossman has announced on the SWMM-USERS list that he will be retiring from the USEPA as of April 1, 2014, after more than 30 years of service to his profession.
I’d guess that everybody on this list either has communicated directly with Lew over the years, or has heard of him; certainly everybody has used one of his software packages, EPANET. Lew has helped out so many people over his career through his interactions on water infrastructure modeling research, and is widely appreciated throughout the world for his contributions. To say he does not seek the limelight would be the understatement of the century, perhaps. But he will be missed dearly in terms of his work at the USEPA, by engineers and scientists of several different generations.
I was thinking of how to document, in some modest way, Lew’s contributions to potable water systems modeling. A colleague told me yesterday about how Microsoft had recently contributed the source code for Word 1.0 in order to document it’s significant place in computing history. So with that, I am attaching here the earliest know (to me) version of EPANET, in terms of the user’s guide and the source code, dated October 16, 1991. (At that early time, the name ‘EPANET’ didn’t exist, and Lew gave this experimental version the name ‘QHCSIM’.) As far as I know, this code is the first one to combine in one executable both hydraulic and water quality simulation capabilities – from the beginning the hallmark of EPANET. The code consisted of 1487 lines and I am sure fit comfortably on a 1.44 MB floppy drive ;-). A note from the user’s guide indicates that “The program can accommodate 100 nodes, 200 pipes, 10 tanks, and up to 50 pump control rules. For contaminant routing, a pipe can be divided into at most 50 volume segments.”
Perhaps this helps explain, by way of comparison to what is done today, Lew’s dedication to, and impact on, our field.
Here is QHCSIM source code and documentation (opens in a new tab).
Lew Rossman at WDSA 2010
Lew, thank you for creating EPANET. It definitely made a difference and surly changed my professional career! I hope now you will have time for EPANET 3.