{"id":4673,"date":"2018-08-25T03:23:32","date_gmt":"2018-08-25T03:23:32","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=4673"},"modified":"2024-07-26T08:41:03","modified_gmt":"2024-07-26T08:41:03","slug":"create-excel-report-in-ssis-using-template","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/create-excel-report-in-ssis-using-template\/","title":{"rendered":"Create Excel Report in SSIS using a template (Export Formatted xlsx File)"},"content":{"rendered":"

Introduction<\/h2>\n

In our previous post we saw how to create excel dynamically in SSIS<\/a>. Now let’s advance further and find out how to create Excel reports in SSIS using Powerful Template engine (introduced in PowerPack 2.7.4)\u00a0<\/strong>\u00a0In this post you will learn how to use ZappySys Export Excel Task<\/a> to export data from Multiple SQL Tables to Single or Multiple Excel Sheets, we will use Formatting, Expressions and Filtering to export parent child datasets (Master-Detail) using Template approach which supports full customization as per your need.<\/p>\n

We will be using following\u00a0ZappySys SSIS PowerPack<\/a>\u00a0component to make things work in this tutorial:
\n

\n\n\n\n
\"ssis-export-excel-file-task\"
\n<\/a><\/td>\n		SSIS Export Excel File Task (Dynamically generate)<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

You can check the following video to learn basic things about Export Excel Task. In future we will post video tutorial to show how Template engine works.<\/p>\n[youtube https:\/\/www.youtube.com\/watch?v=PXuOWo0xMoc&w=560&h=315]\n

Requirements<\/h2>\n
    \n
  1. First of all, MS Excel installed.<\/li>\n
  2. Secondly, an Excel template that you can download\u00a0here<\/a>.<\/li>\n
  3. In addition, you will need SQL Server installed.<\/li>\n
  4. Aso, the SQL Server Northwind sample database<\/strong> that you can download\u00a0here.<\/a><\/li>\n
  5. Additionally, SSDT for Business Intelligence.<\/li>\n
  6. Finally, use the SSIS Excel package example that you can download\u00a0here<\/a>.<\/li>\n<\/ol>\n

    Concept about Excel Template Engine<\/h2>\n

    ZappySys Export Excel Template engine is compatible with FlexCel engine described here<\/a>. So read it carefully to get most details about how template engine works and what elements supported in Template engine. Basically you have to use Template file which will be used to generate final excel report in SSIS. Advantage of Template engine approach compared to Simple export is you can control every aspect of your report. Also you can create nested reports use multiple datasets in a single sheet (Master-Detail JOINS) which is not possible in default export mode. The only down side of this approach is… learning curve. However once you understand concepts of Template Engine it wont take you too much time to master it.<\/p>\n

    In Template file you can define bands (e.g. Customer Info, Order Info…). Each band can grow in Vertical or Horizontal direction. Band must be created by Named Range<\/a> in Excel. You must name your range in a very specific way. Based on how you suffix and prefix your named range it will decide how to grow your band (i.e. Expand Vertical or Horizontal).<\/p>\n

    Few things to remember with Template Engine.<\/p>\n