Re: [delphi-br] Importar do excel com abas
Dê uma estudada nesta rotina e adapte as suas necessidades.
procedure TMenuPlanilha.PlanDetalhe(arq : string);
var
i : byte;
lin : byte;
fim_plan : boolean;
texto : string;
aba : byte;
begin
excel_le.Workbooks.Open(arq);
for aba := 1 to excel_le.Workbooks[1].sheets.Count do
if excel_le.WorkBooks[1].Sheets[aba].Name =
cdsGrid.fieldbyname('DESCRCARTAO').asstring then
break;
if aba excel_le.Workbooks[1].sheets.Count then
begin
excel_le.quit;
exit;
end;
if UpperCase(trim(excel_le.Workbooks[1].WorkSheets[aba].cells[8,1]))
'COLABORADOR' then
begin
Screen.Cursor := crDefault;
showmessage('Planilha fora do padrão' + #13
+ arq);
excel_le.quit;
exit;
end;
if lin_grava = 1 then
begin
fim_plan := false;
col_lim := 2;
while not fim_plan do
begin
inc(col_lim);
excel_grava.Workbooks[1].WorkSheets[1].columns[col_lim].rowheight := 10;
if trim(excel_le.Workbooks[1].WorkSheets[aba].cells[8,col_lim + 1]) =
'' then
fim_plan := true;
end;
Espero ter ajudado.
--- Em sáb, 12/12/09, Rafael Jorge rafael.jo...@gmail.com escreveu:
De: Rafael Jorge rafael.jo...@gmail.com
Assunto: [delphi-br] Importar do excel com abas
Para: delphi-br delphi-br@yahoogrupos.com.br
Data: Sábado, 12 de Dezembro de 2009, 8:46
tenho uma rotina que importa do excel para meu banco de dados. ate ai tudo
bem, so que alguns clientes usam varias abas do excel (planilhas) dentro do
mesmo excel.
e eu gostaria de saber como sair de planilha em planilha pegando os dados.
tentei usando a rotina abaixo mais nao ta funcionando da erro no active
quando eu tento pegar a ultima celula ativa.
- - -- codigo fonte
- -
procedure TFormImpotarDoExcel .BitBtn5Click( Sender: TObject);
const
xlCellTypeLastCell = $000B;
var
XLApp : OLEVariant;
Sheet : OLEVariant;
RangeMatrix : Variant;
linha : Integer;
coluna : Integer;
I : Integer;
ii : integer;
iii : integer;
x : integer;
y : integer;
arquivo : string;
sql : string;
sair : boolean;
function valida(texto: string): string;
begin
result := stringreplace( texto,#39, '',[rfReplaceAll ]);
end;
begin
// Create Excel-OLE Object
XLApp := CreateOleObject( 'Excel.Applicati on');
//pegando todos os arquivos do diretorio.
g1.MinValue := 0;
g1.MaxValue := self.FileListBox1. Items.Count;
g1.Progress := 0;
for I := 0 to self.FileListBox1. Items.Count - 1 do
begin
g1.Progress := i;
application. ProcessMessages;
try
// Hide Excel
XLApp.Visible := False;
// Open the Workbook
arquivo :=
self.DirectoryListB ox1.Directory+ '\'+self. FileListBox1. Items.Strings[ i];
lbarquivo.caption := arquivo;
application. ProcessMessages;
XLApp.Workbooks. Open(arquivo) ;
// Sheet := XLApp.Workbooks[ 1].WorkSheets[ 1];
Sheet := XLApp.Workbooks[ ExtractFileName( arquivo)] .WorkSheets[ 0];
// In order to know the dimension of the WorkSheet, i.e the number of
rows
// and the number of columns, we activate the last non-empty cell of it
Sheet.Cells. SpecialCells( xlCellTypeLastCe ll, EmptyParam). Activate; ---
dá erro aqui!
// Get the value of the last row
x := XLApp.ActiveCell. Row;
// Get the value of the last column
y := XLApp.ActiveCell. Column;
// Assign the Variant associated with the WorkSheet to the Delphi
Variant
RangeMatrix := XLApp.Range[ 'A1', XLApp.Cells. Item[X, Y]].Value;
// Define the loop for filling in the TStringGrid
linha := strtoint(linhainici al.Text);
sair := false;
repeat
//criando o sql com base na tabela
sql := 'insert into '+self.combtabela. Text+' (';
if CheckBox1.Checked then sql := sql +'codigo,';
for Ii := 1 to self.grid.RowCount - 1 DO
BEGIN
if ii = 1
then sql := sql + self.grid.Cells[ 0,ii]
else sql := sql + ', '+self.grid. Cells[0,ii] ;
end;
//vendo os campos manuais
for iii := 1 to self.gridmanual. RowCount - 1
do sql := sql + ', '+self.gridmanual. Cells[0,iii] ;
//fachando os campos
sql := sql + ') values (';
if CheckBox1.Checked
then sql := sql +
autoincremento( nil,self. combtabela. Text,'codigo' ,fsqlconnection,
self.Socketconne ction)+',
';
//inserindo os dados
g2.MinValue := 0;
g2.MaxValue := self.grid.RowCount + self.gridmanual. RowCount;
g2.Progress := 0;
for ii := 1 to self.grid.RowCount - 1 do
begin
g2.Progress := ii;
application. ProcessMessages;
if (RangeMatrix[ linha, strtoint(self. grid.Cells[ 1,ii])] = '') and
(ii = 1) then
begin
sair := true;
Break;
end;
if self.grid.Cells[ 2,ii] = '0' then
begin
if ii = 1
then sql := sql + #39+ valida(RangeMatrix[ linha,
strtoint(self. grid.Cells[ 1,ii])])+ #39
else sql := sql + ', '+ #39+ valida(RangeMatrix[ linha,
strtoint(self. grid.Cells[ 1,ii])])+ #39;
end else begin
if ii = 1
then sql := sql + #39+ valida(copy( RangeMatrix[ linha,
strtoint(self. grid.Cells[ 1,ii])],1, strtoint( self.grid. Cells[2,ii] )))+#39
else sql := sql + ', '+ #39+ valida(copy( RangeMatrix[ linha,
strtoint(self. grid.Cells[ 1,ii])],1, strtoint
[delphi-br] Importar do excel com abas
tenho uma rotina que importa do excel para meu banco de dados. ate ai tudo
bem, so que alguns clientes usam varias abas do excel (planilhas) dentro do
mesmo excel.
e eu gostaria de saber como sair de planilha em planilha pegando os dados.
tentei usando a rotina abaixo mais nao ta funcionando da erro no active
quando eu tento pegar a ultima celula ativa.
codigo fonte
--
procedure TFormImpotarDoExcel.BitBtn5Click(Sender: TObject);
const
xlCellTypeLastCell = $000B;
var
XLApp : OLEVariant;
Sheet : OLEVariant;
RangeMatrix : Variant;
linha : Integer;
coluna : Integer;
I : Integer;
ii : integer;
iii : integer;
x : integer;
y : integer;
arquivo : string;
sql : string;
sair: boolean;
function valida(texto: string): string;
begin
result := stringreplace(texto,#39,'',[rfReplaceAll]);
end;
begin
// Create Excel-OLE Object
XLApp := CreateOleObject('Excel.Application');
//pegando todos os arquivos do diretorio.
g1.MinValue := 0;
g1.MaxValue := self.FileListBox1.Items.Count;
g1.Progress := 0;
for I := 0 to self.FileListBox1.Items.Count - 1 do
begin
g1.Progress := i;
application.ProcessMessages;
try
// Hide Excel
XLApp.Visible := False;
// Open the Workbook
arquivo :=
self.DirectoryListBox1.Directory+'\'+self.FileListBox1.Items.Strings[i];
lbarquivo.caption := arquivo;
application.ProcessMessages;
XLApp.Workbooks.Open(arquivo);
// Sheet := XLApp.Workbooks[1].WorkSheets[1];
Sheet := XLApp.Workbooks[ExtractFileName(arquivo)].WorkSheets[0];
// In order to know the dimension of the WorkSheet, i.e the number of
rows
// and the number of columns, we activate the last non-empty cell of it
Sheet.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Activate; ---
dá erro aqui!
// Get the value of the last row
x := XLApp.ActiveCell.Row;
// Get the value of the last column
y := XLApp.ActiveCell.Column;
// Assign the Variant associated with the WorkSheet to the Delphi
Variant
RangeMatrix := XLApp.Range['A1', XLApp.Cells.Item[X, Y]].Value;
// Define the loop for filling in the TStringGrid
linha := strtoint(linhainicial.Text);
sair := false;
repeat
//criando o sql com base na tabela
sql := 'insert into '+self.combtabela.Text+' (';
if CheckBox1.Checked then sql := sql +'codigo,';
for Ii := 1 to self.grid.RowCount - 1 DO
BEGIN
if ii = 1
then sql := sql + self.grid.Cells[0,ii]
else sql := sql + ', '+self.grid.Cells[0,ii];
end;
//vendo os campos manuais
for iii := 1 to self.gridmanual.RowCount - 1
do sql := sql + ', '+self.gridmanual.Cells[0,iii];
//fachando os campos
sql := sql + ') values (';
if CheckBox1.Checked
then sql := sql +
autoincremento(nil,self.combtabela.Text,'codigo',fsqlconnection,self.Socketconnection)+',
';
//inserindo os dados
g2.MinValue := 0;
g2.MaxValue := self.grid.RowCount + self.gridmanual.RowCount;
g2.Progress := 0;
for ii := 1 to self.grid.RowCount - 1 do
begin
g2.Progress := ii;
application.ProcessMessages;
if (RangeMatrix[linha, strtoint(self.grid.Cells[1,ii])] = '') and
(ii = 1) then
begin
sair := true;
Break;
end;
if self.grid.Cells[2,ii] = '0' then
begin
if ii = 1
then sql := sql + #39+ valida(RangeMatrix[linha,
strtoint(self.grid.Cells[1,ii])])+#39
else sql := sql + ', '+ #39+ valida(RangeMatrix[linha,
strtoint(self.grid.Cells[1,ii])])+#39;
end else begin
if ii = 1
then sql := sql + #39+ valida(copy(RangeMatrix[linha,
strtoint(self.grid.Cells[1,ii])],1,strtoint(self.grid.Cells[2,ii])))+#39
else sql := sql + ', '+ #39+ valida(copy(RangeMatrix[linha,
strtoint(self.grid.Cells[1,ii])],1,strtoint(self.grid.Cells[2,ii])))+#39;
end;
end;
for ii := 1 to self.gridmanual.RowCount - 1 do
begin
g2.Progress := g2.Progress + 1;
application.ProcessMessages;
if self.gridmanual.Cells[2,ii] = '0'
then sql := sql + ', '+ #39+
valida(self.gridmanual.Cells[1,ii])+#39
else sql := sql + ', '+ #39+
valida(copy(self.gridmanual.Cells[1,ii],1,strtoint(self.gridmanual.Cells[2,ii])))+#39;
end;
sql := sql + ')';
try
if not sair then
begin
rodasp(sql,
self.fsqlconnection,
self.Socketconnection,
self.ultimo_erro);
sair := false;
end else begin
sair := false;
break;
end;
finally
Inc(linha, 1);
end;
until linha strtoint(linhafinal.text);
// Unassign the Delphi Variant Matrix
RangeMatrix :=
