How to Export Data from SQL Server to Excel in C#

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.

Enjoy a smooth experience with Syncfusion’s Excel Library! Get started with a few lines of code and without Microsoft or interop dependencies.

Explore Now

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 an SQL Server database and a query string to retrieve data from an 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("Output.xlsx");

        workbook.SaveAs(excelStream);
        excelStream.Dispose();
    }
}

Handle Excel files like a pro with Syncfusion’s C# Excel Library, offering well-documented APIs for each functionality.

Read Now

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("../../Data/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("../../Data/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");
    }
}

Immerse yourself in practical examples spotlighting the extraordinary features of Syncfusion’s C# Excel Library!

Try Now

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("../../Data/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("../../Data/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 an Excel table in C# GitHub demo.

Don't settle for ordinary spreadsheet solutions. Switch to Syncfusion and upgrade the way you handle Excel files in your apps!

Try It Free

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 ® for File Formats” href=”https://www.syncfusion.com/downloads/fileformats/confirm” target=”_blank” rel=”noopener”>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, support portal, or our feedback portal. We are happy to assist you!

Related Blogs

• 6 Easy Ways to Export Data to Excel in C#
• Easy Steps to Export HTML Tables to an Excel Worksheet in C#
• Export Data from Collection to Excel and Group It in C#
• Export Data to a Predefined Excel Template in C#