4 ms·

A while ago, as a hobby and later a business, I made an Excel plugin for using SQL in Excel: https://www.querystorm.com https://www.querystorm.com I've now add

by anakic 9y ago

A while ago, as a hobby and later a business, I made an Excel plugin for using SQL in Excel: https://www.querystorm.com https://www.querystorm.com I've now added the capability to use C# in Excel, using Roslyn. Sort of like Linqpad in Excel. This lets it work with Excel tables, fiddle with formatting, and automate stuff. Some of the practical applications: - processing data in Excel (LINQ) - getting data from various sources into Excel (REST, databases, files, active directory, whatever) - building interactive dashboards - building prototypes applications (you write logic in C# and use Excel as the UI and data storage) Here's the introduction: https://querystorm.com/documentation.html https://querystorm.com/documentation.html There's also a short video: https://vimeo.com/242216594 https://vimeo.com/242216594 for a brief intro. I charge for the plugin but it's freemium. If you don't want intellisense, error squigglies and so on, you don't need a license. So... what do you think?

holydude 9y ago
I love it and I hate it at the same time. You would not believe how many people abuse Excel to solve every issue. Entire companies run solely on Excel turned into SAP :-)
- anakic 9y ago
  QueryStorm: optimizing the way we abuse Excel!:) Well, jokes aside, C# and .NET are a better tool, so at least its easier to make a good solution in Excel. I think Excel is an excellent platform for developer tools. E.g. if you're making a small application or a prototype, you just write code in C# and you have Excel to take care of data storage, data visualization and user input. With formulas and graphs you also get data-binding for free from Excel. Spend 1% of the time for 30% of the functionality you'd need from a proper app.
runin2k1 9y ago
It is insane to me that Microsoft hasn't already provided this. I get that you can't replace VBA entirely, or phase it out, without breaking a Lot of Things, and putting a lot of VBA programmers out of work. But every aspect of this being native to Excel would make macro work and data programming easier for a huge segment of the business world. In other words, I like what you're doing and will probably buy a license. But... I don't see a license option that looks like it lets me test it out in a commercial environment without paying first. I might just be missing something there though. I'd love to test it out and be in compliance with your licensing.
- anakic 9y ago
  Thanks for the comment! Yeah, VBA in Excel is pretty outdated in many ways, and Excel isn't going anywhere any time soon. I missed having SQL in Excel originally so that's why I started the plugin. I added C# just now, but I really like how C# and Linq fit into Excel, I just love playing around with it. About licensing: you can get a free trial license by clicking "Try Querystorm" on the QueryStorm website. The trial lasts 14 days, but if you need a longer trial just send me an email (antonio at querystorm.com) and I'll send you an extended trial license. If you have any suggestions or questions while you're checking it out, let me know.
- my123 9y ago
  The problem is interoperability with older versions of Excel when that new one is released too...
- act_right 9y ago
  You actually can do it, it's just hidden and sort of a pain in the ass. You can insert a SQL-linked datatable that can pull parameters from your spreadsheet, if the cell changes the query updates.
lowkeyokay 9y ago
It looks quite amazing. Is there any way to provide som sort of log in form, if data is provided by a protected source? Of course, the user could just enter credentials in cells, but if I want something more secure. Is there any way to integrate an OAuth 2 log in form or similar?
- anakic 9y ago
  Yea, you could do it. In the simplest case, you could build a login form (e.g. winforms) in code in your script and use it to get the username and password from the user. In a more serious use case, you could build your login form and all the login logic in a separate dll, and just call that dll from your scripts. If you'd like to try this, let me know via email (antonio at querystorm.com), I can help or at least point you in the right direction.
  - lowkeyokay 9y ago
    Thanks! Will do.
cbcoutinho 9y ago
I really loved the introduction videos you posted. Great showing of different features in your product - wishing you the best with this project
- anakic 9y ago
  Thanks, I really appreciate that! Glad the hours I spent preparing the videos and docs didn't go to waste:)
smortaz 9y ago
Congrats! This is fantastic. Now can you do the same with Python please? :) But seriously, great work, including the docs/videos.
- anakic 9y ago
  Well thanks a lot:) I'd love to support Python, but for now there's not enough manpower to go into it. Glad you like the docs and videos!
Catalyst4NaN 9y ago
This is absolutely awesome! On a related note, I'm constantly astounded by people who use excel all day but have no interest in getting better at using Excel. I've tried explaining how to use tables and why writing readable formulas is important but they just stare back with a bemused look.
- anakic 9y ago
  Cheers! Yep, most people can't be bothered to make their own lives easier. The algorithm is: if it involves anything unknown - bail.
- watersb 9y ago
  Maybe they don't want a system they cannot audit or debug. Or maybe their data acquisition system is nuts, but maybe it expanded a job for someone who would otherwise be out of one. I have encountered situations like these and my suggestions... well, not wrong, but not a good fit for that department.
jackfoxy 9y ago
How about an F# port of this? Aside from it being a very handy language for ETL type tasks, I suspect your average excel power user who otherwise does not program could more easily wrap his head around the functional paradigm.
- anakic 9y ago
  It's entirely possible. I'll have to see what kind of support Roslyn has for F#. If the support is there, it would be extremely easy to support F#. I'll look into this, thanks for the tip!
  - ptrott2017 9y ago
    For an example for F# perhaps take a look at: http://fsprojects.github.io/ExcelProvider/ http://fsprojects.github.io/ExcelProvider/
deleted 9y ago
[deleted]

by anakic 9y ago

holydude 9y ago

I love it and I hate it at the same time. You would not believe how many people abuse Excel to solve every issue. Entire companies run solely on Excel turned into SAP :-)

anakic 9y ago
QueryStorm: optimizing the way we abuse Excel!:) Well, jokes aside, C# and .NET are a better tool, so at least its easier to make a good solution in Excel. I think Excel is an excellent platform for developer tools. E.g. if you're making a small application or a prototype, you just write code in C# and you have Excel to take care of data storage, data visualization and user input. With formulas and graphs you also get data-binding for free from Excel. Spend 1% of the time for 30% of the functionality you'd need from a proper app.

runin2k1 9y ago

It is insane to me that Microsoft hasn't already provided this. I get that you can't replace VBA entirely, or phase it out, without breaking a Lot of Things, and putting a lot of VBA programmers out of work. But every aspect of this being native to Excel would make macro work and data programming easier for a huge segment of the business world. In other words, I like what you're doing and will probably buy a license. But... I don't see a license option that looks like it lets me test it out in a commercial environment without paying first. I might just be missing something there though. I'd love to test it out and be in compliance with your licensing.

anakic 9y ago
Thanks for the comment! Yeah, VBA in Excel is pretty outdated in many ways, and Excel isn't going anywhere any time soon. I missed having SQL in Excel originally so that's why I started the plugin. I added C# just now, but I really like how C# and Linq fit into Excel, I just love playing around with it. About licensing: you can get a free trial license by clicking "Try Querystorm" on the QueryStorm website. The trial lasts 14 days, but if you need a longer trial just send me an email (antonio at querystorm.com) and I'll send you an extended trial license. If you have any suggestions or questions while you're checking it out, let me know.
my123 9y ago
The problem is interoperability with older versions of Excel when that new one is released too...
act_right 9y ago
You actually can do it, it's just hidden and sort of a pain in the ass. You can insert a SQL-linked datatable that can pull parameters from your spreadsheet, if the cell changes the query updates.

lowkeyokay 9y ago

It looks quite amazing. Is there any way to provide som sort of log in form, if data is provided by a protected source? Of course, the user could just enter credentials in cells, but if I want something more secure. Is there any way to integrate an OAuth 2 log in form or similar?

anakic 9y ago
Yea, you could do it. In the simplest case, you could build a login form (e.g. winforms) in code in your script and use it to get the username and password from the user. In a more serious use case, you could build your login form and all the login logic in a separate dll, and just call that dll from your scripts. If you'd like to try this, let me know via email (antonio at querystorm.com), I can help or at least point you in the right direction.
- lowkeyokay 9y ago
  Thanks! Will do.

cbcoutinho 9y ago

I really loved the introduction videos you posted. Great showing of different features in your product - wishing you the best with this project

anakic 9y ago
Thanks, I really appreciate that! Glad the hours I spent preparing the videos and docs didn't go to waste:)

smortaz 9y ago

Congrats! This is fantastic. Now can you do the same with Python please? :) But seriously, great work, including the docs/videos.

anakic 9y ago
Well thanks a lot:) I'd love to support Python, but for now there's not enough manpower to go into it. Glad you like the docs and videos!

Catalyst4NaN 9y ago

This is absolutely awesome! On a related note, I'm constantly astounded by people who use excel all day but have no interest in getting better at using Excel. I've tried explaining how to use tables and why writing readable formulas is important but they just stare back with a bemused look.

anakic 9y ago
Cheers! Yep, most people can't be bothered to make their own lives easier. The algorithm is: if it involves anything unknown - bail.
watersb 9y ago
Maybe they don't want a system they cannot audit or debug. Or maybe their data acquisition system is nuts, but maybe it expanded a job for someone who would otherwise be out of one. I have encountered situations like these and my suggestions... well, not wrong, but not a good fit for that department.

jackfoxy 9y ago

How about an F# port of this? Aside from it being a very handy language for ETL type tasks, I suspect your average excel power user who otherwise does not program could more easily wrap his head around the functional paradigm.

anakic 9y ago
It's entirely possible. I'll have to see what kind of support Roslyn has for F#. If the support is there, it would be extremely easy to support F#. I'll look into this, thanks for the tip!
- ptrott2017 9y ago
  For an example for F# perhaps take a look at: http://fsprojects.github.io/ExcelProvider/ http://fsprojects.github.io/ExcelProvider/

deleted 9y ago

[deleted]