Jump to content

Import form Excel, filtr, export to excel

- - - - -

This topic has been archived. This means that you cannot reply to this topic.
1 reply to this topic

#1
seatcordobawrc

seatcordobawrc

    Newbie

  • Members
  • Pip
  • 1 posts
Hi ! I have big problem. My program work well in for example 300 rows in excel. But on file, where is 40000 rows is mistake:

Could not convert variant of type (String) into type (Double)

var

  Form1: TForm1;

  sciezka,sciezka2,puste,gw:string;

  Excel,Excel2:variant;

  a:array[1..80000,1..10] of ansistring;

  i,j,z,ile:longint;


(...)

procedure TForm1.Button3Click(Sender: TObject);

var

Wybor:integer;

nadpisac:boolean;

begin

  sciezka:=edit1.Text;

  sciezka2:=edit2.Text;

  if (not xls(sciezka) or not xls(sciezka2)) or ((sciezka='') or (sciezka2='')) then Showmessage('Proszę podać pliki o rozszerzeniu .xls!')

  ELSE

  if (sciezka=sciezka2) then

  if Application.MessageBox('Podano takie same ścieżki. Nadpisać?','UWAGA!', MB_YESNO + MB_ICONWARNING) = Id_Yes then

BEGIN

  nadpisac:=true;

END

else

BEGIN

 Showmessage('Podaj inna ścieżkę               ');

 nadpisac:=false;

END;

  if (((sciezka=sciezka2) and (nadpisac=true)) or ((sciezka<>sciezka2))) and (xls(sciezka)) and (xls(sciezka2)) and (sciezka<>'') and (sciezka2<>'') then

begin





Excel := CreateOleObject('Excel.Application');

  Excel.Workbooks.open(sciezka);

//Początek liczenia wierszy

ile:=3;

  puste:='sfasfas';

  while puste<>'' do

begin

  puste:=excel.cells[ile,1].value;

  inc(ile);

end;

//Koniec liczenia wierszy

//Filtr wierszy

  j:=0;

 for i:=1 to ile do

 begin

  if (Excel.Cells[i,1].Interior.Color='12632256') then inc(z);

   end;

   while (z>j) do

   begin

  for i:=1 to ile do

begin

  if (Excel.Cells[i,1].Interior.Color='12632256') then

  begin

  Excel.Rows[i].Delete;

  j:=j+1;

  end;

  end;

  end;

//Koniec filtru wierszy


ile:=ile-z;



  for i:=1 to 80 do

  begin

        if excel.cells[2,i].value='kod_MAX' then

  begin

                        for j:=3 to ile do

                                        a[j-2,1]:=excel.cells[j-1,i].value ;

          end;

      end;



//================

  for i:=1 to 80 do

  begin

        if excel.cells[2,i].value='nazwa' then

  begin

                        for j:=3 to ile do

                                        a[j-2,2]:=excel.cells[j-1,i].value ;

          end;

      end;

//================

  for i:=1 to 80 do

  begin

        if excel.cells[2,i].value='konfig' then

  begin

                        for j:=3 to ile do

                                        a[j-2,3]:=excel.cells[j-1,i].value;

          end;

      end;

//================

  for i:=1 to 80 do

  begin

        if excel.cells[2,i].value='Gw.' then

  begin

                        for j:=3 to ile do

  begin

      if excel.cells[j,i].value='12' then gw:='Gwarancja na 12 miesięcy';

      if excel.cells[j,i].value='24' then gw:='Gwarancja na 24 miesiące';

      if excel.cells[j,i].value='36' then gw:='Gwarancja na 36 miesięcy';

      if excel.cells[j,i].value='60' then gw:='Gwarancja na 60 miesięcy';

      if excel.cells[j,i].value='GP12' then gw:='Gwarancja producenta na 12 miesięcy';

      if excel.cells[j,i].value='GP24' then gw:='Gwarancja producenta na 24 miesięcy';

      if excel.cells[j,i].value='GP36' then gw:='Gwarancja producenta na 36 miesięcy';

      if excel.cells[j,i].value='DTD24' then gw:='Gwarancja na 24 miesiące';

                                        a[j-2,3]:=a[j-2,3]+'  '+gw;

  end;

          end;

      end;

//================

  for i:=1 to 80 do

  begin

        if excel.cells[2,i].value='kod' then

  begin

                        for j:=3 to ile do

                                        a[j-2,4]:=excel.cells[j-1,i].value;

          end;

      end;

//================

  for i:=1 to 80 do

  begin

        if excel.cells[2,i].value='EAN' then

  begin

                        for j:=3 to ile do

                                        a[j-2,5]:=excel.cells[j-1,i].value;

          end;

      end;

//================

  for i:=20 to 80 do

  begin

        if excel.cells[2,i].value='cenaDB' then

  begin

                        for j:=3 to ile do

                                        a[j-2,6]:=excel.cells[j-1,i].value;

          end;

      end;

//================




 Excel.Workbooks.Add;

 Excel.ActiveSheet.Name := 'Cennik';

for j:=1 to 80 do excel.cells[1,j].font.bold:=true;

 for i:=1 to 3 do

  for j:=1 to ile-3 do

begin

  if i=3 then Excel.Cells[j,i+1].Value:=a[j,i]

  else Excel.Cells[j,i].Value:=a[j,i];

end;

for j:=1 to ile-3 do

begin

  Excel.Cells[j,5].Value:=a[j,4];

end;

for j:=1 to ile-3 do

begin

  Excel.Cells[j,7].Value:=a[j,5];

end;

for j:=1 to ile-3 do

begin

  Excel.Cells[j,8].Value:=a[j,6];

end;

  excel.cells[1,1].value:='Kod';

  excel.cells[1,2].value:='Nazwa';

  excel.cells[1,3].value:='Grupa';

  excel.cells[1,4].value:='Opis';

  excel.cells[1,5].value:='NumerKatalogowy';

  excel.cells[1,6].value:='JM';

  excel.cells[1,7].value:='EAN';

  excel.cells[1,8].value:='CenaS4';

for i:=2 to ile-3 do excel.cells[i,3].value:='KMPT';

for i:=2 to ile-3 do excel.cells[i,6].value:='szt.';

excel.cells[1,1].columnwidth:=21;

excel.cells[1,2].columnwidth:=36;

excel.cells[1,3].columnwidth:=6;

excel.cells[1,4].columnwidth:=106;

excel.cells[1,5].columnwidth:=36;

excel.cells[1,7].columnwidth:=14;


  Excel.ActiveWorkbook.SaveAs(sciezka2);

  Excel.Quit;

  showmessage('                 Gotowe                  ');

end;

  end;


#2
WingedPanther

WingedPanther

    A spammer's worst nightmare

  • Moderators
  • 16,831 posts
Do you know what line is causing the error, or what row on your spreadsheet?
Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog