How do I create an Excel add-in that reads custom data?

I have been tas开发者_运维技巧ked with creating an addin for Excel 2007 that reads data from a custom data source. I already have the code that reads the data. It's written in C# and returns objects. This is for a banking system and I cannot give direct access to the data. A userID, password and hardware generated key must be provided to the C# DLL to get up to date data.

My boss wants the data to be updated automatically once per minute or manually using the Refresh function provided by Excel. So I need to make the data appear in Excel as if it were from a standard database connection.

I've looked around for a starting point but I am struggling to find some information that will help with this very specific requirement. Can anyone help?

Begin with downloading VSTO (Visual Studio Tools for Office) this will allow you to created a C# Excel Add-In.

In Visual Studio when you create a new project you will see Office and you will be able to select Excel from it.

Start from there, once you do that you can come back and ask more specific questions.

Some useful tips with working with Excel.

To Select an Active Sheet:

Excel.Worksheet sheet = this.Application.ActiveSheet as Excel.Worksheet;

To Select a specific range (A1 - B5 in this case):

Excel.Range range = sheet.get_Range("A1", "B5") as Excel.Range;

To Set a value to the whole range:

 range.Value2 = 2; //will set every cell in A1 through B5 to 2

You can get values from range in a 2 dimensional array, for instance:

object[,] values = range.Value2 as object[,];
//this will return an multidimensional array representing rows and cols
//as you see in the range. in this case the array is filed with "2"

You can then change the values in the whole array and apply back to the range:

   values[2, 2] = 4;  //will change the value to 4 in row 2, col 2 of the *range*
   range.Value2 = values; //set back the whole range to the array

You can use this technique to update the whole range at once, by preparing an array first and then setting it to the range values.

To get a value from a specific cell in your range (To set a value is the same way but reverse).

Excel.Range cell = range.Cells[1,1] as Excel.Range; //this will take the cell from row 1, cell 1. if you used array this would be values[1,1]
string value = (cell.Value2 ?? "").ToString();

This should allow you to do basic tasks in Excel, you can set values and get values and select ranges. Once you have more specific question, please come back.

Don't forget, arrays coming from Excel are 1-based not zero-based !!!

You could try using XLLoop. This allows you to create excel functions (UDFs) in various languages on a server. So you could write function that returns the data - the user would use Shift-F9 to refresh.

There is a basic C# server implementation here: http://winrun4j.cvs.sourceforge.net/viewvc/winrun4j/org.boris.xlloop/servers/csharp/

BTW, I work on the project so let me know if you have any questions.

To auto-update, you will want to create a DLL that implements the Excel RealTimeData (RTD) COM API. See https://support.microsoft.com/en-us/kb/285339.

Since you want to do it in C#, Excel DNA (https://exceldna.codeplex.com/) is the easiest place to start and will do most of the work for you.