This project has moved. For the latest updates, please go here.

Excel 2010 Protected View from Web Page

Aug 31, 2012 at 1:21 PM

Hello,

Our users have recently been upgraded to Excel 2010 and since have been having problems when exporting an Excel file (.xlsx) from our intranet web pages. The Excel file opens, but it displays a message at tope of screen saying it's open in "Protected View". It also shows an option to enable editing, but this button will not work. I have found that if the user chooses to SAVE the Excel file (when you get the option to Open or Save), it will work fine and does not display the protected view.

The issue I have in protected view is that the AutoFilters which I have set will not work in protected view. Are there any work-a-rounds for this, rather than asking the user to save the file, rather than just opening it?

BTW, this is the code I use to export the excel file:

Thanks so much !

Protected Sub imgExportToExcel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgExportToExcel.Click

        'User clicked the button to export to excel.

        Dim conn As SqlConnection = New SqlConnection(connString)
        Dim adapter As SqlDataAdapter = New SqlDataAdapter
        adapter.SelectCommand = New SqlCommand(lblSqlExcel.Text, conn)
        Dim DT As New DataTable
        conn.Open()
        Try
            adapter.Fill(DT)
        Finally
            If conn IsNot Nothing Then
                If conn.State = ConnectionState.Open Then conn.Close()
                conn = Nothing
            End If
        End Try


        Using pck As New ExcelPackage()
            'Create the worksheet 
            Dim ws As ExcelWorksheet = pck.Workbook.Worksheets.Add("Raw_Data")

            'Load the datatable into the sheet, starting from cell A1. Print the column names on row 1  
            ws.Cells("A1").LoadFromDataTable(DT, True)

            'count the columns
            Dim ColumnCount As String = ""
            ColumnCount = DT.Columns.Count - 1

            'count the rows
            Dim RowCount As String = ""
            RowCount = DT.Rows.Count

            'prepare the range for the column headers 
            Dim headerRange As String = "A1:J1"


            'Format the header for columns 
            Using rng As ExcelRange = ws.Cells(headerRange)
                rng.Style.WrapText = False
                rng.Style.Font.Bold = True

                rng.Style.Fill.PatternType = ExcelFillStyle.Solid
                rng.Style.Fill.BackgroundColor.SetColor(Color.Silver)

                rng.Style.Font.Color.SetColor(Color.Black)
            End Using

            'format as Date/Time AM/PM
            ws.Cells("F:F").Style.Numberformat.Format = "mm/dd/yy h:mm AM/PM;@"

            ws.Cells("A:J").Style.HorizontalAlignment = ExcelHorizontalAlignment.Center
            'ws.Cells("A:J").Style.WrapText = False

            'freeze panes at cell A2
            ws.View.FreezePanes(2, 1)

            'Create an autofilter for the header range
            ws.Cells(headerRange).AutoFilter = True

            'minimum column width can be specified
            ws.Cells("A:J").AutoFitColumns(10)

            'Read the Excel file in a byte array
            Dim fileBytes As [Byte]() = pck.GetAsByteArray()

            'Clear the response 
            Response.Clear()
            Response.ClearContent()
            Response.ClearHeaders()
            Response.Cookies.Clear()
            'Add the header & other information 
            Response.Cache.SetCacheability(HttpCacheability.[Private])
            Response.CacheControl = "private"
            Response.Charset = System.Text.UTF8Encoding.UTF8.WebName
            Response.ContentEncoding = System.Text.UTF8Encoding.UTF8
            Response.AppendHeader("Content-Length", fileBytes.Length.ToString())
            Response.AppendHeader("Pragma", "cache")
            Response.AppendHeader("Expires", "60")
            Response.AppendHeader("Content-Disposition", "attachment; " + "filename=""ExcelReport.xlsx""; " + "size=" + fileBytes.Length.ToString() + "; " + "creation-date=" + DateTime.Now.ToString("R") + "; " + "modification-date=" + DateTime.Now.ToString("R") + "; " + "read-date=" + DateTime.Now.ToString("R"))
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            'Write it back to the client 
            Response.BinaryWrite(fileBytes)
            Response.[End]()
        End Using

        'clear out the data table, we're done with it
        DT.Clear()
    End Sub

 

Paul

Editor
Aug 31, 2012 at 1:37 PM
Edited Aug 31, 2012 at 1:38 PM

I don't know any workaround to help you, but maybe I can help you to find out a solution. Instead of "Save" your file, choose "Save As", then rename yours files to .zip, extract them, compare their content with WinMerge and search to different files. 

Aug 31, 2012 at 1:50 PM

That is an interesting idea, I'll give it a try and if I come up with anything - I'll post back here. Thank you !

Sep 11, 2012 at 3:36 PM
Edited Sep 11, 2012 at 3:39 PM

Probably some side-effect of Excel 2010 Protected View, not EPPlus' fault I don't think.

You can disable in Excel Options -> Trust Center -> Trust Center Settings... button --> Protected View -> and uncheck "Enable Protected View for files originating from the Internet".  I'm not sure if there's way to disable Protected View just for Intranet though...