Copy/Paste from Excel to WebDataGrid

Alex Kartavov / Tuesday, February 09, 2010

Many developers have been enjoying a nice feature of copying/pasting to/from Excel and UltraWebGrid.

This feature is not yet available in WebDataGrid, but with a few lines of code we can make it happen even now. This blog is about copying from Excel to WebDataGrid. Another one will follow shortly that shows how to copy from WebDataGrid and paste into Excel.

Prerequisites for successful pasting into the grid: it needs the Activation behavior and CellEditing behavior to update underlying data source with pasted data. Aspx for the grid may look something like following:

 

<ig:WebDataGrid ID="WebDataGrid1" runat="server" Height="350px" Width="400px"

AutoGenerateColumns="False" DataKeyFields="ID">

    <ClientEvents KeyUp="keyUp" />

    <Behaviors>

            <ig:Selection CellClickAction="Cell" CellSelectType="Single">

            </ig:Selection>

            <ig:Activation>

            </ig:Activation>

            <ig:EditingCore>

                  <Behaviors>

                        <ig:CellEditing>

                        </ig:CellEditing>

                  </Behaviors>

            </ig:EditingCore>

    </Behaviors>

   

    <Columns>

            <ig:BoundDataField Key="ID" DataFieldName="ID">

                  <Header Text="ID" />

            </ig:BoundDataField>

            <ig:BoundDataField Key="Name" DataFieldName="Name">

                  <Header Text="Name" />

            </ig:BoundDataField>

            <ig:BoundDataField Key="Age" DataFieldName="Age">

                  <Header Text="Age" />

            </ig:BoundDataField>

    </Columns>

</ig:WebDataGrid>

 

 

 

 

Now it would be nice to open an Excel spreadsheet and copy some data from there into our good looking grid. Sure, why not!

Note the KeyUp event that is handled off the ClientEvents of the grid. We need to write some logic there to handle pasting of data from Excel.

function keyUp(grid, args)

{

      var evt = args.get_browserEvent();

      if (evt.ctrlKey && evt.keyCode == 86) // Ctrl-V

      {

            var activeCell = grid.get_behaviors().get_activation().get_activeCell();

            var clipboardText = getClipboardData();

            if (activeCell && clipboardText)

            {

                  // Excel stores its data in text clipboard separated

                  // by \n between rows and by \t between cells

                  var pasteData = clipboardText.split("\n");

                  for (var i = 0; i < pasteData.length; i++)

                        pasteData[i] = pasteData[i].split("\t");

 

                  // Last row may be a dummy row

                  if (pasteData[pasteData.length - 1].length == 1 && pasteData[pasteData.length - 1][0] == "")

                        delete pasteData[pasteData.length - 1];

 

                  var rowDataIndex = 0;

                  var rowIndex = activeCell.get_row().get_index();

 

                  // Let's paste until the end of our clipboard data or until the end of the grid rows

                  while (rowDataIndex < pasteData.length && rowIndex < grid.get_rows().get_length())

                  {

                        var row = grid.get_rows().get_row(rowIndex++);

 

                        var cellDataIndex = 0;

                        var cellData = pasteData[rowDataIndex++];

                        var cellIndex = activeCell.get_index();

                       

                        //Let's paste until the end of the clipboard data or until the end of the cells

                        while (cellData && cellDataIndex < cellData.length && cellIndex < row.get_cellCount())

                        {

                              var text = cellData[cellDataIndex++];

                              if (text === null)

                                    break;

                              var cell = row.get_cell(cellIndex++);

                              cell.set_value(text);

                        }

                  }

            }

      }

}

 

 

The KeyUp event handler contains logic that parses clipboard content and puts it cell by cell into grid's cells. Another interesting code, I would like to draw your attention to, is how the clipboard data is retrieved. In the KeyUp event handler there is a call to a method that simply returns the clipboard data. Here how it's retrieved for different browsers:

 

var _browserPasteData = null;

function getClipboardData()

{

      if (_browserPasteData) // Safari/Chrome logic

            return _browserPasteData;

           

      if (window.clipboardData) // IE logic

      {

            return window.clipboardData.getData("Text");

      }

      else if (typeof(netscape) != "undefined") // Firefox logic

      {

            netscape.security.PrivilegeManager.enablePrivilege('UniversalXPConnect');

            var clip = Components.classes["@mozilla.org/widget/clipboard;1"].createInstance(Components.interfaces.nsIClipboard);

            var trans = Components.classes["@mozilla.org/widget/transferable;1"].createInstance(Components.interfaces.nsITransferable);

            trans.addDataFlavor("text/unicode");

            clip.getData(trans, clip.kGlobalClipboard);

            var str = new Object();

            var len = new Object();

            trans.getTransferData("text/unicode", str, len);

            if (str)

                  return str.value.QueryInterface(Components.interfaces.nsISupportsString).toString();

      }

      return null;

}

 

// In Safari/Chrome the clipboard data can only be accessed

// from the onpaste event. In this sample the event is handled

// off the body element: <body onpaste="browserPaste(event)">

function browserPaste(e)

{

      _browserPasteData = e.clipboardData && e.clipboardData.getData ?

            e.clipboardData.getData('text/plain') : null;

 

}

So as you can see with a few lines of code the clipboard data can be successfully retrieved and placed into WebDataGrid. CellEditing will take care of storing every cell's value and once a post back occurs those values will find their way into the data source without any involvement from the developer! Check out the attached sample that contains all of the code, and let me know what you think!

CopyPasteExcel.zip