I am trying to create an Excel sheet and save it in server... Once it is saved, I display a link to the user "download excel". He can click it and then download it.. The pblm is I cant save the excel it says "The file could not be accessed." please help me out.
Protected Sub btn_Excel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Excel.Click
Dim dt As DataTable = New DataTable()
dt = get_SearchRecords()
Dim obj As ExcelHelper = New ExcelHelper
obj.CheckExcellProcesses()
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorksheet As Excel.Worksheet = Nothing
Dim oxl = New Excel.Application()
xlWorkBook = New Excel.Application().Workbooks.Add(Missing.Value)
Dim strCurrentDir As String = Server.MapPath(".") + "\\"
Try
obj.RemoveFiles(strCurrentDir)
xlWorkBook = oxl.Workbooks.Add(Missing.Value)
xlWorkBook.Application.Visible = True
xlWorksheet = xlWorkBook.ActiveSheet
xlWorksheet.Name = "SAMPLE" xlWorksheet.Cells(1, 1) = "ID"
xlWorksheet.Cells(1, 2) = "Title"
xlWorksheet.Cells(1, 3) = "Description"
xlWorksheet.Cells(1, 4) = "Created By"
xlWorksheet.Cells(1, 5) = "Created On"
xlWorksheet.Cells(1, 6) = "Sta"
xlWorksheet.Cells(1, 7) = "Team"
xlWorksheet.Cells(1, 8) = "Challenge"
xlWorksheet.Cells(1, 9) = "Category"
xlWorksheet.Cells(1, 10) = "Sub Category"
xlWorksheet.Cells(1, 11) = "Region"
xlWorksheet.Cells(1, 12) = "Country"
xlWorksheet.Cells(1, 13) = "Votes Up"
xlWorksheet.Cells(1, 14) = "Votes Down"
xlWorksheet.Cells(1, 15) = "Docs"
Dim dtexcel As New DataTable()
dtexcel = dt
If (dtexcel.Rows.Count > 0) Then
Dim i As Integer = 2
xlWorksheet.Range("A1", "O1").Font.ColorIndex = Excel.Constants.xlColor3
xlWorksheet.Range("A1", "O1").Font.Bold = True
xlWorksheet.Range("A1", "O1").HorizontalAlignment = HorizontalAlign.Center
xlWorksheet.Range("A1", "O1").VerticalAlignment = VerticalAlign.Top
For Each dr In dt.Rows
xlWorksheet.Cells(i, 1) = dr("ID")
xlWorksheet.Cells(i, 2) = dr("TITLE")
xlWorksheet.Cells(i, 3) = dr("DESCRIPTION")
xlWorksheet.Cells(i, 4) = dr("Submitter")
xlWorksheet.Cells(i, 5) = dr("Createdon")
xlWorksheet.Cells(i, 6) = dr("STA")
xlWorksheet.Cells(i, 7) = dr("TEAM")
xlWorksheet.Cells(i, 8) = dr("CHALLENGE")
xlWorksheet.Cells(i, 9) = dr("CATEGORY")
xlWorksheet.Cells(i, 10) = dr("SUBCATEGORY")
xlWorksheet.Cells(i, 11) = dr("REGION")
xlWorksheet.Cells(i, 12) = dr("COUNTRY")
xlWorksheet.Cells(i, 13) = dr("voteup")
xlWorksheet.Cells(i, 14) = dr("votedown")
xlWorksheet.Cells(i, 15) = dr("doc_cnt")
xlWorksheet.Range("H1", "H" & i).RowHeight = 16.0
xlWorksheet.Range("H" & i, "H" & i).ColumnWidth = 25.0
i = i + 1
Next
xlWorksheet.Range("A1", "O" & i).Font.Name = "Verdana"
End If
Dim strFile As String = "SAMPLE_" & System.DateTime.Now.Ticks.ToString() & ".xls"
xlWorkBook.SaveAs(strCurrentDir & strFile, Excel.XlFileFormat.xlWorkbookNormal, System.DBNull.Value, System.DBNull.Value, False, False, Excel.XlSaveAsAccessMode.xlShared, False, False, System.DBNull.Value, System.DBNull.Value, System.DBNull.Value)
Dim strMachineName = Request.ServerVariables("SERVER_NAME")
Dim File = "http://" & strMachineName + "/" & "Folder" & "/" & strFile
Dim filename As String = strCurrentDir & strFile
errLabel.Text = "<A class=addlink href=" + File.Replace(" ", "%20") + ">Download Excel</a>"Exception is caught at this line
xlWorkBook.SaveAs(strCurrentDir & strFile, Excel.XlFileFormat.xlWorkbookNormal, System.DBNull.Value, System.DBNull.Value, False, False, Excel.XlSaveAsAccessMode.xlShared, False, False, System.DBNull.Value, System.DBNull.Value, System.DBNull.Value)
What could be the problem?