locked
Getting LS data into Excel, tutorials and examples? RRS feed

  • Question

  • I have an HTML Client app that I'd like to explore data exports for.  I can connect to my backend store using Excel's support for OData.

    I've made connections to my LS backend in Excel, but the actual import  is something that's not terribly intuitive, in the sense that I'm not sure how to maintain relationships between the tables, how to get in only the rows I'm interested, and other fairly beginner's level queustions.  

    Are there any recommended tutorials out there for importing LS data into Excel?

    Thursday, May 7, 2015 6:29 AM

Answers

  • Hi Jim,

    I'd suggest looking into Excel Power BI Tools (Power Query, Power Pivot, Power View, Power Maps).

    I haven't done a ton of work in Excel to know any good tutorials, but I can says it's super easy to connect to LS OData, bring it into Excel 2013, expand related entities, etc.

    The tools tout abilities to combine, Shape, Visualize, etc. but at a quick glance I don't find the experience in Excel highly intuitive. 

    If you search for Excel Power BI tools and a weed out all the stuff about Power BI Services and Power BI for Office365 - subscription required) and focus on Excel add-ins then I think you should find a good tutorial or two.  Share if you do.

    HTH,

    Josh

      

    • Marked as answer by jim bancroft Friday, May 8, 2015 9:59 PM
    Friday, May 8, 2015 12:37 PM

All replies

  • There are several ways of going about this. One we often employ uses the Open XML SDK. So this creates and downloads a spreadsheet to the browser that can be viewed or saved. There are lots of articles  and blogs out there, including the sample code that comes with the SDK. Working with the new Office 365 APIs another route.

    Writing Large Excel Files with the Open XML SDK

    The series of blog posts by Elizabeth shows how to export to Word.

    How to Export Data to Microsoft Word with the LightSwitch HTML Client – Part 3 (Elizabeth Maher)

    So that is using the same technology.

    If Microsoft start working on a LightSwitch vNext or open source we will get back to blogging about this sort of stuff.

    Dave


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Thursday, May 7, 2015 8:36 AM
  • Thanks Dave.  What I was really thinking of was straight imports into excel from the ApplicationData.svc endpoint, so that true end-users could get the data they need direct to Excel, rather than running code as above-- though your links are going to be very helpful for us.

    What I'm really after are tutorials on connecting to the OData endpoint and importing data directly to Excel.  From my brief noodling around with the tools there's a lot of power there but I'm not sure how to harness it or what's possible.  Those are the sorts of examples I'm after.

    Thursday, May 7, 2015 5:06 PM
  • Hi Jim,

    I'd suggest looking into Excel Power BI Tools (Power Query, Power Pivot, Power View, Power Maps).

    I haven't done a ton of work in Excel to know any good tutorials, but I can says it's super easy to connect to LS OData, bring it into Excel 2013, expand related entities, etc.

    The tools tout abilities to combine, Shape, Visualize, etc. but at a quick glance I don't find the experience in Excel highly intuitive. 

    If you search for Excel Power BI tools and a weed out all the stuff about Power BI Services and Power BI for Office365 - subscription required) and focus on Excel add-ins then I think you should find a good tutorial or two.  Share if you do.

    HTH,

    Josh

      

    • Marked as answer by jim bancroft Friday, May 8, 2015 9:59 PM
    Friday, May 8, 2015 12:37 PM
  • I have tried to use power pivot before with the OData service but always had trouble with timeouts for large tables.  I don't recall exactly but there seemed to be a limit as to how much data you can pull down.  I want to say 10K records but don't remember exactly...  For smaller data sets it works fine.
    Friday, May 8, 2015 2:41 PM
  • Thanks Josh (and Hessc.)  I'll follow up on the Excel BI tools stuff and post back here if one or more of the tutorials are worthwhile.
    Friday, May 8, 2015 9:59 PM