array.other.asp

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

  ---

<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<%
  '----------------------------------------------------------------------
  ' array.asp - SpeedGen ASP Sample Script
  ' (c) 2000-2002 Optimized Software Ltd. All Rights Reserved.
  '----------------------------------------------------------------------
  ' This script demonstrates:
  '  - Using a 2D Array as Source Data
  '  - Clearing Cells in the Source Workbook (ClearCells Method)
  '  - Formatting Cells Dynamically (FormatCells Method)
  '
  ' Notes:
  '----------------------------------------------------------------------
  ' Tip: Code Library at http://www.excelspeedgen.com/code
  '----------------------------------------------------------------------
  Dim XLS             ' SpeedGen Object
  Dim SrcBook         ' Source Workbook

  Dim A1(9, 19)      ' Array 1
  Dim A2(49, 99)     ' Array 2
  Dim i, j

  ' These Arrays use Integers but arrays can contain  
  ' Numbers, Strings or other kinds of Variant data
  
  ' Populate Array 1
  For i = LBound(A1, 1) To UBound(A1, 1) 
    For j = LBound(A1, 2) To UBound(A1, 2)
      A1(i, j) = i * 100 + j + 1
    Next  
  Next  
  
  ' Populate Array 2
  For i = LBound(A2, 1) To UBound(A2, 1) 
    For j = LBound(A2, 2) To UBound(A2, 2)
      A2(i, j) = i * 100 + j + 1
    Next  
  Next

  ' Create Excel File
  Set XLS = Server.CreateObject("XLSpeedGen.ASP")
  XLS.EstimatedSize = 200000 ' Set Estimated Output File Size (Critical for speed)

  XLS.AddRS_Array_2D A1, True ' Rows are in 1st Dimension of Array
  XLS.AddRS_Array_2D A2, True ' Rows are in 1st Dimension of Array

  ' Clear Cells that are used for RecordSources 3 to 10 since
  ' we only use 2 record sources in this example
  XLS.ClearCells 1, "A17:IV60"  ' Clear Rows 17 to 60 on Sheet1
  XLS.ClearCells 1, "A3:A5"     ' Clear Hyperlinks for RecordSource 3 to 5
  XLS.ClearCells 1, "C1:C5"     ' Clear Hyperlinks for RecordSource 6 to 10

  ' Format Data from RecordSource 1
  XLS.FormatCells 1, "A10:A10", 2, "A1", True ' Format all data inserted into Sheet1!A10 with formatting from Sheet2!A1
  XLS.FormatCells 1, "B10:B10", 2, "B1", True ' Format all data inserted into Sheet1!B10 with formatting from Sheet2!B1
  XLS.FormatCells 1, "C10:C10", 2, "C1", True ' Format all data inserted into Sheet1!C10 with formatting from Sheet2!C1    

  ' Highlight Some Rows
  XLS.FormatCells 1, "A12:IV12", 2, "A3", False ' Format Sheet1, Row 12 with formatting from Sheet2!A3
  XLS.FormatCells 1, "A15:IV15", 2, "B3", False ' Format Sheet1, Row 15 with formatting from Sheet2!B3  

  ' Format Data from RecordSource 2
  XLS.FormatCells 1, "A15:A15", 2, "A1", True ' Format all data inserted into Sheet1!A15 with formatting from Sheet2!A1
  XLS.FormatCells 1, "B15:B15", 2, "B1", True ' Format all data inserted into Sheet1!B15 with formatting from Sheet2!B1
  XLS.FormatCells 1, "C15:C15", 2, "C1", True ' Format all data inserted into Sheet1!C15 With formatting from Sheet2!C1    
          
  ' Hide Sheet 2
  XLS.HideSheet 2, True ' True = Hide it so user cannot unhide it

  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

  
%>