Provide a detailed summary of the following web content, including what type of content it is (e.g. news article, essay, technical report, blog post, product documentation, content marketing, etc). If the content looks like an error message, respond 'content unavailable'. If there is anything controversial please highlight the controversy. If there is something surprising, unique, or clever, please highlight that as well: Title: Show HN: I've built a C# IDE, Runtime, and AppStore inside Excel Site: querystorm.com Replacing VBA with C#? I spend a fair amount of time writing code in C#, which I enjoy. I occasionally wish I could use C# inside other applications. One application where I think C# would fit right in is Excel, as a replacement for VBA. VBA is a bit (a lot) behind the times and Microsoft isn’t planning on upgrading it. Instead, they’re offering a sandboxed JavaScript environment called Office Scripts. I see the appeal of JavaScript for the web version of Excel, but a good replacement for VBA, it is not. Since Microsoft won’t be doing it, I figured I might have a crack at it. After all, with Microsoft committed to JavaScript, it’s unlikely they will muscle me out of the lucrative C# in Excel market. So here’s what I came up with: QueryStorm IDE running a LINQ query over an Excel table “Is that a C# IDE running LINQ queries over Excel tables?” Yep, that’s exactly what it is. I call it QueryStorm. I started working on it way back in 2014 . It originally had just SQL support , and I’ve continued working on it ever since, gradually adding support for C# scripting, custom C#/Excel functions, NuGet support, a debugger, and even an app store! Let me give you a tour. The QueryStorm IDE QueryStorm uses AvalonEdit as its code editor. AvalonEdit is a fancy textbox that can show text with rich formatting, but it doesn’t know anything about the C# language and its syntax. It needs to be told where symbols and errors are located in the text (so it can highlight them), which autocomplete options to offer, etc. To understand the user’s C# code, QueryStorm uses Microsoft’s C# compiler, aka Roslyn. Roslyn is a .NET library rather than a separate program. Aside from being able to compile C# code into a dll, Roslyn can tell you all about any C# code you give it. For example, if you give it some C# code, it can tell you where any errors might be, where each symbol is defined and referenced, what each symbol means, and what auto-complete options to offer at a particular location in code. Most of the functionality that Roslyn offers is surfaced in the QueryStorm IDE, which enables features such as: Syntax and error highlighting Code completion Code fixes and formatting …and many others Writing C# code in QueryStorm These features make for a productive and enjoyable code editing experience. It might not be exactly on par with Visual Studio or Rider but it certainly beats the pants off of the VBA IDE. C# scripting in Excel The simplest way to explore C# in Excel is to start a C# script which you can do by clicking the “C#” button in the ribbon. Creating a new C# script The scripting flavor of C# allows executing snippets of code and evaluating expressions without all the ceremony of defining namespaces, classes, and a static void Main() method. Example C# expression and its result You can run the script by pressing F5 on the keyboard or clicking the “Run” button in the ribbon. If the script returns a value explicitly (uses the return keyword), or implicitly (ends with an expression that isn’t terminated by a semi-colon), the result will be displayed in the results grid upon execution. You can use this scripting functionality as a C# scratchpad (like LinqPad), but the added value is the interaction with the workbook and Excel. This way, we get the benefits of C# and .NET on top of Excel’s native functionality. Scripts can get a reference to the Excel Application object or the Workbook object using the Resolve<> method. For example, we can write a script to create a new Excel table with a list of files contained in a particular folder: using System.IO; using Microsoft.Office.Interop.Excel; // get some data var appDataFolder = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData); var files = Directory .GetFiles(appDataFolder, "*", SearchOption.AllDirectories) .Take(1000) .Select(f => new { File= Path.GetFileName(f), Size= new FileInfo(f).Length }); // write to Excel as a new table Resolve().Run(excel => { (excel.Selection as Range).WriteTable(files, "myNewTable"); }); Writing a List instance as new Excel table The Excel API available to the user is the same as in VBA, so anything you can do with VBA, you can do with C#. In addition, a set of handy extension methods is also available, like the WriteTable() method shown in the example above. Linq to Excel tables Rather than focusing on Excel concepts like cells and sheets, though, a more interesting thing to focus on is the data. In QueryStorm’s C# scripts, each Excel table shows up as a strongly typed collection we can query and update. For example, suppose we have a table containing a list of cities. For each city, we have its name, population, and the country it belongs to. To find the 3 most populous cities in each country, we could run the following LINQ query: cities.GroupBy(x => x.country) .SelectMany(g => g.OrderByDescending(x => x.population).Take(3)) .OrderBy(x => x.country).ThenBy(x => x.population) .Select(x => new { x.city, x.country, x.population }) Example LINQ query over Excel table ORM for Excel Tables Notice that column names show up in auto-complete, meaning that somewhere there is a strongly typed class that represents the rows of the table. Rows have strongly typed properties that correspond to table columns Where does this class come from? The answer is – it’s generated automatically by QueryStorm (using Reflection.Emit ). The generated class lives inside a dll which the script automatically references. Each time the user changes a table, QueryStorm will examine the change and, if needed, update the generated types. Dynamically generated dll with types for accessing data in Excel tables This functions as an ORM layer on top of Excel tables. For a proper ORM, though, we’d need to be able to specify column data types and table relationships. By default, QueryStorm will guess the data type for each table column based on the data it contains, and it will not assume any relationships between tables. However, defining a custom data context allows us to specify column types and table relationships. Adding relationships between tables will cause extra navigation properties to be added to the generated types. For example, let’s assume we have a “cities” table and a “countries” table. We could modify the column types and add table relationships using the following data context class: public class WorkbookDataContext1 : WorkbookDataContext { public WorkbookDataContext1(IWorkbookAccessor workbookAccessor, ISyncRunner syncRunner) : base(workbookAccessor, syncRunner, false) { } // Set up column data types protected override void Configure(ITablesConfiguration config) { config.ConfigureTable("countries", tableView => { tableView .ConfigureColumn("Name") .ConfigureColumn("Population (2020)"); }); config.ConfigureTable("cities", tableView => { tableView .ConfigureColumn("city") .ConfigureColumn("population"); }); } // Set up relationships protected override IEnumerable CreateRelationships() { // city.Country relationship (each city to 1 country) yield return new RelationInfo("cities", "countryName", To.One, "countries", "Name", "Country"); // country.Cities relationship (each country to N cities) yield return new RelationInfo("countries", "Name", To.Many, "cities", "countryName", "Cities"); } } Once the data context is updated, we can see the new navigation properties in our C# scripts. For example, let’s try to find how much of a country’s population each city accounts for. Since rows in the “cities” table now have a navigation property that allows us to fetch the corresponding country, this is an easy task: The custom data context changes the strongly typed classes that represent table rows As we specified in the data context definition, the population properties are now of type “int?” and cities now have a navigation property “ Country ” that makes it easy to navigate to the corresponding row in the countries table. Updating Excel tables We aren’t limited to just querying the data in Excel tables. We can also perform updates on Excel tables. For example, let’s add a new column in the cities table for the share of the country’s population that the city accounts for. First, we must add the column manually in Excel, and then update it through the C# script. cities.ForEach(c => { int? cityPopulation = c.population; int? countryPopulation = c.Country?.Population__2020_; var shareOfCountrysPopulation = 1.0 * cityPopulation/ countryPopulation; c.share_of_country_s_popupation = $"{100.0 * shareOfCountrysPopulation:0.00}%"; }) Updating the contents of an Excel table’s column using C# LINQ Formatting table rows Lastly, we can update row formatting using the Format() extension method available on IEnumerable objects. For example, let’s highlight all cities that account for more than 20% of the population of their country. cities.Where(c => c.share_of_country_s_popupation > 0.2) .Format(r => r.Interior.ColorIndex = 35); Updating Excel table row formatting using C# Performance The data from Excel tables is internally cached, so reading performance is about the same as working with lists of objects in memory, i.e., very fast. Reading data from large tables, even ones with hundreds of thousands of rows takes just a few milliseconds (except for the first read that populates the cache and can take a few seconds for huge tables). Formatting, though slower than reading, is also highly optimized and has a processing rate of about 1k rows per second. Extending Excel functionality with C# Scripts are mainly used for running one-off queries against data in tables. But we can also use C# to add permanent functionality to Excel itself! We can even publish our extensions to allow other people to use them. We can extend Excel with the following kinds of elements: Custom Excel functions New context menus New ribbon tabs and controls Keyboard shortcuts Custom Excel functions with C# In VBA, we can easily define custom functions for use in Excel formulas. In QueryStorm, it’s just as easy (but more powerful). To define one or more custom Excel functions, we need to create a project in QueryStorm, write the C# function, decorate it with the [ExcelFunction] attribute, and build the project. At that point, the function will be available for use in Excel. Creating and using a C# function in Excel formulas For example, let’s create a function called “ IsMatc