用户选择使用VBA从 ASP.net 导出到Excel时保存文件的位置

Question

我有一个函数，它使用工作表VBA编码从 ASP.net 写入EXCEL，我在其中导出数据表并按照我想要的方式修改每个Excel单元格。

但是我有一个问题：在导出时，我想问用户是否要打开或保存文件

我写了这段代码，打开/另存为弹出窗口不起作用：

（我找到了无需设计即可将数据表导出到 excel 的代码，但我希望我的报表更好地组织）

 Sub WriteToExcel(ByVal DATE1 As String, ByVal DATE2 As String)
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        Dim DT As New DataTable
        Dim filename As String = "Report-" & Date.Now.ToShortDateString.Replace("/", "-") & " " & Date.Now.ToShortTimeString.Replace(":", "-") & ".xls"

        Try

            DT = Session("datatable")
            If DT.Rows.Count > 0 Then

                oExcel = CreateObject("Excel.Application")
                oBook = oExcel.Workbooks.Add
                oSheet = oBook.Worksheets(1)
                oSheet.Range("D1").Value = "Service Based Daily/Monthly Revenu"
                oSheet.Range("D1").Font.Bold = True
                oSheet.Range("D1").font.size = 13
                oSheet.Range("D2").Value = "From " & DATE1 & " to " & DATE2
                oSheet.Range("D2").Font.Bold = True
                oSheet.Range("D2").font.size = 13
                oSheet.Range("D1:E1:F1").MergeCells = True
                oSheet.Range("D2:E2:F2").MergeCells = True
                oSheet.Range("B4").Value = "Report Build Time: " & Date.Now.ToShortDateString & " " & Date.Now.ToShortTimeString
                oSheet.Range("B4").Font.Bold = False
                oSheet.Range("B4").font.size = 11
                oSheet.Range("B4:C4:D4:E4").Interior.Color = RGB(200, 200, 172)
                oSheet.Range("B4:C4:D4:E4").MergeCells = True

                oSheet.Range("B6").Value = "Service Name"
                oSheet.Range("C6").Value = "Hits"
                oSheet.Range("D6").Value = "Revenue"
                oSheet.Range("E6").Value = "Service Cost"

                With oSheet.Range("B6:C6:D6:E6")
                    .Font.Bold = True
                    .font.size = 13
                    .font.Color = RGB(0, 0, 0)
                    .Interior.Color = RGB(70, 134, 196)
                    .HorizontalAlignment = -4108
                    .VerticalAlignment = -4108
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .EntireColumn.ColumnWidth = 20
                    .MergeCells = False
                End With

                Dim i As Integer = 7
                For Each dRow As DataRow In DT.Rows
                    oSheet.Range("B" & i).Value = dRow("Service_Name").ToString()
                    oSheet.Range("C" & i).Value = dRow("Hits").ToString()
                    oSheet.Range("D" & i).Value = dRow("Revenue").ToString()
                    oSheet.Range("E" & i).Value = dRow("Service_Cost").ToString()

                    oSheet.Range("B" & i & ":C" & i & ":D" & i & ":E" & i).HorizontalAlignment = -4108
                    oSheet.Range("B" & i & ":C" & i & ":D" & i & ":E" & i).VerticalAlignment = -4108
                    i += 1

                Next
                Response.ContentType = "application/vnd.ms-excel"
                Response.AddHeader("Content-Disposition", "inline; filename=" & filename)
                Response.BinaryWrite(oExcel)

                oBook = Nothing
                oExcel.Quit()
                oExcel = Nothing
                GC.Collect()
            End If

    End Sub

我想更改这部分：

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "inline; filename=" & filename)
Response.BinaryWrite(oExcel) 

我希望用户选择他想要保存文件的位置。有什么建议吗？

Answer 1

您应该能够使用附件而不是内联来强制另存为对话框。

Response.Clear
Response.AddHeader("Content-Disposition", "attachment; filename=" & filename)