[RESOLVED] [2008] Open an Excel 2007 template

[kul2bme]

I have opened Excel 2003 file just fine with "a little from my friends" here in this forum. Now I am trying to open an Excel 2007 file and I am using the following code.

Private Function OpenExcelTemplate(ByVal sTemplatePath As String) As Boolean
        Try
            Dim iPos As Integer = InStrRev(sTemplatePath, ".")
            If Len(sTemplatePath) - iPos = 3 Then
                Response.Clear()
                Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
                Response.Charset = ""
                Response.ContentType = "application/vnd.xls"
                Response.WriteFile(sTemplatePath)
                Response.Flush()
            Else
                Response.Clear()
                Response.AddHeader("content-disposition", "attachment;filename=FileName.xlsx")
                Response.Charset = ""
                Response.ContentType = "application/vnd.ms-excel"
                Response.WriteFile(sTemplatePath)
                Response.Flush()

            End If


            OpenExcelTemplate = True


        Catch ex As Exception
            Me.WucViewQuery1.DisplayError(ex.Message)

        End Try
    End Function

I am getting the following error when I try this ...

"Excel found unreadable content in 'FileName[1].xlsx'. Do you want to recover the contents of this workbook? If you trust the sourc of this workbook, click Yes."

When i click yes it tells me it can not open the file.

This file contains a pivot table but so does the Excel 2003 file.

I may be way off base with the determine which type of file and changing the AddHeader to match the file type, but I could not get the original method for opening an Excel 2003 file to work on the Excel 2007 file.

[mendhak]

Does the user get this message or is this a caught exception?

From what I see, all you're doing is writing the file out to the stream so that the user either sees the file or gets a download prompt.

[kul2bme]

The user gets this error message.

[mendhak]

According to this blog post, the MIME type for XLSX files is

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

If the user is getting the error then it's possible that the browser or something on the user's machine is not interpreting it properly

[kul2bme]

Thx for the info mendhak. I changed the code to reflect the recommended Content Type (see below). Same results, would not open the file. Then, I also changed the Excel file from a template(.xltx) to a file (.xlsx), still containing the pivot table to external source, which gave me different results.

With this new way I still received the initial error that I received before, BUT it actually opened the file, by clicking Yes, with the following message:

"Repairs to 'FileName(1).xlsx'"
"Excel was able to open the file by repairing or removing the unreadable content."
"Excel completed file level validation and repair. Some parts of the workbook may have been repaired or discarded."


The file seemed OK so I actually saved it and replaced the old template file with this thinking maybe that would help. Still did the same thing. SO, I guess at this point I am unclear on whether this is a ASP issue or is this an Excel issue???

I certainly do not want to post in the wrong place but I do believe this will be helpful to those in this forum as the need arises to work more with Excel 2007.

Private Function OpenExcelTemplate(ByVal sTemplatePath As String) As Boolean
        Try
            Dim iPos As Integer = InStrRev(sTemplatePath, ".")
            If Len(sTemplatePath) - iPos = 3 Then
                Response.Clear()
                Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
                Response.Charset = ""
                Response.ContentType = "application/vnd.xls"
                Response.WriteFile(sTemplatePath)
                Response.Flush()
            Else
                Response.Clear()
                Response.AddHeader("content-disposition", "attachment;filename=FileName.xlsx")
                Response.Charset = ""
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                Response.WriteFile(sTemplatePath)
                Response.Flush()

            End If


            OpenExcelTemplate = True


        Catch ex As Exception
            Me.WucViewQuery1.DisplayError(ex.Message)

        End Try
    End Function

[mendhak]

It really depends on the XLSX file. You have the XLSX file with you somewhere on disk. Find it and copy it straight to your desktop (via FTP or Explorer) and then open it. Does the error still show up? If yes, then it's definitely a problem with the XLSX file itself, not your code. I really doubt it would be your code causing the problem here.

[kul2bme]

Here is the solution to the issue. Sometimes it is hard to see the forest for the trees!

Without the Response.End code, there was "junk" hanging for Excel to trip on.

Private Function OpenExcelTemplate(ByVal sTemplatePath As String) As Boolean
        Try
            Dim iPos As Integer = InStrRev(sTemplatePath, ".")
            If Len(sTemplatePath) - iPos = 3 Then
                Response.Clear()
                Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
                Response.Charset = ""
                Response.ContentType = "application/vnd.xls"
                Response.WriteFile(sTemplatePath)
                Response.Flush()
            Else
                Response.Clear()
                Response.AddHeader("content-disposition", "attachment;filename=FileName.xlsx")
                Response.Charset = ""
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                Response.WriteFile(sTemplatePath)
                Response.Flush()

            End If


            OpenExcelTemplate = True


        Catch ex As Exception
            Me.WucViewQuery1.DisplayError(ex.Message)

        End Try
        Response.End()
    End Function

[mendhak]

Interesting to know, never come across that before!