The VVOUT spreadsheet procedures were enhanced in Valence 4.2 to support the generation of Excel spreadsheets in .xlsx format. You can see a practical example of these procedures in action by clicking on the download button on any of the entities in the Portal Admin app (the corresponding backend RPG code is located in various VVADM*** source members). The procedure for applying styling to cells in these .xlsx-based spreadsheets is a bit different from the old .xls formatting used in prior versions of Valence.
This tip explains the difference and shows you how you can modify the template to add your own custom styles. When VVOUT is generating spreadsheet output, it relies on a special template file to handle cell styling. The template file is a reference spreadsheet containing rows with different styling attributes, such as number of decimal places, alignment, font, bold, etc. These styles can be referenced when building spreadsheets through VVOUT to format cells in the desired manner. The path of the template file specified in Portal Admin > Settings > Hidden Settings, in one of the “Default template for spreadsheets” fields (one for .xls, the other for .xlsx). When generating the older .xls-style spreadsheets, cell styles are referenced by name based on the first column in the .xls template file. The second column contains the formatted values (i.e., a bold, right-aligned cell) from which the styling information is extracted. For example, the following code would be used to specify a column using the “num2” style (which is a numeric value with two decimal places):
vvOut_SSdata('<Row ss:StyleID="num2">'+rowData+'</Row>');
This process changed a bit with the more complex .xlsx files generated through Valence 4.2. The cell styles in the .xlsx template are given a numeric ID behind-the-scenes by Excel, and in Valence 4.2 it is these values that must be specified for the style, resulting in something like this:
vvOut_SSdata('<Row ss:StyleID="6">'+rowData+'</Row>');
So how do you get the “6” out of the template, when this value is specified as a behind-the-scenes ID? Well, if you’ve downloaded the latest version of Valence 4.2 (4.2.20160330.0) then you can use a newly included utility program called VVSSSTYLES to extract these hidden ID values from your .xlsx template file. With the appropriate Valence 4.2 library in your library list, simply call the VVSSSTYLES program with a single parm of ‘1’. The program will pull in the .xlsx template file (as specified in Portal Admin) and place a special version of it called ssStyles.xlsx into the temporary IFS directory, which is specified in Portal Admin > Settings > Hidden Settings > Temporary files path (typically set to “/valence-4.2/temp/”). The extracted file will look something like this:
Here you can see that the numeric value with two decimal places corresponds to a style number of 6. This is what should be specified in your RPG code. Note that this procedure is specific to Valence 4.2. In a future release of Valence we plan to streamline this process so that you can use a reference column similar to what was done for .xls spreadsheets.
