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(); }