OmnisXL / ExcelFormat : formatting cells after $populatewithlist

Nick Renders omnis1 at arcict.com
Fri Jan 26 09:24:37 EST 2018


Hi Greg,

Thanks for your example.

I see you set the $format property for every cell individually.
I would rather avoid that because the list is rather lengthy,
but I don't know if that is possible.

Cheers,

Nick


On 26 Jan 2018, at 14:39, Grzegorz (Greg) Pasternak wrote:

> 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"
>
>
>
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com



More information about the omnisdev-en mailing list