/* EXPORTS DATA VIEWS LIST INTO EXCEL
 * modified from [http://www.codeproject.com/dotnet/ExportToExcel.asp]
 * 
 *  1. source - is an array list containing multiple DataView that is	filled with 
 *	  data you want to save.	You need to sort/filter this view before you	pass
 *	  it	to this function, because all	it does is just saving it. 
 *		 * 
 *  2. Auto replaced string literals that needs escaping (XML character escapes)
 *	  as	specified	on [http://hdf.ncsa.uiuc.edu/HDF5/XML/xml_escape_chars.htm]
 * 
 *  3. Multiple DataView	will	be saved into a single file as multiple	'tabs' 
 *	  (multiple Worksheets in Excel). The name for the sheets will be based off
 *	  DataView.Table.TableName so	prior to setting the DataView, set	the name
 *	  of	your	DataTable. 
 * 
 *  - zen	sugiarto 6 May	2007	- 
 */
public static void exportToExcel(ArrayList source, string fileName)
{
	System.IO.StreamWriter excelDoc;

	excelDoc = new	System.IO.StreamWriter(fileName);
	const string startExcelXML = "<xml	version=\"1.0\">\r\n<Workbook	" + 
			  "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +	
			  " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n	" + 
			  "xmlns:x=\"urn:schemas-    microsoft-com:office:" + 
			  "excel\"\r\n	xmlns:ss=\"urn:schemas-microsoft-com:" + 
			  "office:spreadsheet\">\r\n <Styles>\r\n " + 
			  "<Style	ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " + 
			  "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>"	+ 
			  "\r\n <Font/>\r\n	<Interior/>\r\n <NumberFormat/>" +	
			  "\r\n <Protection/>\r\n </Style>\r\n " + 
			  "<Style	ss:ID=\"BoldColumn\">\r\n <Font " + 
			  "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n	" + 
			  "<Style	    ss:ID=\"StringLiteral\">\r\n <NumberFormat" +	
			  " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +	
			  "ss:ID=\"Decimal\">\r\n <NumberFormat	" + 
			  "ss:Format=\"0.0000\"/>\r\n	</Style>\r\n "	+ 
			  "<Style	ss:ID=\"Integer\">\r\n <NumberFormat " + 
			  "ss:Format=\"0\"/>\r\n	</Style>\r\n <Style	" + 
			  "ss:ID=\"DateLiteral\">\r\n	<NumberFormat " + 
			  "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " + 
			  "</Styles>\r\n ";
	const string endExcelXML	= "</Workbook>";

	int rowCount =	0;
	int sheetCount	= 1;
	/*
		<xml	version>
		<Workbook	xmlns="urn:schemas-microsoft-com:office:spreadsheet"
		xmlns:o="urn:schemas-microsoft-com:office:office"
		xmlns:x="urn:schemas-microsoft-com:office:excel"
		xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
		<Styles>
		<Style ss:ID="Default" ss:Name="Normal">
			<Alignment ss:Vertical="Bottom"/>
			<Borders/>
			<Font/>
			<Interior/>
			<NumberFormat/>
			<Protection/>
		</Style>
		<Style ss:ID="BoldColumn">
			<Font x:Family="Swiss" ss:Bold="1"/>
		</Style>
		<Style ss:ID="StringLiteral">
			<NumberFormat ss:Format="@"/>
		</Style>
		<Style ss:ID="Decimal">
			<NumberFormat ss:Format="0.0000"/>
		</Style>
		<Style ss:ID="Integer">
			<NumberFormat ss:Format="0"/>
		</Style>
		<Style ss:ID="DateLiteral">
			<NumberFormat ss:Format="mm/dd/yyyy;@"/>
		</Style>
		</Styles>
		<Worksheet ss:Name="Sheet1">
		</Worksheet>
		</Workbook>
	*/

	// write XML header
	excelDoc.Write(startExcelXML);


	DataView dv;
	for(int wscount=0; wscount < source.Count; wscount++ )
	{	
		dv =	(DataView)source[wscount];
		String tableName=dv.Table.TableName;
		if( tableName==null	|| tableName=="")
			tableName="Sheet"+wscount+1;

		excelDoc.Write("<Worksheet ss:Name=\"" + tableName + "\">");
		excelDoc.Write("<Table>");
		excelDoc.Write("<Row>");
		
		for(int x	= 0;	x < dv.Table.Columns.Count; x++)
		{
			excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
			excelDoc.Write(dv.Table.Columns[x].ColumnName);
			excelDoc.Write("</Data></Cell>");
		}
		excelDoc.Write("</Row>");


		// writing each row
		IEnumerator iterator = dv.GetEnumerator();
		
		//foreach(DataRow x	in source.Tables[wscount].Rows)
		while( iterator.MoveNext() )
		{
			DataRow x	= ((DataRowView)iterator.Current).Row;
			rowCount++;
			//if	the number of rows is > 64000	create a new page to continue	output
			if(rowCount==64000)	
			{
				rowCount = 0;
				sheetCount++;
				excelDoc.Write("</Table>");
				excelDoc.Write(" </Worksheet>");
				excelDoc.Write("<Worksheet ss:Name=\"" + tableName + "." + sheetCount	+ "\">");
				excelDoc.Write("<Table>");
			}
			excelDoc.Write("<Row>");	//ID=" + rowCount +	"
			for(int y	= 0;	y < dv.Table.Columns.Count; y++)
			{
				System.Type rowType;
				rowType =	x[y].GetType();
				switch(rowType.ToString())
				{
					case	"System.String":
						string XMLstring = x[y].ToString();
						XMLstring	= XMLstring.Trim();
						XMLstring	= XMLstring.Replace("&","&amp;");
						XMLstring	= XMLstring.Replace(">","&gt;");
						XMLstring	= XMLstring.Replace("<","&lt;");
						XMLstring	= XMLstring.Replace("\'","&apos;");
						XMLstring	= XMLstring.Replace("\"","\\\"");
						XMLstring	= XMLstring.Replace("\\","\\\\"); 
						excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +	
							"<Data ss:Type=\"String\">");
						excelDoc.Write(XMLstring);
						excelDoc.Write("</Data></Cell>");
						break;
					case	"System.DateTime":
						//Excel has a specific Date Format	of YYYY-MM-DD followed by  
						//the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
						//The Following Code puts the	date	stored in	XMLDate 
						//to	the format above
						DateTime XMLDate = (DateTime)x[y];
						string XMLDatetoString =	""; //Excel Converted Date
						XMLDatetoString = XMLDate.Year.ToString() +
							"-" + 
							(XMLDate.Month	< 10	? "0" + 
							XMLDate.Month.ToString()	: XMLDate.Month.ToString()) +
							"-" +
							(XMLDate.Day <	10 ?	"0" + 
							XMLDate.Day.ToString() :	XMLDate.Day.ToString()) +
							"T" +
							(XMLDate.Hour < 10 ? "0"	+ 
							XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
							":" +
							(XMLDate.Minute < 10 ? "0" + 
							XMLDate.Minute.ToString() : XMLDate.Minute.ToString())	+
							":" +
							(XMLDate.Second < 10 ? "0" + 
							XMLDate.Second.ToString() : XMLDate.Second.ToString())	+ 
							".000";
						excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" + 
							"<Data ss:Type=\"DateTime\">");
						excelDoc.Write(XMLDatetoString);
						excelDoc.Write("</Data></Cell>");
						break;
					case	"System.Boolean":
						excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +	
							"<Data ss:Type=\"String\">");
						excelDoc.Write(x[y].ToString());
						excelDoc.Write("</Data></Cell>");
						break;
					case	"System.Int16":
					case	"System.Int32":
					case	"System.Int64":
					case	"System.Byte":
						excelDoc.Write("<Cell ss:StyleID=\"Integer\">" + 
							"<Data ss:Type=\"Number\">");
						excelDoc.Write(x[y].ToString());
						excelDoc.Write("</Data></Cell>");
						break;
					case	"System.Decimal":
					case	"System.Double":
						excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" + 
							"<Data ss:Type=\"Number\">");
						excelDoc.Write(x[y].ToString());
						excelDoc.Write("</Data></Cell>");
						break;
					case	"System.DBNull":
						excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +	
							"<Data ss:Type=\"String\">");
						excelDoc.Write("");
						excelDoc.Write("</Data></Cell>");
						break;
					default:
						throw(new	Exception(rowType.ToString() + " not handled."));
				}
			}
			excelDoc.Write("</Row>");
		}
		excelDoc.Write("</Table>");
		excelDoc.Write(" </Worksheet>");
	}

	excelDoc.Write(endExcelXML);
	excelDoc.Close();
}