How to create an Excel add-in that reads user data?

Question

I was tasked with creating an addin for Excel 2007 that reads data from a custom data source. I already have code that reads data. It is written in C # and returns objects. This is for the banking system, and I cannot give direct access to data. To receive updated data, a user identifier, password, and hardware key must be provided, which must be provided in the C # DLL.

My boss wants the data to be updated automatically once a minute or manually, using the Refresh feature provided by Excel. Therefore, I need the data to be displayed in Excel, as if it were from a standard database connection.

I looked around at the starting point, but I'm struggling to find some information that will help in this particular requirement. Can anyone help?

Answer 1

Start by downloading VSTO (Visual Studio Tools for Office) , this will allow you to create a C # Excel add-in add-in.

In Visual Studio, when you create a new project, you will see Office, and you can select Excel from it.

Start at this point, as soon as you do this, you can come back and ask more specific questions.

Some useful tips when working with Excel.

Select active sheet:

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

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

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

Set a value for the entire range:

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

You can get values ​​from a range in a 2-dimensional array, for example:

 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" 

Then you can 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 method to simultaneously update the entire range by first preparing the array 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 thing, but vice versa).

 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 will allow you to perform basic tasks in Excel, you can set values ​​and get values ​​and select ranges. If you have a more specific question, please come back.

Remember that arrays coming from Excel are based on 1st, not based on zero.

Answer 2

You can try using XLLoop . This allows you to create excel functions (UDF) in different languages ​​on the server. Thus, you can write a function that returns data - the user will use Shift-F9 to update.

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

By the way, I am working on a project, so let me know if you have any questions.

Answer 3

For automatic updates, you need to create a DLL that implements the Excel API RealTimeData (RTD) COM API. See https://support.microsoft.com/en-us/kb/285339 .

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