unit Main;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Buttons, ExtCtrls, Grids, DBGrids, DB,
  DBClient, ADODB, ComCtrls,
  { M O J E }
  URekord, DBTables;

const cWERSJA  = '1.0.0.1';
const cSEP     = CHR(9);		// znak TABULACJI
const cHEADERS = 'P1'   + cSEP +
                 'P2'  + cSEP +
                 'P3'    + cSEP +
                 'P4';

type
  TFMain = class(TForm)
    Panel1: TPanel;
    btnCLOSE: TBitBtn;
    ADODataSet: TADODataSet;
    dsXLS: TDataSource;
    StatusBar: TStatusBar;
    ADOConnection: TADOConnection;
    Panel3: TPanel;
    lblDBFName: TLabel;
    lblTXTRec: TLabel;
    Panel4: TPanel;
    lblFileName: TLabel;
    lblReccount: TLabel;
    lblZakladka: TLabel;
    OpenDialog: TOpenDialog;
    lblCzasStart: TLabel;
    lblCzasStop: TLabel;
    lblCzas: TLabel;
    lblRec: TLabel;
    btnVW: TBitBtn;
    DBGrid1: TDBGrid;
    Memo1: TMemo;
    procedure FormShow(Sender: TObject);
    procedure btnCLOSEClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btnVWClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);

  private
    {
    sRec   : String;
    }

  public
    procedure Start(fn: String);
  end;


var
  FMain: TFMain;

implementation
{$R *.dfm}
{------------------------------------------------------------------------------}
procedure TFMain.FormClose(Sender: TObject; var Action: TCloseAction);
begin
   ADODataSet.Close();
   ADOConnection.Connected := false;
end;

procedure TFMain.FormCreate(Sender: TObject);
var
	katalog : String;

begin
	//---------------------------------------------------------------------------
   // dla całego programu:
   // data postaci 13-10-2005
   DateSeparator     := '-';
   ShortDateFormat   := 'yyyy-MM-dd';
   LongDateFormat    := 'yyyy-MM-dd';
   DecimalSeparator  := ',';
   {
   UWAGA: tego mi brakowało:
   na Windows Server 2000 cyfra
   1900,00 była wyświetlana jako 1,900,00 (po sformatowaniu)
   }
   ThousandSeparator := ' ';

   {
   ShowMessage(DateToStr(date()));
   }
	//---------------------------------------------------------------------------


	katalog := ExtractFilePath(Application.ExeName) + 'CSV';
   if not DirectoryExists(katalog) then
   begin
      MkDir(katalog);
   end;
end;

procedure TFMain.FormShow(Sender: TObject);
begin
   DBGrid1.Align := alClient;


	Memo1.Clear();
   Memo1.Visible := false;
end;

procedure TFMain.btnCLOSEClick(Sender: TObject);
begin
	Close();
end;

procedure TFMain.btnVWClick(Sender: TObject);
var
   fn : String;

begin
	OpenDialog.Title := 'Proszę wzskazać plik XLS';
	if OpenDialog.Execute() then
   begin
		fn := OpenDialog.FileName;
      fn := AnsiUpperCase(fn);
   end;

   if Pos('XLS', fn) = 0 then
   begin
   	exit;
   end;

   if FileExists(fn) then
   begin
   	Start(fn);
   end;
end;

procedure TFMain.Start(fn: String);
var
	s              : String;
	cs             : String;
	isOK           : Boolean;
   t1             : TTime;
   t2             : TTime;
   t3             : TTime;
   LZakladki      : TStringList;
   LZakladki2     : TStringList;
   lp             : LongInt;
   zakladka       : String;
   ct             : String;
   Recs           : LongInt;
   RRecs          : LongInt;
   poz            : Integer;
	ARow           : LongInt;
   rec_xls        : RecXLS;

   fn_t           : String;
	fh_t           : TextFile;
	sRec           : String;
   {
   ilosc_zakladek : Integer;
   }

begin

   ADODataSet.Close();
   ADOConnection.Close();

   lblFileName.Caption := 'Plik XLS: ' + ExtractFileName(fn);

	cs := '';
   cs := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='  + fn + ';Mode=Read;Extended Properties=Excel 8.0;Persist Security Info=False';
   ADOConnection.ConnectionString := cs;

	isOK := true;

   try
	   ADOConnection.Open();
   except
   	ShowMessage('ADOConnection - nie udane połączenie ze źródłem danych');
      isOK := false;
   end;

   if not isOK then
   begin
   	exit;
   end;

   t1 := Time();
   lblCzasStart.Caption := TimeToStr(t1);
   Application.ProcessMessages();

   LZakladki  := TStringList.Create();
   LZakladki2 := TStringList.Create();
   ADOConnection.GetTableNames(LZakladki2);

   // w LZakladki2 jest ZAKLADKA1 i ZAKLADKA$1, potrzebuję tylko te ze znakiem $
   for lp := 0 to LZakladki2.Count-1 do
   begin
   	zakladka := LZakladki2[lp];

      if Pos('$', zakladka) > 0 then
		begin
         LZakladki.Add(zakladka);
      end;
   end;

   if Assigned(LZakladki2) then
   begin
    	LZakladki2.Clear();
      FreeAndNil(LZakladki2);
   end;

	// liczę ile mam wierszy do przetworzenia
   lp := 0;
   for lp := 0 to LZakladki.Count-1 do
   begin
   	ct := LZakladki[lp];
		lblZakladka.Caption := ct;
      Application.ProcessMessages();

      ADODataSet.Close();
      ADODataSet.CommandText := ct;
      try
	      ADODataSet.Open();
   	except
         raise;
         RRecs := 0;
         break;
      end;

      Recs  := ADODataSet.RecordCount;
      RRecs := RRecs + Recs;
   end;

   lblReccount.Caption := 'Ilość wierszy: ' + IntToStr(RRecs);

   try
      // wszystkie dane będą zapisywane do pliku
      fn_t   := ExtractFileName(fn);
      poz    := Pos('.', fn_t);
      fn_t   := Copy(fn_t, 1, poz-1);
      fn_t   := ExtractFilePath(Application.ExeName) + 'CSV\' + fn_t + '.CSV';
      fn_t   := AnsiUpperCase(fn_t);

      AssignFile(fh_t, fn_t);
      Rewrite(fh_t);
		s := cHEADERS;
      Writeln(fh_t, s);

      // lecę po wszystkich zakładkach i zrzucam
      // dane do pliku
      Recs := 0;

      for lp := 0 to LZakladki.Count-1 do
      begin
         isOK := true;

         ADODataSet.Close();

         ct := LZakladki[lp];
         lblZakladka.Caption := ct;
	      Application.ProcessMessages();
         ADODataSet.CommandText := ct;

         try
            ADODataSet.Open();
         except
            isOK := false;
         end;


         if isOK then
         begin
         	// pomijam wiersz 0 w którym są nagłowki kolumn
         	for ARow := 1 to ADODataSet.RecordCount do
            begin
               Recs := Recs + 1;
               lblRec.Caption := IntToStr(Recs);
			      Application.ProcessMessages();

            	rec_xls.P1  := ADODataSet.FieldByName('P1').AsString;
               rec_xls.P2  := ADODataSet.FieldByName('P2').AsString;
					rec_xls.P3  := ADODataSet.FieldByName('P3').AsFloat;
					rec_xls.P4  := ADODataSet.FieldByName('P4').AsString;

		         sRec := '';
		         sRec := rec_xls.p1 + cSEP +
                       rec_xls.p2 + cSEP +
                       FloatToStr(rec_xls.p3) + cSEP +
                       rec_xls.p4;

               Writeln(fh_t, sRec);

               ADODataSet.Next();
            end;
         end;
      end;
   finally
	   CloseFile(fh_t);

	   ADODataSet.Close();
	   ADOConnection.Close();
   end;

   if Assigned(LZakladki) then
   begin
    	LZakladki.Clear();
      FreeAndNil(LZakladki);
   end;

   t2 := Time();
   lblCzasStop.Caption := TimeToStr(t2);
   t3 := t2 - t1;
   lblCzas.Caption := TimeToStr(t3);

   DBGrid1.Visible := false;
   Memo1.Align := alClient;
   Memo1.Lines.LoadFromFile(fn_t);
   Memo1.Visible := true;

   ShowMessage('Koniec');
end;

end.

