Differences

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

--- pagecreator:tutorials:excel:exporting_multiple_assortments [2012/09/18 14:44] – dave
+++ pagecreator:tutorials:excel:exporting_multiple_assortments [2025/05/22 21:53] (current) – external edit 127.0.0.1
@@ Line 9: / Line 9: @@
 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 vb>
+<code sqbasic>
 // Declare our global variables that will need to hold their values in
 // other routines
@@ Line 49: / Line 49: @@
 === 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>