vvout_execSqltoSS column colors and freeze top row

[gwilburn]

I've searched for a bit - could not find anything on customizing a downloaded Excel file.

Our users typically like to see the cell background/foreground colors different on the column headings. I would also like to freeze the top row.

Are there any settings in the vvOut structure to handle that?

[robert.swanson]

You would need to modify the xlsx template file VVOUT uses, specifically the Hchar and Hnum styles, to alter the colors on the column headings (the latter is for numeric columns; the former for everything else).

As an experiment, I just uploaded the vvSStemplate.xlsx file (located in the Valence instance's IFS directory /resources/templates) to an AI bot (Grok) and asked it to alter those two styles to have a light grey background, rather than manually altering the contents myself. I then uploaded the result to our IFS and it worked like a charm!

So try replacing your vvSStemplate.xlsx file with what I'm attaching here. Note that you'll need to change the extension from .zip back to .xlsx after you upload it, as this forum won't let me attach a file with an .xlsx suffix. Also, once the file is up on your IFS, find it in wrklnk and use option 8 to check the file's CCSID -- it needs to be 819. If it isn't, use option 13 to change the *CCSID back to 819.

As for freezing one or more rows, there's currently no way to accommodate that in vvOut_execSQLtoSS, but you're not the first person to ask for such a feature... We'll look into adding logic in one of the upcoming builds so you can specify the frozen rows via a new field in the VVOUT data structure (e.g., vvOut.freezeRows=1).

[gwilburn]

Thanks Rob!

I renamed the existing vvSSTemplate.xlsx before uploading.

FWIW I was actually able to edit the template file in Excel and change the background (fill) color and font color for the headers to what they are accustomed to.

I see you have a vvOut.ssTemplate field in the data structure. Could we use that to retain the original?

[robert.swanson]

Yes, you could leave the global template file as is and override it on the fly with a custom template file specified in vvOut.ssTemplate.

[gwilburn]

Got it working... Took me a bit to figure out I needed to include the fully qualified name

vvOut.ssTemplate = '/valence-6p/resources/templates/vvSSTemplateTBF.xlsx';

[robert.swanson]

FYI, the latest 6.4 build released today includes two new VVOUT data structure values for altering the spreadsheet:
vvOut.freezeRows - sets the number of rows at the top of the spreadsheet to freeze (3 = freeze the top 3 rows)
vvOut.mergeCells - allows ranges of cells to be merged ('A1:J1,A2:J2' = horizontally merge cells A1 through J1 and A2 through J2)