{"id":1313,"date":"2014-04-11T15:45:59","date_gmt":"2014-04-11T12:45:59","guid":{"rendered":"http:\/\/www.water-simulation.com\/wsp\/?p=1313"},"modified":"2014-04-11T18:47:16","modified_gmt":"2014-04-11T15:47:16","slug":"epanet-excel-add-in","status":"publish","type":"post","link":"http:\/\/www.water-simulation.com\/wsp\/2014\/04\/11\/epanet-excel-add-in\/","title":{"rendered":"EPANET Excel Add-In"},"content":{"rendered":"<p>A colleague of mine sent me an Excel Add-In which utilize the <a title=\"EPANET Toolkit\" href=\"http:\/\/www.water-simulation.com\/wsp\/2005\/06\/22\/epanet-programmers-toolkit\/\">EPANET Toolkit<\/a> 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:<\/p>\n<p>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:<\/p>\n<ul>\n<li>Combining different sources of data using Excel capabilities (e.g. SCADA data).<\/li>\n<li>Manipulating data in Excel.<\/li>\n<li>Sending data to the EPANET Toolkit.<\/li>\n<li>From Excel, performing network simulations using the EPANET Toolkit with no programming.<\/li>\n<li><span style=\"font-size: 14px; line-height: 1.5em;\">Getting results easily from the EPANET Toolkit to Excel.<\/span><\/li>\n<li><span style=\"font-size: 14px; line-height: 1.5em;\">Analyzing and presenting results already in Excel.<\/span><\/li>\n<\/ul>\n<p><strong>Overview<\/strong><\/p>\n<p>The EPANET Excel Add-In features an easy way to use EPANET Toolkit functions in Excel.\u00a0The Add-in functions are described in EPANET 2 Programmer\u2019s Toolkit help.\u00a0The functions are divided into 2 categories: &#8220;EPANET Traditional&#8221; includes all the functions of the toolkit and &#8220;EPANET Extended&#8221; includes functions based on the fundamental toolkit functions. (e.g. ENXLSetLinkDiameter).<\/p>\n<p><a href=\"http:\/\/www.water-simulation.com\/wsp\/wp-content\/uploads\/2014\/04\/screenshot.23.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1316\" alt=\"Excel Add-In\" src=\"http:\/\/www.water-simulation.com\/wsp\/wp-content\/uploads\/2014\/04\/screenshot.23.png\" width=\"995\" height=\"649\" \/><\/a><\/p>\n<p><strong>Usage<\/strong><\/p>\n<ol>\n<li>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.<\/li>\n<li>Almost every function&#8217;s first parameter is the <b>trigger<\/b>. The trigger should be referenced to a cell computed prior to that function. Because Excel must know the order of computations, the trigger cell &#8220;tells&#8221; this to Excel. Please look at the examples for more information.<\/li>\n<li>Functions getting data from the toolkit always return one value:\n<div>\n<ul>\n<li>E.g. ENxgetlinkvalue returns a link value.<\/li>\n<li>The function ENxgetlinknodes is devided into 2 functions: ENxgetlinknodeto and ENxgetlinknodefrom.<\/li>\n<li>&#8220;Traditional&#8221; functions return &#8220;#N\/A&#8221; if any error occurs. To display the EPANET error code, instead of &#8220;#N\/A&#8221;, pass TRUE to the optional ReturnError parameter of the function.<\/li>\n<li>&#8220;Extended&#8221; functions return an error string (e.g. &#8220;Error 103&#8221; if an Epanet error occurs or some other string otherwise).<\/li>\n<\/ul>\n<\/div>\n<\/li>\n<li>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:\n<div>\n<ul>\n<li>The user can debug the functions with the help of error codes.<\/li>\n<li>Excel is forced to compute the dependent functions <span style=\"text-decoration: underline;\">after<\/span> this one, <span style=\"text-decoration: underline;\">as the return value is used as a trigger in the dependent function.<\/span><\/li>\n<\/ul>\n<\/div>\n<div><\/div>\n<\/li>\n<li>&#8220;Extended&#8221; 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.<\/li>\n<li>&#8220;Extended&#8221; functions include specific solutions such as the ENXLTraceSources function that performs source tracing for multiple sources and selected targets.<\/li>\n<li>The Add-in&#8217;s functions can be used with other functionality of Excel e.g. &#8220;goal seek&#8221;. See examples 3 and 6.<\/li>\n<li>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.<\/li>\n<li>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.<\/li>\n<li><span style=\"text-decoration: underline;\">Warning<\/span>: Some functions, especially Enclose and ENXLOpen may crash Excel. Save your work regarding this warning.<\/li>\n<\/ol>\n<p><strong>Examples<\/strong><\/p>\n<div>\n<p>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).<\/p>\n<ol>\n<li>Parametric test:\u00a0This 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.<\/li>\n<li>Online SCADA net solver :\u00a0In 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.<\/li>\n<li>Goal seek:\u00a0Excel 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.<\/li>\n<li>Get sources of each node:\u00a0The 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.<\/li>\n<li>Compute pump working points:\u00a0This example demonstrates how to run a 24 hour <span style=\"text-decoration: underline;\">hydraulic<\/span> simulation and get data of interest for every hour. &#8220;Extended&#8221; functions are used.<\/li>\n<li>Meeting a minimum chlorine residual target:\u00a0This example demonstrates how to run a 24 hour <span style=\"text-decoration: underline;\">quality<\/span> simulation and get data of interest for every 5 minutes period (Cl residual value). &#8220;Traditional&#8221; and &#8220;Extended&#8221; functions are used.<\/li>\n<li><span style=\"font-size: 14px; line-height: 1.5em;\">Compute pump combination data in a pumping station: \u00a0<\/span><span style=\"font-size: 14px; line-height: 1.5em;\">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.<\/span><\/li>\n<\/ol>\n<p>The Excel Add-In is distributed under the <a href=\"http:\/\/opensource.org\/licenses\/MIT\">MIT<\/a> license. Please report any bugs, issues and fixes to <strong><span style=\"color: #3366ff;\">EpanetXL at gmail.com<\/span><\/strong>. If \u00a0you use this software you may also use this email address to send PayPal donations.<\/p>\n<p>Download <a href=\"http:\/\/www.water-simulation.com\/wsp\/wp-content\/uploads\/2014\/04\/EPANET-FOR-EXCEL.zip\">EPANET Excel Add-In<\/a>\u00a0(ZIP, ~1MB)<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>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 &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"http:\/\/www.water-simulation.com\/wsp\/2014\/04\/11\/epanet-excel-add-in\/\"> <span class=\"screen-reader-text\">EPANET Excel Add-In<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,4,7],"tags":[209,82],"class_list":["post-1313","post","type-post","status-publish","format-standard","hentry","category-epanet-related","category-free-software","category-programming","tag-add-in","tag-excel"],"_links":{"self":[{"href":"http:\/\/www.water-simulation.com\/wsp\/wp-json\/wp\/v2\/posts\/1313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.water-simulation.com\/wsp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.water-simulation.com\/wsp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.water-simulation.com\/wsp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.water-simulation.com\/wsp\/wp-json\/wp\/v2\/comments?post=1313"}],"version-history":[{"count":2,"href":"http:\/\/www.water-simulation.com\/wsp\/wp-json\/wp\/v2\/posts\/1313\/revisions"}],"predecessor-version":[{"id":1320,"href":"http:\/\/www.water-simulation.com\/wsp\/wp-json\/wp\/v2\/posts\/1313\/revisions\/1320"}],"wp:attachment":[{"href":"http:\/\/www.water-simulation.com\/wsp\/wp-json\/wp\/v2\/media?parent=1313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.water-simulation.com\/wsp\/wp-json\/wp\/v2\/categories?post=1313"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.water-simulation.com\/wsp\/wp-json\/wp\/v2\/tags?post=1313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}