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


By:   |   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:

sample data

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:

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


Last Updated: 2021-03-26


get scripts

next tip button



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

View all my tips



Comments For This Article




Tuesday, April 13, 2021 - 2:13:14 PM - Mircea Back To Top (88524)
Obviously there are several ways of working with Excel files. I personally prefer to avoid any 3rd party solutions when there is one built into Windows and into NET in particular

Tuesday, April 13, 2021 - 10:20:11 AM - corey Back To Top (88518)
...or use epplus.dll (.xlsx only) or npoi.dll (.xls or .xlsx).
Both are pretty nice and fully functional "wheels". If you've done any excel stuff thru vba and the Excel COM objects, the programming process with either will be quite familiar. well, lacking the hanging Excel processes! So both epplus and npoi are what I've used on servers.


download





Recommended Reading

Simple way to export SQL Server data to Text Files

Using OPENROWSET to read large files into SQL Server

Simple Image Import and Export Using T-SQL for SQL Server

How to Copy a Table in SQL Server to Another Database

Bulk Insert Data into SQL Server














get free sql tips
agree to terms