Exportar consulta SQL para Excel
Olá, gente.O problema é o seguinte: tenho o código abaixo para, de acordo com uma consulta SQL ser gerado uma planilha do Excel. Só que, na hora de salvar aparece uma mensagem: O método SaveAs da classe Workbook falhou.Alguém pode me dar uma ajuda, por favor?
procedure TfmConsultaRPS.ExportarXLS;var vExcel : Variant; iLinha : Integer; sData, sNomeDoArquivo : string;begin sData := DateToStr(Date); sNomeDoArquivo := Copy(sData,1,2) + '-' + Copy(sData,4,2) + '-' + Copy(sData,7,4) + '_' + RetiraChar(TimeToStr(Now),':') + '.xls'; vExcel := CreateOleObject('Excel.Application'); vExcel.Visible := False; vExcel.Workbooks.Add; //Criando a primeira linha (Cabeçalho) vExcel.WorkBooks[1].Sheets[1].Cells[1, 1] := 'CPF'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 2] := 'N.RPS'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 3] := 'Referência'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 4] := 'Dt. Emissão'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 5] := 'Dt. Vencimento'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 6] := 'Nome do Aluno'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 7] := 'Nome do Responsável'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 8] := 'CPF'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 9] := 'CEP'; vExcel.WorkBooks[1].Sheets[1].Cells[1,10] := 'Logradouro'; vExcel.WorkBooks[1].Sheets[1].Cells[1,11] := 'Nº'; vExcel.WorkBooks[1].Sheets[1].Cells[1,12] := 'Complemento'; vExcel.WorkBooks[1].Sheets[1].Cells[1,13] := 'Bairro'; vExcel.WorkBooks[1].Sheets[1].Cells[1,14] := 'Cidade'; vExcel.WorkBooks[1].Sheets[1].Cells[1,15] := 'UF'; vExcel.WorkBooks[1].Sheets[1].Cells[1,16] := 'Valor a Pagar'; vExcel.WorkBooks[1].Sheets[1].Cells[1,17] := 'Valor Após Vencimento'; vExcel.WorkBooks[1].Sheets[1].Cells[1,18] := 'Desconto'; vExcel.WorkBooks[1].Sheets[1].Cells[1,19] := 'Valor IRRF'; vExcel.WorkBooks[1].Sheets[1].Cells[1,20] := 'Base Cálc. ISS'; vExcel.WorkBooks[1].Sheets[1].Cells[1,21] := 'Alíquota ISS'; vExcel.WorkBooks[1].Sheets[1].Cells[1,22] := 'Valor ISS'; vExcel.WorkBooks[1].Sheets[1].Cells[1,23] := 'E-Mail'; vExcel.WorkBooks[1].Sheets[1].Cells[1,24] := 'Cód. Serviço'; //vExcel.WorkBooks[1].Sheets[1].Cells[1,2] := 'N.RPS';
iLinha := 2; //Passando para a segunda linha with dmEmissor_NFs, qryConsulta do begin while not Eof do begin vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 1] := FieldByName('NFPRESTADOR').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 2] := FieldByName('NUMNF').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 3] := FieldByName('NFREFERENCIA').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 4] := FieldByName('NFDTEMISSAO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 5] := FieldByName('NFVENCIMENTO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 6] := FieldByName('NFALUNO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 7] := FieldByName('NFNOME').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 8] := FieldByName('NFCPF').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 9] := FieldByName('NFCEP').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,10] := FieldByName('NFENDERECO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,11] := FieldByName('NFNUMERO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,12] := FieldByName('NFCOMPLEMENTO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,13] := FieldByName('NFBAIRRO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,14] := FieldByName('NFCIDADE').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,15] := FieldByName('NFUF').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,16] := FieldByName('NFVALOR').AsString; //vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,17] := FieldByName('NFREFERENCIA').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,18] := FieldByName('NFDESCONTO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,19] := FieldByName('NFVALORIRRF').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,20] := FieldByName('NFBCISS').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,21] := FieldByName('NFALIQISS').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,22] := FieldByName('NFVALORISS').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,23] := FieldByName('NFEMAIL').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,24] := FieldByName('NFCODSERVICO').AsString; iLinha := iLinha + 1; Next; end; end; vExcel.WorkBooks[1].SaveAs(dredtPasta.Text + '\' + sNomeDoArquivo); vExcel.WorkBooks[1].Close; MsgInfo('O arquivo será salvo na pasta:' + #13 + QuotedStr(dredtPasta.Text) + #13 + 'com o nome: ' + #13 + QuotedStr(sNomeDoArquivo));
end;
Valeu.
procedure TfmConsultaRPS.ExportarXLS;var vExcel : Variant; iLinha : Integer; sData, sNomeDoArquivo : string;begin sData := DateToStr(Date); sNomeDoArquivo := Copy(sData,1,2) + '-' + Copy(sData,4,2) + '-' + Copy(sData,7,4) + '_' + RetiraChar(TimeToStr(Now),':') + '.xls'; vExcel := CreateOleObject('Excel.Application'); vExcel.Visible := False; vExcel.Workbooks.Add; //Criando a primeira linha (Cabeçalho) vExcel.WorkBooks[1].Sheets[1].Cells[1, 1] := 'CPF'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 2] := 'N.RPS'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 3] := 'Referência'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 4] := 'Dt. Emissão'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 5] := 'Dt. Vencimento'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 6] := 'Nome do Aluno'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 7] := 'Nome do Responsável'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 8] := 'CPF'; vExcel.WorkBooks[1].Sheets[1].Cells[1, 9] := 'CEP'; vExcel.WorkBooks[1].Sheets[1].Cells[1,10] := 'Logradouro'; vExcel.WorkBooks[1].Sheets[1].Cells[1,11] := 'Nº'; vExcel.WorkBooks[1].Sheets[1].Cells[1,12] := 'Complemento'; vExcel.WorkBooks[1].Sheets[1].Cells[1,13] := 'Bairro'; vExcel.WorkBooks[1].Sheets[1].Cells[1,14] := 'Cidade'; vExcel.WorkBooks[1].Sheets[1].Cells[1,15] := 'UF'; vExcel.WorkBooks[1].Sheets[1].Cells[1,16] := 'Valor a Pagar'; vExcel.WorkBooks[1].Sheets[1].Cells[1,17] := 'Valor Após Vencimento'; vExcel.WorkBooks[1].Sheets[1].Cells[1,18] := 'Desconto'; vExcel.WorkBooks[1].Sheets[1].Cells[1,19] := 'Valor IRRF'; vExcel.WorkBooks[1].Sheets[1].Cells[1,20] := 'Base Cálc. ISS'; vExcel.WorkBooks[1].Sheets[1].Cells[1,21] := 'Alíquota ISS'; vExcel.WorkBooks[1].Sheets[1].Cells[1,22] := 'Valor ISS'; vExcel.WorkBooks[1].Sheets[1].Cells[1,23] := 'E-Mail'; vExcel.WorkBooks[1].Sheets[1].Cells[1,24] := 'Cód. Serviço'; //vExcel.WorkBooks[1].Sheets[1].Cells[1,2] := 'N.RPS';
iLinha := 2; //Passando para a segunda linha with dmEmissor_NFs, qryConsulta do begin while not Eof do begin vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 1] := FieldByName('NFPRESTADOR').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 2] := FieldByName('NUMNF').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 3] := FieldByName('NFREFERENCIA').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 4] := FieldByName('NFDTEMISSAO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 5] := FieldByName('NFVENCIMENTO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 6] := FieldByName('NFALUNO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 7] := FieldByName('NFNOME').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 8] := FieldByName('NFCPF').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha, 9] := FieldByName('NFCEP').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,10] := FieldByName('NFENDERECO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,11] := FieldByName('NFNUMERO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,12] := FieldByName('NFCOMPLEMENTO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,13] := FieldByName('NFBAIRRO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,14] := FieldByName('NFCIDADE').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,15] := FieldByName('NFUF').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,16] := FieldByName('NFVALOR').AsString; //vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,17] := FieldByName('NFREFERENCIA').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,18] := FieldByName('NFDESCONTO').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,19] := FieldByName('NFVALORIRRF').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,20] := FieldByName('NFBCISS').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,21] := FieldByName('NFALIQISS').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,22] := FieldByName('NFVALORISS').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,23] := FieldByName('NFEMAIL').AsString; vExcel.WorkBooks[1].Sheets[1].Cells[iLinha,24] := FieldByName('NFCODSERVICO').AsString; iLinha := iLinha + 1; Next; end; end; vExcel.WorkBooks[1].SaveAs(dredtPasta.Text + '\' + sNomeDoArquivo); vExcel.WorkBooks[1].Close; MsgInfo('O arquivo será salvo na pasta:' + #13 + QuotedStr(dredtPasta.Text) + #13 + 'com o nome: ' + #13 + QuotedStr(sNomeDoArquivo));
end;
Valeu.
Walder
Curtidas 0
Respostas
Walder
17/02/2011
Só mais algumas observações:OS: Windows 7 StarterDelphi 7Microsoft Office 2007 (pode ser que o problema esteja aqui??)
GOSTEI 0
José
17/02/2011
Este tópico esta sendo fechado por inatividade. Se necessário, sinalizar para que seja reaberto ou abrir um novo.
GOSTEI 0