|
it
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Export data to Excelhi 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 wrote:
> hi Everyone, 1. No, you can only do 1 sheet.> > 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? 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" <larrybud2***@yahoo.com> wrote in message No, you can write to any sheet in the book. You can even use DDL to create news:1160000944.172164.233190@h48g2000cwc.googlegroups.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. new worksheets and write to them. -- Mike Brind Mike,
Can you provide simple codes or link on how to write data to multiple sheets? THANKS!!!! Show quoteHide quote "Mike Brind" wrote: > "Larry Bud" <larrybud2***@yahoo.com> wrote in message > news:1160000944.172164.233190@h48g2000cwc.googlegroups.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 > > > > 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" %> -- Show quoteHide quoteMike Brind "Vanessa" <Vane***@discussions.microsoft.com> wrote in message news:A713F549-F40D-4369-9EB1-F9FD7CDD1E57@microsoft.com... > Mike, > Can you provide simple codes or link on how to write data to multiple > sheets? THANKS!!!! > > "Mike Brind" wrote: > >> "Larry Bud" <larrybud2***@yahoo.com> wrote in message >> news:1160000944.172164.233190@h48g2000cwc.googlegroups.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 >> >> >> >> 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 -- Show quoteHide quoteMike Brind "Mike Brind" <paxton***@hotmail.com> wrote in message news:%23h9i1PJ6GHA.4064@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" <Vane***@discussions.microsoft.com> wrote in message > news:A713F549-F40D-4369-9EB1-F9FD7CDD1E57@microsoft.com... >> Mike, >> Can you provide simple codes or link on how to write data to multiple >> sheets? THANKS!!!! >> >> "Mike Brind" wrote: >> >>> "Larry Bud" <larrybud2***@yahoo.com> wrote in message >>> news:1160000944.172164.233190@h48g2000cwc.googlegroups.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 >>> >>> >>> >>> > > Mike Brind wrote:
Show quoteHide quote > "Larry Bud" <larrybud2***@yahoo.com> wrote in message If I understand Vanessa right, not the way she wants to do it.> news:1160000944.172164.233190@h48g2000cwc.googlegroups.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 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.
Show quote
Hide quote
"Larry Bud" <larrybud2***@yahoo.com> wrote in message My understanding of the way she wanted to do it is that she didn't want to news:1160398818.639982.277640@i42g2000cwa.googlegroups.com... > > Mike Brind wrote: >> "Larry Bud" <larrybud2***@yahoo.com> wrote in message >> news:1160000944.172164.233190@h48g2000cwc.googlegroups.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. 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. > I noted this in another reply.> If you change the content type to Excel: > > Response.ContentType = "application/vnd.ms-excel" > > You cannot make more than 1 sheet. > There's no ideal way to automate any Office product from ASP. All of them > And using OWC is frought with problems. > fall short in one way or another. -- Mike Brind  
Other interesting topics
How do I make sure that a asp page expires immediately and is not stored in the history ???
How to collect uncertain number of people information in a form How expensive is object instantication? 3-factor login system Restrict access by ip -2147217900:Syntax error in INSERT INTO statement. Split, convert date question ?? Random GUID type number or RegExp Jet Database Error Unable to connect to SQL Server database. |
|||||||||||||||||||||||
