"The file you are trying to open, '[filename]', is in a different format" Excel Error

On a recent project, the client asked me to allow some reports to be exported to Excel (XLS.) I’ve had such a request 1000 times over the years and quickly went to work rendering the grid as HTML, sending its HTML as a response and setting the Response.Header to the Excel MIME type… Simple.

Or so I thought. Turns out that Office 2007 doesn’t like that much. When you open such a spreadsheet in Excel 2007 you get an error like:

“The file you are trying to open, ‘[filename]’, 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?”
(Yes | No | Help)

After searching for hours, I finally ran into this document from MS that essentially says this is a “feature” of the new Excel and no matter how much everyone hates it, they won’t fix it. Here’s an excerpt from that document:

“The current design does not allow you to open HTML content from a web site in Excel… 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… 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.” [Emphasis added]

In other words, give up because there’s no good solution. Here’s some bad solutions you could try, though:

  • Display a message that says something like, “If you are using Office 2007, please select “Yes” from the resulting dialog.”
  • Include a registry script that the user can optionally run to change their HKCUSoftwareMicrosoftOffice12.0ExcelSecurityExtensionHardening DWord to 0, disabling this useless prompt
  • Only export to CSV, not Excel. Sure, it’s not as pretty but it’ll work.
  • Instead of using this much simpler spreadsheet generation method, instead open a template spreadsheet on the server as a data-source, write to it (using SQL), and save it with a unique file name for the user to download. Of course you’ll then have to do stuff like ensuring your saved files have unique file names (perhaps using GUID) and deleting the old spreadsheets from the File System.
Advertisements

How do I install Active Directory management tools on Windows 7?

The Remote Server Administration Tools (RSAT) for Windows 7 can be downloaded from Microsoft’s web site:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=…

Remote Server Administration Tools for Windows 7

You can install the Administration Tools pack on computers that are running the Windows 7 operating system, and use the Administration Tools pack to manage specific technologies on computers that are running either Windows Server® 2008 R2, Windows Server 2008, or, in some cases, Windows Server 2003.

The Administration Tools pack includes support for remote management of computers that are running the Server Core installation option of either Windows Server 2008 R2 or Windows Server 2008. However, Remote Server Administration Tools for Windows 7 cannot be installed on any versions of the Windows Server operating system.

Administration Tools are secure by default. The default Administration Tools configuration opens only those ports and enables only those services and firewall exceptions required for remote management to work.

Continue reading “How do I install Active Directory management tools on Windows 7?”

How to get only alpha or alphanumeric characters from a string in SQL

How to get only alpha chars in SQL

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Call it like this:

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

Capture

How to get alphanumeric characters in SQL

Create Function [dbo].[RemoveNonAlphaNumCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Call it like this:

Select dbo.RemoveNonAlphaNumCharacters('abc1234def5678ghi90jkl')

Capture

Select dbo.RemoveNonAlphaNumCharacters('ab£2+2(")£c123£%"&--4def567&£%"(8ghi90jkl')

Capture