Write Data Into Excel Sheet

Question

auzziez 0 Newbie Poster

15 Years Ago

Hi,
I need to automate an Excel sheet. The requirement is that I need to read data from various CSV Files, maipulate them and place the results into specific cells in an Excel Sheet. This excel sheet is already prepared with lots of text. Only specific columns are left blank and which need to be filled through the new code. I am comforatable with reading data from CSV (as this has been implemented by one of my colleague). But I am not sure of how to write the data into an Excel Workbook. This excel workbook has two worksheets with specific names. So I will have to be able to place data into specific sheet as per requirement.

Note:
1. The system where I will be running this Pascal code does NOT have Microsoft Office(Excel) installed in it. Should it still be possible to achieve the required automation?

2. The excel sheet has some formatting which needs to be preserved.

I am quite new to Pascal so please bare with me. I might not be able to get you on the first instance itself.

pascal

5 Contributors
22 Replies
573 Views
1 Month Discussion Span
Latest Post 15 Years Ago Latest Post by dlhale

All 22 Replies

LizR 171 Posting Virtuoso

15 Years Ago

To achieve proper automation and to use excel spreadsheets it would require excel.

LizR 171 Posting Virtuoso

15 Years Ago

if you're populating the spreadsheet, the only thing thats going to open it to allow automation of filling it in, will be excel. So yes

LizR 171 Posting Virtuoso

15 Years Ago

only if your java code can be run from the equivelent of a command line then you can just call that from pascal.

LizR 171 Posting Virtuoso

15 Years Ago

Look up shellexec

dlhale 2 Newbie Poster

15 Years Ago

{***************************************************************
 XLSFile version 1.0
 (c) 1999 Yudi Wibisono & Masayu Leylia Khodra (DWIDATA)
 e-mail :  yudiwbs@bdg.centrin.net.id
 Address :  Sarijadi Blok 23 No 20, Bandung, Indonesia (40164)
 Phone :  (022) 218101
 XLSfile is free and you can modify it as long as this header
 and its copyright text is intact.
 If you make a modification, please notify me.
 WARNING! THE CODE IS PROVIDED AS IS WITH NO GUARANTEES OF ANY KIND!
 USE THIS AT YOUR OWN RISK - YOU ARE THE ONLY PERSON RESPONSIBLE FOR
 ANY DAMAGE THIS CODE MAY CAUSE - YOU HAVE BEEN WARNED!
 ****************************************************************}
unit XLSfile;
interface
uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs;
const
{BOF}
  CBOF      = $0009;
  BIT_BIFF5 = $0800;
  BIT_BIFF4 = $0400;
  BIT_BIFF3 = $0200;
  BOF_BIFF5 = CBOF or BIT_BIFF5;
  BOF_BIFF4 = CBOF or BIT_BIFF4;
  BOF_BIFF3 = CBOF or BIT_BIFF3;
{EOF}
  BIFF_EOF = $000a;
{Document types}
  DOCTYPE_XLS = $0010;
  DOCTYPE_XLC = $0020;
  DOCTYPE_XLM = $0040;
  DOCTYPE_XLW = $0100;
{Dimensions}
  DIMENSIONS = $0000;
  DIMENSIONS_BIFF4 = DIMENSIONS or BIT_BIFF3;
  DIMENSIONS_BIFF3 = DIMENSIONS or BIT_BIFF3;
type
  EReadError = class(Exception);
  EopCodeError = class(Exception);
  EOverUnderError = class(Exception);
  TModeOpen = (moWrite); //,moRead); //read not implemented yet
  TAtributCell = ( acHidden, acLocked, acShaded, acBottomBorder
                 , acTopBorder, acRightBorder, acLeftBorder, acLeft
                 , acCenter, acRight, acFill);
  TSetOfAtribut = set of TatributCell;
  TMyFiler = class
  public
    Stream : TStream;  //stream yang akan diisi/dibaca
    end;
  TMyReader = class(TMyFiler)
  public
    function readStr : string;
    function readDouble : double;
    function readInt : integer;
    function readByte : byte;
    function readWord : word;
    end;
  TMyWriter = class(TMyFiler)
  public
    procedure WriteSingleStr(s : string);
    //tidak ada informasi length di depan str,
    //digunakan untuk cell string di Excel
    procedure WriteStr(s : string);
    {req :  s shouldn't exceed 64KB}
    procedure WriteByte(b : byte);
    procedure WriteDouble(d : double);
    procedure WriteInt(i : integer);
    procedure WriteWord(w : word);
    end;
  TMyPersistent = class
  public
    opCode : word; //invarian :  opcode<>nil, opcode<>opcodeEOF dan dalam satu aplikasi tidak boleh ada class yang memiliki opcode sama
    procedure Write(W : TMyWriter);virtual;abstract;
    {req :  opcode sudah diisikan}
    procedure Read(R : TMyReader);virtual;abstract;
    {req :  opcode sudah diisikan}
    end;
  TDispatcher = class
  private
    StrList : TStringList;
    Reader : TMyReader;
    Writer : TMyWriter;
  protected
    FStream : TStream; //stream yang menjadi target
    procedure SetStream(vStream : TStream);
  public
    SLError : TStringList;
    OpcodeEOF : word; //opcode yg menandakan EOF
    procedure Clear;
    procedure RegisterObj(MyPers : TMyPersistent);
    {req :  MyPersistent.opCode<>0
    ens :  MyPersistent terdaftar}
    procedure Write;
    {ens :  semua data obj yang mendaftar masuk dalam stream}
    procedure Read;
    {ens :  semua obj yang mendaftar terisi}
    constructor create;
    destructor destroy;override;
    property Stream  :  TStream read FStream write SetStream;
    end;
  TData = class(TMyPersistent)
    end;
  TBOF = class (TData)      //record awal di file
    procedure read(R : TMyReader);override;
    {req :  opcode sudah diisi}
    procedure write(W : TMyWriter);override;
    {req :  opcode sudah diisi}
    constructor create;
    end;
  TDimension = class(TData) //record akhir
    MinSaveRecs,MaxSaveRecs,MinSaveCols,MaxSaveCols : word;
    procedure read(R : TMyReader);override;
    {req :  opcode sudah diisi}
    procedure write(W : TMyWriter);override;
    {req :  opcode sudah diisi}
    constructor create;
    end;
  TCellClass = class of TCell;
  TCell = class(TData)
  protected
     FAtribut : array [0..2] of byte;
     procedure SetAtribut(value : TSetOfAtribut);
     {ens :  FAtribut diatur sesuai dengan nilai value}
  public
     Col,Row : word;  //dari 1
     procedure read(R : TMyReader);override;
     procedure write(W : TMyWriter);override;
     property Atribut  :  TSetOfAtribut write SetAtribut;   //baru bisa nulis
     constructor create;virtual;abstract;
    end;
  TBlankCell = class(TCell)
    procedure read(R : TMyReader);override;
    procedure write(W : TMyWriter);override;
    {req :  col, row  dan  atribut sudah ditulis}
    constructor create;override;
    end;
  TDoubleCell = class(TCell)
    Value : double;
    procedure read(R : TMyReader);override;
    procedure write(W : TMyWriter);override;
    {req :  col, row  dan  atribut sudah ditulis}
    constructor create;override;
    end;
  TWordCell = class(TCell)
    Value : word;
    procedure read(R : TMyReader);override;
    procedure write(W : TMyWriter);override;
    {req :  col, row  dan  atribut sudah ditulis}
    constructor create;override;
    end;
  TStrCell = class(TCell)
     Value : string;
     procedure read(R : TMyReader);override;
     procedure write(W : TMyWriter);override;
     {req :  col, row  dan  atribut sudah ditulis}
     constructor create;override;
    end;
  TXLSfile = class(TComponent)
  private
    FFileName : string;
    ModeOpen : TModeOpen;
    Dispatcher : TDispatcher;
    BOF : TBOF;
    Dimension : TDimension;
    function AddCell(vCol,vRow : word;vAtribut : TSetOfAtribut;CellRef : TCellClass) : TCell;
    procedure AddData(D : TData);
  public
    procedure AddWordCell(vCol,vRow : word;vAtribut : TSetOfAtribut;aValue : word);
    procedure AddDoubleCell(vCol,vRow : word;vAtribut : TSetOfAtribut;aValue : double);
    procedure AddStrCell(vCol,vRow : word;vAtribut : TSetOfAtribut;aValue : String);
    procedure write;
    procedure clear;
    constructor create(AOwner : TComponent);override;
    destructor destroy;override;
  published
    property FileName  : string read FFileName write FFileName;
    end;
procedure Register;
implementation
function TMyReader.readByte : byte;
  begin
  Stream.Read(result,1);
  end;
function TMyReader.readWord : word;
  begin
  Stream.Read(result,2); //panjang string
  end;
function TMyReader.readStr : string;
  var
    strLeng : Word;
    tempStr : string;
  begin
  Stream.Read(strLeng,2); //strLeng string
  SetLength(tempStr,strLeng);
  Stream.Read(tempStr[1],strLeng);
  result := tempStr;
  end;
function TMyReader.readDouble : double;
  begin
  Stream.Read(result,8);
  end;
function TMyReader.readInt : integer;
  begin
  Stream.Read(result,4);
  end;
procedure TMyWriter.WriteByte(b : byte);
  begin
  Stream.write(b,1);
  end;
procedure TMyWriter.WriteWord(w : word);
  begin
  Stream.write(w,2);
  end;
procedure TMyWriter.WriteSingleStr(s : string);
  begin
  Stream.write(s[1],length(s));
  end;
procedure TMyWriter.WriteStr(s : string);
{req :  s shouldn't exceed 64KB}
  var
    strLeng : integer;
  begin
  strLeng := length(s);
  WriteWord(strLeng);
  Stream.write(s[1],strLeng);
  end;
procedure TMyWriter.WriteDouble(d : double);
  begin
  Stream.write(d,8); //asumsi double adalah 8 bytes
  end;
procedure TMyWriter.WriteInt(i : integer);
  begin
  Stream.write(i,4);
  end;
procedure TDispatcher.Clear;
  var
    i : integer;
  begin
  for i := 0 to StrList.count-1 do
    begin
    TMyPersistent(StrList.Objects[i]).Free;
    end;
  StrList.Clear;
  SLError.Clear;
  end;
procedure TDispatcher.SetStream(vStream : TStream);
  begin
  FStream := vStream;
  Reader.Stream := Fstream;
  Writer.stream := Fstream;
  end;
constructor TDispatcher.create;
  begin
  OpCodeEOF := 999;
  StrList := TStringlist.create;
  Reader := TMyReader.create;
  Writer := TMyWriter.create;
  SLError := TStringList.create;
  end;
destructor TDispatcher.destroy;
  begin
  Clear;
  StrList.free;
  Reader.free;
  Writer.free;
  SLError.free;
  inherited;
  end;
procedure TDispatcher.RegisterObj(MyPers : TMyPersistent);
{req :  MyPersistent.opCode<>0
ens :  MyPersistent terdaftar}
  begin
  StrList.AddObject(IntToStr(MyPers.opCode),MyPers);
  end;
procedure TDispatcher.Write;
{ens :  semua data obj yang mendaftar masuk dalam stream}
  var
    i : integer;
    pos,length : longint;
    x : Integer;
  begin
  //index stream, mulai dari 0!
  for i := 0 to StrList.Count-1 do
    begin
    Writer.WriteWord(TMyPersistent(StrList.objects[i]).Opcode);  //opcode
    Writer.WriteWord(0); //untuk tempat length record, nanti diisi lagi
    pos := Stream.Position;
    TMyPersistent(StrList.Objects[i]).Write(Writer);
    //length-nya jangan lupa
    length := Stream.Position-pos;
    Stream.Seek(-(length+2),soFromCurrent);  //balikin ke posisi tempat length
    Writer.WriteWord(length);
    Stream.Seek(length,soFromCurrent); //siap menulis lagi
    end;
  //penutup
  Writer.WriteWord(opCodeEOF);
  Writer.WriteWord(0); //panjangnya 0
  end;
procedure TDispatcher.Read;
{ req :  StrList terurut
  ens :  semua obj yang mendaftar terisi}
var
   idx : integer;
   opCode : word;
   strLeng,pos : longint;
   stop : boolean;
  begin
  stop := false;
  while not(stop) do
    begin
    opCode := Reader.ReadWord;
    strLeng := Reader.ReadWord;
    if opCode = opCodeEOF then
      stop := true
    else
      begin
      pos := Stream.Position;
      idx := StrList.IndexOf(IntToStr(opcode));
      if idx <> -1  then
        TMyPersistent(StrList.Objects[idx]).Read(Reader)
      else
        begin //opcode nggak dikenali
        SLError.Add(format('Unknown Opcode %d ',[opCode]));
        Stream.Seek(strLeng,soFromCurrent);  //repair
        end;
      //cek apakah kelewatan/kurang ngebacanya
      if Stream.Position <> pos+strLeng then
        begin
          begin
          if Stream.Position<pos+strLeng then
            begin
            SLError.Add(Format('Opcode %d underrun %d bytes',[opcode,(pos+strLeng)-Stream.Position]));
            Stream.Seek(Stream.Position - (pos+strLeng),soFromCurrent);//repair
            end
          else
            begin
            SLError.Add(Format('Opcode %d overrun %d bytes',[opcode,Stream.Position-(pos+strLeng)]));
            Stream.Seek((pos+strLeng)-Stream.Position,soFromCurrent); //repair
            end;
          end;
        end;
      end; //opcode EOF
    end; //end while
  if SLerror.count>0 then
    begin
    raise EReadError.Create
    ('File format error or file corrupt . Choose File -> Save as to save this file with new format');
    end;
  end;
constructor TXLSFile.create(AOwner : TComponent);
  begin
  inherited create(AOwner);
  ModeOpen := moWrite;
  Dispatcher := TDispatcher.create;
  Dispatcher.opcodeEOF := BIFF_EOF;
  clear;
  end;
destructor TXLSFile.destroy;
  begin
  Dispatcher.free;
  inherited;
  end;
function TXLSFile.AddCell(vCol,vRow : word;vAtribut : TSetOfAtribut;CellRef : TCellClass) : TCell;
//vCol dan Vrow mulai dari 0
//ens :  XLSfile yg buat, XLSFile yang bertanggung jawab
  var
     C : TCell;
  begin
  C := CellRef.create;
  with C do
    begin
    Col := vCol-1;
    Row := vRow-1; //yw 23 agt
    Atribut := vAtribut;
    end;
  AddData(C);
  Result := C;
  end;
procedure TXLSFile.AddWordCell(vCol,vRow : word;vAtribut : TSetOfAtribut;aValue : word);
  begin
  with TWordCell(AddCell(vCol,vRow,vAtribut,TWordCell)) do
    value := aValue;
  end;
procedure TXLSFile.AddDoubleCell(vCol,vRow : word;vAtribut : TSetOfAtribut;aValue : double);
  begin
  with TDoubleCell(AddCell(vCol,vRow,vAtribut,TDoubleCell)) do
    value := aValue;
  end;
procedure TXLSFile.AddStrCell(vCol,vRow : word;vAtribut : TSetOfAtribut;aValue : String);
  begin
  with TStrCell(AddCell(vCol,vRow,vAtribut,TStrCell)) do
    value := aValue;
  end;
procedure TXLSFile.AddData(D : TData);
//req :  BOF dan dimension telah ditambahkan lebih dulu
  begin
  Dispatcher.RegisterObj(D);
  end;
procedure TXLSFile.write;
{req :  ListDAta telah diisi}
  var
    FileStream : TFIleStream;
  begin
  FileStream := TFileStream.Create(FFileName,fmCreate);
  Dispatcher.Stream := FileStream;
  Dispatcher.Write;
  FileStream.Free;
  end;
procedure TXLSFile.clear;
{req :  - objek data yang dibuat secara manual (lewat c := TWordCell.create dst..) sudah di-free
      - BOF<>nil, Dimension<>nil    }
  begin
  Dispatcher.Clear;
  BOF := TBOF.create;
  Dimension := TDimension.create;
  Dispatcher.RegisterObj(BOF); //harus pertama
  Dispatcher.RegisterObj(Dimension); //harus kedua
  end;
//TBOF  ********************************************************************
constructor TBOF.create;
  begin
  opCOde := BOF_BIFF5;
  end;
procedure TBOF.read(R : TMyReader);
  begin
  end;
procedure TBOF.write(W : TMyWriter);
{req :  opcode sudah diisikan}
  begin
  with W do
    begin
    writeWord(0); //versi
    writeWord(DOCTYPE_XLS);
    writeWord(0);
    end;
  end;
//TDimension ****************************************************************
procedure TDimension.read(R : TMyReader);
{req :  opcode sudah diisi}
  begin
  end;
procedure TDimension.write(W : TMyWriter);
{req :  opcode sudah diisi}
  begin
  with w do
    begin
    WriteWord(MinSaveRecs);
    WriteWord(MaxSaveRecs);
    WriteWord(MinSaveCols);
    WriteWord(MaxSaveCols);
    end;
  end;
constructor TDimension.create;
  begin
  opCode := DIMENSIONS;
  MinSaveRecs  := 0;
  MaxSaveRecs  := 1000;
  MinSaveCols  := 0;
  MaxSaveCols  := 100;
  end;
//TCell ******************************************************************
procedure TCell.SetAtribut(value : TSetOfAtribut);
{ens :  FAtribut diatur sesuai dengan nilai value}
  var
    i : integer;
  begin
     //reset
  for i := 0 to High(FAtribut) do
    FAtribut[i] := 0;
     {Byte Offset     Bit   Description                     Contents
     0          7     Cell is not hidden              0b
                      Cell is hidden                  1b
                6     Cell is not locked              0b
                      Cell is locked                  1b
                5-0   Reserved, must be 0             000000b
     1          7-6   Font number (4 possible)
                5-0   Cell format code
     2          7     Cell is not shaded              0b
                      Cell is shaded                  1b
                6     Cell has no bottom border       0b
                      Cell has a bottom border        1b
                5     Cell has no top border          0b
                      Cell has a top border           1b
                4     Cell has no right border        0b
                      Cell has a right border         1b
                3     Cell has no left border         0b
                      Cell has a left border          1b
                2-0   Cell alignment code
                           general                    000b
                           left                       001b
                           center                     010b
                           right                      011b
                           fill                       100b
                           Multiplan default align.   111b
     }
   //  bit sequence 76543210
  if  acHidden in value then  //byte 0 bit 7 :
    FAtribut[0]  := FAtribut[0] + 128;
  if  acLocked in value then //byte 0 bit 6 :
    FAtribut[0]  := FAtribut[0] + 64 ;
  if  acShaded in value then //byte 2 bit 7 :
    FAtribut[2]  := FAtribut[2] + 128;
  if  acBottomBorder in value then //byte 2 bit 6
    FAtribut[2]  := FAtribut[2] + 64 ;
  if  acTopBorder in value then //byte 2 bit 5
    FAtribut[2]  := FAtribut[2] + 32;
  if  acRightBorder in value then //byte 2 bit 4
    FAtribut[2]  := FAtribut[2] + 16;
  if  acLeftBorder in value then //byte 2 bit 3
    FAtribut[2]  := FAtribut[2] + 8;
  if  acLeft in value then //byte 2 bit 1
    FAtribut[2]  := FAtribut[2] + 1
  else
    if  acCenter in value then //byte 2 bit 1
      FAtribut[2]  := FAtribut[2] + 2
    else if acRight in value then //byte 2, bit 0 dan bit 1
      FAtribut[2]  := FAtribut[2] + 3;
  if acFill in value then //byte 2, bit 0
    FAtribut[2]  := FAtribut[2] + 4;
  end;
procedure TCell.read(R : TMyReader);
  begin
  end;
procedure TCell.write(W : TMyWriter);
{req :  opcode sudah ditulis}
  var
     i : integer;
  begin
  with w do
    begin
    WriteWord(Row);
    WriteWord(Col);
    for i := 0 to 2 do
      begin
      writeByte(FAtribut[i]);
      end;
    end;
  end;
//TBlankCell  **************************************************************
procedure TBlankCell.read(R : TMyReader);
  begin
  end;
procedure TBlankCell.write(W : TMyWriter);
{req :  col, row  dan  atribut sudah ditulis}
  begin
  end;
constructor TBlankCell.create;
  begin
  opCode := 1;
  end;
//TWordCell **************************************************************
procedure TWordCell.read(R : TMyReader);
  begin
  end;
procedure TWordCell.write(W : TMyWriter);
{req :  col, row  dan  atribut sudah ditulis}
  begin
  inherited write(W);
  w.WriteWord(value);
  end;
constructor TWordCell.create;
  begin
  opCode := 2;
  end;
//TDoubleCell **************************************************************
procedure TDoubleCell.read(R : TMyReader);
  begin
  end;
procedure TDoubleCell.write(W : TMyWriter);
{req :  col, row  dan  atribut sudah ditulis}
  begin
  inherited write(W);
  w.writeDouble(value);
  end;
constructor TDoubleCell.create;
  begin
  opCode := 3;
  end;
//TStrCell ***************************************************************
procedure TStrCell.read(R : TMyReader);
  begin
  inherited read(R);
  end;
procedure TStrCell.write(W : TMyWriter);
{req :  col, row  dan  atribut sudah ditulis}
  begin
  inherited Write(W);
  w.WriteByte(length(value));
  w.WriteSIngleStr(value);
  end;
constructor TStrCell.create;
  begin
  opCode := 4;
  end;
//Register Component ******************************************************
procedure Register;
  begin
  RegisterComponents('Samples', [TXLSfile]);
  end;
{
  DIMENSIONS          = $0000;
  BLANK               = $0001;
  INTEGER             = $0002;
  NUMBER              = $0003;
  LABEL               = $0004;
  BOOLERR             = $0005;
  FORMULA             = $0006;
  STRING              = $0007;
  ROW                 = $0008;
  BOF                 = $0009;
  EOF                 = $000A;
  INDEX               = $000B;
  CALCCOUNT           = $000C;
  CALCMODE            = $000D;
  PRECISION           = $000E;
  REFMODE             = $000F;
  DELTA               = $0010;
  ITERATION           = $0011;
  PROTECT             = $0012;
  PASSWORD            = $0013;
  HEADER              = $0014;
  FOOTER              = $0015;
  EXTERNCOUNT         = $0016;
  EXTERNSHEET         = $0017;
  NAME                = $0018;
  WINDOWPROTECT       = $0019;
  VERTICALPAGEBREAKS  = $001A;
  HORIZONTALPAGEBREAK = $001B;
  NOTE                = $001C;
  SELECTION           = $001D;
  FORMAT              = $001E;
  BUILTINFMTCOUNT     = $001F;
  COLUMNDEFAULT       = $0020;
  ARRAY               = $0021;
  DATEMODE            = $0022;
  EXTERNNAME          = $0023;
  COLWIDTH            = $0024;
  DEFAULTROWHEIGHT    = $0025;
  LEFTMARGIN          = $0026;
  RIGHTMARGIN         = $0027;
  TOPMARGIN           = $0028;
  BOTTOMMARGIN        = $0029;
  PRINTHEADERS        = $002A;
  PRINTGRIDLINES      = $002B;
  FILEPASS            = $002F;
  FONT                = $0031;
  FONT2               = $0032;
  TABLEOP             = $0036;
  TABLEOP2            = $0037;
  CONTINUE            = $003C;
  WINDOW1             = $003D;
  WINDOW2             = $003E;
  BACKUP              = $0040;
  PANE                = $0041;
  CODEPAGE            = $0042;
  XF                  = $0043;
  IXFE                = $0044;
  EFONT               = $0045;
  DCONREF             = $0051;
  DEFCOLWIDTH         = $0055;
  BUILTINFMTCOUNT     = $0056;
  XCT                 = $0059;
  CRN                 = $005A;
  FILESHARING         = $005B;
  WRITEACCESS         = $005C;
  UNCALCED            = $005E;
  SAVERECALC          = $005F;
  OBJECTPROTECT       = $0063;
  COLINFO             = $007D;
  GUTS                = $0080;
  WSBOOL              = $0081;
  GRIDSET             = $0082;
  HCENTER             = $0083;
  VCENTER             = $0084;
  BOUNDSHEET          = $0085;
  WRITEPROT           = $0086;
  COUNTRY             = $008C;
  HIDEOBJ             = $008D;
  SORT                = $0090;
  PALETTE             = $0092;
  STANDARDWIDTH       = $0099;
  SCL                 = $00A0;
  SETUP               = $00A1;
  GCW                 = $00AB;
  MULRK               = $00BD;
  MULBLANK            = $00BE;
  RSTRING             = $00D6;
  DBCELL              = $00D7;
  BOOKBOOL            = $00DA;
  SCENPROTECT         = $00DD;
  XF                  = $00E0;
  MERGEDCELLS         = $00E5;
  BITMAP              = $00E9;
  PHONETIC            = $00EF;
  SST                 = $00FC;
  LABELSST            = $00FD;
  EXTSST              = $00FF;
  LABELRANGES         = $015F;
  USESELFS            = $0160;
  DSF                 = $0161;
  SUPBOOK             = $01AE;
  CONDFMT             = $01B0;
  DVAL                = $01B2;
  HLINK               = $01B8;
  DV                  = $01BE;
  DIMENSIONS          = $0200;
  BLANK               = $0201;
  NUMBER              = $0203;
  LABEL               = $0204;
  BOOLERR             = $0205;
  FORMULA             = $0206;
  STRING              = $0207;
  ROW                 = $0208;
  BOF                 = $0209;
  INDEX               = $020B;
  NAME                = $0218;
  ARRAY               = $0221;
  EXTERNNAME          = $0223;
  DEFAULTROWHEIGHT    = $0225;
  FONT                = $0231;
  TABLEOP             = $0236;
  WINDOW2             = $023E;
  XF                  = $0243;
  RK                  = $027E;
  STYLE               = $0293;
  FORMULA             = $0406;
  BOF                 = $0409;
  FORMAT              = $041E;
  XF                  = $0443;
  SHRFMLA             = $04BC;
  QUICKTIP            = $0800;
  BOF                 = $0809;
  SHEETLAYOUT         = $0862;
  SHEETPROTECTION     = $0867;
  RANGEPROTECTION     = $0868;
}
end.

delphiman commented: Excellent +2

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

auzziez 0 Newbie Poster · Answer 1 · 2008-11-10T12:11:41+00:00

Hi,
Actually the scenario is that I'll have to run the code for Automation at one of my test site(which has no excel). Once the code is able to perform the automation i'll just copy this excel sheet(completely filled by the automation process) to my local system(which has Office suite). Still is it necessary to have excel at my test site?... I will never open the excel sheet at the site itself.

auzziez 0 Newbie Poster · Answer 2 · 2008-11-11T18:25:14+00:00

ok..thanks for the info.....i'll first get excel installed onto the PC and then carry on forward....

auzziez 0 Newbie Poster · Answer 3 · 2008-11-11T18:30:03+00:00

and i am pretty comfortable with Java coding.... since pascal is quite new to me.... can i write the population code in java and call the class file in my pascal code?

auzziez 0 Newbie Poster · Answer 4 · 2008-11-11T20:47:51+00:00

i think that should surely be possible(running my java code from a command line)..... could you please help me how to go abt this technique?

auzziez 0 Newbie Poster · Answer 5 · 2008-11-11T21:11:54+00:00

hi...just for testing purpose i inserted the following line in one of my procedures:

ShellExecute(Handle, 'open', 'C:\Throughput.csv', nil, nil, SW_SHOWNORMAL);

but it gave error that "Undeclared identifier Handle".
I suppose this Handle refers to some form. How can I call ShellExec from a procedure?

LizR 171 Posting Virtuoso · Answer 6 · 2008-11-11T22:59:05+00:00

LizR 171 Posting Virtuoso

15 Years Ago

Was it in a form? or was that a console app?

auzziez 0 Newbie Poster · Answer 7 · 2008-11-12T15:25:43+00:00

hi...i got that working....just replaced "Handle" with "0"(zero)...and it could open the file....it was not in a form... it was in an application....

LizR 171 Posting Virtuoso · Answer 8 · 2008-11-12T15:37:56+00:00

ok, so it was a console application - and no that doesnt have a handle, all forms in a windows application have a handle.

jsosnowski 1 Junior Poster in Training · Answer 9 · 2008-11-14T00:01:24+00:00

I have a question for LizR here. I have read and written into Excel spreadsheets using an ADO table and Clientdataset setup. It is my thinking that this would note require Excel to work with the Excel file.

The set-up for doing this takes some knowledge of ADO databases to work with it, but it is possible to access (not a Microsoft Office pun) the spreadsheet represented within a DBGrid in an independent Delphi program.

LizR 171 Posting Virtuoso · Answer 10 · 2008-11-14T03:20:05+00:00

It would normally need excel to have the drivers for excel.

auzziez 0 Newbie Poster · Answer 11 · 2008-11-17T18:34:48+00:00

Hi,

That ShellExecute thing worked...but now in one of the functions(delphi/pascal) i need to connect to excel. Currently i am running on my PC which has Excel2003. But when i run the following code....it gives a runtime exception(EAccessViolation) at:
"AdoConnection1.Connected:=False;"

Function UpdateReport : Boolean;
begin
strConn:='Provider=Microsoft.Jet.OLEDB.4.0;' +
'Data Source= ' + 'C:\original_multiplaytrafficendtoend.xls' + ';' +
'Extended Properties=Excel 8.0;';

AdoConnection1.Connected:=False;
AdoConnection1.ConnectionString:=strConn;
try
AdoConnection1.Open;
AdoConnection1.GetTableNames(tableList,True);
except
ShowMessage('Unable to connect to Excel, make sure the workbook exist!');
raise;
end;
end;

delphiman 0 Newbie Poster · Answer 12 · 2009-01-02T21:47:15+00:00

Hi,
I need to automate an Excel sheet. The requirement is that I need to read data from various CSV Files, maipulate them and place the results into specific cells in an Excel Sheet. This excel sheet is already prepared with lots of text. Only specific columns are left blank and which need to be filled through the new code. I am comforatable with reading data from CSV (as this has been implemented by one of my colleague). But I am not sure of how to write the data into an Excel Workbook. This excel workbook has two worksheets with specific names. So I will have to be able to place data into specific sheet as per requirement.
Note:
1. The system where I will be running this Pascal code does NOT have Microsoft Office(Excel) installed in it. Should it still be possible to achieve the required automation?
2. The excel sheet has some formatting which needs to be preserved.
I am quite new to Pascal so please bare with me. I might not be able to get you on the first instance itself.

1.
You need to accumulate the "From" data into a PivotTable wherein the fields correspond with your already prepared spreadsheet.
2.
When you use the procedure below the result will be an XL table called "Book1".
3.
Save that to OVERWRITE an INTERFACE [XL] table which you have already prepared and to which your exisitng spread-sheet relates.

You will probably have to experiment with the following to suit your needs.

NOTE: This procedure is dependant upon your having MS-Excel but with some juggling you could probably acheive the same result with another spread-sheet.

procedure frmYourForm.btnSprdSheetClick(Sender: TObject);
var
AsAtDate : TDate;
AsAtYear, AsAtMonth, AsAtDay : Word;
RangeE: Excel2000.Range;
I, Row: Integer;
Bookmark: TBookmarkStr;
begin
AsAtDate := tblUsrTfrAsAtDate.value;
DecodeDate (AsAtDate, AsAtYear, AsAtMonth, AsAtDay);
begin
with qryPivotTable do
// Load Details from your FROM table into the PivotTable as follows
PopulatePivotTable; // You need to create this procedure
// create and show
ExcelApplication1.Visible [0] := True;
ExcelApplication1.Workbooks.Add (NULL, 0);
// fill is the first row with field titles
RangeE := ExcelApplication1.ActiveCell;
for I := 0 to qryPivotTable.Fields.Count - 1 do
begin
RangeE.Value := qryPivotTable.Fields .DisplayLabel;
RangeE := RangeE.Next;
end;
// add field data in following rows
qryPivotTable.DisableControls;
try
Bookmark := qryPivotTable.Bookmark;
try
qryPivotTable.First;
Row := 2;
while not qryPivotTable.EOF do
begin
RangeE := ExcelApplication1.Range ['A' + IntToStr (Row),
'A' + IntToStr (Row)];
for I := 0 to qryPivotTable.Fields.Count - 1 do
begin
RangeE.Value := qryPivotTable.Fields .AsString;
RangeE := RangeE.Next;
end;
qryPivotTable.Next;
Inc (Row);
end;
finally
qryPivotTable.Bookmark := Bookmark;
end;
finally
qryPivotTable.EnableControls;
// format the section
RangeE := ExcelApplication1.Range ;
RangeE.AutoFormat (3, NULL, NULL, NULL, NULL, NULL, NULL);
end;
end;
end;
end;

initialization
CoInitialize (nil);

delphiman 0 Newbie Poster · Answer 13 · 2009-01-02T21:50:08+00:00

SORRY! I have just noticed that you are using Excell 2003. I suspect the suggestion I gave you will only work with Exell 2007

LizR 171 Posting Virtuoso · Answer 14 · 2009-01-02T22:02:56+00:00

LizR 171 Posting Virtuoso

15 Years Ago

So you had to wake up a 6 week old post?

delphiman 0 Newbie Poster · Answer 15 · 2009-01-02T22:39:38+00:00

So you had to wake up a 6 week old post?

I did not "wake up" to the post. In fact I hardly ever come here but being at a loose end [2nd January!] I visited and found this problem. My solution works perfectly for me and I hope it solves HIS problem.

If you were so "awake" why didn't YOU solve [as described] during the past 6 weeks..Hmmmmm?

LizR 171 Posting Virtuoso · Answer 16 · 2009-01-02T22:47:35+00:00

Because he didnt come back and say anyt of the previous posts hadnt helped him with his issue.

delphiman 0 Newbie Poster · Answer 17 · 2009-01-02T23:22:48+00:00

>Because he didnt come back and say anyt of the previous posts >hadnt helped him with his issue.

Probably becase he didn't know the difference between what he already had - and the REAL slution!

Write Data Into Excel Sheet

Recommended Answers Collapse Answers

All 22 Replies

Recommended Answers