Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
How to create and manipulate (read/write) Excel (XLSX) documents with complex rules using general programming languages?
Our organization (blindness non-profit in California) is obligated to submit reports to grant organizations (e.g., Department of Rehabilitation) on a regular basis. Most of the time, this means exporting data from our case management system and copying it manually into an Excel template file containing several worksheets provided by the grant organizations. Data validation rules, summaries, etc. are all implemented with Excel functions; basically, the Excel files are treated as databases (I know, blasphemy).
I'm looking for projects written in general programming languages (so VBA is not an option) to automate this process of filling out the reports - or, when the provided Excel templates are protected and the org is not willing to accommodate this use case, to re-create them from scratch with the existing functionality.
1 answer
All of these projects use Microsoft's OpenXML SDK for Office behind the scenes:
1. .NET (F#, C#)
The F# Guide recommends NPOI, which also seems to be the most popular and most up-to-date, but there are others as well (ClosedXML, EPPlus).
2. Elixir
-
elixlsxseems to be only for manipulating existing Excel files and its documentation is very sparse. -
xlsxiron the other hand appears to be the full package and its documentation is in a somewhat better shape.
3. Python
-
openpyxl
There is a great guide by Real Python:A Guide to Excel Spreadsheets in Python Withopenpyxl. -
INFO
Requires Excel to be installed. Seexlwings's install docs.
0 comment threads
1 comment thread