From a SQL Command
Into a Formatted Excel
Introduction
This project demonstrates how to create formatted Excel worksheets based on the results of SQL Select commands. The resulting Excel spreadsheet includes formatted, sortable and filter headers based on the columns returned from the SQL results. Columns can be formatted to text or currency within the spreadsheet. The user can create several sheets in the same Excel workbook with results from different SQL commands or databases. The column headers of the worksheet depends on the results of the SQL statement, change the Select and the Excel columns change.
I created this project so we could quickly generate ad-hoc information in a business friendly, professional fashion without having to write code for each request.
Requirements
This project was generated using Visual Studio 2010 and Office Excel 2007. Some of the code is using features added to VS 2010 and some of the Excel commands require the 2007 version of Office. The concepts could work with earlier versions of VS and Office, with some modifications of the program. This program uses the SQL specific ADO commands but I think this will work with the generic ODBC version of the commands.
This problem should run on any machine that has Framework V4.0 installed, Office 2007 installed and access to a SQL database.
Operational Overview
- The user enters the Connection String, SQL command and Sheet name.
- The code will access the database and build a
DataTable from the results. - The code then generates the Excel header from the
DataTable columns data. - The code adds the
DataRows from the DataTable to the Excel workbook. - The user can save or discard the spreadsheet as desired.
The Program
I created this project using WPF as the user interface. The interface is simple: the user enters the connection string, the SQL command, name of the worksheet and then presses the “To Excel” button.
Build the DataTable from SQL
The click event handler does minimal validation of the data entered, then calls PerformSqlGet() to return the DataTable from the database using ADO.NET.
conn = new SqlConnection(_ConnectionString);
cmd = new SqlCommand(_Command);
The type of this command (StoredProcedure or Text) is based on the first word of the command. An adaptor is created to process the Command and the adaptor is used to fill the DataTable:
cmd.Connection = conn;
cmd.CommandTimeout = _Timeout;
adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
Loading Excel
The first time this button is pressed, Excel is loaded in the background. Communicating with Excel is by invoking COM interoperability calls, but the process of coding this seems much easier now than in previous versions of Excel and Visual Studio. Instead of locating and downloading the Primary Interop Assemble for Excel, I simply added a reference of Excel.exe to my program (in my case: c:\Program Files\Microsoft Office\Office12\EXCEL.EXE). This generated the interop assemble for me. In my code, I added:
using Microsoft.Office.Interop.Excel;
and declared some class variables:
Microsoft.Office.Interop.Excel.Application _ExcelApp = null;
Microsoft.Office.Interop.Excel.Workbook _wb = null;
Now I am ready to load Excel.
On the first press of the "To Excel" button, I load the Excel Application, create a Workbook and add a Worksheet to the Workbook.
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_wb = _ExcelApp.Workbooks.Add();
Worksheet ws = AddWorksheetToWorkbook(_wb, txtWorksheetName.Text);
_ExcepApp is the pointer to Excel Application instance running in the background. Worksheet (aka Microsoft.Office.Interop.Excel.WorkSheet) is the Excel sheet to which I will add data from the DataTable data.
Loading a Worksheet in Excel
When this is complete, I call my method AddToExcel() to perform the work of adding the headers and data to the worksheet. In AddToExcel(), I make Excel invisible for a slight performance improvement. Then I add the Column names from the DataTable to the first row of Excel.
for (int Idx = 0; Idx < dt.Columns.Count; Idx++)
{
ws.Range["A1"].Offset[0, Idx].Value = dt.Columns[Idx].ColumnName;
}
This has created the Excel headers. Now I add the data from each row of the DataTable to the spreadsheet.
for (int Idx = 0; Idx < dt.Rows.Count; Idx++)
{
ws.Range["A2"].Offset[Idx].Resize[1, dt.Columns.Count].Value = dt.Rows[Idx].ItemArray;
}
Now the header and data rows are loaded. There is an Excel 2007 function to format a sheet as a table. To use this, you define an Excel range and call the function to create a table. The code to format a table is:
string EndColumn = GetColumnName(dt.Columns.Count);
Microsoft.Office.Interop.Excel.Range r = ws.Range["A1:" + EndColumn
+ (dt.Rows.Count + 1).ToString()];
r.Select();
ws.ListObjects.Add(XlListObjectSourceType.xlSrcRange, r, null
, XlYesNoGuess.xlYes).Name = "Table1";
The result is a nicely formatted table with colored lines and the ability to sort or filter by column data.
Finishing Touches
Column Formatting
This version of this program will allow the formatting of named columns into text or currency instead of taking the Excel default formatting for the cells. For example, this example returns a PostalCode column which has numbers and letters. Default Excel is odd looking:
Adding the DataTable column name ('PostalCode' in this case) to the “String Columns” field changes the presentation of this column in Excel.
The Excel column with the matching name is reformatted to the 'text' format resulting in a nicer presentation:
The technique used to accomplish this is to check each in the DataTable for its column name in the user interface. If there is a match, then apply the 'Text' formatting to that column. Oddly enough, the 'Text' formatting is a number formatting option.
for (int Idx = 0; Idx < dt.Columns.Count; Idx++)
{
if (ColumnsToMakeString != null)
{
if (ColumnsToMakeString.Contains(dt.Columns[Idx].ColumnName))
{
string ColumnName = GetColumnName(Idx + 1);
ColumnName += ":" + ColumnName;
ws.Range[ColumnName].NumberFormat = "@";
}
}
if (ColumnsToMakeCurrency != null)
{
if (ColumnsToMakeCurrency.Contains(dt.Columns[Idx].ColumnName))
{
string ColumnName = GetColumnName(Idx + 1);
ColumnName += ":" + ColumnName;
ws.Range[ColumnName].NumberFormat = "$#,##0.00";
}
}
}
Numbers to Excel Columns
The method GetColumnName() used above was developed to translate from numbers to Excel's column name. For instance, it translates a 1 to a column name of 'A', a 2 to 'B', 26 to 'AA', 27 to 'BB' and so on. I accomplished this by a simple index into a pair of arrays.
private string GetColumnName(int Column)
{
if (Column < 1)
{
return "A";
}
if (Column > 26 * 27)
{
return "ZZ";
}
char[] Alphabet = new char[] { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H'
, 'I', 'J', 'K' , 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S'
, 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
char[] Leader = new char[] { ' ', 'A', 'B', 'C', 'D', 'E', 'F', 'G'
, 'H', 'I', 'J', 'K' , 'L', 'M', 'N', 'O', 'P', 'Q', 'R'
, 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
int IdxLeader = (Column - 1) / 26;
string results = Leader[IdxLeader].ToString()
+ Alphabet[(Column - IdxLeader * 26) - 1].ToString();
return results.Trim();
}
Note: DataTable and C# work relative to zero, while Excel works relative to 1, so the calls to GetColumnName() use "Idx+1"
Excel Column Width
I also had a concern about wide data fields. In some of my production tables, the data could be up to 2000 characters wide. I wanted to limit the width of a column in Excel. On the other hand, I did not want wide columns with empty space in them. The code processes each column, first setting ‘AutoFit’ to set the width to fit all the data, then sets a maximum of 80 points on any column. The result is columns wide enough to hold the data, and wraps at 80 points. I am not sure that measurement this is in but I know it is not pixels.
for (int Idx = 0; Idx < dt.Columns.Count; Idx++)
{
string ColumnName = GetColumnName(Idx + 1);
string RangeName = ColumnName + ":" + ColumnName;
ws.Range[RangeName].EntireColumn.AutoFit();
if (ws.Range[RangeName].ColumnWidth > 80)
{
ws.Range[RangeName].ColumnWidth = 80;
}
}
Multiple Worksheets
I wanted to create several worksheets in the same Excel workbook for data from several tables or databases. To accomplish this, I open Excel once and reuse the Excel for every SQL command. This posed a problem in cases where we closed Excel between these SQL commands. If Excel was closed and another SQL command was issued, then _ExcelApp contained a pointer to Excel which had been closed. To overcome this problem, I trap an Exception on the AddWorksheetToWorkBook(). If this fails, then I assume that Excel was closed open a new Excel. This is not a very good solution, but it is the best I have at this time.
The code to accomplish this is in the click event for the button:
if (_ExcelApp == null)
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_wb = _ExcelApp.Workbooks.Add();
}
Worksheet ws;
try
{
ws = AddWorksheetToWorkbook(_wb, txtWorksheetName.Text);
}
catch (Exception ex)
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_wb = _ExcelApp.Workbooks.Add();
ws = AddWorksheetToWorkbook(_wb, txtWorksheetName.Text);
}
Conclusion
This code is not perfect yet. Error handling and recovery could be a little better and it currently cannot handle parameters for stored procedures. But it is a quick way to prepare Excel documents in support of ad-hoc reports and information.
History
