Web Application, C# managing more than one Excel at a time to consolidate and release.

See more:

C#2.0

C#

MS-Excel

interop

, +

Dear Friends'

I have one process in which i have to generate 7 Reports and Export in Excel format.
Once generation complete i have to consolidate this all Excel data in one single Excel file with required format change and Response to user.

All done on Web base application created using Dot Net 2.0 and C# language report using Crystal Report tool.

The issue occurs at the time of Consolidation, I am using Microsoft.Office.Interop.Excel.dll to Open two file at time (one Main and other Child) once Import and Formatting done I am using following code to Release that code and kill process, once all done the final Excel Save and close and pass to User.

All code try to manage as much as possible but still one of those file stuck and wont allow to generate next report (I have keep the name of file same as can't effort utilizing Server memory for this files which are not required once process complete as next time this file are overwrite by new file).

To Open

Excel.Application xlobj = new Excel.ApplicationClass();
Excel.Workbook w;//= new Excel.Workbook(); 
Excel.Worksheet s;

w = xlobj.Workbooks.Open(path, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
   true, false, 0, true, false, false);

s = (Excel.Worksheet)w.Worksheets.get_Item("Sheet1");

To Close

w.Save();
w.Save();
w.Close(true, path, Type.Missing);
Marshal.ReleaseComObject(w);

xlobj1.Quit();
TryKillProcessByMainWindowHwnd(xlobj1.Hwnd);
Marshal.ReleaseComObject(xlobj1);

[DllImport("user32.dll")]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);
public static bool TryKillProcessByMainWindowHwnd(int hWnd)
{
    uint processID;
    GetWindowThreadProcessId((IntPtr)hWnd, out processID);
    if (processID == 0)
        return false;
    try
    {
        System.Diagnostics.Process.GetProcessById((int)processID).Kill();
    }
    catch (ArgumentException) { return false; }
    catch (Win32Exception) { return false; }
    catch (NotSupportedException) { return false; }
    catch (InvalidOperationException) { return false; } return true;
}

I Just bang my head from last two days to make this code stream line, but in vain, help me if you have any best process and let me know.

I am thinking to create one window application which kill all those unwanted not in use Excel file from Task Manager but this is not logical but still searching for that as need to make customer happy.

Thanks and Regard's
Sasmi