[HELPED] execSQLtoSS column headings

[ktoole]

Is it possible to have the text in the column headings wrap with the setting from columnsPointer? I have one column that comes through with wrap text set on and others that do not. Would setting the colunmn width do this? I have not set the column width on any of the columns at this point.

[eweiler]

I think setting the width would work. It is worth trying. That in connection with opening the spreadsheet and formatting the cells for word wrap.

[BRandle]

Excel formatting is handled through Styles, using the Columns model you can set the name of the style for Headings or the data. Styles are kept in a text/xml file in the IFS and the default template file is in your Valence instance directory vvresources/vvSSTemplate.xls

In the vvSSTemplate.xls file you'll see styles formated like this

<Style ss:ID="HTleft">
 <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="0"/>
 <Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="12.0" ss:Bold="1"/> 
 <Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
</Style>

In the default SSTemplate.xls file, the setting for WrapText is 0 (do not wrap). You can copy this file and alter it to your liking (change the WrapText to 1) and specify the setting in vvOut for ssTemplate to reference your new Template file. You then can change or add Styles to change colors, font, size, etc. for the columns and Headings.

[ktoole]

I reworked the template, but cannot get vvOut.ssTemplate to find the template. I put in a path of /html/lps/mlfSSTemplate.xls, which is where I put the template, but I keep getting the error, "No such path or directory". How do I spell out the path for this entry. I also tried altering the colWidth. With the default template, it does not matter what I set the width to, it keeps making the columns the width of the data and not what I tell it. Without colWidth, the columns were coming out the width of the title.

[BRandle]

The path for the template would need to be a full path, /Valence32/html/Ips/mlfSSTemplate.xls for example (I don't think /html is off the root).

For colWidth, you are using the ColumnsArray to set the columnWidth of each column, like the example 2 of the Valence Spreadsheet download EXSS01? If you don't pass a Columns Array the Column widths will be based on the larger of the column width or the column name (heading) and between 5 and 80 characters. vvOut.columnWidth is not used to set the width of all columns.

[ktoole]

I did the same thing you have here in sample 2:

col(1).sqlName  = 'CUSNO';       
col(1).heading  = 'Customer';    
col(1).styleID  = 'val0';        
col(1).colWidth = 80;

I did not use the embed at all with XML.

Here is what mine looks like:

         cn = 1;
         col(cn).sqlName  = 'FKGRTP';
         col(cn).heading  = 'Group Type';
         col(cn).colWidth = 8;

Is this not correct?

I also tried the full path as you suggested and I get the same error that there is no such path or directory.

[BRandle]

Make sure your case is correct for the file. I copied the template to the html directory in my vvBrian instance and was able to load the template:

vvOut.SSTemplate =                              
      '/vvBrian/html/vvSSTemplate.xls';

Are you trying to make the column smaller than the data and the heading? vvOut will set each column to 'AutoFitWidth' so Excel will expand the column size so the largest element will show (instead of '####' in the cell). AutoFitWidth is not configurable - if you require it then we can consider adding the value to the vvSSCol in a future release.

[ktoole]

I tried " vvOut.ssTemplate = 'valence-3.2/html/lps/mlfSSTemplate.xls'; " and
" vvOut.ssTemplate = 'valence32/html/lps/mlfSSTemplate.xls'; " (valence32 is the share name) and I checked the windows path and the file is there and appears to match the case.

I created the spreadsheet with the default template, then resized the columns to fit. I then looked up the column width for each column and put that rounded up value in the program for the colWidth. The real problem is that I have columns that have a heading that is 30 wide, but the data is only 8 wide. I would like the heading to wrap and the data to all fit in a column 12 wide. This makes the spreadsheet not appear so large.

[BRandle]

You mentioned windows path - you can get to file from a green screen command WRKLNK '/valence-3.2/html/lps/mlfSSTemplate.xls' ? Nothing special going on with the template name - vvOut just opens and reads it using

You should be able to set the width and have the Headings wrap, as long as the largest value in the column is less than the width you set.
I changed the exss01 example program to

   col(9).sqlName  = 'ISILL';            
   col(9).colWidth = 12;                 
   col(9).heading  = 'Is State IL?';     
   col(9).styleId  = 'bool';

and the column width adjusted to the size of 'false'. So you should be able to set the width of a column and get it down to size of the largest value. (and also changing the heading style to Wrap in the template).

Screen Shot 2013-03-28 at 12.46.56 PM.png

[ktoole]

I copied in the template again. I also checked it with WRKLNK and now it does appear to be working. I must have had something wrong in the name.

Can't I set the column width to a particular value and have it be that value, instead of adjusting to the size of the data?

I also changed this:

<Style ss:ID="HTitle">
 <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>
 <Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="12.0" ss:Bold="1"/> 
 <Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
</Style>

in the xml document and the title property does not show wrap for all the titles. Do have have to change them all?

I changed all the titles to ss:WrapText="1" and it still is only wraping the first, third, and fourth titles. The rest are unwrapped and when I look at the properties that is what I find the settings at.

What I would like to have happen is to manually in the RPG program set the column width and then have wrap on for the title. That way I can control the size of the spreadsheet.

[BRandle]

Reread the post on autoFitWidth above -- you can not set the width to a value smaller than the data.

There are 2 styles for Headings for alignment (Number Headings align right, Text Headings align left). The default Heading styles are Hchar and Hnum. FYI - In vvSSCol in addition to styleID, there is a hStyleID, so you can override the heading styleID (but should not need to in this case).

[ktoole]

Are you trying to make the column smaller than the data and the heading? vvOut will set each column to 'AutoFitWidth' so Excel will expand the column size so the largest element will show (instead of '####' in the cell). AutoFitWidth is not configurable - if you require it then we can consider adding the value to the vvSSCol in a future release.

I assume this is what you are referring to. I am trying to make the cells Larger than the data, but smaller than the title. I can't get the column to go to the size I specify. Wrapping is really weird. I have all the tempate values set to wrap, but only the ones I mentioned are actually wrapping, the others just get cut off.

[ktoole]

I was going over Example 2, that you mentioned earlier in your posting, to figure out what I was missing. I run that and when I get the spreadsheet the Customer Column comes out the a format width of 14.71 and not the 80 that is in the program. The code from your program looks like this:
[CODE]

col(1).sqlName  = 'CUSNO';      
col(1).heading  = 'Customer';   
col(1).styleID  = 'val0';       
col(1).colWidth = 80;

What does the colWidth = 80 do? That is what I thought would set the width of the column in the spreadsheet and it does not appear to do that.

I did a test and changed the value to 500. The column did get bigger, but to 97.xx. What unit of measure is the column width being specified in? I thought it was characters, that must not be the case.

[ktoole]

I played with the numbers for the columns and when I increased the numbers to much larger sizes, it appears to work correctly. Clearly the numbers are not character size. The numbers also do not match the column width that excel shows you when you examine the column format.

Now the only problem left is to get the column headings to wrap. The first one does, but none of the others do. I change every wrap option on a copy of the template to wrap and it still does not work. I have to RPG program pointing to the copy of the template. Any idea what I could be missing?

I tried adding CR or LF to the string for the column heading, but that did not work either. Excel allows you to control the title wrap with Alt-Enter which supposedly put in character '10' or '0A' for LF.

[BRandle]

The Valence API docs refer to the Excel XML Spreadsheet reference - which says width is in points, but depending on the version and settings of Excel it will show you the width as a measurement (inches). Look at the source code for vvOut - if you pass a colWidth, it multiplies it by 7 to get an approximate size in points for the width.

The style would need ss:WrapText="1" to wrap the text. In the vvSSTemplate.xls shipped, the styleID Hnum would need to have ss:WrapText="1".