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 = $0000000B;
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 := Unassigned;
finally
// Quit Excel
if not VarIsEmpty(XLApp) then
begin
// XLApp.DisplayAlerts := False;
XLApp.Quit;
{XLAPP := Unassigned;
Sheet := Unassigned;}
end;
end;
end;
XLApp.free;
end;
------------------------------------------- fim
--------------------------------------------
--
_________________________
Rafael jorge alves
Desenvolvedor/analista
Ativa Soluções em TI.
Recife - PE
[As partes desta mensagem que não continham texto foram removidas]
