Tutorial 28: How to Export Data to Excel from Formspider Datasources and Grids

This tutorial explains how to export data to Excel in Formspider. You will discover how to generate Excel output using Formspider datasources and grids.

Generating Excel output of a particular data is a must-have business requirement for many contemporary applications aiming to provide a high quality and complete reporting feature. For this reason, Formspider has generic API’s providing data export to Excel through datasources and grids. Formspider supports generation of Excel files with both of .xls and .xlsx extensions.

In this tutorial you will create a basic application with a grid containing data provided by a datasource. You will discover how to export the data contained by this datasource to Excel in two different ways:

  • Through the grid component where the generated Excel output will contain the same columns, data formats and column header labels presented in grid component.
  • Through the datasource where you will be able to select the datasource columns to be exported and define data formats and column header labels. This usage is suitable when you are aiming to accomplish a more flexible and dynamic export process.

You may click here to run the completed application.

Open Formspider IDE and click “New Application” under the “File” menu. The “New Application” dialog will show up. Enter “excelOutput” as the name of the application and “HR” as its Datasource Schema name.  If you are using Formspider Online, you do not need to enter database schema name since your account is already configured to use your online database. Leave other fields empty and click “OK”. This creates the “excelOutput” application with a default mainframe (called “mainFrame”) and a default panel (called “mainPanel”).

Creating Model

As the first step, you will create the necessary Datasource Definition for your grid.
Expand the Datasource Definitions accordion, select the Datasource Definitions node and click the “+” button. Alternatively, you may right click the “Datasource Definitions” node and select “New” from the pop up menu. The “New Datasource Definition” Dialog will show up. Ensure that the “Table” radio button is selected and pick “EMPLOYEES” table from the combo box. Note that the combo box shows a list of all tables in your Datasource Schema “HR”. Also ensure that the “Create Datasource” checkbox is ticked. Please note that the Formspider IDE automatically assigned the name “EMPLOYEES” to your datasource definition and picked the “EMPLOYEE_ID” as its Primary Key.

Creating “EMPLOYEES” datasource definition

Click “OK” to save and close the “New Datasource Definition” dialog. This will create the “EMPLOYEES” datasource definition and the “EMPLOYEES1″ datasource. You will use the “EMPLOYEES1” datasource as the datasource of your grid.

Creating User Interface

As the next step you will create the user interface. Double click the “mainPanel” under the “Panels” node of the “Containers” navigation tree to open it in the editor. Your panel will contain a grid named “grid_employees” displaying “First Name”, “Last Name”, “Hire Date”, “Email”, “Phone Number” and “Salary” information of the employees using “EMPLOYEES1” datasource that you have created recently. Also you will add two buttons labeled “Excel Output From Grid” and “Excel Output From Datasource”.

The “Excel Output From Grid” button will serve to export data contained by “EMPLOYEES1” datasource through the “grid_employees” grid, therefore as explained at the beginning of the tutorial, the generated Excel file will contain exactly the same columns and column header labels with this grid. On the other hand, the “Excel Output From Datasource” button will serve to export data through the “EMPLOYEES1” datasource, where you will be able to specify which datasource columns to be exported as well as their column header labels and data formats. Your panel XML will look like;

<panel>
  <tableLayout cellSpacing="5">
    <row height="20">
      <cell childWidth="170">
        <button label="Excel Output From Grid"/>
      </cell>
      <cell childWidth="170">
        <button label="Excel Output From Datasource"/>
      </cell>
    </row>
    <row>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <grid name="grid_employees" dataSource="EMPLOYEES1">
          <column headerLabel="First Name">
            <textField column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textField column="LAST_NAME"/>
          </column>
          <column headerLabel="Hire Date">
            <dateField dateFormat="DD/MM/YYYY" column="HIRE_DATE"/>
          </column>
          <column headerLabel="Email">
            <textField column="EMAIL"/>
          </column>
          <column headerLabel="Phone Number">
            <textField column="PHONE_NUMBER"/>
          </column>
          <column headerLabel="Salary">
            <textField numberMask="#,###" column="SALARY"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Notice that “DD/MM/YYYY” date format is used for displaying “Hire Date” value and “#,###” numbermask is used for displaying “Salary” value.

When “Excel Output From Grid” button is pressed you will export the data contained in “grid_employees” grid as presented on this grid. To accomplish this you have to create a Formspider action which will be fired when “Excel Output From Grid” button is pressed.

In your datasource schema, create a package called  “excelOutput_pkg” and open your newly created “excelOutput_pkg” package in your favorite PL/SQL Editor. Add a procedure named “printToExcelFromGrid” and ensure that the procedure is exposed in the package specification. This procedure uses api_component.printToExcel API to export the data contained in “grid_employees” grid.

procedure printToExcelFromGrid is
begin
  api_component.printToExcel(in_panelDotComponentName_tx => 'mainPanel.grid_employees'
                            ,in_usepaging_yn             => 'N'
                            ,in_filename_tx              => 'Excel_Output_From_Grid'
                            ,in_fileformat_cd            => api_component.FILE_FORMAT_XLS);
end;

Parameter definitions of this API are as the following;

Parameter Definition
in_panelDotComponentName_tx The fully qualified name of the component in the [panelName].[componentName] format.
in_usePaging_yn Indicates whether current paging settings of the grid dataource should be used while exporting excel file data. Default value is Y, if the value is N, all paging related settings will be ignored.
in_fileName_tx Name of the generated excel file. If not specified “file_YYYYMMDD_HHMI” will be used as default name value.
in_fileFormat_cd File extension of the generated excel file. Default value is FILE_FORMAT_XLS. Valid constants for this input variable are: FILE_FORMAT_XLS, FILE_FORMAT_XLSX.

Therefore the “printToExcelFromGrid” procedure will generate an Excel file named “Excel_Output_From_Grid” with .xls extension through the grid component named “grid_employees” existing on “mainPanel” by ignoring any paging settings defined for the datasource of this grid.

In Formspider IDE expand the “Actions” accordion, select the “Actions” node, click the “+” button to create a new action. Alternatively you may right click the “Actions” node and select “New” from the pop-up menu. The “New Action” dialog will show up. Enter “printToExcelFromGrid” as the action name and “excelOutput_pkg.printToExcelFromGrid” as the procedure. Then, tick “Allow Popups” check box for avoiding any popup window which can be opened during download of exported Excel file. Click “OK” to save your action.

Creating “printToExcelFromGrid” action

Open the “mainPanel” in the editor. Add a buttonPress event to the “Excel Output From Grid” button triggering the “printToExcelFromGrid” action, the panel XML should look like;

<panel>
  <tableLayout cellSpacing="5">
    <row height="20">
      <cell childWidth="170">
        <button label="Excel Output From Grid">
          <events>
            <buttonPress action="printToExcelFromGrid"/>
          </events>
        </button>
      </cell>
      <cell childWidth="170">
        <button label="Excel Output From Datasource"/>
      </cell>
    </row>
    <row>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <grid name="grid_employees" dataSource="EMPLOYEES1">
          <column headerLabel="First Name">
            <textField column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textField column="LAST_NAME"/>
          </column>
          <column headerLabel="Hire Date">
            <dateField dateFormat="DD/MM/YYYY" column="HIRE_DATE"/>
          </column>
          <column headerLabel="Email">
            <textField column="EMAIL"/>
          </column>
          <column headerLabel="Phone Number">
            <textField column="PHONE_NUMBER"/>
          </column>
          <column headerLabel="Salary">
            <textField numberMask="#,###" column="SALARY"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Press “Run on Web” button for running your application. Click “Excel Output From Grid” button, an excel file named “Excel_Output_From_Grid” will be automatically downloaded. Notice that the generated Excel file contains exactly the same columns with the same header labels as defined in your grid. Also notice that the date format assigned to “Hire Date” and the numbermask assigned to “Salary” columns are successfully conserved.

Excel export will be taken through the grid component

Exported Excel file from the grid

As the final step you will create a procedure named “printToExcelFromDatasource” which will be used when “Excel Output From Datasource” button is pressed.

Open “excelOutput_pkg” package and create the “printToExcelFromDatasource” procedure, this procedure uses api_datasource.printToExcel API to export the data contained in “EMPLOYEES1” datasource.

procedure printToExcelFromDatasource is
  v_excelCols_t api_datasource.tt_excelColumns;
begin

  v_excelCols_t(1).datasourceColumnName_tx := 'HIRE_DATE';
  v_excelCols_t(1).label_tx := 'HIRE DATE';
  v_excelCols_t(1).format_tx :='DD-MM-YYYY';

  v_excelCols_t(2).datasourceColumnName_tx := 'PHONE_NUMBER';
  v_excelCols_t(2).label_tx := 'PHONE NUMBER';

  v_excelCols_t(3).datasourceColumnName_tx := 'FIRST_NAME';
  v_excelCols_t(3).label_tx := 'FIRST NAME';

  v_excelCols_t(4).datasourceColumnName_tx := 'LAST_NAME';
  v_excelCols_t(4).label_tx := 'LAST NAME';

  api_datasource.printToExcel(in_datasourceName_tx => 'EMPLOYEES1'
                             ,in_excelColumns_t =>  v_excelCols_t
                             ,in_usePaging_yn => 'N'
                             ,in_fileName_tx => 'Excel_Output_From_Datasource'
                             ,in_fileFormat_cd => api_datasource.FILE_FORMAT_XLSX);
end;

Parameter definitions of this API are as the following;

Parameter Definition
in_datasourceName_tx The name of datasource to be exported
in_excelColumns_t Array holding datasource column information to be exported.(datasource column name, label and data format to be used in excel file)
in_usePaging_yn Indicates whether current paging settings of the grid dataource should be used while exporting excel file data. Default value is Y, if the value is N, all paging related settings will be ignored.
in_fileName_tx Name of the generated excel file. If not specified “file_YYYYMMDD_HHMI” will be used as default name value.
in_fileFormat_cd File extension of the generated excel file. Default value is FILE_FORMAT_XLS. Valid constants for this input variable are: FILE_FORMAT_XLS, FILE_FORMAT_XLSX.

Notice that since you are selected “Hire Date”, “Phone Number”, “First Name” and “Last Name” columns only these specified columns will be exported to Excel with the given sequence. Also notice that this time all column headers are upper case and you are using “DD-MM-YYYY” as date format of “Hire Date” value.

In conclusion the “printToExcelFromDatasource” will generate an Excel file named “Excel_Output_From_Datasource” with .xlsx extension through the datasource “EMPLOYEES1” by ignoring any paging settings defined for the datasource of this grid.

Create a new action. Enter “printToExcelFromDatasource” as the action name and “excelOutput_pkg.printToExcelFromDatasource” as the procedure. Then, tick “Allow Popups” check box again for avoiding any popup window which can be opened during download of exported Excel file. Click “OK” to save your action.

Open the “mainPanel” in the editor. Add a buttonPress event to the “Excel Output From Datasource” button triggering the “printToExcelFromDatasource” action, the panel XML should look like;

<panel>
  <tableLayout cellSpacing="5">
    <row height="20">
      <cell childWidth="170">
        <button label="Excel Output From Grid">
          <events>
            <buttonPress action="printToExcelFromGrid"/>
          </events>
        </button>
      </cell>
      <cell childWidth="170">
        <button label="Excel Output From Datasource">
          <events>
            <buttonPress action="printToExcelFromDatasource"/>
          </events>
        </button>
      </cell>
    </row>
    <row>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <grid name="grid_employees" dataSource="EMPLOYEES1">
          <column headerLabel="First Name">
            <textField column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textField column="LAST_NAME"/>
          </column>
          <column headerLabel="Hire Date">
            <dateField dateFormat="DD/MM/YYYY" column="HIRE_DATE"/>
          </column>
          <column headerLabel="Email">
            <textField column="EMAIL"/>
          </column>
          <column headerLabel="Phone Number">
            <textField column="PHONE_NUMBER"/>
          </column>
          <column headerLabel="Salary">
            <textField numberMask="#,###" column="SALARY"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Run your application again and press “Excel Output From Datasource” button, an excel file named “Excel_Output_From_Datasource” will be automatically downloaded. Notice that the generated Excel file contains “Hire Date”, “Phone Number”, “First Name” and “Last Name” columns that you have specified. Also, notice that the date format of “Hire Date” column is “DD-MM-YYYY” and all column header labels are upper cased as expected.

You may click here to run the application.

Exported Excel file from the datasource

  • İbrahim Sandallı

    Hi Vicky,

    You can define a generic datasource containing multiple varchar2, number and date datatyped columns. You can copy your form data to this datasource and use it as an interface to transform form data to excel.

    The query of such a datasource (named formToExcel1 for example) may be like:

    select cast(null as varchar2(4000)) as tx_1
    ,cast(null as varchar2(4000)) as tx_2
    ,cast(null as number) as nr_1
    ,cast(null as number) as nr_2
    ,cast(null as date) as dt_1
    ,cast(null as date) as dt_2
    from dual

    Assume that you have a form containing two fields, first name and salary:

    In this case you can use the following procedure to export your form data to excel:

    procedure exportFormToExcel is
    v_excelCols_t api_datasource.tt_excelColumns;
    begin

    api_datasource.executequery(‘formToExcel1′);
    api_datasource.setcolumnvalue(‘formToExcel1.TX_1′, api_component.getvaluetx(‘mainPanel.tf1′));
    api_datasource.setcolumnvalue(‘formToExcel1.NR_1′, api_component.getvaluetx(‘mainPanel.tf2′));

    v_excelCols_t(1).datasourceColumnName_tx := ‘TX_1′;
    v_excelCols_t(1).label_tx := ‘FIRST NAME’;

    v_excelCols_t(2).datasourceColumnName_tx := ‘NR_1′;
    v_excelCols_t(2).label_tx := ‘SALARY’;

    api_datasource.printtoexcel(‘formToExcel1′, v_excelCols_t, ‘N’, ‘formData’);
    end;

    Hope this helps,

    Ibrahim

  • Vicky

    i am creating something like a submission form. At this point we dont need to store data in a table. is there an easy way to extract data in excel without using a table and just capture the data from the main panel?

    • İbrahim Sandallı

      Hi Vicky,

      You can define a generic datasource containing multiple varchar2, number and date datatyped columns. You can copy your form data to this datasource and use it as an interface to transform form data to excel.

      The query of such a datasource (named formToExcel1 for example) may be like:

      select cast(null as varchar2(4000)) as tx_1
      ,cast(null as varchar2(4000)) as tx_2
      ,cast(null as number) as nr_1
      ,cast(null as number) as nr_2
      ,cast(null as date) as dt_1
      ,cast(null as date) as dt_2
      from dual

      Assume that you have a form containing two fields, first name and salary:

      In this case you can use the following procedure to export your form data to excel:

      procedure exportFormToExcel is
      v_excelCols_t api_datasource.tt_excelColumns;
      begin

      api_datasource.executequery(‘formToExcel1′);

      api_datasource.setcolumnvalue(‘formToExcel1.TX_1′, api_component.getvaluetx(‘mainPanel.tf1′));

      api_datasource.setcolumnvalue(‘formToExcel1.NR_1′, api_component.getvaluetx(‘mainPanel.tf2′));

      v_excelCols_t(1).datasourceColumnName_tx := ‘TX_1′;
      v_excelCols_t(1).label_tx := ‘FIRST NAME’;

      v_excelCols_t(2).datasourceColumnName_tx := ‘NR_1′;
      v_excelCols_t(2).label_tx := ‘SALARY’;

      api_datasource.printtoexcel(‘formToExcel1′, v_excelCols_t, ‘N’, ‘formData’);
      end;

      Hope this helps,
      Ibrahim

  • Ilia

    This is a great feature, indeed! However, many applications require custom report headers and trailers, multiple report sections, embedded images (logos) and so on. For example, how would you go about creating an invoice form? It would be great to expose the lower level API functions to manipulate contents and formatting of a particular Excel cell, row, column – so we can put text, apply bold font, add more database fields, or even create a template file.