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
– Kiru, Commented Apr 27, 2012 at 10:22

Community · Accepted Answer · 2017-05-23 12:21:17Z

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, 2017 at 12:21

CommunityBot

11 silver badge

answered Apr 28, 2012 at 6:08

Brijesh Mishra

2,7581 gold badge21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

rider Over a year ago

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;

Brijesh Mishra Over a year ago

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

rider Over a year ago

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 Over a year ago

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.

Brijesh Mishra Over a year ago

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.

|

Mathias · Accepted Answer · 2012-04-28 16:34:24Z

0

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.

answered Apr 28, 2012 at 16:34

Mathias

15.4k10 gold badges62 silver badges93 bronze badges

Collectives™ on Stack Overflow

Excel Program using C# VSTO

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related