End-user software engineering approach: improve spreadsheets capabilities using Python-based user-defined functions

Abstract

End-user computing enables non-developers to handle data and applications, boosting collaboration and productivity. Spreadsheets are a key example of end-user programming environments that are extensively utilized in business for data analysis. However, the functionalities of Excel have limitations compared to specialized programming languages. This study aims to address this shortcoming by proposing a prototype that integrates Python's features into Excel via standalone desktop Python-based user-defined functions (UDFs). This method mitigates potential latency concerns linked to cloud-based solutions. This study employs Excel-DNA (dynamic network access) and IronPython; Excel-DNA facilitates the creation of custom Python functions that integrate smoothly with Excel's calculation engine, while IronPython allows these Python UDFs to run directly within Excel. Core components include C# and visual studio tools office (VSTO) add-ins, which enable communication between Python and Excel. This approach grants users the chance to execute Python UDFs for various tasks such as mathematical computations and predictions — all within the familiar Excel environment. The prototype showcases seamless integration, enabling users to invoke Python-based UDFs just like built in Excel functions. This study enhances the capabilities of spreadsheets by harnessing Python's strengths within Excel. Future work may focus on expanding the Python UDF library and examining user experiences with this innovative approach to data analysis.