Excel column formatting using ExcelFormat xcomp

[Andy Hilton]

Update….

If I apply the Format object to an individual cell, it works fine…..

Calculate Cell as Sheet.$cell(2,13)
Calculate Cell.$format as Format

This does indeed apply my percent number format to the cell in row 2 column 13 (or column ‘M’ - either way works) as well as the border styling

So I am wondering why the $setCol command only works on unfilled cells ?? I am sure I don’t have to iterate through every single cell to format them do I ??

Andy Hilton
Totally Brilliant Software Inc
Phone (US) : (863) 409 4870 
Phone (UK) : 0207 193 8582
Web : www.totallybrilliant.com <http://www.totallybrilliant.com/> 
Helpdesk : http://totallybrilliant.kayako.com
Email : andyh at totallybrilliant.com

> On Sep 19, 2020, at 4:31 PM, Andy Hilton <andyh at totallybrilliant.com> wrote:
> 
> Folks
> 
> Am trying to get a little fancier with my excel spreadsheet creation….
> 
> Starting with something very simple - columns 12 to 17 of a spreadsheet I am creating want to be formatted as a percentage (or kXLNumFormatPercent_d2 in ExcelFormat speak)
> 
> So this is what I have created :
> 
> Do oExcel.$initialize(pExportFormat) 
> Do oExcel.$populatewithlist(ExportList,pColumnHeaderFormat,HeaderRow) 
> Calculate Sheet as oExcel.$getSheet(1)
> Calculate Format as oExcel.$addFormat()
> Calculate Format.$borderleft as kXLBorderStyleDashDot
> Calculate Format.$bordertop as kXLBorderStyleDashDot
> Calculate Format.$borderright as kXLBorderStyleDashDot
> Calculate Format.$borderbottom as kXLBorderStyleDashDot
> Calculate Format.$numformat as kXLNumFormatPercent_d2
> Do Sheet.$setCol(12,17,9,Format,kFalse) Returns #F
> Do oExcel.$save(pPathName)
> 
> Fundamentally this works in that columns 12 to 17 *do* have their width set (the third parameter of $setCols), and all cells beneath my own data are shown with the border style (I added the border style as its easier to visually see what happens !)
> 
> However all of my actual data cells are unaffected by this call - does anyone know why this is ??
> 
> Andy Hilton
> Totally Brilliant Software Inc
> Phone (US) : (863) 409 4870 
> Phone (UK) : 0207 193 8582
> Web : www.totallybrilliant.com <http://www.totallybrilliant.com/> 
> Helpdesk : http://totallybrilliant.kayako.com
> Email : andyh at totallybrilliant.com
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com