Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi,
 
I have a requirement to use the template of an excel sheet placed on the server. When we click on Generate Report button on aspx page, the program should take a copy of the template and write data to it and give a download popup. After the file is downloaded the copy should be deleted in order to prevent the program storing files on the server.
 
I could do it with excel MIME type and rendering as excel but formatting is becoming very much difficult.
 
The reason for using template here is, it has lot of formatting work in it and also it contains a lot of conditional formatting rules.
 
I could use the template and write data to it and save a copy of it on the server using excel interop model. But the copy should not reside on the server permanently, it should be available only till the download completes.
 
How can I do this?
 
Thanks for your help..
Posted 3-Nov-11 3:49am
Edited 3-Nov-11 3:54am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Create a process that clears the files when a session ends or set it as a timer job that clears files at a certain time every day, or several times a day
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Got a solution.
ApplicationClass excelApplication = null;
        Workbook newWorkbook = null;
        Worksheet targetSheet = null;
 
        string paramWorkbookPath = Server.MapPath("~/Files/Template.xls");
        string paramWorkbookPathNew = Server.MapPath("~/Files/" + System.Guid.NewGuid().ToString() + ".xls");
        object paramMissing = Type.Missing;
 
        excelApplication = new ApplicationClass();
        
        newWorkbook = excelApplication.Workbooks.Open(paramWorkbookPath, 1,
            false, paramMissing, paramMissing, paramMissing, false, paramMissing, paramMissing, false, true,
            paramMissing, false, true, XlCorruptLoad.xlNormalLoad);
 
        targetSheet = (Worksheet)(newWorkbook.Worksheets[1]);
        
        //Write data to the sheet
        targetSheet.get_Range("A7:E7", paramMissing).Value2 = "Srikanth";
        targetSheet.get_Range("A8:C8", paramMissing).Value2 = "Not available";
 
        newWorkbook.SaveAs(paramWorkbookPathNew, paramMissing, paramMissing, paramMissing, paramMissing, 
            paramMissing, XlSaveAsAccessMode.xlShared, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
        
        // Release the references to the Excel objects.
        targetSheet = null;
 
        // Close the Workbook object.
        if (newWorkbook != null)
        {
            newWorkbook.Close(false, paramMissing, paramMissing);
            newWorkbook = null;
        }
 
        // Close the ApplicationClass object.
        if (excelApplication != null)
        {
            excelApplication.Quit();
            excelApplication = null;
        }
 
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
 
        Response.ClearContent();
        Response.AddHeader("Content-Disposition", "attachment;filename = KPI.xls");
        Response.ContentType = "application/vnd.ms-excel";
        //Transmit the excel file
        Response.TransmitFile(paramWorkbookPathNew);
        Response.Flush();
        //Delete the file
        System.IO.File.Delete(paramWorkbookPathNew);
 
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 295
1 OriginalGriff 273
2 Aajmot Sk 234
3 Marcin Kozub 205
4 Richard MacCutchan 200
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,127
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 4 Nov 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100