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