Jan 6, 2009

Flex datagrids and Excel part 2

A few moths ago I write a post on extracting data from a flex datagrid see: HERE

I had several people contact me about this regarding extending functionality or how to get it into a file. At the time I was asked to implement the function the customer wanted it into the clipboard hence the copy/paste method I produced but I didn't think about how easy it would be to extend this to a downloadable file.

John Gag mentioned a method he uses which didn't quite work when I had date formatters. We currently generate many excel spreadsheets using HTML formatting to color code excel but I wanted a simpler version with no formatting/colors. Excel will automatically populate cells based on TAB delimited strings so I used my original method with a few changes and simply posted this back to Coldfusion ( or in this case Railo ) or PHP. ( I tested this in OSX with Excel, Windows with Excel and Ubuntu with Open Office and they all worked ).



View source is enabled: http://demo.kukiel.net/flex/copy/srcview/index.html

Demo: http://demo.kukiel.net/flex/copy/

And below is the CFML and PHP.
CFML:


PHP:


Now I'm sure this method could apply to asp, pl or any other server side language I just made it for the 2 I have available. The great thing about this method is that I can send any tab delimited data from a grid as a POST to my server without having to customize server side code in anyway.

The data being displayed in the grid comes directly from Full as a goog
Posted by Paul Kukiel at 12:02 PM
Labels: ,

21 comments:

  1. Cool, I am going to have to check this out. Thanks!
    ReplyDelete
  2. I have tried to change your test application to accommodate an arraycollection instead of an RSS feed and I keep getting in the excel sheet that the headers a shifted one cell to the right. Enabling the clipboard functionality and pasting directly into excel does not have this problem.

    How can I correct this?
    ReplyDelete
  3. Perhaps the first Item in the arrayCollection is empty? Hence its putting in an empty TAB
    ReplyDelete
  4. theData = new ArrayCollection([
    {name:"John Smith",isManager:false,age:34,sex:"M"},
    {name:"Jenny Zurich",isManager:true,age:45,sex:"F"},
    {name:"Jason Ruins",isManager:false,age:22,sex:"M"},
    {name:"Tim Johnson",isManager:false,age:21,sex:"M"},
    {name:"Mary Winn",isManager:false,age:20,sex:"F"},
    {name:"Joe Rock",isManager:true,age:31,sex:"M"}
    ]);
    ReplyDelete
  5. Without seeing all the code I'm just not sure. I know I have a copy from arrayCollection mod myself and it is working fine. If you want to pastebin the code I can take a look.
    ReplyDelete
  6. The code looks fine. Perhaps its a server setting. In the Coldfusion / Railo admin there is "Suppress white space" try turn that on see if it helps.
    ReplyDelete
  7. That is exactly the the solution. In the Web Admin of Railo under the Settings menu, there is a sub-item called Output and on that page is a checkbox as follows:

    "Removes all white spaces in the output that follow a white space"

    I checked that and all was fine.

    For Coldfusion 7.02...

    In the Administrator, Server Settings menu, Settings sub-menu check the checkbox:

    "Enable Whitespace Management
    Reduces the file size of the pages that ColdFusion returns to the browser by removing many of the extra spaces, tabs, and carriage returns that ColdFusion might otherwise persist from the CFML source file."

    Much thanks to Paul for helping me troubleshoot this problem and find the solution.
    ReplyDelete
  8. Another issue that I had was printing descriptions that were text areas and included tabs, new lines and carriage returns.

    In the copyToDatagrid function where 'Remove all " as it messes with the tab delimited string' is, I added this to remove those characters from the the strings:

    data = data.replace(/\r/g, " ");
    data = data.replace(/\n/g, " ");
    data = data.replace(/\t/g, " ");
    ReplyDelete
  9. When I try to implement the excel.php wiht excel.jsp, I get the content in a single cell. The content is not getting delimeted. This is what my code in the jsp. Please throw some light into it:

    page contentType="application/vnd.ms-excel"

    response.setHeader("Content-Disposition", "attachment; filename=\"Workbook.xls\"");

    out.print(request.getParameter("htmltable"));
    ReplyDelete
  10. Hi Madhava, I would say its something to do with the issue Mike had where additional whitespace was being introduced somewhere breakign the tab delimitation. I'm not sure how to over come this with jsp ( assume tomcat ) but you could wrap the data in html table code which excel will also will open and display fine. You can find this example at this link: http://www.saskovic.com/blog/?p=3
    ReplyDelete
  11. Thanks a lot and it helped me a lot.
    ReplyDelete
  12. Thanks alot paul, for this post
    ReplyDelete
  13. i cant see the flex application, and the link is broken http://cf9.kukiel.net/blog/datagridexcel/

    can you please share the new link.,...
    ReplyDelete
  14. @Anju singh

    Sorry about that as I was moving to CF9 I messed up some of the links. All those links and demos should now be restored.
    ReplyDelete
  15. Paul -

    I need your help. I'm VERY new to FLEX (About 3 days old) and my client wants exactly what you did, however when I view your source, download the zip, and import the folder to Flex 3 professional, I am then confused on what I need to do. Do you have step-by-step instructions for us newbies on actually getting your application to work with the server side to the point where we can actually see the results you are seeing?

    Because I'm so confused with serverside/flex interactions, I don't know how to implement your toolset.

    Either that or if you can point me to a REALLY easy to read step-by-step method on another authors functionality on something similar, I can run with that for now before using your technique.

    let me know your thoughts.
    ReplyDelete
  16. Paul - Additionally, when I import your entire FLEX application (after viewing source), I get the error message:

    Creating output directory...)Time of error: March 24, 2010 ...)
    Reason:
    'CF_FLEX_OUTPUT_FOLDER/DataGridCoy/' is not a valid location. The location is relative to undefined workspace path variable 'CF_FLEX_OUPTUT_FOLDER'

    What do I do from here?
    ReplyDelete
  17. @Piyush Are you using ColdFusion as the backend? Perhaps I can do a full walk through as a screencast?
    ReplyDelete
  18. Paul, the link provided for the source is broken. Can you please share the new link?
    ReplyDelete
  19. @Lorena here it is:

    http://www.kukiel.net/demos/datagridexcel/srcview/index.html
    ReplyDelete
  20. Anyone got this working with .NET yet?
    ReplyDelete
  21. @Richard I have an example of this using .NET with c# here: http://blog.kukiel.net/2011/08/net-c-flex-datagrids-and-excel.html
    ReplyDelete

Subscribe to: Post Comments (Atom)