dim sHidden as string = trim(Request.QueryString("hidden"))\r
if not IsNumeric(sRequestOffset) then sRequestOffset="0"\r
\r
+ if sRequestOffset<>"" then sRequestOffset=Regex.Replace( sRequestOffset, "[^0-9-]", "" )\r
if sRequestOffset<>"" then offset=CLng(sRequestOffset)\r
+ if sRequestSize<>"" then sRequestSize=Regex.Replace( sRequestSize, "[^0-9-]", "" )\r
if sRequestSize<>"" then numrows=CLng(sRequestSize)\r
if sDistinct<>"" then distinctCol=CLng(sDistinct)\r
if sEdit<>"" then editCol=CLng(sEdit)\r
SendHdg=true\r
case "xl":\r
Response.ContentType="application/vnd.ms-excel"\r
- writer.WriteLine("<html><head></head><body>")\r
- closetags="</body></html>"\r
- RowsStart=vbLf & "<table>"\r
- RowsEnd=vbLf & "</table>"\r
+ Response.AddHeader("Content-Disposition", "attachment; filename=" & RequestId & ".xml")\r
+ writer.WriteLine("<?xml version='1.0' encoding='iso-8859-1'?>")\r
+ writer.WriteLine("<?mso-application progid='Excel.Sheet'?>")\r
+ writer.WriteLine("<s:Workbook xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:s='urn:schemas-microsoft-com:office:spreadsheet'>")\r
+ writer.WriteLine(" <s:Styles>")\r
+ writer.WriteLine(" <s:Style s:ID='sDate'><s:NumberFormat s:Format='Short Date' /></s:Style>")\r
+ writer.WriteLine(" </s:Styles>")\r
+ writer.WriteLine(" <s:Worksheet s:Name='" & RequestId & "'>")\r
+ closetags="</s:Worksheet></s:Workbook>"\r
+ RowsStart=vbLf & "<s:Table>"\r
+ RowsEnd=vbLf & "</s:Table>"\r
+\r
gettotal=false\r
sendDebugMsgs=false\r
- SendHdg=true\r
+ AllRowsMax=65534 ' allow 1 row for heading\r
case "json":\r
Response.ContentType="application/json"\r
writer.Write("{" & vbLf & """id"":""" & RequestId & """")\r
\r
' returns the total number of rows produced by the query (or -1 if unknown)\r
Protected Function RenderQueryRows(writer as HTMLTextWriter) As Integer\r
- dim rowcnt as integer, fldNum as integer, dbDate as DateTime, strFieldItem as String\r
+ dim rowcnt as integer, fldNum as integer, dbDate as DateTime, strFieldItem as String, fldType as String, fldAttr as String\r
dim firstCol as Integer=0, limitQuery as Boolean=false, eof as Boolean=false, n as String\r
dim rdr as object\r
dim totcnt as Integer=0\r
oParse.ParseSelect(sqlText)\r
ApplyQStringParms()\r
sqlText=oParse.UnparseSelect()\r
- elseif numrows < 0 then\r
+ elseif numrows < 0 or offset=0 then\r
ApplyQStringParms()\r
sqlText=oParse.UnparseSelectSkip(HiddenCols)\r
else\r
\r
rowcnt=0\r
if numrows < 0 then numrows=AllRowsMax\r
- if fmt="json" then\r
- if SendHdg then\r
- writer.Write(vbLf & "[")\r
- for fldNum=firstCol to rdr.FieldCount -1\r
- if IsNothing(oParse) then\r
- n=Nothing\r
- else\r
- n=oParse.Headings(fldNum-firstCol)\r
- end if\r
- if IsNothing(n) then n=rdr.GetName(fldNum)\r
- writer.Write("""" & escapeJSON(n) & """")\r
- next\r
- writer.Write("]")\r
- end if\r
- while (rowcnt < numrows) and (not eof)\r
- if rdr.Read() then\r
- if rowcnt > 0 or SendHdg then writer.Write(",")\r
+ select case fmt\r
+\r
+ case "json":\r
+ if SendHdg then\r
writer.Write(vbLf & "[")\r
- for fldNum = firstCol to rdr.FieldCount -1\r
- strFieldItem = ""\r
- if not rdr.IsDBNull(fldNum) then\r
- select case rdr.GetFieldType(fldNum).Name\r
- case "DateTime":\r
- dbDate=rdr.GetDateTime(fldNum)\r
- strFieldItem = replace(dbDate.ToString("s"),"T"," ") ' convert to ISO-8601 format\r
- case else:\r
- strFieldItem = escapeJSON(rdr.GetValue(fldNum))\r
- end select\r
+ for fldNum=firstCol to rdr.FieldCount -1\r
+ if IsNothing(oParse) then\r
+ n=Nothing\r
+ else\r
+ n=oParse.Headings(fldNum-firstCol)\r
end if\r
- if fldNum > firstCol then writer.Write(",")\r
- writer.Write("""" & strFieldItem & """")\r
+ if IsNothing(n) then n=rdr.GetName(fldNum)\r
+ writer.Write("""" & escapeJSON(n) & """")\r
next\r
writer.Write("]")\r
- rowcnt += 1\r
- else\r
- eof=true\r
end if\r
- end while\r
- else\r
- if SendHdg then\r
- writer.Write(vbLf & "<tr>")\r
- for fldNum=firstCol to rdr.FieldCount -1\r
+ while (rowcnt < numrows) and (not eof)\r
+ if rdr.Read() then\r
+ if rowcnt > 0 or SendHdg then writer.Write(",")\r
+ writer.Write(vbLf & "[")\r
+ for fldNum = firstCol to rdr.FieldCount -1\r
+ strFieldItem = ""\r
+ if not rdr.IsDBNull(fldNum) then\r
+ select case rdr.GetFieldType(fldNum).Name\r
+ case "DateTime":\r
+ dbDate=rdr.GetDateTime(fldNum)\r
+ strFieldItem = replace(dbDate.ToString("s"),"T"," ") ' convert to ISO-8601 format\r
+ case else:\r
+ strFieldItem = escapeJSON(rdr.GetValue(fldNum))\r
+ end select\r
+ end if\r
+ if fldNum > firstCol then writer.Write(",")\r
+ writer.Write("""" & strFieldItem & """")\r
+ next\r
+ writer.Write("]")\r
+ rowcnt += 1\r
+ else\r
+ eof=true\r
+ end if\r
+ end while\r
+\r
+ case "xl":\r
+ writer.Write(vbLf & "<s:Row>")\r
+ for fldNum=firstCol to rdr.FieldCount-1\r
if IsNothing(oParse) then\r
n=Nothing\r
else\r
n=oParse.Headings(fldNum-firstCol)\r
end if\r
if IsNothing(n) then n=rdr.GetName(fldNum)\r
- writer.Write("<td>" & server.HTMLEncode(n) & "</td>")\r
+ writer.Write("<s:Cell><s:Data s:Type='String'>" & server.HTMLEncode(n) & "</s:Data></s:Cell>")\r
next\r
- writer.Write("</tr>")\r
- end if\r
- while (rowcnt < numrows) and (not eof)\r
- if rdr.Read() then\r
- rowcnt += 1\r
- writer.Write("<tr>")\r
- for fldNum = firstCol to rdr.FieldCount -1\r
- strFieldItem = ""\r
- if not rdr.IsDBNull(fldNum) then\r
- select case rdr.GetFieldType(fldNum).Name\r
- case "DateTime":\r
- dbDate=rdr.GetDateTime(fldNum)\r
- strFieldItem = replace(dbDate.ToString("s"),"T"," ") ' convert to ISO-8601 format\r
- case else:\r
- strFieldItem = server.HTMLEncode(rdr.GetValue(fldNum))\r
- end select\r
+ writer.Write("</s:Row>")\r
+ while (rowcnt < numrows) and (not eof)\r
+ if rdr.Read() then\r
+ rowcnt += 1\r
+ writer.Write("<s:Row>")\r
+ for fldNum = firstCol to rdr.FieldCount -1\r
+ strFieldItem = ""\r
+ fldAttr = ""\r
+ fldType = "String"\r
+ if not rdr.IsDBNull(fldNum) then\r
+ select case UCase(Left(rdr.GetFieldType(fldNum).Name, 3))\r
+ case "DAT":\r
+ dbDate=rdr.GetDateTime(fldNum)\r
+ strFieldItem = dbDate.ToString("s") ' convert to ISO-8601 format\r
+ fldType = "DateTime"\r
+ fldAttr = " s:StyleID='sDate'"\r
+ case "INT", "DOU", "DEC":\r
+ strFieldItem = CStr(rdr.GetValue(fldNum))\r
+ fldType = "Number"\r
+ case else:\r
+ strFieldItem = server.HTMLEncode(rdr.GetValue(fldNum))\r
+ end select\r
+ end if\r
+ writer.Write("<s:Cell" & fldAttr & "><s:Data s:Type='" & fldType & "'>" & strFieldItem & "</s:Data></s:Cell>")\r
+ next\r
+ writer.Write("</s:Row>")\r
+ else\r
+ eof=true\r
+ end if\r
+ end while\r
+\r
+ case else:\r
+ if SendHdg then\r
+ writer.Write(vbLf & "<tr>")\r
+ for fldNum=firstCol to rdr.FieldCount -1\r
+ if IsNothing(oParse) then\r
+ n=Nothing\r
+ else\r
+ n=oParse.Headings(fldNum-firstCol)\r
end if\r
- writer.Write("<td>" & strFieldItem & "</td>")\r
+ if IsNothing(n) then n=rdr.GetName(fldNum)\r
+ writer.Write("<td>" & server.HTMLEncode(n) & "</td>")\r
next\r
writer.Write("</tr>")\r
- else\r
- eof=true\r
end if\r
- end while\r
- end if\r
+ while (rowcnt < numrows) and (not eof)\r
+ if rdr.Read() then\r
+ rowcnt += 1\r
+ writer.Write("<tr>")\r
+ for fldNum = firstCol to rdr.FieldCount -1\r
+ strFieldItem = ""\r
+ if not rdr.IsDBNull(fldNum) then\r
+ select case rdr.GetFieldType(fldNum).Name\r
+ case "DateTime":\r
+ dbDate=rdr.GetDateTime(fldNum)\r
+ strFieldItem = replace(dbDate.ToString("s"),"T"," ") ' convert to ISO-8601 format\r
+ case else:\r
+ strFieldItem = server.HTMLEncode(rdr.GetValue(fldNum))\r
+ end select\r
+ end if\r
+ writer.Write("<td>" & strFieldItem & "</td>")\r
+ next\r
+ writer.Write("</tr>")\r
+ else\r
+ eof=true\r
+ end if\r
+ end while\r
+ end select\r
totcnt += rowcnt\r
\r
if not eof and gettotal then\r