FiveTech Software tech support forums

Code: Select all  Expand view

oExcel  := TLibXL():New()
oBook   := oExcel:CreateXMLBook() // create .xlsx
oBook:setLocale('UTF-8') // encode utf8,  oBook:setLocale('en_US.BIG5') // ansi
oSheet := oBook:AddSheet( oQry:FieldGet("CR_COD"))
// Font
oBook:setDefaultFont('細明體',12) // 設定整體字型
//
oFnt1 := oBook:AddFont(); oFnt1:SetName('細明體'); oFnt1:SetSize(12)
oFnt2 := oBook:AddFont(); oFnt2:SetName('細明體'); oFnt2:SetSize(16)
// Format
oFmtR := oBook:AddFormat(); oFmtR:setAlignH( ALIGNH_RIGHT          ) // Horizontal - right
oFmtL := oBook:AddFormat(); oFmtL:setAlignH( ALIGNH_LEFT           ) // Horizontal - left
oFmt1 := oBook:AddFormat(); oFmt1:setNumFormat(NUMFORMAT_TEXT      )
oFmt2 := oBook:AddFormat(); oFmt2:setNumFormat(NUMFORMAT_NUMBER    ) // ex: 1000
oFmt3 := oBook:AddFormat(); oFmt3:setNumFormat(NUMFORMAT_NUMBER_D2 ) // ex: 1000.00
// create format formula '0.0'
nFmtD1 := oBook:AddCustomNumFormat("0.0")
oFmtD1 := oBook:AddFormat(); oFmtD1:setNumFormat(nFmtD1) // ex: 1000.0
//
lPtHead := .T.
nRow    := 1
//
Do While ! oQry:Eof()
   //
   If lPtHead
      //
      lPtHead := .F.
      //
      nRow := 1
      oSheet:writeStr( nRow, 1, '客戶訂貨單')
      // oSheet:setMerge( 1, 1, 1, Len(aHD) ) // merge
      //
      nRow += 1
      oSheet:writeStr( nRow, 1, "CustName: "+RTrim( oQry:FieldGet("CT_CAM"))+"("+RTrim( oQry:FieldGet("CT_NUM"))+")" )
      //
      nRow += 1
      oSheet:writeStr( nRow, 1, "Order NO: "+RTrim( oQry:FieldGet("CR_COD")))
      // Print Head
      nRow += 1
      For nI := 1 To Len( aHD )
          If aHD[nI, 3] == 'N' // Number, align-right
             oSheet:SetCol(nI, nI, aHD[nI, 2], oFmtR ) // oExcel:oSheet:Columns( nI ):HorizontalAlignment := xlRight // 靠右
          Else
             oSheet:SetCol(nI, nI, aHD[nI, 2], oFmtL ) // oExcel:oSheet:Columns( nI ):HorizontalAlignment := xlLeft  // 靠左
          EndIf
          //
          oSheet:writeStr( nRow, nI, aHD[nI, 1] ) // oExcel:oSheet:Cells( nRow,  nI ):Value := aHD[nI, 1]
      Next nI
      //
   EndIf
   //
   nRow += 1
   //----------------------------
   nCol := 0
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_NOT"))        )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("CR_ONB")), oFmt2 )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_NAM"))        )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_AUT"))        )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("FM_DAM"))        )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_COD")), oFmt1 )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_ISB")), oFmt1 )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_CIP"))        )
   oSheet:writeNum( nRow, ++nCol, oQry:FieldGet("CR_AMO"), oFmt2         )
   oSheet:writeNum( nRow, ++nCol, oQry:FieldGet("PD_DMN"), oFmt2         )
   oSheet:writeNum( nRow, ++nCol, oQry:FieldGet("PD_TDM"), oFmt2         )
   oSheet:writeNum( nRow, ++nCol, oQry:FieldGet("CR_JER"), oFmt3         )
   oSheet:writeNum( nRow, ++nCol, oQry:FieldGet("CR_DMN"), oFmt3         )
   If Empty(oQry:FieldGet("PD_ODT"))
      cData := ''
   Else
      cData := TransForm( oQry:FieldGet("PD_ODT"), "@R 999.99.99" )
   EndIf
   oSheet:writeStr( nRow, ++nCol, cData )
   If Substr( ::cLimPrd, 3, 1 ) == '1'
      oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_FCO")), oFmt1 ) // oExcel:oSheet:Cells( nRow, 15 ):NumberFormat := [@]       ;
      oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("FM_DA2"))        )
      oSheet:writeNum( nRow, ++nCol,        oQry:FieldGet("PD_FYJ") , oFmt3 ) // oExcel:oSheet:Cells( nRow, 17 ):NumberFormat := [#,##0.00];
   EndIf
   //
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_VER"))    )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_CNT"))    )
   oSheet:writeStr( nRow, ++nCol, TrLnuPrd( oQry:FieldGet("PD_LNU")) )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_BID"))    )
   oSheet:writeStr( nRow, ++nCol, TrPkgPrd( oQry:FieldGet("PD_PKG")) )
   oSheet:writeStr( nRow, ++nCol, TrTfsPrd( oQry:FieldGet("PD_TFS")) )
   oSheet:writeNum( nRow, ++nCol, oQry:FieldGet("PD_PKN"), oFmt2     )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_BCO"))    )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_BC2"))    )
   oSheet:writeStr( nRow, ++nCol, TrRadPrd( oQry:FieldGet("PD_RAD")) )
   oSheet:writeNum( nRow, ++nCol, oQry:FieldGet("PD_HGH"), oFmtD1    )
   oSheet:writeNum( nRow, ++nCol, oQry:FieldGet("PD_WDH"), oFmtD1    )
   oSheet:writeNum( nRow, ++nCol, oQry:FieldGet("PD_LGH"), oFmtD1    )
   oSheet:writeNum( nRow, ++nCol, oQry:FieldGet("PD_PAG"), oFmt2     )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_ARE"))    )
   oSheet:writeStr( nRow, ++nCol, RTrim( oQry:FieldGet("PD_TAK"))    )
   //
   oQry:Skip()
   //
EndDo
//
oQry:End()
// Get temp file and save
Do While .T.
   If ! File( cPath + ( cXLS := cNetFile + PadL( Int( nRandom( 9999 )), 4, "0" )+".xlsx" ))
      oBook:Save(AllTrim(cPath+cXLS))
      oBook:End()
      Exit
   EndIf
EndDo
oExcel:End() // Close
SysRefresh() // Speed too fast, Need add this
 

Code: Select all  Expand view

/*
 * Proyecto: hdo_general
 * Fichero: ej37_xls_rs.prg
 * Descripci�n:
 * Autor:
 * Fecha: 11/04/2020
    Se necesita {Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
    Descargarlo desde:
    http://www.microsoft.com/en-us/download ... x?id=13255
    o en espa�ol aqui
    https://www.microsoft.com/es-ES/downloa ... x?id=13255
 */

//------------------------------------------------------------------------------

#include "hdo.ch"

REQUEST RDLODBCN

//------------------------------------------------------------------------------
// Programa principal

procedure main

    TApp():new( "contacts.xls", "Hoja1" )

return

//------------------------------------------------------------------------------
// Clase aplicacion

CLASS TApp

    DATA oXLS
    DATA oSheet

    METHOD new( cXLS, cSheet ) CONSTRUCTOR
    METHOD end()

ENDCLASS

//------------------------------------------------------------------------------
// Constructor de la clase

METHOD new( cXLS, cSheet ) CLASS TApp

    local e

    if ValType( cXLS ) == 'C' .and. File( cXLS )
        cXLS := AllTrim( cXLS )
        // Creamos el objeto XLS
        ::oXLS := THDO():new( "odbcn" )
        ::oXLS:setAttribute( HDO_ATTR_DEF_STR_PAD, .t. )
        try
            // Intenta crear los objetos XLS y hoja
            ::oXLS:connect( "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=./" + cXLS + ";" )
            ::oSheet := ::oXLS:rowSet( "SELECT * FROM " + "[" + AllTrim( cSheet ) + "$]" )
            ::oSheet:load()
            simpleBrowse( ::oSheet, "Mirando la Hoja1" )
        catch e
            // Gestion del error aqui uso el estandar de harbour
            eval( errorBlock(), e )
        finally
            // Cierre automatico en caso de error
            ::end()
        end
    else
        alert( "No se ha pasado un fichero excel" )
    endif

return Self

//------------------------------------------------------------------------------
// Libera recursos

PROCEDURE end() CLASS TApp

    //libera los objetos en orden inverso a su creacion

    if ValType( ::oSheet ) == 'O'
        ::oSheet:free()
    endif

    if ValType( ::oXLS ) == 'O'
        ::oXLS:free()
    endif

    msg( "FIN" )
return

//------------------------------------------------------------------------------

 

carito wrote:Hola:

Donde se encuentra esa libreria TLibXL, es de fivewin o de terceras personas,
me puedes dar mas informacion por favor, gracias.

Esta disponible el codigo fuente o mas informacion.

karinha wrote:Good morning ssbbstw, could you please provide an example translated from Chinese to English? Thanks.

Buenos días ssbbstw, ¿podría proporcionar un ejemplo traducido del chino al inglés? Gracias.

Regards, saludos.