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 5 and 6 and type the answer here:
  • Post
  • Wonderful post, Sheel!

  • Can't get it working. Clicking on the button displays error: Dialogs must be user-initiated. Any ideea why?

    LightSwitch application is developed with Forms authentication and run in browser.

  • The importer will only work in a LightSwitch desktop application.  Silverlight has some security restrictions it places on web applications.

  • It is working in developing but it doesn't work after I publish it in desktop application and give me error "Error: Could not open this file.  It may not be a valid Excel document." but I changed the code to get the real exception and the exception is "Exception from HRESULT: 0x800A03EC\nMicrosoft Excel cannot access the file" but the is in my document folder and the same file I imported it in developing

  • If you try to import an Excel file that does not reside in the "My Documents" folder, it will throw a Silverlight security error and it wont import.

  • Without showing the content of the Excel sheets it's quite strange. Will i have to addt the Category name in the Productes Excel sheet or the name of the category. I tried with my own data - failed. Simply exported some of my existing data to Excel using the LS Excel Export then changed the string values - failed. :-(

  • Show-stopper problem with importing Excel file in Documents directory.  Several tables did import successfully, but now get a dialog "Server Busy" with instructions to Switch to... or Retry.  No way to get out of this without Windows Task Manager stopping the process.  Examining the excel file indicates it is locked for editing.  Saving with clean cut and paste to a new excel file with a new name and the scenario repeats it self.  All this with a 2+ hour cycle time of importing the file (Please wait) while it processes the rows (7K) before this fail.

    Hope this works better in the first production release of Lightswitch.

  • @Kenneth

    I'm not sure what is going on.  We use the Excel COM APIs to grab data from the file.  This can get pretty slow for large files.  Can you try split the file up into smaller chunks and see if it works?

    @HJRP

    Can you provide some details on what is failing?

  • @Kenneth

    I've done a little more investigation.  There are a couple of things that slow it down.  We iterate over all the values from the Excel document to create an array that we use.  This can get pretty slow for large files.

    If you have any relationships that you're trying to import (ex. the Category for each Product), we also hit the server to validate these.  This can get pretty slow.  I've modified the importer to cache these results.  

    Where do you receive the server busy error, before or after the importer displays a dialog that lets you map columns between your document and the table?

    Thanks!

  • Hi there,

    I'm building a LS solution using SQLServer 2008 R2, and using uniqueidentifiers (GUID) for my tables. I was testing this excel importer and it does not work using when tables use GUID, is this correct?

    IDtype:

    ID --> GUID

    Name --> NVARCHAR

    Patient

    ID --> GUID

    Name --> NVARCHAR

    IDType --> IDType.

    Is this a bug that will be fixed with next week release? Thanks!

  • @juan616

    What error are you receiving?  Is it not able to set the IDType for each patient?  Are the GUIDs generated by the database?

  • Nice, but how can we change delimiters? and manage localized datetime fields?

  • I have problem importing large excel files. any how i can work around this one?

  • Anyone can translate the VB Code in an C# Code for Example:

    LightSwitchUtilities.Client.ImportFromExcel(Me.Categories)

    Thank you!

  • partial void ImportFromExcel_Execute()

       {

         // Write your code here.

         LightSwitchUtilities.Client.ExcelImporter.ImportFromExcel(this.Facilities);

       }

Page 1 of 4 (48 items) 1234