Excel conditional format

#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

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