Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Exporting to Excel (xlsx files)

Reply
Thread Tools

Exporting to Excel (xlsx files)

 
 
Doogie
Guest
Posts: n/a
 
      11-19-2008
Can anoyne tell me why this VBScript will create the file to Excel
just fine, but the Excel file will not open up? I am saving it as a
xlsx file instead of an xls one and I have the new version of Excel
on
my machine and have opened other Excel files with that extension but
this one I get the following error:

"Excel cannot open the file 'Test.xlsx' because the file format or
file extension is not valid. Verify that the file has not been
corrupted and the file extension matches the format of the file."


If I switch the file type to be .xls instead of .xlsx, it will save
the file and open with no problems. Below is an example of the VB
script I'm using.


dim Cn,Rs
set Cn=server.createobject("ADODB.connection")
set Rs=server.createobject("ADODB.recordset")
Cn.open "MyConnectionString"
Rs.open "mysqlquery",Cn,1,3
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment;
filename=Test.xlsx"
if Rs.eof <> true then
response.write "<table border=1>"
while not Rs.eof
response.write "<tr><td>" & Rs.fields("mydatafield") & "</
td></tr>"
Rs.movenext
wend


response.write "</table>"
end if


set rs=nothing
Cn.close


 
Reply With Quote
 
 
 
 
Anthony Jones
Guest
Posts: n/a
 
      11-19-2008
"Doogie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Can anoyne tell me why this VBScript will create the file to Excel
> just fine, but the Excel file will not open up? I am saving it as a
> xlsx file instead of an xls one and I have the new version of Excel
> on
> my machine and have opened other Excel files with that extension but
> this one I get the following error:
>
> "Excel cannot open the file 'Test.xlsx' because the file format or
> file extension is not valid. Verify that the file has not been
> corrupted and the file extension matches the format of the file."
>
>
> If I switch the file type to be .xls instead of .xlsx, it will save
> the file and open with no problems. Below is an example of the VB
> script I'm using.
>
>
> dim Cn,Rs
> set Cn=server.createobject("ADODB.connection")
> set Rs=server.createobject("ADODB.recordset")
> Cn.open "MyConnectionString"
> Rs.open "mysqlquery",Cn,1,3
> Response.ContentType = "application/vnd.ms-excel"
> Response.AddHeader "Content-Disposition", "attachment;
> filename=Test.xlsx"
> if Rs.eof <> true then
> response.write "<table border=1>"
> while not Rs.eof
> response.write "<tr><td>" & Rs.fields("mydatafield") & "</
> td></tr>"
> Rs.movenext
> wend
>
>
> response.write "</table>"
> end if
>
>
> set rs=nothing
> Cn.close
>
>


Because an .xlsx file is opened strictly as a Zip package used by 2007
office applications. Whereas as an .xls file is opened by 2007 using the
same content sniffing tricks the previous versions did, hence it detects
whether the content is a proper ole storage based xls binary, a csv text
stream or a html text stream.

--
Anthony Jones - MVP ASP/ASP.NET

 
Reply With Quote
 
 
 
 
Doogie
Guest
Posts: n/a
 
      11-19-2008
On Nov 19, 3:55*pm, "Anthony Jones" <(E-Mail Removed)>
wrote:
> "Doogie" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
>
>
> > Can anoyne tell me why this VBScript will create the file to Excel
> > just fine, but the Excel file will not open up? *I am saving it as a
> > xlsx file instead of an xls one and I have the new version of Excel
> > on
> > my machine and have opened other Excel files with that extension but
> > this one I get the following error:

>
> > "Excel cannot open the file 'Test.xlsx' because the file format or
> > file extension is not valid. *Verify that the file has not been
> > corrupted and the file extension matches the format of the file."

>
> > If I switch the file type to be .xls instead of .xlsx, it will save
> > the file and open with no problems. *Below is an example of the VB
> > script I'm using.

>
> > dim Cn,Rs
> > set Cn=server.createobject("ADODB.connection")
> > set Rs=server.createobject("ADODB.recordset")
> > Cn.open "MyConnectionString"
> > Rs.open "mysqlquery",Cn,1,3
> > Response.ContentType = "application/vnd.ms-excel"
> > Response.AddHeader "Content-Disposition", "attachment;
> > filename=Test.xlsx"
> > if Rs.eof <> true then
> > * * response.write "<table border=1>"
> > * * while not Rs.eof
> > * * * * *response.write "<tr><td>" & Rs.fields("mydatafield")& "</
> > td></tr>"
> > * * * * *Rs.movenext
> > * * wend

>
> > * * response.write "</table>"
> > end if

>
> > set rs=nothing
> > Cn.close

>
> Because an .xlsx file is opened strictly as a Zip package used by 2007
> office applications. *Whereas as an .xls file is opened by 2007 using the
> same content sniffing tricks the previous versions did, hence it detects
> whether the content is a proper ole storage based xls binary, a csv text
> stream or a html text stream.
>
> --
> Anthony Jones - MVP ASP/ASP.NET- Hide quoted text -
>
> - Show quoted text -


So is there anyway to get it to work in VB script or am I stuck using
xls? The reason I ask, is when opening up the xls file I always get a
warning message too. It still opens, but it's annoying to get the
message. The message I get is:

"The file you are trying to open, 'Test.xls' is in a different format
than specified by the file extension. Verify that the file is not
corrupted and is from a trusted source before opening the file. Do
you want to open the file now?"
 
Reply With Quote
 
Mike Brind
Guest
Posts: n/a
 
      11-20-2008

"Doogie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
On Nov 19, 3:55 pm, "Anthony Jones" <(E-Mail Removed)>
wrote:
> "Doogie" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
>
>
> > Can anoyne tell me why this VBScript will create the file to Excel
> > just fine, but the Excel file will not open up? I am saving it as a
> > xlsx file instead of an xls one and I have the new version of Excel
> > on
> > my machine and have opened other Excel files with that extension but
> > this one I get the following error:

>
> > "Excel cannot open the file 'Test.xlsx' because the file format or
> > file extension is not valid. Verify that the file has not been
> > corrupted and the file extension matches the format of the file."

>
> > If I switch the file type to be .xls instead of .xlsx, it will save
> > the file and open with no problems. Below is an example of the VB
> > script I'm using.

>
> > dim Cn,Rs
> > set Cn=server.createobject("ADODB.connection")
> > set Rs=server.createobject("ADODB.recordset")
> > Cn.open "MyConnectionString"
> > Rs.open "mysqlquery",Cn,1,3
> > Response.ContentType = "application/vnd.ms-excel"
> > Response.AddHeader "Content-Disposition", "attachment;
> > filename=Test.xlsx"
> > if Rs.eof <> true then
> > response.write "<table border=1>"
> > while not Rs.eof
> > response.write "<tr><td>" & Rs.fields("mydatafield") & "</
> > td></tr>"
> > Rs.movenext
> > wend

>
> > response.write "</table>"
> > end if

>
> > set rs=nothing
> > Cn.close

>
> Because an .xlsx file is opened strictly as a Zip package used by 2007
> office applications. Whereas as an .xls file is opened by 2007 using the
> same content sniffing tricks the previous versions did, hence it detects
> whether the content is a proper ole storage based xls binary, a csv text
> stream or a html text stream.
>
> --
> Anthony Jones - MVP ASP/ASP.NET- Hide quoted text -
>
> - Show quoted text -


> So is there anyway to get it to work in VB script or am I stuck using
> xls? The reason I ask, is when opening up the xls file I always get a
> warning message too. It still opens, but it's annoying to get the
> message. The message I get is:


> "The file you are trying to open, 'Test.xls' is in a different format
> than specified by the file extension. Verify that the file is not
> corrupted and is from a trusted source before opening the file. Do
> you want to open the file now?"


I think that what Anthony was saying is that you are not actually creating
an xslx file. You are creating an html file, and giving it a different
extension. Previous versions of Excel were happy with this approach (to a
point), but the latest version appears not to like it at all. If you tried
to read from these "faux" Excel files using OleDb, they would also complain
that the file format was not as expected.

One way to get round this is to create a real .xslx file in Excel and use
that as a blank template, then use the ACE OleDb provider to write your data
to it in much the same way as if your were writing data to a database. The
ACE OleDb provider will need to be installed on the machine on which your
app is running for this to work.

Or you can ask yourself if you really need to try to generate the file with
an xlsx extension, since 2007 will happily open xls files and even csv
files.

--
Mike Brind
MVP - ASP/ASP.NET


 
Reply With Quote
 
Doogie
Guest
Posts: n/a
 
      11-20-2008
On Nov 20, 1:51*am, "Mike Brind" <(E-Mail Removed)> wrote:
> "Doogie" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed)...
> On Nov 19, 3:55 pm, "Anthony Jones" <(E-Mail Removed)>
> wrote:
>
>
>
>
>
> > "Doogie" <(E-Mail Removed)> wrote in message

>
> >news:(E-Mail Removed)....

>
> > > Can anoyne tell me why this VBScript will create the file to Excel
> > > just fine, but the Excel file will not open up? I am saving it as a
> > > xlsx file instead of an xls one and I have the new version of Excel
> > > on
> > > my machine and have opened other Excel files with that extension but
> > > this one I get the following error:

>
> > > "Excel cannot open the file 'Test.xlsx' because the file format or
> > > file extension is not valid. Verify that the file has not been
> > > corrupted and the file extension matches the format of the file."

>
> > > If I switch the file type to be .xls instead of .xlsx, it will save
> > > the file and open with no problems. Below is an example of the VB
> > > script I'm using.

>
> > > dim Cn,Rs
> > > set Cn=server.createobject("ADODB.connection")
> > > set Rs=server.createobject("ADODB.recordset")
> > > Cn.open "MyConnectionString"
> > > Rs.open "mysqlquery",Cn,1,3
> > > Response.ContentType = "application/vnd.ms-excel"
> > > Response.AddHeader "Content-Disposition", "attachment;
> > > filename=Test.xlsx"
> > > if Rs.eof <> true then
> > > response.write "<table border=1>"
> > > while not Rs.eof
> > > response.write "<tr><td>" & Rs.fields("mydatafield") & "</
> > > td></tr>"
> > > Rs.movenext
> > > wend

>
> > > response.write "</table>"
> > > end if

>
> > > set rs=nothing
> > > Cn.close

>
> > Because an .xlsx file is opened strictly as a Zip package used by 2007
> > office applications. Whereas as an .xls file is opened by 2007 using the
> > same content sniffing tricks the previous versions did, hence it detects
> > whether the content is a proper ole storage based xls binary, a csv text
> > stream or a html text stream.

>
> > --
> > Anthony Jones - MVP ASP/ASP.NET- Hide quoted text -

>
> > - Show quoted text -
> > So is there anyway to get it to work in VB script or am I stuck using
> > xls? *The reason I ask, is when opening up the xls file I always get a
> > warning message too. *It still opens, but it's annoying to get the
> > message. *The message I get is:
> > "The file you are trying to open, 'Test.xls' is in a different format
> > than specified by the file extension. *Verify that the file is not
> > corrupted and is from a trusted source before opening the file. *Do
> > you want to open the file now?"

>
> I think that what Anthony was saying is that you are not actually creating
> an xslx file. *You are creating an html file, and giving it a different
> extension. *Previous versions of Excel were happy with this approach (to a
> point), but the latest version appears not to like it at all. *If you tried
> to read from these "faux" Excel files using OleDb, they would also complain
> that the file format was not as expected.
>
> One way to get round this is to create a real .xslx file in Excel and use
> that as a blank template, then use the ACE OleDb provider to write your data
> to it in much the same way as if your were writing data to a database. *The
> ACE OleDb provider will need to be installed on the machine on which your
> app is running for this to work.
>
> Or you can ask yourself if you really need to try to generate the file with
> an xlsx extension, since 2007 will happily open xls files and even csv
> files.
>
> --
> Mike Brind
> MVP - ASP/ASP.NET- Hide quoted text -
>
> - Show quoted text -


Hi Mike,
2007 does open up the xls file for me, but it generates that warning I
displayed in my previous message, every single time. That is a bit
annoying and for a user can be even more so. Is there a way around
that warning?
 
Reply With Quote
 
Mike Brind
Guest
Posts: n/a
 
      11-20-2008

"Doogie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
On Nov 20, 1:51 am, "Mike Brind" <(E-Mail Removed)> wrote:
> "Doogie" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed)...
> On Nov 19, 3:55 pm, "Anthony Jones" <(E-Mail Removed)>
> wrote:
>
>
>
>
>
> > "Doogie" <(E-Mail Removed)> wrote in message

>
> >news:(E-Mail Removed)...

>
> > > Can anoyne tell me why this VBScript will create the file to Excel
> > > just fine, but the Excel file will not open up? I am saving it as a
> > > xlsx file instead of an xls one and I have the new version of Excel
> > > on
> > > my machine and have opened other Excel files with that extension but
> > > this one I get the following error:

>
> > > "Excel cannot open the file 'Test.xlsx' because the file format or
> > > file extension is not valid. Verify that the file has not been
> > > corrupted and the file extension matches the format of the file."

>
> > > If I switch the file type to be .xls instead of .xlsx, it will save
> > > the file and open with no problems. Below is an example of the VB
> > > script I'm using.

>
> > > dim Cn,Rs
> > > set Cn=server.createobject("ADODB.connection")
> > > set Rs=server.createobject("ADODB.recordset")
> > > Cn.open "MyConnectionString"
> > > Rs.open "mysqlquery",Cn,1,3
> > > Response.ContentType = "application/vnd.ms-excel"
> > > Response.AddHeader "Content-Disposition", "attachment;
> > > filename=Test.xlsx"
> > > if Rs.eof <> true then
> > > response.write "<table border=1>"
> > > while not Rs.eof
> > > response.write "<tr><td>" & Rs.fields("mydatafield") & "</
> > > td></tr>"
> > > Rs.movenext
> > > wend

>
> > > response.write "</table>"
> > > end if

>
> > > set rs=nothing
> > > Cn.close

>
> > Because an .xlsx file is opened strictly as a Zip package used by 2007
> > office applications. Whereas as an .xls file is opened by 2007 using the
> > same content sniffing tricks the previous versions did, hence it detects
> > whether the content is a proper ole storage based xls binary, a csv text
> > stream or a html text stream.

>
> > --
> > Anthony Jones - MVP ASP/ASP.NET- Hide quoted text -

>
> > - Show quoted text -
> > So is there anyway to get it to work in VB script or am I stuck using
> > xls? The reason I ask, is when opening up the xls file I always get a
> > warning message too. It still opens, but it's annoying to get the
> > message. The message I get is:
> > "The file you are trying to open, 'Test.xls' is in a different format
> > than specified by the file extension. Verify that the file is not
> > corrupted and is from a trusted source before opening the file. Do
> > you want to open the file now?"

>
> I think that what Anthony was saying is that you are not actually creating
> an xslx file. You are creating an html file, and giving it a different
> extension. Previous versions of Excel were happy with this approach (to a
> point), but the latest version appears not to like it at all. If you tried
> to read from these "faux" Excel files using OleDb, they would also
> complain
> that the file format was not as expected.
>
> One way to get round this is to create a real .xslx file in Excel and use
> that as a blank template, then use the ACE OleDb provider to write your
> data
> to it in much the same way as if your were writing data to a database. The
> ACE OleDb provider will need to be installed on the machine on which your
> app is running for this to work.
>
> Or you can ask yourself if you really need to try to generate the file
> with
> an xlsx extension, since 2007 will happily open xls files and even csv
> files.
>
> --
> Mike Brind
> MVP - ASP/ASP.NET- Hide quoted text -
>
> - Show quoted text -


> Hi Mike,
> 2007 does open up the xls file for me, but it generates that warning I
> displayed in my previous message, every single time. That is a bit
> annoying and for a user can be even more so. Is there a way around
> that warning?


Only by preventing it from happening in the first place - generate a valid
file from within Excel, and use ADO to write to it.

--
Mike Brind
MVP - ASP/ASP.NET


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting to Excel using new version of Excel Doogie ASP .Net 1 11-19-2008 09:10 PM
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
Exporting the datagrid to excel, text and html files. Ravindra ASP .Net 0 12-14-2005 04:14 AM
exporting an excel file from database; making changes to excel file and updating the database by importing it back Luis Esteban Valencia ASP .Net 1 01-12-2005 12:28 AM
Re: Exporting to Excel using CSS Marshal Antony ASP .Net 1 01-12-2004 09:19 PM



Advertisments