This topic describes the steps involved in creating an excel report using DsExcel template. Learn more in DsExcel docs.This walkthrough considers the use case to create a Marketing Report of a company which is launching a new series of smartphones. Hence, an Excel report for the planned marketing activities needs to be created. The report details out the planned events for the launch, its budget and expenses. The datasource used for binding the data, in this case, is Custom Object. The template layout is created in different Excel tabs to generate multiple reports. 

The below steps describe how to create an Excel report using template:

1. Create template layouts in different Excel worksheets of a workbook. Define the template layout of Marketing report using different types of fields:
   • Static Fields: Define the static fields in template layout, that is, the fields whose values will remain constant in the final report. For example, the header fields or template header like Marketing Report, SmartPhone, Event etc.
   • Bound Fields: Specify the datasource bound fields in mustache braces {{ }}. For custom object datasource, define the bound fields as {{ds.Records.FieldName}} where ds is the alias of the datasource, specified in code using addDataSource method.
   • Expression Fields: Specify the functions in fields whose value will be calculated using formulas.
   • Sheet Name: Create multiple template layouts in different Excel tabs, namely, Marketing Report, Smartphone expenses and Launch events. Specify a data bound FieldName for last sheet, {{ds.Records.Country}}, which will generate multiple reports based on the values of 'Country' field in the data source. 

   Template Layout: Marketing Report

   The below layout uses the Group property (G=Merge), which will group the smartphones against the corresponding records by displaying it once per group. The merge value merges the cells of each group.          

   

   Template Layout: SmartPhone Expenses

   The below layout uses two template properties, Cell expansion (E=H) and Cell context (C=A3)

   • The cell expansion property will expand the smartphone field horizontally.
   • The cell context property will make sure that the expense field expands horizontally depending upon the smartphone field. 

   

   Template Layout: Launch Events

   The below layout uses four template properties, Range (R=A3:B5), Sort (S=None), Cell expansion (E=H) and Page break (PageBreak=True)

   • The Range property which acts as the fallback context for the fields in specified range, which means, that the fields which have no default or explicit context will use this current field as their context.
   • The Sort property will not sort the events based on its 'none' value
   • The event field will expand horizontally based on the cell expansion property
   • The Page Break property will add a vertical and a horizontal page break

   

   Template Layout: {{ds.Records.Country}}

   

   
   
2. Load the template in DsExcel.

   Java	Copy Code
   System.out.println("Generating Marketing Report using Templates"); // Initialize workbook Workbook workbook = new Workbook(); // Load BudgetPlan_CustomObject.xlsx Template in workbook String templateFile = "BudgetPlan_CustomObject.xlsx"; workbook.open(templateFile);

   
   
3. Configure DataSource and add Records.
   

4. Java

   Copy Code

   // We can have mutiple types of DataSource like Custom Object/ DataSet/ // DataTable/ Json/ Variable. // Here dataSource is a Custom Object BudgetVals dataSource = new BudgetVals(); { dataSource.Records = new ArrayList<BudgetRecord>(); } BudgetRecord record1 = new BudgetRecord(); record1.SmartPhone = "Apple iPhone 11"; record1.Event = "Phone Launch"; record1.Budget = 1000; record1.Expense = 950; record1.City = "Seattle"; record1.Country = "USA"; dataSource.Records.add(record1); BudgetRecord record2 = new BudgetRecord(); record2.SmartPhone = "Apple iPhone 11"; record2.Event = "CEO Meet"; record2.Budget = 2000; record2.Expense = 1850; record2.City = "New York"; record2.Country = "USA"; dataSource.Records.add(record2); BudgetRecord record3 = new BudgetRecord(); record3.SmartPhone = "Samsung Galaxy S10"; record3.Event = "CEO Meet"; record3.Budget = 1600; record3.Expense = 1550; record3.City = "Paris"; record3.Country = "France"; dataSource.Records.add(record3); BudgetRecord record4 = new BudgetRecord(); record4.SmartPhone = "Apple iPhone XR"; record4.Event = "Phone Launch"; record4.Budget = 1800; record4.Expense = 1650; record4.City = "Cape Town"; record4.Country = "South Africa"; dataSource.Records.add(record4); BudgetRecord record5 = new BudgetRecord(); record5.SmartPhone = "Samsung Galaxy S9"; record5.Event = "Phone Launch"; record5.Budget = 1500; record5.Expense = 1350; record5.City = "Paris"; record5.Country = "France"; dataSource.Records.add(record5); BudgetRecord record6 = new BudgetRecord(); record6.SmartPhone = "Apple iPhone XR"; record6.Event = "CEO Meet"; record6.Budget = 1600; record6.Expense = 1550; record6.City = "New Jersey"; record6.Country = "USA"; dataSource.Records.add(record6); BudgetRecord record7 = new BudgetRecord(); record7.SmartPhone = "Samsung Galaxy S9"; record7.Event = "CEO Meet"; record7.Budget = 1200; record7.Expense = 1150; record7.City = "Seattle"; record7.Country = "USA"; dataSource.Records.add(record7); BudgetRecord record8 = new BudgetRecord(); record8.SmartPhone = "Samsung Galaxy S10"; record8.Event = "Phone Launch"; record8.Budget = 1100; record8.Expense = 1070; record8.City = "Durban"; record8.Country = "South Africa"; dataSource.Records.add(record8);

   
   
5. Add DataSource in DsExcel, using the addDataSource method.
   

   Java	Copy Code
   // Add DataSource // Here "ds" is the alias name of dataSource which is used in templates to // define fields like {{ds.Records.SmartPhone}} workbook.addDataSource("ds", dataSource);

   
   
6. Execute the template using ProcessTemplate method.

   Java	Copy Code
   // Invoke to process the template workbook.processTemplate();

   
   
7. Save the final report.

   Java	Copy Code
   // Save to an excel file System.out.println( "BudgetPlan_DataTable.xlsx Template is now bound to Custom Object and generated MarketingReport_CustomObject.xlsx file"); workbook.save("MarketingReport_CustomObject.xlsx");

   The output of the Marketing Report is shown as below:

   Excel Report: Marketing Report

    

   Excel Report: Smartphone Expenses

   

   Excel Report: Launch Events

   

   Excel Report: Countries (Multiple reports are created)

   

In the above process, the template is overwritten while generating the Excel report. To create a report while keeping the template intact, see the Create Report While Retaining Template section below.

Create Report While Retaining Template

DsExcel provides generateReport method in the IWorkbook interface that returns an instance of a new workbook report while retaining the template. The method also provides an overload so that you can generate report for a specific worksheet only.

To generate report using the generateReport method, see the example code below:

//Process the template and return the instance of report workbook
IWorkbook report = workbook.generateReport();
//Process the template and return the instance of report workbook
//IWorkbook report = workbook.generateReport(workbook.getWorksheets().get("Sales"));

To view the code in action, see Generate Report Demos.