Konu: Excel conditional format
Visual Fox Pro
#Define xlCellValue 1
#Define xlExpression 2
#Define xlBetween 1
#Define xlNotBetween 2
#Define xlEqual 3
#Define xlNotEqual 4
#Define xlGreater 5
#Define xlLess 6
#Define xlGreaterEqual 7
#Define xlLessEqual 8
Local ix,ox
Rand(-1)
Select order_id, cust_id, emp_id, to_name,;
to_address, to_city, to_region, postalcode,;
to_country, ;
ship_via, Date()+10-Int(Rand()*20) As due_date ;
FROM (_samples+'data\orders') ;
INTO Array SampleData
* Add headers to array
Dimension SampleData[ALEN(SampleData,1)+1,ALEN(SampleData,2)]
Ains(SampleData,1)
For ix=1 To Alen(SampleData,2)
SampleData[1,m.ix] = 'Sample_'+Chr(Asc('A')-1+m.ix)
Endfor
ox = Createobject('Excel.Application')
With ox
.WorkBooks.Add
.Visible = .T.
With .ActiveWorkBook.ActiveSheet
Local loDataRange, loFormatRange
* Data range
loDataRange = .Range(.Cells(1,1), ;
.Cells(Alen(SampleData,1),Alen(SampleData,2)))
* Format range - start at row 2 skipping header
loFormatRange = .Range(.Cells(2,1), ;
.Cells(Alen(SampleData,1),Alen(SampleData,2)))
Endwith
* Put the data in Excel
loDataRange.Value = getArrayRef('SampleData')
* Conditional format
With loFormatRange
.FormatConditions.Delete
.FormatConditions.Add(xlExpression,, '=$K1 < TODAY()')
.FormatConditions.Add(xlExpression,, '=$K1 = TODAY()')
.FormatConditions.Add(xlExpression,, '=$K1 > TODAY()')
With .FormatConditions(1)
.Font.Bold = 1
.Font.Italic = 1
.Font.Color = 0xFFFFFF && white
.Interior.Color = 0x0000FF && red
Endwith
With .FormatConditions(2)
With .Font
.Bold = 1
.Color = 0 && black
Endwith
.Interior.Color = 0x00FFFF && yellow
Endwith
With .FormatConditions(3)
.Font.Bold = 0
.Font.Color = 0 && black
.Interior.Color = 0xFFFFFF && white
Endwith
Endwith
Endwith
Function getArrayRef(tcArrayName)
Return @&tcArrayName
Visual Fox Pro
lcXLS = Sys(5)+Curdir()+'Conditional.xls'
Rand(-1)
Select cust_id, Company, Iif(Int(Rand()*100)%2=0,0.05,Round(Rand(),2)) As myValue ;
from customer ;
into Cursor crsCustomer
Copy To (lcXLS) Type Xl5
#Define xlCellValue 1
#Define xlExpression 2
#Define xlBetween 1
#Define xlNotBetween 2
#Define xlEqual 3
#Define xlNotEqual 4
#Define xlGreater 5
#Define xlLess 6
#Define xlGreaterEqual 7
#Define xlLessEqual 8
lnRows = Reccount()
lnCols = Fcount()
Use In 'crsCustomer'
Use In 'customer'
oExcel = Createobject('Excel.Application')
With oExcel
.WorkBooks.Open(lcXLS)
*ValueSample()
ExpressionSample()
.Visible = .T.
Endwith
Function ValueSample
lcRange = _GetChar(lnCols)+'2:'+;
_GetChar(lnCols)+Ltrim(Str(lnRows+1)) && 1 header
With oExcel.ActiveWorkBook.ActiveSheet.Range(lcRange)
.FormatConditions.Delete
.FormatConditions.Add(xlCellValue, xlEqual, ".05" )
.FormatConditions.Add(xlCellValue, xlLess, ".50" )
.FormatConditions.Add(xlCellValue, xlGreaterEqual, ".8" )
With .FormatConditions(1)
.Font.ColorIndex = 1
.Interior.ColorIndex = 4
.Font.Bold = 1
Endwith
With .FormatConditions(2)
.Font.ColorIndex = 1
.Interior.ColorIndex = 44
.Font.Bold = 1
Endwith
With .FormatConditions(3)
.Font.ColorIndex = 1
.Interior.ColorIndex = 3
.Font.Bold = 1
Endwith
Endwith
Function ExpressionSample
lcRange = 'A2:'+_GetChar(lnCols)+Ltrim(Str(lnRows+1)) && 1 header
oExcel.ActiveWorkBook.ActiveSheet.Range('A2').Activate
With oExcel.ActiveWorkBook.ActiveSheet.Range(lcRange)
.FormatConditions.Delete
.FormatConditions.Add(xlExpression,, '=$C2=0.05')
With .FormatConditions(1)
.Font.Bold = .T.
.Interior.ColorIndex = 4
Endwith
Endwith
* Return A, AA, BC etc noation for nth column
Function _GetChar
Lparameters tnColumn && Convert tnvalue to Excel alpha notation
If tnColumn = 0
Return ""
Endif
If tnColumn <= 26
Return Chr(Asc("A")-1+tnColumn)
Else
Return _GetChar(Int(Iif(tnColumn % 26 = 0,tnColumn - 1, tnColumn) / 26)) + ;
_GetChar((tnColumn-1)%26+1)
Endif