We often use Excel during working to record or deal with data for it has a powerful function to calculate data. Usually, we export or update data to Excel manually. It will be a big project if there is thousand of information needed to deal with. VBA macro helps users to realize Excel automation by using object and offers kinds a large number of methods and properties of each object class to control Excel. However, it is also a trouble when we want to get data from outside source even if using VBA. In order to export data from database to Excel conveniently, C# .Net in Excel is used.
How to use C# .Net to populate the data in the Active Worksheet?
Firstly, we need to get the Office 2003 beta kit form and office add-on for Visual Studio.Net 2003. After opening Visual.Net, select an Excel Workbook project in new project. Then, the Microsoft Office Project Wizard window appears and prompts if you want to create a new Excel Workbook or use an existing one. We choose to create a new one and finish it. Now, we can open a new blank workbook and then import some useful data to it by adding some code.
At the beginning, we need to use ThisWorkbook_Open method which will run on the moment we open the workbook.
protected void ThisWorkbook_Open()
{
Excel.Worksheet ws=new Excel.WorksheetClass();
ws = (Excel.Worksheet)ThisApplication.ActiveWorkbook.ActiveSheet;
for(int i=1;i<11;i++)
{
for(int j=1;j<11;j++)
{
ws.Cells[i,j]=i;
}
}
}
According to the code, WorksheetClass is used and the ActiveSheet is retrieved. Then, set cell value by row and column.
In ws = (Excel.Worksheet)ThisApplication.ActiveWorkbook.ActiveSheet; sentence, (Excel.Worksheet) is used to cast the object to a worksheet because .ActiveSheet returns an object but not a worksheet object.
Run the code and we can get a completed Excel workbook with data. And every time, the code will be run once we open this workbook in C# .Net.