Import, Export, Copy & Paste Flex DataGrid

In addition to searching, two common features implemented in DataGrids are import/export and copy/paste. There are a number of great solutions already out there. The purpose of this post is to show how we can bring them together (along with some additional code/tweaks) to provide the user with a DataGrid which plays nicely with their favorite spreadsheet application.

Overview of demo

  • There are three ways to copy the selected items from the left DataGrid: using the “Copy” button, pressing Ctrl+C or right clicking and then selecting “Copy Item(s)”.
  • The copied data is stored in tab separated format, when pasting it into a spreadsheet it will retain its column structure.
  • To paste data into the right DataGrid give it the focus by clicking on it and then press Ctrl+V.
  • Click “Import” to upload either a CSV (comma sperated) or TSV (tab separated) file.
  • Click “Export” to generate a CSV file of the data in the DataGrid.

Import/Export

In Flex 3, in order to upload a file you need a small script on the server (in Flex 4/Flash 10 the client is able to handle this locally). Included in the source you’ll find two PHP files: csvImport.php and csvExport.php. To support exporting the data from the DataGrid I’m leveraging a nice algorithm written by Sasa Radovanovic explained in this post.

This solution is meant to work with plain text csv and tsv files, if you need to support Excel (.xls) files I’d recommend checking out as3xls.

Copy/Paste

One of the limitations of the Flash Player is that (for security reasons) we’re unable to access data from the user’s clipboard. This makes pasting rather tricky. Manish Jethani has documented a clever solution in this post where he uses a hidden text field to allow the user to paste data into a DataGrid.

Field Mapper

This component provides the user with a way to map the columns in the data to the properties of the objects. It will attempt to determine if the first row of the data looks like it should be a header row and will then try to automatically map the fields.

Hope you find this useful,
Hillel

41 thoughts on “Import, Export, Copy & Paste Flex DataGrid”

  1. I’ve downloaded your source code but seem to be having a problem getting it to work locally. After I select the file to import from, the import data form just shows col$x and $part in the datagrid. I’m relatively new to flex and have little experience with php. Any ideas what I may be doing wrong? Thanks in advance.

    Reply

    1. Matt,

      The first step is to figure out if the problem is with the PHP script or the Flex app. Could you please send me a sample of the data returned from the script. You can either add a trace statement or use a tool like Charles Proxy to sniff the traffic.

      Reply

  2. Hillel,
    Thanks for the quick reply. Sorry, you’ll have to put on kids gloves as I’m apparently more of a noob than I thought. Let me tell you what I did so far.
    I use Flex Builder 3. I tried to import your zip directly but it would not work. It didn’t recognize the zip file as a Flex Archive either, so I unzipped it and then manually moved the files into my project. I placed the php files in the same folder as the consts.as file and index.mxml files. I modified the consts.as file to use http://localhost:8500/excelGrid/ as the SERVER_URL. I did not understand the ‘secret’ part of the CSV_IMPORT_SCRIPT string, so I’ve tried a variety of different things. Currently it is set to csvImport.php?secret=”. The app runs as expected. I can browse to the files and select the csv, but it does not show me the columns like your online example does with my same csv file.
    I downloaded the Charles Proxy and tried it but when I run your app, nothing shows in the list referencing the php files. I wasn’t sure where to trace the data you asked for either so I’m sure I’m not helping much.
    Sorry for being apparently useless on this end. Anything you can do to help me is very much appreciated.

    Reply

    1. Matt,

      Seems like you’re moving in the right direction. The “secret” property is meant to secure the PHP scripts, w/o it anyone could call the script (for it to be effective you should also you HTTPS). Ok, back to the problem at hand… try adding a trace statement in FieldMapper.mxml on line 90 and print out the value of the XML string.

      Reply

      1. Trace results:

        ";

        $file = file_get_contents($name);
        $file = str_replace("\r\n", "\n", $file);
        $file = str_replace("\r", "\n", $file);
        $lines = explode("\n", $file);

        $isTsv = strpos($lines[0],"\t");
        $delimiter = $isTsv ? "\t" : ",";
        $expr="/$delimiter(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";

        foreach ($lines as $line)
        {
        $line = trim($line);

        if (!$line)
        {
        continue;
        }

        $str .= "";
        $x = 0;

        $parts = preg_split($expr, $line);

        foreach ($parts as $part)
        {
        $x++;

        $part = preg_replace("/^\"(.*)\"$/","$1",$part);
        $part = htmlentities($part);

        $str .= "$part";
        }

        $str .= "";
        }

        $str .= "";

        echo $str;
        exit;

        ?>

      2. Matt,

        There’s something wrong with your server, I’d check to make sure that PHP is running correctly. It looks like the server is treating the PHP script as a regular file.

      3. OK, I have just been using the Flex 3 Builder built in server for debugging files. I published the files to our web server (which has PHP 4.4.4 installed) and got this error…

        ReferenceError: Error #1069: Property rows not found on String and there is no default value.
        at com.hillelcoren.components::FieldMapper/handleFileUploaded()

        Do I need to install full IIS and PHP on my local machine in order to get the debugging working properly on my machine?

      4. Matt,

        You can use the PHP script running on the server from your local machine (you don’t need PHP running locally). Can you once again send me the trace from the same point as before.

      5. Got this error when I tried to import using the PHP on the production server. I know it’s something I should understand, but I’m not sure where to fix it. Sorry.

        Error #2044: Unhandled SecurityErrorEvent:. text=Error #2049: Security sandbox violation: http://localhost:8500/excelGrid/index.swf cannot upload data to http://www.interfacesys.com/excelGrid/csvImport.php?secret=”.
        at com.hillelcoren.components::FieldMapper/handleBrowseClicked()[C:\Users\mmontana\Documents\Flex Builder 3\excelGrid\src\com\hillelcoren\components\FieldMapper.mxml:66]
        at com.hillelcoren.components::FieldMapper/__browseButton_click()[C:\Users\mmontana\Documents\Flex Builder 3\excelGrid\src\com\hillelcoren\components\FieldMapper.mxml:356]

  3. Hello Hillel,

    I used the AdvancedAutoComplete in English and it works very well. But when I tried to use it with Arabic, it looks does not work.

    I tried modified function highlighMatch in StringUtils.as to following code:

    var inputStr:String=string;
    var matchPos:int = inputStr.lastIndexOf(searchStr);
    var stringLength:Number=inputStr.length;
    var otherStr:String = inputStr.substring(matchPos+1,stringLength);
    var otherStr:String = inputStr.substring(0,matchPos);
    var returnStr:String =”“+searchStr+”” +otherStr;

    but after change, all characters in the dropdown list become invisible.

    If I change code from

    var otherStr:String = inputStr.substring(matchPos+1,stringLength);

    to

    var otherStr:String = inputStr.substring(0,matchPos);

    all characters in the dropdown list become visible but in wrong sequence.

    I am confused, please help. Thanks a lot in advance.

    Reply

    1. sorry, the code that characters in the dropdown list become invisible is

      var inputStr:String=string;
      var matchPos:int = inputStr.lastIndexOf(searchStr);
      var stringLength:Number=inputStr.length;
      var otherStr:String = inputStr.substring(matchPos+1,stringLength);
      var returnStr:String =”“+searchStr+”” +otherStr;

      Reply

  4. Really useful component. I’ve taken the DataGridUtils class and used it as part of an AIR app to export csv data from a datagrid, and it worked pretty much perfectly first time. Great job!

    Reply

  7. Hi Hillel,
    Another fantastic component I can’t live without. One *minor* nit, I’m getting truncated csv when I put a “\n” in one of the labelFields for rending in the datagrid. I’m considering a regex aorund line 118 to remove all “\n” for the TYPE_CSV? Any reasons why I shouldn’t do that? Thought I might mention it, to your post. Anyway, Thanks for sharing!

    Reply

    1. Doug in Paia,

      Pleased to hear you’re finding the component useful. I can’t think of any reasons that should be a problem.

      Best,
      Hillel

      Reply

  8. Hi,

    I am applying filterfunction(s) on the arraycollection that is the dataprovider for the datagrid. When I export, the xls file shows unfiltered data as well. How can I only export filtered data?

    Thanks

    Reply

  9. Okay, this is how I accomplished this –

    I was able to loop through the datagrid and select all the indices. In DataGridUtils.as, I changed the copyData function call to only export_selected. This was easy but I saw export_visible and thought there must be a way to export only visible rows.

    Reply

  10. Do you know why this works fine as long as I put the datagrid inside a mx panel, but as soon as I put it inside a spark panel, the custom context menu no longer gets displayed.

    Reply

  11. Hillel-

    Your site is fantastic – and the online demo is really great! – Unfortunately, I’m having a bit of an issue when I try to implement the Copy/Paste DataGrid on my server:

    The Copy/Paste functionality only seems to be working from the left panel to the right, and the browser initiates immediately (i.e. – no field mapper functionality). After I identify a CSV to upload, the “Import Data” “LOADING” pop-up just spins indefinitely. I tried compiling on another machine with the same result. Seems like I’m missing something very basic here…

    I’m an honest newbie to Flex and have only done limited PHP work, so any help you can provide would be greatly appreciated. Many thanks in advance!

    Here’s a link so you can see what I see:

    http://173.54.39.104/FlashUpload/tmp/index.swf

    Reply

    1. Josh,

      It looks like you’re using Flex 4, the code was written for Flex 3 (my guess is that’s the root of the problem). The file upload trick (with the PHP script) should actually no longer be necessary as that’s now possible using just Flex (with the version 4 SDK). The copy/paste should still work though. I don’t have time right now to debug it but will try to look into it over the next couple of days.

      Best,
      Hillel

      Reply

      1. Hillel-

        Thanks for the quick response! You are correct – I am using Flex 4. I will try it with Flex 3 to see if that fixes the issue and will report back.

        Josh

      3. I’ve managed to get it to work using the Flex 4 SDK.

        In index.mxml, move the “fieldMapper.data = data;” line in the showFieldMapper function after “PopUpManager.centerPopUp( fieldMapper );”.

  15. Hillel,

    I’ve been using your CopyPasteDataGrid for a while now, and I love it! I’m only using the Copy Item(s) functionality, and then I paste them into Excel. I just noticed for the first time that when the data is pasted into Excel, the first record from my Flex datagrid is last and the last record is first. The demo does this as well. I want this to be more WYSIWYG, but I’m not sure what I would have to change or if it’s even possible. Any insight?

    Reply

  16. Thanks, Hillel,

    Not sure if this is what you meant, but changing the line to
    “for each (var item:Object in data.reverse())” in the copyData function worked.

    Thanks for the prompt reply!

    Reply

  19. Hillel, this does not work with Spark datagrid. Can you let me know what code changes i need to make to get it to work? Thanks!

    Reply

Leave a comment