Support Article

Incorrect Data format/spreadsheet format on export to excel

SA-28791

Summary



The user is exporting data to excel using cutom activity with following steps
 
  1. Step Property-Set-HTML  to set pyiewData parameter
  2. Then calling  OOTB ViewExcelData.


Error Messages

Not applicable

Steps to Reproduce

  1. Create HTML stream to populate data in the excel
  2. Create an activity with step "Property-Set-HTML" to set pyViewdata parameter with step1 stream rule
  3. Call OOTB ViewExceldata.


Root Cause



A software use or operation error :-

In MAC , safari browser exporttoexcel activity  is downloading excel with HTML text directly instead of the excel formatted sheet.

User while generating the HTML stream has added only <table><tr><tdt>....</td></tr></table> to populate the data in table structure but not added valid HTML markup for excel formatting 

as 
<html><head><META HTTP-EQUIV=\"Content-Type\" CONTENT=\"CHARSET=UTF-8\"><style>...</style></head><body>...</body></html>, 

which only in MAC safari/firefox browser is having issue, where excel getting saved as .csv file
 

Resolution



Perform the following local-change: 

Adding below code in the HTML rule resolved  issue.


{JAVA}
appendString( "<HTML>" );
    
    appendString( "<head><META HTTP-EQUIV=\"Content-Type\" CONTENT=\"CHARSET=UTF-8\">" );
    
    
    appendString( "<style>" );
    appendString( ".excel-hide-row{display:none;}" );
    appendString( ".excelText{vnd.ms-excel.numberformat:@;}" );
    appendString( ".excel-datetime{vnd.ms-excel.numberformat:m/d/yyyy h:mm AM/PM}" ); /// needed for date/number formatting style
    appendString( ".excel-date{vnd.ms-excel.numberformat:m/d/yyyy}" );
    appendString( ".excel-timeofday{vnd.ms-excel.numberformat:hh:mm}" );
    appendString( ".excel-number{vnd.ms-excel.numberformat:0;}" );
    appendString( "</style>" );
    appendString( "</head> ");

String viewData = tools.getParamValue( "ViewData" ).toString();

appendString(viewData ); //viewdata is the property that contains the userspecific HTML stream


appendString( "</HTML>" );
{/JAVA}


 

Published October 4, 2016 - Updated October 17, 2016

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.