Click here to Skip to main content
14,640,483 members
Rate this:
Please Sign up or sign in to vote.
I have an ASP.Net Website with an ImageButton Web Control and a few Label Web Controls. The user can interact with the ImageButton by clicking on it. The program gives the user the distance between a pair of clicks and augments this distance to a running total. The click events in the ImageButton are handled by a script of type "text/javascript". The OnClientClick handler of the ImageButton calls the function Pic_Click from the script every time the user clicks. Every time the running total is updated, the value is passed to an ASP.Net Hidden Field named "totalhold".

What I am trying to do is update the active cell of an open Excel WorkBook with this running total when the user clicks an ASP.Net Button. The click event for the button is handled by the OnClick handler of the Button.The method is called from the appropriate C# file (Calling the C# method with the OnClick handler does work in my case). I've done something similar in a C# Windows Forms Website using a reference to Microsoft.Office.Interop.Excel.dll. I referenced the same .dll in my current Website, but nothing happens when I click the update button.

The question is, how can I properly use the Microsoft.Office.Interop.Excel.dll to update the active cell from an ASP.Net website?

Here is the .aspx page:
<asp:ImageButton ID="pic" runat="server" ImageAlign="Left" OnClientClick="Image_Click(); return false;" />
<asp:Button ID="Upload" runat="server" Text="Upload File" OnClick="Open_It" style="z-index: 1; position: absolute; top: 136px; left: 1392px; height: 27px;" />
<asp:HiddenField ID="totalhold" runat="server" />
<asp:Label ID="labeltotal" runat="server" Text="Total: "></asp:Label>
<asp:Label ID="labelarea" runat="server" Text="Area: "></asp:Label>
<asp:Button ID="UpdateExcel" runat="server" Text="Update Excel" Width="195px"  OnClick="updateExcel"/>

Here is the script function:
function Image_Click()
    {
        numClick++;
        document.getElementById("labelcount").innerText = "Count: " + numClick;
        if (numClick % 2 == 1)
        {
            pos1x = event.pageX;
            pos1y = event.pageY;
        }
        else if (numClick % 2 == 0)
        {
            pos2x = event.pageX;
            pos2y = event.pageY;
            distx = Math.abs(pos2x - pos1x);
            disty = Math.abs(pos2y - pos1y);
            redistx = Math.ceil(distx / (1 / zoom * Math.floor(200 / 4)));
            redisty = Math.ceil(disty / (1 / zoom * Math.floor(200 / 4)));
            newdist = Math.ceil(Math.sqrt(redistx * redistx + redisty * redisty));

                document.getElementById("lengthlabel").innerText ="Length: " + newdist;
                total += newdist;
                var th = document.getElementById("totalhold");
                th.value = total;
                document.getElementById("labeltotal").innerText = "Span: " + total;
        }
    }

The script works fine.

What I have tried:

This is my update method called by the OnClick handler of the update Button:

protected void updateExcel(object sender, EventArgs e)
    {
        
        bpRow = excelApp.ActiveCell.Row;
        bpColumn = excelApp.ActiveCell.Row;
        MySheet.Cells[bpRow, bpColumn] = Convert.ToDouble(totalhold.Value);
        MyBook.Save();

        labelarea.Text = totalhold.Value;
        
    }

For added context I know that the value of the ASP.Net Hidden Field value survives the postback because the last line in the method works without fail.

This is how I set the open Excel spreadsheet:
protected void Open_It(object sender, EventArgs e)
    {
        string folderPath = Server.MapPath("~/Files/");
        if (!Directory.Exists(folderPath))
        {
            Directory.CreateDirectory(folderPath);
        }
        string filename = FileUploader.FileName;
        if (!File.Exists(Path.Combine(folderPath, filename)))
            FileUploader.SaveAs(folderPath + filename);
        string ext = Path.GetExtension(filename);
        else if (ext == ".xlsx")
        {
            excelApp = new Excel.Application();
            excelApp.Visible = true;
            MyBook = excelApp.Workbooks.Open(Path.GetFileName(FileUploader.FileName));
            isOpen = true;
            MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here

        }
    }

This part actually does open up the spreadsheet
Posted
Updated 11-Oct-18 10:46am
Rate this:
Please Sign up or sign in to vote.

Solution 2

The short answer is, you can't.

You can NOT use Office Interop in an ASP.NET app. All of that code runs entirely server-side, never on the client. Your server-side code will never have any access to anything on the client. All your ASP.NET code can do is generate HTML and script files and send them to the client browser.

Office doesn't support re-entrancy, meaning it cannot handle multiple requests at the same time, and some functions require user interaction to complete. Since the code is running on a web server, there is usually nobody logged into the server console to respond to these dialogs.

On the client-side, your javascript code will NOT have access to any instance of Excel running on the client. This would be a massive security risk if it was possible.

Yeah, I know you're going to say "well it works on my development machine!". Yeah, because everything is running as you and running locally on the same machine. This will NOT be the case when you deploy the web app to a server. As soon as you do that, everything you've written will stop working.

You cannot do what you want to do.
   
Comments
Member 13396929 11-Oct-18 16:59pm
   
I get what you are saying. So to summarize, the Javascript code can't access the Excel instance on the client computer.
If my proprietary Excel spreadsheet is stored on the website server, can I open it inside the web page and write to it using something like office.js?
Dave Kreskowiak 11-Oct-18 17:29pm
   
I have no idea. You're going to have to try it.
Richard Deeming 12-Oct-18 14:07pm
   
The Excel file is on the server. The data you want to add to the Excel file is on the server. Why not manipulate the file on the server?

As Dave said, you can't manipulate the file using Office Interop: read Considerations for server-side Automation of Office[^] for more details.

But there are plenty of libraries which you can use on the server, which don't even require you to install Excel on the server. For example:

* EPPlus[^]
* ClosedXML[^]
* The OpenXML SDK[^]

Those all support the new .xlsx format files. If you need to support old .xls format files, then you'll need to use NPOI[^].
Rate this:
Please Sign up or sign in to vote.

Solution 1

you're going to have to use activeX run an app on the users machine a bad thing to do. plus you will need to create that file holding the data and get the user to download it and then via activeX run the app with the downloaded file as a parameter. I would create the file have the user download it and then let the user open their copy of Excel to look at the file. YMMV
   
Comments
Member 13396929 11-Oct-18 16:02pm
   
I don't quite understand what you said about activeX, which I am not using at all. The excel spreadsheet I'm using is proprietary, and I already hold it is already stored in the website. Also, in my field everyone has access to Excel

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