473,386 Members | 1,621 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

Export data to Excel

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!!
Oct 4 '06 #1
7 7768

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.

Oct 4 '06 #2
"Larry Bud" <la**********@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
>
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

Oct 5 '06 #3
Mike,
Can you provide simple codes or link on how to write data to multiple
sheets? THANKS!!!!

"Mike Brind" wrote:
"Larry Bud" <la**********@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

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

Oct 5 '06 #4
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" <Va*****@discussions.microsoft.comwrote in message
news:A7**********************************@microsof t.com...
Mike,
Can you provide simple codes or link on how to write data to multiple
sheets? THANKS!!!!

"Mike Brind" wrote:
>"Larry Bud" <la**********@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googleg roups.com...
>
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


Oct 5 '06 #5
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" <pa*******@hotmail.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
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" <Va*****@discussions.microsoft.comwrote in message
news:A7**********************************@microsof t.com...
>Mike,
Can you provide simple codes or link on how to write data to multiple
sheets? THANKS!!!!

"Mike Brind" wrote:
>>"Larry Bud" <la**********@yahoo.comwrote in message
news:11**********************@h48g2000cwc.google groups.com...

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



Oct 5 '06 #6

Mike Brind wrote:
"Larry Bud" <la**********@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

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.

Oct 9 '06 #7
"Larry Bud" <la**********@yahoo.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
>
Mike Brind wrote:
>"Larry Bud" <la**********@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googleg roups.com...
>
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
Oct 9 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Matt | last post by:
I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table....
6
by: Elena | last post by:
I'm trying to export data to an Excel worksheet. I can export the data in the cell values perfectly. I need the code to change a header and footer for the worksheet, not for the columns. Is...
4
by: Jiro Hidaka | last post by:
Hello, I would like to know of a fast way to export data source data into an Excel sheet. I found a way from C# Corner(Query Tool to Excel using C# and .NET) which is a neat little way of...
13
by: Hemant Sipahimalani | last post by:
The following piece of code is being used to export HTML to excel. HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"...
1
by: JawzX01 | last post by:
Hello All, First, thank you for any help you can provide. I'm trying to do a simple export to excel. I've used the classic code that is all over the internet, and of course it worked without a...
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
7
Merlin1857
by: Merlin1857 | last post by:
Its great producing data for users to look at in your web pages and generally that is sufficient for their needs but sometimes you may want to supply your user with the data in a form they can...
3
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this...
2
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.