Adjust the width of Excel cell

[sajith]

I am trying to export the data of a recordset to excel sheet .I want to adjust the cell width of a particular cell,How can i do it.Following is my code.

Code: Select all  Expand view

//------------------------------------------------------------------------------
FUNCTION ExportToExcel(oRecSet)
//------------------------------------------------------------------------------
   LOCAL  oExcel ,oBook,oSheet
   LOCAL  j, i ,cFormat

    TRY
       oExcel := CreateObject( "Excel.Application"  )
       oBook  := oExcel:Workbooks:Add()
       oSheet := oBook:Worksheets( 1 )
    CATCH
       MsgBox( "Excel not installed" )
       Return
    END
    oRecSet:movefirst()
    CreateExcelHeader(@oExcel,oRecSet)
   FOR J:=1 TO oRecSet:RecordCount


      FOR K:=1 TO oRecSet:Fields:Count-1

         DO CASE
         CASE ValType(oRecSet:Fields( K ):Value) == 'N'
           oExcel:Cells(J+1, k):Value:=oRecSet:Fields( K ):Value
           cFormat     := If( .F., If( .T., "#,##0", "#.##0" ), "0" )
           oSheet:Columns( k):NumberFormat :=cFormat
        CASE ValType(oRecSet:Fields( K ):Value) == 'D'
          oExcel:Cells(J+1, k):Value:=AllTrim(MySqlDateFormat(oRecSet:Fields( K ):Value))
          oSheet:Columns( k):NumberFormat := Lower( Set( _SET_DATEFORMAT ) )
        OTHERWISE
          oExcel:Cells(J+1, k):Value:=oRecSet:Fields( K ):Value
       ENDCASE

     NEXT
    oRecSet:moveNext()
  NEXT
    oSheet:Range( oSheet:Cells( 1, 1 ), oSheet:Cells(1,oRecSet:Fields:Count) ):Font:Bold = .T.
    oSheet:Range(oSheet:Cells( 1, 1 ), oSheet:Cells(1,oRecSet:Fields:Count) ):Select()
    oExcel:Selection:Borders(9):LineStyle := 1
    oExcel:Selection:Borders(9):Weight    := 4
    oExcel:Visible = .T.

    oBook  := Nil
    oSheet := Nil
    oExcel := Nil
RETURN Nil
//------------------------------------------------------------------------------
FUNCTION CreateExcelHeader(oExcel,oRecSet)
//------------------------------------------------------------------------------

   LOCAL Kcnt
   FOR Kcnt:=1 TO oRecSet:Fields:Count-1
         oExcel:Cells(1, Kcnt):Value:=oRecSet:Fields( Kcnt ):Name
   NEXT
RETURN Nil


 

[anserkk]

Dear Mr.Sajith,

Code: Select all  Expand view

oExcel   := CreateObject( "Excel.Application" )
oBook   := oExcel:WorkBooks:Add()
oSheet   := oExcel:ActiveSheet
oSheet:Columns( 1 ):ColumnWidth:=20

Regards
Anser

[sajith]

Dear Anser,

Thanks for ur Replay.Its Works Fine

regards sajith