1. Home
  2. Forum
  3. ASP.NET MVC
  4. Exporting to Excel taking a lot of time

Exporting to Excel taking a lot of time

Hi, have a grid with 17 columns and 3614 row, when i press export it takes a lot of time to export it to excel, also if i group them takes more time to export, is there a way to reduce time?

regards

13 Replies
SE Sathyanarayanamoorthy Eswararao Syncfusion Team January 9, 2018 01:14 PM UTC

Hi Gian, 

We have analyzed your query and we are able to reproduce the issue. To avoid this issue we suggest you to set the theme for the grid as none while exporting the file. Because if the theme is passed then the exporting would take time. 

Please refer the code example below. 


 
public void ExportToExcel(string GridModel) 
        { 
            ………………… 
            GridProperties obj = ConvertGridObject(GridModel); 
            exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "none"); 
        } 
 
 
 
 

Regards, 
Sathyanarayanamoorthy 


GC Gian Carlo January 11, 2018 03:12 PM UTC

Thx for your reply i follow yous instructions but still taking a lot of time to export the content grouped, this is my code

public void ExportToExcel3(string GridModel)
        {
            ExcelExport exp = new ExcelExport();
           
            GridProperties obj = ConvertGridObject(GridModel);

          

            var context = new WebDBEntities();
            ViewBag.datasource = (from u in context.RCalculoPrecios select u).Take(3620).ToList();

            GridExcelExport exc = new GridExcelExport();

            exp.Export(obj, ViewBag.datasource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "none");
        }




        private GridProperties ConvertGridObject(string gridProperty)
        {

            JavaScriptSerializer serializer = new JavaScriptSerializer();
            IEnumerable div = (IEnumerable)serializer.Deserialize(gridProperty, typeof(IEnumerable));
            GridProperties gridProp = new GridProperties();
            foreach (KeyValuePair<string, object> ds in div)
            {
                var property = gridProp.GetType().GetProperty(ds.Key, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);
                if (property != null)
                {
                    Type type = property.PropertyType;
                    string serialize = serializer.Serialize(ds.Value);
                    object value = serializer.Deserialize(serialize, type);
                    property.SetValue(gridProp, value, null);
                }
            }
            return gridProp;
        }





SE Sathyanarayanamoorthy Eswararao Syncfusion Team January 12, 2018 12:45 PM UTC

Hi Gian, 

We are currently validating this issue, so we will update you the response by 17th January,2018. 

Regards, 
Sathyanarayanamoorthy 


GC Gian Carlo January 15, 2018 04:36 PM UTC

Thanks a lot


SE Sathyanarayanamoorthy Eswararao Syncfusion Team January 16, 2018 03:03 PM UTC

Hi Gian, 

We have checked in our sample and it takes only 23 seconds to export the grid with more than 400 rows and 18 columns. 

Please refer the below link to download the sample. 



Can you please tell us how long does it take in your sample to export the grid. So, we can check and provide the solution. 

If possible please share the complete code example of the grid. 

Regards, 
Sathyanarayanamoorthy 


GC Gian Carlo January 31, 2018 04:28 PM UTC

Hi sorry for the dela, i change the code to 
int code = 1000;
 for (int i = 1; i < 3000; i++) Exception of type 'System.OutOfMemoryException' was thrown.

if i use workbook.SaveAs( XlsIO ) how can i get the grouping from the div table or the current consult i have on screen



SE Sathyanarayanamoorthy Eswararao Syncfusion Team February 1, 2018 03:10 PM UTC

Hi Gian, 

Query:Exception of type 'System.OutOfMemoryException' was thrown. 
 
We checked in our sample and we are unable to reproduce the mentioned issue. Please share the following details.  
 
1.       Share the exact scenario in which you are facing this issue. 
2.       video or screen shot of the issue replication procedure. 
 
 
Query: if i use workbook.SaveAs( XlsIO ) how can i get the grouping from the div table or the current consult i have on screen 
 
We have analyzed your query and found that you need to save the grid view model(i.e. grouped or ungrouped) on the screen in the workbook while exporting. We have achieved your requirement in the following sample and the same can be found below. 
 
 
Please refer the code example 

  
 public void ExportToExcel(string GridModel) 
        { 
            ExcelExport exp = new ExcelExport(); 
            ExcelEngine excel = new ExcelEngine(); 
            IApplication application = excel.Excel; 
            BindDataSource(); 
            var DataSource = order; 
            GridProperties obj = ConvertGridObject(GridModel); 
             
            IWorkbook workbook = application.Workbooks.Create(2); 
            //Setting multiExport as true and export the document and assigned to workbook 
            workbook = exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "default-theme", true); 
            //Inserted new row for adding title 
            workbook.ActiveSheet.InsertRow(1); 
            //Merging the sheet from Range A1 to D1 for adding title space 
            workbook.ActiveSheet.Range["A1:D1"].Merge(); 
            //Adding the title using Text property 
            workbook.ActiveSheet.Range["A1"].Text = "Methodologies for the Typical Unification of Access Points and Redundancy"; 
            //Adding the footer information using SetValue method of the ActiveSheet 
            workbook.ActiveSheet.SetValue(workbook.ActiveSheet.Rows.Length + 2, workbook.ActiveSheet.Columns.Length - 3, "This report is for the Deputy Senior VP in charge of things that don't really matter. If this is not you, please destroy and notify the intended recipient immediately"); 
            //Downloading the document 
            workbook.SaveAs("Export.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2010); 
        } 
 

Please refer the screenshot of exported file with grouped columns in grid using workbook. 

 



If you need any further assistance please get back to us. 

Regards, 
Sathyanarayanamoorthy 
 


GC Gian Carlo February 1, 2018 09:58 PM UTC

Hi, Sorry if i didnt explain mi point,
its taking a lot of time to export to excel 24,995 items, more than 30 min, even with "none" style, 
exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "none");

can you suggest me a way to export the result that i have on the grid more fast, even if i group a column or i use a filter that shows me less results?

this is what i change in the code you attach

private void BindDataSource()
        {
            int code = 10000;
            for (int i = 1; i < 5000; i++) < ------ from 1000 to 5000 
            {
                order.Add(new Orders(code + 1, "ALFKI", i + 0, 2.3 * i, new DateTime(1991, 05, 15), "Berlin", code + 1, "ALFKI", i + 0, 2.3 * i, new DateTime(1991, 05, 15), "Berlin", code + 1, "ALFKI", i + 0, 2.3 * i, new DateTime(1991, 05, 15), "Berlin"));
                order.Add(new Orders(code + 2, "ANATR", i + 2, 3.3 * i, new DateTime(1990, 04, 04), "Madrid", code + 2, "ANATR", i + 2, 3.3 * i, new DateTime(1990, 04, 04), "Madrid", code + 2, "ANATR", i + 2, 3.3 * i, new DateTime(1990, 04, 04), "Madrid"));
                order.Add(new Orders(code + 3, "ANTON", i + 1, 4.3 * i, new DateTime(1957, 11, 30), "Cholchester", code + 3, "ANTON", i + 1, 4.3 * i, new DateTime(1957, 11, 30), "Cholchester", code + 3, "ANTON", i + 1, 4.3 * i, new DateTime(1957, 11, 30), "Cholchester"));
                order.Add(new Orders(code + 4, "BLONP", i + 3, 5.3 * i, new DateTime(1930, 10, 22), "Marseille", code + 4, "BLONP", i + 3, 5.3 * i, new DateTime(1930, 10, 22), "Marseille", code + 4, "BLONP", i + 3, 5.3 * i, new DateTime(1930, 10, 22), "Marseille"));
                order.Add(new Orders(code + 5, "BOLID", i + 4, 6.3 * i, new DateTime(1953, 02, 18), "Tsawassen", code + 5, "BOLID", i + 4, 6.3 * i, new DateTime(1953, 02, 18), "Tsawassen", code + 5, "BOLID", i + 4, 6.3 * i, new DateTime(1953, 02, 18), "Tsawassen"));
                code += 5;
            }

        }


thi is my screen

SE Sathyanarayanamoorthy Eswararao Syncfusion Team February 2, 2018 01:23 PM UTC

Hi Gian, 

Query: its taking a lot of time to export to excel 24,995 items, more than 30 min, even with "none" style 
 
We have analyzed your query and to avoid the time delay while exporting we suggest you to use the export using workbook which we have achieved in the below code example. 
 
Please refer the below code example. 
  public void ExportToExcel(string GridModel) 
        { 
            ExcelExport exp = new ExcelExport(); 
            ExcelEngine excel = new ExcelEngine(); 
            IApplication application = excel.Excel; 
            GridProperties obj = ConvertGridObject(GridModel); 
            IWorkbook workbook = application.Workbooks.Create(2); 
 
            List<Orders> reports = BindDataSource(); 
 
 
            workbook.Worksheets[0].ImportData(reports, 1, 1, false); 
             
            workbook.SaveAs("Export.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2010); 
 
             
        } 
  
 
We have prepared a sample for your reference and video of the grid exporting taking less time which can be found in the below link. 



If you need any further assistance please get back to us. 

Regards, 
Sathyanarayanamoorthy 



GC Gian Carlo February 2, 2018 04:38 PM UTC

thanks for your help, but in the excel sheet is not grouped as you did in the video

regards

SE Sathyanarayanamoorthy Eswararao Syncfusion Team February 5, 2018 01:02 PM UTC

Hi Gian, 
When we use workbook.saveas method for exporting the grid model is not passed as a parameter so the grid grouping model cannot be exported. If you want to export the grid with the grouping model we suggest you to use the Export method in which the grid model is passed while exporting so that the current view of the grid is exported. 
In the export method each cell of the grid is accessed while exporting so there will be delay in exporting the grid with large number of records. 
Also you have mentioned that it takes 30min to export with 25000 records but it is taking only 3 min for exporting the grid with 25000 records on our side. 
Please refer the below code. 
 
  public void ExportToExcel(string GridModel) 
        { 
            ExcelExport exp = new ExcelExport(); 
            BindDataSource(); 
            var DataSource = order; 
            GridProperties obj = ConvertGridObject(GridModel); 
 
            exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron"); 
 
        } 
  private GridProperties ConvertGridObject(string gridProperty) 
        { 
            JavaScriptSerializer serializer = new JavaScriptSerializer(); 
            IEnumerable div = (IEnumerable)serializer.Deserialize(gridProperty, typeof(IEnumerable)); 
            GridProperties gridProp = new GridProperties(); 
            foreach (KeyValuePair<string, object> ds in div) 
            { 
                var property = gridProp.GetType().GetProperty(ds.Key, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase); 
                if (property != null) 
               { 
                    Type type = property.PropertyType; 
                    string serialize = serializer.Serialize(ds.Value); 
                    object value = serializer.Deserialize(serialize, type); 
                    property.SetValue(gridProp, value, null); 
                } 
            } 
            return gridProp; 
        } 
 
 
We have prepared a sample for your reference which can be downloaded from below location. 
Regards, 
Sathyanarayanamoorthy 



GC Gian Carlo February 9, 2018 05:09 AM UTC

Thaanks a lot, and also thx for your patience. Just a las question. It is possible to add a wating popup while exporting? 

SE Sathyanarayanamoorthy Eswararao Syncfusion Team February 12, 2018 12:02 PM UTC

Hi Gian, 

We have used form submit action for exporting Grid, so full post will occur and it reloads the page while exporting the Grid.  
 
Without making full post, we are unable to download the exported Excel file. So it is not possible to have a waiting popup for the export action of Grid when using the default “export” method of the Grid.  

But we have achieved your requirement using Ajax call and localSave option of exporting. The localSave option can be enabled using one of the overload of the export method. Please refer to the below code example.  


 
@using SyncfusionMvcApplication1.Controllers 
@{ 
    ViewBag.Title = "Home Page"; 
} 
 
 
 
@(Html.EJ().Grid<Object>("Grid") 
         
                 ---------------------- 
       .Columns(col => 
        { 
            ------------------ 
           col.Field("ShipCity").HeaderText("Ship City").Width(110).Priority(2).Add(); 
        }) 
        .ClientSideEvents(eve => eve.ToolbarClick("toolbarclick")) 
        ) 
 
 
<script type="text/javascript"> 
    function toolbarclick(args) { 
        var url = null; 
        var proxy = this; 
        if (args.itemName == "Excel Export") { 
            args.cancel = true; 
            url = "/Home/ExportToExcel"; 
        } 
        if (args.itemName == "PDF Export") { 
            args.cancel = true; 
            url = "/Home/ExportToPDF"; 
        } 
        if (args.itemName == "Word Export") { 
            args.cancel = true; 
            url = "/Home/ExportToWord"; 
        } 
        if (args.itemName.indexOf("Export") != -1) { 
            $("#Grid").ejWaitingPopup("show"); 
            var model = $.extend(true, {}, this.model); 
            if (this.ignoreOnExport) { 
                for (var i = 0; i < this.ignoreOnExport.length; i++) 
                    delete model[this.ignoreOnExport[i]]; 
            } 
            $.ajax({ 
                type: "POST", 
                url: url, 
                data: { GridModel: JSON.stringify(model) },//pass the grid model                      
                success: function (response) { 
                    proxy.element.ejWaitingPopup("hide"); 
                    alert("Grid Exported"); 
                }, 
                error: function (Result) { 
                    alert("Error"); 
                } 
            }); 
        } 
    } 
</script> 
 
[controller.cs] 
 
 public partial class HomeController : Controller  
    {   
              . . . . .  
                   . . .  .  
        public ActionResult ExportToExcel(string GridModel)  
        {  
            GridProperties gridPropert = (GridProperties)Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties),GridModel);  
            ExcelExport exp = new ExcelExport();  
              BindDataSource(); 
            var DataSource = order; 
            exp.Export(gridPropert, (IEnumerable)data, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-lime",true,Server.MapPath("/outPut"));  
            return Json(new List<string>(), JsonRequestBehavior.AllowGet);  
        }  
             . . . . .  
    }   
}  
  

We have prepared a sample which can be downloaded from below location. 


If you need any further assistance please get back to us. 

Regards, 
Sathyanarayanamoorthy 



Loader.
Up arrow icon