如何使用epplus写入打开的Excel电子表格？ [英] How do I write to an open excel spreadsheet using epplus?

问题描述

我有一个ASP.Net Web窗体站点，用户可以在其中将值写入Excel电子表格。我能够允许用户打开保存在服务器上的电子表格副本并在Excel上打开它（所有用户都可以访问Excel）。当用户用按钮打开文件时，我能够让用户正确地写入文件。问题是用户打开后无法写入文件。我在下面添加了一些代码来说明我的问题。



工作代码：

 protected void openExcel（object sender，EventArgs e）
 {
 string xlsxpath = Server.MapPath（〜/ new_takeoff.xlsx）; 
 fi = new FileInfo（Server.MapPath（〜/ new_takeoff.xlsx））; 
 excelPackage =新ExcelPackage（fi）; 
 first = excelPackage.Workbook.Worksheets [1]; 
 ExcelRange selected = first.SelectedRange; 
 first.Cells [I54]。值= 51; 
 excelPackage.Save（）; 
 
 System.Diagnostics.Process.Start（xlsxpath）; 
} 

正如你所看到的，我用相同的方法处理写入和打开（由一个按钮的OnClick实例处理）。



这些单独的方法（由单独的按钮处理）不起作用：

 protected void openExcel（object sender， EventArgs e）
 {
 string xlsxpath = Server.MapPath（〜/ new_takeoff.xlsx）; 
 
 System.Diagnostics.Process.Start（xlsxpath）; 
} 
 protected void updateIT（object sender，EventArgs e）
 {
 
 fi = new FileInfo（Server.MapPath（〜/ new_takeoff.xlsx）） ; 
 excelPackage =新ExcelPackage（fi）; 
 first = excelPackage.Workbook.Worksheets [1]; 
 ExcelRange selected = first.SelectedRange; 
 first.Cells [I54]。值= 51; 
 excelPackage.Save（）; 
 
} 
 

调试器告诉我无法访问该文件，因为它正由另一个进程使用。



顺便说一句，我需要打开文件，因为用户必须选择要填充的单元格。后面的C＃代码使用EPPlus库中的SelectedRange选择所选的ExcelRange。



我尝试过：



我尝试使用Microsoft.Office.Interop.Excel dll，但当然这对网站不起作用。

解决方案

< blockquote>在Web应用程序中，你没有。



你显然很难理解浏览器和Web服务器的断开性质以及运行的代码哪里。



再次，每个客户都必须下载Excel工作簿文件并在那里打开它。服务器将无法更新每个客户端在工作簿副本中看到的内容。



部署到真实Web服务器时，这些代码都不起作用。它在您的机器上工作的唯一原因是因为您的机器同时是服务器和客户端！



如果您正在更改Excel工作簿在服务器上，您必须确保没有多个客户端请求同时触发对同一工作簿的修改。第一个打开文件的请求将被锁定，因此进入服务器的所有其他后续请求将无法打开该文件，直到第一个请求关闭文件为止。



如果您需要为每个用户自定义相同的Excel工作簿，则必须复制原始Excel文件，为该副本提供唯一名称。您可以使用EPPlus打开副本，进行更改并保存。然后，您可以将该更新的文件作为下载返回给客户端。



EPPlus可用于在服务器上构建Excel工作簿，但它无法在客户端计算机上使用实时数据更新工作表。您的服务器代码永远不会访问客户端上运行的任何内容，包括Excel工作簿的打开副本。

I have an ASP.Net Web Forms site where a user can write values to an Excel Spreadsheet. I was able to allow users to open a copy of the spreadsheet saved on the server and open it on Excel (all users have access to Excel). I was able to have the user write to the file right as they open it with a button. The problem is that the user can't write to the file after it is open. I added some code below to illustrate my problem.

Working code:

protected void openExcel(object sender, EventArgs e)
    {
        string xlsxpath = Server.MapPath("~/new_takeoff.xlsx");
        fi = new FileInfo(Server.MapPath("~/new_takeoff.xlsx"));
        excelPackage = new ExcelPackage(fi);
        first = excelPackage.Workbook.Worksheets[1];
        ExcelRange chosen = first.SelectedRange;
        first.Cells["I54"].Value = 51;
        excelPackage.Save();

        System.Diagnostics.Process.Start(xlsxpath);
    }

As you can see I took care of writing and opening in the same method (handled by an OnClick instance of a button).

These separate methods (handled by separate buttons) do not work:

protected void openExcel(object sender, EventArgs e)
{
    string xlsxpath = Server.MapPath("~/new_takeoff.xlsx");

    System.Diagnostics.Process.Start(xlsxpath);
}
protected void updateIT(object sender, EventArgs e)
{

    fi = new FileInfo(Server.MapPath("~/new_takeoff.xlsx"));
    excelPackage = new ExcelPackage(fi);
    first = excelPackage.Workbook.Worksheets[1];
    ExcelRange chosen = first.SelectedRange;
    first.Cells["I54"].Value = 51;
    excelPackage.Save();

}

The debugger tells me that the file cannot be accessed because it is being used by another process.

By the way, I need the file open because the user has to pick what cell to fill. The C# code behind picks up the chosen ExcelRange using SelectedRange in the EPPlus library.

What I have tried:

I tried using the Microsoft.Office.Interop.Excel dll, but of course that doesn't work for a website.

解决方案

In a web application, you don't.

You're apparently having a real hard time understanding the disconnected nature of browsers and web servers and what code runs where.

AGAIN, each client will have to download the Excel workbook file and open it there. The server will NOT be able to update what each client sees in their copy of the workbook.

None of that code will work when deployed to a real web server. The only reason it works on your machine is because your machine is both the server AND the client at the same time!

If you're making changes to an Excel workbook on the server, you have to make sure you don't have multiple client requests all triggering modifications to the same workbook at the same time. The first request to get the file open will have it locked so all other subsequent requests that come into the server will fail to open that file until the first request closes the file.

If you need to customize the same Excel workbook for each user, you're going to have to make a copy of the original Excel file, giving the copy a unique name. You them open the copy using EPPlus, make the changes and save it. Then you can return that updated file to the client as a download.

EPPlus can be used to build an Excel workbook on the server, but it can NOT update the sheet with live data on client machines. Your server code will NEVER have access to anything running on the client, including an "open" copy of an Excel workbook.

这篇关于如何使用epplus写入打开的Excel电子表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！