How to Import Data from Excel

How to Import Data from Excel

Rate This
  • Comments 48

One of the more common requests from our customers is the ability to import data into LightSwitch applications. While this can be done programmatically, it’s pretty common to store data in Excel documents. This tutorial will walk through using an Excel Import extension that our team has built.

Download the Excel Import Extension

Functionality

The Excel Import extension will allow you to add “Import from Excel” functionality to any existing LightSwitch screen. End users of the LightSwitch application can then import their business data when necessary. The extension will allow users to map columns of their Excel document to fields on LightSwitch tables. It will then validate the data and create records in the application.

Getting Setup

First we’ll create a couple of Excel documents that we will import data from. Create an Excel document titled Categories that contains the following information. Ensure that the document is saved in the Documents folder. Silverlight has limitations on what it can access on the file system.

 

Name Description
Beverages Soda and juice
Condiments Sauces, relishes, spreads, and seasonings
Dairy Products Prepared and fresh meats
Grains and Cereals Breads, cereals, crackers and pasta
Produce Dried and fresh fruit and vegetables
Seafood Seaweed and fish

Create another Excel document titled Products that contains the following information. Again, ensure that the document is saved in the Documents directory.

Name

Quantity

Category

Sasquatch Ale

10

Beverages

Aniseed Syrup

15

Condiments

Steeleye Stout

8

Beverages

Spiced Okra

24

Produce

Carpet Clean Plus

6

Cleaners

Scottish Longbreads

None

Grains and Cereals

Boston Crab Meat

9

Seafood

Install the Extension

Download the attached extension on the machine that has LightSwitch installed. Open the zip file, navigate to the Binaries folder and double click on the ExcelImporter.vsix file. This should install the extension for LightSwitch.

image

Create a new LightSwitch application. Double click on the Properties node in the application explorer. In the application properties, navigate to the Extensions tab. Enable the LightSwitch utilities extension for this application. If you like, you can default it to enabled for all future applications.

image

Define the Application Data

Add a Category table with the following fields:

  • Name (String, Required)
  • Description (String, Not Required)

image

Add a Product table with the following fields:

  • Name (String, Required)
  • Quantity (Integer, Required)

image

Now click on the “+ Relationship” button in the command bar to open the “Add New Relationship” dialog. We will set a relationship between Product and Category. In our case, each Category has many Products and each Product must have a Category.

image

Created our Screens

Now that we have our data setup, let’s add screens to edit our Categories and Products. Add a new Editable Grid screen for Categories.

image

On this screen, let’s add a button to import data from Excel. Right click on the screen command bar and select “Add Button…” Name the method ImportFromExcel and click Ok.

image

image

Double click on the Import From Excel button and add the following code.

Private Sub ImportFromExcel_Execute()

   ' Write your code here.

   ExcelImporter.Importer.ImportFromExcel(Me.Categories)

End Sub

Similarly, add a screen for editing our Products. Add a new Editable Grid screen for Products.

image

Add a similar ImportFromExcel button to this screen with the following code.

Private Sub ImportFromExcel_Execute()

   ' Write your code here.

   ExcelImporter.Importer.ImportFromExcel(Me.Products)

End Sub

Testing our Application

Build and run the application. The “Editable Categories Grid” screen should appear. Click on the “Import From Excel” button. This should open a dialog allowing you to select the file you wish to import from. Select the Categories excel document and click “Ok”.

 

image

The extension should now open a mapping dialog. This will allow you to map columns in the Excel document to fields on your LightSwitch table. The dialog will try to match the names of Excel columns and LightSwitch fields. Click on “Continue”.

image

The extension will now add records to the LightSwitch grid for each row in the Excel document. If necessary, you can edit the data prior to saving it. Save and close this screen.

image

Now open the “Editable Products Grid” screen. Click on the “Import From Excel” button and select the Products excel document.

image

The mapping dialog will again match up the Excel column names and the LightSwitch field names. In this case, the Category field is selected, even though this represents a relationship. The extension will attempt to look up matching categories in the Category table when importing the data from this document. Click on “Continue”.

image

The extension will validate any data prior to importing it into LightSwitch. Any errors will be displayed back to the user. In this case, a category called “Cleaners” was specified for one of the products. This category couldn’t be found. Additionally, “None” was specified as a quantity for one of the products. This can’t be represented as an integer. Click on “Continue” to correct any data errors in your LightSwitch screen.

image

In your LightSwitch screen, change the category of the Carpet Clean Plus product to allow it to be saved.  You can also modify the quantity of Scottish Longbreads if necessary. Save the screen when you’re done fixing up the data.

image

The attachment includes both the source code for the extension and an installable VSIX package. Building the source will require Visual Studio Professional and the Visual Studio SDK to be installed.  This extension isn’t intended to be production quality (it won’t work when used from a Web application, for example). 

To get started building your own extensions, check out the Developer Center.

Leave a Comment
  • Please add 1 and 6 and type the answer here:
  • Post
  • This is a great extension for Lightswitch Silverlight. Unfortunately I haven't been able to figure out how to use this with the LightSwitch's HTMLClient. I did read up somewhere that for HTML we could import from CSV- do you have any sample code for that?

  • I create the button and run it, but then it doesnt open a mapping dialog,

    it brings up this error message: Object reference not set to an instance of an object.

  • Lightswicht Html Öğrenmem lazım Lütfen Yardım Teşekkürler

Page 4 of 4 (48 items) 1234