Articles in this section
Category / Section

How to merge the exported worksheet in an existing workbook?

2 mins read

In Certain cases, users may like to merge exported data to an existing excel worksheet. We can achieve this requirement using the Syncfusion.Xlsio namespace

Solution

Render the Grid Control.

@(Html.EJ().Grid<object>("FlatGrid")
        .Datasource((IEnumerable<object>)ViewBag.dataSource) 
        .AllowPaging()
        .ToolbarSettings(toolbar =>
        {
            toolbar.ShowToolbar(true)
            .ToolbarItems(
                items =>
                {                    
                    items.AddTool(ToolBarItems.ExcelExport);
                    items.AddTool(ToolBarItems.WordExport);
                    items.AddTool(ToolBarItems.PdfExport);
                });
        }) 
        .Mappers(map => map.ExportToExcelAction("/Home/ExportToExcel").ExportToPdfAction("/Home/ExportToPdf").ExportToWordAction("/Home/ExportToWord"))
        .Columns(col =>
        {
            col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).Width(90).Add();
            col.Field("CustomerID").HeaderText("Customer ID").Width(90).Add();
            col.Field("EmployeeID").HeaderText("Employee Name").ForeignKeyField("EmployeeID").ForeignKeyValue("FirstName").DataSource((IEnumerable<object>)ViewBag.data).Width(75).Add();
            col.Field("Freight").HeaderText("Freight").Width(75).Format("{0:C}").Add();
            col.Field("ShipCity").HeaderText("Ship City").Width(80).Add();
        })
)

 

Code Behind

public class HomeController : Controller
    {
        
        public ActionResult Index()
        {
            ViewBag.dataSource = OrderRepository.GetAllRecords().ToList();  
            ViewBag.data = EmployeeRepository.GetAllRecords().ToList();
            return View();
        }        
 
     }   
 

 

In the Controller Export Action, load the existing workbook using the Open method of the IWorkBooks. We can find the last row of the existing worksheet and copy the exported sheet data to the existing worksheet.

public void ExportToExcel(string GridModel)
        {
            GridProperties gridProperty = (GridProperties)Utils.DeserializeToModel(typeof(GridProperties), GridModel);            
            ExcelExport exp = new ExcelExport();
            IEnumerable data = EmployeeRepository.GetEmployees().ToList();
            IWorkbook book = exp.Export(gridProperty, (IEnumerable)data, "Export.xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron", true);
 
            IWorksheet sheet = book.Worksheets[0];
 
            ExcelEngine excelEngine = new ExcelEngine();
 
            //Loads or open an existing workbook through Open method of IWorkbooks
 
            IWorkbook workbook = excelEngine.Excel.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + "/TemplateBook.xlsx");
            IWorksheet worksheet = workbook.Worksheets[0];
 
            IRange destination = worksheet.Range["A"+(worksheet.UsedRange.LastRow+1)];//find the last row of the template workbook
            
            
            //add the exported sheet as new sheet in template workbook and copy palettes
            IWorksheet newSheet = workbook.Worksheets.AddCopy(sheet, ExcelWorksheetCopyFlags.CopyPalette | ExcelWorksheetCopyFlags.CopyAll);
 
            IRange source = newSheet.UsedRange;
 
            //copy the exported sheet data to the template worksheet
            source.CopyTo(destination, ExcelCopyRangeOptions.All);
            newSheet.Remove();//remove the added new sheet
 
            IWorksheet worksheet1 = workbook.Worksheets[0];
            IRange row = worksheet1.Range["A9"].EntireRow;
            row.CellStyle.Font.Color = ExcelKnownColors.Blue;
 
            IRange col = worksheet1.Range["B9"].EntireColumn;
            col.CellStyle.Font.Color = ExcelKnownColors.Brown;
 
            //to save as a new workbook
            workbook.SaveAs("Test.xlsx", ExcelSaveType.SaveAsXLS, System.Web.HttpContext.Current.Response, ExcelDownloadType.Open);
 
            //to save in already existing template workbook
            //workbook.Save();
            workbook.Close();
 
            excelEngine.Dispose();
            
        }

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied