Press "Enter" to skip to content

Copy/pasting from Excel into the Smartforms List View

4

export-to-excel

There are a lot of requirements where customers would like to transfer data from an Excel sheet into a K2 List View. Moreover, most of the requirements emphasize the necessity of copying multiple rows. In this post I will show you how this can be achieved with almost no coding. After the items are transferred to the List View, they can be saved into the underlying Data Source with the help of Smartform rules.

1. Define the structure of your table.

It does not matter a lot if you are trying to use my approach or writing a fully functional import, defining a structure of the imported data will be among the first tasks you need to carry out. For the demo purposes, I selected a simple table I need to paste into the List View, which consists of the following columns:

  • FQN – Text (String)
  • Display Name – Text (String)
  • Active – Yes/No (Boolean)

E.g.:

image

2. Create a tiny .NET assembly for deserialization

When you try to copy and paste the data from the Excel table into a TextArea control or even the notepad, you will see the following result:

K2:DENALLIXAdministrator    Denallix Administrator    TRUE
K2:DENALLIXAndrew    Andrew Murphy    FALSE
K2:DENALLIXAnthony    Anthony Petro    TRUE

In order to be able to deserialize the above-mentioned text and convert it to an items list, you can use the following piece of code for deserialization:

public static class Deserializer
{
  public static List<Item> ParseExcelData (string table)
  {
    var items = new List<Item>();
    //splitting, looking for a new line symbol
    var rows = table.Split(new [] {“rn”, “n”}, StringSplitOptions.RemoveEmptyEntries);
    foreach (var row in rows)
    {
      var item = new Item();
      //splitting, looking for a Tab symbol
      var columns = row.Split(new [] {“t”}, StringSplitOptions.None);
      item.FQN = columns[0];
      item.DisplayName = columns[1];
      bool active;
      Boolean.TryParse(columns[2], out active);
      item.Active = active;
      items.Add(item);
    }
    return items;
  }
  public class Item
  {
    public string FQN { get; set; }
    public string DisplayName { get; set; }
    public bool Active { get; set; }
  }
}

After you compile the above-mentioned code and create the Service Instance with the help of the Endpoint Assembly Service, you will have the following ServiceObject:

Capture

3. Create/Update a SmartObject and use it on your List View.

For the demo purposes, I created a SmartObject, based on the SQL Server ServiceInstance. This SmartObject and the underlying table share the same structure:

CaptureNew

Then you need to create a list view, adding a TextArea control to the header of the view:

image

Upon the ‘Parse Excel Data’ button click you need to execute the following rule:

image

When you click the ‘Parse Excel Button’, your List View will be populated with the copied data:

image

Playing around with Smartforms rules, you can also upload the inserted data into the underlying Data Source. For this, you need to create a rule like this for the Save to DB button:

image

Making the list editable and adding other rules (Edit, Delete etc.), you can allow users to process the data before uploading them to the Data Source.

To summarize the described approach, I would like to point out certain Pros and Cons.

Pros:

  • Relatively codeless approach – you have to write a short assembly;
  • Quick to implement;
  • Easy to understand;

Cons:

  • Subject to errors – user’s input is difficult to validate;
  • Might be complex to process a large amount of data;
  • Necessity to maintain the same structure (columns order) in Excel and Custom assembly.
  1. Hi, I have tried it on my system and seems to work wonderfully, Thank a lot.

    I used the list method to populate the view with the data loaded into the text area, if I want to add another chunk of excel data to the list how can I do that? For now if I put in new excel data it would just replace the current one.

  2. So I would say, you need to store it somewhere. For example, you can trigger the SMO method so that every added item is saved into SMO. And then you will populate it with a bit of new portion of items.

  3. Thank you for your reply.

    I used 2 list views, one for storing the newly deserialised array (temporary) and other one for storing all the array (main). When the function is called, it populate temporary list then copy the array over to the main list view.

    Thank you again for wring the blog, it is very helpful 🙂

  4. It will be nice if you can support the upload excel instead of copy and paste content

Leave a Reply

Your email address will not be published. Required fields are marked *

17 + six =