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… 😉
Pingback: LightSwitch Community and Content Rollup–March 2014 (+//build/) - Visual Studio LightSwitch Team Blog - Site Home - MSDN Blogs
Thanks… do you have the code? It would be nice if you could shared it…
Hey Johann,
I am deeply sorry but we’ve done so many proprietary changes to the importer that I cannot legally share it entirely. Hope you understand!
Keep rocking LS!
Jan