Quick tip: LS Excel importer speed improvements

The LightSwitch desktop client has a great extension that lets you load an excel file, map the columns to your LightSwitch entities, then import the data in your application.

The source code is available and provides a great start to build an excel importer that fits your needs specifically.  Besides a couple of functional shortcomings (find the line ‘//TODO multiple worksheets’), it has one really big technical flaw: it uses COM interop to read the excel file.

This means that it’s really slow (3 mins for a 5k line file), the end-user MUST have excel installed on the local pc AND it works for Out-Of-Browser apps only.

However, an excel file is really just a zip that contains a couple of XML files.  If you replace the COM interop with some code that unzips the file and interprets those XML files instead, it becomes really fast (less than a second for a 5k line file), the end-user does not need to have excel installed on the local pc AND it works for in-browser apps too!

SilverlightShow.Com has a really great post on how to read and excel file via XML parsing.  It returns the data as an IEnumerable of Dictionary of ColumnName -> CellValue though, so you’ll need to mold it a bit:

                        FileInfo f = dialog.File;
                        XLSXReader reader = new XLSXReader(f);                        
                        var data = reader.GetData(reader.GetListSubItems().First()).ToArray();

                        var columns = data[0].Keys.OfType().ToList();
                        object[,] values = new object[data.Length, columns.Count];

                        for (int r = 0; r < data.Length; r++)
                        {
                            var row = data[r];
                            foreach (var c in row.Keys)
                            {
                                values[r, columns.IndexOf(c)] = row[c];
                            }
                        }

                        _excelDocRange = values;

Keep rocking LS!

Jan

 

PS:sorry my content is reaching you in ‘waves’, but I have so many pots cooking at the same time I often do not find the time to serve you a decent lunch…  ;-)

About these ads

4 thoughts on “Quick tip: LS Excel importer speed improvements

  1. Pingback: LightSwitch Community and Content Rollup–March 2014 (+//build/) - Visual Studio LightSwitch Team Blog - Site Home - MSDN Blogs

  2. You can buy these cabinets online without leaving the comfort of your home.

    You would spend ample time preparing food for your family on your
    kitchen and through meal time your household enjoys quality family quantity of the adjoining area.
    Shutters can be cleaned easily, making them a popular choice.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s