BlackTDN Search

terça-feira, 20 de setembro de 2011

Protheus :: Advpl :: Utilizando scripts em PowerShell para manipular objetos COM :: Combo I : Excel


Manipulando objetos COM utilizando o PowerShell veio para demonstrar que, com um pouco de "criatividade", podemos usar as ferramentas integradas do SO em nosso benefício. Se você não tem o TOII, poderá abrir mão desse recurso para fazer a sua "integração" com os "Componentes" COM.

Neste primeiro exemplo procurei ser simplista, de forma a facilitar o entendimento e a assimilação, outros mais complexos virão. A idéia aqui é entender o conceito.

Para esse exemplo necessitaremos:

1 . Que as seguintes tabelas estejam "populadas":

  • SRA (Cadastro de Funcionários);
  • SRJ (Funções);
  • SRB (Dependentes);
  • CTT (Centro de Custo);
2. Que o Microsoft Excel esteja instalado no Client (testei com a versão 2007);

3. Que o Windows Power Shell esteja instalado e habilitado a Executar Script (Windows XP Service Pack 3 ou +);
 
4 . MS-SQL 2000 ou +;

A partir do Windows Vista, o Windows PowerShell é parte integrante do SO. Se for executar o teste no Windows XP far-se-a necessário, primeiro, a sua instalação.

O código, disponibilizado para download, contém algumas importantes recomendações acerca da execução de scripts no que diz respeito a "segurança". Sendo assim, recomendo a leitura.

Ao que interessa:

A idéia básica é, gerar uma "Planilha" Excel a partir de Dados Extraídos de um conjunto de Tabelas do Sistema. Normalmente, e para facilitar a geração (na falta das ferramentas TOII) criamos um arquivo "delimitado" ou exportamos os dados para um arquivo ISAM (dbf) e/ou no formato XML e forçamos a abertura pelo Excel. É uma solução criativa, rápida mas limitada.

Para podermos utilizar todos os recursos que um objeto COM nos disponibiliza, no exemplo o Excel (formatações condicionais, inclusão de Fórmulas, formatação de Célula/Planilha, conversão de uma Planilha em uma Tabela Dinâmica, inclusão e formatação de Gráficos), precisaremos mais que isso.

Propositadamente, tornei este primeiro exemplo um pouco mais lento (por isso limitei o intervalo de dados a serem gerados). Isso se fez necessário para a melhor compreensão da idéia base. Os demais executarão mais rápido pois utilizaremos muito mais os recuros do PS e do Excel. Deixando para o Advpl o proposito da Extração dos Dados.

No exemplo a seguir extraimos, usando ADVPL, dados das Tabelas do sistema  e através de "scripts" em PowerShell criamos, populamos, formatamos e apresentamos o nosso objeto COM.

O Código:
/*
***
* Exemplo de Uso do Protheus com PowerShell e Excel através da interação com o shell usando WaitRun.
*
* **************************************************************************************************************************
*
* Função: WaitRun
*
* Executa um programa externo (arquivo executável) através do sistema operacional da estação onde o Smart Client está sendo
* executado, e aguarda pelo término do programa externo.
*
* Sintaxe: WaitRun ( < cExeName>, [ nOpc] ) --> nStatus
*
* **************************************************************************************************************************
*
* Por questoes de seguranca, recomendo ler sobre assinatura de script digitando get-help about_signing no Console do Windows
* PowerShell.
*
* **************************************************************************************************************************
*
* PS C:\> get-help about_signing | more
*
* TÓPICO
* about_signing
*
* DESCRIÇÃO RESUMIDA
* Explica como assinar scripts em conformidade com as diretiva
* execução do Windows PowerShell.
*
* DESCRIÇÃO LONGA
* A diretiva de execução Restricted não permite a execução de
* As diretivas de execução AllSigned e RemoteSigned impedem o
* PowerShell de executar scripts sem uma assinatura digital.
*
* Este tópico explica como executar scripts selecionados não
* assinados, até mesmo com a diretiva de execução RemoteSigned
* como assinar scripts para seu próprio uso.
*
* Para obter mais informações sobre as diretivas de execução d
* PowerShell, consulte about_Execution_Policy.
*
*
* PARA PERMITIR A EXECUÇÃO DE SCRIPTS ASSINADOS
* -------------------------------
* Quando você inicia o Windows PowerShell pela primeira vez em
* computador, é provável que a diretiva de execução Restricted
* (padrão) esteja em vigor.
*
* A diretiva Restricted não permite a execução de scripts.
*
* Para determinar qual a diretiva de execução em vigor no seu
* te:
*
* get-executionpolicy
*
* Para executar scripts não assinados gravados por você no seu
* computador local e scripts assinados de outros usuários, use
* comando a seguir para alterar a diretiva de execução no
* computador para RemoteSigned:
*
* set-executionpolicy remotesigned
*
* Para obter mais informações, consulte Set-ExecutionPolicy.
*
* **************************************************************************************************************************
*
* Para a didatica do exemplo, autorize a execução dos scripts digitando: Set-ExecutionPolicy unrestricted no Console do
* PowerShell como:
*
* **************************************************************************************************************************
*
* PS C:\> Set-ExecutionPolicy unrestricted
*
* Alteração da Diretiva de Execução
* A diretiva de execução ajuda a proteger contra scripts não confiáveis. A
* alteração da diretiva de execução pode implicar em exposição aos riscos de
* segurança descritos no tópico da ajuda about_Execution_Policies. Deseja alterar
* a diretiva de execução?
* [S] Sim [N] Não [U] Suspender [?] Ajuda (o padrão é "S"): S
*
* **************************************************************************************************************************
*
* Isso permitira que qualquer script seja executado pelo PowerShell. O Recomendado é, por questões óbvias de segurança, que
* todo script seja assinado.
*
* Para entender e aprender um pouco mais sobre assinatura de um script consulte:
*
* Windows PowerShell: about_Signing
* Windows PowerShell: Set-ExecutionPolicy: < http://technet.microsoft.com/pt-br/library/dd347628.aspx>
* Windows PowerSheel: Protegendo o Shell
* Windows PowerSheel: Protegendo o Shell
* Windows PowerShell: Prevenção contra código mal-intencionado
*
* **************************************************************************************************************************
*
* Interessando em entender um pouco mais sobre Windows PowerShell:
*
* Windows PowerShell em Ação
* PowerShell Tutorials
* Windows PowerShell 1.0 Documentation Pack
***
*/

#INCLUDE "PROTHEUS.CH"
#INCLUDE "TBICONN.CH"
#INCLUDE "TOPCONN.CH"
#INCLUDE "DBSTRUCT.CH"
#INCLUDE "SHELL.CH"

#DEFINE PS_CREATE_EXCEL_FILE 1
#DEFINE PS_ADD_LINE_EXCEL_FILE 2
#DEFINE PS_SHOW_EXCEL_FILE 3

#DEFINE MAX_TOP_SELECT "50"

Static __cCRLF := CRLF

/*/
Funcao: U_T2PSExcel
Autor: Marinaldo de Jesus (Sharing the Experience)
Data: 19/09/2011
Descricao: Exemplo de Integracao Totvs/Protheus vs PowerShell & Excel via WaitRun
Sintaxe:
/*/
User Function T2PSExcel()

Local lPrepEnv := ( IsBlind() .or. ( Select( "SM0" ) == 0 ) )
Local lSetCentury := .F.

IF ( lPrepEnv )
PREPARE ENVIRONMENT EMPRESA "01" FILIAL "01"
EndIF

SetsDefault()
lSetCentury := __SetCentury( "ON" )

MsgRun( "Aguarde...." , "Gerando Planilha Excel...." , { || T2PSExcel() } )

IF ( lPrepEnv )
RESET ENVIRONMENT
EndIF

__SetCentury( IF( lSetCentury , "ON" , "OFF" ) )

Return( NIL )

/*/
Funcao: T2PSExcel
Autor: Marinaldo de Jesus (Sharing the Experience)
Data: 19/09/2011
Descricao: Exemplo de Integracao Totvs/Protheus vs PowerShell & Excel via WaitRun
Sintaxe:
/*/
Static Function T2PSExcel()

Local aDbStruct

Local cPath := GetTempPath()
Local cQuery := ""
Local cExcelFile

Local cNextAlias := GetNextAlias()

Local nLine

cExcelFile := PsNewExcelFile( cPath )
IF !( cExcelFile == "ERROR" )

cQuery := "SELECT" + __cCRLF
cQuery += " TOP " + MAX_TOP_SELECT + __cCRLF
cQuery += " SRA.RA_FILIAL," + __cCRLF
cQuery += " SRA.RA_MAT," + __cCRLF
cQuery += " 'NOME DO FUNCIONARIO' + ' ' + SRA.RA_FILIAL + ' ' + SRA.RA_MAT AS RA_NOME, --SRA.RA_NOME" + __cCRLF
cQuery += " SRA.RA_ADMISSA," + __cCRLF
cQuery += " SRA.RA_CODFUNC," + __cCRLF
cQuery += " 'FUNCAO DO FUNCIONARIO' + ' ' + SRA.RA_FILIAL + ' ' + SRA.RA_CODFUNC AS RJ_DESC, --SRJ.RJ_DESC" + __cCRLF
cQuery += " SRA.RA_SALARIO," + __cCRLF
cQuery += " SRA.RA_CC," + __cCRLF
cQuery += " 'CENTRO DE CUSTO DO FUNCIONARIO' + ' ' + SRA.RA_FILIAL + ' ' + SRA.RA_CC AS CTT_DESC01, --CTT.CTT_DESC01" + __cCRLF
cQuery += " ( " + __cCRLF
cQuery += " SELECT " + __cCRLF
cQuery += " COUNT(1) " + __cCRLF
cQuery += " FROM " + __cCRLF
cQuery += " " + RetSqlName( "SRB" ) + " SRB WITH (NOLOCK)" + __cCRLF
cQuery += " WHERE " + __cCRLF
cQuery += " SRB.RB_FILIAL = SRA.RA_FILIAL " + __cCRLF
cQuery += " AND" + __cCRLF
cQuery += " SRB.RB_MAT = SRA.RA_MAT" + __cCRLF
cQuery += " ) AS RB_DEP" + __cCRLF
cQuery += "FROM" + __cCRLF
cQuery += " " + RetSqlName( "SRA" ) + " SRA WITH (NOLOCK)" + __cCRLF
cQuery += "INNER JOIN" + __cCRLF
cQuery += " " + RetSqlName( "SRJ" ) + " SRJ WITH (NOLOCK)" + __cCRLF
cQuery += "ON" + __cCRLF
cQuery += "(" + __cCRLF
cQuery += " (" + __cCRLF
cQuery += " SRJ.RJ_FILIAL = ' '" + __cCRLF
cQuery += " OR" + __cCRLF
cQuery += " SRJ.RJ_FILIAL = SRA.RA_FILIAL" + __cCRLF
cQuery += " )" + __cCRLF
cQuery += " AND" + __cCRLF
cQuery += " SRJ.RJ_FUNCAO = SRA.RA_CODFUNC" + __cCRLF
cQuery += ")" + __cCRLF
cQuery += "INNER JOIN" + __cCRLF
cQuery += " " + RetSqlName( "CTT" ) + " CTT WITH (NOLOCK)" + __cCRLF
cQuery += "ON" + __cCRLF
cQuery += "(" + __cCRLF
cQuery += " (" + __cCRLF
cQuery += " CTT.CTT_FILIAL = ' '" + __cCRLF
cQuery += " OR" + __cCRLF
cQuery += " CTT.CTT_FILIAL = SRA.RA_FILIAL" + __cCRLF
cQuery += " )" + __cCRLF
cQuery += " AND" + __cCRLF
cQuery += " CTT.CTT_CUSTO = SRA.RA_CC" + __cCRLF
cQuery += ")" + __cCRLF
cQuery += "ORDER BY" + __cCRLF
cQuery += " SRA.RA_FILIAL," + __cCRLF
cQuery += " SRA.RA_CC," + __cCRLF
cQuery += " SRA.RA_MAT" + __cCRLF

TCQUERY ( cQuery ) ALIAS ( cNextAlias ) NEW

TcSetField( cNextAlias , "RA_SALARIO" , GetSx3Cache( "RA_SALARIO" , "X3_TIPO" ) , GetSx3Cache( "RA_SALARIO" , "X3_TAMANHO" ) , GetSx3Cache( "RA_SALARIO" , "X3_DECIMAL" ) )
TcSetField( cNextAlias , "RA_ADMISSA" , GetSx3Cache( "RA_ADMISSA" , "X3_TIPO" ) , GetSx3Cache( "RA_ADMISSA" , "X3_TAMANHO" ) , GetSx3Cache( "RA_ADMISSA" , "X3_DECIMAL" ) )
TcSetField( cNextAlias , "RB_DEP" , "N" , 3 , 0 )

aDbStruct := ( cNextAlias )->( dbStruct() )
nFields := Len( aDbStruct )
nLine := 1

( cNextAlias )->( PsAddExcelLine( cExcelFile , cPath , aDbStruct , nFields , nLine ) )

While ( cNextAlias )->( !Eof() )

( cNextAlias )->( PsAddExcelLine( cExcelFile , cPath , aDbStruct , nFields , ++nLine ) )

( cNextAlias )->( dbSkip() )

End While

( cNextAlias )->( dbCloseArea() )

PsExecute( PS_SHOW_EXCEL_FILE , cExcelFile , cPath )

EndIF

dbSelectArea( "SRA" )

Return( NIL )

/*/
Funcao: PsAddExcelLine
Autor: Marinaldo de Jesus (Sharing the Experience)
Data: 19/09/2011
Descricao: Adiciona uma Nova Linha na Planilha Excel
Sintaxe:
/*/
Static Function PsAddExcelLine( cExcelFile , cPSPath , aDbStruct , nFields , nLine )

Local cLine := ""
Local cValue
Local cFieldN
Local cPicture
Local cFormatCell := ""

Local nField

IF ( nLine == 1 )

For nField := 1 To nFields

cFieldN := aDbStruct[ nField ][ DBS_NAME ]

cValue := GetSx3Cache( cFieldN , "X3_TITULO" )
DEFAULT cValue := cFieldN

cLine += "'"
cLine += cValue
cLine += "'"

cFormatCell += "'"
cFormatCell += "Geral"
cFormatCell += "'"

IF ( nField < nFields )
cLine += ","
cFormatCell += ","
EndIF

Next nField

Else

For nField := 1 To nFields

cFieldN := aDbStruct[ nField ][ DBS_NAME ]
cPicture := GetSx3Cache( cFieldN , "X3_PICTURE" )
cValue := AllToChar( FieldGet( nField ) , cPicture )
DEFAULT cPicture := ""

cFormatCell += "'"
IF ( aDbStruct[ nField ][ DBS_TYPE ] == "N" )
IF ( "@E" $ cPicture )
cValue := StrTran( cValue , "." , "" )
ElseIF ( "@R" $ cPicture )
cValue := StrTran( cValue , "," , "" )
cValue := StrTran( cValue , "." , "," )
ElseIF ( "," $ cValue ) .and. ( "." $ cValue )
cValue := StrTran( cValue , "," , "" )
cValue := StrTran( cValue , "." , "," )
EndIF
IF ( "," $ cValue )
cFormatCell += "#.##0,00"
Else
cFormatCell += "0"
EndIF
ElseIF ( aDbStruct[ nField ][ DBS_TYPE ] == "C" )
IF ( SubStr( cValue , 1 , 1 ) $ "0123456789" )
cFormatCell += Replicate( "0" , aDbStruct[ nField ][ DBS_LEN ] )
Else
cFormatCell += "Geral"
EndIF
ElseIF ( aDbStruct[ nField ][ DBS_TYPE ] == "D" )
cFormatCell += "dd/mm/aaaa"
EndIF
cFormatCell += "'"

cLine += "'"
cLine += cValue
cLine += "'"

IF ( nField < nFields )
cLine += ","
cFormatCell += ","
EndIF

Next nField

EndIF

PsExecute( PS_ADD_LINE_EXCEL_FILE , cExcelFile , cPSPath , cLine , nLine , cFormatCell )

Return( NIL )

/*/
Funcao: PsNewExcelFile
Autor: Marinaldo de Jesus (Sharing the Experience)
Data: 19/09/2011
Descricao: Cria uma Nova Planilha Excel
Sintaxe:
/*/
Static Function PsNewExcelFile( cPath )

Local cExcelFile := ( CriaTrab( NIL , .F. ) + ".xlsx" )
Local cNewExcelFile := ""

IF !( SubStr( cPath , -1 ) == "\" )
cPath += "\"
EndIF

cNewExcelFile := Lower( cPath + cExcelFile )
While File( cNewExcelFile )
cExcelFile := ( CriaTrab( NIL , .F. ) + ".xlsx" )
cNewExcelFile := Lower( cPath + cExcelFile )
End While

PsExecute( PS_CREATE_EXCEL_FILE , cNewExcelFile , cPath )

IF !( File( cNewExcelFile ) )
cNewExcelFile := "ERROR"
EndIF

Return( cNewExcelFile )

/*/
Funcao: PsExecute
Autor: Marinaldo de Jesus (Sharing the Experience)
Data: 19/09/2011
Descricao: Cria e Executa, via WaitRun, os Scripts em PowerShell
Sintaxe:
/*/
Static Function PsExecute( nScript , cExcelFile , cPSPath , cLine , nLine , cFormatCell )

Local cPsFile := ( CriaTrab( NIL , .F. ) + ".ps1" )
Local cStrLine := ""
Local cPsScript := ""
Local cNewPsFile := ""
Local cWaitRunCmd := ""

Local lStatus := .F.

cPsScript += '# -----------------------------------------------------'+ __cCRLF
cPsScript += 'function Release-Ref ($ref) {' + __cCRLF
cPsScript += ' ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(' + __cCRLF
cPsScript += ' [System.__ComObject]$ref) -gt 0)' + __cCRLF
cPsScript += ' [System.GC]::Collect()' + __cCRLF
cPsScript += ' [System.GC]::WaitForPendingFinalizers()' + __cCRLF
cPsScript += '}' + __cCRLF
cPsScript += '# -----------------------------------------------------' + __cCRLF
cPsScript += '$objExcel = New-Object -Com Excel.Application;' + __cCRLF
DO CASE
CASE ( nScript == PS_CREATE_EXCEL_FILE )
cPsScript += '$objExcel.Visible = $False;' + __cCRLF
cPsScript += '$objExcel.DisplayAlerts = $False;' + __cCRLF
cPsScript += '$objWorkBook = $objExcel.Workbooks.Add();' + __cCRLF
cPsScript += '$objWorkBook.SaveAs("'+cExcelFile+'");' + __cCRLF
cPsScript += '$objExcel.Quit();' + __cCRLF
cPsScript += '$dummy = Release-Ref($objWorkBook) | Out-Null;' + __cCRLF
CASE ( nScript == PS_ADD_LINE_EXCEL_FILE )
cStrLine := AllTrim(Str(nLine))
cPsScript += '$objExcel.Visible = $False;' + __cCRLF
cPsScript += '$objExcel.DisplayAlerts = $False;' + __cCRLF
cPsScript += '$objWorkBook = $objExcel.Workbooks.Open("'+cExcelFile+'");' + __cCRLF
cPsScript += '$objWorksheet = $objWorkBook.Worksheets.Item(1);' + __cCRLF
cPsScript += '$aColsValue=(' + cLine + ');' + __cCRLF
cPsScript += '$aFormat=(' + cFormatCell + ');' + __cCRLF
cPsScript += '[int]$nColNum=0;' + __cCRLF
cPsScript += '[int]$nFormat=0;' + __cCRLF
cPsScript += '[String]$sFormat="";' + __cCRLF
cPsScript += 'ForEach( $sColValue in $aColsValue )' + __cCRLF
cPsScript += '{' + __cCRLF
cPsScript += ' ++$nColNum;' + __cCRLF
cPsScript += ' $sFormat=$aFormat[$nFormat++];' + __cCRLF
cPsScript += ' $objWorksheet.Cells.Item('+cStrLine+',$nColNum) = $sColValue;' + __cCRLF
cPsScript += ' $objWorksheet.Cells.Item('+cStrLine+',$nColNum).NumberFormat = $sFormat;' + __cCRLF
cPsScript += ' $objWorksheet.Cells.Item('+cStrLine+',$nColNum).Activate() | Out-Null;' + __cCRLF
cPsScript += ' $objWorksheet.Cells.Item('+cStrLine+',$nColNum).NumberFormat = $sFormat;' + __cCRLF
cPsScript += ' $objExcel.ActiveCell.NumberFormat = $sFormat;' + __cCRLF
cPsScript += '}' + __cCRLF
cPsScript += '$objWorkBook.SaveAs("'+cExcelFile+'");' + __cCRLF
cPsScript += '$objExcel.Quit();' + __cCRLF
cPsScript += '$dummy = Release-Ref($objWorksheet) | Out-Null;' + __cCRLF
cPsScript += '$dummy = Release-Ref($objWorkbook) | Out-Null;' + __cCRLF
CASE ( nScript == PS_SHOW_EXCEL_FILE )
cPsScript += '$objWorkBook = $objExcel.Workbooks.Open("'+cExcelFile+'");' + __cCRLF
cPsScript += '$objWorksheet = $objWorkBook.Worksheets.Item(1);' + __cCRLF
cPsScript += '$objWorksheet.Cells.Item(1,1).Activate() | Out-Null;' + __cCRLF
cPsScript += '$objRange = $objWorksheet.UsedRange;' + __cCRLF
cPsScript += '[void] $objRange.EntireColumn.Autofit();' + __cCRLF
cPsScript += '$objExcel.DisplayAlerts = $True;' + __cCRLF
cPsScript += '$objExcel.Visible = $True;' + __cCRLF
cPsScript += '$dummy = Release-Ref($objWorksheet) | Out-Null;' + __cCRLF
cPsScript += '$dummy = Release-Ref($objWorkbook) | Out-Null;' + __cCRLF
END CASE
cPsScript += '$dummy = Release-Ref($objExcel) | Out-Null;' + __cCRLF

IF !( SubStr( cPSPath , -1 ) == "\" )
cPSPath += "\"
EndIF

cNewPsFile := Lower( cPSPath + cPsFile )
While File( cNewPsFile )
cPsFile := ( CriaTrab( NIL , .F. ) + ".ps1" )
cNewPsFile := Lower( cPSPath + cPsFile )
End While

MemoWrite( cNewPsFile , cPsScript )

IF ( File( cNewPsFile ) )

cWaitRunCmd := "PowerShell -NonInteractive -WindowStyle Hidden -File " + cNewPsFile + ""

lStatus := ( WaitRun( cWaitRunCmd , SW_HIDE ) == 0 )

fErase( cNewPsFile )

EndIF

Return( lStatus )

E termos uma Planilha com as seguinte informações e formato:


image


O Fragmento que irá fazer esse “milagre” será:






/*/
    Funcao:        PsExecute
    Autor:        Marinaldo de Jesus (Sharing the Experience)
    Data:        19/09/2011
    Descricao:    Cria e Executa, via WaitRun, os Scripts em PowerShell
    Sintaxe:    <vide parametros formais>
/*/
Static Function PsExecute( nScript , cExcelFile , cPSPath , cLine , nLine , cFormatCell )

    Local cPsFile        := ( CriaTrab( NIL , .F. ) + ".ps1" )
    Local cStrLine        := ""
    Local cPsScript        := ""
    Local cNewPsFile    := ""
    Local cWaitRunCmd    := ""

    Local lStatus        := .F.

    cPsScript     += '# -----------------------------------------------------'+ __cCRLF
    cPsScript     += 'function Release-Ref ($ref) {' + __cCRLF
    cPsScript     += '    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(' + __cCRLF
    cPsScript     += '    [System.__ComObject]$ref) -gt 0)' + __cCRLF
    cPsScript     += '    [System.GC]::Collect()' + __cCRLF
    cPsScript     += '    [System.GC]::WaitForPendingFinalizers()' + __cCRLF
    cPsScript     += '}' + __cCRLF
    cPsScript     += '# -----------------------------------------------------' + __cCRLF
    cPsScript     += '$objExcel    = New-Object -Com Excel.Application;' + __cCRLF
    DO CASE
    CASE ( nScript == PS_CREATE_EXCEL_FILE )
        cPsScript     += '$objExcel.Visible        = $False;' + __cCRLF
        cPsScript     += '$objExcel.DisplayAlerts    = $False;' + __cCRLF
        cPsScript     += '$objWorkBook = $objExcel.Workbooks.Add();' + __cCRLF
        cPsScript     += '$objWorkBook.SaveAs("'+cExcelFile+'");' + __cCRLF
        cPsScript     += '$objExcel.Quit();' + __cCRLF
        cPsScript     += '$dummy = Release-Ref($objWorkBook)    | Out-Null;' + __cCRLF
    CASE ( nScript == PS_ADD_LINE_EXCEL_FILE )
        cStrLine    := AllTrim(Str(nLine))
        cPsScript     += '$objExcel.Visible             = $False;' + __cCRLF
        cPsScript     += '$objExcel.DisplayAlerts    = $False;' + __cCRLF
        cPsScript     += '$objWorkBook  = $objExcel.Workbooks.Open("'+cExcelFile+'");' + __cCRLF
        cPsScript     += '$objWorksheet = $objWorkBook.Worksheets.Item(1);' + __cCRLF
        cPsScript     += '$aColsValue=(' + cLine + ');' + __cCRLF
        cPsScript    += '$aFormat=(' + cFormatCell + ');' + __cCRLF
        cPsScript     += '[int]$nColNum=0;' + __cCRLF
        cPsScript     += '[int]$nFormat=0;' + __cCRLF
        cPsScript     += '[String]$sFormat="";' + __cCRLF
        cPsScript     += 'ForEach( $sColValue in $aColsValue )' + __cCRLF
        cPsScript     += '{' + __cCRLF
        cPsScript     += '    ++$nColNum;' + __cCRLF
        cPsScript     += '    $sFormat=$aFormat[$nFormat++];' + __cCRLF
        cPsScript     += '    $objWorksheet.Cells.Item('+cStrLine+',$nColNum) = $sColValue;' + __cCRLF
        cPsScript     += '    $objWorksheet.Cells.Item('+cStrLine+',$nColNum).NumberFormat = $sFormat;' + __cCRLF
        cPsScript     += '    $objWorksheet.Cells.Item('+cStrLine+',$nColNum).Activate() | Out-Null;' + __cCRLF
        cPsScript     += '    $objWorksheet.Cells.Item('+cStrLine+',$nColNum).NumberFormat = $sFormat;' + __cCRLF
        cPsScript     += '    $objExcel.ActiveCell.NumberFormat = $sFormat;' + __cCRLF
        cPsScript     += '}' + __cCRLF
        cPsScript     += '$objWorkBook.SaveAs("'+cExcelFile+'");' + __cCRLF
        cPsScript     += '$objExcel.Quit();' + __cCRLF
        cPsScript     += '$dummy = Release-Ref($objWorksheet)    | Out-Null;' + __cCRLF
        cPsScript     += '$dummy = Release-Ref($objWorkbook)    | Out-Null;' + __cCRLF
    CASE ( nScript == PS_SHOW_EXCEL_FILE )
        cPsScript     += '$objWorkBook                = $objExcel.Workbooks.Open("'+cExcelFile+'");' + __cCRLF
        cPsScript     += '$objWorksheet                 = $objWorkBook.Worksheets.Item(1);' + __cCRLF
        cPsScript     += '$objWorksheet.Cells.Item(1,1).Activate() | Out-Null;' + __cCRLF
        cPsScript     += '$objRange                     = $objWorksheet.UsedRange;' + __cCRLF
        cPsScript     += '[void] $objRange.EntireColumn.Autofit();' + __cCRLF
        cPsScript     += '$objExcel.DisplayAlerts        = $True;' + __cCRLF
        cPsScript     += '$objExcel.Visible            = $True;' + __cCRLF
        cPsScript     += '$dummy = Release-Ref($objWorksheet)    | Out-Null;' + __cCRLF
        cPsScript     += '$dummy = Release-Ref($objWorkbook)    | Out-Null;' + __cCRLF
    END CASE
    cPsScript     += '$dummy = Release-Ref($objExcel)    | Out-Null;' + __cCRLF

    IF !( SubStr( cPSPath , -1 ) == "\" )
        cPSPath += "\"
    EndIF

    cNewPsFile            := Lower( cPSPath + cPsFile )
    While File( cNewPsFile )
        cPsFile            := ( CriaTrab( NIL , .F. ) + ".ps1" )
        cNewPsFile        := Lower( cPSPath + cPsFile )
    End While

    MemoWrite( cNewPsFile , cPsScript )

    IF ( File( cNewPsFile ) )

        cWaitRunCmd    := "PowerShell -NonInteractive -WindowStyle Hidden -File " + cNewPsFile + ""

        lStatus := ( WaitRun( cWaitRunCmd , SW_HIDE ) == 0 )

        fErase( cNewPsFile )

    EndIF

Return( lStatus )


Considerando que os dados extraídos são dados reais, manipulei a “query” para proteger informações sigilosas. Modifique-a para que traga os dados dos funcionários conforme baixo:


De:






cQuery += " 'NOME DO FUNCIONARIO' + ' ' +  SRA.RA_FILIAL + ' ' + SRA.RA_MAT AS RA_NOME, --SRA.RA_NOME" + __cCRLF
cQuery += " SRA.RA_ADMISSA," + __cCRLF
cQuery += " SRA.RA_CODFUNC," + __cCRLF
cQuery += " 'FUNCAO DO FUNCIONARIO' + ' ' +  SRA.RA_FILIAL + ' ' + SRA.RA_CODFUNC AS RJ_DESC, --SRJ.RJ_DESC" + __cCRLF
cQuery += " SRA.RA_SALARIO," + __cCRLF
cQuery += " SRA.RA_CC," + __cCRLF
cQuery += " 'CENTRO DE CUSTO DO FUNCIONARIO' + ' ' +  SRA.RA_FILIAL + ' ' + SRA.RA_CC AS CTT_DESC01,--CTT.CTT_DESC01" + __cCRLF


Para:






cQuery += “ SRA.RA_NOME,” + __cCRLF
cQuery += " SRA.RA_ADMISSA," + __cCRLF
cQuery += " SRA.RA_CODFUNC," + __cCRLF
cQuery += “ RJ_DESC,” + __cCRLF
cQuery += " SRA.RA_SALARIO," + __cCRLF
cQuery += " SRA.RA_CC," + __cCRLF
cQuery += “ CTT_DESC01," + __cCRLF



Para baixar o código do exemplo, clique aqui


[]s


иαldσ dj

7 comentários:

  1. Boa tarde, estou testando esta implementação e observei que rodando pelo waitrun não cria o arquivo xlsx. Porem se rodar no PowerShell direto o sistema cria o arquivo.

    ResponderExcluir
    Respostas
    1. Qual a versão do SO em que o remote está sendo executado?

      Excluir
    2. Tente executar o remote como administrador e veja se via waitrun funciona. Caso contrário envie a instrução que está tentando executar.

      Excluir
  2. Naldo, estou enfrentando um problema parecido. Criei um script simples para gerar um arquivo xls e pelo prompt funciona normal, mas pelo Protheus não.
    Já tentei executar o smartclient como administrador e não funcionou.
    Estou utilizando o Windows 8.1
    Alguma dica?

    ResponderExcluir
  3. Srs.

    Estou com o mesmo problema, rodando pelo smartclient nao cria o arquivo, apenas no powershell direto. Foi configurada a execucao de scripts como: Set-ExecutionPolicy unrestricted.

    Pus o waitrun para exibir a janela e encontrei o seguinte erro:

    O Arquivo C:\Temp\sc003160.ps1 nao pode ser carregado porque a execucao de scripts foi desabilitada nesse sistema. Consulte "get-help about_signing" para obter mais detalhes.

    + CategoryInfo : NotSpecifield: (:) [], ParentContainsErrorException
    + FullyQualifiedErrorId: RuntimeException

    Ja alterei a configuracao de scripts para varias formas e ate agora nada.

    Alguém tem uma luz?

    Atenciosamente

    Ewerton Carreira

    ResponderExcluir
  4. Bom dia senhores,

    tenho o mesmo problema dos colegas acima... alguma sugestão para resolução?

    ResponderExcluir