Excel export from selected gridpanel values in one button

  1. #1

    Excel export from selected gridpanel values in one button

    Hey everyone,

    I'm currently working on an internship project where I have to export values to an excel file,
    based on the selected rows in a GridPanel during the excel export button press.

    My environment is Asp.net mvc3 and my view is through the use of Razor.

    My current status:
    1. Check if any rows were actually selected [CHECK]
    2. Get the selected values in the controller [CHECK]
    3. Use them in retrieving the desired data [CHECK]
    4. Generate a DataSet object based on that [CHECK]
    5. Convert the DataSet object into Excel [CHECK]
    6. Send that excel file back to the user


    I've tried several ways but none of them really worked. The one that comes closest, and is imo also the worst option, is that I can create an excel file and store it on the server side. Then through the use of a html.actionlink calling a separate download function I can download that file from the server. (Yes, you read that correctly, two separate clicks.) Due to the system being multiple user based I'm assuming I'll risk one of the two following as well:

    1. File overwriting if the files use the same name giving some problems during downloading (Imagine 2+ people exporting at the same time).
    2. More and more files through unique file ID's (Possibly fixable by deleting the file after downloading but still not the best way imo).

    The best solution would be to just stream the file to the user after generating the file, without ever really creating the file server-side (and therefore getting the risk of overwriting during sending or something.) Also, I need it to be in just one button.

    At this moment the options I've tried all result in either an unauthorized access exception, some variations of unknown characters / invalid input exceptions and at the moment it also gives me a 200 / OK message without anything happening after just generating (not downloading by actionlink click).

    View (getSelectedIDs)
        <script type = "text/javascript">
            /** Get the selected values from the grid.
            *
            */
            var GetSelectedIds = function (targetGridPanel) {
                var ids = '';
                var selections = targetGridPanel.getSelectionModel().selected;
                for (var i = 0; i < selections.length; i++) {
                    //Comma is used for splitting later - Example string: "3,4,5,7,14,"
                    ids += selections.items[i].data.CompanyID + ',';
                }
                //Remove the last comma.
                ids = ids.substr(0, ids.length - 1);
                return ids;
            }
        </script>
    View (GridPanel)
    /** Other code */
        .ColumnModel(
            X.RowNumbererColumn().Width(36).Align(Alignment.Center),
            X.Column().Text("ID").DataIndex("CompanyID").Align(Alignment.Left).Hidden(true),
            X.Column().Text("公司名称").DataIndex("CompanyNameCN").Align(Alignment.Left),
            X.Column().Text("英文名称").DataIndex("CompanyNameEN").Align(Alignment.Left),
            X.Column().Text("成立时间").DataIndex("CreateYear").Width(55).Align(Alignment.Left),
            X.Column().Text("总部所在地").DataIndex("HeadQuarters").Align(Alignment.Right),
            X.Column().Text("股票代码").DataIndex("StockCode").Align(Alignment.Right),
            X.Column().Text("公司网站").DataIndex("CompanyWebsite").Align(Alignment.Center),
            X.Column().Text("其他情况说明").DataIndex("OtherDesc").Width(180).Align(Alignment.Right),
            X.TemplateColumn().Text("图示").DataIndex("Picture").Width(50).Align(Alignment.Left).Template(t => t.Html = "<a id=\"{Picture}\" href=\"#\" onclick=\"showModalDialog('/ReportTypes/ViewImage?ID={CompanyID}', null, 'status:no;center:yes;help:no;dialogWidth:1000px;dialogHeight:700px');\">({Picture})</a>"),
            X.TemplateColumn().Text("其他附件").DataIndex("Attachment").Width(55).Align(Alignment.Left).Template(t => t.Html = "<a id=\"{Attachment}\" href=\"#\" onclick=\"showModalDialog('/DownloadPage?c=ReportTypes&a=Attachment&ID={CompanyID}', null, 'status:no;center:yes;help:no;dialogWidth:515px;dialogHeight:250px');\">({Attachment})</a>"),
            X.Column().Text("资料来源").DataIndex("DocSource").Align(Alignment.Right),
            X.Column().Text("资料整理人").DataIndex("Doc").Width(65).Align(Alignment.Right),
            X.Column().Text("录入人").DataIndex("ModifyName").Width(55).Align(Alignment.Right)
    
    /** Some other code */
    
        .SelectionModel(
            X.CheckboxSelectionModel()
            .RowSpan(1)
            .ID("CheckBox")
            //.Mode(SelectionMode.Multi)
            )
         )
    
        .View(X.GridView().StripeRows(true))
        .TopBar(
            X.Toolbar()
                //Excel button
                .Items(X.Button()
                    .ID("btnExcelExport")
                    .Text("Export")
                    .Icon(Icon.PageExcel)
                    //TODO Translate english to chinese in the handler?
                    .Listeners(l => { 
                        l.Click.Handler = "if(Ext.getCmp('gpCompany').getSelectionModel().hasSelection()) return true; else{ Ext.MessageBox.show({title:'Prompt Message',msg:'No values chosen',icon:Ext.MessageBox.WARNING,buttons:Ext.Msg.OK }); return false; }"; 
                    })
                    .DirectEvents(de =>
                    {
                        //NOTE: Extra step to press yes for confirmation. Delete comments if desired anyway.
                        //de.Click.Confirmation.ConfirmRequest = true;
                        //de.Click.Confirmation.Title = "Confirmation";
                        //de.Click.Confirmation.Message = "Would you like to export these files?";
                        
                        de.Click.Url = Url.Action("GenerateExcelReport");
                        de.Click.Success = "App.gpCompany.getStore().reload();";
                        de.Click.ExtraParams.Add(new { selectedIDs = JRawValue.From("GetSelectedIds(App.gpCompany)") } );
                    })
            )
        )
    Controller(All of my tries, divided over two functions by now)
            public ActionResult DownloadExcelReport()
            {
                DirectoryInfo dirInfo = new DirectoryInfo(HostingEnvironment.MapPath("~/Files"));
                string filePath = @dirInfo.ToString() + "\\" + "ExcelReport.xlsx";
                string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    
                return new FilePathResult(filePath, contentType);
                
            }
    
            /** Exports selected IDs to excel.
             *
             */
            [HttpGet]
            public ActionResult GenerateExcelReport(string selectedIds)
            {
                // Get the data that is to be exported as an Excel file
                DataSet dsToExport = getCompanyMinesData(selectedIds);
                //string fileName = "ExcelExport" + DateTime.Today.Year.ToString() + DateTime.Today.Month.ToString("00") + DateTime.Today.Day.ToString("00") + ".xlsx";
                string fileName = "ExcelReport.xlsx";
    
                #region MemoryStream_To_XML_Serialization_Without_Temporary_File
                /**
                try
                {
                    //Create Memory Stream to store XML Data
                    MemoryStream ms = new MemoryStream();
                    dsToExport.WriteXml(ms);
                    ms.Position = 0; // Due to the direction of serialization.
    
                    //Convert Memory Stream to Byte Array
                    byte[] data = ms.ToArray();
    
                    //Creating the Context
                    Response.Clear();
    
                    //Heads up browser, here comes some XML
                    Response.ContentType = "text/xml; charset=utf-8";
                    Response.AddHeader("Content-Disposition:",
                        "attachment;filename=" + HttpUtility.UrlEncode(fileName));
    
                    //Set the size of the file so the progress bar reports that correctly
                    Response.AddHeader("Content-Length", data.Length.ToString());
    
                    //Option 1
                    XmlNode xml = 
    
                    XslCompiledTransform xtExcel = new XslCompiledTransform();
    
                    xtExcel.Load(Server.MapPath(fileName));
                    xtExcel.Transform(xml, null, this.Response.OutputStream);
                    this.Response.End();
    
                    //Option 2
                    //Download the file and prompt the user to save
                    Response.BinaryWrite(data);
                    Response.End();
                    ms.Flush();
                    ms.Close();
                }
                catch (Exception exc)
                {
                }
                */
                #endregion
    
                #region Writing_File_As_Stream_Without_Temporary_File
                /**
                // Create an excel file with the dataset
                ExportFileCreator.CreateExcelDocumentAsStream(dsToExport, fileName, Response);
                */
                #endregion
    
                // Create the file physically on in the Files folder, then find it and return it.
                #region Create_File_Locally_Then_Search_And_Send
                
                FileSearcher fileSearcher = new FileSearcher();
                DirectoryInfo dirInfo = new DirectoryInfo(HostingEnvironment.MapPath("~/Files"));
    
                ExportFileCreator.CreateExcelDocument(dsToExport, dirInfo.ToString() + "\\" + fileName);
    
                #region Search_For_File_And_Return
                /**
                List<FileNames> files = fileSearcher.GetFiles(dirInfo);
                string filePath = (from f in files
                                   where f.FileName == fileName
                                   select f.FilePath).First();
                string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    
                //Parameters to file are
                //1. The File Path on the File Server
                //2. The connent type MIME type
                //3. The parameter for the file save asked by the browser
                return File(filePath, contentType, fileName);
                //NOTE Change return value if taking this out of comments.
                */
                
                #endregion
                
                #endregion
                return DownloadExcelReport();
            }
    Related topics that I've found:
    Export the grid to excel
    Ajax Export Example
    Postback Export Example

    Since I'm new to asp.net and razor, and not having worked with MVC for quite a while, this is probably a very simple thing to fix.
    However I haven't been able to after quite some hours and the chinese company isn't able to help me either so I was hoping someone could help me out here. The main problem with the first related topic being that I can't figure out how to convert the given code to my own environment / where to put it.

    Regards
  2. #2

    [UPDATE] Current Status

    So I've been working on it for some more, and went through some other code to isolate the problem
    to the specific function.

            /** Exports selected IDs to excel.
             *
             */
            public void GenerateExcelReport(string selectedIds)
            {
                // Get the data that is to be exported as an Excel file
                DataSet dsToExport = getCompanyMinesData(selectedIds);
    
                //dirInfo.ToString() returns the current path and appends /Files to it.
                DirectoryInfo dirInfo = new DirectoryInfo(HostingEnvironment.MapPath("~/Files"));
    
                //string fileName = "(" + DateTime.Today.Year.ToString() + DateTime.Today.Month.ToString("00") + DateTime.Today.Day.ToString("00") + ") CompaniesReport.xlsx";
                string fileName = "ExcelReport.xlsx";
                string filePath = dirInfo.ToString() + "/" + fileName;
    
                #region Writing_File_As_Stream_Without_Temporary_File
    
                StreamWriter writer = new StreamWriter(filePath);
                System.Web.HttpResponse httpResponse = new HttpResponse(writer);
    
                // Create an excel file with the dataset and immediately return it.
                ExportFileCreator.CreateExcelDocumentAsStream(dsToExport, fileName, httpResponse);
    
                #endregion
                
                /** Some other approaches that are in comments */
    
                }
    To clarify:
    The CreateExcelDocumentAsStream converts the dataset, then throws it into a Response, which is then flushed/ended.

    When calling this function through my button, it returns the following error:
    Image
    Image content:

    Title: Request Failure
    Status Code: 200
    Status Text: OK
    Message:
    {}

    I've also got this function, unchanged as of the first post:
    
            public ActionResult DownloadExcelReport()
            {
                DirectoryInfo dirInfo = new DirectoryInfo(HostingEnvironment.MapPath("~/Files"));
                string filePath = @dirInfo.ToString() + "\\" + "ExcelReport.xlsx";
    
                return new FilePathResult(filePath, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");       
            }
    If I call it from the following statement, which I placed under the gridpanel:
    @Html.ActionLink("Download Excel", "DownloadExcelReport")
    it works perfectly - Just the way I want it to work. The browser just handles a normal file download according to the user's browser preferences.

    If I call it from my button, within the table, like this:
        .TopBar(
            X.Toolbar()
                //Excel button
                .Items(X.Button()
                    .ID("btnExcelExport")
                    .Text("Export")
                    .Icon(Icon.PageExcel)
                    .Listeners(l => { 
                        l.Click.Handler = "if(Ext.getCmp('gpCompany').getSelectionModel().hasSelection()) return true; else{ Ext.MessageBox.show({title:'Prompt Message',msg:'No values chosen',icon:Ext.MessageBox.WARNING,buttons:Ext.Msg.OK }); return false; }"; 
                    })
                    .DirectEvents(de =>
                    {                    
                        de.Click.Url = Url.Action("DownloadExcelReport"); // Normally GenerateExcelReport with the bottom two lines not in comment
                        //de.Click.Success = "App.gpCompany.getStore().reload();";
                        //de.Click.ExtraParams.Add(new { selectedIDs = JRawValue.From("GetSelectedIds(App.gpCompany)") } );
                    })
            )
        )
    It gives me a slightly different error than from before:

    Image
    Image content:

    Title: Request Failure
    Status Code: 200
    Status Text: NORESPONSE
    Message:

    I'm thinking it has something to do with the attributes - This is probably the next thing I'm going to look into.
    Last edited by Forvaine; Mar 31, 2014 at 3:35 AM. Reason: Fixed a typo in tag syntax
  3. #3
    Hi @Forvaine,

    Welcome to the Ext.NET forums!

    Please try this setting:
    de.Click.IsUpload = true;
    Here you can find more details:
    http://forums.ext.net/showthread.php...ng-DirectEvent

Similar Threads

  1. Replies: 0
    Last Post: Feb 12, 2014, 12:19 PM
  2. how to export selected columns from a grid to excel
    By deepu s nair in forum 2.x Help
    Replies: 0
    Last Post: Feb 05, 2013, 12:09 PM
  3. How can Export data from GridPanel to Excel
    By delta in forum 2.x Help
    Replies: 2
    Last Post: Nov 14, 2012, 1:07 AM
  4. [CLOSED] Gridpanel export Excel HeaderText
    By CPA1158139 in forum 1.x Legacy Premium Help
    Replies: 1
    Last Post: Jun 10, 2012, 9:36 AM
  5. How to export GridPanel to the Excel?
    By jachnicky in forum 1.x Help
    Replies: 3
    Last Post: Dec 01, 2008, 4:57 PM

Tags for this Thread

Posting Permissions