Microsoft Excel spreadsheet used as a computing engine called code
I have a MS Excel spreadsheet that does some complex calculations. I would like to create a script that will create a CSV file with the results obtained from a spreadsheet.
I could rewrite the logic from the spreadsheet in my programming language (for example Ruby, but I'm open to using another language), but then I'll have to update my code whenever someone changes the logic in the spreadsheet. Can a MS Excel spreadsheet be used as a black box, computing engine that can be called from my code? Then I would only write the CSV part and loading the input data in my code, all the calculation logic could remain in the spreadsheet and could be easily updated.
Ideally, I don't want to add the CSV generation code or data loading code to the spreadsheet as it is used by domain experts (not programmers). Also, I have to download some data from the internet and feed it into a spreadsheet as input values. I would like to store this piece of code externally, in a version control system like Git. Another note: the spreadsheet uses the Solver Excel plugin.
Any help on how to do this would be much appreciated.
Thank you Michal
source to share
To manipulate your Excel spreadsheet using Ruby you can use win32ole
Here's an example script:
data = [["Hello", "World"]]
# Require the WIN32OLE library
require 'win32ole'
# Create an instance of the Excel application object
xl = WIN32OLE.new('Excel.Application')
# Make Excel visible
xl.Visible = 1
# Add a new Workbook object
wb = xl.Workbooks.Add
# Get the first Worksheet
ws = wb.Worksheets(1)
# Set the name of the worksheet tab
ws.Name = 'Sample Worksheet'
# For each row in the data set
data.each_with_index do |row, r|
# For each field in the row
row.each_with_index do |field, c|
# Write the data to the Worksheet
ws.Cells(r+1, c+1).Value = field.to_s
end
end
# Save the workbook
wb.SaveAs('workbook.xls')
# Close the workbook
wb.Close
# Quit Excel
xl.Quit
To develop more complex code, just write a macro of what you want to do and then look at your macro code and convert it from VB to Ruby.
source to share