index_markers.ado.asp

• Download the Source Workbook (Right-Click, Save Target As...)
• Run this Sample Script

  ---

<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<%
  '----------------------------------------------------------------------
  ' index_markers.asp - SpeedGen ASP Sample Script
  ' (c) 2000-2002 Optimized Software Ltd. All Rights Reserved.
  '----------------------------------------------------------------------
  ' This script demonstrates:
  '  - Reading ADO RecordSets
  '  - Inserting Fields by Index Number
  '  - Field Name Markers (to insert Field's Name by Index Number)
  '  - This can be used to insert an unknown number of fields.
  '  - Using the Format Cells method to format the Date data that has
  '    been inserted.
  '  - Using the ClearCells method and the HideSheet method to "cleanup"
  '    the Source Workbook. (ie. to remove the extra Field Markers and 
  '    cells that are not used)
  '
  ' Notes:
  '  - Do NOT write any HTML or use Response.Write!
  '  - The "dump.xls" Source Workbook (Template) contains 256 Field Markers
  '    per row. It is suggested that you trim this to the minimum required
  '    so that your generation is faster.
  '  - When creating your own Workbook using Index and Field Name markers 
  '    you should cut and paste from "dump.xls". It is much faster than
  '    typing them yourself.
  '  - You may notice that the columns are not very wide. "dump.xls" is 
  '    just a generic source workbook. In your Source Workbook you can 
  '    widen the columns to whatever width you need. Or you can use VBA
  '    to autofit the columns (see the "vba_autofit" sample)
  '  - Server Side (or no) Cursors can cause queries (and stored procedures) to
  '    be executed multiple times! Using a Client Side Cursor will prevent this.
  '----------------------------------------------------------------------
  ' Tip: Code Library at http://www.excelspeedgen.com/code
  '----------------------------------------------------------------------
  Dim dbFile          ' Database File
  Dim oConn           ' ADO Connection object

  Dim Rs1             ' Record Source 1
  Dim Rs2             ' Record Source 2
  Dim Rs3             ' Record Source 3

  Dim XLS             ' SpeedGen Object
  Dim SrcBook         ' Source Workbook

  ' Open Microsoft Access Database
  dbFile = Server.MapPath("sample.mdb")
  Set oConn = Server.CreateObject("ADODB.Connection")
  oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbFile

  ' Open the RecordSets
  Set Rs1 = oConn.Execute("USStates") ' SQL Statement (or Table name)
  Set Rs2 = oConn.Execute("Wine") ' SQL Statement (or Table name)
  Set Rs3 = oConn.Execute("Coffee") ' SQL Statement (or Table name)

  ' Create Excel File
  Set XLS = Server.CreateObject("XLSpeedGen.ASP")
  XLS.EstimatedSize = 100000 ' Set Estimated Output File Size (Critical for speed)
  ' Clear Cells that are used for RecordSources 4 to 10 since
  ' we only use 3 record sources in this example
  XLS.ClearCells 1, "A22:IV60"  ' Clear Rows 17 to 60 on Sheet1
  XLS.ClearCells 1, "A4:A5"     ' Clear Hyperlinks for RecordSource 4 to 5
  XLS.ClearCells 1, "C1:C5"     ' Clear Hyperlinks for RecordSource 6 to 10

  ' Format Date Values (RecordSource 2)
  XLS.FormatCells 1, "A15:A15", 2, "A5", True ' Format all data inserted into Sheet1!A15 with formatting from Sheet2!A5

  ' Hide Sheet 2 ("Formatting" Sheet)
  XLS.HideSheet 2, True ' True = Hide it so user cannot unhide it

  ' Add RecordSources
  XLS.AddRS_ADO Rs1, 20 ' RecordSource 1 (read 20 rows at a time)
  XLS.AddRS_ADO Rs2, 20 ' RecordSource 2 (read 20 rows at a time)
  XLS.AddRS_ADO Rs3, 20 ' RecordSource 3 (read 20 rows at a time)

  SrcBook = Server.MapPath("dump.xls") ' Location of Source Workbook
  XLS.Generate SrcBook, "", True ' Generate SpreadSheet and Stream to Client, Open in Place

  Set XLS = Nothing ' Destroy object when done

  ' Cleanup Code
  oConn.close ' Close Connection and all Recordsets
  Set oConn = Nothing
%>