1

I have written code for exporting data to xlsx file. But i dont understand how to show window prompt for downloading that xlsx file at client end. Here's my code:

Private Sub DataTableToExcel(ByVal tbl As DataTable)
    Dim Excel As Object = CreateObject("Excel.Application")
    Dim strFilename As String
    Dim intCol, intRow As Integer
    Dim strPath As String = "C:\"


    If Excel Is Nothing Then
        MsgBox("It appears that Excel is not installed on this machine. This operation requires MS Excel to be installed on this machine.", MsgBoxStyle.Critical)
        Return
    End If
    Try
        With Excel
            .SheetsInNewWorkbook = 1
            .Workbooks.Add()
            .Worksheets(1).Select()

            .cells(1, 1).value = "Complaint Detail Report" 'Heading of the excel file
            .cells(1, 1).EntireRow.Font.Bold = True


            Dim intI As Integer = 1
            For intCol = 0 To tbl.Columns.Count - 1
                .cells(2, intI).value = tbl.Columns(intCol).ColumnName
                .cells(2, intI).EntireRow.Font.Bold = True
                intI += 1
            Next
            intI = 3
            Dim intK As Integer = 1
            For intCol = 0 To tbl.Columns.Count - 1
                intI = 3
                For intRow = 0 To tbl.Rows.Count - 1
                    .Cells(intI, intK).Value = tbl.Rows(intRow).ItemArray(intCol)
                    intI += 1
                Next
                intK += 1
            Next
            If Mid$(strPath, strPath.Length, 1) <> "\" Then
                strPath = strPath & "\"
            End If
            strFilename = strPath & "ComplaintDetail.xlsx"
            .ActiveCell.Worksheet.SaveAs(strFilename)
        End With
        System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
        Excel = Nothing
        MsgBox("Data's are exported to Excel Succesfully: Location: '" & strFilename & "'", MsgBoxStyle.Information)
        ' Response.AddHeader("content-disposition", "attachment;filename=ComplaintDetail.xlsx")
        'Response.ContentType = "application/vnd.excel"
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
    For Each i As Process In pro
        i.Kill()
    Next

End Sub

Here I am saving .XLSX file directly to "C Drive". Why I choose C Drive? : Because 99% of people have C: in there pc. But I got some scenario where user don't allow access of their C drive or they don't give permission to write anything inside c drive. That's why I am trying to add this window prompt where user will decide where to save that file. But i got some issue in above code. Can you please help me to add window prompt in above code?

Kishor Rajendra Kulkarni
  • 57
  • 2
  • 15
  • Why did you put a C# tag? – Gab Sep 04 '14 at 05:59
  • Is it a windows application? If yes then you should not tagged it with asp.net. – Priyank Sep 04 '14 at 06:04
  • No @Priyank.. Its not windows application... Its a web application – Kishor Rajendra Kulkarni Sep 04 '14 at 06:07
  • There's no need to loop through processes looking for excel and calling Kill() if your releasing these object's correctly, you should NEVER have to do this... – Trevor Sep 04 '14 at 06:29
  • Thanks for your reply @MrCoDeXeR.. I have updated my code as per your suggestion... But can you please tell me how to show window prompt or anything through which user can save file where ever he wants.. – Kishor Rajendra Kulkarni Sep 04 '14 at 06:33
  • After your End Try put a finally in there and call GC.Collect which in return will release the object and free it, hence this is why your doing it the way you did because it wasn't being released. You can find more detail here http://stackoverflow.com/questions/15697282/excel-application-not-quitting-after-calling-quit – Trevor Sep 04 '14 at 06:34
  • Sorry to burst your hope @KishorRajendraKulkarni but you cannot save files to the client's machine from the web browser because it has security implications. Could you imagine if you browsed to a site and they got access to store files on your machine? – Trevor Sep 04 '14 at 06:38
  • they can download the file, but that's it you can't pick it for them... – Trevor Sep 04 '14 at 06:40
  • ohh... thats to bad... :(. But thanks for your valuable comment on releasing object part. – Kishor Rajendra Kulkarni Sep 04 '14 at 06:59

2 Answers2

0
  1. Save in the App_Data directory. You can find the absolute path with Server.MapPath("~/App_Data") This path is writeable by the application
  2. Use Response.TransmitFile to make the file to be downloaded.
onof
  • 17,167
  • 7
  • 49
  • 85
-1

Try using something like a save file dialog (this can be added via the ui designer). Then use:

If dialog.Show() = Windows.Forms.DialogResult.OK Then
     strPath = dialog.FileName
End If
Forrest4096
  • 159
  • 1
  • 8