pivot table

SELECT DIST wkakod FROM sonuc INTO CURSOR firmalar

SELECT DIST siptar FROM sonuc INTO CURSOR tarihler

dosyaa=SYS(3)

dosyaadi = SYS(2023)+ "\"+dosyaa

SELECT sonuc

EXPORT TO &dosyaadi TYPE XL5 &&to xls file

lcRows = LTRIM(STR(RECCOUNT('sonuc')+1)) && Including header line

lcCols = LTRIM(STR(FCOUNT('sonuc')))

lcsatir= RECCOUNT('firmalar')

oExcel = CREATEOBJECT("Excel.application")

#DEFINE xlDatabase 1

#DEFINE xlRowField 1

#DEFINE xlColumnField 2

#DEFINE xlPageField 3

#DEFINE xlDataField 4

#DEFINE xlsum -4157

#DEFINE xlRangeAutoFormatColor2 37

#DEFINE xlRangeAutoFormatTable1 12

WITH oExcel

    .workbooks.OPEN(dosyaadi) && Open saved xls

    WITH .ActiveWorkBook

        .ActiveSheet.PivotTableWizard(xlDatabase, ;

            "&dosyaa"+"!R1C1:R"+lcRows+"C"+lcCols,"", "PivotTable1") && Wizard sets Range and name

        WITH .ActiveSheet.PivotTables("PivotTable1")

            .AddFields( "wkakod", "siptar", "sfiyat" ) && Fields added

            .PivotFields("wkakod").ORIENTATION = xlRowField   && Dates added as second row field

            .PivotFields("sfiyat").ORIENTATION = xlDataField && Quantity set as data field

            .PivotFields("sure").ORIENTATION = xlDataField && Quantity set as data field

            .PivotFields("hamfiyat").ORIENTATION = xlDataField && Quantity set as data field

            IF THISFORM.Optiongroup1.VALUE=2

                .PivotFields("Count of hamfiyat").FUNCTION= xlsum

            ENDIF

            IF THISFORM.Optiongroup1.VALUE=1 AND EMPTY(THISFORM.text6.VALUE)

                .PivotFields("Count of Sure").FUNCTION= xlsum

                .PivotFields("Count of hamfiyat").FUNCTION= xlsum

            ENDIF

        ENDWITH

        .ActiveSheet.RANGE("A1").AutoFormat(xlRangeAutoFormatColor2)

        .ActiveSheet.RANGE("A1").AutoFormat(xlRangeAutoFormatTable1)

 

    ENDWITH

    oExcel.COLUMNS("A:BB").COLUMNWIDTH = 11

    oExcel.COLUMNS("B:CC").NumberFormat = "##,###"

    oExcel.RANGE('A4:CC4').NumberFormat = "MmmM-yy"

    oExcel.Cells(1,3).SELECT

    oExcel.ActiveCell.VALUE = "TARİH-SAAT"

    oExcel.ActiveCell.FONT.bold = .T.

    oExcel.Cells(1,4).SELECT

    oExcel.ActiveCell.VALUE = "'"+TTOC(DATETIME())

    oExcel.ActiveCell.FONT.bold = .T.

    oExcel.Cells(1,6).SELECT

    oExcel.ActiveCell.VALUE = "Filitreler: "+DTOC(THISFORM.text7.VALUE)+' '+ALLTRIM(IIF(THISFORM.Optiongroup1.VALUE=1,',Satışlar',',Siparişler'))+',Ab Number '+THISFORM.text6.VALUE+' Olanlar.'

    oExcel.Cells(5,2).SELECT

    oExcel.ActiveCell.VALUE ='Tutar'

    oExcel.Cells(5,3).SELECT

    oExcel.ActiveCell.VALUE ='Saat'

    oExcel.Cells(5,4).SELECT

    oExcel.ActiveCell.VALUE ='Hammadde'

    *    oexcel.Range("A1:D7").Select

    *   oexcel.Selection.BorderS(7).LineStyle = 1 &&taralı alana yaz cizgi çizer 6,7,8,9, kenar nolar.

 .VISIBLE = .T.