End-user computing empowers non-developers to manage data and applications, enhancing collaboration and efficiency. Spreadsheets, a prime example of end-user programming environments widely used in business for data analysis. However, Excel functionalities have limits compared to dedicated programming languages. This paper addresses this gap by proposing a prototype for integrating Python’s capabilities into Excel through on-premises desktop to build custom spreadsheet functions with Python. This approach overcomes potential latency issues associated with cloud-based solutions. This prototype utilizes Excel-DNA and IronPython. Excel-DNA allows creating custom Python functions that seamlessly integrate with Excel’s calculation engine. IronPython enables the execution of these Python (CSFs) directly within Excel. C# and VSTO add-ins form the core components, facilitating communication between Python and Excel. This approach empowers users with a potentially open-ended set of Python (CSFs) for tasks like mathematical calculations, statistical analysis, and even predictive modeling, all within the familiar Excel interface. This prototype demonstrates smooth integration, allowing users to call Python (CSFs) just like standard Excel functions. This research contributes to enhancing spreadsheet capabilities for end-user programmers by leveraging Python’s power within Excel. Future research could explore expanding data analysis capabilities by expanding the (CSFs) functions for complex calculations, statistical analysis, data manipulation, and even external library integration. The possibility of integrating machine learning models through the (CSFs) functions within the familiar Excel environment.
References
[1]
Global Market Insights (2023) End-User-Computing-EUC-Market. https://www.gminsights.com/industry-analysis/end-user-computing-euc-market
[2]
Borghouts, J., Gordon, A.D., Sarkar, A. and Toronto, N. (2019) End-User Probabilistic Programming. Quantitative Evaluation of Systems, Glasgow, 10-12 September 2019, 3-24. https://doi.org/10.1007/978-3-030-30281-8_1
[3]
Tallis, M., Waltzman, R. and Blazer, R. (2007) Adding Deductive Logic to a Cots Spreadsheet. The Knowledge Engineering Review, 22, 255-268. https://doi.org/10.1017/s0269888907001166
[4]
Microsoft (2023) Announcing Python in Excel: Combining the Power of Python and the Flexibility of Excel. https://techcommunity.microsoft.com/t5/excel-blog/announcing-python-in-excel-combining-the-power-of-python-and-the/ba-p/3893439
[5]
Győrödi, R., Pavel, M.I., Győrödi, C. and Zmaranda, D. (2019) Performance of OnPrem versus Azure SQL Server: A Case Study. IEEE Access, 7, 15894-15902. https://doi.org/10.1109/ACCESS.2019.2893333
[6]
NET Foundation (2023) Ironpython. https://ironpython.net/
[7]
Turk, T. (2021) SDFunc: Modular Spreadsheet Design with Sheet-Defined Functions in Microsoft Excel. Journal of Software: Practice and Experience, 52, 415-426. https://doi.org/10.1002/spe.3027
[8]
Microsoft (2023) Architecture of VSTO Add-Ins. https://learn.microsoft.com/en-us/visualstudio/vsto/architecture-of-vsto-add-ins?view=vs-2022
[9]
Dwivedi, V. (2022) Halo: A Framework for End-User. Carnegie Mellon University, Pittsburgh.
[10]
Ko, A.J., Abraham, R., Beckwith, L., Blackwell, A., Burnett, M., Erwig, M., Scaffidi, C., Lawrance, J., Lieberman, H., Myers, B., Rosson, M.B., Rothermel, G., Shaw, M. and Wiedenbeck, S. (2011) The State of the Art in End-User Software Engineering. ACM Computing Surveys, 43, Article No. 21. https://doi.org/10.1145/1922649.1922658
[11]
Chambers, C., Erwig, M. and Luckey, M. (2010) Sheetdiff: A Tool for Identifying Changes in Spreadsheets. 2010 IEEE Symposium on Visual Languages and Human-Centric Computing, Leganes, 21-25 September 2010, 85-92. https://doi.org/10.1109/VLHCC.2010.21
[12]
Bock, A. and Biermann, F. (2019) Puncalc: Task-Based Parallelism and Speculative Reevaluation in Spreadsheets. The Journal of Supercomputing, 76, 4977-4997. https://doi.org/10.1007/s11227-019-02823-8
[13]
Hermans, F., Pinzger, M. and Deursen, A. (2011) Supporting Professional Spreadsheet Users by Generating Leveled Dataflow Diagrams. Proceedings of the 33rd International Conference on Software Engineering, Honolulu, 21-28 May 2011, 451-460. https://doi.org/10.1145/1985793.1985855
[14]
Abraham, R., Burnett, M. and Erwig, M. (2009) Spreadsheet Programming. In: Wah, B.W., Ed., Wiley Encyclopedia of Computer Science and Engineering, John Wiley & Sons, Hoboken. https://doi.org/10.1002/9780470050118.ecse415
[15]
McCutchen, M., Borghouts, J., Gordon, A., Jones, S. and Sarkar, A. (2020) Elastic Sheet-Defined Functions: Generalising Spreadsheet Functions to Variable-Size Input Arrays. Journal of Functional Programming, 30, e26. https://doi.org/10.1017/S0956796820000234
[16]
Klasson, K.T. (2018) QXLA: Adding Upper Quantiles for the Studentized Range to Excel for Multiple Comparison Procedures. Journal of Statistical Software, 85, 1-9. https://doi.org/10.18637/jss.v085.c01
[17]
Bartholomew, P. (2023) Excel as a Turing-Complete Functional Programming Environment. arXiv:2309.00115. https://doi.org/10.48550/arXiv.2309.00115
[18]
Nassereldine, A., Chen, P. and Xiong, J. (2022) Excel Spreadsheet Analyzer. https://arxiv.org/abs/2211.06333
[19]
McKinney, W. (2011) pandas: A Foundational Python Library for Data Analysis and Statistics. https://docslib.org/doc/4231522/a-foundational-python-library-for-data-analysis-and-statistics
Introduction to PyXLL. https://www.pyxll.com/docs/introduction.html
[24]
Fatra, R., Flodin, E., Bawono, C., Arshanda, M., Rivano, F. and Rachmanto, D. (2020) Teasing Insight Out of Reams of Data Using Advanced Data Visualization and Analytics Software for Improved Reservoir Management, Rokan Light Oil, Indonesia. SPE/IATMI Asia Pacific Oil & Gas Conference and Exhibition, Bali, 29-31 October 2019. https://doi.org/10.2118/196318-MS
