OmnisXL / ExcelFormat : formatting cells after $populatewithlist

[Grzegorz (Greg) Pasternak]

Nick;

I have created following code when looking into this:
=========
;  
Calculate vExportFilePath as con(cExportPath,cFileName)
Do FileOps.$converthfspathtoposixpath(vExportFilePath,vPosixPath)
;  
Do vExcelWorkbookObj.$initialize(cExtLst.cCode)     ;; use the constants to determine the type of export
If iOutputDataLstHeader
Calculate vColHeaderFormat as 2
Calculate vHeaderRow as cHeaderRow
Else
Calculate vColHeaderFormat as 0
End If
Do vExcelWorkbookObj.$populatewithlist(cDataLst,vColHeaderFormat,vHeaderRow)     ;; pColumnheaderFormat comes into play here  1=database column names, 2=english names
;  
Calculate vFontObj as vExcelWorkbookObj.$addfont()
Calculate vFontObj.$fontname as 'Arial'     ;; ex: 'Times New Roman'
Calculate vFontObj.$color as kXLColorBlack
Calculate vFontObj.$italic as 0
Calculate vFontObj.$bold as 0
Calculate vFontObj.$size as 11
;  
If iOutputDataLstHeader
;  - handle header row
For cColNameLst.$line from 1 to cColNameLst.$linecount step 1
Calculate vFormatObj as vExcelWorkbookObj.$addformat()
Switch cColNameLst.cColAlign
Case kLeftJst
Calculate vFormatObj.$alignh as kXLAlignHLeft
Case kRightJst
Calculate vFormatObj.$alignh as kXLAlignHRight
Default
Calculate vFormatObj.$alignh as kXLAlignHCenter
End Switch
Calculate vFormatObj.$borderbottom as kXLBorderStyleMedium
Calculate vFormatObj.$font as vFontObj
Calculate vSheetObj as vExcelWorkbookObj.$getsheet(1)
;  
Calculate vRowNo as 1     ;; to accommodate header row
Calculate vColNo as cColNameLst.$line
Calculate vCellObj as vSheetObj.$cell(vRowNo,vColNo)
Calculate vCellObj.$format as vFormatObj
End For
End If
;  - handle data list
For cColNameLst.$line from 1 to cColNameLst.$linecount step 1
;  
Calculate vFormatObj as vExcelWorkbookObj.$addformat()
Switch cColNameLst.cColAlign
Case kLeftJst
Calculate vFormatObj.$alignh as kXLAlignHLeft
Case kRightJst
Calculate vFormatObj.$alignh as kXLAlignHRight
Default
Calculate vFormatObj.$alignh as kXLAlignHCenter
End Switch
Calculate vFormatObj.$borderbottom as kXLBorderStyleNone
Calculate vFormatObj.$font as vFontObj
Calculate vSheetObj as vExcelWorkbookObj.$getsheet(1)
;  
For cDataLst.$line from 1 to cDataLst.$linecount step 1
Calculate vRowNo as cDataLst.$line+pick(iOutputDataLstHeader,0,1)     ;; added 1 to accommodate the header row
Calculate vColNo as cColNameLst.$line
Calculate vCellObj as vSheetObj.$cell(vRowNo,vColNo)
Calculate vCellObj.$format as vFormatObj
End For
End For
;  
Do vExcelWorkbookObj.$save(vPosixPath) Returns #F
;  
Quit method 
=========

I am using "font object" 
Calculate vFontObj as vExcelWorkbookObj.$addfont()

set the size
Calculate vFontObj.$size as 11

initialize "format object"
Calculate vFormatObj as vExcelWorkbookObj.$addformat()

set font
Calculate vFormatObj.$font as vFontObj

get "sheet object"
Calculate vSheetObj as vExcelWorkbookObj.$getsheet(1)

get "cell object"
Calculate vCellObj as vSheetObj.$cell(vRowNo,vColNo)


apply format to the cell 
Calculate vCellObj.$format as vFormatObj


Perhaps this can help you,

Greg


On Jan 26, 2018, at 4:29 AM, Nick Renders <omnis1 at arcict.com> wrote:

> Hi List,
> 
> I am playing around with Artsman's ExcelFormat external and I have hit a little snag.
> 
> Basically, what I would like to do is save an Omnis list as an Excel file and set the
> row height and text wrap properties for all cells. So my code looks like this:
> 
> ------------------------------------------------------------
> 
> Do lXLSBook.$initialize(kXLxlsx)
> 
> Do lXLSBook.$addformat() Returns lXLSFormat
> Do lXLSFormat.$wrap.$assign(kTrue)
> Do lXLSFormat.$bordertop.$assign(kXLBorderStyleDouble)     ;; ** purely to visualize the issue
> 
> Do lXLSBook.$populatewithlist(lList)
> 
> Do lXLSBook.$getsheet(1) Returns lXLSSheet
> For lList.$line from 1 to lList.$linecount step 1
> 	Do lXLSSheet.$setrow(lList.$line,20,lXLSFormat)
> End For
> 
> Do lXLSBook.$save(lPath)
> 
> ------------------------------------------------------------
> 
> 
> The resulting Excel file has all rows with height 20, but the lXLSFormat is only
> applied to the cells that come after my imported list. So if lList has 3 columns,
> only column D and beyond have text wrap and a border style.
> 
> Any idea what I am doing wrong here? It works fine if I do it cell by cell, for
> instance:
> 
> lXLSSheet.$cell(2,2).$setvalue(lXLSSheet.$cell(2,2).$value,lXLSFormat)
> 
> But that seems like a bit overkill.
> 
> 
> Thanks,
> 
> Nick Renders
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> 




======================
Grzegorz (Greg) Pasternak
grzegorz at myfastcom.ca

"Facta non verba"