WCF Notes

Instance Management

WCF supports three types of instance activation: per-call/Sessionful/singleton service.

[ServiceContract]
interface IMyContract
{...}

[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]
[ServiceBehavior(InstanceContextMode = InstanceContextMode.PerSession)]
[ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall)]
class MyService : IMyContract
{...}

Operations

WCF supports three invocation model: Request-Reply/One-Way/Callback.

[ServiceContract]
interface IMyContract
{
   [OperationContract(IsOneWay = true)]
   void MyMethod( );
}

[ServiceContract(CallbackContract = typeof(ISomeCallbackContract))]
interface IMyContract
{
   [OperationContract]
   void DoSomething( );
}

Fault Contracts

The client can actually encounter three types of errors when trying to invoke the service: communication errors, errors related to the state of the proxy, service-thrown exceptions.

By default, any exception thrown by the service reaches the client as a FaultException.

Transactions

Transaction Flow: Allowed, NotAllowed, Mandatory.

Transaction Protocols

Transaction management protocols: Lightweight, OleTx(RPC), WS-Atomic Transaction (WSAT).

Transaction Managers

The three transaction managers are the Lightweight Transaction Manager (LTM), the Kernel Transaction Manager (KTM), and the Distributed Transaction Coordinator (DTC).

Data Contract Hierarchy in WCF

Data contract class may be the subclass of another data contract class.
Known Types
By default, you cannot use a subclass of a data contract class instead of its base class. The KnownType attribute allows you to designate acceptable subclasses for the data contract.

[DataContract]
[KnownType(typeof(Customer))]
class Contact
{...}

[DataContract]
class Customer : Contact
{...}

Service Known Types


Instead of using the KnownType attribute on the base data contract, when the ServiceKnownType attribute is applied on a specific operation on the service side, then only that operation (across all supporting services) can accept the known subclass.


When the ServiceKnownType attribute is applied at the contract level, all the operations on that contract can accept the known subclass across all implementing services

[ServiceContract]
[ServiceKnownType(typeof(Customer))]
interface IContactManager
{
   [OperationContract]
   void AddContact(Contact contact);

   [OperationContract]
   Contact[] GetContacts( );
}

Configuring Known Types


WCF also lets you configure the known types in the service's or client's config file.

Generics


You cannot define WCF contracts that rely on generic type parameters. Generics are specific to .NET, and using them would violate the service-oriented nature of WCF. However, you can use bounded generic types in your data contracts.

The ABC of WCF Service

A WCF service allows communication through an Endpoint.

ABC is an abbreviation of Address, Binding and Contract attributes of an Endpoint.

A - Where is the endpoint?
B- How to communicate with endpoint?
C - What functionalities do the endpoint provide?

The Binding is an attribute of an endpoint and it lets you configure transport protocol, encoding and security.

Types of Binding

 

basicHttpBinding
This type of binding exists in new .Net world only to support backward compatibility with ASMX based clients (WS-Basic Profile 1.1). Basic http binding sends SOAP 1.1 messages and is used when there is a requirement for the WCF services to communicate with non WCF based systems.

Note: All other bindings except basicHttpBinding support WS* specifications including security, reliable messaging and transaction support, where appropriate.

wsHttpBinding
This binding sends SOAP 1.2 messages and implements WS* specifications to support enterprise requirements of security, reliability, ordered delivery and transaction management.

netTcpBinding
This binding sends SOAP 1.2 messages, provides binary encoding and optimized communication between WCF services and WCF clients on Windows network. This binding is the fastest binding amongst all WCF binding options. Unlike http bindings, the TCP binding does not offer interoperability but is highly optimized for .Net 3.0 and above clients. Thus, in .Net version 3.0 and above, providing an endpoint with netTcpBinding is an easy option to development of distributed systems and can replace COM+ and .Net Remoting model.

…

Bingding	Interoperability	Security	Session	Transactions	Duplex
BasicHttpBinding	Basic Profile 1.1	(None), Transport, Message	None, (None)	None	n/a
WSHttpBinding	WS	Transport, (Message), Mixed	(None), Transport, Reliable Session	(None), Yes	n/a
WSDualHttpBinding	WS	(Message)	(Reliable Session)	(None), Yes	Yes
WSFederationHttpBinding	WS-Federation	(Message)	(None), Reliable Session	(None), Yes	No
NetTcpBinding	.NET	(Transport), Message	Reliable Session, (Transport)	(None), Yes	Yes
NetNamedPipeBinding	.NET	(Transport)	None, (Transport)	(None), Yes	Yes
NetMsmqBinding	.NET	Message, (Transport), Both	(None)	(None), Yes	No
NetPeerTcpBinding	Peer	(Transport)	(None)	(None)	Yes
MsmqIntegrationBinding	MSMQ	(Transport)	(None)	(None), Yes	n/a

Use NPOI to export to Excel spreadsheet in MVC

NPOI is a little known .NET port of POI Java project to create an open source Microsoft Office 97-2003 file reader and writer (without using or having Microsoft Office installed). It’s really easy to create a new Excel sheet from scratch, or just reading from a existing template and populate new cells.

To use NPIO, first include the namespace,

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;

Then you can open a template or create a need sheet:

FileStream fs = new FileStream(templateFile, FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook1 = new HSSFWorkbook(fs, true);
// Create new book
HSSFWorkbook workbook2 = new HSSFWorkbook();

To populate the cell content,

//HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFSheet sheet1 = hssfworkbook.GetSheet("Sheet1");
HSSFRow row = sheet1.CreateRow(rowIndex);
row.CreateCell(cellIndex).SetCellValue(value);

After all done, to export as a Excel file in MVC,

MemoryStream stream = new MemoryStream();
workbook.Write(stream);
return File(stream.ToArray(), "application/vnd.ms-excel", "filename.xls");

Also, Hunter Beanland has a great NPOI Reader/Writer Helper to make reading and writing of Excel files extremely easy.

Excel xr = new Excel;
xr.Open("");
while (xr.ReadRow())
{
    while (xr.CanReadCell())
    {
        lblMessage.Text &= "," & xr.ReadCell().ToString();
    }
    lblMessage.Text &= "
";
}
xr.WriteNewRow();
xr.WriteCell("text");
xr.WriteCell(1234);
xr.WriteCell(DateTime.Now);
xr.WriteCellAsDate(Now, "dd/MM/yy");
xr.FormatLastCell(true, CellHorizontalAlignment.RIGHT, 15, 2, NPOI.HSSF.Util.HSSFColor.BLUE.index, NPOI.HSSF.Util.HSSFColor.OLIVE_GREEN.index, true);
xr.Save("c:\path\sample data_OUT.xls");

Maurits Kammer did a quick VB to C# port of this this helper, full c# source code below:

//Excel XLS BIFF File Reader/Writer - Shell for NPOI 1.2.2 alpha2
//Written by Hunter Beanland: www.beanland.net.au
//ver 1.1 2010-04-07 +TableToXLS, fix ReadCell exception if value is nothing
//ver 1.2 2010-05-04 Fixed IsNothing(xlRow) in ReadCell/CanReadCell
//MKA 2010-04-29> code ported from VB.NET to C# ... thank goodness ;)
//Note: This C# port may not have the very latest changes/fixes which the VB original has.

using System;
using System.Web;
using System.Data;
using System.Collections;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using System.Text.RegularExpressions;
using System.Drawing;

    //Sample Code:
    //Excel xr = new Excel;
    //xr.Open("");
    //while (xr.ReadRow())
    //{
    //    while (xr.CanReadCell())
    //    {
    //        lblMessage.Text &= "," & xr.ReadCell().ToString();
    //    }
    //    lblMessage.Text &= "
";
    //}
    //xr.WriteNewRow();
    //xr.WriteCell("text");
    //xr.WriteCell(1234);
    //xr.WriteCell(DateTime.Now);
    //xr.WriteCellAsDate(Now, "dd/MM/yy");
    //xr.FormatLastCell(true, CellHorizontalAlignment.RIGHT, 15, 2, NPOI.HSSF.Util.HSSFColor.BLUE.index, NPOI.HSSF.Util.HSSFColor.OLIVE_GREEN.index, true);
    //xr.Save("c:\path\sample data_OUT.xls");
    

    /// 

    /// Provides easy to use API for basic Excel 97+ BifF file reading and writing. Exposes the NPOI Excel objects for advanced usage.
    /// 
    /// 
    public class Excel
    {
        public string ErrorMessage;
        /// 

        /// return String.Empty or Nothing if error (such as no more rows/columns)
        /// 
        public bool returnEmptyOnError; 
        public HSSFWorkbook xlWorkbook;
        public HSSFSheet xlSheet;
        public HSSFRow xlRow;
        public HSSFCell xlCell;
        public int CurrentRow, CurrentColumn;

        /// 

        /// Open the XLS file for reading
        /// 
        /// Path to the file to open. Empty/not specified for new file
        /// true if opened successfully
        /// 
        public bool Open(string XlsFilePath)
        {
            if (XlsFilePath == null)
            {
                XlsFilePath = "";
            }

            try
            {
                if (XlsFilePath == String.Empty)
                {
                    xlWorkbook = new HSSFWorkbook();
                    xlSheet = xlWorkbook.CreateSheet();
                }
                else
                {
                    FileStream fs = new FileStream(XlsFilePath, FileMode.Open);
                    using (fs)
                    {
                        xlWorkbook = new HSSFWorkbook(fs);
                        xlSheet = xlWorkbook.GetSheetAt(0);
                        fs.Close();
                    }
                }
                CurrentRow = -1;
                CurrentColumn = -1;
                return true;
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
                //HttpContext.Current.Trace.Warn(ex.ToString());
                return false;
            }

        }


        //MKA: provides default values for Optional params
        public bool ReadRow() 
        {
            return ReadRow(-1);
        }

        /// 

        /// See if the next row has data. return false if end of sheet.
        /// 
        /// if provided, skip to the specified row. 0 based index (Excel iteself is 1 based index).
        /// true if read ok
        /// 
        public bool ReadRow(int Row)
        {
            try
            {
                if (Row > -1)
                {
                    CurrentRow = Row;
                }
                else
                {
                    CurrentRow += 1;
                }
                xlRow = xlSheet.GetRow(CurrentRow);
                if (xlRow == null)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
                return false;
            }
        }

        //MKA: provides default values for Optional params
        public bool CanReadCell()
        {
            return CanReadCell(-1, -1);
        }

        /// 

        /// Tests if we can read the next cell (it exists)
        /// 
        /// if provided, skip to the specified row. 0 based index (Excel iteself is 1 based index).
        /// if provided, skip to the specified column. 0 based index (Excel iteself is 1 based index).
        /// true if the cell exists
        /// 
        public bool CanReadCell(int Row, int Column)
        {
            if (Row > -1)
            {
                xlRow = xlSheet.GetRow(Row);
            }
            if (xlRow == null)
            {
                return false;
            }
            if (Column > -1)
            {
                if (Column > xlRow.PhysicalNumberOfCells - 1)
                {
                    return false;
                }
                else
                if (CurrentColumn + 1 > xlRow.PhysicalNumberOfCells - 1)
                {
                    return false;
                }
            }

            return true;
        }

        //MKA: provides default values for Optional params
        public Object ReadCell()
        {
            return ReadCell(-1, -1);
        }


        /// 

        /// Read the value at the next cell (or at the specified cell). 
        /// 
        /// if provided, read from the specified row. 0 based index (Excel iteself is 1 based index).
        /// if provided, skip to the specified column. 0 based index (Excel iteself is 1 based index).
        /// Value or Nothing/Empty if read error
        /// 
        public Object ReadCell(int Row, int Column)
        {
            if (Row > -1)
            {
                CurrentRow = Row;
                xlRow = xlSheet.GetRow(Row);
            }
            if (xlRow == null)
            {
                return (returnEmptyOnError ? String.Empty : null);
            }
            if (Column > -1)
            {
                CurrentColumn = Column;
            }
            else
            {
                CurrentColumn += 1;
            }
            if (CurrentColumn > xlRow.PhysicalNumberOfCells - 1)
            {
                return (returnEmptyOnError ? String.Empty : null);
            }

            if (xlRow.GetCell(CurrentColumn) == null)
            {
                return String.Empty;
            }
            else
            {
                switch (xlRow.GetCell(CurrentColumn).CellType)
                {
                    case HSSFCellType.STRING:
                    {
                        return xlRow.GetCell(CurrentColumn).StringCellValue;
                    }
                    case HSSFCellType.NUMERIC:
                    {
                        return xlRow.GetCell(CurrentColumn).NumericCellValue;
                    }
                    case HSSFCellType.BOOLEAN:
                    {
                        return xlRow.GetCell(CurrentColumn).BooleanCellValue;
                    }
                    default:
                    {
                        return (returnEmptyOnError ? String.Empty : null);
                    }
                }
            }
        }

        //MKA: provides default values for Optional params
        public int ReadCellAsInteger()
        {
            return ReadCellAsInteger(-1, -1);
        }

        /// 

        /// Read the value at the next cell (or at the specified cell) int type. Strings are attempted to be converted to Integer
        /// 
        /// if provided, read from the specified row. 0 based index (Excel iteself is 1 based index).
        /// if provided, skip to the specified column. 0 based index (Excel iteself is 1 based index).
        /// Value or 0 if read error
        /// 
        public int ReadCellAsInteger(int Row, int Column)
        {
            Object myInteger = ReadCell(Row, Column);
            if (myInteger.GetType() == typeof(Int32))
            {
                return (int)myInteger;
            }
            else if (myInteger.GetType() == typeof(String))
            {
                int xlInteger;
                int.TryParse(myInteger.ToString(), out xlInteger);
                return xlInteger;
            }
            else
            {
                return 0;
            }
        }

        /// 

        /// Read the value at the next cell (or at the specified cell) as Double type. Strings are attempted to be converted to Double
        /// 
        /// if provided, read from the specified row. 0 based index (Excel iteself is 1 based index).
        /// if provided, skip to the specified column. 0 based index (Excel iteself is 1 based index).
        /// Value or 0 if read error
        /// 
        public double ReadCellAsDouble(int Row, int Column)
        {
            if (Row == null)
            {
                Row = -1;
            }
            if (Column == null)
            {
                Column = -1;
            }

            Object dbl = ReadCell(Row, Column);
            if (dbl.GetType() == typeof(Double))
            {
                return (double)dbl;
            }
            else if (dbl.GetType() == typeof(String))
            {
                double xlDouble;
                Double.TryParse(dbl.ToString(), out xlDouble);
                return xlDouble;
            }
            else
            {
                return 0.0;
            }
        }

        //MKA: provides default values for Optional params
        public DateTime ReadCellAsDate()
        {
            return ReadCellAsDate(-1, -1);
        }

        /// 

        /// Read the value at the next cell (or at the specified cell) as DateTime type. Strings are attempted to be converted to DateTime
        /// 
        /// if provided, read from the specified row. 0 based index (Excel iteself is 1 based index).
        /// if provided, skip to the specified column. 0 based index (Excel iteself is 1 based index).
        /// Value or MinValue if read error
        ///    
        public DateTime ReadCellAsDate(int Row, int Column)
        {
            Object dbl = ReadCell(Row, Column);
            if (dbl.GetType() == typeof(Double))
            {
                return xlRow.GetCell(CurrentColumn).DateCellValue;
            }
            else if (dbl.GetType() == typeof(String))
            {
                DateTime xlDate;
                DateTime.TryParse(dbl.ToString(), out xlDate);
                return xlDate;
            }
            else
            {
                return DateTime.MinValue;
            }
        }

        //MKA: provides default values for Optional params
        public bool WriteCell(Object Value)
        {
            return WriteCell(Value, -1, -1);
        }

        /// 

        /// Write the value at the next cell (or at the specified cell). 
        /// 
        /// if provided, read from the specified row. 0 based index (Excel iteself is 1 based index).
        /// if provided, skip to the specified column. 0 based index (Excel iteself is 1 based index).
        /// true if ok
        /// 
        public bool WriteCell(Object Value, int Row, int Column)
        {
            try
            {
                if (Row > -1)
                {
                    CurrentRow = Row;
                    xlRow = xlSheet.GetRow(Row);
                    if (xlRow == null)
                    {
                        xlRow = xlSheet.CreateRow(Row);
                        CurrentColumn = -1;
                    }
                }
                else
                {
                    if (xlRow == null)
                    {
                        if (CurrentRow == -1)
                        {
                            CurrentRow = 0;
                        }
                        xlRow = xlSheet.CreateRow(CurrentRow);
                        CurrentColumn = -1;
                    }
                }
                if (Column > -1)
                {
                    CurrentColumn = Column;
                }
                else
                {
                    CurrentColumn += 1;
                }
                
                if (CurrentColumn > xlRow.PhysicalNumberOfCells - 1)
                {
                    xlCell = xlRow.CreateCell(CurrentColumn);
                }
                if (Value.GetType() == typeof(String))
                {
                    xlCell.SetCellType(HSSFCellType.STRING);
                }
                else if (Value.GetType() == typeof(Int32) || Value.GetType() == typeof(Decimal) || Value.GetType() == typeof(Double))
                {
                    xlCell.SetCellType(HSSFCellType.NUMERIC);
                }
                else if (Value.GetType() == typeof(DateTime))
                {
                    HSSFDataFormat format;
                    format = xlWorkbook.CreateDataFormat();
                    HSSFCellStyle dateStyle;
                    dateStyle = xlWorkbook.CreateCellStyle();
                    dateStyle.DataFormat = format.GetFormat(System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern + " HH:mm:ss");
                    xlCell.CellStyle = dateStyle;
                }
                else if (Value.GetType() == typeof(Boolean))
                {
                    xlCell.SetCellType(HSSFCellType.BOOLEAN);
                }
                xlCell.SetCellValue(Value.ToString());
                return true;
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
                return false;
            }
        }

        //MKA: provides default values for Optional params
        public bool WriteCellAsDate(DateTime Value, string DateTimeFormat)
        {
            return WriteCellAsDate(Value, DateTimeFormat, -1, -1);
        }

        /// 

        /// Write the date/teime value at the next cell (or at the specified cell) with the specified format. 
        /// 
        /// Date/Time value
        /// Format to display the date/time in. ie: "dd/MM/yyyy HH:mm:ss AM/PM"
        /// if provided, read from the specified row. 0 based index (Excel iteself is 1 based index).
        /// if provided, skip to the specified column. 0 based index (Excel iteself is 1 based index).
        /// true if ok
        /// 
        public bool WriteCellAsDate(DateTime Value, string DateTimeFormat, int Row, int Column)
        {
            try
            {
                if (Row > -1)
                {
                    CurrentRow = Row;
                    xlRow = xlSheet.GetRow(Row);
                    if (xlRow == null)
                    {
                        xlRow = xlSheet.CreateRow(Row);
                        CurrentColumn = -1;
                    }
                }
                else
                {
                    if (xlRow == null)
                    {
                        if (CurrentRow == -1)
                        {
                            CurrentRow = 0;
                        }
                        xlRow = xlSheet.CreateRow(CurrentRow);
                        CurrentColumn = -1;
                    }
                }
                if (Column > -1)
                {
                    CurrentColumn = Column;
                }
                else
                {
                    CurrentColumn += 1;
                }
                if (CurrentColumn > xlRow.PhysicalNumberOfCells - 1)
                {
                    xlCell = xlRow.CreateCell(CurrentColumn);
                }
                if (String.IsNullOrEmpty(DateTimeFormat))
                {
                    DateTimeFormat = System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern + " HH:mm:ss";
                }
                
                HSSFDataFormat format;
                format = xlWorkbook.CreateDataFormat();
                HSSFCellStyle dateStyle;
                dateStyle = xlWorkbook.CreateCellStyle();
                dateStyle.DataFormat = format.GetFormat(DateTimeFormat);
                xlCell.SetCellType(HSSFCellType.NUMERIC);
                xlCell.CellStyle = dateStyle;
                xlCell.SetCellValue(Value);
                return true;
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
                return false;
            }
        }

        /// 

        /// Set the cell style of the last cell
        /// 
        /// Bold font. Default = false
        /// Cell alignment. Default = HSSFCellStyle.ALIGN_GENERAL
        /// Cell Width. Default = 10
        /// Cell span. Default = 1
        /// Font colour. Default = NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index
        /// Background colour. Default = NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index
        /// Thin black border. Default = false
        /// true if ok
        /// 
        public bool FormatLastCell(bool Bold, CellHorizontalAlignment Align, int Width, int CellSpan, short FGColour, short BGColour, bool Border)
        {
            HSSFCellStyle xlStyle = xlCell.CellStyle;
            if (xlStyle == null)
            {
                xlStyle = xlWorkbook.CreateCellStyle();
            }
            if (Bold || (FGColour != NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index))
            {
                HSSFFont xlFont = xlWorkbook.CreateFont();
                if (FGColour != NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index)
                {
                    xlFont.Color = FGColour;
                }
                if (Bold)
                {
                    xlFont.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
                }
                xlStyle.SetFont(xlFont);
            }
            if (BGColour != NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index)
            {
                if (BGColour != NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index)
                {
                    xlStyle.FillForegroundColor = BGColour;
                }
                xlStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;
            }
            if (Border)
            {
                xlStyle.BorderTop = CellBorderType.THIN;
                xlStyle.BorderLeft = CellBorderType.THIN;
                xlStyle.BorderRight = CellBorderType.THIN;
                xlStyle.BorderBottom = CellBorderType.THIN;
            }
            if (Align != CellHorizontalAlignment.GENERAL)
            {
                xlStyle.Alignment = Align;
            }
            xlCell.CellStyle = xlStyle;
            if (CellSpan > 1)
            {
                xlSheet.AddMergedRegion(new NPOI.HSSF.Util.Region(CurrentRow, CurrentColumn, CurrentRow, CurrentColumn + CellSpan));
            }
            xlSheet.SetColumnWidth(CurrentColumn, Width * 256);

            return true;
        }

        /// 

        /// Creates a new row for writing. Use this if using WriteCell with no row/column specified.
        /// 
        /// true if ok
        /// 
        public bool WriteNewRow()
        {
            CurrentRow += 1;
            try
            {
                xlRow = xlSheet.CreateRow(CurrentRow);
                CurrentColumn = -1;
                return true;
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
                return false;
            }
        }

        /// 

        /// Save the workbook to a new file
        /// 
        /// Absolute path and filename for the .xls
        /// true if saved
        /// 
        public bool Save(string XlsFilePath)
        {
            try
            {
                if (File.Exists(XlsFilePath))
                {
                    File.Delete(XlsFilePath);
                }
                
                FileStream fs = new FileStream(XlsFilePath, FileMode.Create);

                using (fs)
                {
                    xlWorkbook.Write(fs);
                    fs.Flush();
                    fs.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
                //HttpContext.Current.Trace.Warn(ex.ToString());
                return false;
            }
        }

        /// 

        /// Save a Table to an Excel 97+ BifF .xls file
        /// 
        /// Source table
        /// Source column name order. Nothing if auto order (per table). Required if SourceColText specified
        /// Source column header text. Nothing if auto names (per table). Required if SourceColOrder specified
        /// File Name with absolute path to save to file, or just filename for immediate stream to Response
        /// Write column names
        /// true if successful
        /// 
        public bool TableToXLS(DataTable SourceData, string[] SourceColOrder, string[] SourceColText, bool IncludeHeader, string FileToSave)
        {
            try
            {
                if (System.IO.File.Exists(FileToSave))
                {
                    System.IO.File.Delete(FileToSave);
                }
            }
            catch
            {
                //HttpContext.Current.Trace.Warn("Can not delete previous file");
            }
            
            FileInfo fiSave = new FileInfo(FileToSave);
            string SheetName = fiSave.Name.Substring(0, fiSave.Name.IndexOf("."));
            SheetName = Regex.Replace(SheetName, "[^A-Z]", String.Empty, RegexOptions.IgnoreCase);
            bool blnNoError = true;

            xlWorkbook = new HSSFWorkbook();

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //dsi.Company = "{company name here}";
            xlWorkbook.DocumentSummaryInformation = dsi;
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Title = SheetName;
            //si.Author = "{user name here}";
            //si.ApplicationName = "{application's name}";
            xlWorkbook.SummaryInformation = si;

            xlSheet = xlWorkbook.CreateSheet(SheetName);
            xlSheet.DisplayGridlines = false;          
            HSSFCellStyle xlStyle;
            int[] aryWidths = new int[SourceData.Columns.Count - 1];
            CurrentRow = 0;

            //Write header
            if (IncludeHeader)
            {
                xlRow = xlSheet.CreateRow(CurrentRow);
                if (SourceColText == null)
                {
                    //Auto names
                    SourceColOrder = new string[SourceData.Columns.Count - 1];
                    foreach (DataColumn dcCol in SourceData.Columns)
                    {
                        SourceColOrder[CurrentColumn] = dcCol.ColumnName;
                        xlCell = xlRow.CreateCell(CurrentColumn);
                        xlStyle = xlWorkbook.CreateCellStyle();
                        HSSFFont xlFont = xlWorkbook.CreateFont();
                        xlFont.Color = NPOI.HSSF.Util.HSSFColor.WHITE.index;
                        xlFont.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
                        xlStyle.SetFont(xlFont);
                        xlStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index;
                        xlStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;
                        xlCell.SetCellValue(dcCol.ColumnName.Replace("_", " "));
                        xlCell.CellStyle = xlStyle;
                        if (dcCol.ColumnName.Length + 1 > aryWidths[CurrentColumn])
                        {
                            aryWidths[CurrentColumn] = dcCol.ColumnName.Length + 1;
                        }
                        CurrentColumn += 1;
                    }
                }
                else
                {
                    //Specified names
                    foreach (string strName in SourceColText)
                    {
                        xlCell = xlRow.CreateCell(CurrentColumn);
                        xlStyle = xlWorkbook.CreateCellStyle();
                        HSSFFont xlFont = xlWorkbook.CreateFont();
                        xlFont.Color = NPOI.HSSF.Util.HSSFColor.WHITE.index;
                        xlFont.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
                        xlStyle.SetFont(xlFont);
                        xlStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index;
                        xlStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;
                        xlCell.SetCellValue(strName);
                        xlCell.CellStyle = xlStyle;
                        if (strName.Length + 1 > aryWidths[CurrentColumn])
                        {
                            aryWidths[CurrentColumn] = strName.Length + 1;
                        }
                        CurrentColumn += 1;
                    }
                }
            }

            if (SourceColOrder == null)
            {
                //Get column names if not specified
                CurrentColumn = 0;
                SourceColOrder = new string[SourceData.Columns.Count - 1];
                foreach (DataColumn dcCol in SourceData.Columns)
                {
                    SourceColOrder[CurrentColumn] = dcCol.ColumnName;
                    CurrentColumn += 1;
                }
            }

            //Write data rows
            Object SourceDataCell;

            foreach (DataRow drvRow in SourceData.Rows)
            {
                CurrentRow += 1;
                CurrentColumn = 0;
                xlRow = xlSheet.CreateRow(CurrentRow);
                foreach (string strColName in SourceColOrder) //For CurrentColumn = 0 To SourceData.Columns.Count - 1
                {
                    SourceDataCell = drvRow[strColName];
                    xlCell = xlRow.CreateCell(CurrentColumn);
                    xlStyle = xlWorkbook.CreateCellStyle();
                    xlStyle.BorderLeft = CellBorderType.THIN;
                    xlStyle.BorderRight = CellBorderType.THIN;
                    xlStyle.BorderBottom = CellBorderType.THIN;
                    xlCell.CellStyle = xlStyle;
                    if (SourceDataCell.GetType() == typeof(System.Int32) || SourceDataCell.GetType() == typeof(System.Int64))
                    {
                        xlCell.SetCellType(NPOI.HSSF.UserModel.HSSFCellType.NUMERIC);
                        if (SourceDataCell != null)  //IsDBNull
                        {
                            xlCell.SetCellValue((double)SourceDataCell);
                        }
                    }
                    else if (SourceDataCell.GetType() == typeof(System.DateTime))
                    {
                        xlCell.SetCellType(NPOI.HSSF.UserModel.HSSFCellType.STRING);
                        if (SourceDataCell != null)  //IsDBNull
                        {
                            xlCell.SetCellValue((DateTime)SourceDataCell);
                        }
                    }
                    else if (SourceDataCell.GetType() == typeof(System.Double) || SourceDataCell.GetType() == typeof(System.Decimal))
                    {
                        xlCell.SetCellType(NPOI.HSSF.UserModel.HSSFCellType.NUMERIC);
                        if (SourceDataCell != null)  //IsDBNull
                        {
                            xlCell.SetCellValue((double)SourceDataCell);
                        }
                    }
                    else
                    {
                        xlCell.SetCellValue(SourceDataCell.ToString());
                    }
                    if (SourceDataCell.ToString().Length > aryWidths[CurrentColumn])
                    {
                        aryWidths[CurrentColumn] = SourceDataCell.ToString().Length;
                    }
                    CurrentColumn += 1;
               }
            }

            //Set column widths
            for (CurrentColumn = 0; CurrentColumn <= aryWidths.GetUpperBound(0); CurrentColumn++)
            {
                if (aryWidths[CurrentColumn] > 50)
                {
                    xlSheet.SetColumnWidth(CurrentColumn, 50 * 256);
                }
                else if (aryWidths[CurrentColumn] < 10)
                {
                    xlSheet.SetColumnWidth(CurrentColumn, 10 * 256);
                }
                else
                {
                    xlSheet.SetColumnWidth(CurrentColumn, (aryWidths[CurrentColumn] + 1) * 256);
                }
            }

            try
            {
                if (FileToSave.IndexOf(":") < 1)
                {
                    //just filename to stream to browser and force it to download the file (and launch Excel or save, not display in the browser)
                    MemoryStream ms = new MemoryStream();
                    xlWorkbook.Write(ms);
                    //HttpContext.Current.Response.Clear();
                    //HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + FileToSave);
                    //HttpContext.Current.Response.AddHeader("Content-Length", ms.Length.ToString());
                    //HttpContext.Current.Response.ContentType = "application/octet-stream";
                    //HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
                    //HttpContext.Current.Response.Flush();
                    //HttpContext.Current.Response.End();
                }
                else
                {
                    FileStream fs = new FileStream(FileToSave, FileMode.Create);
                    using (fs)
                    {
                        xlWorkbook.Write(fs);
                        fs.Close();
                    }
                }

            }
            catch (Exception ex)
            {
                blnNoError = false;
                ErrorMessage = ex.Message;
                //HttpContext.Current.Trace.Warn(ex.ToString());
            }
            
            return blnNoError;
        }

        private short GetXLColour(System.Drawing.Color SystemColour)
        {
            //Just map the colours we usually use in our reports to Excel colours - add yours here!

            //MKA> killed select case construct here, C# cant handle switch on complex types

            if (SystemColour == Color.White)
            {
                return NPOI.HSSF.Util.HSSFColor.WHITE.index;
            }
            else if (SystemColour == Color.DarkBlue)
            {
                return NPOI.HSSF.Util.HSSFColor.DARK_BLUE.index;
            }
            else if (SystemColour == Color.Gray)
            {
                return NPOI.HSSF.Util.HSSFColor.GREY_50_PERCENT.index;
            }
            //else
            //{
                return NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index;
            //}

        }

    }