Export SQL Server Data to Excel and Add New Columns with C#

By: Mircea Dragan   |   Updated: 2021-03-26   |   Comments (2)   |   Related: More > Import and Export

Problem

Loading data from a SQL Server table to Excel is a common task. Once the data is loaded into Excel, the user can often add their own columns and use Excel functions to compute different values.  This can also be done in the SQL query, but in this tutorial, we will look at this can be done programmatically to build new columns automatically as data is loaded into Excel.

Solution

As an example, let us consider the following data which represents sales for different items:

We want to compute the total value for each line in the order by calculating UnitPrice times Quantity. We can compute the value of each line by adding in the SQL SELECT statement a new column as follows.

UnitPrice * Quantity AS [Line Total]

But, the purpose of this article is to show how we can do computations in Excel rather than in SQL Server. One advantage of using computations in Excel rather than in SQL Server is that we have less network traffic (we export less data from SQL Server). Another advantage is that Excel has a lot more formulas than SQL Server.

I will use SQL Server 2019, Visual Studio 2019 and Microsoft Excel 2013 for the examples below. Some of the C# code will not work if you don’t use the latest .NET (minimum 4.8). I used the northwind database for SQL Server 2019 (see https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs).

In a previous article, Export SQL Server Data to Multiple Excel Worksheets with C#, I showed how to extract data, so we won't cover that in this article.

Export Sales Orders to Excel

The Excel file will be created on the desktop. If a previous file with the same name exists, it will be replaced.

The query which selects the orders is quite strait forward:

SELECT 
    Orders.OrderID,
    OrderDate,
    ShipCity,
    ShipCountry,
    ProductID,
    UnitPrice,
    Quantity
FROM Orders join [Order Details] on Orders.OrderID = [Order Details].OrderID
ORDER BY OrderID

We export this query to a sheet named "Sales Report". The sheet has one extra column, which we will name "Line Total" and we create with the code below:

int i = 3;
if(dr.HasRows)
{
    for(int j = 0; j < dr.FieldCount; ++j)
    {
        xlsItemOrder.Cells[i, j + 1] = dr.GetName(j);
    }
    xlsItemOrder.Cells[i, dr.FieldCount + 1] = "Line Total";
    ++i;
}

Once we have the headers we can export data into the sheet. For doing this we need to know the names of the columns which we will use in the formulas. We have 3 columns involved, the third column being the total.

char col1 = (char)('A' + dr.FieldCount - 2);
char col2 = (char)('A' + dr.FieldCount - 1);
char coltotal = (char)('A' + dr.FieldCount);

This code assumes that we have less than 26 columns. It is easy to extend this for more than 26 columns, but for this example we kept it simple. If you are not sure how to do this, please leave a comment and I will write the formula.

The data is exported to Excel using this code:

while(dr.Read())
{
    for(int j = 1; j <= dr.FieldCount; ++j)
        xlsItemOrder.Cells[i, j] = dr.GetValue(j - 1);
    string formula = "=" + col1 + i.ToString() + "*" + col2 + i.ToString();
    xlsItemOrder.Cells[i, dr.FieldCount + 1].NumberFormat = "0.00";
    xlsItemOrder.Cells[i, dr.FieldCount + 1] = formula;
    ++i;
}

We can see that we use a formula exactly the same way as we would in Excel.

What is left is the sum of the new column we created:

xlsItemOrder.Cells[++i, 1] = "Total Sales";
Excel.Range rangeTotal = xlsItemOrder.get_Range("A" + i, "D" + i);
rangeTotal.Merge(1);
rangeTotal.Borders.Color = Color.Black.ToArgb();
rangeTotal.Interior.Color = Color.Yellow.ToArgb();
rangeTotal.Font.Name = "Courier New";
rangeTotal.Font.Size = 14;
rangeTotal.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
string sum = "=Sum(" + coltotal + 3.ToString() + ":" + coltotal + (i - 2).ToString() + ")";
xlsItemOrder.Cells[i, dr.FieldCount + 1].NumberFormat = "0.00";
xlsItemOrder.Cells[i, dr.FieldCount + 1] = sum;
dr.Close();

As an exercise you can try to use different formulas with data you select at your choice.

Download Project files

You can download the C# project and the Excel file which contains data generated by this project.

References

Check out these related articles:

• Export SQL Server Table to Excel with C#
• Export SQL Server Data to Multiple Excel Worksheets with C#
• https://www.dotnetperls.com/excel
• https://docs.microsoft.com/en-us/sql/relational-databases/tables/lesson-1-converting-a-table-to-a-hierarchical-structure?view=sql-server-ver15
• https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases

Next Steps

• Try to use Excel to replace the missing COMPUTE BY clause in SQL Server
• Generating simple reports in Excel with exported data
• Import data in SQL Server from an Excel file

About the author

Mircea Dragan is a Senior Computer Scientist with a strong mathematical background with over 18 years of hands-on experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips