如何在导入后将数据写入excel并删除文件? [英] How to write data to excel and delete the file after import?

问题描述

我需要使用放置在服务器上的Excel工作表的模板.当我们单击aspx页面上的Generate Report按钮时，该程序应获取模板的副本并向其中写入数据并给出下载弹出窗口.下载文件后，应删除副本，以防止程序将文件存储在服务器上.

我可以使用excel MIME类型并将其呈现为excel，但是格式化变得非常困难.

在这里使用模板的原因是，它具有很多格式化工作，并且还包含很多条件格式化规则.

我可以使用模板向其中写入数据，并使用excel interop模型将其副本保存在服务器上.但是该副本不应永久地驻留在服务器上，只有在下载完成之前该副本才可用.

我该怎么办?

感谢您的帮助..

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..

推荐答案

创建一个在会话结束时清除文件的进程，或将其设置为每天在特定时间清除文件的计时器作业. ，或每天几次

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

找到解决方案.

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);

这篇关于如何在导入后将数据写入excel并删除文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！