<%@ Page Language="vb" ContentType="application/vnd.ms-excel"%>
<%
   '! Support file only, run AjaxTablePaging.html instead !
   ' This file is used as Export_Url
   ' Generates data to export to Excel
   ' Single file, without using TreeGridFramework.asp

   ' --- Database initialization ---
   Dim Path As String : Path = System.IO.Path.GetDirectoryName(Context.Request.PhysicalPath)
   Dim Conn As System.Data.OleDb.OleDbConnection : Conn = New System.Data.OleDb.OleDbConnection("Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=""" + Path + "\\Database.mdb"";Mode=Share Deny None;Jet OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1")
   Conn.Open()
   Dim Cmd As System.Data.OleDb.OleDbCommand : Cmd = Conn.CreateCommand()
   
 
   ' --- Response initialization ---
   Response.Charset = "utf-8"
   Response.AppendHeader("Cache-Control", "max-age=1, must-revalidate")
   System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
   Dim file As String : file = Request("File") : If file = Nothing Then file = "Export.xls"
   Response.AppendHeader("Content-Disposition", "attachment; filename=""" + file + """")

   ' --- Request read ---   
   Dim XML As String : XML = Request("TGData")
   If XML = Nothing Then XML = "<Grid><Cfg SortCols='Week,Hours' SortTypes='1,0'/><Cols><C Name='Project' Visible='1' Width='200'/><C Name='Resource' Visible='1' Width='150'/><C Name='Week' Visible='1' Width='60'/><C Name='Hours' Visible='1' Width='60'/></Cols></Grid>" ' Just for examples if called directly

   Dim X As System.Xml.XmlDocument : X = New System.Xml.XmlDocument()
   X.LoadXml(HttpUtility.HtmlDecode(XML))
   
   ' --- Parses sorting settings ---
   Dim Cfg As System.Xml.XmlElement : Cfg = CType(X.GetElementsByTagName("Cfg")(0), System.Xml.XmlElement)
   Dim SC() As String : SC = Cfg.GetAttribute("SortCols").Split(",".ToCharArray())
   Dim ST() As String : ST = Cfg.GetAttribute("SortTypes").Split(",".ToCharArray())
   Dim S As String : S = ""
   Dim i As Integer
   If SC(0) <> "" Then
      For i = 0 To SC.Length - 1
         If S <> "" Then S += ", "
         S = S + SC(i)
         If Int32.Parse(ST(i)) >= 1 Then S = S + " DESC"
      Next i
      If S <> "" Then S = " ORDER BY " + S
   End If
   
   ' --- Parses Column position, visibility and width ---
   Dim Cols As System.Xml.XmlNodeList : Cols = X.GetElementsByTagName("C")
   Dim N(4) As String
   Dim p As Integer : p = 0
   Dim W(4) As String
   For Each C As System.Xml.XmlElement In Cols
      If C.GetAttribute("Visible") <> "0" Then
   
         N(p) = C.GetAttribute("Name")
         w(p) = C.GetAttribute("Width")
         p = p + 1
      End If
   Next C


   ' --- Reads data from database ---
   Cmd.CommandText = "SELECT * FROM TableData" + S
   Dim R As System.Data.OleDb.OleDbDataReader : R = Cmd.ExecuteReader()
   
   ' --- Writes Excel settings ---
   Response.Write("<html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns=""http://www.w3.org/TR/REC-html40"">")
   Response.Write("<head><meta http-equiv=Content-Type content=""text/html; charset=utf-8""></head><body>")
   Response.Write("<style>td {white-space:nowrap}</style>")
   Response.Write("<table border=1 bordercolor=silver style='table-layout:fixed;border-collapse:collapse;border:1px solid black'>")
   
   ' --- Writes columns' widths ---
   For i = 0 To p - 1
      Response.Write("<col width='" + w(i) + "'>")
   Next i

   ' --- Writes captions ---
   Response.Write("<tr>")
   For i = 0 To p - 1
      Response.Write("<td style='border-bottom:1px solid black;background:yellow;font-weight:bold;'>" + N(i) + "</td>")
   Next i
   Response.Write("</tr>")
   
   ' --- Writes data ---
   While (R.Read())
      Response.Write("<tr>")
      For i = 0 To p - 1
         If N(i) = "Resource" Or N(i) = "Project" Then ' string
            Response.Write("<td>" + R(N(i)).ToString().Replace("&", "&amp;").Replace("'", "&apos;").Replace("<", "&lt;") + "</td>")
         Else
            Response.Write("<td x:num='" + R(N(i)).ToString() + "'>" + R(N(i)).ToString() + "</td>")
         End If
      Next i
      Response.Write("</tr>")
   End While

   ' ---
   Response.Write("</table></body></html>")
   ' --------------------------------------------------------------------------
%>