XML created Excel sheet opens with warnings

gdemaria
gdemaria used Ask the Experts™
on
I am creating an Excel spreadsheet using XML, I've written the XML via Coldfusion, but I don't think it matters which language is used.

Problem 1) When opening the file, I get the warning message "You are trying to open 'myFile.xls' in a different format than specified by the file extension.  Verify that it is not corrupted and from a trusted source before opening the file"     I've tried many different content types, etc.  How do I get rid of this message?


Problem 2)  When I save the file to my desktop it wants to save it as an .XML file and I have to change the type manually.   I can't ask my users to do this.   I need to generate the file and have it act like an Excel file.


For content types, I have used every variation of Excel such as..

 application/msexcel
 application/ms-excel
 application/vnd.ms-excel

Thanks for any help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It's by design (yay)

"The current design does not allow you to open HTML content from a web site in Excel unless the extension of the URL is .HTM/.HTML/.MHT/.MHTML. So ASP pages that return HTML and set the MIME type to something like XLS to try to force the HTML to open in Excel instead of the web browser (as expected) will always get the security alert since the content does not match the MIME type.  If you use an HTML MIME type, then the web browser will open the content instead of Excel. So there is no good workaround for this case because of the lack of a special MIME type for HTML/MHTML that is Excel specific.  You can add your own MIME type if you control both the web server and the client desktops that need access to it, but otherwise the best option is to use a different file format or alert your users of the warning and tell them to select Yes to the dialog."


http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/03/11/excel-2007-extension-warning.aspx

Even though the article is written for ASP, CF should have the same limitations.

There is a way to turn it off with a regedit (in the article) but that's not useful except on some intranets
btw ScriptAddict - the code from Ben's site produces the warning as well.
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Hey Sid,  I feared that answer.

Would you know of a way I can get Coldfusion to open the file and do a "Save As" so I can change the data type automatically?

That would help with my other problem (#2 above), the user is going to be asked to save it as a .xml file which will make no sense to them at all
if you add

  <cfheader
    name="content-disposition"
    value="attachment; filename=myfile.xls"
    />

it will name it .xls no matter what the source is

(I think that's what you are asking for?)

But even saved, when they open it they'll get the warning.

Stupid security features. Why can't Microsoft be more like Apple - they -never- have security problems <cough> :)
> it will name it .xls no matter what the source is

actually, I am naming it xls.   I open the file from a web link, when I try to save it to my desktop it wants to save it as .xml even though I named it xls.   I think that is worse than the warning.   Users will be saving the file as .xml and have no idea what to do with it
Most Valuable Expert 2015

Commented:
Unfortunately as long as the actual content and extension differ, I don't think there's anything you can do about it.  As that's exactly what the "extension hardening" stuff is designed to do.  Aside from generating a real excel file or the user changing their registry settings, there isn't a way to avoid it AFAIK.
so you're generating a static xml file and dropping a link for the user?

<a href="test.xls">Open</a>

and it's trying to open it as an xml file??

Don't see that behaviour here.

or am I confused (more than usual at least)

(fwiw I thought POI might be able to generate "real" excel files but apparently not http://forums.adobe.com/thread/209449)
Thanks agx,  it makes sense in some ways, but then again, why does MS offer XML as a method to create an Excel file if they won't let it become an Excel file! :)

Do you know of a way to automatically open a file and do a "save as" perhaps using CFEXECUTE or something?

Sid, I am using XML to generate an Excel worksheet.  I use cfcontent to "name" the file and associate the file type.  I never actually save the file on the server-side, just generate it and the user can open it.  

I named the file .xls in hopes that when opened it would become an Excel file.  Although it gives this warning we've been discussing and it does open it in Excel, there is one more negative side effect - when the user goes to save the file to their desktop, the "file type" is defaulted to xml.   Most users don't pay attention to the file type and just give it a name and save it.  This is much worse to me than the warning, the user has to save it to their computer as an .xls file or they will lose it or not know what to do with it.
aha! yes I can reproduce that here when saving the file which is downloaded as xls but excel tries to name it xml on save or save as. Probably nothing you can do about that....Silly excel.

afaik cfexecute won't help as it's execute server side...
I've been digging around trying to find a little script or .ini file or something that would open the file and do a "save as" under a different type (the real Excel type).

I could do this on the server side before delivering the file to the user, perhaps?
I was just thinking the same thing - If you've got excel on the server you could probably open the file xml & save as xls maybe http://www.visualbasicscript.com/Convert-XML-file-to-Excel-2003-XLS-file-m66035.aspx

I believe that openoffice has an API which may do something similar without needing an excel license
Most Valuable Expert 2015
Commented:
(Edit: Didn't see sid's last response before posting. The first comment was to you GD)

No, like sid said I don't believe there's any server side fix that's going to help (other than generating a real excel file) since it's the client browser that reads the content type and the local Excel install that's doing all this.

> but then again, why does MS offer XML as a method to create an Excel
> file if they won't let it become an Excel file! :)

Well if you create a new sheet in excel and save it as XML it'll open with Excel if you double click it.  That's the one type I would've expected to work. I can't access Office 2007+ right now. But if you and Sid both say this doesn't work I think you're out of luck.

<!--- using a file created by Excel and saved as XML --->
<cfheader name="Content-Disposition" value="attachment; file=book1.xml">
<cfcontent type="application/vnd.ms-excel" file="c:\path\to\book1.xml">
Most Valuable Expert 2015

Commented:
> I believe that openoffice has an API which may do something similar
> without needing an excel license

Yeah. Though I rarely recommend COM, it is way easier in this case :)
hi, check if this website can be of any help

http://www.cfregex.com/cfcomet/excel/
Most Valuable Expert 2015

Commented:
That's what Sid already suggested above ie Use COM
bit late in jumping into question ;)
Thanks for the help guys.

I ended up leaving the extension as .xml so the warning does not appear.   The file, even though .xml, does open Excel.   But I put a note on the page recommending users to do a SAVE AS and set the type to their preferred version of Excel (2010, 2003..)

Not a solution I would like for the general public, but this is limited to the more advance and limited set of administrators; so I think it is ok.

Thanks agian

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial