rubensma Posted December 31, 2014 Report Share Posted December 31, 2014 Boa noite, Estou exportando uma tabela para o excel e quero fazer um sort (classificar) direto do aplicativo no ambiente do Excel, segue a parte que exporta uma das guias. Após exportar quero fazer o sort lá no Excel. // Seleciona a guia MONTAGEM para preenchimento oExcel:Sheets("MONTAGEM"):Select() oMeter:cCaption := "Guia MONTAGEM" oHoja := oExcel:Get( "ActiveSheet" ) dbSelectArea( "mon" ) mon->( dbGoTop() ) FOR nCol := 1 TO FCOUNT() oHoja:Cells( nRow, nCol ):Value := FieldName( nCol ) NEXT DO WHILE .NOT. EOF() nRow++ FOR nCol := 1 TO FCOUNT() if ValType( FieldGet( nCol ) ) = "D" // se for campo data converte p/string oHoja:Cells( nRow, nCol ):Value := dtoc(FieldGet( nCol )) else if ValType( FieldGet( nCol ) ) = "N" // se for campo numérico oHoja:Cells( nRow, 7 ):NumberFormat = "###.##0,00" // FORMATAR numerico else oHoja:Cells( nRow, nCol ):NumberFormat = "@" // FORMATAR TEXTO endif oHoja:Cells( nRow, nCol ):Value := FieldGet( nCol ) endif NEXT oMeter:Set(++nCount) oMeter:nTotal := mon->( RecCount()*1 ) oText:SetText(" Exportando guia Montagem - Registro : "+str(nCount,6)+"/"+str(oMeter:nTotal,6) ) SKIP ENDDO ElliottKl 1 Quote Link to comment Share on other sites More sharing options...
Ariston Santos Posted January 1, 2015 Report Share Posted January 1, 2015 Salva primeiro em vetor. Depois da ASORT no vetor. Em seguida, envia para o Excel. Ou, Indexa seu DBF na ordem desejada. Envia para o Excel classificado pela ordem do índice. -> dbsetorder(nOrd) Um exemplo usando vetor classificado com ASORT() *--------( Relatório de Positivação dos clientes )-----------------------------* FUNCTION RelPositiv() LOCAL lGoOn := .F., lSoPos := .T., lSoNeg := .F., lTodos := .F., lOrdNo:=.T.,lOrdVr:=.F.,lOrdId:=.F. PUBLIC nClie := SPACE(8) ,; cClie := SPACE(50),; nVddr := SPACE(3) ,; cVddr := SPACE(50),; cCliTp:="" d_Fim := CTOD("01/"+STRZERO(MONTH(DATE()),2)+"/"+STRZERO(YEAR(DATE()),4))-1 // Final do mês passado d_Ini := CTOD("01/"+STRZERO(MONTH(d_Fim-90),2)+"/"+STRZERO(YEAR(d_Fim-90),4)) // Início de três mêses atrás DEFINE FONT oDlTtf NAME "Verdana" SIZE 0, -12 DEFINE DIALOG oDPstv FROM 0,0 TO 20, 45 TITLE "Critérios para o relatório" oDPstv:SetFont(oDlTtf) @03, 05 SAY oSay1 PROMPT "Cliente" OF oDPstv SIZE 25, 10 PIXEL @14, 05 GET oNcli VAR nClie PICTURE "99999999" OF oDPstv SIZE 25, 12 PIXEL WHEN ! lTodos UPDATE VALID CliPesqCli(@nClie, @cClie, oDPstv) @13, 32 GET oCcli VAR cClie OF oDPstv SIZE 140, 12 PIXEL WHEN ! lTodos UPDATE MEMO READONLY @28,05 SAY "Tipo" SIZE 50,10 PIXEL OF oDPstv @38,05 COMBOBOX oCbx VAR cCliTp ITEMS {"ESPECIAL","DE VAREJO","DE ATACADO"," "} ; SIZE 170,40 OF oDPstv ; PIXEL @52, 05 SAY oSay2 PROMPT "Vendedor" OF oDPstv SIZE 40, 10 PIXEL @62, 05 GET oNvdd VAR nVddr PICTURE "999" OF oDPstv SIZE 25, 12 PIXEL UPDATE VALID PesqVdr(@nVddr, @cVddr, oDPstv, .F.) @62, 32 GET oCvdd VAR cVddr OF oDPstv SIZE 140, 12 PIXEL UPDATE MEMO READONLY @80, 05 SAY oSay3 PROMPT "Período:" OF oDPstv SIZE 30, 08 PIXEL @78, 37 GET oDat1 VAR d_Ini OF oDPstv SIZE 45, 12 PIXEL @78, 92 GET oDat2 VAR d_Fim OF oDPstv SIZE 45, 12 PIXEL @94, 05 SAY oSay1 PROMPT "Mostrar" OF oDPstv SIZE 25, 10 PIXEL @92, 32 CHECKBOX lSoPos PROMPT "Só Atendidos" SIZE 50,10 PIXEL OF oDPstv UPDATE ON CHANGE EVAL({||(lSoPos:=.T.,lSoNeg:=.F.,lTodos:=.F.,oDPstv:Update()),.T.}) @92, 84 CHECKBOX lSoNeg PROMPT "Não atendidos" SIZE 60,10 PIXEL OF oDPstv UPDATE ON CHANGE EVAL({||(lSoPos:=.F.,lSoNeg:=.T.,lTodos:=.F.,oDPstv:Update()),.T.}) @92,146 CHECKBOX lTodos PROMPT "Todos" SIZE 30,10 PIXEL OF oDPstv UPDATE ON CHANGE EVAL({||(lSoPos:=.F.,lSoNeg:=.F.,lTodos:=.T.,oDPstv:Update()),.T.}) @106, 05 SAY oSay1 PROMPT "Classif." OF oDPstv SIZE 25, 10 PIXEL @104, 32 CHECKBOX lOrdNo PROMPT "Por nome" SIZE 50,10 PIXEL OF oDPstv UPDATE ON CHANGE EVAL({||(lOrdNo:=.T.,lOrdVr:=.F.,lOrdId:=.F.,oDPstv:Update()),.T.}) @104, 84 CHECKBOX lOrdVr PROMPT "Maior valor" SIZE 60,10 PIXEL OF oDPstv UPDATE ON CHANGE EVAL({||(lOrdNo:=.F.,lOrdVr:=.T.,lOrdId:=.F.,oDPstv:Update()),.T.}) @104,146 CHECKBOX lOrdId PROMPT "Código" SIZE 30,10 PIXEL OF oDPstv UPDATE ON CHANGE EVAL({||(lOrdNo:=.F.,lOrdVr:=.F.,lOrdId:=.T.,oDPstv:Update()),.T.}) @130, 35 SBUTTON oS01 PROMPT "Prosseguir" OF oDPstv FILENAME ".\bitmaps\printer.bmp" ACTION (lGoOn := .t., oDPstv:End()) PIXEL SIZE 50, 14 COLORS CLR_BLACK,{nBtClr,CLR_WHITE,3} NOBORDER XP @130, 90 SBUTTON oS02 PROMPT "Cancelar" OF oDPstv FILENAME ".\bitmaps\stop.bmp" ACTION (lGoOn := .f., oDPstv:End()) PIXEL SIZE 50, 14 COLORS CLR_BLACK,{nBtClr,CLR_WHITE,3} NOBORDER XP ACTIVATE DIALOG oDPstv CENTERED ON INIT xFocus(oNcli) oDlTtf:End() IF ! lGoOn ; RETURN NIL ; ENDIF // Definir os meses abrangidos aMeses := {} aTotls := {{},{},{}} FOR nDt := d_Ini TO d_Fim cMano := RIGHT(DTOC(nDt),7) IF ASCAN(aMeses, cMano) == 0 AADD(aMeses, cMano) AADD(aTotls[1], 0) AADD(aTotls[2], 0) AADD(aTotls[3], 0) ENDIF NEXT nTtMes := LEN(aMeses) // Quantidade de meses // Pegar logo todos os clientes SELECT 6 CLIENTE->(DBCLEARFIL(NIL)) CLIENTE->(DBSETORDER(1)) CLIENTE->(DBGOTOP()) aRelInf := {} WHILE ! CLIENTE->(EOF()) IF ! EMPTY(cCliTp) IF ALLTRIM(CLIENTE->TIPOCLI) != cCliTp CLIENTE->(DBSKIP()) LOOP ENDIF ENDIF AADD(aRelInf, {.T.,; // Selecionado CLIENTE->CODCLI,; // Cód. Cliente CLIENTE->NOME}) // Nome Cliente CLIENTE->(DBSKIP()) END IF LEN(aRelInf) = 0 SysRefresh() MsgAlert("Sem clientes que se ajustem aos critérios informados","Aviso") RETURN NIL ENDIF // Criar um array com os meses abrangidos aListFor := {} // Vetor para informações FOR nLin := 1 TO LEN(aRelInf) // Incluir logo todos os clientes nForn := aRelInf[nLin,2] cForn := aRelInf[nLin,3] DECLARE aThisFor[nLin][nTtMes+1] // Quantidade de mêses + total AFILL(aThisFor[nLin], 0) // Iniciar zerado AADD(aThisFor[nLin],nForn) // Acrescentar coluna para codigo do fornecedor AADD(aThisFor[nLin],cForn) // Acrescentar coluna para nome do fornecedor AADD(aListFor, aThisFor[nLin]) NEXT // Pegar as vendas do período cFltr := "" IF VAL(nClie) > 0 cFltr += "VENDAS->ITEM01 = VAL(nClie)" ELSE cFltr += "VENDAS->ITEM01 > VAL('0')" ENDIF IF VAL(nVddr) > 0 IF ! EMPTY(cFltr) ; cFltr += " .AND. " ; ENDIF cFltr += "VENDAS->ITEM36 = VAL(nVddr)" ENDIF // ImpressÆo das vendas cTmpName := "" nFile := 1 WHILE .T. cTmpName := cPasta+"\VNDTMP"+STRZERO(nFile,2)+".CDX" FERASE(cTmpName) if ! file(cTmpName) EXIT endif nFile ++ END cTmpName := "VNDTMP"+STRZERO(nFile,2) SET SOFTSEEK ON SELECT 2 VENDAS->(DBSETORDER(3)) VENDAS->(DBSEEK(d_Ini, .T.)) INDEX ON VENDAS->ITEM48 TAG VNDTMP01 TO &(cTmpName) WHILE( VENDAS->ITEM48 >= d_Ini .AND. VENDAS->ITEM48 <= d_Fim) ordListClear() OrdListAdd(cTmpName, "VNDTMP01") SET SOFTSEEK OFF WHILE ! VENDAS->(EOF()) IF ! EMPTY(cFltr) IF ! &(cFltr) VENDAS->(DBSKIP()) LOOP ENDIF ENDIF c_Codig := VENDAS->ITEM01 d_Movim := VENDAS->ITEM48 n_Valor := VndTotal(1) TRY nSkn := ASCAN(aListFor, {|nCol|nCol[nTtMes+2]==c_Codig}) IF nSkn > 0 nCol := ASCAN(aMeses, RIGHT(DTOC(d_Movim),7)) aListFor[nSkn,nCol] += n_Valor ENDIF CATCH END TRY VENDAS->(DBSKIP()) END // Reabrir vendas para corrigir os índices SELECT 2 CLOSE VENDAS SysWait(0.2) SELECT 2 USE VENDAS SHARED ordListClear() OrdListAdd("VENDAS", "VENDCODI", "VENDCLIE", "VENDDATA", "VENDNCNT", "VENDPLAC", "VENDNCLI", "VENDNCCF") // Totalizar para poder classificar por maior valor FOR nRc := 1 TO LEN(aListFor) nTtl := 0 FOR nMe := 1 TO LEN(aMeses) nTtl += aListFor[nRc,nMe] NEXT aListFor[nRc,LEN(aMeses)+1] := nTtl NEXT // Classificar conforme critérios IF lOrdNo ; ASORT(aListFor,,,{|A,B| A[LEN(aMeses)+3] < B[LEN(aMeses)+3]}); ENDIF IF lOrdVr ; ASORT(aListFor,,,{|A,B| A[LEN(aMeses)+1] > B[LEN(aMeses)+1]}); ENDIF IF lOrdId ; ASORT(aListFor,,,{|A,B| A[LEN(aMeses)+2] < B[LEN(aMeses)+2]}); ENDIF // Exportar para excel nFile := 1 WHILE .T. cXlsFile := CurDrive()+":\"+CurDir()+"\tempdir\positv_cli_"+STRZERO(nFile,4)+".xls" if ! file(cXlsFile) EXIT endif nFile ++ END nFtit := XLSFont("Verdana", 11, .t., .f., .f.) nFinf := XLSFont("Arial Narrow", 12, .f., .f., .f.) nFtot := XLSFont("Verdana", 11, .t., .t., .f.) nForm := XLSFormat( '0.00' ) XLS oXLS FILE &(cXlsFile) AUTOEXEC OF oMainWnd nLin := 1 XLS COL 1 WIDTH 12 OF oXLS // Código do fornecedor XLS COL 2 WIDTH 35 OF oXLS // Nome do fornecedor FOR nFo := 1 TO LEN(aMeses) XLS COL nFo+2 WIDTH 12 OF oXLS // Meses NEXT XLS COL LEN(aMeses)+3 WIDTH 15 OF oXLS // Total @nLin, 1 XLS SAY "CÓDIGO" OF oXLS ALIGNAMENT ALING_LEFT BORDER FONT nFtit @nLin, 2 XLS SAY "CLIENTE" OF oXLS ALIGNAMENT ALING_LEFT BORDER FONT nFtit FOR nFo := 1 TO LEN(aMeses) @nLin, nFo+2 XLS SAY aMeses[nFo] OF oXLS ALIGNAMENT ALING_RIGHT BORDER FONT nFtit NEXT @nLin, LEN(aMeses)+3 XLS SAY "TOTAL" OF oXLS ALIGNAMENT ALING_RIGHT BORDER FONT nFtit FOR nRc := 1 TO LEN(aListFor) nTtl := aListFor[nRc,LEN(aMeses)+1] IF ! lTodos // Não incluir todos IF nTtl > 0 .AND. lSoPos == .F.; LOOP ; ENDIF // Só atendidos IF nTtl = 0 .AND. lSoNeg == .F.; LOOP ; ENDIF // Só não atendidos ENDIF nLin ++ @nLin, 1 XLS SAY STRZERO(aListFor[nRc,LEN(aMeses)+2],8) OF oXLS ALIGNAMENT ALING_LEFT BORDER FONT nFinf @nLin, 2 XLS SAY ALLTRIM(aListFor[nRc,LEN(aMeses)+3]) OF oXLS ALIGNAMENT ALING_LEFT BORDER FONT nFinf FOR nMe := 1 TO LEN(aMeses) @nLin, nMe+2 XLS SAY aListFor[nRc,nMe] OF oXLS ALIGNAMENT ALING_RIGHT FORMAT nForm BORDER FONT nFinf aTotls[1,nMe] += aListFor[nRc,nMe] aTotls[2,nMe] += IIF(aListFor[nRc,nMe] > 0, 1, 0) aTotls[3,nMe] += IIF(aListFor[nRc,nMe] = 0, 1, 0) NEXT @nLin, LEN(aMeses)+3 XLS SAY nTtl OF oXLS ALIGNAMENT ALING_RIGHT FORMAT nForm BORDER FONT nFinf NEXT nLin ++ @nLin, 1 XLS SAY " " OF oXLS ALIGNAMENT ALING_LEFT SHADED BORDER FONT nFtot @nLin, 2 XLS SAY "TOTAIS" OF oXLS ALIGNAMENT ALING_LEFT SHADED BORDER FONT nFtot FOR nMe := 1 TO LEN(aMeses) @nLin, nMe+2 XLS SAY "= = = = =" OF oXLS ALIGNAMENT ALING_RIGHT SHADED BORDER FONT nFtot NEXT @nLin, LEN(aMeses)+3 XLS SAY "= = = = =" OF oXLS ALIGNAMENT ALING_RIGHT SHADED BORDER FONT nFtot nTtl := 0 nLin ++ @nLin, 1 XLS SAY " " OF oXLS ALIGNAMENT ALING_LEFT BORDER FONT nFtot @nLin, 2 XLS SAY "EM VALOR:" OF oXLS ALIGNAMENT ALING_RIGHT BORDER FONT nFtot FOR nMe := 1 TO LEN(aTotls[1]) @nLin, nMe+2 XLS SAY aTotls[1][nMe] OF oXLS ALIGNAMENT ALING_RIGHT FORMAT nForm BORDER FONT nFtot nTtl += aTotls[1][nMe] NEXT @nLin, LEN(aTotls[1])+3 XLS SAY nTtl OF oXLS ALIGNAMENT ALING_RIGHT FORMAT nForm BORDER FONT nFtot nTtl := 0 nLin ++ @nLin, 1 XLS SAY " " OF oXLS ALIGNAMENT ALING_LEFT BORDER FONT nFtot @nLin, 2 XLS SAY "ATENDIDOS:" OF oXLS ALIGNAMENT ALING_RIGHT BORDER FONT nFtot FOR nMe := 1 TO LEN(aTotls[2]) @nLin, nMe+2 XLS SAY aTotls[2][nMe] OF oXLS ALIGNAMENT ALING_RIGHT FORMAT nForm BORDER FONT nFtot nTtl += aTotls[2][nMe] NEXT @nLin, LEN(aTotls[2])+3 XLS SAY "Méd: "+ALLTRIM(STR(nTtl/len(aMeses))) OF oXLS ALIGNAMENT ALING_RIGHT FORMAT nForm BORDER FONT nFtot nTtl := 0 nLin ++ @nLin, 1 XLS SAY " " OF oXLS ALIGNAMENT ALING_LEFT BORDER FONT nFtot @nLin, 2 XLS SAY "NÃO ATENDIDOS:" OF oXLS ALIGNAMENT ALING_RIGHT BORDER FONT nFtot FOR nMe := 1 TO LEN(aTotls[3]) @nLin, nMe+2 XLS SAY aTotls[3][nMe] OF oXLS ALIGNAMENT ALING_RIGHT FORMAT nForm BORDER FONT nFtot nTtl += aTotls[3][nMe] NEXT @nLin, LEN(aTotls[3])+3 XLS SAY "Méd: "+ALLTRIM(STR(nTtl/len(aMeses))) OF oXLS ALIGNAMENT ALING_RIGHT FORMAT nForm BORDER FONT nFtot ENDXLS oXLS RELEASE nClie, cClie, nVddr, cVddr, cCliTp SELECT 0 RETURN NIL Quote Link to comment Share on other sites More sharing options...
ElliottKl Posted January 1, 2015 Report Share Posted January 1, 2015 Hey lá, você tem feito um excelente trabalho . Eu definitivamente digg it e , pessoalmente, recomendo aos meus amigos. Tenho certeza de que ? Vai ser beneficiados a partir deste site. Quote Link to comment Share on other sites More sharing options...
rubensma Posted January 1, 2015 Author Report Share Posted January 1, 2015 Valew Ariston, vou fazer dessa forma que vc postou, obrigado e feliz ano novo. Abraços. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.