Documents for Excel, Java Edition Documentation
Templates / Create Excel Report using Template
In This Topic
    Create Excel Report using Template
    In This Topic

    This topic describes the steps involved in creating an excel report using GcExcel template. Learn more in GcExcel 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.          

      Grouping in layout

      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. 

      Cell expansion and context properties

      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

      Range, sort, and page break properties

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

      Template layout


    2. Load the template in GcExcel.
      Java
      Copy Code
      System.out.println("Generating Marketing Report using GcExcel 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 GcExcel, 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

       Marketing report

      Excel Report: Smartphone Expenses

      Smartphone Expenses report

      Excel Report: Launch Events

      Launch Events report

      Excel Report: Countries (Multiple reports are created)

      Countries report