How to Fill a XLSX File

[vilian]

Hi Guys,
A customer sent me a XLSX that i have to fill. How could i open and fill that file ?

[Marc Venken]

I use this to make a file. I think the code I put in // is for opening. The rest will be the same I think.

Code: Select all  Expand view

function makedroponfILE(oBrw)

//  I believe this is for a existing file
  oExcel:=TOleAuto():New( "Excel.Application" )
  oExcel:WorkBooks:Open('P:\tmp\CE005.xlsx')
  oHoja := oExcel:Get( "ActiveSheet" )

 //  I use this for making and filling a Xls from Xbrowse

oExcel := CreateObject( "Excel.Application" )
oExcel:WorkBooks:Add()


oAs := oExcel:Activesheet()

oAs:Cells:Font:Name := "Calibri"
oAs:Cells:Font:Size := 10

//  Making colums
oAs:Columns(  1 ):ColumnWidth := 10
oAs:Columns(  2 ):ColumnWidth := 60
oAs:Columns(  3 ):ColumnWidth := 60
oAs:Columns(  4 ):ColumnWidth := 5
oAs:Columns(  5 ):ColumnWidth := 8
oAs:Columns(  6 ):ColumnWidth := 30
oAs:Columns(  7 ):ColumnWidth := 40
oAs:Columns(  8 ):ColumnWidth := 20
oAs:Columns(  9 ):ColumnWidth := 10
oAs:Columns( 10 ):ColumnWidth := 10
oAs:Columns( 11 ):ColumnWidth := 15
oAs:Columns( 12 ):ColumnWidth := 50
oAs:Columns( 13 ):ColumnWidth := 8
oAs:Columns( 14 ):ColumnWidth := 10
oAs:Columns( 15 ):ColumnWidth := 10
// Making First row (headers)
oAs:Cells( 1,  1 ):Value := "Handle"
oAs:Cells( 1,  2 ):Value := "Recipient"
oAs:Cells( 1,  3 ):Value := "Street and number"
oAs:Cells( 1,  4 ):Value := "Bus"
oAs:Cells( 1,  5 ):Value := "Postal code"
oAs:Cells( 1,  6 ):Value := "City"
oAs:Cells( 1,  7 ):Value := "Email"
oAs:Cells( 1,  8 ):Value := "Phone"
oAs:Cells( 1,  9 ):Value := "Product"
oAs:Cells( 1, 10 ):Value := "Quantity"
oAs:Cells( 1, 11 ):Value := "Capacity (kg)"
oAs:Cells( 1, 12 ):Value := "Comment"
oAs:Cells( 1, 13 ):Value := "Language"
oAs:Cells( 1, 14 ):Value := "Planning date"
oAs:Cells( 1, 15 ):Value := "Reference"

factinfo->(dbclearfilter())
factinfo->(dbgotop())
n = 2
// Start filling
do while !factinfo->(eof())
    if empty(factinfo->tr_handle) .or. alltrim(factinfo->tr_handle) = "SEND"
       factinfo->(dbskip())
       loop
    endif
    klant->(dbseek(factinfo->klant))
    sysrefresh()
    oAs:Cells( n, 1 ):Value := factinfo->TR_handle     // Type (Pickup,Drop,Retour)
    oAs:Cells( n, 2 ):Value := factinfo->TR_Name    // Naam
    oAs:Cells( n, 3 ):Value := factinfo->TR_straat       // Straat
    oAs:Cells( n, 4 ):Value := "" // Bus Nr.
    oAs:Cells( n, 5 ):Value := factinfo->TR_post
    oAs:Cells( n, 6 ):Value := factinfo->TR_plaats
    oAs:Cells( n, 7 ):Value := factinfo->TR_email
    oAs:Cells( n, 8 ):Value := factinfo->TR_tel
    oAs:Cells( n, 9 ):Value := "box"
    oAs:Cells( n,10 ):Value := factinfo->tr_aantal
    oAs:Cells( n,11 ):Value := "25"
    oAs:Cells( n,12 ):Value := factinfo->tr_comment
    oAs:Cells( n,13 ):Value := "NL"
    oAs:Cells( n,14 ):Value := dtoc(factinfo->tr_datum)
    oAs:Cells( n,15 ):Value := factinfo->document
    n++
    if factinfo->(rLock())
       factinfo->TR_handle ="SEND"
    endif
    factinfo->(dbskip())
enddo

cFile:="\\marc\Dropon_"+str(year(date()),4)+str(month(date()),2)+str(day(date()),2)
Cfile = StrTran( cFile, " ", "0" )
oAs:SaveAs( cFile )
oExcel:visible := .T.
msginfo("File Done")
oExcel:quit()
return
 

[vilian]

Thank you Marc,
I'll try it