====== Exporting Multiple Assortment Information To Excel ======

Many times you will need to export assortment information to excel, but you will not know in advance how many assortment items the user will have filled.  In order to export a variable number of pieces of information do the following.  We will use the Assortment UPCs for this example.

There are 3 scripting sections that you need to work with:

=== Globals ===

The "Globals" area is where you set up any global variables or functions you will need.  Here we will declare a stringlist to hold all valid UPCs which will be used in the OnBeforeData and OnAfterData scripts.

<code sqbasic>
// Declare our global variables that will need to hold their values in
// other routines
dim slUPCs  // a list to hold valid UPCs
dim EndingRow // the EndingRow in Excel for each product

// A Function to retrieve a valid up or a blank
// index is the index into the extra rows of info
FUNCTION GetListValue(list, index)
  if (list.count > index) then
     GetListValue = list.Strings[index]
  else
     GetListValue = ""
  end if
END FUNCTION

// function to output a formula
// ws - an excel Worksheet variable a la excel.ActiveSheet
// col - the column name - e.g. "AG"
// row - the excel row number - e.g. 25
// formula - a template for the formula with python like string format vars.
//      e.g. "=B{0} + E{1} * F{2}"
// data - an array of variables to complete the formula
//      e.g. [row, row - 4, 43] would replace {0} with the value of the row variable
//                                            {1} with the value of row-4
//                                            {2} with 43
SUB exportFormula(ws, col, row, formula, data)
  // enumerate through the data array and substitute the values
  for i = low(data) to high(data)
    formula = ReplaceAll(formula, "{" + i + "}", row, false)
  next
  // output to Excel
  addr = col + row
  Rng = ws.Range(addr)
  Rng.formula = formula
END SUB
</code>

=== OnBeforeData ===

<code sqbasic>
// We need to figure out if we have enough rows left on this worksheet to
// fill in all the assortment UPCs

// max_rows variable is used to find how many extra rows we have.
// Its not essential when only using one list, but useful if we
// have multiple lists.
max_rows = 0

// Create our TStringList to hold the valid UPCs
slUPCs = TStringList.Create
// enumerate all of the "valid" upc values
for i = 1 to 40
    szUPC = trim(SQForm.Link("Asst_UPC_" + format("%.3d", [Round(i)]), 0).Data)
    if (szUPC <> "") then
       slUPCs.add(szUPC)
       inc(max_rows)
    end if
next

// EndingRow is the row offset of the last row for this group
// Since the first "Multiline" row is row 22, EndingRow of 30 would be Excel row 52
EndingRow = max_rows + Row
</code>

=== OnAfterData ===

<code sqbasic>
// FIRST_ROW is the first valid row in the Excel.
FIRST_ROW = 22

// FORMULA_AG is a formula template - put here to make it easier to find if changes are necessary
FORMULA_AG = "=IF((FA{0}<>0),((FA{0}-DB{0})/FA{0}),"""")"

ws = excel.ActiveSheet

// main_row is the current row
main_row = FIRST_ROW + Row - 1

// We want to export the correct formula in cell AG for the current row
exportFormula(ws, "AG", main_row, FORMULA_AG, [main_row])

// xlRow is the actual row in Excel we will output into
for xlRow = main_row + 1 to FIRST_ROW + EndingRow
  // idx is the index into the current lists
  idx = xlRow - main_row - 1
  // output to Excel - GetUPC is a Global function to make it easier to get the correct UPC value
  ws.Range("Z" + xlRow).value = GetListValue(slUPCs, idx)
  
  // We want to export the correct formula in cell AG for each UPC row
  exportFormula(ws, "AG", xlRow, FORMULA_AG, [xlRow])
next

// Remember to free any lists you create in OnBeforeData
slUPCs.free

// Set the current row to the EndingRow.  SQ will autoinc Row by 1
Row = EndingRow
</code>