How to export repeat grid layout data to Excel using pzRDExportWrapper in Pega 7.1.8?

Question

I am trying to export repeat grid data to excel. To do this, I have provided a button which runs "MyCustomActivity" activity via clicking. The button is placed above the grid in the same layout. It also worth pointing out that I am utulizing an article as a guide to configure. According to the guide my "MyCustomActivity" activity contains two steps:

1. Method: Property-Set, Method Parameters: Param.exportmode = "excel"
2. Method: Call pzRDExportWrapper. And I pass current parameters (There is only one from the 1st step).

But after I had got an issue I have changed the 2nd step by Call Rule-Obj-Report-Definition.pzRDExportWrapper

But as you have already understood the solution doesn't work. I have checked the log files and found interesting error:

2017-04-11 21:08:27,992 [ WebContainer : 4] [OpenPortal] [ ] [ MyFW:01.01.02] (ctionWrapper._baseclass.Action) ERROR as1|172.22.254.110 bar - Activity 'MyCustomActivity' failed to execute; Failed to find a 'RULE-OBJ-ACTIVITY' with the name 'PZRESOLVECOPYFILTERS' that applies to 'COM-FW-MyFW-Work'. There were 3 rules with this name in the rulebase, but none matched this request. The 3 rules named 'PZRESOLVECOPYFILTERS' defined in the rulebase are: 2017-04-11 21:08:42,807 [ WebContainer : 4] [TABTHREAD1] [ ] [ MyFW:01.01.02] (fileSetup.Code_Security.Action) ERROR as1|172.22.254.110 bar - External authentication failed:

If someone have any suggestions and share some, I will appreciate it. Thank you.

Answer 1

I wanted to provide a functionality of exporting retrieved works to a CSV file. The functionality should has a feature to choose fields to retrieve, all results should be in Ukrainian and be able to use any SearchFilter Pages and Report Definition rules.

At a User Portal I have two sections: the first section contains text fields and a Search button, and a section with a Repeat Grid to display results. The textfields are used to filter results and they use a page Org-Div-Work-SearchFilter.

I made a custom parser to csv. I created two activities and wrote some Java code. I should mention that I took some code from the pzPDExportWrapper.

The activities are:

• ExportToCSV - takes parameters from users, gets data, invokes the ConvertResultsToCSV;
• ConvertResultsToCSV - converts retrieved data to a .CSV file.

Configurations of the ExportToCSV activity:
The Pages And Classes tab:

• ReportDefinition is an object of a certain Report Definition.
• SearchFilter is a Page with values inputted by user.
• ReportDefinitionResults is a list of retrieved works to export.

• ReportDefinitionResults.pxResults denotes a type of a certain work.

  The Parameters tab:
  

• FileName is a name of a generated file

• ColumnsNames names of columns separated by comma. If the parameter is empty then CSVProperties is exported.
• CSVProperties is a props to display in a spreadsheet separated by comma.
• SearchPageName is a name of a page to filter results.
• ReportDefinitionName is a RD's name used to retrieve results.
• ReportDefinitionClass is a class of utilized report definition.

  The Step tab:
  

Lets look through the steps:
1. Get an SearchFilte Page with a name from a Parameter with populated fields:
2. If SearchFilter is not Empty, call a Data Transform to convert SearchFilter's properties to Paramemer properties:

A fragment of the data Transform:
3. Gets an object of a Report Definition
4. Set parameters for the Report Definition
5. Invoke the Report Definition and save results to ReportDefinitionResults:
6. Invoke the ConvertResultsToCSV activity:
7. Delete the result page:

The overview of the ConvertResultsToCSV activity.

The Parameters tab if the ConvertResultsToCSV activity:

• CSVProperties are the properties to retrieve and export.
• ColumnsNames are names of columns to display.
• PageListProperty a name of the property to be read in the primay page
• FileName the name of generated file. Can be empty.
• AppendTimeStampToFileName - if true, a time of the file generation.
• CSVString a string of generated CSV to be saved to a file.
• FileName a name of a file.

• listSeperator is always a semicolon to separate fields.

  Lets skim all the steps in the activity:
  

  1. Get a localization from user settings (commented):
     
     In theory it is able to support a localization in many languages.
  2. Set always "uk" (Ukrainian) localization.
     

  3. Get a separator according to localization. It is always a semicolon in Ukrainian, English and Russian. It is required to check in other languages.
     

  4. The step contains Java code, which form a CSV string:

StringBuffer csvContent = new StringBuffer(); // a content of buffer
String pageListProp = tools.getParamValue("PageListProperty");
ClipboardProperty resultsProp = myStepPage.getProperty(pageListProp);

// fill the properties names list
java.util.List<String> propertiesNames = new java.util.LinkedList<String>(); // names of properties which values display in csv
String csvProps = tools.getParamValue("CSVProperties");
propertiesNames = java.util.Arrays.asList(csvProps.split(","));

// get user's colums names
java.util.List<String> columnsNames = new java.util.LinkedList<String>();
String CSVDisplayProps = tools.getParamValue("ColumnsNames");
if (!CSVDisplayProps.isEmpty()) {
  columnsNames = java.util.Arrays.asList(CSVDisplayProps.split(","));
} else {
  columnsNames.addAll(propertiesNames);
}

// add columns to csv file
Iterator columnsIter = columnsNames.iterator();
while (columnsIter.hasNext()) {
  csvContent.append(columnsIter.next().toString());
  if (columnsIter.hasNext()){
    csvContent.append(listSeperator); // listSeperator - local variable
  }
}
csvContent.append("\r");

for (int i = 1; i <= resultsProp.size(); i++) { 
  ClipboardPage propPage = resultsProp.getPageValue(i); 
  Iterator iterator = propertiesNames.iterator(); 
  int propTypeIndex = 0;
  while (iterator.hasNext()) {
    ClipboardProperty clipProp = propPage.getIfPresent((iterator.next()).toString());

    String propValue = "";
    if(clipProp != null && !clipProp.isEmpty()) {
      char propType = clipProp.getType();
      propValue = clipProp.getStringValue();

      if (propType == ImmutablePropertyInfo.TYPE_DATE) {
        DateTimeUtils dtu = ThreadContainer.get().getDateTimeUtils();
        long mills = dtu.parseDateString(propValue);
        java.util.Date date = new Date(mills);
        String sdate = dtu.formatDateTimeStamp(date);
        propValue = dtu.formatDateTime(sdate, "dd.MM.yyyy", "", "");
      } 
      else if (propType == ImmutablePropertyInfo.TYPE_DATETIME) {
        DateTimeUtils dtu = ThreadContainer.get().getDateTimeUtils();
        propValue = dtu.formatDateTime(propValue, "dd.MM.yyyy HH:mm", "", "");
      } 
      else if ((propType == ImmutablePropertyInfo.TYPE_DECIMAL)) {
        propValue = PRNumberFormat.format(localeCode,PRNumberFormat.DEFAULT_DECIMAL, false, null,  new BigDecimal(propValue));
      } 
      else if (propType == ImmutablePropertyInfo.TYPE_DOUBLE) {
        propValue = PRNumberFormat.format(localeCode,PRNumberFormat.DEFAULT_DECIMAL, false, null,  Double.parseDouble(propValue));
      } 
      else if (propType == ImmutablePropertyInfo.TYPE_TEXT) {
        propValue = clipProp.getLocalizedText();
      } 
      else if (propType == ImmutablePropertyInfo.TYPE_INTEGER) {
        Integer intPropValue = Integer.parseInt(propValue);
        if (intPropValue < 0) {
          propValue = new String();
        }

      }
    }
    if(propValue.contains(listSeperator)){
      csvContent.append("\""+propValue+"\"");
    } else {
      csvContent.append(propValue);
    }
    if(iterator.hasNext()){
      csvContent.append(listSeperator);
    }
    propTypeIndex++;
  }
  csvContent.append("\r");
}

CSVString = csvContent.toString();

5. This step forms and save a file in server's catalog tree

char sep = PRFile.separatorChar;
String exportPath= tools.getProperty("pxProcess.pxServiceExportPath").getStringValue();
DateTimeUtils dtu = ThreadContainer.get().getDateTimeUtils();

String fileNameParam = tools.getParamValue("FileName");
if(fileNameParam.equals("")){
    fileNameParam = "RecordsToCSV";
}

//append a time stamp
Boolean appendTimeStamp = tools.getParamAsBoolean(ImmutablePropertyInfo.TYPE_TRUEFALSE,"AppendTimeStampToFileName");
FileName += fileNameParam;
if(appendTimeStamp) {
  FileName += "_";
  String currentDateTime = dtu.getCurrentTimeStamp();
  currentDateTime = dtu.formatDateTime(currentDateTime, "HH-mm-ss_dd.MM.yyyy", "", "");
  FileName += currentDateTime;
}

//append a file format
FileName += ".csv";

String strSQLfullPath = exportPath + sep + FileName;
PRFile f = new PRFile(strSQLfullPath);

PROutputStream stream = null;
PRWriter out = null;
try {
 // Create file 
 stream = new PROutputStream(f);
 out = new PRWriter(stream, "UTF-8");

 // Bug with Excel reading a file starting with 'ID' as SYLK file. If CSV starts with ID, prepend an empty space.
 if(CSVString.startsWith("ID")){
    CSVString=" "+CSVString;
 }
 out.write(CSVString);
} catch (Exception e) {
 oLog.error("Error writing csv file: " + e.getMessage());
} finally {
  try {
    // Close the output stream
    out.close();
  } catch (Exception e) {
    oLog.error("Error of closing a file stream: " + e.getMessage());
  }
}

6. The last step calls @baseclass.DownloadFile to download the file:
   

Finally, we can post a button on some section or somewhere else and set up an Actions tab like this:

It also works fine inside "Refresh Section" action.

A possible result could be

Thanks for reading.