I resolved this issue with the following steps

1. Login to the server as an administrator.
2. Go to “Start” -> “Run” and enter “taskmgr”
3. Go to the process tab in task manager and check “Show Processes from all users”
4. If there are any “Excel.exe” entries on the list, right click on the entry and select “End Process”
5. Close task manager.
6. Go to “Start” -> “Run” and enter “services.msc”
7. Stop the service automating Excel if it is running.
8. Go to “Start” -> “Run” and enter “dcomcnfg”
9. This will bring up the component services window, expand out “Console Root” -> “Computers” -> “DCOM Config”
10. Find “Microsoft Excel Application” in the list of components.
11. Right click on the entry and select “Properties”
12. Go to the “Identity” tab on the properties dialog.
13. Select “The interactive user.”
14. Click the “OK” button.
15. Switch to the services console
16. Start the service automating Excel
17. Test you application again.

1. Add Reference to Visual Studio by righ clicking the “Reference” folder in the project, click “Add Reference” and choose “Microsoft Excel 11.0 Object Library” in COM tab

2. do the coding like the following example,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection; 
				

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application excel = new Excel.Application();
            Excel._Workbook wBook = excel.Workbooks.Open(@"C:\Users\allenk\Documents\Visual Studio 2010\Projects\ConsoleApplication1\ConsoleApplication1\bin\Debug\Sample_V1.1.xls");
            Excel._Worksheet wSheet = (Excel._Worksheet)wBook.Worksheets["Data"];
            
            // cleare data worksheet
            // cannot use this as this will remove the reference of pivot table
            wSheet.Columns["A:F"].Clear();
            wSheet.Cells[1, 1].Value = "Location";
            wSheet.Cells[1, 2].Value = "Year";
            wSheet.Cells[1, 3].Value = "Season";
            wSheet.Cells[1, 4].Value = "Division";
            wSheet.Cells[1, 5].Value = "Rec Qty";
            wSheet.Cells[1, 6].Value = "Ord Qty";

            // fill in data here
            for(int i=2;i<=100;i++)
            {
                wSheet.Cells[i, 1].Value = "Test";
                wSheet.Cells[i, 2].Value = "2010";
                wSheet.Cells[i, 3].Value = "08";
                wSheet.Cells[i, 4].Value = "Test";
                wSheet.Cells[i, 5].Value = 70;
                wSheet.Cells[i, 6].Value = 100;

            }

            // Refresh Pivot Table
            wBook.RefreshAll();
            
            // Save the workbook after editing the data
            wBook.Save();
            wBook.Close();
            excel.Quit();
        }
    }
}