Coding Excel queries under .NET

Kobus Burger

I need guidance on how to code Excel queries using .NET. It is easy to code Excel queries with VBA. The Excel macro recorder will guide you to produce code similar to the following:

Sub Macro2()
ActiveWorkbook.Queries.Add _
Name:="TabWBS112288", _
Formula:="let Source = Excel.CurrentWorkbook(){[Name=""TabWBS112288""]}[Content] in Source"
Workbooks("WBS test.xlsx").Connections.Add2 _
Name:="Query - TabWBS112288", _
Description:="Connection to the 'TabWBS112288' query in the workbook.", _
ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=TabWBS112288;Extended Properties=""""", _
CommandText:="SELECT * FROM [TabWBS112288]", _
lCmdtype:=2
End Sub

With .NET I use the Excel interop but it seems not have the same query objects as VBA. The interop has workbookconnections and models but not queries.

I need to manipulate Excel queries using C# in Visula Studio but I do not know how to.

How can I code the example VBA in C#?

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kobus Burger5y ago
Thank you for all the amazing answers but I may be bad communicating my problem. I would like to create a Power Query in Excel using C# so that the query can later be changed using the Power Query Editor in Excel (see image). I can create a query with VBA but I struggle with C#.
alice yang5y ago
You can try Free Spire.XLS API. The following code explains how to load an existing excel file and access a specific worksheet by name, then you're able to do lots of manipulations you want in the sheet.
using System.Drawing;
using Spire.Xls;

namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance and load the excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Input.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets["sheetName"];

            //do some manipulations...

            //Save the resultant file
            workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);
        }
    }
}
Read Excel to Datatable:
Workbook workbook = new Workbook();
//Load Excel file
workbook.LoadFromFile("Input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Read sheet data to DataTable and bind to DataGridView control in WinForms application
this.dataGridView1.DataSource = sheet.ExportDataTable();
For more online documentations, visit this link:
https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Spire.XLS-Program-Guide-Content.html
Sachin Singh5y ago
This is a short example how we query excel in C#
OleDbConnection con = new OleDbConnection(
    "provider=Microsoft.Jet.OLEDB.4.0;data source="
    + XLS_FILE_NAME_AND_PATH_HERE
    + ";Extended Properties=Excel 8.0;");

StringBuilder stbQuery = new StringBuilder();
stbQuery.Append("SELECT * FROM [" + SHEETNAME_HERE + "$A1:D1]");
OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.ToString(), con);

DataSet dsXLS = new DataSet();
adp.Fill(dsXLS);

DataView dvEmp = new DataView(dsXLS.Tables[0]);

dataGridView1.DataSource = dvEmp;
Amit Gupta5y ago
To do your stuffs like querying, you can use OLEdbConnection, you can manipulate the excel which is much faster processing in compared to Interop. https://www.codeproject.com/Articles/1088970/Read-Write-Excel-file-with-OLEDB-in-Csharp-without
Kobus Burger5y ago
Thanks for the fast responses but both links are about how to create Excel files. I need to know how to create queries in Excel, not how to create Excel files.
Mageshwaran R5y ago
Hi Burger,
please refer this link:
https://help.syncfusion.com/file-formats/xlsio/getting-started-create-excel-file-csharp-vbnet
Sachin Singh5y ago
please go through the link http://csharp.net-informations.com/excel/csharp-create-excel.htm