Skip to main content

This content is closed to future replies and is no longer being maintained or updated.
Links may no longer function. If you have a similar request, please write a new post.

Question

 Suchandra Roy (SUCHANDRAROY)
Coforge
Suchandra Roy
Coforge
GB
SUCHANDRAROY Member since 2012 4 posts
Coforge
Posted: Feb 12, 2016
Last activity: Jun 20, 2017
Posted: 12 Feb 2016 2:26 EST
Last activity: 20 Jun 2017 2:59 EDT
Closed
Solved

How to change names of columns exported in excel from activity ?

When i am trying to export a page list, which is getting populated from the DB, into an excel, the excel generated contains the exact names of the columns in the DB (say pyMetaData, pyLabel etc.)

I am using the activity pxConvertResultsToCSV to convert the page list to excel. is there a way by which i can change the name of the columns(inside excel) to more user friendly names ?export to excel issue.png

To see attachments, please log in.
Low-Code App Development
Reporting

Accepted Solution

Posted: 8 years ago
Posted: 17 Feb 2016 20:05 EST
Srividhya Renganathan (Srividhya.R)
Common Wealth Bank of Australia
Senior Case Engineer
Common Wealth Bank of Australia
AU

Hi Suchandra,

I have tried customising the pxConvertResultsCSV and it is working good as expected.

  1. Save the pxConvertResultsCSV into application ruleset
  2. Add a new parameter called CSVDisplayProperties
  3. Paste the below Java code in the "Construct CSV String" Java Step
  4. Save the activity
  5. In the calling activity specify the CSVDisplayProperties with the custom header as specified on your requirement
  6. CSV file will be generated with the new header.

<<Java Snippet to be included  - Start>>

StringBuffer csvbuff = new StringBuffer();

//Construct list of properties to be displayed on CSV

java.util.List propList = new ArrayList();

java.util.List<String> propTypes = null;

java.util.List<String> displayProps = null;

java.util.List<String> fieldModes = null;

if(tools.getParamValue("CSVPropertyTypes")!= null && !tools.getParamValue("CSVPropertyTypes").equals("")) {

  propTypes = java.util.Arrays.asList(tools.getParamValue("CSVPropertyTypes").split(","));

}

if(tools.getParamValue("CSVFieldModes")!= null && !tools.getParamValue("CSVFieldModes").equals("")) {

  fieldModes = java.util.Arrays.asList(tools.getParamValue("CSVFieldModes").split(",",-1));

}

Iterator iter = null;

String CSVProps = tools.getParamValue("CSVProperties");

String pageListProp = tools.getParamValue("PageListProperty");

Show More

Hi Suchandra,

I have tried customising the pxConvertResultsCSV and it is working good as expected.

  1. Save the pxConvertResultsCSV into application ruleset
  2. Add a new parameter called CSVDisplayProperties
  3. Paste the below Java code in the "Construct CSV String" Java Step
  4. Save the activity
  5. In the calling activity specify the CSVDisplayProperties with the custom header as specified on your requirement
  6. CSV file will be generated with the new header.

<<Java Snippet to be included  - Start>>

StringBuffer csvbuff = new StringBuffer();

//Construct list of properties to be displayed on CSV

java.util.List propList = new ArrayList();

java.util.List<String> propTypes = null;

java.util.List<String> displayProps = null;

java.util.List<String> fieldModes = null;

if(tools.getParamValue("CSVPropertyTypes")!= null && !tools.getParamValue("CSVPropertyTypes").equals("")) {

  propTypes = java.util.Arrays.asList(tools.getParamValue("CSVPropertyTypes").split(","));

}

if(tools.getParamValue("CSVFieldModes")!= null && !tools.getParamValue("CSVFieldModes").equals("")) {

  fieldModes = java.util.Arrays.asList(tools.getParamValue("CSVFieldModes").split(",",-1));

}

Iterator iter = null;

String CSVProps = tools.getParamValue("CSVProperties");

String pageListProp = tools.getParamValue("PageListProperty");

ClipboardProperty resultsProp = myStepPage.getProperty(pageListProp);

if(!CSVProps.equals("")){

  propList = java.util.Arrays.asList(CSVProps.split(","));

}else{

  ClipboardPage firstResultPage = resultsProp.getPageValue(1);

  java.util.Set propSet = firstResultPage.keySet();

  String propName="";

   iter = propSet.iterator();

  while (iter.hasNext()) {

  propName = (String)iter.next();

  if(propName.equals("pxObjClass")){

  continue;

  }

  propList.add(propName);

  }

}

Iterator displayIter = null;

String CSVDisplayProps = tools.getParamValue("CSVDisplayProperties");

if(!CSVDisplayProps.equals("")){

  displayProps = java.util.Arrays.asList(CSVDisplayProps.split(","));

}

//loop through properties and add as column headers in the CSV file

displayIter = displayProps.iterator();

int disp = 0;

while(displayIter.hasNext()){

  csvbuff.append((String)displayIter.next());

  if(displayIter.hasNext()){

  csvbuff.append(listSeperator);

  }

  disp++;

}

csvbuff.append("\n");

ClipboardPage propPage =null;

String propValue = "";

String propType ="";

int j=0;

for(int i =1; i<=resultsProp.size();i++){

  iter = propList.iterator();

  propPage = resultsProp.getPageValue(i);

  while(iter.hasNext()){

  propValue = propPage.getString((String)iter.next());

       

          if(!propValue.equals("") && propTypes != null){

            propType = propTypes.get(j);

            if ((propType.equals("Decimal"))) {

            propValue = PRNumberFormat.format(localeCode,PRNumberFormat.DEFAULT_DECIMAL, false, null,  new BigDecimal(propValue));

          } else if (propType.equals("Double")) {

              propValue = PRNumberFormat.format(localeCode,PRNumberFormat.DEFAULT_DECIMAL, false, null,  Double.parseDouble(propValue));

            }else if (propType.equals("Text") && propValue.contains("\n")) {

                      propValue = propValue.replaceAll("\n"," ");

            }

          }

     

        if(propValue.contains(listSeperator)){

     csvbuff.append("\""+propValue+"\"");

  } else {

            csvbuff.append(propValue); 

        }

 

  if(iter.hasNext()){

  csvbuff.append(listSeperator);

  }

      j++;

  }

  j=0;

  csvbuff.append("\n");

}

CSVString = csvbuff.toString();

<<Java Snippet to be included  - End>>


Show Less
To see attachments, please log in.
Posted: 8 years ago
Posted: 12 Feb 2016 3:20 EST
Phani Sahukaru (sahup1)
Pegasystems Inc.
Phani Sahukaru
Pegasystems Inc.
IN

Hi Suchandra, Good morning!

could we try creating a MAP Value rule for 'pyPropertyName' returning user friendly name appended to parameter 'param.CSVProperties' in OOTB activity pxConvertResultsToCSV?

Please share your thoughts/observations, Thank you!

psahukaru

To see attachments, please log in.
Posted: 8 years ago
Posted: 16 Feb 2016 22:47 EST
Murali Kakarla (Muralik7)
Infosys

Infosys
AU

Is this activity updated in 71.9? In 7.1.7 version I see there is no way to replace column names with customized names. CSVProperties param has to have exact property names (as in pagelist) separated by comma.

Murali...

To see attachments, please log in.
Posted: 8 years ago
Posted: 17 Feb 2016 16:59 EST
Phani Sahukaru (sahup1)
Pegasystems Inc.
Phani Sahukaru
Pegasystems Inc.
IN

Hi Murali, Good morning!

before calling pxConvertResultsToCSV we will have to customize the parameter - param.CSVProperties.

Please share your thoughts/comments, Thank you!

psahukaru

To see attachments, please log in.
Posted: 8 years ago
Posted: 17 Feb 2016 22:23 EST
Murali Kakarla (Muralik7)
Infosys

Infosys
AU

Phani, as Suchandra mentioned, param is used to get property value from pagelist. So only option to make a copy of this activity and make changes as Srividhya mentioned!.

Murali...

To see attachments, please log in.
Posted: 8 years ago
Posted: 17 Feb 2016 22:03 EST
Suchandra Roy (SUCHANDRAROY)
Coforge
Suchandra Roy
Coforge
GB

Hi phani,

We cannot rename the columns to any other name before calling this activity. In that case, it fails to fetch data for that particular column.

To see attachments, please log in.
Posted: 8 years ago
Posted: 13 Mar 2016 15:45 EDT
Phani Sahukaru (sahup1)
Pegasystems Inc.
Phani Sahukaru
Pegasystems Inc.
IN

my bad, sorry Suchandra. would a linked property mechanism suites here? [again sorry for the comment if doesn't fit...]

To see attachments, please log in.
Posted: 8 years ago
Posted: 13 Mar 2016 20:03 EDT
Murali Kakarla (Muralik7)
Infosys

Infosys
AU

No Phani, I don't think Lined Property will suit this scenario as linked property is used to load data from DB using keys. To customize the column labels, one of the options suggested by Sri will be more appropriate for this.

Murali...

To see attachments, please log in.
Posted: 8 years ago
Posted: 16 Feb 2016 20:40 EST
Srividhya Renganathan (Srividhya.R)
Common Wealth Bank of Australia
Senior Case Engineer
Common Wealth Bank of Australia
AU

Hi Phani/Suchandra,

I have used the exact same activity to download the CSV file of a  pagelist.

When I run the activity individually, I could see the CSV getting downloaded.

But when I call the same activity as "Run actvity" on an Image Click in a section, I cannot view the CSV file downloaded and the file is not being downloaded at all.

However, I can see the activity executed successfully via tracer.

How and where are you calling "ExportToExcel" activity? Are you able to view the downloaded file?

Do we have to do any specific configuration on the section?

Please clarify.

Regards,

Sri

To see attachments, please log in.
Posted: 8 years ago
Posted: 16 Feb 2016 22:49 EST
Srividhya Renganathan (Srividhya.R)
Common Wealth Bank of Australia
Senior Case Engineer
Common Wealth Bank of Australia
AU

I am able to view the downloaded file, if I invoke the activity as part of the "Refresh This Section" action instead of the "Run Activity" Action.

Regards,

Sri

To see attachments, please log in.
Posted: 8 years ago
Posted: 16 Feb 2016 23:26 EST
Srividhya Renganathan (Srividhya.R)
Common Wealth Bank of Australia
Senior Case Engineer
Common Wealth Bank of Australia
AU

Hi Suchandra - Does the requirement say the file has to be in CSV format? or if it can be in the xlsx format, why can't we try customising the MSOGenerateExcelFile activity. This will accept a template and you can name the template header as per your requirement.

To see attachments, please log in.

Accepted Solution

Posted: 8 years ago
Posted: 17 Feb 2016 20:05 EST
Srividhya Renganathan (Srividhya.R)
Common Wealth Bank of Australia
Senior Case Engineer
Common Wealth Bank of Australia
AU

Hi Suchandra,

I have tried customising the pxConvertResultsCSV and it is working good as expected.

  1. Save the pxConvertResultsCSV into application ruleset
  2. Add a new parameter called CSVDisplayProperties
  3. Paste the below Java code in the "Construct CSV String" Java Step
  4. Save the activity
  5. In the calling activity specify the CSVDisplayProperties with the custom header as specified on your requirement
  6. CSV file will be generated with the new header.

<<Java Snippet to be included  - Start>>

StringBuffer csvbuff = new StringBuffer();

//Construct list of properties to be displayed on CSV

java.util.List propList = new ArrayList();

java.util.List<String> propTypes = null;

java.util.List<String> displayProps = null;

java.util.List<String> fieldModes = null;

if(tools.getParamValue("CSVPropertyTypes")!= null && !tools.getParamValue("CSVPropertyTypes").equals("")) {

  propTypes = java.util.Arrays.asList(tools.getParamValue("CSVPropertyTypes").split(","));

}

if(tools.getParamValue("CSVFieldModes")!= null && !tools.getParamValue("CSVFieldModes").equals("")) {

  fieldModes = java.util.Arrays.asList(tools.getParamValue("CSVFieldModes").split(",",-1));

}

Iterator iter = null;

String CSVProps = tools.getParamValue("CSVProperties");

String pageListProp = tools.getParamValue("PageListProperty");

Show More

Hi Suchandra,

I have tried customising the pxConvertResultsCSV and it is working good as expected.

  1. Save the pxConvertResultsCSV into application ruleset
  2. Add a new parameter called CSVDisplayProperties
  3. Paste the below Java code in the "Construct CSV String" Java Step
  4. Save the activity
  5. In the calling activity specify the CSVDisplayProperties with the custom header as specified on your requirement
  6. CSV file will be generated with the new header.

<<Java Snippet to be included  - Start>>

StringBuffer csvbuff = new StringBuffer();

//Construct list of properties to be displayed on CSV

java.util.List propList = new ArrayList();

java.util.List<String> propTypes = null;

java.util.List<String> displayProps = null;

java.util.List<String> fieldModes = null;

if(tools.getParamValue("CSVPropertyTypes")!= null && !tools.getParamValue("CSVPropertyTypes").equals("")) {

  propTypes = java.util.Arrays.asList(tools.getParamValue("CSVPropertyTypes").split(","));

}

if(tools.getParamValue("CSVFieldModes")!= null && !tools.getParamValue("CSVFieldModes").equals("")) {

  fieldModes = java.util.Arrays.asList(tools.getParamValue("CSVFieldModes").split(",",-1));

}

Iterator iter = null;

String CSVProps = tools.getParamValue("CSVProperties");

String pageListProp = tools.getParamValue("PageListProperty");

ClipboardProperty resultsProp = myStepPage.getProperty(pageListProp);

if(!CSVProps.equals("")){

  propList = java.util.Arrays.asList(CSVProps.split(","));

}else{

  ClipboardPage firstResultPage = resultsProp.getPageValue(1);

  java.util.Set propSet = firstResultPage.keySet();

  String propName="";

   iter = propSet.iterator();

  while (iter.hasNext()) {

  propName = (String)iter.next();

  if(propName.equals("pxObjClass")){

  continue;

  }

  propList.add(propName);

  }

}

Iterator displayIter = null;

String CSVDisplayProps = tools.getParamValue("CSVDisplayProperties");

if(!CSVDisplayProps.equals("")){

  displayProps = java.util.Arrays.asList(CSVDisplayProps.split(","));

}

//loop through properties and add as column headers in the CSV file

displayIter = displayProps.iterator();

int disp = 0;

while(displayIter.hasNext()){

  csvbuff.append((String)displayIter.next());

  if(displayIter.hasNext()){

  csvbuff.append(listSeperator);

  }

  disp++;

}

csvbuff.append("\n");

ClipboardPage propPage =null;

String propValue = "";

String propType ="";

int j=0;

for(int i =1; i<=resultsProp.size();i++){

  iter = propList.iterator();

  propPage = resultsProp.getPageValue(i);

  while(iter.hasNext()){

  propValue = propPage.getString((String)iter.next());

       

          if(!propValue.equals("") && propTypes != null){

            propType = propTypes.get(j);

            if ((propType.equals("Decimal"))) {

            propValue = PRNumberFormat.format(localeCode,PRNumberFormat.DEFAULT_DECIMAL, false, null,  new BigDecimal(propValue));

          } else if (propType.equals("Double")) {

              propValue = PRNumberFormat.format(localeCode,PRNumberFormat.DEFAULT_DECIMAL, false, null,  Double.parseDouble(propValue));

            }else if (propType.equals("Text") && propValue.contains("\n")) {

                      propValue = propValue.replaceAll("\n"," ");

            }

          }

     

        if(propValue.contains(listSeperator)){

     csvbuff.append("\""+propValue+"\"");

  } else {

            csvbuff.append(propValue); 

        }

 

  if(iter.hasNext()){

  csvbuff.append(listSeperator);

  }

      j++;

  }

  j=0;

  csvbuff.append("\n");

}

CSVString = csvbuff.toString();

<<Java Snippet to be included  - End>>


Show Less
To see attachments, please log in.
Posted: 7 years ago
Posted: 20 Jun 2017 2:59 EDT
Swapna Ravula (SR@PRPC7)
Virtusa
Senior Lead Software Engineer
Virtusa
US

Thanks 

Srividhya.R

  The above code is displaying the custom columns but when there is no value in the Pagelist as it wont come on the clipboard the data is getting shuffled among columns names Is there a way we can fix it in the above code with some changes 

To see attachments, please log in.

We'd prefer it if you saw us at our best.

Pega Collaboration Center has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice