EXPORTACAO EXCEL

HELDERMOTA 08/04/2010 15:17:33
#338886
Estou exportando dados do relatório para Excel, segue abaixo o código, porém os campos com formato de valores, aparece no excel sem as decimais quando o valor é por exemplo [Ô]344,00[Ô] no excel fica [Ô]344[Ô], como faço para formatar de forma que sempre apareça as casas decimais.

strFont = [Ô]<font face=[Ô] & Chr(34) & [Ô]arial[Ô] & Chr(34) & [Ô] size=[Ô] & Chr(34) & [Ô]2[Ô] & Chr(34) & [Ô]>[Ô]
intFreeFile = FreeFile
Open strCaminho For Output As #intFreeFile
[ô]*** INICIA DOCUMENTO ******************************************************************************************
Print #intFreeFile, [Ô]<html>[Ô]
Print #intFreeFile, [Ô]<body>[Ô]
Print #intFreeFile, [Ô]<table border=2>[Ô]
[ô]*** CABEÇALHO *************************************************************************************************
Print #intFreeFile, [Ô]<tr>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]<b>[Ô] & [Ô]PLACA[Ô] & [Ô]</b></td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]<b>[Ô] & [Ô]N.F.[Ô] & [Ô]</b></td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]<b>[Ô] & [Ô]CTRC[Ô] & [Ô]</b></td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]<b>[Ô] & [Ô]DATA[Ô] & [Ô]</b></td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]<b>[Ô] & [Ô]MOTORISTA[Ô] & [Ô]</b></td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]<b>[Ô] & [Ô]CLIENTE[Ô] & [Ô]</b></td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]<b>[Ô] & [Ô]CIDADE[Ô] & [Ô]</b></td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]<b>[Ô] & [Ô]UF[Ô] & [Ô]</b></td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]<b>[Ô] & [Ô]QUANTIDADE[Ô] & [Ô]</b></td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]<b>[Ô] & [Ô]TOTAL DO FRETE[Ô] & [Ô]</b></td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]<b>[Ô] & [Ô]COMISSÃO[Ô] & [Ô]</b></td>[Ô]
Print #intFreeFile, [Ô]</tr>[Ô]
[ô]*** DADOS *****************************************************************************************************
Do Until .EOF
If .Fields!SNF_SITUACAO = [Ô]C[Ô] Then GoTo ProximaNotaLpt
If .Fields!SNF_OPERACAO <> [Ô]V[Ô] And .Fields!SNF_OPERACAO <> [Ô]T[Ô] Then GoTo ProximaNotaLpt
If .Fields!SNF_TRANSP <> TranspPadrao Then GoTo ProximaNotaLpt
[ô]Calculo do Frete da Nota Fiscal
BuscaCliente .Fields!CLI_CODIGO
If TemFrete = False Then
Printer.KillDoc
DoEvents
cmdCancelar_Click
Exit Sub
End If
If .Fields!SNF_CTRC <= 0 Then
FreteNf = Format(.Fields!ISNF_QTDE, [Ô]##,##[Ô]) * Format(FreteNf, [Ô]##,####0.0000[Ô])
Else
FreteNf = .Fields!SNF_VR_CTRC
End If
[ô]Calculo da Margem de Lucro Líquida da Nota Fiscal
Comissaonf = (FreteNf * ComissaoMot) / 100
Print #intFreeFile, [Ô]<tr>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & .Fields!SNF_PLACA & [Ô]</td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & .Fields!SNF_NOTA & [Ô]</td>[Ô]
If .Fields!SNF_CTRC > 0 Then
Print #intFreeFile, [Ô]<td>[Ô] & strFont & .Fields!SNF_CTRC & [Ô]</td>[Ô]
Else
Print #intFreeFile, [Ô]<td>[Ô] & strFont & [Ô]--[Ô] & [Ô]</td>[Ô]
End If
Print #intFreeFile, [Ô]<td>[Ô] & strFont & Format(.Fields!ISNF_DT_EMISSAO, [Ô]DD/MM/YYYY[Ô]) & [Ô]</td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & .Fields!MOT_NOME & [Ô]</td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & .Fields!CLI_NOME & [Ô]</td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & NomeCidade & [Ô]</td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & UfCliente & [Ô]</td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & Format(.Fields!ISNF_QTDE, [Ô]##,##0[Ô]) & [Ô]</td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & Format(FreteNf, [Ô]##,##0.00[Ô]) & [Ô]</td>[Ô]
Print #intFreeFile, [Ô]<td>[Ô] & strFont & Format(Comissaonf, [Ô]##,##0.00[Ô]) & [Ô]</td>[Ô]
Print #intFreeFile, [Ô]</tr>[Ô]

Valeu
JEAN.JEDSON 08/04/2010 17:20:26
#338893
particularmente eu usaria um objeto excel para fazer esta exportação... da forma que vc está fazendo, é um html aberto pelo excel...
ou ainda vc poderia abrir o excel por ADO e gravar como se fosse um recordset :)

outra coisa... excel as vezes é chatinho de acordo com as configurações regionais de cada computador... não esqueça de verificar se o separador de decimal é ponto ou vírgula... o que funciona em um computador pode não funcionar em outro
MARCELOSN 08/04/2010 21:16:07
#338900
caso queira um exemplo para usar com excel,





Option Explicit

Function excelgrafico()
Dim oXL As Object [ô] Aplicação
Dim oBook As Object [ô] workbook
Dim oSheet As Object [ô] Worksheet
Dim oChart As Object [ô] grafico Excel

Dim iRow As Integer [ô] variavel para a linha atual
Dim iCol As Integer [ô] variavel para coluna atual

Const cNumCols = 6 [ô] numero de pontos em cada serie
Const cNumRows = 2 [ô] Numero de series


ReDim aTemp(1 To cNumRows, 1 To cNumCols)

[ô]inicia o Excel e cria um novo workbook
Set oXL = CreateObject([Ô]Excel.application[Ô])
Set oBook = oXL.Workbooks.Add
Set oSheet = oBook.Worksheets.Item(1)

[ô] Inclua alguns dados nas células para as duas series
Randomize Now()
For iRow = 1 To cNumRows
For iCol = 1 To cNumCols
aTemp(iRow, iCol) = Int(Rnd * 50) + 1
Next iCol
Next iRow
oSheet.Range([Ô]A1[Ô]).Resize(cNumRows, cNumCols).Value = aTemp

[ô]Inclui um objeto chart para o primeiro worksheet
Set oChart = oSheet.ChartObjects.Add(50, 40, 300, 200).Chart
oChart.SetSourceData Source:=oSheet.Range([Ô]A1[Ô]).Resize(cNumRows, cNumCols)

[ô] torna o Excel Visivel
oXL.Visible = True

oXL.UserControl = True
End Function
JEAN.JEDSON 08/04/2010 22:10:55
#338902
sim, não cria o vinculo, mas tbm tem o problema de estar formatando errado...
tenta substituir o format por formatnumber(campo, 2)
HELDERMOTA 09/04/2010 10:01:31
#338922
Jean coloquei o formatnumer mas continua o problema...
HELDERMOTA 09/04/2010 10:10:09
#338924
Segue abaixo um exemplo do html.

<html>
<body>
<table border=2>
<tr>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]><b>PLACA</b></td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]><b>N.F.</b></td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]><b>CTRC</b></td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]><b>DATA</b></td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]><b>MOTORISTA</b></td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]><b>CLIENTE</b></td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]><b>CIDADE</b></td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]><b>UF</b></td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]><b>QUANTIDADE</b></td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]><b>TOTAL DO FRETE</b></td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]><b>COMISSÃO</b></td>
</tr>
<tr>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>DAJ0163</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>13546</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>--</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>01/03/2010</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MARCIO APARECIDO DA SILVA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>TRANSPORTES E CONSTRUCOES LTDA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>ARAXA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MG</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>12.000</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>312,00</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>9,36</td>
</tr>
<tr>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>DAJ0163</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>13547</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>11437</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>01/03/2010</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MARCIO APARECIDO DA SILVA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>ACOL ABDO COMBUSTIVEIS E LUBRIFICANTES LTDA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>ARAXA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MG</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>3.000</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>108,00</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>3,24</td>
</tr>
<tr>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>DAJ0163</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>13567</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>--</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>01/03/2010</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MARCIO APARECIDO DA SILVA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>TRANSPORTES E CONSTRUCOES LTDA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>ARAXA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MG</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>5.000</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>130,00</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>3,90</td>
</tr>
<tr>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>DAJ0163</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>13568</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>11447</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>01/03/2010</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MARCIO APARECIDO DA SILVA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>POSTO RZG DOMINGOS ZEMA LTDA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>ARAXA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MG</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>2.000</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>72,00</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>2,16</td>
</tr>
<tr>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>DAJ0163</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>13569</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>11448</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>01/03/2010</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MARCIO APARECIDO DA SILVA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>PETRUS WILHELMUS JOZEF SCHOENMAKER E OUTROS</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>TAPIRA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MG</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>8.000</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>384,00</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>11,52</td>
</tr>
<tr>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>DAJ0163</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>13570</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>11449</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>01/03/2010</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MARCOS ANTONIO MARTINS DE QUEIROZ</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>POSTO RETÃO LTDA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>SANTA JULIANA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MG</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>5.000</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>144,00</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>4,32</td>
</tr>
<tr>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>DAJ0163</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>13571</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>11450</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>01/03/2010</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MARCOS ANTONIO MARTINS DE QUEIROZ</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MILK MAX TRANSPORTES LTDA</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>FRUTAL</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>MG</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>10.000</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>648,00</td>
<td><font face=[Ô]arial[Ô] size=[Ô]2[Ô]>19,44</td>
</tr>
<tr>
</table>
</body>
</html>
JEAN.JEDSON 09/04/2010 11:40:59
#338934
este html abaixo, quando aberto no excel, me mostra os numeros formatados e com as respectivas casas decimais corretas...


<html>
<head>
<title>Exemplo HTML x Excel</title>
</head>
<body>
<table border=[Ô]0[Ô] width=[Ô]100%[Ô] cellpadding=[Ô]0[Ô] cellspacing=[Ô]0[Ô]>
<tr>
<td><b><i><font size=5>Ranking de Lojas</font></i></b><br><br></td>
<td align=[Ô]right[Ô]><b><i><font size=5>EMPRESA TESTE</font></i></b><br><br></td>
</tr>
</table>
<table border=[Ô]0[Ô] width=[Ô]100%[Ô] cellpadding=[Ô]0[Ô] cellspacing=[Ô]1[Ô]>
<tr align=[Ô]center[Ô] bgcolor=[Ô]black[Ô]>
<td align=[Ô]left[Ô]> <font size=1 color=[Ô]white[Ô]><b>Razão Social</b></font></td>
<td align=[Ô]right[Ô]> <font size=1 color=[Ô]white[Ô]><b>Vlr. Venda</b></font></td>
</tr>
<tr bgcolor=[Ô]#D3D3D3[Ô]>
<td align=[Ô]left[Ô]><font size=1> Loja 31 </font></td>
<td align=[Ô]right[Ô]><font size=1>35.697,80</font></td>
</tr>
<tr bgcolor=[Ô]#D3D3D3[Ô]>
<td align=[Ô]left[Ô]><font size=1> Loja 15 </font></td>
<td align=[Ô]right[Ô]><font size=1>17.938,00</font></td>
</tr>
<tr bgcolor=[Ô]#D3D3D3[Ô]>
<td align=[Ô]left[Ô]><font size=1> Loja 06 </font></td>
<td align=[Ô]right[Ô]><font size=1>25.463,45</font></td>
</tr>
<tr bgcolor=[Ô]#D3D3D3[Ô]>
<td align=[Ô]left[Ô]><font size=1> Loja 03 </font></td>
<td align=[Ô]right[Ô]><font size=1>31.406,78</font></td>
</tr>
</table>
</body>
</html>
Página 1 de 2 [11 registro(s)]
Tópico encerrado , respostas não são mais permitidas
Voltar