Click here to Skip to main content
14,644,587 members
Rate this:
Please Sign up or sign in to vote.
See more:
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.
Posted
Updated 17-Oct-18 15:20pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
v2
Comments
Member 13396929 17-Oct-18 21:38pm
   
I understand what you are saying, but I feel like I'm just going in circles. I tried embedding the spreadsheet using the tutorial here: https://support.office.com/en-us/article/share-it-embed-an-excel-workbook-on-your-web-page-or-blog-from-onedrive-804e1845-5662-487e-9b38-f96307144081. It should allow the user to add values in the embedded sheet (edits don't need saving). From what you and others have said, I found that the code won't be able to write directly to the embedded sheet.

Should I just copy the value to the user clipboard, and have them paste it to the embedded sheet?
Dave Kreskowiak 17-Oct-18 22:46pm
   
You'd have to use Office 365 for that and store your workbooks in OneDrive. You're also going to be paying a monthly subscription to use it.

Really, it sounds like all you're doing is providing a workbook for people to download and experiment with. Why couldn't they just download a workbook and use Excel on their desktop to experiment with it without all the embedded crap?


Member 13396929 17-Oct-18 23:25pm
   
The only reason I'm asking about writing a value to the excel spreadsheet is because I need to write a specific value. This specific value is calculated by a Javascript function on the web application
Dave Kreskowiak 17-Oct-18 23:50pm
   
Javascript runs on the client side, not the server.

EPPlus runs on the server side.

Google for "Javascript Excel library".

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




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