Apache POI, forms, Hippo and excel download

65 views
Skip to first unread message

Kitty Liem

unread,
Jun 15, 2018, 12:41:03 PM6/15/18
to Hippo Community

I have an export component with a form where you can select element (tag with a button) and from/until dates. 

The info is used to get database info and is put into an Apache POI XSSF  Workbook in the doAction of the component using Workbook and FileOutputStream in the doBeforeRender to present excel download.
I do get an excel but it does not contain the content I made in the Workbook, instead it contains the HTML from the page (and excel will not open file).
Googleing gives me that I should try to use ServletOutputStream, which I can get from the HstResponse but then the JSP tag fails with an org.apache.jasper.JasperException and I get an empty page. The reason the jsp fails is not clear to me or if the ServletOutputStream from HstResponse is the one I should use.
 
A solution in Hippo version 7.9 with a form and method GET and a servlet extending HttpServlet kind of worked with doGet, the jsp and a lot of not so clear sitemap configuration but it will not work in version 12.1.
 
Anybody experience with Apache POI and forms and/or tips?

Woonsan Ko

unread,
Jun 15, 2018, 1:47:19 PM6/15/18
to hippo-c...@googlegroups.com
Hi Kitty,

Please don't use #doBeforeRender() to open and write to an output stream.
Instead, combine (a) HST Resource URL, (b) #doBeforeServeResource() and (c) a dedicated servlet code.
Here's the detail.

1. HST Resource URL

In your HstComponent's template, use <@hst.resourceURL/> to create a download link:

  <@hst.resourceURL var="downloadLink" resourceId="download" />

And use the "downloadLink" as form action attribute with POST method while having other (hidden) input fields for start date and end date, and place a submit button for download.

2. In your HstComponent's Java code, implement #doBeforeServeResource(...).

public class MyComponent extends BaseHstComponent {

    // SNIP

    public void doBeforeServeResource(HstRequest request, HstResponse response) throws HstComponentException {
        if ("download".equals(request.getResourceID())) {
            String startDateParam = request.getParameter("start");
            String endDateParam = request.getParameter("end");
            // Do database query here..
            // And convert those into a (container) POJO object ("myModel").
            // Finally set the object as request attribute for the 'serializing' servlet.
            request.setAttribute("model", myModel);
        }
    }

}

3. Write a 'serializing' servlet

public class BinariesServlet extends HttpServlet {

    // SNIP

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
        throws ServletException, IOException {
        doGet(req, resp);
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
        throws ServletException, IOException {
        // Read the model
        Object myModel = request.getAttribute("model");
        if (myModel != null) {
            // get servlet output stream from resp and write output using POI.
        }
    }

}

You need to configure this servlet in site/.../web.xml by either path info or name.

3.1. Register the servlet as an hst:template

Add an hst:template node with @hst:renderpath = "<the servlet mapping path>" if you set the servlet by path with servlet-mapping.
Or an hst:template node with @hst:renderpath = "<the servlet name>" and @hst:isnamed = true if you set the servlet by name only.

3.2. Let your component understand the new resource template.

Set your component configuration to have @hst:resourcetemplate = '<the template name added in 3.1>'.

Test.

-----

It's a bit long, but I think it should be straightforward.
The reason why you cannot use #doBeforeRender() in a component is because the normal request handling in HST is page-rendering in which each component is supposed to contribute some markups to the whole page. As a result, it is not just invoking your #doBeforeRender() but invokes all the other components and aggregate into the page. That's why you ended up seeing html content by doing that.

HST Resource URL and its companion methods and template is designed for your use case as well as others such as some ajax calls from html. In Resource URL serving phase, HST invokes only that specific component's #doBeforeServeResource() and its associated template, which should be better with a dedicated servlet if you're not serving simple text-based json but serving a binary stream.
You will find more detail on this architectural distinctions in our online site.

HTH,

Woonsan



--
Hippo Community Group: The place for all discussions and announcements about Hippo CMS (and HST, repository etc. etc.)
 
To post to this group, send email to hippo-community@googlegroups.com
RSS: https://groups.google.com/group/hippo-community/feed/rss_v2_0_msgs.xml?num=50
---
You received this message because you are subscribed to the Google Groups "Hippo Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hippo-community+unsubscribe@googlegroups.com.
Visit this group at https://groups.google.com/group/hippo-community.
For more options, visit https://groups.google.com/d/optout.



--

Kitty Liem

unread,
Jun 20, 2018, 11:57:08 AM6/20/18
to Hippo Community
Hi Woonsan,

Thanks for your reaction! 
It seems I'm stuck after several tries to solve the problem.

All steps work fine, 1. jsp uses hst:resourceURL, 2. component has doBeforeRender that sets workbook as attribute, 3. Servlet with servlet mapped to path info for the page and resource template added to main of page and then it goes wrong.

doPost serves the excel to the browser page not as download, but as excel on browser page with as URL "sitemapitem path info/requested-download?_hn:type=resource&_hn:ref=r34_r1&_hn:rid=downloadExcel, and stops rendering. 
content type is set to application/vnd.openxmlformats-officedocument.spreadsheetml.sheet and content disposition attachment; filename=\"%s\"
 
The renderpath for servlet is the same as sitemapitem path info for page. It would not work if I gave servlet its own renderpath. 

Do I assume correctly that configuring servlet by name means using annotation @WebServlet? I tried that also.  

What should I look at to get this working?

Thanks!

Kind regards,
Kitty 

Op vrijdag 15 juni 2018 19:47:19 UTC+2 schreef woonsan.ko:
To post to this group, send email to hippo-c...@googlegroups.com

RSS: https://groups.google.com/group/hippo-community/feed/rss_v2_0_msgs.xml?num=50
---
You received this message because you are subscribed to the Google Groups "Hippo Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hippo-communi...@googlegroups.com.

Woonsan Ko

unread,
Jun 20, 2018, 12:09:25 PM6/20/18
to hippo-c...@googlegroups.com
Hi Kitty,

I guess I missed one more step:

3.3. Register a container resource sitemap item for the servlet

By default, HST container tries to process most URL as a component aggregation processing page.
So, you need to exclude some paths if those paths are not supposed to be processed as a component aggregation page by HST like the default BinariesServlet at /binaries.
The 'exclusion' for the BinariesServlet (path = '/binaries') is configured at /hst:hst/hst:configurations/hst:default/hst:sitemap/binaries. [1]
Therefore, one of the easiest way is as follows (supposing your servlet path mapping is at '/myexceldownload' below):
- Copy /hst:hst/hst:configurations/hst:default/hst:sitemap/binaries to /hst:hst/hst:configurations/hst:default/hst:sitemap/myexceldownload.
- Save.

If this step is missing, then HST must have tried to map the url (.../myexceldownload) by finding a sitemap item (probably .../_any_) and render something or 404 html content.
By doing this step, HST will invoke the servlet on paths at .../myexceldownload/*. So the servlet will have the full control.

-----

If this helps you solve the issue, then you can try with servlet-name only or @WebServlet annotation without servlet-mapping.

Let me know.

Woonsan



To post to this group, send email to hippo-community@googlegroups.com

RSS: https://groups.google.com/group/hippo-community/feed/rss_v2_0_msgs.xml?num=50
---
You received this message because you are subscribed to the Google Groups "Hippo Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hippo-community+unsubscribe@googlegroups.com.

Kitty Liem

unread,
Jun 20, 2018, 3:45:20 PM6/20/18
to Hippo Community
Hi Woonsan,

It still is not working, I think it has to do with taking configuration and sources from 7.9 to 12. 
I think it has to do with a page that renders 7.9 style in 12 environment, e.g. the mains are hst:components on hst:page and not hst:containercomponents in workspace.
Maybe I can solve the problem by setting up the configuration differently? 
 
Following the path as it is now, in version 12:
hst:sitemap: /b/f/e/_any_: hst:componentconfigurationid hst:pages/export, hst:relativecontentpath f-e/${1}
hst:pages/export: hst:referencecomponent abstractpages/base (hst:template base-lay-out)
main (hst:component): hst:referencecomponent hst:components/export-component, hst:resourcetemplate download-excel, hst:template export
hst:components/export-component: hst:componentclassname ExportComponent, hst:resourcetemplate download-excel, hst:template export
hst:templates/export: hst:renderpath export/main.jsp
hst:templates/download-excel: hst:renderpath b/f/e/${1}
The servlet I mapped to /b/f/e/* in web.xml because hst:resourceURL uses sitemap path of current document.
/hst:hst/hst:configurations/hst:default/hst:sitemap/b/f/e/_any_ with containerresource true on b.

Rendering just gives empty page now, so I think the rendering lost its way because current configuration (mainly primary types) is not ok for verion 12.  

In 7.9 version they made it work using hst:link with sitemapRefId to export/download (servletmapping, with another hst:componentconfigurationid) instead of hst:resourceURL (and doBeforeRender).   

What do you think, is this an option I should further investigate?
 
I will be working on this tomorrow again.

Thanks for all your efforts!

Kind regards,
Kitty 
 


Op woensdag 20 juni 2018 18:09:25 UTC+2 schreef woonsan.ko:

Woonsan Ko

unread,
Jun 20, 2018, 4:05:52 PM6/20/18
to hippo-c...@googlegroups.com
Hi Kitty,

Are you testing on v12 now?
There are some changes in versions since 7.9, but HST Resource URL handling has not been changed.
I'd like to make a simple demo project which has a download link in the news article overview (list) page. The download link may simply demonstrate downloading a CSV file (containing document titles in CSV format). So you will be able to run it and compare with it. I guess that will make it easier.
Could you confirm your version?

Regards,

Woonsan


To post to this group, send email to hippo-community@googlegroups.com

RSS: https://groups.google.com/group/hippo-community/feed/rss_v2_0_msgs.xml?num=50
---
You received this message because you are subscribed to the Google Groups "Hippo Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hippo-community+unsubscribe@googlegroups.com.

Kitty Liem

unread,
Jun 20, 2018, 4:41:18 PM6/20/18
to Hippo Community
v12.1.0

Use case is using document mapping to jsp with form to get data from database and export result to excel download in browser and rendering filled in form again.

Thanks!


Op woensdag 20 juni 2018 22:05:52 UTC+2 schreef woonsan.ko:

Woonsan Ko

unread,
Jun 21, 2018, 1:18:29 AM6/21/18
to hippo-c...@googlegroups.com
I've created a running example:

Please see README.md. All the steps mentioned earlier are valid, but I found a gotcha:

Woonsan


To post to this group, send email to hippo-community@googlegroups.com

RSS: https://groups.google.com/group/hippo-community/feed/rss_v2_0_msgs.xml?num=50
---
You received this message because you are subscribed to the Google Groups "Hippo Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hippo-community+unsubscribe@googlegroups.com.

Kitty Liem

unread,
Jun 21, 2018, 3:03:17 AM6/21/18
to Hippo Community
Hi Woonsan,

Yes, it works!

Your help is very appreciated!

It would be nice if the gotcha would be added to serve-a-dynamic-resource page. This because if you google for solution you will find for POI that header and content type should be set on HttpServletResponse.

Kind regards,
Kitty.
 

Op donderdag 21 juni 2018 07:18:29 UTC+2 schreef woonsan.ko:

Woonsan Ko

unread,
Jun 21, 2018, 9:01:13 AM6/21/18
to hippo-c...@googlegroups.com
On Thu, Jun 21, 2018 at 3:03 AM, Kitty Liem <lie...@gmail.com> wrote:
Hi Woonsan,

Yes, it works!

Your help is very appreciated!

It would be nice if the gotcha would be added to serve-a-dynamic-resource page. This because if you google for solution you will find for POI that header and content type should be set on HttpServletResponse.

You're right.
I filed an improvement in HSTTWO:

Please anyone leave comments or click on "Vote" link there if you agree or like. ;-)

Woonsan
 
To post to this group, send email to hippo-community@googlegroups.com

RSS: https://groups.google.com/group/hippo-community/feed/rss_v2_0_msgs.xml?num=50
---
You received this message because you are subscribed to the Google Groups "Hippo Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hippo-community+unsubscribe@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages