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 2 and 3 and type the answer here:
  • Post
  • thanks for you answer Beth, but is there no way to print directly from the lightswitch screen?

  • @aliasale - Check out code I posted last August near the bottom of thread.

    social.msdn.microsoft.com/.../a09206aa-ab11-4b29-a725-186211b29a64

    If you're prepared to accept that the user launches Excel and navigates to the desired sheet that they wish to load (which seems sensible!) you might just find the code to mod' Sheel's vsix, useful. I've been using this method live for nearly one year, it does enable Excel to be left running, and also bypasses the Silverlight "My Documents" security restriction!

    I "get" the current Active workbook and sheet from Excel (attaching to the process via COM) regardless how many workbooks you have open and use this to select what data I import. The V1 vsix will convert when you migate to V2beta! I have yet to update Sheel/The Team's latest code but I'm sure the concept still holds.

    Keith

  • Hello,

            I had test import excel file using ExcelImporter extention in light switch with existing table and it work proper but when used sql server database and applied same code it gives error shown in below,

    "cannot convert from 'Microsoft.LightSwitch.Framework.EntitySet<LightSwitchApplication.Product>' to 'Microsoft.LightSwitch.Client.IVisualCollection'"

    "The best overloaded method match for 'ExcelImporter.Importer.ImportFromExcel(Microsoft.LightSwitch.Client.IVisualCollection)' has some invalid arguments"

    Internal Tables:

    Private Sub ImportFromExcel_Execute()

      ' Write your code here.

      ExcelImporter.Importer.ImportFromExcel(Me.Products)

    End Sub

    Sql server external database syntax is:

    Private Sub ImportFromExcel_Execute()

      ' Write your code here.

      ExcelImporter.Importer.ImportFromExcel(DataWorkspace.LightSwitchDBData.Products)

    End Sub

    Please help me.

  • Hi hardikramwani,

    I saw your post in the forum and ansered you there. Please see this thread:

    social.msdn.microsoft.com/.../775a9415-670e-4b83-b47a-1a8ae7fd3a28

    HTH,

    -Beth

  • Hello,

    Is there a veriation of Excel Importer for ListDetailScreen?

    best,

  • Good to see it still works in 2012 RC, so just wanted to say thanks.

  • Hello and thank you for the wonderfull post! I have a question thought: Can i use Excel Importer to import in anyway pictures in a given table?

    For example i have the following table:

    Product - string

    ProductImage - Image

    ProductDiscriptio  - String

    ProductCategory - String

    Price - Money

    I did i excel with al the data above and i`m curious if i can use a link for example to import the image directly in the table.

    Ty in advance

  • if i put one messagebox after the

    ' Write your code here.

      ExcelImporter.Importer.ImportFromExcel(Me.Products)

    this line this.showmessagebox();

    it shows message while showing the box to map the excel columns with collections columns?

    so how avoid it???means how to knw tht it got completed its importing and how it maps?

  • The data I'm importing has a " as an indicator of inches.  When the importer sees the " it physically changes the imported data field to enclose it in "'s.  Any recommendations to preventing this?

  • Two issues :

    1) If I save my Excel data in CSV, Excel 10 inserts semicolons, not commas, between fields. This is not recognized by ExcelImporter as a field separator, so mapping does not work. Could we at least have an option ?

    2) If you use "foreign" caracters sets (I use French) all the "special" caracters, very common for us (as accentuated) are turned to garbage. Could we have a Unicode version, which will solve it for everybody ?

    Nt solving those issues makes the extension unusable for me (and some others I guess).

    Thanks in advance.

    Georges

  • Hi guys,

    You should check out the Office Integration Pack. It's a free extension and has A LOT more options for importing Excel data. Check it out here: officeintegration.codeplex.com/documentation

    HTH,

    -Beth

  • Does the office integration pack support lightswitch web applications?

    Also was trying to get even the ExcelImporter to compile up successfully on VS2012, anyone had luck on that without major editing of the projects?

  • Please Web Support, it currently only uses csv with semi coma, not excel and the source code is too old and gives me several erros migrating...

    PLEASE MICROSOFT HELP ME.....

  • I'm trying to import Excel data into a SQL Server table that has a calculated field.  The importer throws an error because the field isn't in the import.  how do I get around this?

  • Any update to what Jerry suggested a couple years ago?  I have the same situation. Id like to import from Excel but if the entry exists (based on a unique data), have it overwrite/update this information rather than add duplicates.  

    Is there a way to do this?

Page 3 of 4 (48 items) 1234