Microsoft Excel is known for storing, analyzing, and visualizing data. Databases are great for storing a large set of data. At times, you may need to use or analyze data from a database in Excel.
In this article, we are going to see how the Syncfusion .NET Excel (XlsIO) library helps you export data from SQL Server to Excel in C# through external data connections, export filtered data using query parameters, and programmatically refresh Excel data when its database is updated.
Before we see how to export data from SQL Server to Excel, you must understand Excel tables, because Microsoft Excel allows data to be exported from SQL Server to Excel tables. Excel tables allow you to analyze data quickly and easily by performing operations such as sorting, filtering, calculating, and formatting.
How to export data from SQL Server to Excel
In this blog, we are going to use an Employee_Details table from a database mapped to an Excel table. We have used the following query to fill an Excel table from a database:
select * from Employee_Details;
Let’s see the steps involved in exporting data from SQL Server to Excel using C#. Before proceeding with the following steps, refer to the Getting Started guide for the assemblies required to create an Excel file.
Step 1: Create the instances of ExcelEngine and IApplication. It is like opening an Excel application.
Step 2: Create a new workbook instance with one worksheet.
Step 3: Create a connection string to establish a connection with a SQL Server database and query string to retrieve data from a SQL Server database similar to ADO.NET.
Step 4: Establish the connection to the workbook with the connection string and query string.
Step 5: Create an Excel table with an external data connection using the AddEx method of the *IWorksheet.ListObjects * collection class.
Step 6: Pull the data from the database and store it in Excel using the Refresh() method of the *IListObject * class.
Step 7: Save the Excel file and close its instances.
The following code sample shows how to export data from a database to an Excel table.
private void btnCreateExcel_Click(object sender, EventArgs e)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Create a new workbook
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
if (sheet.ListObjects.Count == 0)
{
//Estabilishing the connection in the worksheet
string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password = myPassword";
string query = "SELECT * FROM Employees";
IConnection connection = workbook.Connections.Add("SQLConnection", "Sample connection with SQL Server", connectionString, query, ExcelCommandType.Sql);
//Create Excel table from external connection.
sheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, connection, sheet.Range["A1"]);
}
//Refresh Excel table to get updated values from database
sheet.ListObjects[0].Refresh();
sheet.UsedRange.AutofitColumns();
//Save the file in the given path
Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
workbook.SaveAs(excelStream);
excelStream.Dispose();
}
}
How to export data using query parameters in an Excel table
To export data from SQL Server to an Excel table, queries are used. Queries always return the same results, and their data cannot be filtered at runtime. Therefore, Microsoft Excel provides the option Parameter , which is used to get dynamic values, apply them to the query, and return filtered results. Parameters can be set either through a prompt event, as a constant, or as an Excel range. Developers can write a query and leave it up to the end-user to filter data on their own. The values entered as parameters are used in the WHERE clause of the SQL query. The available parameter types are:
- Prompt parameter
- Constant parameter
- Range parameter
Let’s see these parameter types in detail and how to implement them.
Prompt parameter
The prompt parameter type helps the user by raising a prompt event where a filter value can be entered at runtime on refreshing the Excel table. This is useful when there is a need to filter the Excel table with different values.
The following code example illustrates how to set two parameters through prompt events by accessing an existing Excel table with an external connection.
private void btnApplyPromptParameter_Click(object sender, EventArgs e)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
//Load existing Excel template document with external connection
IWorkbook workbook = application.Workbooks.Open(DataPathBase + "Template.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Accessing the query table from Excel table
QueryTableImpl queryTable = worksheet.ListObjects[0].QueryTable;
//Update Excel query to filter data from various parameter types
string query = "select * from Employee_Details where Emp_Age = ? AND Country = ?;";
queryTable.CommandText = query;
//Add a parameter
IParameter promptParam = queryTable.Parameters.Add("PromptParameter", ExcelParameterDataType.ParamTypeChar);
//Set PROMPT parameter by raising the event SetParam
promptParam.SetParam(ExcelParameterType.Prompt, "Emp Age");
promptParam.Prompt += new PromptEventHandler(SetParameter1);
promptParam.Prompt += new PromptEventHandler(SetParameter2);
//Refresh Excel table to filter data while loading Excel document
worksheet.ListObjects[0].Refresh();
//Save the workbook
workbook.SaveAs("Output.xlsx");
}
}
private void SetParameter1(object sender, PromptEventArgs args)
{
args.Value = 30;
}
private void SetParameter2(object sender, PromptEventArgs args)
{
args.Value = "Argentina";
}
Constant parameter
The constant parameter type helps the user define a constant from the code and filter the Excel table. Here, if the data is modified in the database, the filtering is always done for the constant value defined in the code.
The following code example illustrates how to set the parameter through the constant type by accessing an existing Excel table with an external connection.
private void btnApplyConstantParameter_Click(object sender, EventArgs e)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
//Load existing Excel template document with external connection
IWorkbook workbook = application.Workbooks.Open(DataPathBase + "Template.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Accessing the query table from Excel table
QueryTableImpl queryTable = worksheet.ListObjects[0].QueryTable;
//Update Excel query to filter data from various parameter types
string query = "select * from Employee_Details where Emp_Age > ?;";
queryTable.CommandText = query;
//Add a parameter object
IParameter constParam = queryTable.Parameters.Add("ConstantParameter", ExcelParameterDataType.ParamTypeChar);
//Set CONSTANT parameter
constParam.SetParam(ExcelParameterType.Constant, 25);
//Refresh Excel table to filter data while loading Excel document
worksheet.ListObjects[0].Refresh();
//Save the workbook
workbook.SaveAs("Output.xlsx");
}
}
Range parameter
Situations may arise where you need to show data from a database based on a cell value in an Excel worksheet. For example, if a cell contains a formula and the value varies for certain cases, the Excel table will reflect this accordingly. The range parameter helps filter data based on the cell values available in a worksheet range.
The following code shows how to set a parameter type to a specific range.
private void btnApplyRangeParameter_Click(object sender, EventArgs e)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
//Load existing Excel template document with external connection
IWorkbook workbook = application.Workbooks.Open(DataPathBase + "Template.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Accessing the query table from Excel table
QueryTableImpl queryTable = worksheet.ListObjects[0].QueryTable;
//Update Excel query to filter data from various parameter types
string query = "select * from Employee_Details where Emp_Age = ?;";
queryTable.CommandText = query;
//Add a parameter
IParameter rangeParam = queryTable.Parameters.Add("RangeParameter", ExcelParameterDataType.ParamTypeChar);
//Set RANGE parameter
rangeParam.SetParam(ExcelParameterType.Range, worksheet.Range["H1"]);
rangeParam.RefreshOnChange = true;
//Refresh Excel table to filter data while loading Excel document
worksheet.ListObjects[0].Refresh();
//Save the workbook
workbook.SaveAs("Output.xlsx");
}
}
How to refresh Excel data from a database
Excel worksheets that are connected to SQL Server as an external data source will fetch data for an Excel table. If data is updated in the database, it is mandatory to refresh the Excel table to update its data. Essential XlsIO allows you to update the data by refreshing the table from its source. Refresh the connection every time, when the data is updated.
Here we invoke the Refresh() method of the *IListObject * class to get updated data from the database in an Excel table.
The following code sample shows how to export data from a database to an Excel table.
private void btnCreateExcel_Click(object sender, EventArgs e)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
//Load existing Excel document with an external connection
IWorkbook workbook = application.Workbooks.Open(DataPathBase + "Template.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Refresh Excel table to update data after loading the Excel document.
worksheet.ListObjects[0].Refresh();
//Save the workbook
workbook.SaveAs("Output.xlsx");
}
}
GitHub Sample
You can download the sample to export data from SQL Server into Excel table in C# here.
Conclusion
In short, Excel is mainly used for processing and visualizing data, and Syncfusion’s Excel (XlsIO) library provides an easy way to export data from SQL Server and filter it at runtime. Use them effectively to generate Excel reports with high performance and process large amounts of data.
For more information about creating external data connections and setting parameters, refer to our documentation on external data connections. This feature is supported in .NET Framework platforms such as Windows Forms, WPF, ASP.NET, and ASP.NET MVC.
Take a moment to peruse the documentation, where you’ll find other options and features, all with accompanying code samples. Using the Excel library, you can also export Excel data to PDF, image, CSV, TSV, HTML, and ODS file formats; data tables; collections of objects; and more.
If you are new to our Excel library, it is highly recommended that you follow our Getting Started guide.
Already a Syncfusion user? You can download the product setup from the Essential Studio Downloads page. If you’re not yet a Syncfusion user, you can download a free, 30-day trial from our website.
If you have any questions or require clarifications about these features, please let us know in the comments below. You can also contact us through our support forum, Direct-Trac, or our feedback portal. We are happy to assist you!
Related Blog
The post How to Export Data from SQL Server to Excel Table in C# appeared first on Syncfusion Blogs.
Top comments (0)