Modifying Excel Worksheets using .NET

Quite a few times i have been working with Excel sheets for generating Data to load into warehouses. During Proof of Concept's(POC) most of us do not have enough time to clean real Data, so this application comes handy and helps to generate the data on the fly, which can then be loaded in the data warehouses.

I am sure there a lots of tutorials available online, which guides you through modifying the Excel sheets. But i did not find any tutorial which explains step by step to modify the excel worksheets. So this is my attempt to give a step by step description for beginners to modify their Excel application using . NET. So here i go....

The Prerequisites are basic understanding of .NET principles and any one programming language;i have used C# for my example.

Step 1:

In order to use the library functions which are available for working with Excel, we need to add a reference called Microsoft.Office.Core.Excel. This is available at the following link Microsoft XP PIA's. Then add the reference to your project/solution. Add this line

using Microsoft.Office.Interop.Excel;


Step 2:

We need to create an instance as shown


Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
//
if (xlApp == null)
{
Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
return;
}


Step 3:
Then we open a a workbook and then the worksheet as shown


Workbook wb1 = xlApp.Workbooks.Open("C:\\" + NameofFile,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
//
Worksheet ws1 = (Worksheet)wb1.Worksheets[1];
//
if (ws1 == null)
{
Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
}


Step 4:

Then the last step, accessing each cell in the worksheet


((Range)ws1.Cells[row_num, column_num]).Value2 = "Customer";
// E.g
((Range)ws1.Cells[1, 1]).Value2 = "Customer";
((Range)ws1.Cells[1, 2]).Value2 = "Target_Proceeds";
((Range)ws1.Cells[1, 3]).Value2 = "Target_Contribution";


Here we can dynamically change the values and do n number of things as we all programmers know :)

Step 5:

Finally we close the application and give the resources back to the system


xlApp.Workbooks.Close();


This application comes very handy when we want to transpose the data available to us. As many of us who has worked in Data Warehousing industry, converting rows into columns and vice versa is common issue. We can convert rows columns and vice versa using SQL also, but .NET is one more option who loves to do programming. I hope this is helpful and would make of our lives easier :)