Link to home
Start Free TrialLog in
Avatar of LuckyLucks
LuckyLucks

asked on

Opening an existing excel worksheet in ASP.NET

Hi,
I need to open an existing worksheet that comprises of 3 sheets and that sits on the host server,say: \\myserver\dir1\dir2\myworksheet.xls. The application runs on the same host.

What import statements beside System.Data do I need and the snippet of code that achieves this will be appreciated.

thanx a lot!
Avatar of caball88
caball88

do you just need the data from the excel files or do you want to be able to edit and save back to the file? you can use ado.net to connect to the excel file and then export the data to a dataset.
here is an example from microsoft to get you started.

http://support.microsoft.com/default.aspx?scid=kb%3B[LN]%3BQ316934
Avatar of Aaron Jabamani
Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "
#End Region
    Dim oExcel As Object


    Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
        oExcel = CreateObject("Excel.Application")
        Dim wbk As Object

        wbk = oExcel.Workbooks.Open(Filename:="C:\temp\test.xls", UpdateLinks:=False, ReadOnly:=False)
        With wbk.ActiveSheet
            'read
            'MsgBox(.Range("A1").Value)
            'read and write
            .Range("B1").Value = "Iouri" & .Range("A1").value
        End With ' wbk

        'save Excel
        oExcel.DisplayAlerts = False
        wbk.Save()
        wbk = Nothing
        MsgBox("Done")

        'release excel lib
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
        oExcel = Nothing
        'collect garbage
        GC.Collect()

    End Sub
Hi LuckyLucks,

i think that you have clarify what you have to do.
the previous posts correctly give you the way of reading excel file through oledb and office library

B..M
mmarinov
Avatar of LuckyLucks

ASKER

I need to open Excel ws thats on the server. Multiple users can edit it and then it saves changes back to the excel ws on the server. Also, I was reading stuff and I read that Excel needs to be installed on the server and all the stuff is really done on the server and not the client side. Is this correct?

thanx
Hi,
   Above is written in code-behind which means server side which means EXCEl which is is placed in server is read. In the below line  u use like
Server.MapPath
to get and point to where ur excel file is located in the server.

wbk = oExcel.Workbooks.Open(Filename:="C:\temp\test.xls", UpdateLinks:=False, ReadOnly:=False)

-Aaron
Is there a  way that the Excel ws can be opened on the client side?? I want to automate the opening of the worksheet rather than expect the user to search and find the worksheet and manually open it.
if you give them a link(i.e. http://yoursite/dir/yourexcel.xls) to the excel file it will ask them to save, open or cancel. if they select open it will open it with the default application(in most cases its excel). but be aware that once they open it you have no control over the file. if you want the user to make changes and save it back you will have to give them an upload function.
I dont have excel installed on my server. Is there a way to open it on the client's side using the client's resources like the excel exe?
you don't need excel installed on the server, if you provide them a link to the file it will open up the client side excel.exe app.
It doesnt seem to do anything here. I have used the following in my aspx .

<asp:linkbutton id="openExcelWS" Text="Open Excel Worksheet"
NavigateUrl="\\localhost\dir1\dir1a\myws.xls" runat="server"></asp:linkbutton>

and I see the following in the task bar but Excel doesnt open up.
javascript:__doPostBack('openExcelWS','')

The url is valid since I have opened the ws successfully thru the Run command prompt.
Ignore the directly above post.

I am using a hyperlink NavigateURL not a linkbuttons'. Is there  a way that hyperlink's can open in a new window ? not using javascript but rather the asp:hyperlink?
ASKER CERTIFIED SOLUTION
Avatar of caball88
caball88

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I loaded an excel doc into a dataset and one of the columbs
has string and numeric values. When I displayed the dataset in
a datagrid all the numeric values disapeared. Anyone had this
problem?