We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Excel Export in Grid

Thread ID:

Created:

Updated:

Platform:

Replies:

128920 Feb 15,2017 02:01 PM Feb 16,2017 11:34 AM ASP.NET MVC 1
loading
Tags: Grid
Dayne
Asked On February 15, 2017 02:01 PM

Hi, 

I am using the Grid control export functionality to download the grid data file in the client, but I want to saved the generated excel book to specific path in the server. I'm also manipulating the cells of that excel, and of course I want to maintain the formats applied. I will put an example of my code, for yo to undestand.

 public void ExcelExportInvoices()
        {
            ExcelExport exp = new ExcelExport();
            Invoices model = new Invoices();
            string gridModel = System.Web.HttpContext.Current.Request.Params["GridModel"];
            GridProperties obj = ConvertGridObject(gridModel);
            
            var DataSource = model.getOpenInvoices().ToList();

            string nombre = "CxC_" + model.getCompanyNamebyId(empresa);
            
            string targetFolder = HttpContext.Server.MapPath("") + "\\Repositorio\\" + User.Identity.Name.ToString() + "\\" ;           

            AutoFormat auto = new AutoFormat();
            GridExtensions ext = new GridExtensions();
            ext.SetTheme(auto, "default-theme");
            auto.FontFamily = "Arial Narrow";
            auto.ContentFontSize = 8;
            auto.HeaderFontSize = 12;
            auto.GHeaderBgColor = Color.Crimson;

            obj.AutoFormat = auto;

            IWorkbook book = exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2013, false, false, "default-theme", true);       

            book.ActiveSheet.Range[2, 8, count, 8].HorizontalAlignment = ExcelHAlign.HAlignRight;
            book.ActiveSheet.Range[1, 8, count, 8].NumberFormat = "_($* #,##0.00_)";
            book.ActiveSheet.Range[1, 8, count, 8].AutofitColumns();
            book.ActiveSheet.Range[2, 10, count, 10].HorizontalAlignment = ExcelHAlign.HAlignRight;
            book.ActiveSheet.Range[1, 10, count, 10].NumberFormat = "_($* #,##0.00_)";
            book.ActiveSheet.Range[1, 10, count, 10].AutofitColumns();
            book.ActiveSheet.Range[2, 11, count, 11].HorizontalAlignment = ExcelHAlign.HAlignRight;
            book.ActiveSheet.Range[1, 11, count, 11].NumberFormat = "_($* #,##0.00_)";
            book.ActiveSheet.Range[1, 11, count, 11].AutofitColumns();

             //When I do this, the file is downloaded to my personal computer but not in the targetFolder 
            book.SaveAs(targetFolder + + nombre + ".xlsx", ExcelSaveType.SaveAsXLS, System.Web.HttpContext.Current.Response);

           
           // If I tried this other piece of code, the file is stored in the targetFolder, but the cells does not have the format.
           // string targetFolder = HttpContext.Server.MapPath("") + "\\Repositorio\\" + User.Identity.Name.ToString() + "\\";
           // exp.Export(obj, DataSource, fileName, ExcelVersion.Excel2010, false, false, "flat-saffron", true, targetFolder);

           
        }


So the question is, how can I achieve both behaviors?

Prasanna Kumar Viswanathan [Syncfusion]
Replied On February 16, 2017 11:34 AM

Hi Dayne, 

Thanks for contacting Syncfusion support. 

To save the generated excel book with the formats to the specific path in the server, use ServerExcelRowInfo event of Grid.  In this event we can get the rowIndex and columnIndex in the arguments. In this we recommend you to check the condition with column index or row index and apply the format to the cell. 
 
Find the code example, screenshot and sample: 
 
 
public void ExportToExcel(string GridModel) 
        { 
            ------------------------------- 
           auto.FontFamily = "Arial Narrow"; 
            auto.ContentFontSize = 8; 
            auto.HeaderFontSize = 12; 
            auto.GHeaderBgColor = Color.Crimson; 
            obj.AutoFormat = auto; 
            obj.ServerExcelRowInfo = querycell; 
            var destination = Server.MapPath("~/App_Data"); 
            exp.Export(obj, ds, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron", true, destination); 
        } 
 
------------------------------------------------- 
 
public void querycell(object sender) 
        { 
            var data = (IRange)sender; 
             
            if (data.Column == 4 && (data.Row >= 2 && data.Row < 6) ) 
            { 
                data.HorizontalAlignment = ExcelHAlign.HAlignLeft; 
                data.NumberFormat = "#,##0.00"; 
            } 
        } 
 
We have setisLocalSave” property as true and also set the path for the property filePath of the Export method for saving the exported file in the specified file path. 

Screenshot:  

 


Refer to the Help document of exporting server events. 


Query : “book.SaveAs(targetFolder + + nombre + ".xlsx", ExcelSaveType.SaveAsXLS, System.Web.HttpContext.Current.Response);” 

If you pass the response in SavaAs method, then the file should be downloaded instead of saved in local machine. We request you to use following code snippet to achieve your requirement.  
  
Code Snippet:  
 
book.SaveAs(targetFolder + + nombre + ".xlsx");  

Regards, 
Prasanna Kumar N.S.V 
 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;