By: Mircea Dragan | Updated: 2020-10-28 | Comments (3) | Related: More > Import and Export
Problem
In several companies I have worked it has been a common practice to export SQL Server tables to Excel files for various reasons. One reason, is that using data in Excel is convenient for people if they don't have the T-SQL skills needed or need the functionality that Excel offers. This article presents how you can export data from a SQL Server table to an Excel file.
Solution
Exporting data from a SQL Server table to Excel is a pretty common practice. There are several ways this can be done, but in this article, we will cover how this can be done using C# code.
These are the versions used for these articles: SQL Server 2019, Visual Studio 2019 and Microsoft Excel 2013. Some of the C# code will not work if you don’t use the latest .NET (minimum 4.8). I used the old pubs database (this database was created by Microsoft years ago as a sample database). In the download, I include scripts to create the pubs database together with the project files.
Create Excel File from SQL Table
For this example, I will export data from the authors table to an Excel file.
The first step is to create the Excel file.
When working with Excel, we need the following namespace:
using Excel = Microsoft.Office.Interop.Excel;
The Excel file will be created on the desktop. If a previous file with the same name exists, it will be deleted first and then the new file is created.
string fileName = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" +
"ExcelReport.xlsx";
Excel.Application xlsApp;
Excel.Workbook xlsWorkbook;
Excel.Worksheet xlsWorksheet;
object misValue = System.Reflection.Missing.Value;
// Remove the old excel report file
try
{
FileInfo oldFile = new FileInfo(fileName);
if(oldFile.Exists)
{
File.SetAttributes(oldFile.FullName, FileAttributes.Normal);
oldFile.Delete();
}
}
catch(Exception ex)
{
MessageBox.Show("Error removing old Excel report: " + ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Stop);
return; }
We create the Excel file using code like this:
xlsApp = new Excel.Application();
xlsWorkbook = xlsApp.Workbooks.Add(misValue);
xlsWorksheet = (Excel.Worksheet)xlsWorkbook.Sheets[1];
// Create the header for Excel file
xlsWorksheet.Cells[1, 1] = "Example of Excel report. Get data from pubs database, table authors";
Excel.Range range = xlsWorksheet.get_Range("A1", "E1");
range.Merge(1);
range.Borders.Color = Color.Black.ToArgb();
range.Interior.Color = Color.Yellow.ToArgb();dynamic dbschema = new JObject();
For this file we create a header first. The header is one line which describes the content of the file, displayed in a different color, and for each column of the table we will have the column name as the column header in the Excel file. You can choose something different, according to your needs.
As you can see, the "general" header will not fit in column A (it is too long) and we don’t want to enlarge column A to fit it. I choose to merge several cells from the first row, such that the header spans over those cells.
The next step is to export the table to an Excel file. This can be done with the following code:
using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlCommand cmd = new SqlCommand(sqlselect, conn);
using SqlDataReader dr = cmd.ExecuteReader();
if(dr.HasRows)
{
for(int j = 0; j < dr.FieldCount; ++j)
{
xlsWorksheet.Cells[i, j + 1] = dr.GetName(j);
}
++i;
}
while(dr.Read())
{
for(int j = 1; j <= dr.FieldCount; ++j)
xlsWorksheet.Cells[i, j] = dr.GetValue(j - 1);
++i;
}
In the for loop, we fill in the column headers in the Excel file and export the data.
Because some of the data in the same column might be wider that others (this usually happens with text cells), we will set the properties of each column to auto fit the largest cell in each column:
range = xlsWorksheet.get_Range("A2", "I" + (i + 2).ToString());
range.Columns.AutoFit();
The last step is to save the Excel file and to release the resources the Excel object uses:
xlsWorkbook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue,
misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges,
misValue, misValue, misValue, misValue);
xlsWorkbook.Close(true, misValue, misValue);
xlsApp.Quit();
ReleaseObject(xlsWorksheet);
ReleaseObject(xlsWorkbook);
ReleaseObject(xlsApp);
At the end we can ask the user if they want to see the Excel file:
if(MessageBox.Show("Excel report has been created on your desktop\nWould you like to open it?",
"Created Excel report",
MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2) ==
DialogResult.Yes)
{
Process.Start(fileName);
}
Download Project Files
I attached the C# project that I used that you can download from here, this download also includes T-SQL scripts to create the pubs database. Because the database script is from an older version of SQL Server, it does not run correctly on SQL Server 2019, so I commented out the stored procedure code that fails. The stored procedures fail in SQL Server 2019, because of the COMPUTE BY clause that is used in the stored procedures. If you want to fix these procedures, see this tip on how to replace COMPUTE BY with ROLL UP.
References
Here are a few other articles used when putting this together:
Next Steps
- Export data from different tables into specific Excel sheets
- Do some computation in the Excel file with the exported data
- Create a chart with the exported data
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
Article Last Updated: 2020-10-28
