openmdao-bridge-excel
An OpenMDAO component for running analyses in Excel.
Science Score: 54.0%
This score indicates how likely this project is to be science-related based on various indicators:
-
✓CITATION.cff file
Found CITATION.cff file -
✓codemeta.json file
Found codemeta.json file -
✓.zenodo.json file
Found .zenodo.json file -
○DOI references
-
✓Academic publication links
Links to: zenodo.org -
○Committers with academic emails
-
○Institutional organization owner
-
○JOSS paper metadata
-
○Scientific vocabulary similarity
Low similarity (13.3%) to scientific vocabulary
Keywords
Repository
An OpenMDAO component for running analyses in Excel.
Basic Info
Statistics
- Stars: 0
- Watchers: 2
- Forks: 0
- Open Issues: 1
- Releases: 2
Topics
Metadata Files
README.md
OpenMDAO Bridge for Excel
A reusable component for running Excel analyses from OpenMDAO. It uses xlwings to communicate with Excel and works through the following procedure:
1. Start Excel.
2. Open a pre-defined workbook file.
3. Optional: Run pre-calculation macros.
4. Set the input variables in corresponding ranges/cells.
5. Optional: Run main macros.
6. Read values of the output ranges/cells and assign to output variables.
7. Optional: Run post-calculation macros.
8. Close the workbook without saving it.
Features:
* Cells can be addressed by named ranges (use them!) or regular means (e.g. C3).
* Timeout handling by mercilessly killing the Excel process after a specified number of seconds.
* The Excel process will be kept alive between evaluations, unless the previous one timed out.
* The macro runner will try to catch and log macro errors, raising an OpenMDAO AnalysisError.
* Rudimentary logging using the Python logging system.
Non-features/pitfalls/known issues: * "Trust access to the VBA project object model" must be enabled. * MacOS has not been tested and will probably not work. * Non-scalar values (i.e. ranges with more than one cell) have not been tested and will probably not work. * Multi-processing (i.e. MPI) has not been tested, but might work. * Excel has a few numeric quirks and you are encouraged to be very critical of your results. I personally don't even recommend using Excel in this context, unless you really have to. * The timeout handler runs in a separate Python thread. This shouldn't be a problem, but you might want to know.
Compatibility
The component is regularly-ish tested on the latest stable versions of: * Windows 10, 64-bit * Microsoft Excel, 32-bit * Python 3 * OpenMDAO
I wish I could set up a CI pipeline to make this process more transparent and repeatable, but Excel being a proprietary product makes this more or less impossible.
Installation
sh
pip install git+https://github.com/ovidner/openmdao-bridge-excel.git#egg=openmdao_bridge_excel
Usage example
```python import openmdao.api as om from openmdaobridgeexcel import ExcelComponent, ExcelVar
prob = om.Problem()
...
excelcomp = prob.addsubsystem("excel", ExcelComponent(
filepath="absolute/or/relative/path/to/file.xlsm",
premacros=["foo", "bar"],
mainmacros=["main", "foo"],
postmacros=["cleanup"],
inputs=[
ExcelVar("in1", "Sheet1!C2"),
ExcelVar("in2", "NamedRangesAreSupported"),
],
outputs=[
ExcelVar("out1", "B1"),
ExcelVar("out2", "AnotherNamedRange"),
],
timeout=60, # One minute
))
...
``
Input/output variables can then be addressed on the OpenMDAO component as usual (e.g.excel.in1,excel.out2`).
Development environment setup
This is automagically handled with anaconda-project:
sh
anaconda-project run setup
You should then be able to run the tests:
sh
anaconda-project run pytest
Owner
- Name: Olle Vidner
- Login: ovidner
- Kind: user
- Location: Linköping, Sweden
- Company: Linköping University @DesignAutomationLaboratory
- Website: https://se.linkedin.com/in/ovidner
- Repositories: 61
- Profile: https://github.com/ovidner
PhD student, mechanical engineer and developer. I like coffee, optimization, automation, the question "why?" and German easy-listening music from 1967–1973.
Citation (CITATION.cff)
cff-version: 1.2.0
message: "If you use this software, please cite it using the metadata supplied in the CITATION.cff file."
doi: 10.5281/zenodo.4501330
title: "OpenMDAO-Bridge-Excel"
authors:
- family-names: Vidner
given-names: Olle
affiliation: Linköping University
orcid: https://orcid.org/0000-0002-1157-2480
license: MIT
repository-code: https://github.com/ovidner/openmdao-bridge-excel
GitHub Events
Total
Last Year
Committers
Last synced: about 1 year ago
Top Committers
| Name | Commits | |
|---|---|---|
| Olle Vidner | o****e@v****e | 30 |
Committer Domains (Top 20 + Academic)
Issues and Pull Requests
Last synced: 11 months ago
All Time
- Total issues: 1
- Total pull requests: 0
- Average time to close issues: N/A
- Average time to close pull requests: N/A
- Total issue authors: 1
- Total pull request authors: 0
- Average comments per issue: 0.0
- Average comments per pull request: 0
- Merged pull requests: 0
- Bot issues: 0
- Bot pull requests: 0
Past Year
- Issues: 0
- Pull requests: 0
- Average time to close issues: N/A
- Average time to close pull requests: N/A
- Issue authors: 0
- Pull request authors: 0
- Average comments per issue: 0
- Average comments per pull request: 0
- Merged pull requests: 0
- Bot issues: 0
- Bot pull requests: 0
Top Authors
Issue Authors
- ovidner (1)
Pull Request Authors
Top Labels
Issue Labels
Pull Request Labels
Dependencies
- openmdao *