I have searched through every possible answer on SO for a solution, but nothing has worked. I am basically creating an excel file from a database and sending the results to the response stream using EPPlus(OpenXML). The following code gives me an error when trying to open my generated excel sheet "[report].xlsx is locked for editing by 'another user'." It will open fine the first time, but the second time it's locked.
Dim columnData As New List(Of Integer)
Dim rowHeaders As New List(Of String)
Dim letter As String = "B"
Dim x As Integer = 0
Dim trendBy = context.Session("TRENDBY").ToString()
Dim dateHeaders As New List(Of String)
dateHeaders = DirectCast(context.Session("DATEHEADERS"), List(Of String))
Dim DS As New DataSet
DS = DirectCast(context.Session("DS"), DataSet)
Using excelPackage As New OfficeOpenXml.ExcelPackage
Dim excelWorksheet = excelPackage.Workbook.Worksheets.Add("Report")
'Add title to the top
With excelWorksheet.Cells("B1")
.Value = "Account Totals by " + If(trendBy = "Months", "Month", "Week")
.Style.Font.Bold = True
End With
'add date headers
x = 2 'start with letter B (aka 2)
For Each Header As String In dateHeaders
With excelWorksheet.Cells(letter + "2")
.Value = Header
.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right
.AutoFitColumns()
End With
x = x + 1
letter = Helper.GetColumnIndexToColumnLetter(x)
Next
'Adds the descriptive row headings down the left side of excel sheet
x = 0
For Each DC As DataColumn In DS.Tables(0).Columns
If (x < DS.Tables(0).Columns.Count) Then
rowHeaders.Add(DC.ColumnName)
End If
Next
Dim range = excelWorksheet.Cells("A3:A30")
range.LoadFromCollection(rowHeaders)
'Add the meat and potatoes of report
x = 2
For Each dTable As DataTable In DS.Tables
columnData.Clear()
For Each DR As DataRow In dTable.Rows
For Each item As Object In DR.ItemArray
columnData.Add(item)
Next
Next
letter = Helper.GetColumnIndexToColumnLetter(x)
excelWorksheet.Cells(letter + "3").LoadFromCollection(columnData)
With excelWorksheet.Cells(letter + "3")
.Formula = "=SUM(" + letter + "4:" + letter + "6)"
.Style.Font.Bold = True
.Style.Font.Size = 12
End With
With excelWorksheet.Cells(letter + "7")
.Formula = "=SUM(" + letter + "8:" + letter + "11)"
.Style.Font.Bold = True
.Style.Font.Size = 12
End With
With excelWorksheet.Cells(letter + "12")
.Style.Font.Bold = True
.Style.Font.Size = 12
End With
With excelWorksheet.Cells(letter + "13")
.Formula = "=SUM(" + letter + "14:" + letter + "20)"
.Style.Font.Bold = True
.Style.Font.Size = 12
End With
With excelWorksheet.Cells(letter + "21")
.Formula = "=SUM(" + letter + "22:" + letter + "23)"
.Style.Font.Bold = True
.Style.Font.Size = 12
End With
With excelWorksheet.Cells(letter + "24")
.Formula = "=SUM(" + letter + "25:" + letter + "26)"
.Style.Font.Bold = True
.Style.Font.Size = 12
End With
With excelWorksheet.Cells(letter + "27")
.Formula = "=SUM(" + letter + "28:" + letter + "29)"
.Style.Font.Bold = True
.Style.Font.Size = 12
End With
With excelWorksheet.Cells(letter + "30")
.Formula = "=SUM(" + letter + "3," + letter + "7," + letter + "12," + letter + "13," + letter + "21," + letter + "24," + letter + "27)"
.Style.Font.Bold = True
.Style.Font.Size = 12
End With
x = x + 1
Next
range.AutoFitColumns()
'send it to response
Using stream As New MemoryStream(excelPackage.GetAsByteArray())
context.Response.Clear()
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
context.Response.AddHeader("content-disposition", "attachment; filename=filetest.xlsx")
context.Response.OutputStream.Write(stream.ToArray(), 0, stream.ToArray().Length)
context.Response.Flush()
context.Response.Close()
End Using
End Using
