When you buy this book you support this site! - Thank You for your support!

Jul 14

Written by: kchristo
Thursday, July 14, 2011  RssIcon

Immediately after using Excel Importer extension for the first time and trying to see how this would fit into our new “want to be” business application framework, I realized that defining the column mappings every time was definitely an issue.

So I decided to implement and integrate to the original Excel Importer a mechanism for using Column Mapping Templates as an optional capability. I decided to give it back to the LightSwitch community, where the original idea (msdn.com Lightswitch Samples) came from. You can download the the extension here. Feel free, no better yet, feel obliged Winking smile,  to rate it after you evaluate it. The sample application I am presenting here can be downloaded from here or from the link provided in the related forum post.

After downloading the extension and the sample application you should be ready to run the application. Be sure to note the P.S.1 in the above forum post.
Build the extension and then go to bin\Debug folder (Be careful! in the original LightSwitchUtilities extension the package was found in the binaries folder, it still is but it is the original package, left it there to be easy to convert back easily) of the vsix project and install the extension. Build and run the sample application.

In case you haven’t read the forum post, which I strongly suggest you do, a small briefing:

It’s a very naïve application with one business entity named Size. My company Computer Life is specialized in software for the apparel industry, so Size entity is something we vastly use. The other two entities, that one can easily notice in the one and only datasource found in the application, are implemented as such (entities) for simplicity. My implementation of IColumnProperty, IColumnPropertyTemplate and IColumnPropertyTemplateProvider are based on these entities. Copied from the related forum post:

01.public bool Save(LightSwitchUtilities.Client.Contracts.IColumnPropertyTemplate template, ref stringerrorMessage) {
02.  if (templates.Where(t => t.EntityTypeName.Equals(template.EntityTypeName) && t.TemplateName.Equals(template.TemplateName)).Count() > 0) {
03.    errorMessage = "Template already exists";
04.    return false;
05.  }
06.  try {
07.    templates.Screen.Details.Dispatcher.BeginInvoke(() => {
08.      using (DataWorkspace dw = LightSwitchApplication.Application.Current.CreateDataWorkspace()) {
09.        Template templateNew = dw.ApplicationData.Templates.AddNew();
10.        templateNew.TemplateName = template.TemplateName;
11.        templateNew.EntityTypeName = template.EntityTypeName;
12.        foreach (LightSwitchUtilities.Client.Contracts.IColumnProperty columnProperty intemplate.Mappings) {
13.          TemplateItem templateItem = dw.ApplicationData.TemplateItems.AddNew();
14.          templateItem.ColumnName = columnProperty.ColumnName;
15.          templateItem.PropertyName = columnProperty.PropertyName;
16.          templateItem.Template = templateNew;
17.          templateNew.TemplateItems.Add(templateItem);
18.        }
19.        dw.ApplicationData.SaveChanges();
20.      }
21.    });
22.    return true;
23.  }    
24.  catch(Exception ex) {
25.    errorMessage = ex.Message;
26.    return false;
27.  }
28.}

This one is the Save implementation found in BaseTemplateProvider. This class is actually a proxy between our Template and TemplateItem entities and IColumnPropertyTemplate and IColumnProperty respectively.(I say again this was implemented this way for convenience, could be implemented any other way, with xml files for example, another RIA DataSource, whatever).

Now for the application itself. Let’s run the application.

Capture

Nothing much, I know. But it’s actually more than meets the eye. See the highlighted button “Import From Excel”? It all starts there. This is what code is executed when you press the button:

private LightSwitchUtilities.Client.Contracts.IColumnPropertyTemplateProvider provider = null;
partial void ImportFromExcel_Execute() {
      // Write your code here.
      provider = new BaseTemplateProvider(Templates);
      //Import with template support
      LightSwitchUtilities.Client.ExcelImporter.ImportFromExcel(Sizes, provider);
      //Import without template support
      //LightSwitchUtilities.Client.ExcelImporter.ImportFromExcel(Sizes);
}

A BaseTemplateProvider as described above is created and passed to ExcelImporter’s ImportFromExcel method. An Open File Dialog pop’s up and you have to choose the excel file to import.

Capture1

You can find a sample file used for testing and running this sample here.

Selecting the file this dialog comes up:

image

If you have used the original Excel Importer you could can easily notice the extra UI from the label “Select Template” and down.

The excel file used as sample is rather ideal as it has an one-to-one matching-name mapping between columns and Size entity properties. That’s why using either version of the Excel Importer (no functionality of the previous version is lost or somehow modified) the mappings are automatically filled as you can notice. Of course this is far from being a common scenario. This is where templates come in.

Now let’s say that we only want to import the name of the size and not the Id (I cannot imagine many scenarios that you would import a primary key from an excel file) or the Comments.

We change the mappings to look like this.

Capture3

Notice we have selected to ignore ID and Comments column of the Excel. Nothing new up to now. Typical Excel Importer functionality. The new comes in now. I want to save these mappings for reuse when this format of excel file comes in again.

Capture4

I check the Save Template check box and give a name to the template. Also notice the perfect ellipse shape drawn completely by hand.

We press continue…

If everything goes as planned this is should come up…

Capture5

…informing us the template was saved.

Our screen now should look like this.

Capture6

Notice that the sizes imported have not been saved yet (highlighted). Close the window selecting OK to the dialog asking you if you want to discard changes.

Running the application again the first empty screen should appear. Press the Import from excel button once more. The open file dialog comes up, you select the file and the column mapping dialog comes up and everything is the same as before. Not quite. Open the combo box under the “Select Template” label. You will see your template. Select it.

Capture7

This is what you should be seeing now. The column mappings have automatically been set. The 33.3% you can see in front of your template name is the score calculated automatically from the default score evaluator included in the Excel Importer with support for templates. The complex scoring mechanism used is very hard to explain Smile with tongue out. Any way, seriously now, as explained in the forum post and in the documentation posted along with extension, you can optionally implement an IColumnPropertyTemplateScoreEvaluator and pass it to the excel importer along with your implementation of IColumnPropertyTemplateProvider to have your scoring for templates. The only restriction is scoring with a value between 0-1 with 1 being the best match. If you want to change this behavior you can change ScoredTemplate implementation found in the extension code. You should change the ScorePct property implementation and the CompareTo function implementation, according to your needs.

Key Notes:

  1. In this sample implementation if a template with the same name and entity type name already exists, the BaseTemplateProvider’s Save method will return an error. You can do what ever you like with your implementation. Allow duplicates, update the existing, delete the old one and create a new one, whatever.
  2. In the Save method of the BaseTemplateProvider you can easily notice a new DataWorkspace is used to save the template. The reason for this is that when the user completes the import and asks for the template to be saved, it must be saved without the user having to explicitly save the changes. He might want to cancel the changes but save the template (what we did in this walkthrough). Implementing templates the way I did in this sample app (as entities that is) this is the way to achieve the described behavior. Also, this is optional. Maybe you want templates to be saved along with your data and not before. You should have no problem with implementing that as it’s completely “out” of the importer.
  3. Also in the Save method you can notice threading is used to save the templates. If you have read all the related posts you already know this has to do with respecting the way Excel Importer’s threading mechanisms used internally for importing the data.
  4. If you are more “adventurous” developer look for the ImportCompleted event and the related documentation in the related posts.
  5. Last but not least, if you have code using the original version of the Excel Importer as implemented by the LS Team, replacing the original extension with this one would cause to change at all to the behavior of your application. Template related parameters are optional and if omitted (call ImportFromExcel with just the VisualCollection to update) you have exactly the look-feel-function of the original Excel Importer.
Related Resources:
  1. Forum post in Lightswitchhelpwebsite
  2. Sample in msdn.com/lightswitch

About the Author

Picture 003

I am a 40-year old, ever-learning (trying hard) software developer. Not engineer, not architect, not evangelist, not paratrooper, just developer. It's a dirty job but some has to do it and admit he is doing it.
I got a Bachelor in Computer Science from the pioneer in Greece, University of Crete, Heraklion in 1995.
I am an MCSE since 1999. A total of 16 years of professional experience.
My fields of expertise in the last 11 years are mostly CAD/CAM and Telematics/GIS.
I also have long training experience, teaching Microsoft's Technical Seminars for the last 13 years.
Been working with .net since 2002 when in Tech-Ed Europe Barcelona I realized for the first time it was "a brave new world" emerging and I knew I couldn't be left out.

I have been working for Computer Life for the last 4 years.

679 comment(s) so far...


Gravatar

Re: Excel Importer with Template Support

Hi,

Excellent stuff!
do you have the sources in c sharp as well ?

thx
paul.

By paul van bladel on   Wednesday, November 9, 2011
Gravatar

Re: Excel Importer with Template Support

Thanks for the comment. I am glad you like it. I am a C# developer. I had to modify the original extension that was published by LightSwitch Team and it was written in VB, so in order not to have mixed code I had to write in VB. Sorry. It was a challenge but it had to be all in VB.

By kchristo on   Thursday, November 10, 2011
Gravatar

Re: Excel Importer with Template Support

Hi,
Awesome stuff! helped me a lot to implement import from excel functionality in my project. Thanks a lot!

Thanks,
Prashanth

By Prashanth on   Thursday, May 3, 2012
Gravatar

Re: Excel Importer with Template Support

Thank you for your kind words Prashanth. Glad you found the post helpful

By kchristo on   Thursday, May 3, 2012
Gravatar

Re: Excel Importer with Template Support

Wow, that's a thorough material! Thanks

By Planilhas Excel on   Monday, May 7, 2012
Gravatar

Re: Excel Importer with Template Support

You are welcome! Glad to be helpful :-)

By kchristo on   Monday, May 7, 2012
Gravatar

Re: Excel Importer with Template Support

Do you plan to update it to support web server and CSV files?

Thanks!

By Mike on   Friday, July 27, 2012
Gravatar

Re: Excel Importer with Template Support

please share the VISX for vs2012 of Excel Importer with Suport for Templates

By brunobola on   Thursday, April 4, 2013
Gravatar

re:

I’m impressed. Very informative and trustworthy blog does exactly what it sets out to do. I’ll bookmark your weblog for future use.

Pebbles
www.joeydavila.net

By Pebbles on   Wednesday, May 22, 2013
Gravatar

Re: Excel Importer with Template Support

to a Best purchase ANd raise to plug them into a Zune then an iPod and see that one sounds higher to you, and that interface causes you to smile additional. Then you may understand that is true for you. click http://www.ashleyofmanchester.co.uk

By Ashleyofmanchester.co.uk on   Friday, September 20, 2013
Gravatar

Packers and Movers Ranchi

Awesome stuff! helped me a lot to apparatus acceptation from excel functionality in my project. Thanks a lot! click here http://www.anilpackersmovers.com/ranchi.html

By Packers and Movers Ranchi on   Thursday, September 26, 2013
Gravatar

re:

I like your post a lot! You should write some more on this!Great job coming with such terrific post!


mocsbar.com

By mocsbar.com on   Saturday, July 12, 2014
Gravatar

Re: Excel Importer with Template Support

Is there an update that works in Visual Studio 2013?

By Percy on   Monday, August 25, 2014
Gravatar

re:

I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well. .

Drew
www.imarksweb.org

By rachelle on   Thursday, January 22, 2015
Gravatar

Re: Excel Importer with Template Support

Insecurity is one of the aspect that a person would probably fall. They tried to be something they never used before and tried to compete among others. insecurity looses self-confidence and will make a person pathetic or shall we say paranoid but insecurities has always a cure or a solution just try to love oneself and avoid comparing your life , actions and status to others. Try to visit my site #n8fan.net . Thank you.

n8fan.net

www.n8fan.net

By hannah on   Tuesday, March 3, 2015
Gravatar

Re: Excel Importer

Hello i am ussing the ExcelImporter.Importer.ImportFromExcel(this.MyTable);
but i can find how don't ask for the Map Columns, i just want don't show the Map Columns Windows.
Please can helpme.

By Eveli on   Thursday, March 12, 2015
Gravatar

Re: Excel Importer with Template Support

I am developing LIghtSwitch HTML client application, how i can use Excel Importer in HTMl client. Please give some guideline.

By Srikanth on   Friday, March 27, 2015
Gravatar

Re: Excel Importer with Template Support

Lightswitch community is an excellent place for the people who want to learn about this specific subject matter. I have always learned something new and will visit this page again and again in the future. Thanks to all who tirelessly helping out people like me. Visit http://www.shushincalls.co.uk

By Shush Incalls on   Tuesday, July 7, 2015
Gravatar

re:

I really enjoyed reading your article. I found this as an informative and interesting post, so i
think it is very useful and knowledgeable. I would like to thank you for the effort you have made
in writing this article.


edupdf.org

By edupdf.org on   Monday, October 26, 2015
Gravatar

Re: Excel Importer with Template Support

Excel is a big platform and it is needed for very company today for manage their office work and other important data. Your post is really helpful for all of us.

http://www.manchesterincallsescorts.co.uk/.

By Manchester Incalls Escorts on   Wednesday, December 9, 2015
Gravatar

Re: Excel Importer with Template Support

Excel is a big platform and it is needed for very company today for manage their office work and other important data. Your post is really helpful for all of us.

http://www.manchesterincallsescorts.co.uk/.

By Manchester Incalls Escorts on   Wednesday, December 9, 2015
Gravatar

Re: Excel Importer with Template Support

Good day. I was impressed with your article. Keep it up . You can also visit my site if you have time. Thank you and Bless you always.

triciajoy.com

www.triciajoy.com

By andrea on   Wednesday, July 13, 2016
Gravatar

packers and movers bangalore

movers and packers in bangalore @ http://www.shiftingsolutions.in/packers-and-movers-bangalore.html
packers and movers in pune @ http://www.shiftingsolutions.in/packers-and-movers-pune.html

By movers and packers bangalore on   Friday, September 23, 2016
Gravatar

Re: Excel Importer with Template Support

movers and packers in bangalore @ http://www.shiftingsolutions.in/packers-and-movers-bangalore.html
packers and movers in pune @ http://www.shiftingsolutions.in/packers-and-movers-pune.html

By packers and movers pune on   Friday, September 23, 2016
Gravatar

Re: Excel Importer with Template Support

Life is too short to start your day with broken pieces of yesterday, it will definitely destroy your wonderful today and ruin your great tomorrow! Have a great day!
gofastek.com

By Thea on   Tuesday, September 27, 2016
Gravatar

Re: Excel Importer with Template Support

Thank you so much for sharing this tips. i am looking for this tips because i only know to import temple in the WordPad. i do not know how to do this in the excel so my knowledge is doubled to visit here.

By Madona Murfy on   Wednesday, April 26, 2017
Gravatar

Buying A Home - Book Your Condo In M city

Hey there I feel so fortunate to come over your blog you express it in a very easiest way I am very much active person over your blog pretty good to read your blog its quit interesting to read your blog always because it looks fresh all the time.
By Luxurious condos in Mississauga on   Thursday, May 4, 2017

Gravatar

Book your Apartment in 1 Yonge Street


Very interesting article with great useful information. I am also sharing this article with my friends you have written excellent content. Thanks for sharing this kind of informative blog.

By 1 Yonge St on   Friday, May 5, 2017
Gravatar

Charisma Condos Vaughan

I want to thank you for ones time due to this fantastic read! Always has really good posts and topics please keep it up.

By Charisma Condos on   Friday, May 5, 2017
Gravatar

Re: Excel Importer with Template Support

I don't know who you are. Don't even know your name I wish we could talk but I don't have a number to call. So hold your hand up if you hear me, I've been searching but all that I found Is everywhere that I go, is standing alone in the crowd Maybe you're right here in front of me Am I looking too hard, it's hard to see Oh, give me a sign; I'm starting to wonder if you've lost your way I've been right here waiting patiently, Your lane should be right here next to me I need you tonight, think of you all of the time. thank you

By super smash flash 2 on   Saturday, June 24, 2017
Gravatar

Re: Excel Importer with Template Support

I've been sleepless at night. 'Cause I don't know how I feel. I've been waiting on you
Just to say something real.There's a light on the road. And I think you know. Morning is coming. And I have to go.I don't know why, I don't know why. We need to break so hard. I don't know why we break so hard But if we're strong enough
To let it in. We strong enough. To let it go.

By bloons tower defense 5 on   Saturday, June 24, 2017
Gravatar

Build Your Brand With Professional Bulk SEO Services - Jacksonville SEO Service

Well I would like to congratulate to you amazing writing skills as a regular member of your blog I wanted to say this, it’s really informative blog good work!

By SEO Companies Jacksonville on   Monday, July 3, 2017
Gravatar

Re: Excel Importer with Template Support

very nice information you share with us. thanks for th post

By Goibibo Customer Care Number on   Tuesday, August 1, 2017
Gravatar

Re: Excel Importer with Template Support

Very good, I think I found the knowledge I needed. I will see and refer some information in your post. thank you.

By html color picker on   Tuesday, September 26, 2017
Gravatar

Re: Excel Importer with Template Support

How long does it take to complete this article? I have read through other blogs, but they are cumbersome and confusing. I hope you continue to have such quality articles to share with everyone!

By io games on   Friday, September 29, 2017
Gravatar

Re: Excel Importer with Template Support

If you are facing any problem regarding Ms Office then we are here to help you. We provide full assistance regarding MS Office. Visit us to know more .

By ms office product keys support on   Monday, October 16, 2017
Gravatar

Re: Excel Importer with Template Support

The above shared article is really very good, it helped me a lot. Thanks for sharing.

By Dell Customer Support on   Wednesday, October 25, 2017
Gravatar

Re: Excel Importer with Template Support

This is really an interesting blog I love it and enjoyed it so much keep sharing like this. Windows tech support

By Windows tech support on   Wednesday, October 25, 2017
Gravatar

Re: Excel Importer with Template Support

Great work! Totally motivating and inspiring.Keep sharing such more in future also.Thank you so much.

By Acer Support on   Wednesday, October 25, 2017
Gravatar

Re: Excel Importer with Template Support

Thankful to you for interfacing with us to answer this site page. Ideally, this site page switches up certainly more world class and clear for others.

By ludhiana Escorts Service on   Thursday, October 26, 2017
Gravatar

Re: Excel Importer with Template Support

These services are provided by Lenovo to run their products efficiently.

By Toshiba support on   Thursday, October 26, 2017
Gravatar

Re: Excel Importer with Template Support

quite helpful and handy when it comes to productivity!

By iPhone Support on   Thursday, October 26, 2017
Gravatar

Re: Excel Importer with Template Support

nice post I enjoyed it a lot keep sharing like this

By epson support on   Thursday, October 26, 2017
Gravatar

Get best Ayurvedic Medicines For Kidney Damage

Hello, I am the first time here; this is very nice and gives in-depth information. I will definitely come back to more times in this year for submitting more post. Keep trying to make best.Great Thanks!

By Get best Ayurvedic Medicines For Kidney Damage on   Friday, October 27, 2017
Gravatar

Re: Excel Importer with Template Support

Very nice post.
Mcafee Support Number

By Mcafee Phone Number on   Monday, October 30, 2017
Gravatar

Re: Excel Importer with Template Support


Your blog posts are more interesting and impressive. I think there are many people like and visit it regularly, including me. I actually appreciate your own position and I will be sure to come back here.

By Yahoo Customer Service on   Tuesday, October 31, 2017
Gravatar

Re: Excel Importer with Template Support

amazing post

By hp customer support number on   Wednesday, November 1, 2017
Gravatar

<a href="http://windowstechsupport.webnode.com/ "> windows tech support</a>

Thank you for posting the great content. I was looking for something like this. I found it quiet interesting, hopefully you will keep posting such blogs. windows tech support

By Windows tech support on   Thursday, November 2, 2017
Gravatar

Re: Excel Importer with Template Support

really great post

By outlook support on   Thursday, November 2, 2017
Gravatar

Re: Excel Importer with Template Support

Nice blog post. Looking forward to sharing the new series with my potential brides.

By Chrome Customer Care on   Friday, November 3, 2017
Gravatar

Re: Excel Importer with Template Support

Thanks for sharing a nice post!!! https://firefoxsupport.site123.me/

By Firefox Tech Support on   Friday, November 3, 2017
Gravatar

Re: Excel Importer with Template Support

Thanks for sharing this blog with us. This is really very knowledgeable.

By mark on   Tuesday, November 7, 2017
Gravatar

Re: Excel Importer with Template Support

Thanks for sharing this. Its very helpful blog for ASP.Net Programmers.

By Mark Davis on   Tuesday, November 7, 2017
Gravatar

Re: Excel Importer with Template Support

lovely post

By outlook support on   Wednesday, November 8, 2017
Gravatar

Re: Excel Importer with Template Support

wow nice blog. Great stuff, the way you describe the thing is really awesome. keep sharing

By yash on   Thursday, November 9, 2017
Gravatar

Re: Excel Importer with Template Support

great post so thanks for this because this is the amazing.

By Samsung Printer Contact Number on   Saturday, November 11, 2017
Gravatar

Re: Excel Importer with Template Support


I am very much pleased with the contents you have mentioned. I wanted to thank you for this great article.

By Hifi Escorts Services in Udaipur on   Thursday, November 16, 2017
Gravatar

Re: Excel Importer with Template Support

I just want to say thanks for your wonderful post, it contains a lot of knowledge and information that I needed right now.

By Hot and sexy girls in Delhi on   Thursday, November 16, 2017
Gravatar

compaq laptops and computers repair

THE POST WAS QUITE INTERESTING!!!!!!

By compaq laptops and computers r