If our finance department wants us to create a web app that pulls data from a spreadsheet calculation, based on user input, but also want that spreadsheet to be private from all developers working on the app, is this possible?
How do companies launch an app with sensitive information that they want to protect?
Can you access data from an excel spreadsheet that is security protected?
I would recommend the excel workbook update a table that you apply whatever security scheme you need to on. Also you can put the workbook in a location that only those with proper rights can access.
Easiest way to develop such an application (this is a high level answer of "how do develop an app when you can't see the classified or protected data" not "how do i make a web portal with an excel backend") is to get "dummy" data.
You make the dummy data just as protected, but give the developers the ability to see the data on the other side of the wall. You make sure the datatypes are the same (the only thing different should be values) and go from there. That's the easy way. After the app is developed with the dummy data, it's deployed and people with access to the data "connect the pipes" and away you go.
As to using an excel sheet as a backend to a website..please look into MySQL or MSSQL or some other database solution. The developer coming after you will thank you.
If you can't because of management..ripping open an excel file directly is not for the faint of heart, but microsoft does provide an api to access the insides. I've done it once..and don't remember how to off the top of my head, but perhaps someone more skilled remembers the api. The easier road is to have that workbook output to CSV (which is easily parsed).
If the user input is limited to a a field or two and the values are for easily determined values that doesn't span a huge range, then create a lookup table. In this table, you would store the results for all possible combinations of input values. This would allow for you to not putting the Excel spreadsheet on the Web server.