External Connections and Query Tables

EPPlus from version 8.3 supports adding, modifying and removing external connections. EPPlus supports adding most types of connections including Text, Database, OLAP, Web and Power Query connections. Connections can be used by Query Tables or as sources for Pivot Tables. Please note that EPPlus does not execute the queries; it relies on the spreadsheet application (e.g., Excel) to do so.

Adding a connection.

The connections object has five functions to add different connections.

Function	Description
AddPowerQuery	Adds a connection using power query. This requires a connection string using the Microsoft.Mashup.OleDb.1 provider and a M-Formula to describe the data source.
AddDatabase	Adds a connection using a connection string. For example an OLEDB or ODBC connection.
AddOlap	Adds a connection to an OLAP data source.
AddWeb	Adds a web query connection. This type of connection is considered legacy in Excel.
AddText	Adds a connection to a text file. This type of connection is considered legacy in Excel.

Adding a database connection.

To add a connection to a workbook you use one of the Add prefixed methods. Below we add an OLEDB connection, using the AddDatabase method. Use this method to add OLEDB or ODBC connection and other database related connections.

var connectionString = $"provider=Microsoft.ACE.OLEDB.12.0;data source=C:\\MyCsvFolder\\;extended properties=\"text;HDR=Yes;FMT=Delimited\"";
var c = p.Workbook.Connections.AddDatabase("OleDbConnection1", connectionString);
c.DatabaseProperties.CommandType = eCommandType.SqlStatement;
c.DatabaseProperties.Command = "select * from [Sample9-1.txt]";  //Use the file name as the table.

Adding a Power Query connection.

EPPlus 8.3 also supports adding power query connections.
A power query connection is an OLEDB connection that describes the query using a M-formula. Excel uses power query for most of its query created in the data wizards.

// Setup the connection string for power query. It uses the Microsoft.Mashup.OleDb.1 provider.  
// The Location property should contain the object in the M-formula.  
var connectionString = "Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=\"Table 2\";Extended Properties=\"\"";

var csvFile = FileUtil.GetFileInfo("09-Connections and QueryTables", "Sample9-1.txt");
//The M formula. EPPlus do not validate this formula, so make sure you set it up correctly.  
//A good way of getting your formulas is to create the query in Excel and then open the file with EPPlus 
//to extract the formula from the Workbook.PowerQuerySettings.Formulas property or getting it from the advanced Power Query editor in Excel. Using the power query advanced editor will require you to add the declaration ( shared #\"yourTableName\" = ...).
//Please note that the M-Formula supplied to the AddPowerQuery method should not contain the Section1 declaration.
var mFormula = "shared #\"Table 2\" = let\r\n    Source = Csv.Document(File.Contents(\"" + csvFile + "\"),[Delimiter=\",\", Columns=7, QuoteStyle=QuoteStyle.None]),\r\n    #\"Promoted Headers\" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),\r\n    #\"Changed Type\" = Table.TransformColumnTypes(#\"Promoted Headers\",{{\"Period\", type date}, {\"Europe\", type number}, {\"Africa\", type number}, {\"Asia\", type number}, {\"North America\", type number}, {\"South America\", type number}, {\"Australia\", type number}}, \"en-US\")\r\nin\r\n    #\"Changed Type\";";

var dbConn = p.Workbook.Connections.AddPowerQuery("PowerQueryTextConnection", connectionString, mFormula);
//The table in the Select command should be the power query object.
dbConn.DatabaseProperties.Command = "SELECT * FROM [Table 2]";

Power query formulas are stored separately in the PowerQuerySettings.Formulas property of the Workbook object. These setting contain additional settings for the power query connection like permissions and meta data. Power Query settings are stored separately in the CustomXml under the "DataMashup" namespace as a base 64 encoded string inside the package.

Adding a query table

A query table is a range or a table connected to a connection. Modern connections like Power query and database connections uses a table object as output for the data. Older types of connections, like legacy Text and Web connection uses a query table connected to a worksheet range. To add a query table connected to a Table object using the power query connection created above:

 var ws = p.Workbook.Worksheets.Add("PowerQueryText");
 //Add a query table with seven columns. As EPPlus does not execute connections/queries, the columns must be specified in the last string array parameter and must match the query output.
 var tbl = ws.Tables.AddQueryTable(ws.Cells["A1:G2"], "Table_2", dbConn, ["Period", "Europe", "Africa", "Asia", "North America", "South America", "Austraila"]);
 tbl.TableStyle = OfficeOpenXml.Table.TableStyles.Dark3;

//EPPlus does not execute connection queries, so we refresh the query when the workbook is loaded.
tbl.QueryTable.RefreshOnLoad = true;

//Styling for a table column can be set via the DataStyle property like this:
tbl.Columns[0].DataStyle.NumberFormat.Format = "yyyy-MM";

For legacy types of connection the query table must be added directly to a worksheet range.

//Legacy text connections must be add directly to the worksheet, as it is not supported using tables.
var qt = ws.QueryTables.Add(ws.Cells["A1:G5"], "MyQuertyTable", c);

Using a connection as source for a pivot table.

You can also use a connection as source for a pivot table. Please note that using a connection as source for a pivot table depends on Excel to execute the connection/query. EPPlus will not calculate pivot tables connected to an external source. To add a pivot table using a connection as source:

var ws = package.Workbook.Worksheets.Add("PivotTableWithConnection");
var pt = ws.PivotTables.Add(ws.Cells["A3"], connection, "PivotTable1", ["Name", "Date", "Amount", "Percent", "Category"]);
var rf = pt.RowFields.Add(pt.Fields["Date"]);            
rf.Sort = eSortType.Ascending;
pt.DataFields.Add(pt.Fields["Amount"]);

See also

• Sample 9.1-C# or Sample 9.1 VB