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…  😉