Trouble Downloading Excel File Updated with EPPlus: File Being Blocked - How to Resolve?

Question

I am using EPPus-LGPL for Editing and downloading an excel. But always I am getting the file as blocked; and i need to go to properties then Unblock the file to open always. I tried to Unblock using the code but it doesn't work. So please give me the solution to unblock the Excel from code.

Button Click For Download Excel

 Protected Sub btnExcell_Click(sender As Object, e As EventArgs) Handles btnExcell.Click
    Try
        Dim year As String = ""
        Dim month As String = ""

        Dim selectedYear As Integer = Convert.ToInt32(ddlYear.SelectedValue)
        Dim selectedMonth As Integer = Convert.ToInt32(ddlMonth.SelectedValue)

        year = selectedYear
        month = selectedMonth


        If String.IsNullOrEmpty(year) Then
            year = ""
        End If

        If String.IsNullOrEmpty(month) Then
            month = ""
        End If

        ' Parse year and month
        'Dim selectedYear As Integer = Convert.ToInt32(year)
        'Dim selectedMonth As Integer = DateTime.ParseExact(month, "MMMM", System.Globalization.CultureInfo.CurrentCulture).Month

        ' Calculate StartDate and EndDate
        Dim startDate As New DateTime(selectedYear, selectedMonth, 1)
        Dim endDate As DateTime = startDate.AddMonths(1).AddDays(-1) ' Last day of the month

        Dim monthName As String = startDate.ToString("MMMM", CultureInfo.InvariantCulture)

        Dim centralBankReport As New CentralBankReportVM()
        centralBankReport = PopulateCentralBankReport(startDate, endDate)

        ' Path to the original Excel file
        Dim originalFilePath As String = Server.MapPath("~/Files/CentralBankReport/sample.xlsx")
        Using package As New OfficeOpenXml.ExcelPackage(New FileInfo(originalFilePath))

            'Dim worksheet As OfficeOpenXml.ExcelWorksheet = package.Workbook.Worksheets("central_bank_of_somalia_Demo.xlsx") ' Replace "Sheet1" with the actual name of your worksheet



            Dim worksheet = package.Workbook.Worksheets(1)
            'Dim worksheet = package.Workbook.Worksheets("central_bank_of_somalia_Demo.xlsx")

            ' Modify individual cells
            worksheet.Cells("F20").Value = year
            worksheet.Cells("L20").Value = monthName

            worksheet.Cells("F55").Value = "$" & centralBankReport.TotalUSDToSO
            worksheet.Cells("I55").Value = "$" & centralBankReport.TotalUSDFrSO

           
            worksheet.Cells("F69").Value = "$0.00"
            worksheet.Cells("L69").Value = "$" & centralBankReport.OtherExpense
             

            worksheet.Cells("F75").Value = "$0.00"
            worksheet.Cells("L75").Value = "$0.00"


            worksheet.Cells("K43").Value = DateTime.Now.ToString("dd-MMM-yyyy")

            package.Save()

            UnblockFile(originalFilePath)
            ' Prepare the modified Excel file for download
            Dim fileBytes As Byte() = File.ReadAllBytes(originalFilePath)
            Dim fileName As String = "sample_" & DateTime.Now.ToString("yyyyMMdd_HHmmss") & ".xlsx"

            ' Set the content type and headers
            Response.Clear()
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

            Response.Buffer = True
            '  Response.AddHeader("content-disposition", "attachment;filename=" & fileName)
            ' Response.BinaryWrite(fileBytes)
            Response.AddHeader(
        "content-disposition", String.Format(CultureInfo.InvariantCulture, "attachment; filename={0}", fileName))
            Response.BinaryWrite(package.GetAsByteArray())
            Response.End()
        End Using
    Catch ex As Exception
        Dim message As String = ex.Message
        Response.Write("An error occurred while processing the request.")
    End Try
End Sub

Unblock Function Currently have:

Private Sub UnblockFile(filePath As String)
    Try
        ' Use PowerShell to unblock the file
        Dim powerShellCommand As String = "Unblock-File -Path """ & filePath & """"
        Dim processInfo As New ProcessStartInfo("powershell.exe", "-ExecutionPolicy Bypass -Command """ & powerShellCommand & """")
        processInfo.CreateNoWindow = True
        processInfo.UseShellExecute = False
        processInfo.RedirectStandardOutput = True

        Using process As New Process()
            process.StartInfo = processInfo
            process.Start()
            process.WaitForExit()
        End Using
    Catch ex As Exception
        ' Log or display the exception message for debugging
        Response.Write("An error occurred while unblocking the file: " & ex.Message)
    End Try
End Sub

Thanks in advance.

Answer 1

Seems like the stream related to your original file doesn't closed at moment you are trying to open the file.

Thy move the End Using right after the package.Save() line.

And then replace the

Response.BinaryWrite(package.GetAsByteArray()) 

line by

Response.BinaryWrite(File.ReadAllBytes(originalFilePath))