Excel Export in Grid

[Dayne]

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]

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 set “isLocalSave” 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:  

 

Sample: https://www.syncfusion.com/downloads/support/forum/128920/ze/Sample142103_(2)-909551612 

Refer to the Help document of exporting server events. 

https://help.syncfusion.com/aspnetmvc/grid/exporting#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