SQL Query Results as Excel Spreadsheet Mailer Web Service
4.67/5 (15 votes)
Web Service that e-mails results of a SQL Query as Excel Spreadsheet attachments
Introduction
This article describes a Web Service infrastructure that e-mails the results of a given SQL query on a given datasource with as an Excel Spreadsheet attachment. This service can then be used by a controller process that can be set up to run on a scheduled basis providing various reports that can be distributed via e-mail. A typical corporate scenario is when management requests the top N report for the previous day and they want the output to be formatted as an Excel Spreadsheet which they happen to be comfortable with and can also pass around to peers via e-mail. Or perhaps you would like to monitor the hits on your website and could use the numeric functions that Excel can provide to tabulate, group, arrange the data conveniently sent to your inbox on demand or on schedule.
Background
Automating Excel on the server-side is not for the faint hearted. Excel is designed for heavy end-user interaction and is full of modal dialog boxes that can be disastrous in a server side environment where unattended execution is a must. Luckily for us, Office 2000 and higher provides an assortment of components named Office Web Components (OWC for short) that are specifically designed for use with server-side applications. These are free-threaded components completely devoid of modal dialog boxes and various office assistants (remember Clippy ?) that appear to have a habit of popping up every now and then.
We will be using the OWC.SpreadSheet component in this article. The Office Web Components are installed by default with Office XP, however they can also be downloaded from here.
Using the code
The main entry point to the webservice is the SendQueryresultsAsSpreadSheet method which takes the connection string, the SQL query itself and the e-mail address to be notified of the results as parameters. Once the method is called it simply instantiates an ExcelQueryprocessor class and queues it by calling the Threadpool.QueueUserWorkItem method specifying a callback procedure ProcessRequest which the CLR will call with the persisted stateInfo when a thread is available. This implementation allows the Web Service call to return almost instantenously even under heavy load.
[WebMethod]
public void SendQueryResultsAsSpreadSheet(string ConnectString,
string SQLQuery, string EmailAddress)
{
// create our worker class
ExcelQueryProcessor eqp = new ExcelQueryProcessor(
ConnectString,SQLQuery,EmailAddress);
// queue it
ThreadPool.QueueUserWorkItem(new WaitCallback(ProcessRequest),eqp);
}
private void ProcessRequest(Object stateInfo) {
// cast stateInfo back to an ExcelQueryProcessor
ExcelQueryProcessor eqp = (ExcelQueryProcessor)stateInfo;
// off we go ...
eqp.GenerateExcelSheet();
}
We start by adding a reference to the Office Web Components.
The main workhorse of the Web Service is the GenerateExcelSheet method of the ExcelQueryProcessor class. We being by checking whether the e-mail address passed to the service is valid by calling the ValidEmail method passing the e-mail adress as a parameter, which is a simple Regex check that filters out most malformed e-mail addresses. If the e-mail address is determined to be valid we continue by generating a SqlConnection with the connection string that is passed to the Web Service. We use a DataReader for performance reasons and start populating the OWC.Spreadsheet component with the data that returned from the query. This is done in two loops, one for the field names and the other for the data itself.
When the spreadsheet is generated we call the ActiveSheet.Export method of the Spreadheet object to persist it to a local directory. The actual path of this directory is stored in the Web.Config file allowing for flexibility of being able to change it. One point of caution : the ASP.Net Worker process account (MACHINE\ASPNET) must have write permissions to this directory. The filename to be used is generated by calling Guid.NewGuid() method. Other techniques that can assign more meaningful filenames can also be be used, such as counters, datetime stamps etc. After the e-mail is sucessfully sent the XLS file that is generated during the process is deleted to conserve diskspace. Also if an exception is caught such as a malformed query the description of the exception and the call stack is e-mailed to the recipient to help diagnose the error.
using System;
using System.IO;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
namespace Hosca.SQLExcelMail {
public class ExcelQueryProcessor {
string connectString;
string sqlQuery;
string emailAddress;
public ExcelQueryProcessor(string ConnectString, string SQLQuery,
string EmailAddress) {
connectString = ConnectString;
sqlQuery = SQLQuery;
emailAddress = EmailAddress;
}
public void GenerateExcelSheet(){
// is this a valid e-mail address ?
if (ValidEmail(emailAddress)){
try{
// grab a SqlConnection
using (SqlConnection sqlCon = GetSqlConnection(
connectString)) {
SqlCommand sqlCommand = new SqlCommand(sqlQuery,
sqlCon);
// populate DataReader
SqlDataReader dr = sqlCommand.ExecuteReader(
CommandBehavior.CloseConnection);
// create an instance of SpreadSheet Web Control
OWC.Spreadsheet spread = new OWC.SpreadsheetClass();
// reset counters
int rowCount = 0;
rowCount++;
// write out field headers
for(int colCount = 0;colCount < dr.FieldCount;
colCount++){
spread.Cells[rowCount,colCount + 1] =
dr.GetName(colCount);
}
// write out the actual data by looping thru
// the dataReader
while (dr.Read()) {
rowCount++;
for(int colCount = 0;colCount <
dr.FieldCount;colCount++){
// filter out any embedded \r & \n in
// the field data
spread.Cells[rowCount,colCount + 1] =
dr.GetValue(colCount).ToString().
Replace("\r","").Replace("\n","");
}
}
// make sure the output directory path
// terminates with a "\"
string outputFolder =
ConfigurationSettings.AppSettings[
"SpreadSheetDirectory"];
if (!(outputFolder.Substring(outputFolder.Length - 1)
== @"\"))
outputFolder = string.Concat(outputFolder,@"\");
// create a unique filename
string fileName = outputFolder +
Guid.NewGuid().ToString() +".XLS";
// generate the XLS file
spread.ActiveSheet.Export(fileName,0);
// de-reference the SpreadSheet Control
spread = null;
// generate the e-mail body
string emailBody = string.Concat(
DateTime.Now.ToString(),
" - Your query was :<hr>" , sqlQuery);
// send e-mail
SpreadSheetMailer sm = new SpreadSheetMailer(
emailAddress,emailBody,fileName);
sm.SendEMail();
// clean-up the file
File.Delete(fileName);
}
}
catch(Exception ex){
// notify recipient of caught exception
string message = string.Concat(ex.Message,
ex.StackTrace,DateTime.Now.ToString());
SpreadSheetMailer sm = new SpreadSheetMailer(
emailAddress,message,"");
sm.SendEMail();
}
}
}
private bool ValidEmail(string EmailAddress){
// validate e-mail address
// TODO: expand this to handle multiple e-mail
// addresses for group distribution
Regex rx = new Regex(
@"^[\w-]+(?:\.[\w-]+)*@(?:[\w-]+\.)+[a-zA-Z]{2,7}$");
Match m = rx.Match(EmailAddress);
if (m.Success)
return true;
else
return false;
}
private SqlConnection GetSqlConnection(string ConnectString){
SqlConnection sqlCon = new SqlConnection(ConnectString);
try{
// make sure the time-out value specified is acceptable
if (!(sqlCon.ConnectionTimeout == 0)||
(sqlCon.ConnectionTimeout > 60)){
sqlCon.Open();
return sqlCon;
}
else{
throw new Exception(
"Invalid Connection Time Out Value Specified");
}
}
catch (Exception ex){
throw ex;
}
}
}
}
The SpreadSheetMailer object is responsible for sending out the actual e-mail with the Spreadsheet attached. A quick check is made to determine if there is a valid attachment file before the Send method is called. The address of the SMTP server used is also stored in the Web.Config file.
using System;
using System.IO;
using System.Web.Mail;
using System.Configuration;
namespace Hosca.SQLExcelMail
{
public class SpreadSheetMailer
{
string mailRecipient = null;
string mailBody = null;
string mailAttachFile = null;
public SpreadSheetMailer(string Recipient, string Body,
string AttachFile) {
mailRecipient = Recipient;
mailBody = Body;
mailAttachFile = AttachFile;
}
public void SendEMail() {
// get the MailFrom address from Web.Config
string mailFrom =
System.Configuration.ConfigurationSettings.AppSettings[
"MailFrom"];
MailMessage msg = new MailMessage();
// parse out username from e-mail address
msg.From = mailFrom.Substring(0,mailFrom.IndexOf("@"))
+ "<" + mailFrom + ">";
msg.To = mailRecipient + "<" + mailRecipient + ">";
msg.Subject = "SQLExcelMail Query Results";
msg.Priority = MailPriority.Normal;
msg.BodyFormat = MailFormat.Html;
msg.BodyEncoding = System.Text.Encoding.UTF8;
msg.Body = mailBody;
// do we have an attachment and is it valid ?
if ((mailAttachFile.Length > 0) &&
(File.Exists(mailAttachFile))){
msg.Attachments.Add(new MailAttachment(mailAttachFile));
}
// get the SMTP server address from Web.Config
SmtpMail.SmtpServer = ConfigurationSettings.AppSettings[
"SmtpServerName"];
try {
SmtpMail.Send(msg);
}
catch (Exception ex) {
throw ex;
}
}
}
}
Points of interest
This Web Service implementation is meant to be used as a part of a larger framework. This particular implementation allows the end user to specify the connection string to be used to allow for maximum flexibility. In a production environment however this should not be the case. Allowing the requester to specify the connection string opens up the possibility of the requester unknowingly (or willingly) specify the ConnectionTimeout value as zero, which would in theory be a significant drain on the available resources. Also any exceptions that are caught should really be e-mailed to a designated Administrator's inbox.
History
- Oct 5 2003 Initial version (1.0.0)