Excel file "Open" "Save As" dialog box not showing in asp.net

I have searched S/O, but unable to find a solution that helps my problem. I am exporting data to an xls file from my asp.net application. I need to display the Open / Sav开发者_开发百科e As / Cancel dialog box when the user clicks "Export Data" button.

How do I accomplish that?

        oSheet = Nothing
        oRange = Nothing
        oWB.SaveAs(sFileName.ToString())
        oWB.Close()
        oWB = Nothing
        oXL.Quit()

Try this code block. You may have to fine tune it. The Response block of the code has to do with the dialog box:

Private Sub GenerateXLSXFile(tbl As DataTable) 

    Dim excelPackage = New ExcelPackage 

    Dim excelWorksheet = excelPackage.Workbook.Worksheets.Add("DemoPage") 

    excelWorksheet.Cells("A1").LoadFromDataTable(tbl, True) 

    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" 
    Response.AddHeader("content-disposition", "attachment;  filename=ExcelDemo.xlsx") 

    Dim stream As MemoryStream = New MemoryStream(excelPackage.GetAsByteArray()) 

    Response.OutputStream.Write(stream.ToArray(), 0, stream.ToArray().Length) 

    Response.Flush() 

    Response.Close() 

End Sub 

You can do it several ways. Your code sample you gave has nothing to do with that, but apparently your creating the file and saving it to disk. Seeing as you have the file path, you could use Response.WriteFile. You need to add the Content-Disposition to bring up the Save As dialog.

Response.AddHeader("Content-Disposition", "attachment; filename=EXCELFILE.xs")
Response.ContentType = "application/ms-excel" // not sure you need the contenttype, and it may be different
Response.WriteFile(FULLPATHTOFILE)

Uhm. You do not. Your excel objects are running in the ASP.NET process ON THE WEB SERVER. There's no way to make the dialog show up on the CLIENT (BROWSER) side.

public void export()
{
try
{
    if (Session["ExcelGrid"] != null)
    {
        DataSet DS = null;
        if (Session["ExcelGrid"] != null)
        {
            DS = (DataSet)Session["ExcelGrid"];
            if (DS.Tables[0].Rows.Count > 0)
            {
                int j = 0;
                int k = 0;
                string FileName = string.Concat(DateTime.Now.ToString(), "Order");
                DataTable dt = DS.Tables[0];

                dt.Columns.Remove("UserRole");
                dt.Columns.Remove("iControlDeviationID");
                dt.Columns.Remove("sErrorCode");
                dt.Columns.Remove("sItemCategory");
                dt.Columns.Remove("sItemSubCategory");
                dt.Columns.Remove("iDeviationID");
                dt.Columns.Remove("iControlPointID");
                dt.Columns.Remove("sFacility");
                dt.Columns.Remove("sItemCategory1");
                dt.Columns.Remove("sItemSubCategory1");
                dt.Columns.Remove("sRegion");
                dt.Columns.Remove("sCountry");
                dt.Columns.Remove("sItem");
                dt.Columns["dRegisterDate"].SetOrdinal(5);


                Context.Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
                Context.Response.ContentType = "application/vnd.ms-excel";
                Context.Response.Charset = "";
                Context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("ISO-8859-1");
                Context.Response.Write("<table border=0 width='100%'><tr align = 'center'>");
                for (j = 0; j < 30; j++)
                {
                    if (j < 1)
                    {
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (dt.Columns[i].Caption.ToString() == "sDeviation")
                                Context.Response.Write("<td style='border:#D0D7E5 1px solid; border-left:none;border-bottom:none'><b>" + Resources.Resource.DeviationTypes.ToString().ToUpper() + "</b></td>");
                            if (dt.Columns[i].Caption.ToString() == "sDeviationCriticalLevel")
                                Context.Response.Write("<td style='border:#D0D7E5 1px solid; border-left:none;border-bottom:none'><b>" + Resources.Resource.DeviationCriticalLevel.ToString().ToUpper() + "</b></td>");
                            if (dt.Columns[i].Caption.ToString() == "sObject")
                                Context.Response.Write("<td style='border:#D0D7E5 1px solid; border-left:none;border-bottom:none'><b>" + Resources.Resource.Object.ToString().ToUpper() + "</b></td>");
                            if (dt.Columns[i].Caption.ToString() == "sControlArea")
                                Context.Response.Write("<td style='border:#D0D7E5 1px solid; border-left:none;border-bottom:none'><b>" + Resources.Resource.ControlArea.ToString().ToUpper() + "</b></td>");
                            if (dt.Columns[i].Caption.ToString() == "sControlPoint")
                                Context.Response.Write("<td style='border:#D0D7E5 1px solid; border-left:none;border-bottom:none'><b>" + Resources.Resource.ControlPoint.ToString().ToUpper() + "</b></td>");
                            if (dt.Columns[i].Caption.ToString() == "dRegisterDate")
                                Context.Response.Write("<td style='border:#D0D7E5 1px solid; border-left:none;border-bottom:none'><b>" + Resources.Resource.RegisteredDate.ToString().ToUpper() + "</b></td>");
                        }
                    }
                    else
                    {
                        Context.Response.Write("<td style='border:#D0D7E5 1px solid; border-left:none;border-bottom:none'>" + " " + "</td>");
                    }
                }
                Context.Response.Write(" ");
                Context.Response.Write("</tr>");
                for (int l = 0; l < 100; l++)
                {

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        Context.Response.Write("<tr align = 'Left'>");
                        for (k = 0; k < j; k++)
                        {
                            if (k < 1)
                            {
                                for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
                                {
                                    if (l < 1)
                                    {
                                        Context.Response.Write("<td style='border:#D0D7E5 1px solid; border-left:none;border-bottom:none'>" + dr[iCol].ToString() + "</td>");
                                    }
                                    else
                                    {
                                        Context.Response.Write("<td style='border:#D0D7E5 1px solid; border-left:none;border-bottom:none'>" + " " + "</td>");
                                    }
                                }
                            }
                            else
                            {
                                Context.Response.Write("<td style='border:#D0D7E5 1px solid; border-left:none;border-bottom:none'>" + " " + "</td>");
                            }
                        }
                    }
                }
                Context.Response.Write(" ");
                Context.Response.Write("</tr>");
                Context.Response.Write(" ");
                Context.Response.Write("</table>");

                Response.Buffer = true;
                Context.Response.Flush();                       
                Context.Response.Close();
                Context.Response.End();
                //ScriptManager.RegisterStartupScript(this, GetType(), "refresh", "window.setTimeout('window.location.reload(true);',5000);", true);
            }
        }
    }
}
catch (Exception ex)
{
    Response.Write(ex.Message.ToString());
}
finally
{
    ScriptManager.RegisterStartupScript(this, GetType(), "refresh", "a.aspx;", true);
}
}