Export data to Excel

Discussion in 'ASP General' started by Vanessa, Oct 4, 2006.

  1. Vanessa

    Vanessa Guest

    hi Everyone,

    I have two questions on exporting data to Excel using ASP (w/o converting
    formatted excel file into web page and then plug in the dynamic data):

    1. Can we export data into different sheets at Excel?
    2. Can we do graph at export?

    Thanks!!
    Vanessa, Oct 4, 2006
    #1
    1. Advertising

  2. Vanessa

    Larry Bud Guest

    Vanessa wrote:
    > hi Everyone,
    >
    > I have two questions on exporting data to Excel using ASP (w/o converting
    > formatted excel file into web page and then plug in the dynamic data):
    >
    > 1. Can we export data into different sheets at Excel?
    > 2. Can we do graph at export?


    1. No, you can only do 1 sheet.
    2. I haven't tried it, but you should be able to.

    The best way to try these things is to create a workbook, do a Save as
    Web Page, choose "sheet" and save it as HTM. Then, if any additional
    files were created, delete them, and reopen the HTM in Excel and see
    what was preserved. In this case, the simple chart I made was
    preserved.

    Then just reverse engineer the HTM to see how it's done. Much of the
    time you can whittle the XML down to basic components.

    Lots of trial and error, but if your client insist on requiring an
    Excel file, this is about the only way it can be done.
    Larry Bud, Oct 4, 2006
    #2
    1. Advertising

  3. Vanessa

    Mike Brind Guest

    "Larry Bud" <> wrote in message
    news:...
    >
    > Vanessa wrote:
    >> hi Everyone,
    >>
    >> I have two questions on exporting data to Excel using ASP (w/o converting
    >> formatted excel file into web page and then plug in the dynamic data):
    >>
    >> 1. Can we export data into different sheets at Excel?
    >> 2. Can we do graph at export?

    >
    > 1. No, you can only do 1 sheet.


    No, you can write to any sheet in the book. You can even use DDL to create
    new worksheets and write to them.

    --
    Mike Brind
    Mike Brind, Oct 5, 2006
    #3
  4. Vanessa

    Vanessa Guest

    Mike,
    Can you provide simple codes or link on how to write data to multiple
    sheets? THANKS!!!!

    "Mike Brind" wrote:

    > "Larry Bud" <> wrote in message
    > news:...
    > >
    > > Vanessa wrote:
    > >> hi Everyone,
    > >>
    > >> I have two questions on exporting data to Excel using ASP (w/o converting
    > >> formatted excel file into web page and then plug in the dynamic data):
    > >>
    > >> 1. Can we export data into different sheets at Excel?
    > >> 2. Can we do graph at export?

    > >
    > > 1. No, you can only do 1 sheet.

    >
    > No, you can write to any sheet in the book. You can even use DDL to create
    > new worksheets and write to them.
    >
    > --
    > Mike Brind
    >
    >
    >
    >
    Vanessa, Oct 5, 2006
    #4
  5. Vanessa

    Mike Brind Guest

    Create a workbook called Book1.xls, and put it in the same directory as the
    following script, making sure you have set the appropriate permissions on
    the directory:

    <%
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="_
    & Server.Mappath("Book1.xls") & ";Extended Properties=""Excel
    8.0;HDR=Yes;"""
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open strConn
    sql = "Create Table Test1 (Username TEXT, Surname TEXT)"
    Conn.execute(sql)
    sql = "Insert INTO [Test1$] (Username, Surname) VALUES ('Mike', 'Brind')"
    Conn.execute(sql)
    sql = "Create Table Test2 (Username TEXT, Surname TEXT)"
    Conn.execute(sql)
    sql = "Insert INTO [Test2$] (Username, Surname) VALUES ('Mike', 'Brind')"
    Conn.execute(sql)
    Conn.Close
    Response.Write "Done"
    %>

    --
    Mike Brind


    "Vanessa" <> wrote in message
    news:...
    > Mike,
    > Can you provide simple codes or link on how to write data to multiple
    > sheets? THANKS!!!!
    >
    > "Mike Brind" wrote:
    >
    >> "Larry Bud" <> wrote in message
    >> news:...
    >> >
    >> > Vanessa wrote:
    >> >> hi Everyone,
    >> >>
    >> >> I have two questions on exporting data to Excel using ASP (w/o
    >> >> converting
    >> >> formatted excel file into web page and then plug in the dynamic data):
    >> >>
    >> >> 1. Can we export data into different sheets at Excel?
    >> >> 2. Can we do graph at export?
    >> >
    >> > 1. No, you can only do 1 sheet.

    >>
    >> No, you can write to any sheet in the book. You can even use DDL to
    >> create
    >> new worksheets and write to them.
    >>
    >> --
    >> Mike Brind
    >>
    >>
    >>
    >>
    Mike Brind, Oct 5, 2006
    #5
  6. Vanessa

    Mike Guest

    Some more stuff on dealing with Excel from ASP pages:

    If you create a workbook in the manner suggested by Larry, it will not be
    accessible via the Jet driver. You will get an error stating that the file
    is not in the format expected. This can be a problem sometimes if you want
    to import the file into another app to do something further with it. The
    same is true of reverse-engineered Word docs. For example, Quark refuses to
    recognise them as genuine Word docs and opens them up as HTML.

    I haven't found a way to add worksheets to the reverse-engineered version of
    an xls file, which supports Larry's observation that you can only write to
    one sheet using the method he suggested.

    With a genuine workbook, you can also access the file using ADOX to
    establish the number of tables etc. Apparently, you can use DDL to DROP
    tables (worksheets) but when I tried, they steadfastly remained in the
    workbook. No error was reported.

    The following can be adapted to VBScript easily enough:

    http://support.microsoft.com/kb/303814/EN-US/

    I haven't tried this, but there appears to be another way to achieve what
    you want:

    http://www.4guysfromrolla.com/webtech/022801-1.shtml


    --
    Mike Brind


    "Mike Brind" <> wrote in message
    news:%...
    > Create a workbook called Book1.xls, and put it in the same directory as
    > the following script, making sure you have set the appropriate permissions
    > on the directory:
    >
    > <%
    > strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="_
    > & Server.Mappath("Book1.xls") & ";Extended Properties=""Excel
    > 8.0;HDR=Yes;"""
    > Set Conn = Server.CreateObject("ADODB.Connection")
    > Conn.Open strConn
    > sql = "Create Table Test1 (Username TEXT, Surname TEXT)"
    > Conn.execute(sql)
    > sql = "Insert INTO [Test1$] (Username, Surname) VALUES ('Mike', 'Brind')"
    > Conn.execute(sql)
    > sql = "Create Table Test2 (Username TEXT, Surname TEXT)"
    > Conn.execute(sql)
    > sql = "Insert INTO [Test2$] (Username, Surname) VALUES ('Mike', 'Brind')"
    > Conn.execute(sql)
    > Conn.Close
    > Response.Write "Done"
    > %>
    >
    > --
    > Mike Brind
    >
    >
    > "Vanessa" <> wrote in message
    > news:...
    >> Mike,
    >> Can you provide simple codes or link on how to write data to multiple
    >> sheets? THANKS!!!!
    >>
    >> "Mike Brind" wrote:
    >>
    >>> "Larry Bud" <> wrote in message
    >>> news:...
    >>> >
    >>> > Vanessa wrote:
    >>> >> hi Everyone,
    >>> >>
    >>> >> I have two questions on exporting data to Excel using ASP (w/o
    >>> >> converting
    >>> >> formatted excel file into web page and then plug in the dynamic
    >>> >> data):
    >>> >>
    >>> >> 1. Can we export data into different sheets at Excel?
    >>> >> 2. Can we do graph at export?
    >>> >
    >>> > 1. No, you can only do 1 sheet.
    >>>
    >>> No, you can write to any sheet in the book. You can even use DDL to
    >>> create
    >>> new worksheets and write to them.
    >>>
    >>> --
    >>> Mike Brind
    >>>
    >>>
    >>>
    >>>

    >
    >
    Mike, Oct 5, 2006
    #6
  7. Vanessa

    Larry Bud Guest

    Mike Brind wrote:
    > "Larry Bud" <> wrote in message
    > news:...
    > >
    > > Vanessa wrote:
    > >> hi Everyone,
    > >>
    > >> I have two questions on exporting data to Excel using ASP (w/o converting
    > >> formatted excel file into web page and then plug in the dynamic data):
    > >>
    > >> 1. Can we export data into different sheets at Excel?
    > >> 2. Can we do graph at export?

    > >
    > > 1. No, you can only do 1 sheet.

    >
    > No, you can write to any sheet in the book. You can even use DDL to create
    > new worksheets and write to them.


    If I understand Vanessa right, not the way she wants to do it.

    If you change the content type to Excel:

    Response.ContentType = "application/vnd.ms-excel"

    You cannot make more than 1 sheet.

    And using OWC is frought with problems.
    Larry Bud, Oct 9, 2006
    #7
  8. Vanessa

    Mike Brind Guest

    "Larry Bud" <> wrote in message
    news:...
    >
    > Mike Brind wrote:
    >> "Larry Bud" <> wrote in message
    >> news:...
    >> >
    >> > Vanessa wrote:
    >> >> hi Everyone,
    >> >>
    >> >> I have two questions on exporting data to Excel using ASP (w/o
    >> >> converting
    >> >> formatted excel file into web page and then plug in the dynamic data):
    >> >>
    >> >> 1. Can we export data into different sheets at Excel?
    >> >> 2. Can we do graph at export?
    >> >
    >> > 1. No, you can only do 1 sheet.

    >>
    >> No, you can write to any sheet in the book. You can even use DDL to
    >> create
    >> new worksheets and write to them.

    >
    > If I understand Vanessa right, not the way she wants to do it.


    My understanding of the way she wanted to do it is that she didn't want to
    reverse-engineer an existing file to html and "plug" dynamic data into it.
    At least,that what I assume "w/o [without?] converting formatted excel file
    into web page and then plug in the dynamic data" meant. The only
    alternative to that that I'm aware of is to open an existing blank document
    and write to it.

    >
    > If you change the content type to Excel:
    >
    > Response.ContentType = "application/vnd.ms-excel"
    >
    > You cannot make more than 1 sheet.


    I noted this in another reply.

    >
    > And using OWC is frought with problems.
    >


    There's no ideal way to automate any Office product from ASP. All of them
    fall short in one way or another.

    --
    Mike Brind
    Mike Brind, Oct 9, 2006
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Deva

    Export data from grid to Excel

    Deva, Jan 13, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    2,348
  2. Joy
    Replies:
    2
    Views:
    2,663
    Sabre
    Aug 22, 2003
  3. =?Utf-8?B?U3JpZGhhcg==?=

    using Microsoft Excel image for Export to Excel button

    =?Utf-8?B?U3JpZGhhcg==?=, Dec 9, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    3,104
    =?Utf-8?B?U3JpZGhhcg==?=
    Dec 9, 2005
  4. =?Utf-8?B?SGVtYW50IFNpcGFoaW1hbGFuaQ==?=

    Export to Excel (Default File Type - Excel)

    =?Utf-8?B?SGVtYW50IFNpcGFoaW1hbGFuaQ==?=, Apr 24, 2006, in forum: ASP .Net
    Replies:
    15
    Views:
    17,519
    syed hassan
    May 21, 2009
  5. Grey
    Replies:
    4
    Views:
    1,972
    Mark Rae [MVP]
    Oct 17, 2007
Loading...

Share This Page