Excel export using 'export to content' options

Hi ,
 
I have a sample cross tab "Cross_tab_Excel.rptdesign". It has an excel button which export the output to excel as "Cross_tab_Excel_Button.xls" but the output should be like "Cross_tab_Excel_Require_Output.xls".
 
When I use the 'export to content' option and check uncheck the properties "screen_shot_export.jpg" then we get the output as "Cross_tab_Excel_Require_Output.xls".
 
So how to use these properties in report's 'excel button'.
 
 
Thanks in advance!!!

Find more posts tagged with

Comments

Clement Wong

Those format conversion options (such as $$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.multipleSheet) are only available from the /iv servlet.
 
The drill through hyperlink you defined uses /executereport.do which will ignore those options, and will actually rerun your report.
 
There's no need to rerun the report. What you want to do it make the hyperlink just perform a Export Content option to Excel as if a user manually selected the menu option in the Viewer.
 
You can either add a HTML Button Report Item, or a HTML Report Item.
 
If use an HTML button, you can add a new onClick event, that will generate the Excel with the crosstab exported out without it being converted to a native Excel pivot table.
<pre class="_prettyXprint">
this.onclick = function(event)
{
window.location = window.location.href + "&__format=XLS&&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.wrappingText=true&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.EnablePivotTable=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.AutoFitPivotTable=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_ChartDpi=192&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.ExportChartsAsImages=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.EnableLiveFormula=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.hideGridlines=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.multipleSheet=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.hideDataSheets=true";
}
</pre>
I tested this in iHub 3.1 and 3.1.1, and it works for me.

RRK

Hi Clement,
 
Thanks it worked as expected but how can we set it for xlsx format ?

Clement Wong

In my code snippet above, instead of __format=XLS, change that to __format=XLSX.

RRK

Hi Clement,
 
I tried to change the format to XLSX in the above code, it exports it to xlsx but doesnt set the export properties as 
wrapping text = true
multiple sheet = true etc..
 
could you please check on this ?

RRK

Hi Clement,
 
I tried this in attached example.And have updated the code as : 
this.onclick = function(event) 
{ 
window.location = window.location.href + "&__format=XLSX&&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.wrappingText=true&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.EnablePivotTable=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.AutoFitPivotTable=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_ChartDpi=192&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.ExportChartsAsImages=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.EnableLiveFormula=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.hideGridlines=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.multipleSheet=false&$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.hideDataSheets=true"; 
}
 
It exports the report to xlsx format but does'nt hide the gridlines.Please find attached report output.
In this case the other properties didn't seem to be working.

Clement Wong

Taking this button out of the equation for troubleshooting... do those options work from the Export Content in the Viewer when you manually export to Excel? If not, then you'll need to contact support to have them assist you further.
 
Otherwise, you can try escaping the $$$ in the URL.

RRK

Hi Clement, 
Yes these option for exporting in xlsx and applying different options like 'multiple sheet', hide gridlines' format works while exporting the report by 'export content'option.
 
if we escape "$$$" then its not exporting in xls format as well. So your snippet is working as expected for xls but not for xlsx.
 
do we have different "$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption" for xlsx ?
 
Also have attached the cross tab report and in this i have set "&__format=XLSX" and have set : 
"$$$AC_CONVERSION_OPTION_rptdocument_XLS_excelRenderOption.hideGridlines=true "
But as in attached output it export the file to xlsx but not hiding the gridlines.
 
Cross_tab_Excel_using_export_content_option.xlsx - > using export content works fine(expected output)
Cross_tab_Excel_using_button.xlsx - > using button which is not exporting correctly.
 
 
 
Please let me know how I can contact support team.

RRK

Hi Clement,
 
Hope above example explains the issue scenario...
Is it possible to convert the file to .xlsx along with applying "page settings" properties in similar way as you suggested for xls ?

Clement Wong

Sorry, the options for XLSX are different. The prefix should be "$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.". For example, "$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.hideGridlines".
 
If the settings do not work from the UI web viewer's Export Option, then please contact Support to log a case.
<pre class="_prettyXprint">
$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.wrappingText:true
$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.EnablePivotTable:true
$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.AutoFitPivotTable:true
$$$AC_CONVERSION_OPTION_rptdocument_xlsx_ChartDpi:192
$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.ExportChartsAsImages:false
$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.EnableLiveFormula:false
$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.hideGridlines:true
$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.multipleSheet:false
$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.hideDataSheets:true
</pre>

RRK

Hi Clement,
 
Sorry but this approach is also not working.
I have used below code in my html button 'on click' : 
 
this.onclick = function(event) 
{ 
window.location = window.location.href + "&__format=XLSX&$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.wrappingText=true&$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.EnablePivotTable=true&$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.AutoFitPivotTable=false&$$$AC_CONVERSION_OPTION_rptdocument_xlsx_ChartDpi=192&$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.ExportChartsAsImages=false&$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.EnableLiveFormula=false&$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.hideGridlines=true&$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.multipleSheet=false&$$$AC_CONVERSION_OPTION_rptdocument_xlsx_excelRenderOption.hideDataSheets=true" 
}
 
Please find attached report. 
 
Please let me know how to raise a case with support team for this.

Clement Wong

I'm not sure where you are located and if you are current on maintenance, so here are our global support centers' information:
<a data-ipb='nomediaparse' href='http://www.actuate.com/resources/support/global-support-centers'>http://www.actuate.com/resources/support/global-support-centers</a>
 
The Support team would most likely want you to test with the out of the box commercial BIRT web viewer's UI, Export Content (to XLSX) and see if you see the same problem. Then, the next step would be to check the button and the conversion options.

RRK

Hi Clement,
 
The xlsx worked fine.
 
But one question on bookmark. If we have a hyperlink from one report to other eg(rpt-A to rpt-B and if we use bookmark to go to some page in the rpt-B then this export doesn't work.
So eg : I will have a 'sales' and 'region' two hyperlink in rpt-A and when the user click on 'Sales' it will search as a bookmark - Sales in rpt B. in this scenario it doesnt export the complete report.
Please find attached rptdesign as an example.

Clement Wong

I see the problem, but it appears that Excel is mishandling the redirect.
 
If I click on the link from within Excel, it sends me to the files page. Not the behavior we are expecting.
 
However, if I copy the link from Excel, and paste the link in a new private/incognito browser window, it redirects as expected.
 
 
To confirm, I set Chrome to be my default browser and closed out any existing windows. Copied and pasted the hyperlink and looked at the network traffic on the initial request:
 
GET /iportal/servlet/GenericRedirector?command=submit&__requesttype=immediate&invokeSubmit=true&__executableName=/Home/administrator/draft/Drilled.rptdesign HTTP/1.1 
Host: myserver:8700 
Connection: keep-alive 
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8 
Upgrade-Insecure-Requests: 1 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.109 Safari/537.36 
Accept-Encoding: gzip, deflate, sdch 
Accept-Language: en-US,en;q=0.8 Cookie: skin="treeview"; skin="treeview" 
HTTP/1.1 302 Found Set-Cookie: JSESSIONID=D2540F3E535AE59A9F2F4AF2F85C60BA; Path=/; HttpOnly 
Location: <a data-ipb='nomediaparse' href='http://myserver:8700/iportal/newrequest/do_executereport.jsp?command=submit&__requesttype=immediate&invokeSubmit=true&__executableName=/Home/administrator/draft/Drilled.rptdesign'>http://myserver:8700/iportal/newrequest/do_executereport.jsp?command=submit&__requesttype=immediate&invokeSubmit=true&__executableName=/Home/administrator/draft/Drilled.rptdesign</a> 
Content-Length: 0 
Date: Thu, 11 Feb 2016 20:59:43 GMT 
Server: server
 
 
Then, I closed out Chrome. Clicked on the hyperlink from Excel.
 
GET /iportal/servlet/GenericRedirector?command=submit&__requesttype=immediate&invokeSubmit=true&__executableName=/Home/administrator/draft/Drilled.rptdesign HTTP/1.1 
Accept: */* 
Accept-Encoding: gzip, deflate User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; InfoPath.2; MS-RTC LM 8; .NET4.0C; .NET4.0E) 
Host: myserver:8700 
Connection: Keep-Alive Cookie: JSESSIONID=36A083C13A3CD102AF3571CA93115019 
HTTP/1.1 302 Found 
Location: <a data-ipb='nomediaparse' href='http://myserver:8700/iportal/newrequest/do_executereport.jsp?command=submit&__requesttype=immediate&invokeSubmit=true&__executableName=/Home/administrator/draft/Drilled.rptdesign'>http://myserver:8700/iportal/newrequest/do_executereport.jsp?command=submit&__requesttype=immediate&invokeSubmit=true&__executableName=/Home/administrator/draft/Drilled.rptdesign</a> 
Content-Length: 0 
Date: Thu, 11 Feb 2016 21:11:15 GMT 
Server: server
 
There quite a few web pages that explain this (quirky) Excel behavior.
 
<a data-ipb='nomediaparse' href='http://stackoverflow.com/questions/16814418/why-does-clicking-links-in-excel-word-appear-to-cause-my-non-ie-browser-to-masqu'>http://stackoverflow.com/questions/16814418/why-does-clicking-links-in-excel-word-appear-to-cause-my-non-ie-browser-to-masqu</a>
<a data-ipb='nomediaparse' href='http://stackoverflow.com/questions/5843332/why-clicking-a-hyperlink-in-excel-will-generate-an-http-request-with-msie-7-0-in'>http://stackoverflow.com/questions/5843332/why-clicking-a-hyperlink-in-excel-will-generate-an-http-request-with-msie-7-0-in</a>
<a data-ipb='nomediaparse' href='http://webmasters.stackexchange.com/questions/71112/make-server-force-excel-to-hand-over-hyperlink-to-browser-without-trying-to-load'>http://webmasters.stackexchange.com/questions/71112/make-server-force-excel-to-hand-over-hyperlink-to-browser-without-trying-to-load</a>
<a data-ipb='nomediaparse' href='https://www.wobsta.de/blog/excel-followhyperlink-twice'>https://www.wobsta.de/blog/excel-followhyperlink-twice</a>
 
 
And this one:
<a data-ipb='nomediaparse' href='http://superuser.com/questions/444984/excel-hyperlink-not-redirecting-properly-bug'>http://superuser.com/questions/444984/excel-hyperlink-not-redirecting-properly-bug</a>
 
Clicking a URL in Excel seems to open it in your default browser. But that's not really true. Before opening it in your browser, Excel first runs Microsoft Office Protocol Discovery. This uses a Windows/Internet Explorer component to determine if the URL works. (It does not identify itself as Internet Explorer, but as "User Agent: Microsoft Office Existence Discovery".) And if the results are (somehow) okay then it will open the result of that check in your default browser
 
 
So the only workaround I can see is to copy the link and paste it into your browser so that is handled correctly.

RRK

Hi Clement, 
The scenario here is related to just download the correct data into Excel and not going from excel to any report. 
If you keep the above two files in Report Design folder in your birt designer and run main report it will show you two links 'country' and 'State'. Now this link will take you to drilled report and to specific page (if u click on country it will take you to first page and if you click on state then it will take you to States page directly) as I have used bookmarks. 
Now in this case on every page I have kept export button.When I click on it, it should download the complete report and not just few contain.

Clement Wong

Thanks for clarifying. Because you have drilled down to a bookmarked target, the browser window has the &__bookmark=value option in the URL. When you export with a bookmark, it will only export that section. That's why if you went to the menu item Export Content, it will export the entire report.
 
To fix your code, we'll use a JavaScript String replace with regex magic to take out the &__bookmark=value out.
 
In your HTML code, change this:
window.location.href
 
To this:
window.location.href.replace(/&__bookmark=[^&]+/,"")
 
And keep everything else the same.

RRK

Hi Clement,
 
Many thanks

This worked as expected ....cheers!!!

RRK

Hi Clement,
 
I just saw an observation that the above bookmark option is not working correctly.
means when i download. everything gets downloaded it should only download the content which is visible to the user.
 
eg: if i have two html button in my report. On one html button click i will show a table : names Table_1
and on second html button click it will show other table : named Table_2.
And the report has this excel button on which click only that table should get downloaded which is visible to user.
eg: if the user is viewing Table_1 and when he clicks on the export button then the downloaded excel should have only Table_1 and visa versa

Clement Wong

I'm not sure I quite understand. Do you have example reports of this new observation?
 
Also, I will not be available for the next two weeks. To ensure the best service response time, you may want to work with your local Support team on this issue. They will ask for an example as well that replicates the problem you're facing.