How to generate an Excel file from a form’s gridview


#1

You can easily integrate document generation into a workflow. This article provides an example of how to use the EPPlus library to generate an Excel file from a form’s gridview, such as the Investment Application process (template) form grid.

The generated file will be available as one of the first action’s OUT parameters.

  1. Download EPPlus 4.0.5 from http://epplus.codeplex.com/releases/view/118053.

    Note: The EPPLUS license is a GNU Library General Public License (LGPL) (see http://epplus.codeplex.com/license for details).

  2. Copy the EPPlus DLL to the DRIVE:\Inetpub\YOURSITE\wfgen\WfApps\WebForms\Bin folder.

  3. Create a new WorkflowGen process with a grid, such as the Investment Application Process (template).

  4. Create a FORM_EXCEL process data and set its data type to file. This process data will contain the generated Excel file.

  5. Create a new textbox field in the same section as the grid with the ID EXCEL. Hide this textbox for all actions (edit the textbox settings and check Hide checkbox for all actions).

  6. Using the Mapping tool in the form editor, select the first action, and click the Value OUT button associated to this new textbox. In the existing data field, select the FORM_EXCEL data.

  7. In the code-behind, create your new private Gridview2Excel method. With this method, you must save the generated file name in the “FORM_EXCEL” process data. This is how the generated file will be accessible from the follow-up form (for more information on this topic see the Form designer: How to generate a PDF version of the form archive topic).

    // Generate excel file with Gridview datas
    private void Gridview2Excel()
    {
          // Create the excel file
          System.IO.FileInfo newFile = new System.IO.FileInfo(this.StoragePath + @"\investment_excel.xlsx"); 
      
          // Create a spreadsheet
          OfficeOpenXml.ExcelPackage output = new  OfficeOpenXml.ExcelPackage(newFile);
          OfficeOpenXml.ExcelWorksheet ws = output.Workbook.Worksheets.Add("Investment");
      
          // Add grid header
          for (int i = 0; i this.INVESTMENTS_GRIDVIEW.HeaderRow.Cells.Count - 1; i++) {
            ws.Cells[1, i + 1].Value = ((Label)this.INVESTMENTS_GRIDVIEW.HeaderRow.Cells[i].Controls[0]).Text;
            ws.Cells[1, i + 1].Style.Font.Bold = true;
            ws.Cells[1, i + 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
          }
      
          // Add grid values
          for (int i = 0; i this.INVESTMENTS_GRIDVIEW.Rows.Count - 1; i++) {
            for (int j = 0; j this.INVESTMENTS_GRIDVIEW.Rows[i].Cells.Count - 1; j++) {
                ws.Cells[i + 2, j + 1].Value = ((Label)this.INVESTMENTS_GRIDVIEW.Rows[i].Cells[j].Controls[0]).Text;
                ws.Cells[i + 2, j + 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Hair);
            }
          }
      
          // Add some cells style
          ws.Cells.AutoFitColumns();
    
          output.Save();
      
          // Save file name in Excel textbox field
          this.INVESTMENTS_EXCEL.Text = "investment_excel.xlsx";
    }
    
  8. In the code-behind MySubmitButton_Click method, call your Gridview2Excel method.


#2

Hello,

Is it possible to do the opposite? We would like to upload spreadsheet to WFG request that should be transferred to gridview / table in the same WFG request.
We also would like to have separate data element for each field in the gridview.
Does this sound possible and is there any documentation that may help us achieving this?

Thank you,
Marina


#3

Hi Marina,

Yes, this is possible but there is no documentation regarding this.

What can be done is the following:

  1. Create a C# library that retrieves the file (using GraphQL for example), read through the Excel sheet and returns a DataSource object.
  2. Copy the DLL file of this class into wfgen\webapps\webforms\bin
  3. You can use this class in the .NET code behind editor to send the Excel file, retrieve the DataSource object and bind it to the Gridview.

If you want assistance regarding the implementation of this solution, feel free to open a ticket in our Helpdesk site.

Regards,
Eddy.