Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
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
Rate this: bad
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
Rate this: bad
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 = null;
        Response.AddHeader("Content-Disposition", "attachment;filename = KPI.xls");
        Response.ContentType = "application/";
        //Transmit the excel file
        //Delete the file

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

  Print Answers RSS
0 OriginalGriff 320
1 Peter Leow 155
2 ProgramFOX 145
3 DamithSL 140
4 Zoltán Zörgő 130
0 OriginalGriff 160
1 ProgramFOX 105
2 Sergey Alexandrovich Kryukov 60
3 Mehdi Gholam 50
4 DamithSL 50

Advertise | Privacy | Mobile
Web04 | 2.8.150224.1 | Last Updated 4 Nov 2011
Copyright © CodeProject, 1999-2015
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