locked
How do I add in a save dialogue in my export to excel codes? RRS feed

  • Question

  • User41614756 posted

    Hi Members,

    I will be saving my excel file upon export using 2 methods, 1st to save as directly in server, 2nd to come up with a save dialogue to let user choose their own location thus in this case 2 files will be saved in total in each export which I am able to save directly into the server but How do I add in a save dialogue in my export to excel codes?

    protected void EXPORT_BUTTON_Click(object sender, EventArgs e)
    {
    Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
     
    // creating new WorkBook within Excel application
    Microsoft.Office.Interop.Excel._Workbook workbook  =  app.Workbooks.Add(Type.Missing);
     
    String DT1 = "Data table 1";
    String DT2 = "Data table 2";
     
    ExportToExcel(app, workbook, Gridview1, DT1, 1);
     
    ExportToExcel(app, workbook, Gridview2, DT2, 2);   
    
    workbook.SaveAs(@"C:\Users\testacc\Desktop\Test\" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); }

      public void ExportToExcel(Microsoft.Office.Interop.Excel._Application app, Microsoft.Office.Interop.Excel._Workbook workbook, GridView gridview, string SheetName)
            {
                // see the excel sheet behind the program
                app.Visible = false;
    
                Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add();
               
                // changing the name of active sheet
                worksheet.Name = SheetName;
    
                // storing header part in Excel
                for (int i = 1; i < gridview.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = gridview.Columns[i - 1].HeaderText;
                }
    
    
    
                // storing Each row and column value to excel sheet
                for (int i = 0; i < gridview.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < gridview.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = gridview.Rows[i].Cells[j].Text.ToString();
                    }
                }
    
               
            }

    Thursday, October 15, 2015 4:17 AM

Answers

  • User-821857111 posted

    If you want to invoke the Save As dialogue on the client, you set the content-disposition to attachment, and the content type to application/octet-stream:

    var filename = datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx";
    workbook.SaveAs(@"C:\Users\testacc\Desktop\Test\" + filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    Response.ContentType = "application/octet-stream";
    Response.AddHeader("content-disposition", "attachment, filename=" + filename);
    Response.TrasmitFile(@"C:\Users\testacc\Desktop\Test\" + filename);



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 16, 2015 3:01 AM
  • User-821857111 posted

    Error 33 'System.Web.HttpResponse' does not contain a definition for 'TrasmitFile'
    Sorry - clumsy thumbs typo. That should be TransmitFile
    isit possible to save in a virtual path before invoking?
    Yes:

    workbook.SaveAs(Server.MapPath("~/" )+ filename, etc);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 16, 2015 4:49 AM

All replies

  • User-821857111 posted

    If you want to invoke the Save As dialogue on the client, you set the content-disposition to attachment, and the content type to application/octet-stream:

    var filename = datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx";
    workbook.SaveAs(@"C:\Users\testacc\Desktop\Test\" + filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    Response.ContentType = "application/octet-stream";
    Response.AddHeader("content-disposition", "attachment, filename=" + filename);
    Response.TrasmitFile(@"C:\Users\testacc\Desktop\Test\" + filename);



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 16, 2015 3:01 AM
  • User41614756 posted

    Hi Mike, I have an error with TrasmitFile saying

    Error 33 'System.Web.HttpResponse' does not contain a definition for 'TrasmitFile' and no extension method 'TrasmitFile' accepting a first argument of type 'System.Web.HttpResponse' could be found (are you missing a using directive or an assembly reference?) 

    Is it possible to put saveas in virtual path before invoke? or the saveas does not really save a file in the location before invoke a pop up?

    Friday, October 16, 2015 3:59 AM
  • User41614756 posted

    Hi mike,

    Does it save the file to workbook.SaveAs(@"C:\Users\testacc\Desktop\Test\" + filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing when using this? isit possible to save in a virtual path before invoking?

    I also receive an error with transmit file :

    Error 33 'System.Web.HttpResponse' does not contain a definition for 'TrasmitFile' and no extension method 'TrasmitFile' accepting a first argument of type 'System.Web.HttpResponse' could be found (are you missing a using directive or an assembly reference?) 

    thanks

    Friday, October 16, 2015 4:04 AM
  • User-821857111 posted

    Error 33 'System.Web.HttpResponse' does not contain a definition for 'TrasmitFile'
    Sorry - clumsy thumbs typo. That should be TransmitFile
    isit possible to save in a virtual path before invoking?
    Yes:

    workbook.SaveAs(Server.MapPath("~/" )+ filename, etc);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 16, 2015 4:49 AM
  • User41614756 posted

    Hi Mike,

    Thanks for your reply.

       
    Sunday, October 18, 2015 8:50 PM
  • User41614756 posted

    Thanks mike I solved my own problem.

    Monday, October 19, 2015 12:07 AM