ListGrid export2Excel

[zdary]

Hi,

is it possible to export ListGrid data to Excel if the ListGrid doesn't have a DataSource?

I have a ListGrid where users drag and drop items so no DataSource is needed.
ListGridFields are created dynamically.

thank you,
Zdary

[Isomorphic]

exportClientData() supports Excel and export and does not require a server-side DataSource to be present.

[zdary]

Hi,

I tried this function and it seems that the excel file is generated wrongly.
Ater I choose to recovery the file then it seems ok as far as data is concerned. However, all cell are clack even though my ListGrid uses red color for some columns. See attachments.

DSRequest dsRequestProperties = new DSRequest();  
dsRequestProperties.setExportAs(ExportFormat.OOXML);  
dsRequestProperties.setExportDisplay(ExportDisplay.DOWNLOAD);  
 grid.exportClientData(dsRequestProperties);

ExportFormat.XLS works fine.
Tested on SmartGWT 3.0 EVAL 29-09-2011

best regards,
Zdary

[Isomorphic]

Please provide:
1. the version of Excel and OS you're testing with
2. the actual exported file that Excel didn't like

[zdary]

Hi,

the file is located on www.zdary.cz/export.xlsx
My OS is Win7 64bit, Microsoft Excel 2010 Version 14.0.6106.5005 (32bit)

=== 2011-10-01 18:26:02,170 [l0-2] INFO  BuiltinRPC - Generating and streaming ooxml file...
=== 2011-10-01 18:26:02,237 [l0-2] WARN  BuiltinRPC - Exception during export - continuing anyway to avoid sending the client to a blank screen
java.lang.NullPointerException
	at com.isomorphic.rpc.DataExport.exportResultSet(DataExport.java:524)
	at com.isomorphic.rpc.DataExport.exportResultSet(DataExport.java:376)
	at com.isomorphic.rpc.DataExport.exportResultSet(DataExport.java:369)
	at com.isomorphic.rpc.BuiltinRPC.downloadClientExport(BuiltinRPC.java:717)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.isomorphic.base.Reflection.adaptArgsAndInvoke(Reflection.java:950)
	at com.isomorphic.base.Reflection.adaptArgsAndInvoke(Reflection.java:803)
	at com.isomorphic.rpc.RPCDMI.execute(RPCDMI.java:305)
	at com.isomorphic.rpc.RPCDMI.execute(RPCDMI.java:66)
	at com.isomorphic.rpc.RPCRequest.execute(RPCRequest.java:229)
	at com.isomorphic.servlet.IDACall.handleRPCRequest(IDACall.java:245)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:152)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:121)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1097)
	at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:259)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:368)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
	at cz.bcom.smartrise.server.authentication.SpringConcurrentSessionFilter.doFilter(SpringConcurrentSessionFilter.java:62)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:169)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:324)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
	at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
=== 2011-10-01 18:26:02,238 [l0-2] INFO  ProxyHttpServletResponse - content-disposition set to: attachment; fileName=export.xlsx - unwrapping response output stream (CompressionFilter)
=== 2011-10-01 18:26:02,247 [l0-2] DEBUG RPCDMI - rpc returned data

cheers,
Zdary

[Isomorphic]

The Excel error window mentions an error log file that describes what data had to be repaired or discarded - could you please provide that file as well?

[zdary]

the error file is an XML

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error022680_01.xml</logFileName><summary>Errors were detected in file 'C:\zdary\svn\export.xlsx'</summary><additionalInfo><info>Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.</info></additionalInfo></recoveryLog>

I can prepare a test case for you should you need it.

Can I have an additional question? the method exportClientData has a javaDoc "Exports this component's data with client-side formatters applied". Does it mean that colors, fonts, number of decimal places will be applied as well?
At the moment the excel sheet contains everithing in default black for dsRequestProperties.setExportAs(ExportFormat.XLS);

Is there any override point for this proccess? For example I'd like to add a title for the exported table.

The goal is to have this excel in user friendly format so that they can print it out and it would look like a table from GUI.

best regards,
Zdary

[Isomorphic]

Thanks for the error report - this was a regression introduced by the new ability to export to the server filesystem. We have now fixed this, fix will be present in nightly 3.x builds as of tomorrow. Note that this problem does not affect SmartClient 8.0 / SmartGWT 2.5 releases.

On your other question: we only export client-side color formatting that is due to hilighting, because base formatting is not always appropriate in an export to Excel where users often want to apply their own formatting instead. If you want to export color formatting exactly as it is in your grid, apply that formatting via hilites, by just specifying a hilite expression that is always true.

[zdary]

Hi,

in IE9 and Chrome there is a problem with a default file name. It is without extension and excel therefore has a problem with opening it.
In firefox6 it works. The default file name is export.XLS

If I set explicitly dsRequestProperties.setExportFilename("export.xls"); the it works fine.

=== 2011-10-09 20:16:06,313 [l0-4] INFO  BuiltinRPC - Export data passed to server:
[
    {
        Obdobie:"1Q2011",
        Región:"81 - Nitra",
        CS:"2.88",
        CSE:"2.88"
    }
]
=== 2011-10-09 20:16:06,319 [l0-4] INFO  ProxyHttpServletResponse - content-disposition set to: attachment; fileName=export - unwrapping response output stream (CompressionFilter)
=== 2011-10-09 20:16:06,322 [l0-4] INFO  BuiltinRPC - Generating and streaming xls file...
=== 2011-10-09 20:16:06,761 [l0-4] DEBUG RPCDMI - rpc returned data

SmartGWT 2011-10-06

[zdary]

we found an error in encoding for a method setExportFilename.
If you pass in a text from i18n constants then the actual file name is wrong.

the accent charts will break processing.

for example:

setExportFilename("Dimenzie_1Q2011_Úroveň_výsledkov_pobočiek.xls");

the file name is Dimenzie_1Q2011_ÚroveH_výsledkov_pobo
Note that the file name doesn't have an extension.

SmartGWT 2011-10-06
best regards,
Zdary

[Isomorphic]

There is no cross browser way to encode a parameter value like a content-disposition filename that contains characters outside of the US-ASCII charset. From RFC 2183, the current standard:

"Current [RFC 2045] grammar restricts parameter values (and hence Content-Disposition filenames) to US-ASCII. We recognize the great desirability of allowing arbitrary character sets in filenames, but it is beyond the scope of this document to define the necessary mechanisms."

It is true that more recent RFCs propose a mechanism for encoding these values in other characters sets, but they are not supported by all browsers (not even by all modern browsers). For that reason, we don't currently have plans to implement support for special characters in downloaded filenames.

[zdary]

Hi,

ok, can you at least convert the filename into US-ASCII reasonably?

for example:

Pattern pattern = Pattern.compile("\\p{InCombiningDiacriticalMarks}+");
String fileName = pattern.matcher(Normalizer.normalize(rawFileName, Normalizer.Form.NFD)).replaceAll("");

This code will do nothing to String already in US-ASCII but will normalize accent string into US-ASCII in a way that it doesn't loose extension...


I cannot do this in GUI because GWT doesn't like this class and I have not find out the override point for this proccess on a server side. Is there one?

best regards,
Zdary

[Isomorphic]

The Normalizer class you refer to here is specific to JDK 1.6, and we don't want to introduce a dependency on that version. Since it is client code that sets that filename in the first place, it seems like the thing to do is fix it on the client - sure, Normalizer is not a GWT-enabled class, but you could do conversion on the name with regexp's, or just scan the string for any unexpected characters and tell the user to try again.