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];
}
}
This part actually does open up the spreadsheet