Excel Program using C# VSTO

Question

I have created C# Excel Addin project, in which I have added a User control to that excel and placing a button in User Control . For the button , the code I have written for the getting data from database to Excel cells(sheet1). But the problem encountered here, when I click on button a new excel sheet (application) is opened and data is populated to to new excel sheet from database. But I want to populate my data from database to existing excel sheet only, but not for new excel sheet. Can you one help me?

I should get data in same excel only not for the new excel sheet. Here my code below:-

worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
app.Visible = true;---- **here i am getting new excel application  and data is getting populated**
worksheet.Name = "Export to Excel";
Excel.Range mycell = Excel.Range)worksheet.get_Range("A1:B1","A1:B1");

int CountColumn = ds.Tables[0].Columns.Count;
int CountRow = ds.Tables[0].Rows.Count;
int col = 0;
int row = 1;
string data = null;
int i = 0;
int j = 0;
for (col = 0; col < CountColumn; col++)
{
    worksheet.Cells[row, col + 1] = ds.Tables[0].Columns[col].ColumnName.ToString();
}

}

Can you add the code how you set the object workbook in your code? — Kiru, Apr 27 '12 at 10:22

score 1 · Answer 1 · edited May 23 '17 at 12:21

1

not sure why you are using Excel Application object here i.e app, rather in addin project you must use Globals.ThisAddin.Application.ActiveSheet/ Globals.ThisAddin.Application.ActiveWorkBook to get active sheet/workbook. Incase you do not have access to Globals in your wpf project, the correct way to get it you can find at post Get the excel cell address from a UDF, see if this helps

  var worksheet = Globals.ThisAddin.Application.ActiveSheet
        worksheet.Name = "Export to Excel"; 
        Excel.Range mycell = (Excel.Range)worksheet.get_Range("A1:B1","A1:B1");  
        int CountColumn = ds.Tables[0].Columns.Count; 
        int CountRow = ds.Tables[0].Rows.Count; 
        int col = 0; 
        int row = 1; 
        string data = null; 
        int i = 0; 
        int j = 0; 
        for (col = 0; col < CountColumn; col++) 
        { 
            worksheet.Cells[row, col + 1] = ds.Tables[0].Columns[col].ColumnName.ToString(); 
        }

edited May 23 '17 at 12:21

Community

1
1

answered Apr 28 '12 at 06:08

Brijesh Mishra

2,738
1
21
36

I have used the this lines before they are missed away:- See below them Excel.Application app = new Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Worksheet worksheet = null; worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; app.Visible = true; – rider Apr 30 '12 at 05:57
Excel.Application app = new Excel.Application(); will create new excel application and hence worksheet you get through it will be in new app. try Globals.ThisAddin.Application.ActiveSheet instead pass it as parameter to your wpf application – Brijesh Mishra Apr 30 '12 at 06:06
Thanks for reply.But i was not using any wpf Application. I was using User Control with button. i have wrote code for a button of above what i have given you to display data in my excel sheet. But the data is populating int to another worksheet. so please can you rewrite code and help me back. Thanks once again. Can you please – rider Apr 30 '12 at 07:32
HI Brijesh, i want one more help , i want to update the database using Excel addin i.e, it is vice versa of above code. Can you help in this ie., using c# code i want to update the database table using Excel Sheet. When i changes some data from excel cells it should update the database table. Can you please help in this. Once again thanks for above code. – rider Apr 30 '12 at 10:04
for this you can create update button, once the user is done making all the change he click update, you can write code in update click to get data from excel sheet and write in database. Alternatively you can make it real time by hooking cell's on change eveent but this can be heavy on sytem and can slow down excel operations. – Brijesh Mishra Apr 30 '12 at 10:15
see this code of statements brijesh is it helpful i should maintain anyother i tried but from excel it is not getting update through the code it is getting update and changing in the database sql = "Update [xxx] set Date ='" + date + "' where FirstName= '" + FirstName + "' "; cmd.CommandText = sql; cmd.ExecuteNonQuery(); – rider Apr 30 '12 at 10:26
cant invite you to chatroom to discuss more on this, better can you post new question for this with detaails about how you are calling update function and also paste there the complete update function – Brijesh Mishra Apr 30 '12 at 10:35

score 0 · Answer 2 · answered Apr 28 '12 at 16:34

By definition, when you are working with an Excel add-in, Excel IS open already, so there is no point in trying to get a new instance started. As Brijesh mentioned, Excel add-ins give you access to the current Excel instance by using Globals.ThisAddin.Application, and from there you can then access the entire object model.

As an aside, it looks like you are writing your data to the sheet by iterating cell by cell. This is a bad idea if you have lots of data, you should be able to write it in one shot.

Excel Program using C# VSTO

2 Answers2