TopoLib contains a number of User Defined Functions (UDFs) for Coordinate Conversion and Transformation, supported by Ribbon commands and dialogs, made available as an Excel extension. The code is based on a number of open source libraries, that are available for download on GitHub.
The code is made available under the ZLib license. The main open source libraries underpinning TopoLib are :
| Library | Version | License type |
|---|---|---|
| ExcelDna.Add-In | 1.5.1 | ZLIB |
| ExcelDna.Integration | 1.5.1 | ZLIB |
| ExcelDna.IntelliSense | 1.5.1 | ZLIB |
| ExcelDna.XmlSchemas | 1.5.0 | ZLIB |
| ExcelDnaDoc | 1.5.1 | ZLIB |
| SharpProj | 8.2001.106 | Apache 2.0 |
| SharpProj.Core | 8.2001.106 | Apache 2.0 |
| XlDialogBox | 1.0.0 | MIT |
These packages need to be installed from NuGet. Furthermore you need to add a reference to System.Configuration and to Microsoft.Office.Interop.Excel in your project.
Please note that SharpProj is the core package in TopoLib, as it makes the proj library writtten in C/C++ accessible from C#.
PROJ is a generic coordinate transformation software that transforms geospatial coordinates from one coordinate reference system (CRS) to another. This includes cartographic projections as well as geodetic transformations.
PROJ started purely as a cartography application letting users convert geodetic coordinates into projected coordinates using a number of different cartographic projections. Over the years, as the need has become apparent, support for datum shifts has slowly worked its way into PROJ as well. Today PROJ supports more than a hundred different map projections and can transform coordinates between datums using all but the most obscure geodetic techniques.
PROJ is widely used in the industry, for instance QGIS, depends on it. QGIS is a very well established Open Source Geographic Information System.
This is the second approach of creating the TopoLib library. The original version was built using C++ in conjunction with the XLW-library to create Excel extensions. The XLW-library is no longer actively maintained, and therefore the transition to 64-bit Excel became troublesome. This is where Excel-DNA came to the rescue (note: 'DNA' stands for DotNet for Applications, similar to VBA - Visual Basic for Applications). This made a transition to C# mandatory. Therefore, PROJ (written in C++) either needed to be replaced by a similar library written in C# (like the DotSpatial library), or alternatively C# bindings to PROJ needed to be used.
The the DotSpatial library isn't very actively maintained. The PROJ library in contrary has made major changes to its functionality and API and an improved accuracy over the past few years. So it would be great to link against that library (somehow).
Interfacing managed C# code with native C++ is however very tricky, and various methods are possible (P/Invoke, COM Interop or C++/CLI), each with their own pros and cons. See for instance this MSDN article, or this blog article.
The advantage of using C++/CLI is that the resulting 'mixed language' DLL can incorporate PROJ as a static library, as well as all required third party libraries. For PROJ these are: jpeg62.lib, lzmad.lib, sqlite3.lib, tiffd.lib and zlibd1.lib. This substantially reduces the number of required DLL's in your project. When used as a (thin) C# wrapper around PROJ, additional type checking and functionality can be also implemented.
This is where SharpProj comes into the picture. It exposes the PROJ library through managed C++/CLI classes, which in turn can be consumed by Excel-DNA to create UDF's in Excel. Almost every PROJ C++-routine finds its counterpart in SharpProj.
The PROJ library uses an environment variable PROJ_LIB to locate its database and the (GeoTiff) grid-files. To help the end user, three routines have been added for you to set and read environment variables under =TL.env in TopoLib. Furthermore, PROJ_LIB can be set using a dialog, launched from the TopoLib ribbon.
Parameters that need to be persistent outside of a particular spreadsheet can be saved in a TopoLib.config file that resides in the folderLocalApplicationData\TopoLib\. See routines under =TL.cfgin TopoLib for more information.
After installing the required packages from GitHub, it is time to build the project. This is as simple as hitting F7. As part of the Post-build events, a batch file build.bat is called that creates a publish folder at the root of the project. Step into this folder and step down to select the proper version of Excel you are using (x64 or x86). Then double-click the *.xll file, to load the Add-In. Once the Add-In is loaded, the UDF's are recognized by Excel and you can run the example spreadsheets. Separate spreadsheets have been included that demonstrate the use of static arrays, using so-called Control-Shift-Enter (CSE) functions used in Excel 2019 and earlier. Later versions of Excel (Office 365) use so-called dynamic arrays, where the results 'spill over' (to the bottom-right ) from the active cell. No more worries on having to pre-select the (anticipated) output area beforehand.
An installation script has been developed to store the *.xll and associated files in the right folder on your PC. Alternatively, just double-click the *.xll file to get it loaded. Have fun using the tool and make improvement suggestions if you'd have these...
As of 12 May 2022, the first binary release of TopoLib has been published on my website. Nearly all functions from SharpProj have been implemented, and the Excel Ribbon Interface provides access to various settings, commands, dialogs and a wizard.
Despite the vast number of routines, the core functionality of the Add-In is very straight forward; after all, as an end-user you just want to convert a list of coordinates from CRS-source to CRS-target. For this to happen, you need effectively just one call:
=TL.cct.ApplyForward($F$89,$F$90,$B112:$C112)
This will transform the coordinates you provided in $B112:$C112to new coordinates using the source Coordinate Reference System (CRS) defined in $F$89 and the target CRS defined in $F$90. That is all there is to it !
The project comes with some example spreadsheets to show how you can use the code. If you get 'lost' please us fx on the formula editor.
You should then see a dialog like the following
There is an explanation of the purpose of each variable, in this example SourceCrs is highlighted. Clicking Help on this function will bring up the precompiled help file with extra information on how to use this function.
Get all identifier information from all contributing transform-steps in one go. Therefore, implement :
string TL.cct.Identifiers = string.Join(", ", transform.Identifiers);Or even better :
string ids = transform.Identifier?.ToString();
string scopes = transform.Scope;
if (ids == null && transform is CoordinateTransformList ctl)
{
ids = string.Join(", ", ctl.Where(x => x.Identifiers != null).SelectMany(x => x.Identifiers));
scopes = string.Join(", ", ctl.Select(x => x.Scope).Where(x => x is not null).Distinct());
}Do a similar thing for Scope and Remarks. . .