Export.cs

using System;
using System.Data;
using System.IO;
using System.Text;
using System.Threading;
using System.Xml;
using System.Xml.Xsl;
using be.gaudry.model.enums;
 
namespace be.gaudry.model
{
	# region Summary
 
	/// <summary>
	/// Exports datatable to CSV or Excel format.
	/// This uses DataSet's XML features and XSLT for exporting.
	/// 
	/// C#.Net Example to be used in WebForms
	/// ------------------------------------- 
	/// using MyLib.ExportData;
	/// 
	/// private void btnExport_Click(object sender, System.EventArgs e)
	/// {
	///   try
	///   {
	///     // Declarations
	///     DataSet dsUsers =  ((DataSet) Session["dsUsers"]).Copy( );
	///     MyLib.ExportData.Export oExport = new MyLib.ExportData.Export("Web"); 
	///     string FileName = "UserList.csv";
	///     int[] ColList = {2, 3, 4, 5, 6};
	///     oExport.ExportDetails(dsUsers.Tables[0], ColList, Export.EXPORT_FORMAT.CSV, FileName);
	///   }
	///   catch(Exception Ex)
	///   {
	///     lblError.Text = Ex.Message;
	///   }
	/// }	
	///  
	/// VB.Net Example to be used in WindowsForms
	/// ----------------------------------------- 
	/// Imports MyLib.ExportData
	/// 
	/// Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
	/// 
	///	  Try	
	///	  
	///     'Declarations
	/// 	Dim dsUsers As DataSet = (CType(Session("dsUsers"), DataSet)).Copy()
	/// 	Dim oExport As New MyLib.ExportData.Export("Win")
	/// 	Dim FileName As String = "C:\\UserList.xls"
	/// 	Dim ColList() As Integer = New Integer() {2, 3, 4, 5, 6}			
	///     oExport.ExportDetails(dsUsers.Tables(0), ColList, Export.EXPORT_FORMAT.CSV, FileName)	 
	///     
	///   Catch Ex As Exception
	/// 	lblError.Text = Ex.Message
	///   End Try
	///   
	/// End Sub
	///     
	/// </summary>
 
	# endregion // Summary
 
	public class Export
	{
        public enum APPLICATION_TYPE
        {
            Web,
            Win
        }
        System.Web.HttpResponse response;
		private APPLICATION_TYPE appType;	
 
		public Export()
		{
			appType = APPLICATION_TYPE.Win;
			//response = System.Web.HttpContext.Current.Response;
		}
 
        public Export(APPLICATION_TYPE ApplicationType)
		{
			appType = ApplicationType;
			//if(appType != "Web" && appType != "Win") throw new Exception("Provide valid application format (Web/Win)");
			if (appType == APPLICATION_TYPE.Web) response = System.Web.HttpContext.Current.Response;
		}
 
		#region ExportDetails OverLoad : Type#1
 
		// Function  : ExportDetails 
		// Arguments : DetailsTable, FormatType, FileName
		// Purpose	 : To get all the column headers in the datatable and 
		//			   exorts in CSV / Excel format with all columns
 
		public void ExportDetails(DataTable DetailsTable, BROL_EXPORT_FORMAT FormatType, string FileName)
		{
			try
			{				
				if(DetailsTable.Rows.Count == 0) 
					throw new Exception("There are no details to export.");				
 
				// Create Dataset
				DataSet dsExport = new DataSet("Export");
				DataTable dtExport = DetailsTable.Copy();
				dtExport.TableName = "Values"; 
				dsExport.Tables.Add(dtExport);	
 
				// Getting Field Names
				string[] sHeaders = new string[dtExport.Columns.Count];
				string[] sFileds = new string[dtExport.Columns.Count];
 
				for (int i=0; i < dtExport.Columns.Count; i++)
				{
					sHeaders[i] = dtExport.Columns[i].ColumnName;
					sFileds[i] = dtExport.Columns[i].ColumnName;					
				}
 
                if (appType == APPLICATION_TYPE.Web)
					Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
                else if (appType == APPLICATION_TYPE.Win)
					Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
			}			
			catch(Exception Ex)
			{
				throw Ex;
			}			
		}
 
		#endregion // ExportDetails OverLoad : Type#1
 
		#region ExportDetails OverLoad : Type#2
 
		// Function  : ExportDetails 
		// Arguments : DetailsTable, ColumnList, FormatType, FileName		
		// Purpose	 : To get the specified column headers in the datatable and
		//			   exorts in CSV / Excel format with specified columns
 
		public void ExportDetails(DataTable DetailsTable, int[] ColumnList, BROL_EXPORT_FORMAT FormatType, string FileName)
		{
			try
			{
				if(DetailsTable.Rows.Count == 0)
					throw new Exception("There are no details to export");
 
				// Create Dataset
				DataSet dsExport = new DataSet("Export");
				DataTable dtExport = DetailsTable.Copy();
				dtExport.TableName = "Values"; 
				dsExport.Tables.Add(dtExport);
 
				if(ColumnList.Length > dtExport.Columns.Count)
					throw new Exception("ExportColumn List should not exceed Total Columns");
 
				// Getting Field Names
				string[] sHeaders = new string[ColumnList.Length];
				string[] sFileds = new string[ColumnList.Length];
 
				for (int i=0; i < ColumnList.Length; i++)
				{
					if((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
						throw new Exception("ExportColumn Number should not exceed Total Columns Range");
 
					sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
					sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;					
				}
 
                if (appType == APPLICATION_TYPE.Web)
					Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
                else if (appType == APPLICATION_TYPE.Win)
					Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
			}			
			catch(Exception Ex)
			{
				throw Ex;
			}			
		}
 
		#endregion // ExportDetails OverLoad : Type#2
 
		#region ExportDetails OverLoad : Type#3
 
		// Function  : ExportDetails 
		// Arguments : DetailsTable, ColumnList, Headers, FormatType, FileName	
		// Purpose	 : To get the specified column headers in the datatable and	
		//			   exorts in CSV / Excel format with specified columns and 
		//			   with specified headers
 
		public void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] Headers, BROL_EXPORT_FORMAT FormatType, 
			string FileName)
		{
			try
			{
				if(DetailsTable.Rows.Count == 0)
					throw new Exception("There are no details to export");
 
				// Create Dataset
				DataSet dsExport = new DataSet("Export");
				DataTable dtExport = DetailsTable.Copy();
				dtExport.TableName = "Values"; 
				dsExport.Tables.Add(dtExport);
 
				if(ColumnList.Length != Headers.Length)
					throw new Exception("ExportColumn List and Headers List should be of same length");
				else if(ColumnList.Length > dtExport.Columns.Count || Headers.Length > dtExport.Columns.Count)
					throw new Exception("ExportColumn List should not exceed Total Columns");
 
				// Getting Field Names
				string[] sFileds = new string[ColumnList.Length];
 
				for (int i=0; i < ColumnList.Length; i++)
				{
					if((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
						throw new Exception("ExportColumn Number should not exceed Total Columns Range");
 
					sFileds[i] = dtExport.Columns[ColumnList[i]].ColumnName;					
				}
 
                if (appType == APPLICATION_TYPE.Web)
					Export_with_XSLT_Web(dsExport, Headers, sFileds, FormatType, FileName);
                else if (appType == APPLICATION_TYPE.Win)
					Export_with_XSLT_Windows(dsExport, Headers, sFileds, FormatType, FileName);
			}			
			catch(Exception Ex)
			{
				throw Ex;
			}			
		}
 
		#endregion // ExportDetails OverLoad : Type#3
 
		#region Export_with_XSLT_Web
 
		// Function  : Export_with_XSLT_Web 
		// Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
		// Purpose   : Exports dataset into CSV / Excel format
 
		private void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, BROL_EXPORT_FORMAT FormatType, string FileName)
		{
			try
			{				
				// Appending Headers
				response.Clear();
				response.Buffer= true;
                switch (FormatType)
                {
                    case BROL_EXPORT_FORMAT.CSV:
                        response.ContentType = "text/csv";
                        response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
                        break;
                    case BROL_EXPORT_FORMAT.Excel:
                        response.ContentType = "application/vnd.ms-excel";
                        response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
                        break;
                    default:
                        throw new Exception("Format d'exportation non suporté : " + FormatType.ToString());
                }
 
				// XSLT to use for transforming this dataset.						
				MemoryStream stream = new MemoryStream( );
				XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
 
				CreateStylesheet(writer, sHeaders, sFileds, FormatType);
				writer.Flush( ); 
				stream.Seek( 0, SeekOrigin.Begin); 
 
				XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
                XslCompiledTransform xslTran = new XslCompiledTransform();				
 
				System.IO.StringWriter  sw = new System.IO.StringWriter();
                xslTran.Transform(new XmlTextReader(stream), writer);
 
				//Writeout the Content				
				response.Write(sw.ToString());				
				sw.Close();	
				writer.Close();
				stream.Close();			
				response.End();
			}
			catch(ThreadAbortException Ex)
			{
				string ErrMsg = Ex.Message;
			}
			catch(Exception Ex)
			{
				throw Ex;
			}
		}		
 
		#endregion // Export_with_XSLT 
 
		#region Export_with_XSLT_Windows 
 
		// Function  : Export_with_XSLT_Windows 
		// Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
		// Purpose   : Exports dataset into CSV / Excel format
 
		private void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds, BROL_EXPORT_FORMAT FormatType, string FileName)
		{
			try
			{				
				// XSLT to use for transforming this dataset.						
				MemoryStream stream = new MemoryStream( );
                XmlTextWriter writer = new XmlTextWriter(stream, Encoding.GetEncoding("iso-8859-1"));
 
                if(FormatType!=BROL_EXPORT_FORMAT.CSV && FormatType!=BROL_EXPORT_FORMAT.Excel)
                {
                        throw new Exception("Format d'exportation non suporté : " + FormatType.ToString());
                }
				CreateStylesheet(writer, sHeaders, sFileds, FormatType);
				writer.Flush( ); 
				stream.Seek( 0, SeekOrigin.Begin); 
 
				XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
				XslTransform xslTran = new XslTransform();				
				xslTran.Load(new XmlTextReader(stream), null, null);
 
                System.IO.StringWriter sw = new System.IO.StringWriter();
				xslTran.Transform(xmlDoc, null, sw, null);
 
				//Writeout the Content									
                StreamWriter strwriter = new StreamWriter(FileName,false, Encoding.GetEncoding("iso-8859-1"));
				strwriter.WriteLine(sw.ToString());
				strwriter.Close();
 
				sw.Close();	
				writer.Close();
				stream.Close();	
			}			
			catch(Exception Ex)
			{
				throw Ex;
			}
		}		
 
		#endregion // Export_with_XSLT 
 
		#region CreateStylesheet 
 
		// Function  : WriteStylesheet 
		// Arguments : writer, sHeaders, sFileds, FormatType
		// Purpose   : Creates XSLT file to apply on dataset's XML file 
 
		private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, BROL_EXPORT_FORMAT FormatType)
		{
			try
			{
				// xsl:stylesheet
				string ns = "http://www.w3.org/1999/XSL/Transform";	
				writer.Formatting = Formatting.Indented;
				writer.WriteStartDocument( );				
				writer.WriteStartElement("xsl","stylesheet",ns);
				writer.WriteAttributeString("version","1.0");
				writer.WriteStartElement("xsl:output");
				writer.WriteAttributeString("method","text");
				writer.WriteAttributeString("version","4.0");
				writer.WriteEndElement( );
 
				// xsl-template
				writer.WriteStartElement("xsl:template");
				writer.WriteAttributeString("match","/");
 
				// xsl:value-of for headers
				for(int i=0; i< sHeaders.Length; i++)
				{
					writer.WriteString("\"");
					writer.WriteStartElement("xsl:value-of");
					writer.WriteAttributeString("select", "'" + sHeaders[i].Replace("'"," ") + "'");
					writer.WriteEndElement( ); // xsl:value-of
					writer.WriteString("\"");
					if (i != sFileds.Length - 1) writer.WriteString( (FormatType == BROL_EXPORT_FORMAT.CSV ) ? "," : "	" );
				}
 
				// xsl:for-each
				writer.WriteStartElement("xsl:for-each");
				writer.WriteAttributeString("select","Export/Values");
				writer.WriteString("\r\n");				
 
				// xsl:value-of for data fields
				for(int i=0; i< sFileds.Length; i++)
				{					
					writer.WriteString("\"");
					writer.WriteStartElement("xsl:value-of");
					writer.WriteAttributeString("select", sFileds[i]);
					writer.WriteEndElement( ); // xsl:value-of
					writer.WriteString("\"");
					if (i != sFileds.Length - 1) writer.WriteString( (FormatType == BROL_EXPORT_FORMAT.CSV ) ? "," : "	" );
				}
 
				writer.WriteEndElement( ); // xsl:for-each
				writer.WriteEndElement( ); // xsl-template
				writer.WriteEndElement( ); // xsl:stylesheet
				writer.WriteEndDocument( );					
			}
			catch(Exception Ex)
			{
				throw Ex;
			}
		}
 
		#endregion // WriteStylesheet
 
	}
}