Hi
#include "fivewin.ch"
#include "FileXLS.ch"
#include "XLSForm.ch"
#include "XLSError.ch"
#define BORDER_UP nOr( BORDER_TOP )
#define BORDER_DOWN nOr( BORDER_BOTTOM )
#DEFINE TRUE .T.
#DEFINE FALSE .L.
LOCAL nFormat,nFormat2,nFormat3,nFont1,nFont2,nFont3,oXLS
local f
local i
DEFINE XLS FORMAT nFormat PICTURE '#,##0.00_);[Red](#,##0.00)'
DEFINE XLS FORMAT nFormat1 PICTURE '0'
DEFINE XLS FONT nFont1 NAME "Arial" HEIGHT 12 BOLD
DEFINE XLS FONT nFont3 NAME "Times New Roman" HEIGHT 10 BOLD
DEFINE XLS FONT nFont4 NAME "Times New Roman" HEIGHT 10
@ 1,1 XLS SAY "ABC" OF oXls FONT nFont1
How to colour "ABC" to become blue.
And if I want to have a grey background, how do I do it.
Regards
AC Woo
Using FWH + bcc582
xls color
Re: xls color
With this class you can not color the font (look at the code), I think if you can put a plot in the background.
Saludos
Saludos
- armando.lagunas
- Posts: 346
- Joined: Mon Oct 05, 2009 3:35 pm
- Location: Curico-Chile
- Contact:
Re: xls color
como ejemplo:
esta es una función adaptada para que construya una hoja en excel a partir de un xBrowse dado
resultado:

esta es una función adaptada para que construya una hoja en excel a partir de un xBrowse dado
Code: Select all | Expand
FUNCTION ExcelConstructor( oBrw, oMeter, cTitle )
LOCAL oExcel, oBook, oSheet, nRow, nCol, uData, nEvery, oRange, cRange, cCell, cLet, nColHead, bError, cText, oClip, nStart, aRepl, ;
nLine := 1, nCount := 0, aCol := { 26, 52, 78, 104, 130, 156 }, aLet := { "", "A", "B", "C", "D", "E" }, xWin
CursorWait()
cLet := aLet[ ASCAN( aCol, {|e| LEN( oBrw:aCols ) <= e } ) ]
IF !EMPTY( cLet )
nCol := ASCAN( aLet, cLet ) - 1
cLet += CHR( 64 + LEN( oBrw:aCols ) - aCol[ MAX( 1, nCol ) ] )
ELSE
cLet := CHR( 64 + LEN( oBrw:aCols ) )
ENDIF
aRepl := {}
*oMeter:SetRange( 0, ( oBrw:nLen + 1 ) )
*oMeter:Setpos( 0 )
*oMeter:Refresh()
nEvery := MAX( 1, INT( ( oBrw:nLen + 1 ) * .02 ) )
bError := ErrorBlock( { | x | Break( x ) } )
BEGIN SEQUENCE
oExcel := TOleAuto():New("Excel.Application")
RECOVER
ErrorBlock( bError )
CursorArrow()
MsgFlip( "Excel no se encuentra instalado..." )
RETURN Nil
END SEQUENCE
ErrorBlock( bError )
nCount -= 15
*oMeter:SetPos( nCount )
oExcel:ScreenUpdating := .F.
oExcel:WorkBooks:Add()
oBook := oExcel:Get( "ActiveWorkBook")
oSheet := oExcel:Get( "ActiveSheet" )
nCount -= 15
*oMeter:SetPos( nCount )
( oBrw:cAlias )->( Eval( oBrw:bGoTop() ) )
cText := ""
FOR nRow := 1 TO oBrw:nLen
IF nRow == 1
oSheet:Cells( nLine++, 1 ):Value := cTitle
oSheet:Range( "A1:" + cLet + "1" ):Set( "HorizontalAlignment", 7 )
++nLine
nStart := nLine
nColHead := 0
FOR nCol := 1 TO LEN( oBrw:aCols )
uData := IIF( VALTYPE( oBrw:aCols[ nCol ]:cHeader ) == "B", EVAL( oBrw:aCols[ nCol ]:cHeader ), oBrw:aCols[ nCol ]:cHeader )
IF VALTYPE( uData ) != "C"
LOOP
ENDIF
uData := STRTRAN( uData, CRLF, Chr( 10 ) )
nColHead ++
oSheet:Cells( nLine, nColHead ):Value := uData
IF nCount % nEvery == 0
*oMeter:SetPos( nCount )
ENDIF
nCount ++
NEXT
nStart := ++ nLine
ENDIF
FOR nCol := 1 To Len( oBrw:aCols )
uData := EVAL( oBrw:aCols[ nCol ]:bEditValue )
IF VALTYPE( uData ) == "C" .AND. AT( CRLF, uData ) > 0
uData := STRTRAN( uData, CRLF, "&&" )
IF ASCAN( aRepl, nCol ) == 0
AADD( aRepl, nCol )
ENDIF
ENDIF
IF oBrw:aCols[ nCol ]:cEditPicture != Nil
uData := TRANSFORM( uData, oBrw:aCols[ nCol ]:cEditPicture )
ENDIF
uData := IIF( VALTYPE( uData )=="D", DTOC( uData ), ;
IIF( VALTYPE( uData )=="N", STR( uData ) , ;
IIF( VALTYPE( uData )=="L", IIF( uData ,".T." ,".F." ), cValToChar( uData ) ) ) )
cText += TRIM( uData ) + Chr( 9 )
IF nCount % nEvery == 0
*oMeter:SetPos( nCount )
ENDIF
nCount ++
NEXT
oBrw:Skip( 1 )
cText += CHR( 13 )
++nLine
IF LEN( cText ) > 400000
oClip := TClipBoard():New()
oClip:Clear()
oClip:SetText( cText )
cCell := "A" + AllTrim( Str( nStart ) )
oRange := oSheet:Range( cCell )
oRange:Select()
oSheet:Paste()
oClip:End()
cText := ""
nStart := nLine + 1
ENDIF
NEXT
IF ASCAN( oBrw:aCols, { |o| o:cFooter != Nil } ) > 0
FOR nCol := 1 TO LEN( oBrw:aCols )
uData := IIF( VALTYPE( oBrw:aCols[ nCol ]:cFooter ) == "B", EVAL( oBrw:aCols[ nCol ]:cFooter ), oBrw:aCols[ nCol ]:cFooter )
uData := cValTochar( uData )
uData := STRTRAN( uData, CRLF, Chr( 10 ) )
oSheet:Cells( nLine, nCol ):Value := uData
NEXT
ENDIF
oSheet:Rows( 1 ):Font:Bold := .T.
( oBrw:cAlias )->( DbGoTop() )
IF LEN( cText ) > 0
oClip := TClipBoard():New()
oClip:Clear()
oClip:SetText( cText )
cCell := "A" + AllTrim( Str( nStart ) )
oRange := oSheet:Range( cCell )
oRange:Select()
oSheet:Paste()
oClip:End()
cText := ""
ENDIF
nLine := If( ! Empty( cTitle ), 3, 1 )
cRange := "A" + LTrim( Str( nLine ) ) + ":" + cLet + AllTrim( Str( oSheet:UsedRange:Rows:Count() ) )
oRange := oSheet:Range( cRange )
oRange:Font:Name := "Calibri"
oRange:Font:Size := 12
oRange:Font:Bold := .F.
IF ! EMPTY( aRepl )
FOR nCol := 1 TO LEN( aRepl )
oSheet:Columns( CHR( 64 + aRepl[ nCol ] ) ):REPLACE( "&&", CHR( 10 ) )
NEXT
ENDIF
//--- color titulo
oSheet:Rows( 1 ):Font:Size := 14
oSheet:Rows( 1 ):Font:Bold := .T.
oSheet:Rows( 1 ):RowHeight := 30
oSheet:Rows( 1 ):Font:ColorIndex := 25
oSheet:Rows( 3 ):Font:Bold := .T.
oSheet:Rows( 3 ):Font:ColorIndex := 20
oSheet:Rows( 3 ):RowHeight := 25
oRange:Borders():LineStyle := 1
oRange:Columns:AutoFit()
IF ! Empty( aRepl )
FOR nCol := 1 TO LEN( aRepl )
oSheet:Columns( CHR( 64 + aRepl[ nCol ] ) ):WrapText := .T.
NEXT
ENDIF
*oMeter:SetPos( ( oBrw:nLen + 1 ) )
//--- color de cabezeras
oSheet:Range( "A3:"+cLet+"3" ):Interior:ColorIndex := 49
oSheet:Range( "A3:"+cLet+"3" ):Borders:ColorIndex := 2
//---
oSheet:Range( "A4" ):Select()
xWin := oExcel:ActiveWindow
xWin:SplitRow := 3
xWin:FreezePanes := .t.
oExcel:ScreenUpdating := .t.
oExcel:Visible := .T.
ShowWindow( oExcel:hWnd, 3 )
BringWindowToTop( oExcel:hWnd )
CursorArrow()
RETURN NIL
resultado:

SkyPe: armando.lagunas@hotmail.com
Mail: armando.lagunas@gmail.com
Mail: armando.lagunas@gmail.com
Re: xls color
Hi
Thanks
How to colour the backgroud ?
Regards
ACWoo
Using FWH + bcc582
Thanks
I think if you can put a plot in the background.
How to colour the backgroud ?
Regards
ACWoo
Using FWH + bcc582
- armando.lagunas
- Posts: 346
- Joined: Mon Oct 05, 2009 3:35 pm
- Location: Curico-Chile
- Contact:
Re: xls color
How to colour the backgroud ?
Puedes agregar o cambiar en estas instrucciones, la variable "cLet" , indica la letra del rango
Code: Select all | Expand
//--- color de cabezeras
oSheet:Range( "A3:"+cLet+"3" ):Interior:ColorIndex := 49
oSheet:Range( "A3:"+cLet+"3" ):Borders:ColorIndex := 2
//---
esto es solo un ejemplo de como utilizarla :
Horizontal donde cLet := "M" que viene del total de columnas del xBrowse
oSheet:Range( "A3:M3" ):Interior:ColorIndex := 71
oSheet:Range( "A3:M3" ):Borders:ColorIndex := 2
Vertical donde cLet := "C" y el 100 es el oBrw:nLen del xBrowse
oSheet:Range( "C4:C100" ):Interior:ColorIndex := 101
oSheet:Range( "C4:C100" ):Borders:ColorIndex := 12
SkyPe: armando.lagunas@hotmail.com
Mail: armando.lagunas@gmail.com
Mail: armando.lagunas@gmail.com