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