Click here to Skip to main content
12,999,744 members (41,147 online)
Rate this:
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 2:49am
Updated 3-Nov-11 2: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
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170624.1 | Last Updated 4 Nov 2011
Copyright © CodeProject, 1999-2017
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