Differences

This shows you the differences between two versions of the page.

--- pagecreator:tutorials:excel:exporting_multiple_assortments [2012/09/17 20:44] – created dave
+++ pagecreator:tutorials:excel:exporting_multiple_assortments [2025/05/22 21:53] (current) – external edit 127.0.0.1
@@ Line 7: / Line 7: @@
 === Globals ===
-The "Globals" area is where you set up any global variables or functions you will need.  Here we will create a stringlist to hold all valid UPCs.
+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>
+<code sqbasic>
-dim slUPCs
+// 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>